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.
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. 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. 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? [1] https://postgr.es/m/CALj2ACVRpA1UnZx%3Ds0Ljsn01FcsEHEfj1mQXvQn69eO3Nq2YOA%40mail.gmail.com [2] https://postgr.es/m/calj2acv9fymk9nhz+dtnkovn_z8umvwf+mwsmgju2v686vu...@mail.gmail.com [3] -- session 1 CREATE TABLE t AS SELECT generate_series(1, 1000) AS a; BEGIN; DELETE FROM t; -- session 2 BEGIN; SELECT pg_current_xact_id(); -- session 3, waits for sessions 1 and 2 psql "dbname=postgres replication=database" -c "CREATE_REPLICATION_SLOT s LOGICAL pgoutput" -- session 1 COMMIT; -- xmin is NULL SELECT slot_name, xmin, catalog_xmin FROM pg_replication_slots; -- only session 2, newer than session 1 SELECT pid, backend_xid FROM pg_stat_activity WHERE backend_xid IS NOT NULL; -- 1000 dead rows not removable, cutoff is session 1's xid VACUUM (VERBOSE) t; -- session 2 -- slot creation finishes COMMIT; -- session 1 -- 1000 dead rows removed VACUUM (VERBOSE) t; -- Bharath Rupireddy Amazon Web Services: https://aws.amazon.com
v1-0001-Show-effective-xmin-in-pg_replication_slots-when-.patch
Description: Binary data
