[PATCH v1] Add automatic retry for autocommit statements on transient concurrency errors

Hi hackers,

Attached is a v1 patch adding optional server-side automatic retry for
single-statement implicit transactions (autocommit mode) that fail due to
transient concurrency errors such as serialization failures (40001) and
deadlocks (40P01).

1. Problem & Motivation
=======================

Under REPEATABLE READ and SERIALIZABLE isolation levels (or high-contention
READ COMMITTED workloads encountering deadlocks or DDL lock conflicts),
applications must implement retry loops around transactions that fail with
SQLSTATE 40001 (serialization_failure) or 40P01 (deadlock_detected).

While multi-statement explicit transactions (BEGIN ... COMMIT) inherently
require client-side retry logic because intermediate application state may
depend on earlier queries in the transaction, standalone autocommit
statements contain their entire specification within a single wire-protocol
request. Retrying such statements directly inside the backend avoids a full
network round-trip, connection-pooler churn, and boilerplate retry wrappers
in simple clients, ORMs, and migration runners.

2. Design & Architecture
========================

The retry loop operates in `src/backend/tcop/postgres.c` around top-level
autocommit executions when `max_autocommit_retries > 0`:

a) Protocol Coverage:
- Simple Query Protocol (`exec_simple_query`):
Eligible when the query string parses into a single statement
(`list_length(parsetree_list) == 1`) and the backend is not initially in
an explicit transaction block (`!(xact_started && IsTransactionBlock())`).
On each retry attempt, the statement is re-parsed from the raw
`query_string` in a fresh `MessageContext` after aborting the failed
transaction.
- Extended Query Protocol (`exec_execute_message`):
Eligible when executing an uncompleted portal (`!portal->atStart` is
false, `max_rows <= 0` so no partial portal fetches occur), not inside an
explicit transaction block, with a single statement in `portal->stmts`.
Before execution begins, the bound `ParamListInfo` and portal context
(`stmt_text`, `prep_stmt_name`, `commandTag`, `cplan` or raw `stmts`) are
snapshot into a dedicated child memory context (`retry_cxt`) under
`TopMemoryContext`. If a retry is triggered after
`AbortCurrentTransaction()`
drops the failed unnamed portal, the portal is cleanly reconstructed and
re-defined from either the cached plan (`GetCachedPlan`) or a fresh copy
of the parse trees before re-running `PortalStart` and `PortalRun`.

b) Statement Classification & Eligible SQLSTATEs:
- Standard retryable statements (`AUTOCOMMIT_RETRY_DML`):
`SELECT`, `INSERT`, `UPDATE`, `DELETE`, `MERGE`, `VALUES`, `TABLE`, and
`CALL` (provided the procedure does not issue internal transaction-ending
`COMMIT`/`ROLLBACK` commands, which disqualify retry as soon as the
original `TransactionId` advances/resets).
Retried on:
* `ERRCODE_T_R_SERIALIZATION_FAILURE` (`40001`)
* `ERRCODE_DEADLOCK_DETECTED` (`40P01`)
- Rollback-safe DDL (`AUTOCOMMIT_RETRY_DDL_SAFE`):
When `enable_ddl_autocommit_retry = on`, rollback-safe transactional DDL
statements (`CREATE`, `ALTER`, `DROP`, `COMMENT`, `SECURITY LABEL`,
`GRANT`/`REVOKE`, `IMPORT FOREIGN SCHEMA`, `REFRESH MATERIALIZED VIEW`
non-concurrently, `REINDEX` non-concurrently, `CLUSTER`, etc.) are also
eligible for retry. Non-transactional or non-rollback-safe commands
(`VACUUM`, `CREATE/DROP DATABASE`, `CREATE/DROP TABLESPACE`,
`ALTER SYSTEM`,
`REINDEX CONCURRENTLY`, `CREATE/DROP INDEX CONCURRENTLY`, `REFRESH
MATERIALIZED VIEW CONCURRENTLY`) and transaction control commands are
explicitly excluded (`AUTOCOMMIT_RETRY_NONE`).
In addition to `40001` and `40P01`, rollback-safe DDL is also retried on:
* `ERRCODE_LOCK_NOT_AVAILABLE` (`55P03`)
* `ERRCODE_QUERY_CANCELED` (`57014`) when caused by `lock_timeout`
(explicitly excluding user `SIGINT` cancels and `statement_timeout`).

c) Speculative Wire-Protocol Output Buffering (`pqcomm.c`):
A `SELECT` or `INSERT ... RETURNING` statement may stream `RowDescription`
and partial `DataRow` messages into `PqSendBuffer` before encountering a
serialization failure or deadlock part-way through execution.
To prevent corrupting the frontend wire stream on retry:
- `pq_begin_retry_Holdoff()` pins all outgoing protocol bytes in
`PqSendBuffer` (expanding the buffer dynamically up to an 8 MB ceiling,
`PQ_RETRY_BUFFER_MAX = 8 * 1024 * 1024`, before falling back to flushing
to the socket).
- If the statement fails with a retryable error and the buffer has not
flushed (`!pq_retry_Holdoff_flushed()`), `pq_discard_retry_Holdoff()`
rewinds `PqSendPointer` to discard the speculative `RowDescription` /
`DataRow` bytes, and `FlushErrorState()` clears the pending error before
sleeping and retrying.
- If the output exceeded 8 MB and already flushed bytes to the wire, retry
is suppressed and the error is reported normally.

d) Backoff, Jitter, and `statement_timeout`:
- Between retry attempts, the backend aborts the failed transaction so locks
and snapshots are released, then sleeps using `WaitLatch(MyLatch,
WL_LATCH_SET | WL_TIMEOUT | WL_EXIT_ON_PM_DEATH, sleep_ms,
WAIT_EVENT_AUTOCOMMIT_RETRY_BACKOFF)`.
- The backoff uses exponential growth with full uniform jitter via
`pg_prng_uint64_range(&pg_global_prng_state, 0, cap_ms)`:
`cap_ms = min(autocommit_retry_backoff_max_ms,
autocommit_retry_backoff_base_ms * 2^(attempt - 1))`.
- `statement_timeout` is re-armed per attempt (`enable_statement_timeout()`).

3. New GUC Parameters
=====================

- `max_autocommit_retries` (integer, default `0`, range `0..100`,
`PGC_USERSET`):
Maximum number of retry attempts for autocommit statements failing with
transient concurrency errors (`0` disables automatic retry).
- `enable_ddl_autocommit_retry` (bool, default `false`, `PGC_USERSET`):
Enables automatic retry for rollback-safe DDL statements on `40001`, `40P01`,
`55P03` (`lock_not_available`), and `lock_timeout` (`57014`).
- `autocommit_retry_backoff_base_ms` (integer, default `5`, range
`0..10000`, `PGC_USERSET`):
Base delay in milliseconds for exponential backoff with jitter
between retries.
- `autocommit_retry_backoff_max_ms` (integer, default `1000`, range
`1..60000`, `PGC_USERSET`):
Maximum delay in milliseconds for exponential backoff between retries.
- `log_autocommit_retries` (bool, default `false`, `PGC_USERSET`):
Emits a `LOG` message for each automatic retry attempt including the attempt
number, `max_autocommit_retries`, SQLSTATE, sleep duration, and
statement text.

4. Verification & Testing
=========================

- Isolation test suite (`src/test/isolation/specs/autocommit-retry.spec`):
10 permutations covering:
* Read-write skew (`40001`) under `default_transaction_isolation =
'repeatable read'`
with `max_autocommit_retries = 0` (fails) vs `max_autocommit_retries = 3`
(transparently retries and commits updated balance).
* CTE (`WITH ... UPDATE`) two-session mutual deadlock (`40P01`) where the
deadlock victim automatically retries and succeeds.
* Rollback-safe DDL (`ALTER TABLE ... ADD COLUMN`) failing with `lock_timeout`
(`57014`) when blocked by an open transaction, then retrying after the
blocker releases the lock (`enable_ddl_autocommit_retry = on` vs `off`).
* `SELECT ... FOR SHARE` / `FOR UPDATE` conflict retry.
* Explicit `BEGIN ... COMMIT` transaction block verification (never
auto-retried).
* Multi-statement Simple Query (`SELECT 1; UPDATE ...`) verification (never
auto-retried).
* `CALL` procedure with internal `COMMIT` verification (never auto-retried
once internal transaction control occurs).
- Regression tests (`src/test/regress/sql/psql.sql`):
* Extended Query protocol (`\bind`) retry on `40001`.
* Speculative `DataRow` output buffer discard (`pq_discard_retry_Holdoff()`)
when a multi-row `SELECT` fails mid-stream with `40001` on the first
attempt and succeeds cleanly on the retry attempt without duplicate rows.

Base commit: e75fcc9867fdfe295957ad5c9a98cbe2e1b4c5c3
GitHub branch: https://github.com/postsql/postgres/tree/auto-retry-serialization-errors
CI run: https://github.com/postsql/postgres/actions/runs/39452018256

Regards,
Hannu Krosing