Hi,
Orphaned prepared transactions cause escalating harm the longer they persist:
- Lock retention : All locks (row-level, table-level, advisory) acquired during the transaction are held indefinitely, blocking concurrent DML and DDL
- VACUUM blockage : The prepared transaction's XID becomes the oldest running transaction, preventing VACUUM from freezing tuples or reclaiming dead rows across the entire cluster, leading to table and index bloat
- Transaction ID wraparound risk : In extreme cases, the frozen XID horizon cannot advance, eventually threatening XID wraparound shutdown
- Resource consumption : Shared memory slots (max_prepared_transactions) remain occupied; the WAL records for the prepared state persist
Today, the only remediation is manual intervention: a DBA must discover the orphan (via pg_prepared_xacts), determine it's truly abandoned, and issue ROLLBACK PREPARED. PostgreSQL already has timeout-based safety nets for other "stuck" session states such as, idle_in_transaction_session_timeout, idle_session_timeout, statement_timeout, but no equivalent for prepared transactions. This patch fills that gap.
How it works ?
CleanupOrphanedPreparedTransactions():
Phase 1 — Collect candidates (under TwoPhaseStateLock, shared mode):
for each GlobalTransactionData (gxact) in TwoPhaseState:
if gxact->valid AND
TimestampDifferenceExceeds(gxact->prepared_at, now, timeout):
save gxact->gid to candidate list
Phase 2 — Roll back each candidate (lock released):
for each saved GID:
lock = LockGXactForCleanup(gid)
if lock succeeded:
FinishPreparedTransaction(gid, isCommit=false)
log: "rolling back orphaned prepared transaction %s"
