[PATCH] Add prepared_orphaned_transaction_timeout GUC
Hi,
Orphaned prepared transactions cause escalating harm the longer they
persist:
1. *Lock retention* : All locks (row-level, table-level, advisory)
acquired during the transaction are held indefinitely, blocking concurrent
DML and DDL
2. *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
3. *Transaction ID wraparound risk *: In extreme cases, the frozen XID
horizon cannot advance, eventually threatening XID wraparound shutdown
4. *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"
*Safety Properties*
1. Timeout = 0 (default): Feature is completely disabled, no behavior
change from default PostgreSQL
2. No false positives on active transactions: The check uses
prepared_at, which is set once at PREPARE TRANSACTION time. A transaction
that is actively being committed/rolled back by a client will either
complete before the timeout or be skipped by LockGXactForCleanup (which
returns NULL if the gxact is already locked by another backend)
3. Crash-safe: If the checkpointer crashes during cleanup, the prepared
transaction's WAL state is unchanged, it remains prepared and will be
cleaned up after recovery
4. Idempotent: If the GID was already resolved between Phase 1 and Phase
2, LockGXactForCleanup returns NULL and the cleanup is silently skipped
--
Regards,
Nikhil Chawla
Twitter <https://twitter.com/chawlanikhil24> | LinkedIn
<http://linkedin.com/in/chawlanikhil24>
Attachments:
0001-Add-prepared_orphaned_transaction_timeout-GUC.patchapplication/octet-stream; name=0001-Add-prepared_orphaned_transaction_timeout-GUC.patchDownload+513-2
On 23 Mar 2026, at 16:47, Nikhil Chawla <chawlanikhil24@gmail.com> wrote:
idle_in_transaction_session_timeout, idle_session_timeout, statement_timeout, but no equivalent for prepared transactions
During implementation of the transaction_timeout we briefly considered prepared xacts, but decided that it's a footgun.
DBA can easily do the same with a cron job, but in case of prepared transactions monitoring is crucial. Prepared xacts are used to coordinate commit in many durable systems and orphan prepared xact is evidence of serious malfunction.
Silent rollback in any scenario I can imagine is a disaster.
Best regards, Andrey Borodin.
Hi Andrey,
My purview is absolutely influenced from "long running transactions" which
are essentially killed by pg itself through idle_session_timeout. This also
happens when the user sets an acceptable value to this timeout and the user
is aware that transactions can be killed if threshold is exceeded, which
further can be confirmed through logs.
The same analogy is being applied with orphaned prepared transactions,
which may/may not complete ever , but going to hinder autovacuum,
increasing bloat. When user sets the value, user will be aware that the
prepared transactions can disappear automatically.
On Wed, Mar 25, 2026 at 2:36 PM Andrey Borodin <x4mmm@yandex-team.ru> wrote:
On 23 Mar 2026, at 16:47, Nikhil Chawla <chawlanikhil24@gmail.com>
wrote:
idle_in_transaction_session_timeout, idle_session_timeout,
statement_timeout, but no equivalent for prepared transactions
During implementation of the transaction_timeout we briefly considered
prepared xacts, but decided that it's a footgun.DBA can easily do the same with a cron job, but in case of prepared
transactions monitoring is crucial. Prepared xacts are used to coordinate
commit in many durable systems and orphan prepared xact is evidence of
serious malfunction.Silent rollback in any scenario I can imagine is a disaster.
Best regards, Andrey Borodin.
--
Regards,
Nikhil Chawla
Twitter <https://twitter.com/chawlanikhil24> | LinkedIn
<http://linkedin.com/in/chawlanikhil24>
Silent rollback in any scenario I can imagine is a disaster.
...
The same analogy is being applied with orphaned prepared transactions, which may/may not complete ever , but going to hinder autovacuum, increasing bloat. When user sets the value, user will be aware that the prepared transactions can disappear automatically.
I agree with Andrey. And I think we cannot use the same analogy of
"long running transactions" to prepared transactions. Prepared
transactions are part of 2PC protocol. And this GUC would break 2PC
protocol, which is after all distributed participants successfully
executed PREPARE you can successfully execute COMMIT, if a database is
healthy.
Another point is that rollback is not always a proper action on a
dangling prepared transaction. If you move data from Instance1 to
Instance2 and a prepared transaction on Instance1 was committed, but
you have a dangling prepared transaction on Instance2 for some reason,
then you want to COMMIT it on Instance2, not to roll back. And vice
versa, if the prepared transaction on Instance1 was rolled back, then
you want to roll it back on Instance2. What to do with the prepared
transaction should be decided by a transaction manager.
From the documentation [1]:
PREPARE TRANSACTION is not intended for use in applications or interactive sessions. Its purpose is to allow an external transaction manager to perform atomic global transactions across multiple databases or other transactional resources.
It seems we don't have ways to enforce this rule, and a user doesn't
need to have advanced privileges to run PREPARE TRANSACTION. Correct
me if I'm wrong. What if instead of adding a GUC we would add a new
role, only this role (and superuser) will be able to run PREPARE
TRANSACTION.
1 - https://www.postgresql.org/docs/current/sql-prepare-transaction.html
--
Kind regards,
Artur
On Mon, Mar 30, 2026 at 6:44 AM Artur Zakirov <zaartur@gmail.com> wrote:
It seems we don't have ways to enforce this rule, and a user doesn't need
to have advanced privileges to run PREPARE TRANSACTION. Correct me if I'm
wrong. What if instead of adding a GUC we would add a new role, only this
role (and superuser) will be able to run PREPARE TRANSACTION.
Well, it is certainly a better solution than a global timeout. But there
are a ton of other ways that a user can mess things up. 2PC is very
powerful, and very hard to get implement. It is disabled by default for
good reason. Someone enabling it needs to have all their ducks in a row,
and should have their own ways to monitor, and handle, errant transactions.
So a strong -1 from me on the timeout, and a weak -1 on a GUC/permission
solution (which could get hashed out more, perhaps).
Cheers,
Greg