Motivation

The HTTP (/api/sql) and WebSocket (/api/experimental/sql) APIs report the statement that ends an implicit transaction as successful before they commit that transaction. If the commit then fails, the client receives the statement's success, then an extra error, and the request continues with its next query, although the write never became durable.

Reproduced on v26.44.0 with a failpoint and a concurrent ALTER TABLE: the Extended request [INSERT INTO t VALUES (100), SELECT 1] returned [{"ok": "INSERT 0 1"}, {"error": {"code": "40001", ...}}, {"tag": "SELECT 1", ...}], three results for two queries, and t stayed empty. The HTTP API docs promise the opposite: the request stops at the first error, and each committed statement returns exactly one value. Writes that stage their rows until the commit are affected, for example a constant INSERT ... VALUES.

Found while working on CNS-170 (server-side execution time for the Console), where the early success also stops a client's timer before the commit.

Description

Best reviewed commit by commit:

1. The fix in src/environmentd/src/http/sql.rs, the HTTP and WebSocket API docs, and a TODO in pgwire.
2. The regression test.

• Commit before reporting.
  • The statement that ends its group in an implicit transaction now commits before its result is built. That is each query of an Extended request, and the last statement of a Simple request.
  • A statement that returns rows commits after its rows and before its completion (CommandComplete on WebSocket).
  • Both paths share commit_if_ends_group. The group-level commit in execute_request remains for groups that end in a SUBSCRIBE or return early.
• Commit failure.
  • The commit error replaces the statement's success and stops the request, like any other statement error.
  • On WebSocket, a Simple request whose last statement returns rows still streams those rows before the error. PostgreSQL's simple query protocol behaves the same way. On HTTP, the error replaces that statement's rows.
• Reverted parameters.
  • Parameters that the commit reverts (a SET LOCAL earlier in the group) are now reported with the statement: HTTP parameters, WebSocket ParameterStatus.
  • HTTP results with rows do not report them yet (TODO in SqlResult::rows).
• Latency.
  • On WebSocket, the CommandComplete of a group's last statement now arrives after the commit.
  • ReadyForQuery already waited for the commit, so the request's total latency is unchanged.
• pgwire is unchanged. It buffers CommandComplete and flushes it after the commit. It still sends a commit error after the success. A TODO at query() records the PostgreSQL order.
• Docs.
  • http-api.md and websocket-api.md state when the result of an implicit transaction's last statement is sent, and that a commit error replaces it.
  • Neither page claims any longer that Extended mode does not eagerly commit DML.
  • A test-only failpoint comment in the adapter no longer names its single user.

User-visible effect: the HTTP and WebSocket SQL APIs no longer report a write as successful when its transaction fails to commit. The commit error becomes that statement's result, and the request stops.

Verification

• test_http_ws_implicit_commit_failure (new, src/environmentd/tests/server.rs) makes the commit fail deterministically, without sleeps:
  • A failpoint callback parks the INSERT after it packs its rows.
  • An ALTER TABLE ... ADD COLUMN runs, and the resumed INSERT's commit fails with 40001.
  • It covers Extended requests and Simple requests that end in a SELECT, over HTTP and WebSocket, and checks that no rows landed.
  • Without the fix it fails with the three-result HTTP response above.
  • The test runs with frontend read-then-write sequencing off, because the failpoint lives in the coordinator's constant-INSERT path. The fix itself is in the HTTP layer and applies to writes staged by either path.

Alternatives

• Keep success-then-error, but send both only after the commit.
  • This is the order of PostgreSQL's extended protocol at Sync.
  • Rejected: the extra result breaks clients that pair results with queries by index, and the docs promise one value per committed statement.

🤖 Generated with Claude Code