Re: Introduce XID age based replication slot invalidation

Hi,

On Tue, Oct 6, 2026 at 11:31 PM Nisha Moond <nisha(dot)moond412(at)gmail(dot)com> wrote:
>
> Found one issue: a long-running transaction on the publisher can get
> every healthy logical slot invalidated, without vacuum gaining
> anything.
>
> IMO, a slot shouldn't be invalidated for XID age when it isn't what's
> holding the horizon back, i.e. invalidate a slot only if its aged
> xmin/catalog_xmin precedes the oldest running XID (e.g.
> GetOldestActiveTransactionId()).
>
> Thoughts?

Nice catch! Yes, you are right. Invalidating the slots in this case
seems unnecessary. But I see a problem with using just the oldest
running XID. It doesn't cover a backend that holds only a snapshot
xmin, e.g., a read-only query running for hours, or a repeatable-read
transaction that keeps its snapshot for the whole transaction [1].
Such a backend has no XID, so the oldest running XID doesn't see it,
but it still holds the catalog horizon back.

So I think we need a new function that tells whether a slot is the one
holding the vacuum back right now. It would loop over the proc-array
and do what the xid horizons computation does, but without folding in
the slot xmins, and take a database oid so the checkpointer can ask
for a logical slot's database without being connected to it. This
function gets called only for a slot that has already aged past the
limit, so no proc-array costs in a normal pass over slots. On a
standby, it also covers the primary's transactions from
KnownAssignedXids.

This way, only the slots that really hold the vacuum back get invalidated.

Thoughts?

[1]
-- max_slot_xid_age = 100
-- Session 1, holds a snapshot with no XID assigned
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT 1;

-- Session 2, make some dead rows in pg_class
CREATE TABLE foo(a int);
DROP TABLE foo;

CALL consume_xid(20);
SELECT pg_create_logical_replication_slot('s1', 'test_decoding');
CALL consume_xid(120);

-- Slot gets invalidated but vacuum wont' advance
CHECKPOINT;
SELECT slot_name, catalog_xmin, invalidation_reason FROM pg_replication_slots;
VACUUM (VERBOSE) pg_class;

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com