Design: Transaction Status Tracking

[SeanTAllen]

Expose the PostgreSQL transaction status indicator (ReadyForQuery status byte) to users via a new callback on SessionStatusNotify, and fix a bug where explicit transactions (BEGIN/COMMIT) stall the query queue.

This is a multi-type PR: a bug fix (broken explicit transactions) and a new feature (transaction status callback). Per CHANGELOG procedures, this needs manual CHANGELOG handling with entries under both "Fixed" and "Added".

Background

Every ReadyForQuery message includes a status byte:

• I — Idle (not in a transaction)
• T — In a transaction block (after BEGIN, before COMMIT/ROLLBACK)
• E — In a failed transaction block (queries rejected until ROLLBACK)

The driver already parses this byte — _ReadyForQueryMessage has idle(), in_transaction_block(), and failed_transaction() methods — but does not expose it to users.

Bug Found During Research

The query state machine only transitions from _QueryNotReady to _QueryReady when the status is idle ('I'). When the status is 'T' or 'E', the state stays _QueryNotReady, which prevents the queue from dispatching the next operation. This means explicit transactions are broken:

1. User sends BEGIN — dispatched via _QueryReady, enters _SimpleQueryInFlight
2. Server responds with CommandComplete + ReadyForQuery('T')
3. on_ready_for_query checks msg.idle() — false — transitions to _QueryNotReady
4. Next queued query never dispatches — permanent stall

The same msg.idle() check exists in all five query states: _QueryNotReady, _SimpleQueryInFlight, _ExtendedQueryInFlight, _PrepareInFlight, _CloseStatementInFlight.

This fix is included in this design because transaction status tracking is only useful when transactions actually work.

Design

New Public Types

transaction_status.pony:

primitive TransactionIdle
  """
  The session is idle, not in a transaction block. Each statement is
  auto-committed.
  """

primitive TransactionInBlock
  """
  The session is inside a transaction block (after BEGIN, before
  COMMIT/ROLLBACK).
  """

primitive TransactionFailed
  """
  The session is in a failed transaction block. All statements will be
  rejected by the server until a ROLLBACK is issued.
  """

type TransactionStatus is
  (TransactionIdle | TransactionInBlock | TransactionFailed)

New Callback

Add to SessionStatusNotify:

be pg_transaction_status(session: Session, status: TransactionStatus) =>
  """
  Called when the server reports its transaction status via ReadyForQuery.
  Fires after every query cycle completes, including the initial ready
  signal after authentication. The status indicates whether the session is
  idle, in a transaction block, or in a failed transaction.
  """
  None

Non-breaking — the default body means existing implementations don't need to change.

Callback Delivery Point

In _SessionLoggedIn.on_ready_for_query, before delegating to the query state:

fun ref on_ready_for_query(s: Session ref, msg: _ReadyForQueryMessage) =>
  _notify.pg_transaction_status(s, msg.transaction_status())
  query_state.on_ready_for_query(s, this)

The conversion from status byte to TransactionStatus is done by a new method on _ReadyForQueryMessage:

fun val transaction_status(): TransactionStatus =>
  if _status == 'I' then TransactionIdle
  elseif _status == 'T' then TransactionInBlock
  else TransactionFailed
  end

This replaces the three individual methods (idle(), in_transaction_block(), failed_transaction()) which become unused after the query state machine fix.

Message Ordering

When SessionStatusNotify and ResultReceiver are implemented by the same actor, pg_query_result is always delivered before pg_transaction_status because CommandComplete precedes ReadyForQuery in the protocol. The same holds for pg_query_failed (from ErrorResponse). When implemented by different actors, the sends are ordered but delivery depends on actor scheduling.

Bug Fix: Always Transition to _QueryReady on ReadyForQuery

ReadyForQuery means the server is ready for the next command — the status byte is informational, not a readiness indicator. Change every on_ready_for_query in the query state machine to always transition to _QueryReady.

