public inbox for [email protected]
help / color / mirror / Atom feed[PATCH] Add prepared_orphaned_transaction_timeout GUC
4+ messages / 3 participants
[nested] [flat]
* [PATCH] Add prepared_orphaned_transaction_timeout GUC
@ 2026-03-23 11:47 Nikhil Chawla <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Nikhil Chawla @ 2026-03-23 11:47 UTC (permalink / raw)
To: [email protected]
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:
[application/octet-stream] 0001-Add-prepared_orphaned_transaction_timeout-GUC.patch (21.0K, 3-0001-Add-prepared_orphaned_transaction_timeout-GUC.patch)
download | inline diff:
From bef6fbe5c2a59e5e014c2f6f0f5f651900d1457e Mon Sep 17 00:00:00 2001
From: Nikhil Chawla <[email protected]>
Date: Mon, 23 Mar 2026 17:15:00 +0530
Subject: [PATCH] Add prepared_orphaned_transaction_timeout GUC
This adds a new GUC parameter that automatically rolls back prepared
transactions that have remained unresolved beyond the configured
timeout. The cleanup is performed by the checkpointer process.
This is useful for preventing orphaned two-phase transactions from
holding locks and preventing VACUUM from reclaiming dead tuples
indefinitely.
The timeout is disabled by default (0) and can be changed with a
configuration reload (SIGHUP).
---
doc/src/sgml/config.sgml | 35 +++
src/backend/access/transam/twophase.c | 252 ++++++++++++++++++
src/backend/postmaster/checkpointer.c | 7 +
src/backend/utils/misc/guc_parameters.dat | 10 +
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/include/access/twophase.h | 5 +-
src/test/modules/test_misc/meson.build | 1 +
.../t/011_prepared_orphaned_timeout.pl | 203 ++++++++++++++
8 files changed, 513 insertions(+), 1 deletion(-)
create mode 100644 src/test/modules/test_misc/t/011_prepared_orphaned_timeout.pl
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 8cdd826fbd3..2938e91ce1c 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -10467,6 +10467,41 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
</listitem>
</varlistentry>
+ <varlistentry id="guc-prepared-orphaned-transaction-timeout" xreflabel="prepared_orphaned_transaction_timeout">
+ <term><varname>prepared_orphaned_transaction_timeout</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>prepared_orphaned_transaction_timeout</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Automatically roll back any prepared transaction that has remained
+ in the prepared state for longer than the specified amount of time.
+ If this value is specified without units, it is taken as milliseconds.
+ A value of zero (the default) disables the timeout.
+ </para>
+
+ <para>
+ Prepared transactions (from <command>PREPARE TRANSACTION</command>)
+ persist even after the originating session disconnects, and hold
+ locks and prevent vacuum cleanup of recently-dead tuples. If the
+ application that issued the <command>PREPARE TRANSACTION</command>
+ fails to follow up with <command>COMMIT PREPARED</command> or
+ <command>ROLLBACK PREPARED</command>, these orphaned prepared
+ transactions can accumulate and impact database performance.
+ This setting provides an automatic safety net to clean up such
+ orphaned transactions.
+ </para>
+
+ <para>
+ The cleanup is performed by the checkpointer process, which
+ periodically scans for prepared transactions exceeding the timeout
+ and rolls them back. A log message is emitted for each transaction
+ that is automatically rolled back.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-bytea-output" xreflabel="bytea_output">
<term><varname>bytea_output</varname> (<type>enum</type>)
<indexterm>
diff --git a/src/backend/access/transam/twophase.c b/src/backend/access/transam/twophase.c
index d468c9774b3..9113dbddf06 100644
--- a/src/backend/access/transam/twophase.c
+++ b/src/backend/access/transam/twophase.c
@@ -116,6 +116,9 @@
/* GUC variable, can't be changed after startup */
int max_prepared_xacts = 0;
+/* GUC variable for orphaned prepared transaction timeout (in ms, 0 = disabled) */
+int prepared_orphaned_transaction_timeout = 0;
+
/*
* This struct describes one global transaction that is in prepared state
* or attempting to become prepared.
@@ -2873,3 +2876,252 @@ TwoPhaseGetOldestXidInCommit(void)
return oldestRunningXid;
}
+
+/*
+ * LockGXactForCleanup
+ * Locate the prepared transaction by GID and mark it busy for cleanup.
+ *
+ * This is similar to LockGXact but does not check database identity or
+ * ownership, since it is used by the background cleanup of orphaned prepared
+ * transactions which operates as a superuser-like facility.
+ *
+ * Returns the GlobalTransaction on success, or NULL if the GID is not found
+ * or is already locked by another backend.
+ */
+static GlobalTransaction
+LockGXactForCleanup(const char *gid)
+{
+ int i;
+
+ /* on first call, register the exit hook */
+ if (!twophaseExitRegistered)
+ {
+ before_shmem_exit(AtProcExit_Twophase, 0);
+ twophaseExitRegistered = true;
+ }
+
+ LWLockAcquire(TwoPhaseStateLock, LW_EXCLUSIVE);
+
+ for (i = 0; i < TwoPhaseState->numPrepXacts; i++)
+ {
+ GlobalTransaction gxact = TwoPhaseState->prepXacts[i];
+
+ /* Ignore not-yet-valid GIDs */
+ if (!gxact->valid)
+ continue;
+ if (strcmp(gxact->gid, gid) != 0)
+ continue;
+
+ /* Already locked by another backend? Skip it. */
+ if (gxact->locking_backend != INVALID_PROC_NUMBER)
+ {
+ LWLockRelease(TwoPhaseStateLock);
+ return NULL;
+ }
+
+ /* OK for us to lock it */
+ gxact->locking_backend = MyProcNumber;
+ MyLockedGxact = gxact;
+
+ LWLockRelease(TwoPhaseStateLock);
+
+ return gxact;
+ }
+
+ LWLockRelease(TwoPhaseStateLock);
+
+ return NULL;
+}
+
+/*
+ * CleanupOrphanedPreparedTransactions
+ * Roll back prepared transactions that have exceeded the
+ * prepared_orphaned_transaction_timeout.
+ *
+ * This is called from the checkpointer's main loop to periodically scan
+ * for prepared transactions that have been in the prepared state for
+ * longer than the configured timeout. Such transactions are considered
+ * orphaned and are automatically rolled back.
+ *
+ * The function first collects GIDs of candidate transactions while holding
+ * TwoPhaseStateLock in shared mode, then processes each one individually.
+ */
+void
+CleanupOrphanedPreparedTransactions(void)
+{
+ int timeout_ms = prepared_orphaned_transaction_timeout;
+ TimestampTz now;
+ int num_orphaned = 0;
+ char **orphaned_gids = NULL;
+ int i;
+
+ /* Quick exit if the feature is disabled or no prepared xacts configured */
+ if (timeout_ms <= 0 || max_prepared_xacts == 0)
+ return;
+
+ now = GetCurrentTimestamp();
+
+ /*
+ * Phase 1: Collect GIDs of prepared transactions that have exceeded the
+ * timeout. We only hold the lock in shared mode for the scan.
+ */
+ LWLockAcquire(TwoPhaseStateLock, LW_SHARED);
+
+ if (TwoPhaseState->numPrepXacts > 0)
+ {
+ orphaned_gids = (char **) palloc(TwoPhaseState->numPrepXacts * sizeof(char *));
+
+ for (i = 0; i < TwoPhaseState->numPrepXacts; i++)
+ {
+ GlobalTransaction gxact = TwoPhaseState->prepXacts[i];
+
+ if (!gxact->valid)
+ continue;
+
+ /* Skip transactions currently being worked on */
+ if (gxact->locking_backend != INVALID_PROC_NUMBER)
+ continue;
+
+ /* Check if this transaction has exceeded the timeout */
+ if (TimestampDifferenceExceeds(gxact->prepared_at, now, timeout_ms))
+ {
+ orphaned_gids[num_orphaned] = pstrdup(gxact->gid);
+ num_orphaned++;
+ }
+ }
+ }
+
+ LWLockRelease(TwoPhaseStateLock);
+
+ /*
+ * Phase 2: Roll back each orphaned prepared transaction. We do this
+ * outside the lock to avoid holding TwoPhaseStateLock for too long.
+ *
+ * We use LockGXactForCleanup which does not enforce database or owner
+ * checks, since the checkpointer process is not connected to any
+ * particular database.
+ */
+ for (i = 0; i < num_orphaned; i++)
+ {
+ GlobalTransaction gxact;
+ PGPROC *proc;
+ FullTransactionId fxid;
+ TransactionId xid;
+ bool ondisk;
+ char *buf;
+ char *bufptr;
+ TwoPhaseFileHeader *hdr;
+ TransactionId latestXid;
+ TransactionId *children;
+ RelFileLocator *abortrels;
+ int ndelrels;
+ xl_xact_stats_item *abortstats;
+
+ ereport(LOG,
+ (errmsg("rolling back orphaned prepared transaction \"%s\"",
+ orphaned_gids[i]),
+ errdetail("This prepared transaction has exceeded the prepared_orphaned_transaction_timeout of %d ms.",
+ timeout_ms)));
+
+ /* Try to lock the gxact; skip if someone else got it first */
+ gxact = LockGXactForCleanup(orphaned_gids[i]);
+ if (gxact == NULL)
+ {
+ pfree(orphaned_gids[i]);
+ continue;
+ }
+
+ proc = GetPGProcByNumber(gxact->pgprocno);
+ fxid = gxact->fxid;
+ xid = XidFromFullTransactionId(fxid);
+
+ /*
+ * Read and validate 2PC state data.
+ */
+ if (gxact->ondisk)
+ buf = ReadTwoPhaseFile(fxid, false);
+ else
+ XlogReadTwoPhaseData(gxact->prepare_start_lsn, &buf, NULL);
+
+ /*
+ * Disassemble the header area
+ */
+ hdr = (TwoPhaseFileHeader *) buf;
+ Assert(TransactionIdEquals(hdr->xid, xid));
+ bufptr = buf + MAXALIGN(sizeof(TwoPhaseFileHeader));
+ bufptr += MAXALIGN(hdr->gidlen);
+ children = (TransactionId *) bufptr;
+ bufptr += MAXALIGN(hdr->nsubxacts * sizeof(TransactionId));
+ /* skip commitrels */
+ bufptr += MAXALIGN(hdr->ncommitrels * sizeof(RelFileLocator));
+ abortrels = (RelFileLocator *) bufptr;
+ bufptr += MAXALIGN(hdr->nabortrels * sizeof(RelFileLocator));
+ /* skip commitstats */
+ bufptr += MAXALIGN(hdr->ncommitstats * sizeof(xl_xact_stats_item));
+ abortstats = (xl_xact_stats_item *) bufptr;
+ bufptr += MAXALIGN(hdr->nabortstats * sizeof(xl_xact_stats_item));
+ /* skip invalmsgs */
+ bufptr += MAXALIGN(hdr->ninvalmsgs * sizeof(SharedInvalidationMessage));
+
+ /* compute latestXid among all children */
+ latestXid = TransactionIdLatest(xid, hdr->nsubxacts, children);
+
+ /* Prevent cancel/die interrupt while cleaning up */
+ HOLD_INTERRUPTS();
+
+ /* Record the abort in WAL and mark transaction as aborted in pg_xact */
+ RecordTransactionAbortPrepared(xid,
+ hdr->nsubxacts, children,
+ hdr->nabortrels, abortrels,
+ hdr->nabortstats,
+ abortstats,
+ orphaned_gids[i]);
+
+ ProcArrayRemove(proc, latestXid);
+
+ /*
+ * Mark the gxact invalid so no one else will try to commit/rollback.
+ */
+ gxact->valid = false;
+
+ /* Drop files that were supposed to be dropped on abort */
+ ndelrels = hdr->nabortrels;
+ DropRelationFiles(abortrels, ndelrels, false);
+
+ pgstat_execute_transactional_drops(hdr->nabortstats, abortstats, false);
+
+ /*
+ * Acquire the two-phase lock for callbacks and cleanup.
+ */
+ LWLockAcquire(TwoPhaseStateLock, LW_EXCLUSIVE);
+
+ /* Run post-abort callbacks */
+ ProcessRecords(bufptr, fxid, twophase_postabort_callbacks);
+
+ PredicateLockTwoPhaseFinish(fxid, false);
+
+ ondisk = gxact->ondisk;
+
+ /* Clear shared memory state */
+ RemoveGXact(gxact);
+
+ LWLockRelease(TwoPhaseStateLock);
+
+ /* Count the prepared xact as aborted */
+ AtEOXact_PgStat(false, false);
+
+ /* Remove on-disk state file if any */
+ if (ondisk)
+ RemoveTwoPhaseFile(fxid, true);
+
+ MyLockedGxact = NULL;
+
+ RESUME_INTERRUPTS();
+
+ pfree(buf);
+ pfree(orphaned_gids[i]);
+ }
+
+ if (orphaned_gids)
+ pfree(orphaned_gids);
+}
diff --git a/src/backend/postmaster/checkpointer.c b/src/backend/postmaster/checkpointer.c
index 3c982c6ffac..9139d64b3c5 100644
--- a/src/backend/postmaster/checkpointer.c
+++ b/src/backend/postmaster/checkpointer.c
@@ -39,6 +39,7 @@
#include <sys/time.h>
#include <time.h>
+#include "access/twophase.h"
#include "access/xlog.h"
#include "access/xlog_internal.h"
#include "access/xlogrecovery.h"
@@ -568,6 +569,12 @@ CheckpointerMain(const void *startup_data, size_t startup_data_len)
/* Check for archive_timeout and switch xlog files if necessary. */
CheckArchiveTimeout();
+ /*
+ * Clean up orphaned prepared transactions that have exceeded the
+ * prepared_orphaned_transaction_timeout.
+ */
+ CleanupOrphanedPreparedTransactions();
+
/* Report pending statistics to the cumulative stats system */
pgstat_report_checkpointer();
pgstat_report_wal(true);
diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat
index 0c9854ad8fc..225130b2595 100644
--- a/src/backend/utils/misc/guc_parameters.dat
+++ b/src/backend/utils/misc/guc_parameters.dat
@@ -2306,6 +2306,16 @@
max => '60',
},
+{ name => 'prepared_orphaned_transaction_timeout', type => 'int', context => 'PGC_SIGHUP', group => 'CLIENT_CONN_STATEMENT',
+ short_desc => 'Sets the maximum time that a prepared transaction can remain unresolved before it is automatically rolled back.',
+ long_desc => 'A value of 0 (the default) disables the timeout.',
+ flags => 'GUC_UNIT_MS',
+ variable => 'prepared_orphaned_transaction_timeout',
+ boot_val => '0',
+ min => '0',
+ max => 'INT_MAX',
+},
+
{ name => 'primary_conninfo', type => 'string', context => 'PGC_SIGHUP', group => 'REPLICATION_STANDBY',
short_desc => 'Sets the connection string to be used to connect to the sending server.',
flags => 'GUC_SUPERUSER_ONLY',
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index e4abe6c0077..25469078ee0 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -144,6 +144,7 @@
# (change requires restart)
# Caution: it is not advisable to set max_prepared_transactions nonzero unless
# you actively intend to use prepared transactions.
+#prepared_orphaned_transaction_timeout = 0 # in milliseconds, 0 is disabled
#work_mem = 4MB # min 64kB
#hash_mem_multiplier = 2.0 # 1-1000.0 multiplier on hash table work_mem
#maintenance_work_mem = 64MB # min 64kB
diff --git a/src/include/access/twophase.h b/src/include/access/twophase.h
index e312514ba87..b660aedcaff 100644
--- a/src/include/access/twophase.h
+++ b/src/include/access/twophase.h
@@ -25,8 +25,9 @@
*/
typedef struct GlobalTransactionData *GlobalTransaction;
-/* GUC variable */
+/* GUC variables */
extern PGDLLIMPORT int max_prepared_xacts;
+extern PGDLLIMPORT int prepared_orphaned_transaction_timeout;
extern Size TwoPhaseShmemSize(void);
extern void TwoPhaseShmemInit(void);
@@ -70,4 +71,6 @@ extern bool LookupGXactBySubid(Oid subid);
extern TransactionId TwoPhaseGetOldestXidInCommit(void);
+extern void CleanupOrphanedPreparedTransactions(void);
+
#endif /* TWOPHASE_H */
diff --git a/src/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build
index 6e8db1621a7..8f71efcfe69 100644
--- a/src/test/modules/test_misc/meson.build
+++ b/src/test/modules/test_misc/meson.build
@@ -19,6 +19,7 @@ tests += {
't/008_replslot_single_user.pl',
't/009_log_temp_files.pl',
't/010_index_concurrently_upsert.pl',
+ 't/011_prepared_orphaned_timeout.pl',
],
# The injection points are cluster-wide, so disable installcheck
'runningcheck': false,
diff --git a/src/test/modules/test_misc/t/011_prepared_orphaned_timeout.pl b/src/test/modules/test_misc/t/011_prepared_orphaned_timeout.pl
new file mode 100644
index 00000000000..a47c0595f7e
--- /dev/null
+++ b/src/test/modules/test_misc/t/011_prepared_orphaned_timeout.pl
@@ -0,0 +1,203 @@
+
+# Copyright (c) 2026, PostgreSQL Global Development Group
+
+# Test for prepared_orphaned_transaction_timeout GUC.
+# Verifies that orphaned prepared transactions are automatically
+# rolled back by the checkpointer when they exceed the configured timeout.
+
+use strict;
+use warnings FATAL => 'all';
+
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+#
+# Test 1: Basic orphaned prepared transaction cleanup
+#
+# Set up a node with prepared transactions enabled and a short timeout.
+my $node = PostgreSQL::Test::Cluster->new('primary');
+$node->init;
+$node->append_conf(
+ 'postgresql.conf', qq(
+ max_prepared_transactions = 5
+ prepared_orphaned_transaction_timeout = '2s'
+ log_min_messages = log
+));
+$node->start;
+
+# Create a test table and prepare a transaction.
+$node->safe_psql('postgres', 'CREATE TABLE t_orphan_test (id int, msg text)');
+$node->safe_psql(
+ 'postgres', "
+ BEGIN;
+ INSERT INTO t_orphan_test VALUES (1, 'orphaned');
+ PREPARE TRANSACTION 'orphan_xact_1';
+");
+
+# Verify the prepared transaction exists.
+my $result = $node->safe_psql('postgres',
+ "SELECT count(*) FROM pg_prepared_xacts WHERE gid = 'orphan_xact_1'");
+is($result, '1', 'prepared transaction orphan_xact_1 exists');
+
+# Record log position before the timeout fires.
+my $log_offset = -s $node->logfile;
+
+# Wait for the timeout to elapse, then trigger the checkpointer loop
+# by issuing a CHECKPOINT. The checkpointer will scan for orphaned
+# prepared transactions as part of its main loop iteration.
+sleep(3);
+$node->safe_psql('postgres', 'CHECKPOINT');
+
+# Give the checkpointer a moment to process the cleanup.
+sleep(1);
+
+# Verify the prepared transaction was rolled back.
+ok( $node->poll_query_until(
+ 'postgres',
+ "SELECT count(*) FROM pg_prepared_xacts WHERE gid = 'orphan_xact_1'",
+ '0'),
+ 'orphaned prepared transaction was rolled back');
+
+# Verify that the inserted data was NOT committed (rolled back).
+$result = $node->safe_psql('postgres',
+ "SELECT count(*) FROM t_orphan_test WHERE id = 1");
+is($result, '0', 'orphaned transaction data was not committed');
+
+# Verify the rollback was logged.
+$node->wait_for_log(
+ qr/rolling back orphaned prepared transaction "orphan_xact_1"/,
+ $log_offset);
+ok(1, 'orphaned transaction rollback was logged');
+
+
+#
+# Test 2: Prepared transaction committed before timeout is not affected
+#
+$node->safe_psql(
+ 'postgres', "
+ BEGIN;
+ INSERT INTO t_orphan_test VALUES (2, 'committed_in_time');
+ PREPARE TRANSACTION 'timely_xact';
+");
+
+# Commit the prepared transaction immediately (before timeout).
+$node->safe_psql('postgres', "COMMIT PREPARED 'timely_xact'");
+
+# Verify the data was committed.
+$result = $node->safe_psql('postgres',
+ "SELECT count(*) FROM t_orphan_test WHERE id = 2");
+is($result, '1',
+ 'prepared transaction committed before timeout preserved data');
+
+
+#
+# Test 3: Timeout of 0 disables the feature
+#
+$node->safe_psql('postgres',
+ "ALTER SYSTEM SET prepared_orphaned_transaction_timeout = '0'");
+$node->safe_psql('postgres', "SELECT pg_reload_conf()");
+
+$node->safe_psql(
+ 'postgres', "
+ BEGIN;
+ INSERT INTO t_orphan_test VALUES (3, 'should_persist');
+ PREPARE TRANSACTION 'persist_xact';
+");
+
+# Wait and trigger checkpointer - the transaction should NOT be rolled back.
+sleep(3);
+$node->safe_psql('postgres', 'CHECKPOINT');
+sleep(1);
+
+$result = $node->safe_psql('postgres',
+ "SELECT count(*) FROM pg_prepared_xacts WHERE gid = 'persist_xact'");
+is($result, '1',
+ 'prepared transaction not rolled back when timeout is disabled');
+
+# Clean up.
+$node->safe_psql('postgres', "ROLLBACK PREPARED 'persist_xact'");
+
+
+#
+# Test 4: Multiple orphaned transactions are all cleaned up
+#
+$node->safe_psql('postgres',
+ "ALTER SYSTEM SET prepared_orphaned_transaction_timeout = '2s'");
+$node->safe_psql('postgres', "SELECT pg_reload_conf()");
+
+$node->safe_psql(
+ 'postgres', "
+ BEGIN;
+ INSERT INTO t_orphan_test VALUES (10, 'multi_1');
+ PREPARE TRANSACTION 'multi_orphan_1';
+");
+
+$node->safe_psql(
+ 'postgres', "
+ BEGIN;
+ INSERT INTO t_orphan_test VALUES (11, 'multi_2');
+ PREPARE TRANSACTION 'multi_orphan_2';
+");
+
+$node->safe_psql(
+ 'postgres', "
+ BEGIN;
+ INSERT INTO t_orphan_test VALUES (12, 'multi_3');
+ PREPARE TRANSACTION 'multi_orphan_3';
+");
+
+$result = $node->safe_psql('postgres',
+ "SELECT count(*) FROM pg_prepared_xacts");
+is($result, '3', 'three prepared transactions exist');
+
+# Wait for timeout, trigger checkpointer.
+sleep(3);
+$node->safe_psql('postgres', 'CHECKPOINT');
+
+# All three should be cleaned up.
+ok( $node->poll_query_until(
+ 'postgres', "SELECT count(*) FROM pg_prepared_xacts", '0'),
+ 'all orphaned prepared transactions were rolled back');
+
+
+#
+# Test 5: Timeout change via reload takes effect
+#
+# Set a very long timeout so nothing gets cleaned.
+$node->safe_psql('postgres',
+ "ALTER SYSTEM SET prepared_orphaned_transaction_timeout = '1h'");
+$node->safe_psql('postgres', "SELECT pg_reload_conf()");
+
+$node->safe_psql(
+ 'postgres', "
+ BEGIN;
+ INSERT INTO t_orphan_test VALUES (20, 'reload_test');
+ PREPARE TRANSACTION 'reload_xact';
+");
+
+sleep(3);
+$node->safe_psql('postgres', 'CHECKPOINT');
+sleep(1);
+
+$result = $node->safe_psql('postgres',
+ "SELECT count(*) FROM pg_prepared_xacts WHERE gid = 'reload_xact'");
+is($result, '1',
+ 'prepared transaction persists with long timeout');
+
+# Now lower the timeout and reload.
+$node->safe_psql('postgres',
+ "ALTER SYSTEM SET prepared_orphaned_transaction_timeout = '1s'");
+$node->safe_psql('postgres', "SELECT pg_reload_conf()");
+
+sleep(2);
+$node->safe_psql('postgres', 'CHECKPOINT');
+
+ok( $node->poll_query_until(
+ 'postgres',
+ "SELECT count(*) FROM pg_prepared_xacts WHERE gid = 'reload_xact'",
+ '0'),
+ 'prepared transaction cleaned up after lowering timeout via reload');
+
+$node->stop;
+done_testing();
--
2.53.0.windows.2
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [PATCH] Add prepared_orphaned_transaction_timeout GUC
@ 2026-03-26 07:32 Nikhil Chawla <[email protected]>
parent: Nikhil Chawla <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Nikhil Chawla @ 2026-03-26 07:32 UTC (permalink / raw)
To: Andrey Borodin <[email protected]>; +Cc: [email protected]
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 <[email protected]> wrote:
>
>
> > On 23 Mar 2026, at 16:47, Nikhil Chawla <[email protected]>
> 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;
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [PATCH] Add prepared_orphaned_transaction_timeout GUC
@ 2026-03-30 10:44 Artur Zakirov <[email protected]>
parent: Nikhil Chawla <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Artur Zakirov @ 2026-03-30 10:44 UTC (permalink / raw)
To: Nikhil Chawla <[email protected]>; +Cc: Andrey Borodin <[email protected]>; [email protected]
>> 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [PATCH] Add prepared_orphaned_transaction_timeout GUC
@ 2026-04-02 12:34 Greg Sabino Mullane <[email protected]>
parent: Artur Zakirov <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Greg Sabino Mullane @ 2026-04-02 12:34 UTC (permalink / raw)
To: [email protected]; +Cc: Nikhil Chawla <[email protected]>; Andrey Borodin <[email protected]>; [email protected]
On Mon, Mar 30, 2026 at 6:44 AM Artur Zakirov <[email protected]> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-04-02 12:34 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-03-23 11:47 [PATCH] Add prepared_orphaned_transaction_timeout GUC Nikhil Chawla <[email protected]>
2026-03-26 07:32 ` Nikhil Chawla <[email protected]>
2026-03-30 10:44 ` Artur Zakirov <[email protected]>
2026-04-02 12:34 ` Greg Sabino Mullane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox