Re: Show effective xmin in pg_replication_slots when xmin is not set

On Mon, Oct 5, 2026 at 6:00 AM Bharath Rupireddy
<bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
>
> Hi,
>
> While working on XID age based slot invalidation [1] and reviewing the
> pg_xmin_horizon patch [2], I realized that a logical replication slot
> can hold back VACUUM on user tables while its xmin is shown as NULL in
> pg_replication_slots. The documentation describes xmin as the oldest
> transaction that the slot needs the database to retain, so the view
> doesn't match the documentation in this case.
>

Thanks for the patch and the reproducer. I could reproduce it, and I
have a few observations/doubts.

> There are two cases where this happens.
>
> 1/ When creating a logical slot with an exported or used snapshot, the
> slot holds back the data xmin through its in-memory effective xmin
> alone, never the one saved to disk and shown in the view. The slot
> creation waits for the transactions with an assigned XID that are
> running when it starts, so a long running or prepared transaction can
> keep a slot in this state for a long time [3]. Table synchronization
> workers in logical replication create their slots this way too, though
> they hold the state only for the slot creation.
>

Agreed that this is a real blind spot. While the slot waits for older
transactions, VACUUM is held back but the slot's xmin is NULL, and no
backend_xmin in pg_stat_activity matches the cutoff (I tested the
default EXPORT_SNAPSHOT). The walsender holds no regular snapshot here
by design, so the slot's effective_xmin is the only thing holding the
rows.

That said, I'm not sure about showing two different values in the same
column. Currently xmin is filled from data.xmin, which for logical
slots is never set. It's only written for physical slots
(hot_standby_feedback, pg_conflict_detection), and logical decoding
only ever advances catalog_xmin. With the patch, xmin would sometimes
be the saved value and sometimes an in-memory one that disappears when
the slot is released, with no way for the user to tell which.
Monitoring queries may also assume that a non-NULL xmin means a
physical slot.

I understand the docs define xmin as the oldest transaction the slot
needs retained, which the patch matches. If that's the main concern,
maybe clarifying the docs is an option too.

If the goal is mainly to let users find what is holding back VACUUM,
wouldn't [1] be sufficient? It reports the holder in VACUUM's output
and matches slots on effective_xmin. With v10 from [1] applied, your
testcase gives:

tuples: 0 removed, 1000 remain, 1000 are dead but not yet removable
removable cutoff: 670, which was 3 XIDs old when operation ended
removable cutoff was held back by: replication slot (slot name = s)

Is there another reason to change the view that I'm missing for this case?

> 2/ In PG19 and later, REPACK (CONCURRENTLY) creates its temporary slot
> in the same way and keeps it in this state for the whole command.
>
> I would like to fix this by reporting the slot's effective xmin in
> pg_replication_slots when the saved xmin is not set. I thought of
> adding new columns for the effective values, but they would mostly be
> the same as the existing ones. Outside the above cases, they differ
> only for a moment while a new value is being saved, and catalog_xmin
> is never unset while its effective value is set.
>

Here I don't think the slot is ever the only holder. The backend
running REPACK executes it as a normal SQL statement, with a
transaction snapshot taken before the decoding worker computes the
slot's xmin, and it keeps that snapshot while the slot exists. The
worker also runs inside a transaction, so it isn't marked
PROC_IN_LOGICAL_DECODING, and its own xmin counts too.

For example, using the same pattern as your test:
-- session 1
BEGIN; DELETE FROM t; -- xid 100
-- session 2
BEGIN; SELECT pg_current_xact_id(); -- xid 101
-- session 3, worker waits for 100 and 101
REPACK (CONCURRENTLY) r;
-- session 1
COMMIT;

Now the REPACK backend and the decoding worker both show backend_xmin
= 100 in pg_stat_activity, the same as the pg_repack_<pid> slot's
catalog_xmin and VACUUM's removable cutoff. With [1]'s patch, VACUUM
(VERBOSE) reported "transaction holding snapshot (pid = <worker
pid>)", not the slot.

In a separate run with no other transactions open, I paused REPACK
after the initial copy using the existing injection point
repack-concurrently-before-lock. There the REPACK backend showed
backend_xid = backend_xmin = the slot's catalog_xmin = the cutoff, and
VACUUM (VERBOSE) again reported "transaction (pid = <REPACK backend
pid>)".

Example:
postgres=# SELECT pid, backend_type, wait_event, backend_xid, backend_xmin
FROM pg_stat_activity
WHERE backend_type IN ('client backend', 'REPACK decoding worker')
AND pid <> pg_backend_pid();
pid | backend_type | wait_event |
backend_xid | backend_xmin
-------+------------------------+---------------------------------+-------------+--------------
87267 | client backend | repack-concurrently-before-lock |
673 | 673
87524 | REPACK decoding worker | WaitForWalFlush |
|
(2 rows)

postgres=# SELECT slot_name, xmin, catalog_xmin FROM pg_replication_slots
WHERE slot_name LIKE 'pg_repack%';
slot_name | xmin | catalog_xmin
-----------------+------+--------------
pg_repack_87524 | | 673
(1 row)

VACUUM (VERBOSE):
removable cutoff: 673, which was 3 XIDs old when operation ended
removable cutoff was held back by: transaction (pid = 87267)
~~~

So in both phases of REPACK (CONCURRENTLY) a visible backend holds the
same xmin as the slot. The invisible case seems limited to the
snapshot-building window in case 1 (and, per your note, tablesync,
though I only tested).

Am I missing a REPACK scenario where the slot alone holds the horizon?

[1] https://postgr.es/m/CAOzEurQwpdKzfvoHNoGSon=B7gtXfirAZvK9G6x=U6Qp_nNANg@mail.gmail.com

--
Thanks,
Nisha