PostgreSQLSeverity.error : Attempting to execute query, but connection is not open.

[kevinelliott]

Occasionally a service I have written will get into a state where the connections are exhausted in closed state, and the executor pool is unusable. This might be because the database suddenly becomes unavailable and restores itself, but the connection pool hasn't reset (reconnected the connections in the pool).

Is there a way to sanity check the pool and ensure that the connections are usable?

[ERROR] - PostgreSQLSeverity.error : Attempting to execute query, but connection is not open. 
#0      _PostgreSQLExecutionContextMixin._query (package:postgres/src/connection.dart:444)
#1      _PostgreSQLExecutionContextMixin.query (package:postgres/src/connection.dart:427)
#2      PostgreSqlExecutor.query (package:angel3_orm_postgres/src/orm_postgres.dart:59)
#3      PostgreSqlExecutorPool.query.<anonymous closure> (package:angel3_orm_postgres/src/orm_postgres.dart:167)
<asynchronous suspension>
#4      Pool.withResource (package:pool/pool.dart:127)
<asynchronous suspension>
#5      StationManager.findByIdent (package:aggregation_server/orm/managers/station_manager.dart:24)
<asynchronous suspension>

I would expect that the pool would self-manage itself and ensure that it is healthy, and if a query was executed it would reconnect as much as possible.

[dukefirehawk]

I will take a look in detail. It is good to have sanity check. If don't have will add one.

[kevinelliott]

Thanks @dukefirehawk , looking forward to that.

[kevinelliott]

Checking in, any luck @dukefirehawk ? I'm still getting plagued by these errors.

[dukefirehawk]

Moving this to top of the priority list to be worked on.

[dukefirehawk]

@kevinelliott PostgreSqlExecutor currently does not handle re-connection when db connection is broken. This is being worked on and will be included in the next release. In the meantime, you may want to try out PostgreSqlPoolExecutor which supports db re-connection. Under the hood, it is using postgres_pool package. See the example below in using it.

Future<void> configureServer(Angel app) async {
  try {
    // var connection = await connectToPostgres(app.configuration);
    // await connection.open();
    // var executor = PostgreSqlExecutor(connection, logger: app.logger);

    var executor = getPostgreSqlPoolExecutor(app.configuration);

    app
      ..container.registerSingleton<QueryExecutor>(executor)
      ..shutdownHooks.add((_) => executor.close());
  } catch (e) {
    app.logger.severe("Failed to connect to PostgreSQL. ORM disabled.", e);
  }
}

PostgreSqlPoolExecutor getPostgreSqlPoolExecutor(Map configuration) {
  var postgresConfig = configuration['postgres'] as Map? ?? {};
  return PostgreSqlPoolExecutor(PgPool(
    PgEndpoint(
      host: postgresConfig['host'] as String? ?? 'localhost',
      port: postgresConfig['port'] as int? ?? 5432,
      database: postgresConfig['database_name'] as String? ??
          Platform.environment['USER'] ??
          Platform.environment['USERNAME'] ??
          '',
      username: postgresConfig['username'] as String?,
      password: postgresConfig['password'] as String?,
    ),
    settings: PgPoolSettings()
      ..maxConnectionAge = Duration(hours: 1)
      ..concurrency = 5,
  ));
}

[kevinelliott]

PostgreSqlPoolExecutor

Thanks, I've gone ahead and implemented and deployed based on this, and will let you know if I see the issue surface again.

[dukefirehawk]

Resolved in the latest release angel3_orm_postgres 6.1.0

[kevinelliott]

Using the suggested code above, I have not had the issue come back for a month now. However, I have not yet tried to use what was released in 6.1.0... what would need to change to use what is built in?

Also, is there support for multiple servers, specifically as they change from read-write to read-only due to failover?

[dukefirehawk]

Great to hear that. The connection retry is done inside PostgreSqlExecutor, so it is transparent to the caller. Nothing need to be changed if already using it.

Multiple servers are not supported. However, if PostgreSQL is run in multi master replication setting behind a load balancer, the connection retry should enable it to work.

[kevinelliott]

Can you suggest a load balancer that is intelligent enough to know which is the active read-write and which is the active read-only?

[dukefirehawk]

I do not have a specific recommendation for dart backend since have not done enough testing in that area to make the call. However, the following documents provide a couple solutions that have been around for a long time.

• https://www.postgresql.org/docs/current/high-availability.html
• https://severalnines.com/blog/comparing-load-balancers-postgresql/