Show effective xmin in pg_replication_slots when xmin is not set

On Mon, Oct 5, 2026 at 6:00 AM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.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]/messages/by-id/CAOzEurQwpdKzfvoHNoGSon=B7gtXfirAZvK9G6x=U6Qp_nNANg@mail.gmail.com be sufficient? It reports the holder in VACUUM's output
and matches slots on effective_xmin. With v10 from [1]/messages/by-id/CAOzEurQwpdKzfvoHNoGSon=B7gtXfirAZvK9G6x=U6Qp_nNANg@mail.gmail.com 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]/messages/by-id/CAOzEurQwpdKzfvoHNoGSon=B7gtXfirAZvK9G6x=U6Qp_nNANg@mail.gmail.com'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]: /messages/by-id/CAOzEurQwpdKzfvoHNoGSon=B7gtXfirAZvK9G6x=U6Qp_nNANg@mail.gmail.com

--
Thanks,
Nisha

Hi,

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.

That's right, this is a gap. The pg_replication_slots.xmin column should also
show the in-memory xmin used while initializing logical decoding with a full
snapshot; otherwise it is not obvious from the slot view that this slot is one
of the things that can be holding the VACUUM cutoff back.

+               else if (slot_contents.effective_xmin != InvalidTransactionId)
+                       values[i++] = TransactionIdGetDatum(...);

From what I can tell, we should only enter this fallback for normal logical
decoding slots while initializing a full snapshot. In the other cases,
data.xmin and
effective_xmin are set together.

The patch overall LGTM, and closes the documentation gap.

I checked that the first case reproduces on all supported branches, so
I think we can backpatch the fix. Please find the attached v1 patch.

Thoughts?

I think backpatching is good here.

I was debating whether we should add a test for this, but the test would need
concurrent sessions, so it would likely have to be a Perl TAP test. I am not
sure that is worth it for this case.

So in both phases of REPACK (CONCURRENTLY) a visible backend holds the
same xmin as the slot.

Right, there can be multiple holders for the same cutoff. The
pg_xmin_horizon reporting work being done here [2]/messages/by-id/CALj2ACV9FYmK9nHZ+DtNkOvN_z8uMvWF+MwsMgJu2V686vuYHg@mail.gmail.com will be the
appropriate utility to identify all contributors holding the cutoff xmin back.

[2]: /messages/by-id/CALj2ACV9FYmK9nHZ+DtNkOvN_z8uMvWF+MwsMgJu2V686vuYHg@mail.gmail.com

--
Sami Imseih
Amazon Web Services (AWS)

Hi,

On 2026-10-04 17:30:00 -0700, Bharath Rupireddy wrote:

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.

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.

That seems like a bogus argument to me. Why don't we also include any other
xids in the whole system then, given that we wait for those xids?

I strongly am against making it impossible to understand what values the slot
actually contains. Changing what value is shows without any indication of that
seems like a terrible idea to me.

If you want to argue for showing more columns in pg_stat_replication slots,
maybe, maybe, but faking up values: no.

Greetings,

Andres Freund