Hi, On Sat, Aug 15, 2026 at 10:35 PM Bharath Rupireddy <[email protected]> wrote: > > On Thu, Aug 13, 2026 at 9:40 PM Bertrand Drouvot > <[email protected]> wrote: > > > Yeah, I agree. Keeping the exemption for idle_replication_slot_timeout but > > removing it from CanInvalidateXidAgedSlot() makes sense to me. > > > Worth adding a test with an actual synced slot, including checking that its > > invalidation releases the feedback horizon on the primary? > > Yes, I will do so. I will turn either one of the following (maybe test > 2 for determinism) into a test by ensuring that the synced slot on the > standby gets XID-age invalidated while its corresponding failover slot > and physical slot on the primary don't get invalidated, and ensure > that vacuum on the system catalogs doesn't get blocked. > > 1/ Slot sync worker stuck or erroring: the synced slot's catalog_xmin > is old and gets sent onto the physical slot on the primary via HS > feedback. > 2/ Slot sync worker is off with rare/missing/forgotten manual syncs: > the synced slot's catalog_xmin is old and gets sent onto the physical > slot on the primary via HS feedback.
Added case 2 as a TAP test in the attached 0003 patch. On Tue, Aug 18, 2026 at 12:42 PM Bharath Rupireddy <[email protected]> wrote: > > On Mon, Aug 17, 2026 at 10:46 PM Amit Kapila <[email protected]> wrote: > > > > > I believe this can happen in practice (as explained above). IMHO, XID > > > age based slot invalidation must invalidate the synced slot on the > > > standby (just like other invalidations except the idle-timeout based) > > > if its catalog_xmin is aged and blocking vacuum on the primary. > > > > Fair enough, then let's invalidate synced slots as well on standby due > > to xid_age > > Thanks. I will post the new patches soon. Please find the attached v14 patches. 0003 now adds support for invalidating XID-aged synced replication slots on standbys. -- Bharath Rupireddy Amazon Web Services: https://aws.amazon.com
From 320edf8016797d7c3fd011eac7a6ec68ec1704b9 Mon Sep 17 00:00:00 2001 From: Bharath Rupireddy <[email protected]> Date: Mon, 10 Aug 2026 16:13:00 +0000 Subject: [PATCH v14 1/3] Invalidate XID-aged replication slots. An inactive or forgotten replication slot holds vacuum back from freezing XIDs and from pruning dead rows, through the xmin or catalog_xmin it retains. This can lead to table and index bloat and, left unchecked, eventually to transaction ID wraparound. Until now the only way to bound this was to notice the slot and drop it by hand. This commit adds a GUC, max_slot_xid_age, that invalidates a replication slot once the age of its xmin or catalog_xmin exceeds the configured number of transactions. A value of zero, the default, disables the feature. The invalidation check runs during checkpoints, and on a standby during the restartpoints that stand in for them. This is the same place the WAL and idle-timeout slot invalidations already run. It terminates the process that owns a slot still in use and waits for the slot to be released. Because checkpoints happen at their own interval, there can be lag between when a slot ages past the limit and when it is invalidated; a manual CHECKPOINT triggers it promptly. Slots on a standby that are being synced from the primary are exempt, since they do not perform logical decoding to produce changes. An upcoming commit adds a non-blocking invalidation path in vacuum, so that a vacuum held back by an aged slot can invalidate that slot itself and proceed to freeze XIDs and prune dead rows, without waiting for the next checkpoint. Author: Bharath Rupireddy <[email protected]> Reviewed-by: John Hsu <[email protected]> Reviewed-by: Masahiko Sawada <[email protected]> Reviewed-by: Hayato Kuroda <[email protected]> Reviewed-by: Satya Narlapuram <[email protected]> Discussion: https://www.postgresql.org/message-id/CALj2ACW4aUe-_uFQOjdWCEN-xXoLGhmvRFnL8SNw_TZ5nJe+aw@mail.gmail.com Discussion: https://www.postgresql.org/message-id/CALj2ACUmPbkcj4y4oeXvzUkBejG68QDtrFF7QHDC_qz2vQcTCg@mail.gmail.com Discussion: https://www.postgresql.org/message-id/CALj2ACVD0_DhCQ_QOAa7F=nFv8+ZGsHR8SbOc-FmuV8ZrV92HQ@mail.gmail.com --- doc/src/sgml/config.sgml | 57 ++++++++ doc/src/sgml/logical-replication.sgml | 4 +- doc/src/sgml/maintenance.sgml | 5 +- doc/src/sgml/system-views.sgml | 8 ++ src/backend/access/transam/xlog.c | 34 ++++- src/backend/replication/slot.c | 121 +++++++++++++++- src/backend/storage/ipc/standby.c | 3 +- src/backend/utils/misc/guc_parameters.dat | 8 ++ src/backend/utils/misc/postgresql.conf.sample | 1 + src/bin/pg_basebackup/pg_createsubscriber.c | 2 +- src/include/replication/slot.h | 8 +- src/test/recovery/t/019_replslot_limit.pl | 133 ++++++++++++++++++ 12 files changed, 368 insertions(+), 16 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 596fd45a3db..a5eb6b36146 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -5020,6 +5020,63 @@ HINT: If it is safe for all REPLICATION users to use this library as an output </listitem> </varlistentry> + <varlistentry id="guc-max-slot-xid-age" xreflabel="max_slot_xid_age"> + <term><varname>max_slot_xid_age</varname> (<type>integer</type>) + <indexterm> + <primary><varname>max_slot_xid_age</varname> configuration parameter</primary> + </indexterm> + </term> + <listitem> + <para> + Invalidate replication slots whose <structfield>xmin</structfield> or + <structfield>catalog_xmin</structfield> transaction age in the + <link linkend="view-pg-replication-slots">pg_replication_slots</link> + view has exceeded the age specified by this setting. + A value of zero (the default) disables this feature. Users can set + this value anywhere from zero to 2.1 billion transactions. This parameter + can only be set in the <filename>postgresql.conf</filename> file or on + the server command line. + </para> + + <para> + Slot invalidation due to this limit occurs during checkpoint. Because + checkpoints happen at their own interval, there can be some lag between + when a slot's <literal>xmin</literal> or <literal>catalog_xmin</literal> + age exceeds <varname>max_slot_xid_age</varname> and when the slot + invalidation is actually triggered. To avoid such lags, users can force + a checkpoint to promptly invalidate the slot. + </para> + + <para> + The current age of a slot's <literal>xmin</literal> and + <literal>catalog_xmin</literal> can be monitored by applying the + <function>age</function> function to the corresponding columns in the + <link linkend="view-pg-replication-slots">pg_replication_slots</link> + view. + </para> + + <para> + Inactive or forgotten replication slots can hold vacuum back from + freezing XIDs and from pruning dead rows. This can lead to table and + index bloat that holds disk space that vacuum would otherwise + reclaim, and eventually to transaction ID wraparound. Invalidating + such a slot removes one of these blockers, letting vacuum freeze XIDs + and reclaim disk space again. See <xref linkend="routine-vacuuming"/> + for more details. + </para> + + <para> + Note that this invalidation mechanism is not applicable for slots + on the standby server that are being synced from the primary server + (i.e., standby slots having + <link linkend="view-pg-replication-slots">pg_replication_slots</link>.<structfield>synced</structfield> + value <literal>true</literal>). Synced slots are always considered to + be inactive because they don't perform logical decoding to produce + changes. + </para> + </listitem> + </varlistentry> + <varlistentry id="guc-wal-sender-timeout" xreflabel="wal_sender_timeout"> <term><varname>wal_sender_timeout</varname> (<type>integer</type>) <indexterm> diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml index 3a61e2d6889..a2c772d478d 100644 --- a/doc/src/sgml/logical-replication.sgml +++ b/doc/src/sgml/logical-replication.sgml @@ -2699,7 +2699,9 @@ CONTEXT: processing remote data for replication origin "pg_16395" during "INSER <para> Logical replication slots are also affected by - <link linkend="guc-idle-replication-slot-timeout"><varname>idle_replication_slot_timeout</varname></link>. + <link linkend="guc-idle-replication-slot-timeout"><varname>idle_replication_slot_timeout</varname></link> + and + <link linkend="guc-max-slot-xid-age"><varname>max_slot_xid_age</varname></link>. </para> <para> diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml index 33ab4edf87c..0689fc7edf7 100644 --- a/doc/src/sgml/maintenance.sgml +++ b/doc/src/sgml/maintenance.sgml @@ -720,7 +720,10 @@ HINT: Execute a database-wide VACUUM in that database. is large. In many cases, such slots were created for replication to servers that no longer exist, or that have been down for a long time. If you drop a slot for a server that still exists and might still try to connect to that slot, that replica may - need to be rebuilt.</simpara> + need to be rebuilt. Setting <xref linkend="guc-max-slot-xid-age"/> makes the + server invalidate such slots automatically once their <literal>age(xmin)</literal> + or <literal>age(catalog_xmin)</literal> exceeds the configured limit, + preventing them from holding vacuum back indefinitely.</simpara> </listitem> <listitem> <simpara>Execute <command>VACUUM</command> in the target database. A database-wide diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml index 5ea19d68622..2b953ab37bd 100644 --- a/doc/src/sgml/system-views.sgml +++ b/doc/src/sgml/system-views.sgml @@ -3103,6 +3103,14 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx <xref linkend="guc-idle-replication-slot-timeout"/> duration. </para> </listitem> + <listitem> + <para> + <literal>xid_aged</literal> means that the slot's + <literal>xmin</literal> or <literal>catalog_xmin</literal> + has reached the transaction age specified by + <xref linkend="guc-max-slot-xid-age"/> parameter. + </para> + </listitem> </itemizedlist> </para></entry> </row> diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index de4c96e135f..cbd6ac4e0ed 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -7439,6 +7439,8 @@ CreateCheckPoint(int flags) VirtualTransactionId *vxids; int nvxids; int oldXLogAllowed = 0; + uint32 slotInvalidationCauses; + TransactionId slotXidLimit; /* * An end-of-recovery checkpoint is really a shutdown checkpoint, just @@ -7878,9 +7880,20 @@ CreateCheckPoint(int flags) */ XLByteToSeg(RedoRecPtr, _logSegNo, wal_segment_size); KeepLogSeg(recptr, &_logSegNo); - if (InvalidateObsoleteReplicationSlots(RS_INVAL_WAL_REMOVED | RS_INVAL_IDLE_TIMEOUT, + + slotInvalidationCauses = RS_INVAL_WAL_REMOVED | RS_INVAL_IDLE_TIMEOUT; + slotXidLimit = InvalidTransactionId; + if (max_slot_xid_age > 0) + { + slotInvalidationCauses |= RS_INVAL_XID_AGE; + slotXidLimit = TransactionIdRetreatedBy(ReadNextTransactionId(), + max_slot_xid_age); + } + + if (InvalidateObsoleteReplicationSlots(slotInvalidationCauses, _logSegNo, InvalidOid, - InvalidTransactionId)) + InvalidTransactionId, + slotXidLimit)) { /* * Some slots have been invalidated; recalculate the old-segment @@ -8174,6 +8187,8 @@ CreateRestartPoint(int flags) XLogRecPtr endptr; XLogSegNo _logSegNo; TimestampTz xtime; + uint32 slotInvalidationCauses; + TransactionId slotXidLimit; /* Concurrent checkpoint/restartpoint cannot happen */ Assert(!IsUnderPostmaster || MyBackendType == B_CHECKPOINTER); @@ -8352,9 +8367,19 @@ CreateRestartPoint(int flags) INJECTION_POINT("restartpoint-before-slot-invalidation", NULL); - if (InvalidateObsoleteReplicationSlots(RS_INVAL_WAL_REMOVED | RS_INVAL_IDLE_TIMEOUT, + slotInvalidationCauses = RS_INVAL_WAL_REMOVED | RS_INVAL_IDLE_TIMEOUT; + slotXidLimit = InvalidTransactionId; + if (max_slot_xid_age > 0) + { + slotInvalidationCauses |= RS_INVAL_XID_AGE; + slotXidLimit = TransactionIdRetreatedBy(ReadNextTransactionId(), + max_slot_xid_age); + } + + if (InvalidateObsoleteReplicationSlots(slotInvalidationCauses, _logSegNo, InvalidOid, - InvalidTransactionId)) + InvalidTransactionId, + slotXidLimit)) { /* * Some slots have been invalidated; recalculate the old-segment @@ -9253,6 +9278,7 @@ xlog_redo(XLogReaderState *record) */ InvalidateObsoleteReplicationSlots(RS_INVAL_WAL_LEVEL, 0, InvalidOid, + InvalidTransactionId, InvalidTransactionId); } else if (sync_replication_slots) diff --git a/src/backend/replication/slot.c b/src/backend/replication/slot.c index 63ce6d27885..c480abdc29f 100644 --- a/src/backend/replication/slot.c +++ b/src/backend/replication/slot.c @@ -118,6 +118,7 @@ static const SlotInvalidationCauseMap SlotInvalidationCauses[] = { {RS_INVAL_HORIZON, "rows_removed"}, {RS_INVAL_WAL_LEVEL, "wal_level_insufficient"}, {RS_INVAL_IDLE_TIMEOUT, "idle_timeout"}, + {RS_INVAL_XID_AGE, "xid_aged"}, }; /* @@ -169,6 +170,12 @@ int max_repack_replication_slots = 5; /* the maximum number of slots */ int idle_replication_slot_timeout_secs = 0; +/* + * Invalidate replication slots whose xmin or catalog_xmin transaction age + * has exceeded this setting; '0' disables it. + */ +int max_slot_xid_age = 0; + /* * This GUC lists streaming replication standby server slot names that * logical WAL sender processes will wait for. @@ -1792,7 +1799,10 @@ ReportSlotInvalidation(ReplicationSlotInvalidationCause cause, XLogRecPtr restart_lsn, XLogRecPtr oldestLSN, TransactionId snapshotConflictHorizon, - long slot_idle_seconds) + long slot_idle_seconds, + TransactionId xidLimit, + TransactionId slot_xmin, + TransactionId slot_catalog_xmin) { StringInfoData err_detail; StringInfoData err_hint; @@ -1837,6 +1847,45 @@ ReportSlotInvalidation(ReplicationSlotInvalidationCause cause, "idle_replication_slot_timeout"); break; } + + case RS_INVAL_XID_AGE: + { + /* + * The caller passes only the xmin or catalog_xmin that has + * aged past the limit (or both, in the rare case that both + * have), so report whichever is valid. exceeded_by is + * positive because the reported xid precedes xidLimit. Note + * that at least one of them is always valid here. + */ + if (TransactionIdIsValid(slot_xmin)) + { + int32 exceeded_by = (int32) (xidLimit - slot_xmin); + int32 slot_age = (int32) max_slot_xid_age + exceeded_by; + + /* translator: %s is a GUC variable name */ + appendStringInfo(&err_detail, _("The slot's xmin age of %d exceeds the configured \"%s\" of %d by %d transactions."), + slot_age, "max_slot_xid_age", max_slot_xid_age, exceeded_by); + } + + if (TransactionIdIsValid(slot_catalog_xmin)) + { + int32 exceeded_by = (int32) (xidLimit - slot_catalog_xmin); + int32 slot_age = (int32) max_slot_xid_age + exceeded_by; + + if (err_detail.len > 0) + appendStringInfoChar(&err_detail, ' '); + + /* translator: %s is a GUC variable name */ + appendStringInfo(&err_detail, _("The slot's catalog xmin age of %d exceeds the configured \"%s\" of %d by %d transactions."), + slot_age, "max_slot_xid_age", max_slot_xid_age, exceeded_by); + } + + /* translator: %s is a GUC variable name */ + appendStringInfo(&err_hint, _("You might need to increase \"%s\"."), + "max_slot_xid_age"); + break; + } + case RS_INVAL_NONE: pg_unreachable(); } @@ -1875,6 +1924,26 @@ CanInvalidateIdleSlot(ReplicationSlot *s) !(RecoveryInProgress() && s->data.synced)); } +/* + * Can we invalidate an XID-aged replication slot? + * + * XID age invalidation is allowed only when: + * + * 1. XID age limit is set + * 2. Slot has a valid xmin or catalog_xmin + * 3. The slot is not being synced from the primary while the server is in + * recovery. This is because synced slots are always considered to be + * inactive because they don't perform logical decoding to produce changes. + */ +static inline bool +CanInvalidateXidAgedSlot(ReplicationSlot *s) +{ + return (max_slot_xid_age != 0 && + (TransactionIdIsValid(s->data.xmin) || + TransactionIdIsValid(s->data.catalog_xmin)) && + !(RecoveryInProgress() && s->data.synced)); +} + /* * DetermineSlotInvalidationCause - Determine the cause for which a slot * becomes invalid among the given possible causes. @@ -1886,7 +1955,10 @@ static ReplicationSlotInvalidationCause DetermineSlotInvalidationCause(uint32 possible_causes, ReplicationSlot *s, XLogRecPtr oldestLSN, Oid dboid, TransactionId snapshotConflictHorizon, - TimestampTz *inactive_since, TimestampTz now) + TimestampTz *inactive_since, TimestampTz now, + TransactionId xidLimit, + TransactionId *slot_xmin, + TransactionId *slot_catalog_xmin) { Assert(possible_causes != RS_INVAL_NONE); @@ -1957,6 +2029,30 @@ DetermineSlotInvalidationCause(uint32 possible_causes, ReplicationSlot *s, } } + /* Check if the slot needs to be invalidated due to max_slot_xid_age GUC */ + if ((possible_causes & RS_INVAL_XID_AGE) && CanInvalidateXidAgedSlot(s)) + { + Assert(TransactionIdIsValid(xidLimit)); + + /* + * Record whichever of xmin and catalog_xmin has aged past the limit, + * so the invalidation message names the xid that actually triggered + * it. Both can have aged in the rare case of a physical slot that + * also holds a catalog_xmin for cascaded logical decoding. + */ + if (TransactionIdIsValid(s->data.xmin) && + TransactionIdPrecedes(s->data.xmin, xidLimit)) + *slot_xmin = s->data.xmin; + + if (TransactionIdIsValid(s->data.catalog_xmin) && + TransactionIdPrecedes(s->data.catalog_xmin, xidLimit)) + *slot_catalog_xmin = s->data.catalog_xmin; + + if (TransactionIdIsValid(*slot_xmin) || + TransactionIdIsValid(*slot_catalog_xmin)) + return RS_INVAL_XID_AGE; + } + return RS_INVAL_NONE; } @@ -1979,6 +2075,7 @@ InvalidatePossiblyObsoleteSlot(uint32 possible_causes, ReplicationSlot *s, XLogRecPtr oldestLSN, Oid dboid, TransactionId snapshotConflictHorizon, + TransactionId xidLimit, bool *released_lock_out) { int last_signaled_pid = 0; @@ -1995,6 +2092,8 @@ InvalidatePossiblyObsoleteSlot(uint32 possible_causes, ReplicationSlotInvalidationCause invalidation_cause = RS_INVAL_NONE; TimestampTz now = 0; long slot_idle_secs = 0; + TransactionId slot_xmin = InvalidTransactionId; + TransactionId slot_catalog_xmin = InvalidTransactionId; Assert(LWLockHeldByMeInMode(ReplicationSlotControlLock, LW_SHARED)); @@ -2032,7 +2131,10 @@ InvalidatePossiblyObsoleteSlot(uint32 possible_causes, dboid, snapshotConflictHorizon, &inactive_since, - now); + now, + xidLimit, + &slot_xmin, + &slot_catalog_xmin); /* if there's no invalidation, we're done */ if (invalidation_cause == RS_INVAL_NONE) @@ -2124,7 +2226,8 @@ InvalidatePossiblyObsoleteSlot(uint32 possible_causes, ReportSlotInvalidation(invalidation_cause, true, active_pid, slotname, restart_lsn, oldestLSN, snapshotConflictHorizon, - slot_idle_secs); + slot_idle_secs, xidLimit, + slot_xmin, slot_catalog_xmin); if (MyBackendType == B_STARTUP) (void) SignalRecoveryConflict(GetPGProcByNumber(active_proc), @@ -2177,7 +2280,8 @@ InvalidatePossiblyObsoleteSlot(uint32 possible_causes, ReportSlotInvalidation(invalidation_cause, false, active_pid, slotname, restart_lsn, oldestLSN, snapshotConflictHorizon, - slot_idle_secs); + slot_idle_secs, xidLimit, + slot_xmin, slot_catalog_xmin); /* done with this slot for now */ break; @@ -2204,6 +2308,8 @@ InvalidatePossiblyObsoleteSlot(uint32 possible_causes, * logical. * - RS_INVAL_IDLE_TIMEOUT: has been idle longer than the configured * "idle_replication_slot_timeout" duration. + * - RS_INVAL_XID_AGE: has an xmin or catalog_xmin whose age exceeds the + * configured "max_slot_xid_age". * * Note: This function attempts to invalidate the slot for multiple possible * causes in a single pass, minimizing redundant iterations. The "cause" @@ -2217,7 +2323,8 @@ InvalidatePossiblyObsoleteSlot(uint32 possible_causes, bool InvalidateObsoleteReplicationSlots(uint32 possible_causes, XLogSegNo oldestSegno, Oid dboid, - TransactionId snapshotConflictHorizon) + TransactionId snapshotConflictHorizon, + TransactionId xidLimit) { XLogRecPtr oldestLSN; bool invalidated = false; @@ -2226,6 +2333,7 @@ InvalidateObsoleteReplicationSlots(uint32 possible_causes, Assert(!(possible_causes & RS_INVAL_HORIZON) || TransactionIdIsValid(snapshotConflictHorizon)); Assert(!(possible_causes & RS_INVAL_WAL_REMOVED) || oldestSegno > 0); + Assert(!(possible_causes & RS_INVAL_XID_AGE) || TransactionIdIsValid(xidLimit)); Assert(possible_causes != RS_INVAL_NONE); if (max_replication_slots == 0 && max_repack_replication_slots == 0) @@ -2256,6 +2364,7 @@ restart: if (InvalidatePossiblyObsoleteSlot(possible_causes, s, oldestLSN, dboid, snapshotConflictHorizon, + xidLimit, &released_lock)) { Assert(released_lock); diff --git a/src/backend/storage/ipc/standby.c b/src/backend/storage/ipc/standby.c index 7f011e04990..6cbf456459f 100644 --- a/src/backend/storage/ipc/standby.c +++ b/src/backend/storage/ipc/standby.c @@ -504,7 +504,8 @@ ResolveRecoveryConflictWithSnapshot(TransactionId snapshotConflictHorizon, */ if (IsLogicalDecodingEnabled() && isCatalogRel) InvalidateObsoleteReplicationSlots(RS_INVAL_HORIZON, 0, locator.dbOid, - snapshotConflictHorizon); + snapshotConflictHorizon, + InvalidTransactionId); } /* diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat index 3c5e16ad1e7..1cf82729135 100644 --- a/src/backend/utils/misc/guc_parameters.dat +++ b/src/backend/utils/misc/guc_parameters.dat @@ -2149,6 +2149,14 @@ max => 'MAX_KILOBYTES', }, +{ name => 'max_slot_xid_age', type => 'int', context => 'PGC_SIGHUP', group => 'REPLICATION_SENDING', + short_desc => 'Sets the maximum transaction age of a replication slot\'s xmin or catalog_xmin before it is invalidated.', + variable => 'max_slot_xid_age', + boot_val => '0', + min => '0', + max => '2100000000', +}, + # We use the hopefully-safely-small value of 100kB as the compiled-in # default for max_stack_depth. InitializeGUCOptions will increase it # if possible, depending on the actual platform-specific stack limit. diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index e759f06b50f..5818603debd 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -362,6 +362,7 @@ #wal_keep_size = 0 # in megabytes; 0 disables #max_slot_wal_keep_size = -1 # in megabytes; -1 disables #idle_replication_slot_timeout = 0 # in seconds; 0 disables +#max_slot_xid_age = 0 # in transaction age; 0 disables #wal_sender_timeout = 60s # in milliseconds; 0 disables #wal_sender_shutdown_timeout = -1 # in milliseconds # -1 disables the timeout and waits for catch-up diff --git a/src/bin/pg_basebackup/pg_createsubscriber.c b/src/bin/pg_basebackup/pg_createsubscriber.c index 20b354aed56..3271d2b51af 100644 --- a/src/bin/pg_basebackup/pg_createsubscriber.c +++ b/src/bin/pg_basebackup/pg_createsubscriber.c @@ -1681,7 +1681,7 @@ start_standby_server(const struct CreateSubscriberOptions *opt, bool restricted_ appendPQExpBufferStr(pg_ctl_cmd, " -s -o \"-c sync_replication_slots=off\""); /* Prevent unintended slot invalidation */ - appendPQExpBufferStr(pg_ctl_cmd, " -o \"-c idle_replication_slot_timeout=0\""); + appendPQExpBufferStr(pg_ctl_cmd, " -o \"-c idle_replication_slot_timeout=0 -c max_slot_xid_age=0\""); if (restricted_access) { diff --git a/src/include/replication/slot.h b/src/include/replication/slot.h index 9b29444cbca..8c77a61db8e 100644 --- a/src/include/replication/slot.h +++ b/src/include/replication/slot.h @@ -66,10 +66,12 @@ typedef enum ReplicationSlotInvalidationCause RS_INVAL_WAL_LEVEL = (1 << 2), /* idle slot timeout has occurred */ RS_INVAL_IDLE_TIMEOUT = (1 << 3), + /* slot's xmin or catalog_xmin age exceeds the limit */ + RS_INVAL_XID_AGE = (1 << 4), } ReplicationSlotInvalidationCause; /* Maximum number of invalidation causes */ -#define RS_INVAL_MAX_CAUSES 4 +#define RS_INVAL_MAX_CAUSES 5 /* * When the slot synchronization worker is running, or when @@ -327,6 +329,7 @@ extern PGDLLIMPORT int max_replication_slots; extern PGDLLIMPORT int max_repack_replication_slots; extern PGDLLIMPORT char *synchronized_standby_slots; extern PGDLLIMPORT int idle_replication_slot_timeout_secs; +extern PGDLLIMPORT int max_slot_xid_age; /* management of individual slots */ extern void ReplicationSlotCreate(const char *name, bool db_specific, @@ -364,7 +367,8 @@ extern void ReplicationSlotsDropDBSlots(Oid dboid); extern bool InvalidateObsoleteReplicationSlots(uint32 possible_causes, XLogSegNo oldestSegno, Oid dboid, - TransactionId snapshotConflictHorizon); + TransactionId snapshotConflictHorizon, + TransactionId xidLimit); extern ReplicationSlot *SearchNamedReplicationSlot(const char *name, bool need_lock); extern int ReplicationSlotIndex(ReplicationSlot *slot); extern bool ReplicationSlotName(int index, Name name); diff --git a/src/test/recovery/t/019_replslot_limit.pl b/src/test/recovery/t/019_replslot_limit.pl index a412faf51c6..52ff48034c8 100644 --- a/src/test/recovery/t/019_replslot_limit.pl +++ b/src/test/recovery/t/019_replslot_limit.pl @@ -546,4 +546,137 @@ is( $publisher4->safe_psql( $publisher4->stop; $subscriber4->stop; +# Wait for the given slot to be invalidated due to its xid age +sub wait_for_xid_aged_invalidation +{ + my ($node, $slot_name) = @_; + $node->poll_query_until( + 'postgres', qq[ + SELECT COUNT(slot_name) = 1 FROM pg_replication_slots + WHERE slot_name = '$slot_name' AND + invalidation_reason = 'xid_aged'; + ]) or die "Timed out waiting for slot $slot_name to be invalidated"; +} + +# A small age lets slots reach the limit after just a few XIDs +my $slot_xid_age = 100; + +# Defines a procedure that consumes XIDs, one per committed transaction, to +# age a slot's xmin or catalog_xmin. Created on each test primary below. +my $consume_xid_proc = qq{ + CREATE PROCEDURE consume_xid(cnt int) + AS \$\$ + DECLARE + i int; + BEGIN + FOR i IN 1..cnt LOOP + EXECUTE 'SELECT pg_current_xact_id()'; + COMMIT; + END LOOP; + END; + \$\$ LANGUAGE plpgsql; +}; + +# Tests where a checkpoint or restartpoint invalidates the slot +my $primary5 = PostgreSQL::Test::Cluster->new('primary5'); +$primary5->init(allows_streaming => 'logical'); +$primary5->append_conf( + 'postgresql.conf', qq{ +max_slot_xid_age = $slot_xid_age +autovacuum = off +checkpoint_timeout = 1h +}); +$primary5->start; +$primary5->safe_psql('postgres', $consume_xid_proc); +$primary5->safe_psql('postgres', + "CREATE TABLE tbl_user5 AS SELECT generate_series(1,10) AS a"); +$backup_name = 'backup5'; +$primary5->backup($backup_name); + +my $standby5 = PostgreSQL::Test::Cluster->new('standby5'); +$standby5->init_from_backup($primary5, $backup_name, has_streaming => 1); + +# Testcase 1: an active physical slot (aged xmin) is invalidated by the +# checkpoint, which terminates its owner. A running standby keeps the slot +# active; an open transaction there, reported via feedback, freezes its xmin. +$primary5->safe_psql('postgres', + "SELECT pg_create_physical_replication_slot('sb5_slot_a', true)"); + +$standby5->append_conf( + 'postgresql.conf', q{ +primary_slot_name = 'sb5_slot_a' +hot_standby_feedback = on +wal_receiver_status_interval = 1 +}); +$standby5->start; +$primary5->wait_for_catchup($standby5); + +# Confirm streaming works +$primary5->safe_psql('postgres', + "INSERT INTO tbl_user5 SELECT generate_series(11,20)"); +$primary5->wait_for_replay_catchup($standby5); +is( $standby5->safe_psql( + 'postgres', "SELECT count(*) FROM tbl_user5"), + '20', + 'check streamed content on standby'); + +$primary5->poll_query_until( + 'postgres', qq[ + SELECT xmin IS NOT NULL FROM pg_replication_slots + WHERE slot_name = 'sb5_slot_a'; +]) or die "Timed out waiting for slot sb5_slot_a xmin from HS feedback"; + +# Open a transaction on the standby to pin its reported xmin +my $held = $standby5->background_psql('postgres'); +$held->query_safe("BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT 1;"); + +$primary5->safe_psql('postgres', qq{CALL consume_xid(2 * $slot_xid_age)}); + +# The checkpoint terminates the owner and invalidates the slot +$primary5->safe_psql('postgres', "CHECKPOINT"); +wait_for_xid_aged_invalidation($primary5, 'sb5_slot_a'); +ok(1, "held physical slot invalidated by checkpoint"); + +$held->quit; +$standby5->stop; + +# Testcase 2: an inactive logical slot on a standby (aged catalog_xmin) is +# invalidated by a restartpoint. The age limit is disabled on the primary so +# only the standby's own logical slot ages out. +$primary5->safe_psql( + 'postgres', q{ +ALTER SYSTEM SET max_slot_xid_age = 0; +SELECT pg_reload_conf(); +}); +$primary5->safe_psql('postgres', + "SELECT pg_create_physical_replication_slot('sb5_slot_b', true)"); + +# Reuse the same standby, now with the age limit set on it +$standby5->append_conf( + 'postgresql.conf', qq{ +primary_slot_name = 'sb5_slot_b' +hot_standby_feedback = off +max_slot_xid_age = $slot_xid_age +}); +$standby5->start; +$primary5->wait_for_catchup($standby5); + +$standby5->create_logical_slot_on_standby($primary5, 'sb5_logical_slot', + 'postgres'); +$standby5->poll_query_until( + 'postgres', qq[ + SELECT catalog_xmin IS NOT NULL FROM pg_replication_slots + WHERE slot_name = 'sb5_logical_slot'; +]) or die "Timed out waiting for sb5_logical_slot catalog_xmin"; + +$primary5->safe_psql('postgres', qq{CALL consume_xid(2 * $slot_xid_age)}); +$primary5->safe_psql('postgres', "CHECKPOINT"); +$primary5->wait_for_replay_catchup($standby5); +$standby5->safe_psql('postgres', "CHECKPOINT"); +wait_for_xid_aged_invalidation($standby5, 'sb5_logical_slot'); +ok(1, "inactive logical slot on standby invalidated by restartpoint"); + +$standby5->stop; +$primary5->stop; + done_testing(); -- 2.47.3
From d8ff00d82398432b76a1ac8b89e6662e9b3afe5f Mon Sep 17 00:00:00 2001 From: Bharath Rupireddy <[email protected]> Date: Mon, 10 Aug 2026 19:39:31 +0000 Subject: [PATCH v14 2/3] Allow vacuum to invalidate XID-aged replication slots. Commit XXX invalidates replication slots whose xmin or catalog_xmin has aged past max_slot_xid_age only at checkpoints and, on a standby, at restartpoints. A slot holding the horizon of a table being vacuumed right now is therefore not cleared until the next checkpoint, and the vacuum in progress does not benefit. This commit lets vacuum clear such a slot itself, without waiting for the next checkpoint, covering both the VACUUM command and autovacuum. It runs per relation, and only when a slot holding the vacuum of the current relation back has aged past the limit, so the extra work happens only when clearing the slot can actually let vacuum remove more rows. A logical slot holds back only the removal of system catalog rows, through its catalog_xmin, so vacuuming a user table does not invalidate it. Such a slot is instead cleared when a system catalog is vacuumed or at a checkpoint. A physical slot holds back the removal of user table rows, through its xmin, and so can be invalidated by vacuuming any table. To avoid retaking the proc array lock for each relation, the horizon computation now returns the oldest slot xmin and catalog_xmin alongside the oldest xmin, all from the same call. Vacuum never blocks on this. It invalidates only the aged slots it can acquire immediately and leaves any slot still in use to the checkpointer, which does the terminate-and-wait invalidation on its regular pass. This keeps vacuum easy to reason about and avoids many autovacuum workers and backends piling onto one slot waiting for a slow walsender. A slot that vacuum skips this way is cleared by the checkpointer, and later relations then pick up the advanced horizon. Author: Bharath Rupireddy <[email protected]> Reviewed-by: John Hsu <[email protected]> Reviewed-by: Masahiko Sawada <[email protected]> Reviewed-by: Hayato Kuroda <[email protected]> Reviewed-by: Satya Narlapuram <[email protected]> Discussion: https://www.postgresql.org/message-id/CALj2ACW4aUe-_uFQOjdWCEN-xXoLGhmvRFnL8SNw_TZ5nJe+aw@mail.gmail.com Discussion: https://www.postgresql.org/message-id/CALj2ACUmPbkcj4y4oeXvzUkBejG68QDtrFF7QHDC_qz2vQcTCg@mail.gmail.com Discussion: https://www.postgresql.org/message-id/CALj2ACVD0_DhCQ_QOAa7F=nFv8+ZGsHR8SbOc-FmuV8ZrV92HQ@mail.gmail.com --- doc/src/sgml/config.sgml | 31 ++++- src/backend/access/heap/vacuumlazy.c | 16 +++ src/backend/access/transam/xlog.c | 7 +- src/backend/commands/vacuum.c | 77 +++++++++++- src/backend/postmaster/autovacuum.c | 11 ++ src/backend/replication/slot.c | 32 ++++- src/backend/storage/ipc/procarray.c | 69 +++++++++-- src/backend/storage/ipc/standby.c | 2 +- src/include/commands/vacuum.h | 19 +++ src/include/replication/slot.h | 4 +- src/include/storage/procarray.h | 4 + src/test/recovery/t/019_replslot_limit.pl | 138 +++++++++++++++++++++- 12 files changed, 378 insertions(+), 32 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index a5eb6b36146..ac12da08bac 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -5039,12 +5039,31 @@ HINT: If it is safe for all REPLICATION users to use this library as an output </para> <para> - Slot invalidation due to this limit occurs during checkpoint. Because - checkpoints happen at their own interval, there can be some lag between - when a slot's <literal>xmin</literal> or <literal>catalog_xmin</literal> - age exceeds <varname>max_slot_xid_age</varname> and when the slot - invalidation is actually triggered. To avoid such lags, users can force - a checkpoint to promptly invalidate the slot. + Slot invalidation due to XID age occurs during vacuum (both the + <command>VACUUM</command> command and autovacuum) and during checkpoint. + During vacuum, only inactive slots (i.e., not currently in use by a + replication connection) are invalidated, so that vacuum never waits + for a connection to release a slot; an active slot is left for the + next checkpoint, which invalidates it even while it is in use. Because + vacuum and checkpoints happen at their own intervals, there can be + some lag between when a slot's <literal>xmin</literal> or + <literal>catalog_xmin</literal> age exceeds + <varname>max_slot_xid_age</varname> and when the slot invalidation is + actually triggered. To avoid such lags, users can force a checkpoint to + promptly invalidate the slot. + </para> + + <para> + During vacuum, a slot is invalidated only when it is holding vacuum + of the current relation back. A logical replication slot holds back + only the removal of system catalog rows (through its + <literal>catalog_xmin</literal>), so vacuuming a user table does + not invalidate it, even when its age has exceeded + <varname>max_slot_xid_age</varname>; such a slot is invalidated when a + system catalog is vacuumed or at the next checkpoint. A physical + replication slot holds back the removal of user table rows (through + its <literal>xmin</literal>) and so can be invalidated by vacuuming any + table. </para> <para> diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c index 063ef2208de..ab7ec92d7b9 100644 --- a/src/backend/access/heap/vacuumlazy.c +++ b/src/backend/access/heap/vacuumlazy.c @@ -799,6 +799,22 @@ heap_vacuum_rel(Relation rel, const VacuumParams *params, * to increase the number of dead tuples it can prune away.) */ vacrel->aggressive = vacuum_get_cutoffs(rel, params, &vacrel->cutoffs); + + /* + * If a replication slot whose XID age exceeds the limit is holding the + * vacuum cutoff (OldestXmin) back, invalidate it and recompute the + * cutoffs. + */ + if (InvalidateXidAgedReplicationSlots(vacrel->cutoffs.OldestXmin, + vacrel->cutoffs.SlotXmin, + vacrel->cutoffs.SlotCatalogXmin, + vacrel->cutoffs.SlotCatalogXminRelevant)) + { + /* Some slots have been invalidated; re-compute the vacuum cutoffs */ + vacrel->aggressive = vacuum_get_cutoffs(rel, params, + &vacrel->cutoffs); + } + vacrel->rel_pages = orig_rel_pages = RelationGetNumberOfBlocks(rel); vacrel->vistest = GlobalVisTestFor(rel); diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index cbd6ac4e0ed..f1b85cf344e 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -7893,7 +7893,7 @@ CreateCheckPoint(int flags) if (InvalidateObsoleteReplicationSlots(slotInvalidationCauses, _logSegNo, InvalidOid, InvalidTransactionId, - slotXidLimit)) + slotXidLimit, false, true)) { /* * Some slots have been invalidated; recalculate the old-segment @@ -8379,7 +8379,7 @@ CreateRestartPoint(int flags) if (InvalidateObsoleteReplicationSlots(slotInvalidationCauses, _logSegNo, InvalidOid, InvalidTransactionId, - slotXidLimit)) + slotXidLimit, false, true)) { /* * Some slots have been invalidated; recalculate the old-segment @@ -9279,7 +9279,8 @@ xlog_redo(XLogReaderState *record) InvalidateObsoleteReplicationSlots(RS_INVAL_WAL_LEVEL, 0, InvalidOid, InvalidTransactionId, - InvalidTransactionId); + InvalidTransactionId, false, + true); } else if (sync_replication_slots) { diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index 64eed16a160..5a029f5ca2e 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -48,6 +48,7 @@ #include "postmaster/autovacuum.h" #include "postmaster/bgworker_internals.h" #include "postmaster/interrupt.h" +#include "replication/slot.h" #include "storage/bufmgr.h" #include "storage/lmgr.h" #include "storage/pmsignal.h" @@ -1157,7 +1158,11 @@ vacuum_get_cutoffs(Relation rel, const VacuumParams *params, * that only one vacuum process can be working on a particular table at * any time, and that each vacuum is always an independent transaction. */ - cutoffs->OldestXmin = GetOldestNonRemovableTransactionId(rel); + cutoffs->OldestXmin = + GetOldestNonRemovableTransactionIdAndSlotXmins(rel, + &cutoffs->SlotXmin, + &cutoffs->SlotCatalogXmin, + &cutoffs->SlotCatalogXminRelevant); Assert(TransactionIdIsNormal(cutoffs->OldestXmin)); @@ -2737,3 +2742,73 @@ vac_tid_reaped(ItemPointer itemptr, void *state) return TidStoreIsMember(dead_items, itemptr); } + +/* + * Invalidate replication slots whose XID age exceeds the limit. + * + * The caller passes the overall oldest xmin, plus the oldest slot xmin and + * catalog_xmin. If a replication slot is not what holds the oldest xmin back, + * or the horizon has not yet aged past the limit, there is nothing to do. + * + * slot_catalog_xmin_relevant tells whether a slot's catalog_xmin can hold this + * relation's oldest xmin back (true for catalog and shared relations). When + * it is false, a slot holding only a catalog_xmin cannot be blocking this + * vacuum, so such slots are neither considered here nor invalidated: even if + * one is aged, invalidating it would not advance this vacuum's cutoff, and it + * still has a chance to advance on its own before a vacuum of a catalog + * relation or a checkpoint acts on it. + * + * Returns true if at least one slot was invalidated. + */ +bool +InvalidateXidAgedReplicationSlots(TransactionId oldest_xmin, + TransactionId slot_xmin, + TransactionId slot_catalog_xmin, + bool slot_catalog_xmin_relevant) +{ + TransactionId xid_limit; + bool slot_holds_oldest_xmin; + + if (max_slot_xid_age == 0) + return false; + + Assert(TransactionIdIsNormal(oldest_xmin)); + + /* + * Check if a replication slot's xmin, or its catalog_xmin when that is + * relevant for this relation, is what's holding the oldest xmin back. If + * not, skip the unnecessary work. + */ + slot_holds_oldest_xmin = + (TransactionIdIsValid(slot_xmin) && + TransactionIdEquals(oldest_xmin, slot_xmin)) || + (slot_catalog_xmin_relevant && + TransactionIdIsValid(slot_catalog_xmin) && + TransactionIdEquals(oldest_xmin, slot_catalog_xmin)); + + if (!slot_holds_oldest_xmin) + return false; + + xid_limit = TransactionIdRetreatedBy(ReadNextTransactionId(), + max_slot_xid_age); + + /* + * A replication slot holds the oldest xmin back, so invalidate any slot + * that has aged past the limit. When catalog_xmin is not relevant for + * this relation, only a slot's xmin is considered, so a slot holding only + * a catalog_xmin (a logical slot) is left alone. + * + * Vacuum never blocks on this. It invalidates only the slots it can + * acquire immediately and leaves any slot still in use to the + * checkpointer, so that many vacuum processes never pile up waiting on + * one slot. + */ + if (TransactionIdPrecedes(oldest_xmin, xid_limit)) + return InvalidateObsoleteReplicationSlots(RS_INVAL_XID_AGE, + 0, InvalidOid, + InvalidTransactionId, + xid_limit, + true, slot_catalog_xmin_relevant); + + return false; +} diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index 874454891d3..3d745fa1787 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -89,6 +89,7 @@ #include "postmaster/autovacuum.h" #include "postmaster/interrupt.h" #include "postmaster/postmaster.h" +#include "replication/slot.h" #include "storage/aio_subsys.h" #include "storage/bufmgr.h" #include "storage/ipc.h" @@ -2542,6 +2543,16 @@ do_autovacuum(void) tab->at_datname, tab->at_nspname, tab->at_relname); EmitErrorReport(); + /* + * We may still be holding a replication slot if we errored out + * while invalidating an XID-aged slot during vacuum. A slot is + * not released by the transaction abort below, so release it + * here. Otherwise this autovacuum worker would carry it into the + * next table. + */ + if (MyReplicationSlot != NULL) + ReplicationSlotRelease(); + /* this resets ProcGlobal->statusFlags[i] too */ AbortOutOfAnyTransaction(); FlushErrorState(); diff --git a/src/backend/replication/slot.c b/src/backend/replication/slot.c index c480abdc29f..c2bb5abde44 100644 --- a/src/backend/replication/slot.c +++ b/src/backend/replication/slot.c @@ -1958,7 +1958,8 @@ DetermineSlotInvalidationCause(uint32 possible_causes, ReplicationSlot *s, TimestampTz *inactive_since, TimestampTz now, TransactionId xidLimit, TransactionId *slot_xmin, - TransactionId *slot_catalog_xmin) + TransactionId *slot_catalog_xmin, + bool check_catalog_xmin) { Assert(possible_causes != RS_INVAL_NONE); @@ -2039,12 +2040,19 @@ DetermineSlotInvalidationCause(uint32 possible_causes, ReplicationSlot *s, * so the invalidation message names the xid that actually triggered * it. Both can have aged in the rare case of a physical slot that * also holds a catalog_xmin for cascaded logical decoding. + * + * catalog_xmin is considered only when it is relevant for the + * caller's relation. A slot holding only a catalog_xmin cannot block + * a vacuum of a user table, so such a slot is left alone there; a + * checkpoint or a vacuum of a catalog relation still invalidates it + * later. */ if (TransactionIdIsValid(s->data.xmin) && TransactionIdPrecedes(s->data.xmin, xidLimit)) *slot_xmin = s->data.xmin; - if (TransactionIdIsValid(s->data.catalog_xmin) && + if (check_catalog_xmin && + TransactionIdIsValid(s->data.catalog_xmin) && TransactionIdPrecedes(s->data.catalog_xmin, xidLimit)) *slot_catalog_xmin = s->data.catalog_xmin; @@ -2076,6 +2084,8 @@ InvalidatePossiblyObsoleteSlot(uint32 possible_causes, XLogRecPtr oldestLSN, Oid dboid, TransactionId snapshotConflictHorizon, TransactionId xidLimit, + bool nowait, + bool check_catalog_xmin, bool *released_lock_out) { int last_signaled_pid = 0; @@ -2134,7 +2144,8 @@ InvalidatePossiblyObsoleteSlot(uint32 possible_causes, now, xidLimit, &slot_xmin, - &slot_catalog_xmin); + &slot_catalog_xmin, + check_catalog_xmin); /* if there's no invalidation, we're done */ if (invalidation_cause == RS_INVAL_NONE) @@ -2199,6 +2210,10 @@ InvalidatePossiblyObsoleteSlot(uint32 possible_causes, if (active_proc != INVALID_PROC_NUMBER) { + /* A nowait caller leaves an active slot untouched. */ + if (nowait) + break; + /* * Prepare the sleep on the slot's condition variable before * releasing the lock, to close a possible race condition if the @@ -2315,6 +2330,11 @@ InvalidatePossiblyObsoleteSlot(uint32 possible_causes, * causes in a single pass, minimizing redundant iterations. The "cause" * parameter can be a MASK representing one or more of the defined causes. * + * If "nowait" is true, slots that are currently held by a live process are + * left untouched instead of terminating the owner and waiting for the slot to + * be released. Vacuum uses this for XID-age invalidation so it never blocks. + * Those slots are instead cleaned up by the checkpointer, which always waits. + * * If it invalidates the last logical slot in the cluster, it requests to * disable logical decoding. * @@ -2324,7 +2344,9 @@ bool InvalidateObsoleteReplicationSlots(uint32 possible_causes, XLogSegNo oldestSegno, Oid dboid, TransactionId snapshotConflictHorizon, - TransactionId xidLimit) + TransactionId xidLimit, + bool nowait, + bool check_catalog_xmin) { XLogRecPtr oldestLSN; bool invalidated = false; @@ -2364,7 +2386,7 @@ restart: if (InvalidatePossiblyObsoleteSlot(possible_causes, s, oldestLSN, dboid, snapshotConflictHorizon, - xidLimit, + xidLimit, nowait, check_catalog_xmin, &released_lock)) { Assert(released_lock); diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c index 8a4d8e7efa4..f03387fadbd 100644 --- a/src/backend/storage/ipc/procarray.c +++ b/src/backend/storage/ipc/procarray.c @@ -1929,6 +1929,31 @@ GlobalVisHorizonKindForRel(Relation rel) return VISHORIZON_TEMP; } +/* + * A helper function to return the appropriate oldest non-removable + * TransactionId from the pre-computed horizons, based on the relation + * type. + */ +static inline TransactionId +GetOldestNonRemovableTransactionIdFromHorizons(ComputeXidHorizonsResult *horizons, + Relation rel) +{ + switch (GlobalVisHorizonKindForRel(rel)) + { + case VISHORIZON_SHARED: + return horizons->shared_oldest_nonremovable; + case VISHORIZON_CATALOG: + return horizons->catalog_oldest_nonremovable; + case VISHORIZON_DATA: + return horizons->data_oldest_nonremovable; + case VISHORIZON_TEMP: + return horizons->temp_oldest_nonremovable; + } + + /* just to prevent compiler warnings */ + return InvalidTransactionId; +} + /* * Return the oldest XID for which deleted tuples must be preserved in the * passed table. @@ -1947,20 +1972,38 @@ GetOldestNonRemovableTransactionId(Relation rel) ComputeXidHorizons(&horizons); - switch (GlobalVisHorizonKindForRel(rel)) - { - case VISHORIZON_SHARED: - return horizons.shared_oldest_nonremovable; - case VISHORIZON_CATALOG: - return horizons.catalog_oldest_nonremovable; - case VISHORIZON_DATA: - return horizons.data_oldest_nonremovable; - case VISHORIZON_TEMP: - return horizons.temp_oldest_nonremovable; - } + return GetOldestNonRemovableTransactionIdFromHorizons(&horizons, rel); +} - /* just to prevent compiler warnings */ - return InvalidTransactionId; +/* + * Same as GetOldestNonRemovableTransactionId(), but also returns the + * replication slot xmin and catalog_xmin from the same ComputeXidHorizons() + * call. This avoids a separate ProcArrayLock acquisition when the caller + * needs both values. + * + * *slot_catalog_xmin_relevant is set to whether a slot's catalog_xmin can + * hold this relation's cutoff back. That is true for catalog and shared + * relations, whose horizon is computed from both the slot xmin and + * catalog_xmin, and false for ordinary and temporary relations, whose horizon + * uses only the slot xmin. + */ +TransactionId +GetOldestNonRemovableTransactionIdAndSlotXmins(Relation rel, + TransactionId *slot_xmin, + TransactionId *slot_catalog_xmin, + bool *slot_catalog_xmin_relevant) +{ + ComputeXidHorizonsResult horizons; + GlobalVisHorizonKind kind = GlobalVisHorizonKindForRel(rel); + + ComputeXidHorizons(&horizons); + + *slot_xmin = horizons.slot_xmin; + *slot_catalog_xmin = horizons.slot_catalog_xmin; + *slot_catalog_xmin_relevant = (kind == VISHORIZON_CATALOG || + kind == VISHORIZON_SHARED); + + return GetOldestNonRemovableTransactionIdFromHorizons(&horizons, rel); } /* diff --git a/src/backend/storage/ipc/standby.c b/src/backend/storage/ipc/standby.c index 6cbf456459f..ae88d608ede 100644 --- a/src/backend/storage/ipc/standby.c +++ b/src/backend/storage/ipc/standby.c @@ -505,7 +505,7 @@ ResolveRecoveryConflictWithSnapshot(TransactionId snapshotConflictHorizon, if (IsLogicalDecodingEnabled() && isCatalogRel) InvalidateObsoleteReplicationSlots(RS_INVAL_HORIZON, 0, locator.dbOid, snapshotConflictHorizon, - InvalidTransactionId); + InvalidTransactionId, false, true); } /* diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h index e62f23748dc..8dcb1bf18e6 100644 --- a/src/include/commands/vacuum.h +++ b/src/include/commands/vacuum.h @@ -287,6 +287,21 @@ struct VacuumCutoffs */ TransactionId FreezeLimit; MultiXactId MultiXactCutoff; + + /* + * Oldest xmin and catalog xmin of any replication slot obtained from the + * same ComputeXidHorizons() call that computed OldestXmin. + */ + TransactionId SlotXmin; + TransactionId SlotCatalogXmin; + + /* + * Whether a slot's catalog_xmin can hold this relation's OldestXmin back. + * That is true for catalog and shared relations, and false for ordinary + * and temporary ones. It decides whether an aged slot holding only a + * catalog_xmin (a logical slot) is worth invalidating for this relation. + */ + bool SlotCatalogXminRelevant; }; /* @@ -399,6 +414,10 @@ extern IndexBulkDeleteResult *vac_bulkdel_one_index(IndexVacuumInfo *ivinfo, VacDeadItemsInfo *dead_items_info); extern IndexBulkDeleteResult *vac_cleanup_one_index(IndexVacuumInfo *ivinfo, IndexBulkDeleteResult *istat); +extern bool InvalidateXidAgedReplicationSlots(TransactionId oldest_xmin, + TransactionId slot_xmin, + TransactionId slot_catalog_xmin, + bool slot_catalog_xmin_relevant); /* In postmaster/autovacuum.c */ extern void AutoVacuumUpdateCostLimit(void); diff --git a/src/include/replication/slot.h b/src/include/replication/slot.h index 8c77a61db8e..1208b8650de 100644 --- a/src/include/replication/slot.h +++ b/src/include/replication/slot.h @@ -368,7 +368,9 @@ extern bool InvalidateObsoleteReplicationSlots(uint32 possible_causes, XLogSegNo oldestSegno, Oid dboid, TransactionId snapshotConflictHorizon, - TransactionId xidLimit); + TransactionId xidLimit, + bool nowait, + bool check_catalog_xmin); extern ReplicationSlot *SearchNamedReplicationSlot(const char *name, bool need_lock); extern int ReplicationSlotIndex(ReplicationSlot *slot); extern bool ReplicationSlotName(int index, Name name); diff --git a/src/include/storage/procarray.h b/src/include/storage/procarray.h index d718a5b542f..f41e039b091 100644 --- a/src/include/storage/procarray.h +++ b/src/include/storage/procarray.h @@ -51,6 +51,10 @@ extern RunningTransactions GetRunningTransactionData(void); extern bool TransactionIdIsInProgress(TransactionId xid); extern TransactionId GetOldestNonRemovableTransactionId(Relation rel); +extern TransactionId GetOldestNonRemovableTransactionIdAndSlotXmins(Relation rel, + TransactionId *slot_xmin, + TransactionId *slot_catalog_xmin, + bool *slot_catalog_xmin_relevant); extern TransactionId GetOldestTransactionIdConsideredRunning(void); extern TransactionId GetOldestActiveTransactionId(bool inCommitOnly, bool allDbs); diff --git a/src/test/recovery/t/019_replslot_limit.pl b/src/test/recovery/t/019_replslot_limit.pl index 52ff48034c8..18ae27bb8d7 100644 --- a/src/test/recovery/t/019_replslot_limit.pl +++ b/src/test/recovery/t/019_replslot_limit.pl @@ -596,9 +596,11 @@ $primary5->backup($backup_name); my $standby5 = PostgreSQL::Test::Cluster->new('standby5'); $standby5->init_from_backup($primary5, $backup_name, has_streaming => 1); -# Testcase 1: an active physical slot (aged xmin) is invalidated by the +# Testcase 1: an active physical slot (aged xmin) is skipped by the VACUUM +# command, which never blocks on an active slot, and invalidated by the # checkpoint, which terminates its owner. A running standby keeps the slot -# active; an open transaction there, reported via feedback, freezes its xmin. +# active, with an open transaction there, reported via feedback, freezing its +# xmin. $primary5->safe_psql('postgres', "SELECT pg_create_physical_replication_slot('sb5_slot_a', true)"); @@ -632,6 +634,15 @@ $held->query_safe("BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT 1;"); $primary5->safe_psql('postgres', qq{CALL consume_xid(2 * $slot_xid_age)}); +# Vacuum leaves the active slot for the checkpoint, so it stays valid +$primary5->safe_psql('postgres', "VACUUM tbl_user5"); +is( $primary5->safe_psql( + 'postgres', + qq[SELECT invalidation_reason IS NULL AND active FROM pg_replication_slots WHERE slot_name = 'sb5_slot_a';] + ), + 't', + 'active physical slot not invalidated by VACUUM'); + # The checkpoint terminates the owner and invalidates the slot $primary5->safe_psql('postgres', "CHECKPOINT"); wait_for_xid_aged_invalidation($primary5, 'sb5_slot_a'); @@ -677,6 +688,129 @@ wait_for_xid_aged_invalidation($standby5, 'sb5_logical_slot'); ok(1, "inactive logical slot on standby invalidated by restartpoint"); $standby5->stop; + +# Restore the age limit on the primary, disabled earlier for the standby's +# own logical slot. +$primary5->safe_psql( + 'postgres', q{ +ALTER SYSTEM RESET max_slot_xid_age; +SELECT pg_reload_conf(); +}); + +# Testcase 3: an inactive logical slot (aged catalog_xmin) is invalidated by +# vacuuming a system catalog, whose horizon includes catalog_xmin. VACUUM is in +# the foreground and the slot is inactive, so it is invalidated synchronously. +$primary5->safe_psql('postgres', + "SELECT pg_create_logical_replication_slot('lsub5_slot', 'pgoutput')"); +$primary5->poll_query_until( + 'postgres', qq[ + SELECT catalog_xmin IS NOT NULL FROM pg_replication_slots + WHERE slot_name = 'lsub5_slot'; +]) or die "Timed out waiting for slot lsub5_slot catalog_xmin"; + +$primary5->safe_psql('postgres', qq{CALL consume_xid(2 * $slot_xid_age)}); + +$primary5->safe_psql('postgres', "VACUUM pg_class"); +is( $primary5->safe_psql( + 'postgres', + qq[SELECT invalidation_reason = 'xid_aged' FROM pg_replication_slots WHERE slot_name = 'lsub5_slot';] + ), + 't', + 'inactive logical slot invalidated by vacuuming a system catalog'); + +# Testcase 4: an inactive physical slot (aged xmin) is invalidated by +# autovacuum. hs_feedback gives the slot an xmin, and stopping the +# standby freezes it. Autovacuum runs on dead tuples with naptime 1s. +$standby5->append_conf('postgresql.conf', "hot_standby_feedback = on"); +$standby5->start; +$primary5->wait_for_catchup($standby5); + +$primary5->poll_query_until( + 'postgres', qq[ + SELECT xmin IS NOT NULL FROM pg_replication_slots + WHERE slot_name = 'sb5_slot_b'; +]) or die "Timed out waiting for slot sb5_slot_b xmin from HS feedback"; + +$standby5->stop; + +$primary5->safe_psql('postgres', qq{CALL consume_xid(2 * $slot_xid_age)}); + +# Turn autovacuum on and give it a table with dead tuples +$primary5->append_conf( + 'postgresql.conf', q{ +autovacuum = on +autovacuum_naptime = 1s +log_autovacuum_min_duration = 0 +}); +$primary5->reload; +$primary5->safe_psql( + 'postgres', q{ + CREATE TABLE tbl_dead5 (a int); + INSERT INTO tbl_dead5 SELECT generate_series(1, 10000); + DELETE FROM tbl_dead5; +}); +wait_for_xid_aged_invalidation($primary5, 'sb5_slot_b'); +ok(1, "inactive physical slot invalidated by autovacuum"); + +$primary5->append_conf('postgresql.conf', "autovacuum = off"); +$primary5->reload; + +# Testcase 5: with an aged physical slot (xmin) and an aged logical slot +# (catalog_xmin) both present, vacuuming a user table invalidates only the +# physical slot. A user table's horizon uses xmin, not catalog_xmin, so the +# logical slot is left alone. Vacuuming a system catalog then invalidates it. +$primary5->safe_psql('postgres', + "SELECT pg_create_logical_replication_slot('lsub5b_slot', 'pgoutput')"); +$primary5->poll_query_until( + 'postgres', qq[ + SELECT catalog_xmin IS NOT NULL FROM pg_replication_slots + WHERE slot_name = 'lsub5b_slot'; +]) or die "Timed out waiting for slot lsub5b_slot catalog_xmin"; + +# A fresh physical slot for the standby, since the previous one was +# invalidated. hs_feedback gives it an xmin, and stopping the standby +# freezes it. +$primary5->safe_psql('postgres', + "SELECT pg_create_physical_replication_slot('sb5_slot_c', true)"); +$standby5->append_conf('postgresql.conf', "primary_slot_name = 'sb5_slot_c'"); +$standby5->start; +$primary5->wait_for_catchup($standby5); + +$primary5->poll_query_until( + 'postgres', qq[ + SELECT xmin IS NOT NULL FROM pg_replication_slots + WHERE slot_name = 'sb5_slot_c'; +]) or die "Timed out waiting for slot sb5_slot_c xmin from HS feedback"; + +$standby5->stop; + +$primary5->safe_psql('postgres', qq{CALL consume_xid(2 * $slot_xid_age)}); + +# Vacuum a user table, which invalidates the physical slot but leaves the +# logical one alone +$primary5->safe_psql('postgres', "VACUUM tbl_user5"); +is( $primary5->safe_psql( + 'postgres', + qq[SELECT invalidation_reason = 'xid_aged' FROM pg_replication_slots WHERE slot_name = 'sb5_slot_c';] + ), + 't', + 'physical slot invalidated by vacuuming a user table'); +is( $primary5->safe_psql( + 'postgres', + qq[SELECT invalidation_reason IS NULL FROM pg_replication_slots WHERE slot_name = 'lsub5b_slot';] + ), + 't', + 'logical slot not invalidated by vacuuming a user table'); + +# Vacuum a system catalog, which now invalidates the logical slot +$primary5->safe_psql('postgres', "VACUUM pg_class"); +is( $primary5->safe_psql( + 'postgres', + qq[SELECT invalidation_reason = 'xid_aged' FROM pg_replication_slots WHERE slot_name = 'lsub5b_slot';] + ), + 't', + 'logical slot invalidated by vacuuming a system catalog'); + $primary5->stop; done_testing(); -- 2.47.3
From b77916d929c5b1a2cbfe8eff39338d306752d5e5 Mon Sep 17 00:00:00 2001 From: Bharath Rupireddy <[email protected]> Date: Thu, 20 Aug 2026 18:19:01 +0000 Subject: [PATCH v14 3/3] Invalidate XID-aged synced replication slots on a standby. A synced slot on a standby carries a catalog_xmin. Unlike the WAL a synced slot pins, which it holds only on the standby, its catalog_xmin is sent to the primary's physical slot through HS feedback and holds the catalog horizon back there. A synced slot whose catalog_xmin has aged can therefore keep vacuum on the primary from pruning dead catalog rows and freezing XIDs, holding the primary back from below, even though the slot does no decoding of its own on the standby. This commit lets XID-age invalidation act on synced slots too, so an aged synced slot on a standby is invalidated during a restartpoint like any other slot. Once it is invalidated the slot stops contributing its catalog_xmin, the standby reports an advanced catalog_xmin through HS feedback, the catalog_xmin held by the primary's physical slot advances, and vacuum on the primary can proceed. Author: Bharath Rupireddy <[email protected]> Reviewed-by: Amit Kapila <[email protected]> Reviewed-by: Bertrand Drouvot <[email protected]> Discussion: CALj2ACWTz5fKMPa0wNghRFdfPBzc+j3x+kcXSeCo+3twZmDdyg@mail.gmail.com">https://www.postgresql.org/message-id/CALj2ACWTz5fKMPa0wNghRFdfPBzc+j3x+kcXSeCo+3twZmDdyg@mail.gmail.com Discussion: https://www.postgresql.org/message-id/CAA4eK1LsFi7GWtwtKwQm1kxgJWz_GvyDNSaO8=gr1zocPni-+g@mail.gmail.com --- doc/src/sgml/config.sgml | 16 +++-- src/backend/replication/slot.c | 14 ++-- src/test/recovery/t/019_replslot_limit.pl | 86 +++++++++++++++++++++++ 3 files changed, 105 insertions(+), 11 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index ac12da08bac..68d35e2bd29 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -5085,13 +5085,17 @@ HINT: If it is safe for all REPLICATION users to use this library as an output </para> <para> - Note that this invalidation mechanism is not applicable for slots - on the standby server that are being synced from the primary server - (i.e., standby slots having + This mechanism also applies to slots on a standby server that are being + synced from the primary server (i.e., standby slots having <link linkend="view-pg-replication-slots">pg_replication_slots</link>.<structfield>synced</structfield> - value <literal>true</literal>). Synced slots are always considered to - be inactive because they don't perform logical decoding to produce - changes. + value <literal>true</literal>). A synced slot's + <literal>catalog_xmin</literal> is sent to the primary's physical slot + through the standby's <xref linkend="guc-hot-standby-feedback"/> and + holds the catalog horizon back there, so a synced slot whose + <literal>catalog_xmin</literal> has aged can keep vacuum on the primary + from pruning dead catalog rows and freezing XIDs. Invalidating such a + slot on the standby advances the <literal>catalog_xmin</literal> held by + the primary's physical slot, letting vacuum there proceed. </para> </listitem> </varlistentry> diff --git a/src/backend/replication/slot.c b/src/backend/replication/slot.c index c2bb5abde44..dd8db1bb74b 100644 --- a/src/backend/replication/slot.c +++ b/src/backend/replication/slot.c @@ -1931,17 +1931,21 @@ CanInvalidateIdleSlot(ReplicationSlot *s) * * 1. XID age limit is set * 2. Slot has a valid xmin or catalog_xmin - * 3. The slot is not being synced from the primary while the server is in - * recovery. This is because synced slots are always considered to be - * inactive because they don't perform logical decoding to produce changes. + * + * Note that this includes synced slots on a standby. A synced slot's + * catalog_xmin is sent to the primary's physical slot through + * hot_standby_feedback and holds the catalog horizon back there, so a synced + * slot whose catalog_xmin has aged can keep vacuum on the primary from pruning + * dead catalog rows and freezing XIDs, even though it does no decoding of its + * own on the standby. Invalidating it advances the catalog_xmin held by the + * primary's physical slot, letting vacuum there proceed. */ static inline bool CanInvalidateXidAgedSlot(ReplicationSlot *s) { return (max_slot_xid_age != 0 && (TransactionIdIsValid(s->data.xmin) || - TransactionIdIsValid(s->data.catalog_xmin)) && - !(RecoveryInProgress() && s->data.synced)); + TransactionIdIsValid(s->data.catalog_xmin))); } /* diff --git a/src/test/recovery/t/019_replslot_limit.pl b/src/test/recovery/t/019_replslot_limit.pl index 18ae27bb8d7..581af0b6375 100644 --- a/src/test/recovery/t/019_replslot_limit.pl +++ b/src/test/recovery/t/019_replslot_limit.pl @@ -813,4 +813,90 @@ is( $primary5->safe_psql( $primary5->stop; +# Testcase 6: a synced slot on a standby (aged catalog_xmin) is invalidated +# by a restartpoint, which releases the catalog_xmin it had pinned on the +# primary's physical slot via hs_feedback. Sync is off after one manual sync +# so the synced catalog_xmin stays frozen, and the age limit is set only on +# the standby. +my $primary6 = PostgreSQL::Test::Cluster->new('primary6'); +$primary6->init(allows_streaming => 'logical'); +$primary6->append_conf( + 'postgresql.conf', qq{ +autovacuum = off +checkpoint_timeout = 1h +}); +$primary6->start; +$primary6->safe_psql('postgres', $consume_xid_proc); +$primary6->safe_psql('postgres', + "SELECT pg_create_physical_replication_slot('sb6_phys')"); +$primary6->backup('backup6'); + +my $standby6 = PostgreSQL::Test::Cluster->new('standby6'); +$standby6->init_from_backup($primary6, 'backup6', has_streaming => 1); +my $connstr6 = $primary6->connstr; +$standby6->append_conf( + 'postgresql.conf', qq{ +primary_slot_name = 'sb6_phys' +primary_conninfo = '$connstr6 dbname=postgres' +hot_standby_feedback = on +wal_receiver_status_interval = 1 +sync_replication_slots = off +checkpoint_timeout = 1h +max_slot_xid_age = $slot_xid_age +}); +$standby6->start; +$primary6->wait_for_replay_catchup($standby6); + +# Create the failover slot now that the standby is up, then sync it once by +# hand. +$primary6->safe_psql('postgres', + "SELECT pg_create_logical_replication_slot('failover6_slot', 'pgoutput', false, false, true)" +); +my $synced6 = 0; +foreach (1 .. 10) +{ + $primary6->safe_psql('postgres', "SELECT pg_log_standby_snapshot()"); + $primary6->wait_for_replay_catchup($standby6); + $standby6->safe_psql('postgres', "SELECT pg_sync_replication_slots()"); + $synced6 = $standby6->safe_psql( + 'postgres', qq[ + SELECT count(*) = 1 FROM pg_replication_slots + WHERE slot_name = 'failover6_slot' AND synced AND NOT temporary + AND catalog_xmin IS NOT NULL; + ]); + last if $synced6 eq 't'; +} +$synced6 eq 't' or die "Timed out waiting for failover6_slot to be synced"; + +# The synced slot's catalog_xmin, pinned onto sb6_phys via hs_feedback. +my $frozen = $standby6->safe_psql('postgres', + "SELECT catalog_xmin FROM pg_replication_slots WHERE slot_name = 'failover6_slot'" +); +$primary6->poll_query_until( + 'postgres', qq[ + SELECT catalog_xmin = '$frozen' FROM pg_replication_slots + WHERE slot_name = 'sb6_phys'; +]) or die "Timed out waiting for sb6_phys to pick up the synced catalog_xmin"; + +# Age the frozen synced catalog_xmin out, then a restartpoint on the standby +# invalidates it (a primary checkpoint gives the standby one to restart from). +$primary6->safe_psql('postgres', qq{CALL consume_xid(2 * $slot_xid_age)}); +$primary6->safe_psql('postgres', "CHECKPOINT"); +$primary6->wait_for_replay_catchup($standby6); +$standby6->safe_psql('postgres', "CHECKPOINT"); +wait_for_xid_aged_invalidation($standby6, 'failover6_slot'); +ok(1, 'synced slot on standby invalidated by restartpoint'); + +# With the synced slot gone, the standby stops reporting its catalog_xmin, so +# the feedback horizon on the primary's physical slot is released. +$primary6->poll_query_until( + 'postgres', qq[ + SELECT catalog_xmin IS DISTINCT FROM '$frozen'::xid FROM pg_replication_slots + WHERE slot_name = 'sb6_phys'; +]) or die "Timed out waiting for sb6_phys catalog_xmin to be released"; +ok(1, 'invalidation releases the feedback horizon on the primary'); + +$standby6->stop; +$primary6->stop; + done_testing(); -- 2.47.3
