Indexer triggers DB error "FATAL: sorry, too many clients already" when doing too many sequential queries (no parallel queries)

[fabrice102]

Summary

Querying accounts with a previous round even sequentially make the DB display the following error:

FATAL:  sorry, too many clients already

and then a long list of

LOG:  unexpected EOF on client connection with an open transaction

The indexer returns the message:

{"message":"Indexer health error: %s"}

(error 500)
and sometimes completely crashes.

To try, run:

for i in $(seq 200)
do
  curl "$AF_IDX_ADDRESS/v2/accounts/GFIDQ2XIX2CMPJZ4DS3ISIOPOCJQDGRB5JQGCVHG63CACQF3OXXRBLOIDM?round=15049487"
done

against an indexer $AF_IDX_ADDRESS for TestNet.
(You may want to change the round to the current round.)

Note that this code does not make any parallel queries to the indexer, it waits for the previous query to end before making a new query. So this should almost create no load on the indexer and on the DB.

TestNet indexer does not seem to crash. MainNet indexer does.

Setup

• algorand-indexer version:

  $ algorand-indexer -v
  2.5.0 compiled at 2021-06-22T18:26:33+0000 from git hash 1f3fb466681dc0f025be504576e4632b83130e27 (modified)

• OS: macOS BigSur 11.4

Version 2.3.4 of indexer did not have this issue.

Urgency/Relative Priority

Relatively urgent: Allows crashes of the indexer with very low load

[winder]

Duplicate of #517

[fabrice102]

Two guesses (not tested/verified):

• indexer/idb/postgres/postgres.go

  Line 1609 in 8a73e91

  close(out)

  
  does not commit/rollback the transactions.

• indexer/idb/postgres/postgres.go

  Line 1722 in 8a73e91

  func (db *IndexerDb) yieldTxnsThreadSimple(ctx context.Context, rows *sql.Rows, results chan<- idb.TxnRow, countp *int, errp *error) {

  does not .Close the rows object when it early finishes the for loop (https://golang.org/pkg/database/sql/#Rows.Close)

[tolikzinovyev]

@fabrice102, thanks for the suggestions! Please check if #554 fixes it.