Bug: _transactionException not cleared after ROLLBACK TO SAVEPOINT, causing silent rollback of recovered transactions

[kartikey321]

Summary

When a PgException occurs inside a runTx callback, the driver sets an internal _transactionException field. If the caller catches the error and issues ROLLBACK TO SAVEPOINT to restore PostgreSQL to a healthy state, the driver still holds the stale exception. At commit time, mayCommit returns false because _transactionException != null, so the driver sends ROLLBACK instead of COMMIT — silently discarding all work that succeeded at the PostgreSQL level.

The failure mode is particularly insidious: no exception is thrown from any individual operation, the transaction callback returns normally, but runTx throws the original (already-handled) error and the entire transaction is rolled back.

Environment

• Package: postgres 3.5.11
• Dart SDK: 3.12.0
• PostgreSQL: 17 (latest)

Reproducer

import 'package:postgres/postgres.dart';

Future<void> main() async {
  final conn = await Connection.open(
    Endpoint(
      host: 'localhost',
      database: 'YOUR_DB',
      username: 'YOUR_USER',
      password: 'YOUR_PASSWORD',
    ),
    settings: ConnectionSettings(sslMode: SslMode.disable),
  );

  await conn.execute('''
    CREATE TABLE IF NOT EXISTS _reproducer_test (
      id TEXT PRIMARY KEY,
      val TEXT
    )
  ''');
  await conn.execute('DELETE FROM _reproducer_test');

  print('--- Test 1: ROLLBACK TO SAVEPOINT recovery ---');
  try {
    await conn.runTx((tx) async {
      await tx.execute('SAVEPOINT sp1');

      try {
        // 42P01 — relation does not exist
        await tx.execute('SELECT 1 FROM _nonexistent_table_xyz LIMIT 1');
      } catch (e) {
        print('  Caught expected error: $e');
        // Restore PG to a healthy state — transaction is fully recoverable
        await tx.execute('ROLLBACK TO SAVEPOINT sp1');
        await tx.execute('RELEASE SAVEPOINT sp1');
      }

      // This INSERT succeeds at the PostgreSQL level
      await tx.execute(
        "INSERT INTO _reproducer_test(id, val) VALUES ('1', 'hello')",
      );
      print('  INSERT succeeded inside transaction');
    });
    print('  runTx completed — no exception (expected)');
  } catch (e) {
    print('  runTx THREW (bug): $e');
  }

  final rows = await conn.execute('SELECT * FROM _reproducer_test');
  if (rows.isEmpty) {
    print('  CONFIRMED BUG: row was NOT committed — silently rolled back');
  } else {
    print('  OK: row committed correctly');
  }

  print('');
  print('--- Test 2: same insert WITHOUT prior error — should commit ---');
  await conn.runTx((tx) async {
    await tx.execute(
      "INSERT INTO _reproducer_test(id, val) VALUES ('2', 'world')",
    );
  });
  final rows2 = await conn.execute('SELECT * FROM _reproducer_test');
  print('  Rows after clean transaction: ${rows2.length} (expect 1)');

  await conn.execute('DROP TABLE IF EXISTS _reproducer_test');
  await conn.close();
}

Actual output

--- Test 1: ROLLBACK TO SAVEPOINT recovery ---
  Caught expected error: Severity.error 42P01: relation "_nonexistent_table_xyz" does not exist
  INSERT succeeded inside transaction
  runTx THREW (bug): Severity.error 42P01: relation "_nonexistent_table_xyz" does not exist
  CONFIRMED BUG: row was NOT committed — silently rolled back

--- Test 2: same insert WITHOUT prior error — should commit ---
  Rows after clean transaction: 1 (expect 1)

Expected output

--- Test 1: ROLLBACK TO SAVEPOINT recovery ---
  Caught expected error: Severity.error 42P01: ...
  INSERT succeeded inside transaction
  runTx completed — no exception (expected)
  OK: row committed correctly

Root cause

In lib/src/v3/connection.dart, every ErrorResponseMessage received while a transaction is active sets the field (line ~531):

_connection._activeTransaction?._transactionException = exception;

There is no code path that ever clears this field. mayCommit checks it:

bool get mayCommit =>
    !_closing &&
    _connection._activeTransaction == this &&
    _transactionException == null; // always false after any error

After a successful ROLLBACK TO SAVEPOINT, PostgreSQL sends ReadyForQuery with status byte 'T' — authoritatively confirming the connection is in a healthy transaction state. The driver receives this message but ignores it for the purpose of clearing _transactionException.

Fix

In _handleMessage, clear _transactionException when PostgreSQL reports the transaction is healthy:

} else if (_pending != null) {
  if (message is ReadyForQueryMessage &&
      message.state == ReadyForQueryMessageState.transaction) {
    _activeTransaction?._transactionException = null;
  }
  await _pending!.handleMessage(message);
}

Impact

This affects any pattern that uses savepoints for error recovery inside runTx:

• Auto-provisioning patterns ("create table if not exists on first use" inside a transaction)
• Nested transactions with partial rollback semantics
• Middleware that catches and recovers from specific PostgreSQL errors mid-transaction

[kartikey321]

This issue is fixed in pr #457