Query resolved prematurely on CommandComplete in implicit transactions, ignoring subsequent ErrorResponse

[jarohen]

Description

postgres.js resolves query promises immediately upon receiving a CommandComplete message, but this can cause issues in implicit transactions where validation errors occur during the sync phase. According to the PostgreSQL protocol, implicit transactions are only committed when the server receives a Sync message, meaning errors can still occur after CommandComplete but before ReadyForQuery.

Current Behavior

In the extended query protocol with implicit transactions:

1. Execute → Server buffers DML and sends CommandComplete
2. Sync → Server attempts to commit the implicit transaction
3. If validation fails during commit → ErrorResponse sent
4. ReadyForQuery → Protocol cycle completes

postgres.js resolves the query promise at step 1, ignoring any errors that occur during the actual transaction commit in steps 2-3.

Expected Behavior

For implicit transactions, the query should not be resolved until the Sync/commit phase completes successfully. If validation fails during commit, the query should be rejected with the appropriate error.

In the extended query protocol:

• Explicit transactions: CommandComplete can safely indicate success since the transaction isn't committed until an explicit COMMIT
• Implicit transactions: CommandComplete only means the command was buffered; the transaction commits during Sync, where validation errors can still occur

Reproduction

This occurs when connecting to databases that:

1. Use implicit transactions for single DML statements
2. Send CommandComplete immediately after buffering operations
3. Perform validation during the sync/commit phase
4. Send ErrorResponse after CommandComplete if validation fails during commit

Example Protocol Trace

→ Parse: "INSERT INTO ..."
← ParseComplete
→ Bind
← BindComplete
→ Execute
← CommandComplete ← Query resolved here (but transaction not committed yet!)
→ Sync ← Implicit transaction commits here
← ErrorResponse ← Validation error during commit (ignored - promise already resolved)
← ReadyForQuery

Potential Root Cause and Fix?

In src/connection.js, the CommandComplete() function calls query.resolve(result) immediately, regardless of whether we're in an implicit transaction that hasn't committed yet:

function CommandComplete(x) {
// ... parsing logic ...
query.resolve(result) // ← Promise resolved before implicit transaction commits
}

For implicit transactions in extended query protocol, delay query resolution until ReadyForQuery is received, which indicates the implicit transaction has been successfully committed. The fix should:

1. Detect when we're in an implicit transaction
2. Store the result from CommandComplete but don't resolve yet
3. Only resolve with the stored result if ReadyForQuery is received without an intervening error
4. If ErrorResponse occurs, reject the query instead

Impact

This affects any PostgreSQL-compatible database that performs validation during the implicit transaction commit phase rather than during command preparation, which is a valid implementation choice.