Since the query states no longer inspect the _ReadyForQueryMessage, remove the msg parameter from the _QueryState.on_ready_for_query interface. The _SessionState.on_ready_for_query interface retains msg (it's called from _ResponseMessageParser which passes it). All five _QueryState implementations simplify:

_QueryNotReady — before:

fun ref on_ready_for_query(s: Session ref, li: _SessionLoggedIn ref,
  msg: _ReadyForQueryMessage)
=>
  if msg.idle() then
    li.query_state = _QueryReady
    li.query_state.try_run_query(s, li)
  end

After:

fun ref on_ready_for_query(s: Session ref, li: _SessionLoggedIn ref) =>
  li.query_state = _QueryReady
  li.query_state.try_run_query(s, li)

Update _QueryNotReady docstring: it is now only the initial state after authentication (before the first ReadyForQuery). It is no longer entered from non-idle ReadyForQuery responses.

_SimpleQueryInFlight, _ExtendedQueryInFlight, _PrepareInFlight, _CloseStatementInFlight — all have the same change pattern. Before (using _SimpleQueryInFlight as example):

fun ref on_ready_for_query(s: Session ref, li: _SessionLoggedIn ref,
  msg: _ReadyForQueryMessage)
=>
  try li.query_queue.shift()? else _Unreachable() end
  if msg.idle() then
    li.query_state = _QueryReady
    li.query_state.try_run_query(s, li)
  else
    li.query_state = _QueryNotReady
  end

After:

fun ref on_ready_for_query(s: Session ref, li: _SessionLoggedIn ref) =>
  try li.query_queue.shift()? else _Unreachable() end
  li.query_state = _QueryReady
  li.query_state.try_run_query(s, li)

_QueryReady (protocol anomaly handler) — unchanged in behavior, just drops the unused parameter.

Files Changed

1. New: postgres/transaction_status.pony — TransactionIdle, TransactionInBlock, TransactionFailed, TransactionStatus
2. Edit: postgres/session_status_notify.pony — add pg_transaction_status callback
3. Edit: postgres/_backend_messages.pony — replace idle(), in_transaction_block(), failed_transaction() with transaction_status() on _ReadyForQueryMessage; update docstring
4. Edit: postgres/session.pony — fire callback in _SessionLoggedIn.on_ready_for_query; remove msg parameter from _QueryState.on_ready_for_query interface; remove msg.idle() checks from all five query states; update _QueryNotReady docstring
5. Edit: postgres/_test_response_parser.pony — update parser tests that call idle(), in_transaction_block(), failed_transaction() to use transaction_status() instead
6. Edit: postgres/_test.pony — register new test classes in Main test actor
7. Edit: CLAUDE.md — update architecture (Query Execution Flow description of _QueryNotReady), test organization (new _test_transaction_status.pony entry), public API types (TransactionStatus), file layout (new files), supported features

Tests

Unit Tests (Mock Servers)

New test file: postgres/_test_transaction_status.pony

Mock port assignments: 7683, 7684, 7685

• _TestTransactionStatusIdle (port 7683) — mock server sends AuthOk + ReadyForQuery('I'). Client verifies pg_transaction_status fires with TransactionIdle.

• _TestTransactionStatusInBlock (port 7684) — mock server authenticates, then on query sends CommandComplete + ReadyForQuery('T'). Client sends a second query to verify the queue dispatches (the bug fix). Verifies pg_transaction_status fires with TransactionInBlock. The test client tracks the sequence of status callbacks to verify the expected order.

• _TestTransactionStatusFailed (port 7685) — mock server authenticates, then on query sends ErrorResponse + ReadyForQuery('E'). Client sends a follow-up query (ROLLBACK simulation) to verify the queue still dispatches. Verifies pg_transaction_status fires with TransactionFailed. The test client tracks the sequence of status callbacks.

Integration Tests

Added to postgres/_test_query.pony:

• _TestTransactionCommit — BEGIN, INSERT into a test table, COMMIT. The test client tracks the sequence of pg_transaction_status callbacks and verifies the transitions: Idle -> InBlock -> InBlock -> Idle (four callbacks total: initial post-auth, after BEGIN, after INSERT, after COMMIT).

• _TestTransactionRollbackAfterFailure — BEGIN, run an invalid query (e.g., SELECT from nonexistent table), ROLLBACK. The test client tracks callbacks and verifies transitions: Idle -> InBlock -> Failed -> Idle.

Existing Tests

No regressions expected. Existing tests don't use explicit transactions, so all ReadyForQuery messages are 'I'. The on_ready_for_query behavior change (always transition to _QueryReady) is transparent when status is idle — the removed else branch was dead code in practice.

Parser tests that assert on idle(), in_transaction_block(), failed_transaction() will be updated to assert on transaction_status() returning the correct primitive.

Example

New example: examples/transaction/transaction-example.pony

Demonstrates:

• BEGIN + INSERT + COMMIT flow
• Transaction status tracking via pg_transaction_status callback
• Console output showing status transitions

Build/Run Commands

make ssl=3.0.x                      # full build and test
make unit-tests ssl=3.0.x           # unit tests only
make integration-tests ssl=3.0.x    # integration tests (needs postgres containers)

[SeanTAllen]

Is TransactionStatus more like TransactionState? That isn't quite right but "not in a transaction" as TransactionIdle feels weird from a terminology standpoint.

[SeanTAllen]

i understand these are pg terms so we should probably keep them.

[SeanTAllen]

Both parts of this design are now implemented:

• Bug fix (ReadyForQuery queue stall with explicit transactions): PR Fix ReadyForQuery queue stall with explicit transactions #105, merged
• Feature (transaction status tracking via pg_transaction_status callback): PR Add transaction status tracking #106, merged

Closing as fully implemented.