Correct way to handle connection failure

[busslina]

For the moment I don't want a Pool, just a simple connection in my backend.

Firstly, I wanted to know where it throws (the SocketException). On Connection.open or on Connection.execute, so I wrapped Connection.open with runZonedGuarded (because it's an unhandled exception that occurs n time later, not just at connecting).
So, I tested it by stopping the Systemd Postgresql service and instantly I got that exception handled by the runZonedGuarded on Connection.open.

So, my questions are:

• Must I only handle this exception on that place or also in other places?
• How must I do to recover from this exception? Let's say I'm on the handler, must I reconnect there maybe using a connectionLock in order to delay queries until connection is recovered? Is there a better alternative?

Thanks.

[isoos]

For the moment I don't want a Pool, just a simple connection in my backend.

Note: you may have a Pool object with a limit of 1.

Must I only handle this exception on that place or also in other places?

In theory every operation that is sending/receiving bytes on the wire may fail.

How must I do to recover from this exception? Let's say I'm on the handler, must I reconnect there maybe using a connectionLock in order to delay queries until connection is recovered? Is there a better alternative?

I would not trust that connection anymore. It may be recoverable, it may not. Hence, Pool, which should create a new connection when there is a need for it, and reuse the existing one as long as it is correct. Furthermore, in long-running server processes, I've found that closing connections after 12 hours or after certain amount of queries being executed is helpful for both the postgres server and the app.

[busslina]

I implemented this solution before reading your response. I will take seriously your advice about not reusing failed connections and maybe to close them after certain amount of time.

I've found that closing connections after 12 hours or after certain amount of queries being executed is helpful for both the postgres server and the app.

This seems strange, like the need to reinstall Windows after some time because it gets dirty. Maybe PostgreSQL is doing something bad there?

import 'dart:async';

import 'package:busslina_dart_lightweight_lib/busslina_dart_lightweight_lib.dart';
import 'package:busslina_dart_postgresql_lib/lib.dart';

import 'package:postgres/postgres.dart';
import 'package:synchronized/synchronized.dart';

class PostgresqlModule with Logging, Module {
  late Connection _connection;

  final _errorLock = Lock(reentrant: false);

  @override
  String get name => 'PostgreSQL';

  Future<Connection> get connection =>

      // Lock mode
      _errorLock.synchronized(() => _connection);

  @override
  FutureOr<void> start() async {
    final endpoint = cc.read(appConfigCapsule).database.endpoint;

    log.t(endpoint.asString);

    await _connect();

    // TODO: Add listener to connection closed property
    missingImplementation(msg('Add listener to connection closed property'));
  }

  @override
  FutureOr<void> stop() async {
    await _connection.close();
  }

  Future<void> _connect({bool reconnectMode = false}) async {
    await runZonedGuarded(
      () async {
        final log = this.log;

        DateTime? attemptDatetime;
        int attempt = 1;

        while (true) {
          // Retry delay
          if (attemptDatetime != null) {
            const minDelay = Duration(seconds: 10);
            final elapsed = now().difference(attemptDatetime);

            if (minDelay > elapsed) {
              await (minDelay - elapsed).wait;
            }
          }

          log.i(msg(
              '${reconnectMode ? 'Reconnecting' : 'Connecting'} to database (attempt $attempt)'));

          // Connecting
          attemptDatetime = now();
          final res = await _connect_();

          // Connecting error
          if (res.connection == null) {
            log.e(
                'Error ${reconnectMode ? 'reconnecting' : 'connecting'} to PostgreSQL. Trying again: ${res.error!}');
            attempt++;
            continue;
          }

          // Connected
          _connection = res.connection!;

          log.i(msg(
              '${reconnectMode ? 'Reconnected' : 'Connected'} to the database'));
          break;
        }
      },

      // Lock mode -- Error
      (error, stack) => _errorLock.synchronized(
        () async {
          log.e(msg('Connection zone error: $error'));

          // Reconnecting
          await _connect(reconnectMode: true);
        },
      ),
    );
  }

  Future<({Connection? connection, String? error})> _connect_() async {
    try {
      return (
        connection:
            await Connection.open(cc.read(appConfigCapsule).database.endpoint),
        error: null,
      );
    } catch (e) {
      return (
        connection: null,
        error: '$e',
      );
    }
  }
}

[busslina]

I tested this implementation with two scenarios.

1. The app starts with PostgreSQL Systemd service stopped. Works fine.
2. The DB connection is established, then I stop the PostgreSQL Systemd service. Immediately, the runZonedGuarded catches the error and tries to reconnect.

So I finally obtained my desired behaviour.

Does Pool behaves similarly?

[busslina]

I didn't realized, but I'm not reusing the failed connection (creating a new one instead)