Race condition when initialising indexer-db container

[almereyda]

Describe the bug

When running the whole stack from an empty state with docker compose up, there will be an issue with a missing import_from_staging_2 procedure.

To Reproduce

1. docker compose up / docker compose up -d && docker compose logs -f
2. See errors:

During initialisation of the indexer-db, we find that sometimes the indexer-db-init logic already runs, when the database is not yet accepting connections.

This reproducibly leads to errors within the blockchain-indexer:

Round 5: Round 5 started at 02/09/2023 22:15:12.
Round 5:  Importing from staging tables ..
42883: procedure import_from_staging_2() does not exist

POSITION: 6
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|211_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery()
   at Dapper.SqlMapper.ExecuteCommand(IDbConnection cnn, CommandDefinition& command, Action`2 paramReader) in /_/Dapper/SqlMapper.cs:line 2848
   at Dapper.SqlMapper.ExecuteImpl(IDbConnection cnn, CommandDefinition& command) in /_/Dapper/SqlMapper.cs:line 581
   at Dapper.SqlMapper.Execute(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType) in /_/Dapper/SqlMapper.cs:line 452
   at CirclesLand.BlockchainIndexer.Persistence.ImportProcedure.ImportFromStaging(NpgsqlConnection connection, Int32 timeout) in /src/CirclesLand.BlockchainIndexer/Persistence/ImportProcedure.cs:line 23
Round 5: 42883: procedure import_from_staging_2() does not exist

Expected behavior

The initialisation script is waiting for the database to come up before attempting to load its schema.

Workaround

To have this running, before starting the whole application stack, we can cleanly initialise the databases beforehand, and only then start the application, such as:

rm -rf .state/indexer-db
docker compose up indexer-db # wait for database initialisation to complete, then ^C
docker compose up -d indexer-db
docker compose run --rm indexer-db-init
docker compose up -d

This way the indexer database will already be present, when the initialisation logic immediately tries to write to it, not leaving the database in an inconsistent state.

[almereyda]

To automatically resolve the dependency question, we can either:

• implement the wait-for pattern inside the *-init containers' seed.sh scripts to wait for the database to be ready, before writing to it, or
• use the Compose v2 Use Compose V2 compose.yaml and DRY it out #5 long syntax of the depends_on expression and add a condition: of service_healthy.

Putting this feature to best use, the services depending on the initialisation containers to run successfully can then depend on those with a condition of service_completed_successfully.