fix(db): reflect underlying driver socket state in isConnected and mark session closed on socket drop #1373

Problem

In PostgresConnection (lib/core/database/postgres_connection.dart, line 162):

bool get isConnected => _isConnected && _conn != null;

In MysqlConnection (lib/core/database/mysql_connection.dart, line 169):

bool get isConnected => _isConnected && _conn != null;

In RedisConnection (lib/core/database/redis_connection.dart, line 124):

bool get isConnected => _isConnected && _command != null;

1. Ghost Connection:
   _isConnected is set to true when connect() completes and is only set to false on explicit disconnect() or forceClose().
   When a remote database server closes the connection (idle timeout, server restart, TCP RST/FIN, network drop), the underlying driver socket is closed:

   • In package:postgres, _conn.isOpen becomes false.
   • In third_party/mysql_client, _conn.connected becomes false.
     However, isConnected continues returning true because _isConnected remains true and _conn != null.

2. Pool Reconnect Bypass:
   In PostgresConnectionPool and MysqlConnectionPool:

   Future<void> _ensureConnected(_PoolEntry entry, PgSessionMode mode) async {
     if (entry.connection.isConnected) return;
     try {
       await _reconnect(entry, mode);
     } catch (_) { ... }
   }

   Because isConnected erroneously returns true, _ensureConnected never calls _reconnect. It hands the dead session to the caller.

3. Persistent Error Loop:
   When execute(...) runs on the closed socket, it throws a socket / connection exception. execute(...) only catches TimeoutException to call forceClose(), so socket exceptions bypass cleanup. _isConnected stays true, permanently locking the pooled entry in a dead state where every subsequent query fails until the connection is manually invalidated or the app restarted.

Scope

• In PostgresConnection.isConnected, check _conn != null && _conn!.isOpen.
• In MysqlConnection.isConnected, check _conn != null && _conn!.connected (and listen to _conn!.onClose).
• In RedisConnection.isConnected, verify socket/command stream state.
• In execute(...) of both drivers, catch ConnectionLostException / socket errors and call unawaited(forceClose()) so the next call or pool acquire triggers a clean reconnect.
• Add unit tests verifying that a closed underlying socket causes isConnected to return false and triggers _reconnect in _ensureConnected.