Thank you for the review!
On Tue, Jun 9, 2026 at 1:36 AM Sami Imseih <[email protected]> wrote:
> 1/ Reporting the GID of a prepared transaction.
>
> This seems unnecessary. The logged xid is sufficient since a DBA
> can look up the GID via pg_prepared_xacts using the "transaction"
> column. I think we can remove GetPreparedTransactionGid() and
> just report the xid.
I'd prefer to keep the GID. pg_prepared_xacts is volatile, so by the
time someone analyzes the VACUUM log, the transaction is usually gone.
Logging the GID makes retrospective investigation much easier for
DBAs. Given the minimal overhead, I've kept it in v6.
> 2/ Reporting the application_name of the standby.
>
> This could be valuable assuming the user sets application_name in the
> primary_conninfo string to make the standby distinguishable. The
> documentation [1] says this "should be set" for physical standbys,
> and in practice most deployments do so since
> synchronous_standby_names matches on it.
>
> However, instead of GetStandbyAppname(), can we use
> pgstat_get_beentry_by_proc_number() to get the application name and
> set it in dst->name while holding the shared ProcArrayLock? This is
> safe if we call pgstat_fetch_stat_numbackends() beforehand to prime
> the local backend status cache. The subsequent lookup is just a
> bsearch on local memory, so it adds negligible time under the lock.
Adopted, thanks. This is much cleaner than the hand-rolled scan. I
record a proc_number during the scan — for both the ProcArray case and
the slot-based standby case — and look the application_name up from
the walsender's backend status entry.
One difference from your sketch is that the lookup runs after all
locks are released, not while holding ProcArrayLock, so there is no
need to prime the snapshot beforehand and no pgstat work happens under
the lock. Keying on proc_number instead of pid makes it robust against
pid reuse, and I kept an st_procpid check as a sanity guard.
GetStandbyAppname() is removed.
> 3/ Over-allocation in GetXidHorizonBlockers().
>
> + /*
> + * Allocate enough space for every PGPROC plus all replication slots.
> This
> + * is a generous upper bound (typically only 0-2 entries are returned),
> + * but keeps the logic simple for a diagnostic function that runs
> + * infrequently.
> + */
> + max_blockers = arrayP->maxProcs + max_replication_slots;
> + result = palloc0_array(XidHorizonBlocker, max_blockers);
>
> This looks wasteful, especially with large max_connections. In
> practice only 1-3 entries are returned. Can we start with a small
> allocation (say 8) and repalloc when needed?
The other ProcArray scanners (GetCurrentVirtualXIDs,
GetConflictingVirtualXIDs) pre-size their result array to maxProcs and
allocate before taking ProcArrayLock, precisely so the scan never
allocates under the lock. Growing on demand would repalloc under
ProcArrayLock, so I kept the pre-sized allocation to match them.
I think the real issue was the per-entry size rather than the entry
count. The scan now collects into a lean candidate struct that does
not carry the inline name[Max(GIDSIZE, NAMEDATALEN)], so each entry is
~20 bytes, on par with the sibling scanners (VirtualTransactionId is
8). The array is still pre-sized to maxProcs and allocated before
ProcArrayLock, exactly as
GetCurrentVirtualXIDs()/GetConflictingVirtualXIDs() do, so we keep
avoiding any allocation under the lock and avoid repalloc-on-demand
under it.
The name (prepared-xact GID, standby application_name or slot name) is
resolved only for the one blocker that is reported, in a new
FillXidHorizonBlocker() that runs after the scan. A nice side effect
is that the per-blocker enrichment — GetPreparedTransactionGid() under
TwoPhaseStateLock, and the standby application_name lookup via
pgstat_get_beentry_by_proc_number() — now happens once for the
selected blocker instead of for every candidate. The slot-name lookup
reuses the existing ReplicationSlotName(index, &name).
This also keeps the infrastructure friendly to the future SQL-callable
view you mentioned: FillXidHorizonBlocker() is the reusable per-row
enrichment step, so a view that lists every blocker would just call it
per row, while the VACUUM log path calls it once.
> 4/ Simplify the loop body by removing candidate_* temporaries.
>
> The values we set in dst don't need to be tracked in separate
> variables. If dst gets populated for the proc, we can just do:
>
> ```
> if (dst)
> {
> dst->pid = proc->pid;
> dst->xid = horizon;
> dst->proc_number = pgprocno;
> }
> ```
>
> at the end of each iteration.
Done.
> 5/ GetXidHorizonBlocker() priority scan.
>
> +/*
> + * Get the highest-priority blocker holding back the xid horizon.
> + *
> + * Returns true and stores the blocker in *blocker if any are found.
> + */
>
> The priority scan makes sense and addresses the concern I raised
> earlier [2] about reporting the wrong blocker due to ProcArray
> ordering. Without it we report whichever blocker happens to come
> first in ProcArray order. If an xmin-holder sits at a lower index
> than the xid-holder, we would report the less actionable one. The
> xid-holder is more useful to surface because killing that single
> transaction advances the horizon, whereas xmin-holders only matter
> once all of them release.
>
> One small optimization: exit the loop early once we find an xid-match
> type, since nothing can beat it:
>
> for (int i = 0; i < nblockers; i++)
> {
> if (best == NULL || blockers[i].type < best->type)
> {
> best = &blockers[i];
>
> /* xid-match types can't be beaten, stop early */
> if (best->type <= XHB_PREPARED_TRANSACTION)
> break;
> }
> }
Added. The scan now breaks as soon as best->type <= XHB_PREPARED_TRANSACTION.
> Finally, the infrastructure added here (GetXidHorizonBlockers,
> XidHorizonBlocker struct, the enum-based priority) will also underpin
> the future SQL-callable view for real-time blocker reporting, so I
> think what is being done here is good for that case also. I would
> also think that GetXidHorizonBlockers() and friends should be a
> separate patch, and the vacuum logging and future SQL view work can
> build off of it.
Agreed. v6 is split into two patches:
0001 - GetXidHorizonBlockers()/GetXidHorizonBlocker(), the
XidHorizonBlocker struct and the priority enum, which is the reusable
infrastructure.
0002 - the VACUUM and autovacuum logging that consumes it.
On Thu, May 28, 2026 at 2:34 PM Shinya Kato <[email protected]> wrote:
> When hot_standby_feedback=on, the location where the standby's xmin is
> recorded on the primary depends on whether physical replication slot
> is used:
>
> - Without a replication slot: the xmin is held on the walsender's PGPROC
> - With a replication slot: the xmin is held on the replication slot itself
>
> I summarized this behavior in my blog:
> https://dev.to/shinyakato_/4-causes-of-table-bloat-in-postgresql-and-how-to-address-them-3ec9
>
> The current patch reports the latter case as XHB_REPLICATION_SLOT with
> the message "logical replication slot", which is misleading because it
> is actually a physical slot used by a standby with
> hot_standby_feedback=on. I will fix this and post an updated patch
> later.
These patches accomplish this.
--
Best regards,
Shinya Kato
NTT OSS Center
From b4d5c70fd0db9964088f8d8766bda07a51054055 Mon Sep 17 00:00:00 2001
From: Shinya Kato <[email protected]>
Date: Wed, 10 Jun 2026 09:30:46 +0900
Subject: [PATCH v6 1/2] Add infrastructure to identify what holds back the xid
horizon
Introduce GetXidHorizonBlocker() and GetXidHorizonBlockers() in
procarray.c. Given an xid horizon, these search the proc array and the
replication slots for what is preventing it from advancing: active
transactions, idle-in-transaction sessions, prepared transactions, hot
standby feedback (held either in a walsender's PGPROC or, when a physical
slot is in use, in the slot itself), and logical replication slots.
A blocker is described by the new XidHorizonBlocker struct, and the
candidates are ranked by XidHorizonBlockerType so that the single
highest-priority (root-cause) blocker can be reported. A companion
GetPreparedTransactionGid() in twophase.c resolves the GID of a blocking
prepared transaction.
This is infrastructure with no in-tree caller yet. A following patch
uses it to report the blocker in VACUUM's log output, and the same API
can underpin a future SQL-callable view that lists every blocker.
Author: Shinya Kato <[email protected]>
Reviewed-by: wenhui qiu <[email protected]>
Reviewed-by: Fujii Masao <[email protected]>
Reviewed-by: Sami Imseih <[email protected]>
Reviewed-by: Dilip Kumar <[email protected]>
Reviewed-by: Japin Li <[email protected]>
Discussion: https://postgr.es/m/CAOzEurSgy-gDtwFmEbj5+R9PL0_G3qYB6nnzJtNStyuf87VSVg@mail.gmail.com
---
src/backend/access/transam/twophase.c | 39 +++
src/backend/storage/ipc/procarray.c | 336 ++++++++++++++++++++++++++
src/include/access/twophase.h | 1 +
src/include/storage/procarray.h | 46 ++++
src/tools/pgindent/typedefs.list | 3 +
5 files changed, 425 insertions(+)
diff --git a/src/backend/access/transam/twophase.c b/src/backend/access/transam/twophase.c
index 1035e8b3fc7..69d1d82b342 100644
--- a/src/backend/access/transam/twophase.c
+++ b/src/backend/access/transam/twophase.c
@@ -2822,6 +2822,45 @@ LookupGXactBySubid(Oid subid)
return found;
}
+/*
+ * GetPreparedTransactionGid
+ * Get the GID for the prepared transaction with the given XID.
+ *
+ * Returns true when a matching prepared transaction is found. gid will be
+ * set to an empty string when no match is found.
+ */
+bool
+GetPreparedTransactionGid(TransactionId xid, char gid[GIDSIZE])
+{
+ bool found = false;
+
+ Assert(TransactionIdIsValid(xid));
+
+ gid[0] = '\0';
+
+ if (max_prepared_xacts == 0 || TwoPhaseState == NULL)
+ return false;
+
+ LWLockAcquire(TwoPhaseStateLock, LW_SHARED);
+ for (int i = 0; i < TwoPhaseState->numPrepXacts; i++)
+ {
+ GlobalTransaction gxact = TwoPhaseState->prepXacts[i];
+
+ if (!gxact->valid)
+ continue;
+
+ if (!TransactionIdEquals(XidFromFullTransactionId(gxact->fxid), xid))
+ continue;
+
+ strlcpy(gid, gxact->gid, GIDSIZE);
+ found = true;
+ break;
+ }
+ LWLockRelease(TwoPhaseStateLock);
+
+ return found;
+}
+
/*
* TwoPhaseGetOldestXidInCommit
* Return the oldest transaction ID from prepared transactions that are
diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c
index f540bb6b23f..2c1f7410c05 100644
--- a/src/backend/storage/ipc/procarray.c
+++ b/src/backend/storage/ipc/procarray.c
@@ -58,11 +58,13 @@
#include "pgstat.h"
#include "postmaster/bgworker.h"
#include "port/pg_lfind.h"
+#include "replication/slot.h"
#include "storage/proc.h"
#include "storage/procarray.h"
#include "storage/procsignal.h"
#include "storage/subsystems.h"
#include "utils/acl.h"
+#include "utils/backend_status.h"
#include "utils/builtins.h"
#include "utils/injection_point.h"
#include "utils/lsyscache.h"
@@ -282,6 +284,28 @@ typedef enum KAXCompressReason
KAX_STARTUP_PROCESS_IDLE, /* startup process is about to sleep */
} KAXCompressReason;
+/*
+ * A candidate blocker collected during the ProcArray/replication-slot scan.
+ *
+ * This is deliberately small. Like the other ProcArray scanners we size the
+ * result array for the worst case (one entry per PGPROC plus one per
+ * replication slot), so a fat per-entry struct would waste a lot of memory at
+ * high max_connections. The human-readable name of a blocker (prepared-xact
+ * GID, standby application_name or slot name) is comparatively large but only
+ * needed for the single blocker that is actually reported, so it is resolved
+ * later by FillXidHorizonBlocker() rather than stored for every candidate.
+ */
+typedef struct XidHorizonBlockerCandidate
+{
+ XidHorizonBlockerType type;
+ TransactionId xid; /* the blocking xid/xmin */
+ int pid; /* backend pid (0 for prepared xacts and slots) */
+ ProcNumber proc_number; /* walsender proc number for hot standby
+ * feedback; INVALID_PROC_NUMBER otherwise */
+ int slot_index; /* replication_slots[] index for slot blockers;
+ * -1 otherwise */
+} XidHorizonBlockerCandidate;
+
static PGPROC *allProcs;
/*
@@ -1999,6 +2023,318 @@ GetReplicationHorizons(TransactionId *xmin, TransactionId *catalog_xmin)
*catalog_xmin = horizons.slot_catalog_xmin;
}
+/*
+ * Return XidHorizonBlockerType for a backend whose xid matches the horizon.
+ */
+static inline XidHorizonBlockerType
+XidHorizonBlockerTypeForBackend(const PGPROC *proc)
+{
+ if (proc->wait_event_info == WAIT_EVENT_CLIENT_READ)
+ return XHB_IDLE_IN_TRANSACTION;
+ return XHB_ACTIVE_TRANSACTION;
+}
+
+/*
+ * Return XidHorizonBlockerType for a backend whose xmin matches the horizon.
+ */
+static inline XidHorizonBlockerType
+XidHorizonBlockerTypeForXminBackend(const PGPROC *proc)
+{
+ if (proc->wait_event_info == WAIT_EVENT_CLIENT_READ)
+ return XHB_XMIN_IDLE_IN_TRANSACTION;
+ return XHB_XMIN_ACTIVE_TRANSACTION;
+}
+
+/*
+ * Find the blockers that are holding back the given xid horizon.
+ *
+ * This function searches for what is preventing the given horizon from being
+ * advanced to allow removal of dead tuples. It checks:
+ * 1. Active transactions (running statements)
+ * 2. Idle-in-transaction sessions
+ * 3. Prepared transactions
+ * 4. Hot standby feedback
+ * 5. Replication slots (physical or logical)
+ *
+ * Hot standby feedback deserves a note, because where the standby's xmin is
+ * stored depends on whether the connection uses a replication slot (see
+ * ProcessStandbyHSFeedbackMessage):
+ *
+ * - Without a slot, the xmin is held in the walsender's PGPROC and is found by
+ * the ProcArray scan below as XHB_HOT_STANDBY_FEEDBACK.
+ * - With a physical slot, the xmin is held in the slot (the walsender's PGPROC
+ * xmin is reset to invalid), so the ProcArray scan does not see it. The slot
+ * scan finds it instead: if a standby is currently connected (the slot is
+ * active) it is still reported as XHB_HOT_STANDBY_FEEDBACK, otherwise the
+ * persisted reservation is reported as XHB_PHYSICAL_REPLICATION_SLOT.
+ *
+ * Logical slots reserve catalog_xmin and are reported as
+ * XHB_LOGICAL_REPLICATION_SLOT.
+ *
+ * Because the horizon was computed earlier, the original blocker may have
+ * already committed by the time this function runs. The result is therefore
+ * best-effort: it may return a different blocker, or no blocker at all.
+ *
+ * Returns a palloc'd array of candidate blockers and stores the number of
+ * entries in *nblockers. The blocker names are not resolved here; the caller
+ * does that for the blocker it reports (see FillXidHorizonBlocker). The array
+ * may be empty if no blocker is found.
+ */
+static XidHorizonBlockerCandidate *
+GetXidHorizonBlockers(TransactionId horizon, int *nblockers)
+{
+ ProcArrayStruct *arrayP = procArray;
+ TransactionId *other_xids = ProcGlobal->xids;
+ XidHorizonBlockerCandidate *result;
+ int count = 0;
+ int max_blockers;
+
+ Assert(TransactionIdIsValid(horizon));
+ Assert(nblockers != NULL);
+
+ /*
+ * Size the result array for the worst case (one entry per PGPROC plus one
+ * per replication slot) and allocate it before acquiring ProcArrayLock, so
+ * the scan below never has to allocate while holding the lock. The other
+ * ProcArray scanners such as GetCurrentVirtualXIDs() size their result
+ * space the same way. Only 0-2 entries are returned in practice, and each
+ * entry is small because the blocker name is resolved later, not here.
+ */
+ max_blockers = arrayP->maxProcs + max_replication_slots;
+ result = palloc_array(XidHorizonBlockerCandidate, max_blockers);
+
+ LWLockAcquire(ProcArrayLock, LW_SHARED);
+
+ for (int index = 0; index < arrayP->numProcs; index++)
+ {
+ int pgprocno = arrayP->pgprocnos[index];
+ PGPROC *proc = &allProcs[pgprocno];
+ int8 statusFlags = ProcGlobal->statusFlags[index];
+ TransactionId proc_xid;
+ TransactionId proc_xmin;
+ XidHorizonBlockerCandidate *dst = NULL;
+
+ /*
+ * Skip over backends either vacuuming (which is ok with rows being
+ * removed, as long as pg_subtrans is not truncated), doing logical
+ * decoding (which manages xmin separately, check below), or myself.
+ */
+ if (statusFlags & (PROC_IN_VACUUM | PROC_IN_LOGICAL_DECODING) ||
+ proc == MyProc)
+ continue;
+
+ /* Fetch xid just once - see GetNewTransactionId */
+ proc_xid = UINT32_ACCESS_ONCE(other_xids[index]);
+ proc_xmin = UINT32_ACCESS_ONCE(proc->xmin);
+
+ /*
+ * Candidates are collected in ProcArray order; callers can reorder if
+ * needed. Only the blocker type differs between the cases below; the
+ * common fields are filled in once afterwards.
+ */
+ if (TransactionIdEquals(proc_xid, horizon))
+ {
+ /* This proc's xid matches the horizon (the root cause) */
+ dst = &result[count++];
+ if (proc->pid == 0)
+ dst->type = XHB_PREPARED_TRANSACTION;
+ else
+ dst->type = XidHorizonBlockerTypeForBackend(proc);
+ }
+ else if (TransactionIdEquals(proc_xmin, horizon))
+ {
+ /* This proc's xmin matches the horizon (held back by the above) */
+ dst = &result[count++];
+ if (statusFlags & PROC_AFFECTS_ALL_HORIZONS)
+ dst->type = XHB_HOT_STANDBY_FEEDBACK;
+ else
+ dst->type = XidHorizonBlockerTypeForXminBackend(proc);
+ }
+
+ if (dst)
+ {
+ dst->pid = proc->pid;
+ dst->xid = horizon;
+ dst->proc_number = pgprocno;
+ dst->slot_index = -1;
+ }
+ }
+
+ LWLockRelease(ProcArrayLock);
+
+ /*
+ * Also check replication slots.
+ *
+ * A physical slot reserves xmin on behalf of a standby using hot standby
+ * feedback. If a standby is currently connected we attribute the
+ * reservation to that feedback (and record the walsender pid so its
+ * application_name can be looked up below); otherwise it is a persisted
+ * physical-slot reservation with no connected standby. A logical slot
+ * reserves catalog_xmin for logical decoding.
+ */
+ if (max_replication_slots > 0)
+ {
+ LWLockAcquire(ReplicationSlotControlLock, LW_SHARED);
+
+ for (int i = 0; i < max_replication_slots; i++)
+ {
+ ReplicationSlot *s = &ReplicationSlotCtl->replication_slots[i];
+ TransactionId slot_xmin;
+ TransactionId slot_catalog_xmin;
+ ProcNumber active_proc;
+ XidHorizonBlockerCandidate *dst;
+
+ if (!s->in_use)
+ continue;
+
+ SpinLockAcquire(&s->mutex);
+ slot_xmin = s->data.xmin;
+ slot_catalog_xmin = s->data.catalog_xmin;
+ active_proc = s->active_proc;
+ SpinLockRelease(&s->mutex);
+
+ if (!TransactionIdEquals(slot_xmin, horizon) &&
+ !TransactionIdEquals(slot_catalog_xmin, horizon))
+ continue;
+
+ dst = &result[count++];
+ dst->xid = TransactionIdIsValid(slot_xmin) ?
+ slot_xmin :
+ slot_catalog_xmin;
+ dst->slot_index = i;
+
+ if (SlotIsPhysical(s) && active_proc != INVALID_PROC_NUMBER)
+ {
+ /* Connected standby: report as hot standby feedback. */
+ dst->type = XHB_HOT_STANDBY_FEEDBACK;
+ dst->pid = GetPGProcByNumber(active_proc)->pid;
+ dst->proc_number = active_proc;
+ }
+ else
+ {
+ /*
+ * A physical slot with no connected standby, or a logical
+ * slot. The slot name is resolved later from slot_index.
+ */
+ dst->type = SlotIsPhysical(s) ?
+ XHB_PHYSICAL_REPLICATION_SLOT :
+ XHB_LOGICAL_REPLICATION_SLOT;
+ dst->pid = 0;
+ dst->proc_number = INVALID_PROC_NUMBER;
+ }
+ }
+
+ LWLockRelease(ReplicationSlotControlLock);
+ }
+
+ *nblockers = count;
+ return result;
+}
+
+/*
+ * Resolve a scanned candidate into a fully-populated blocker.
+ *
+ * The scan deliberately leaves the (comparatively large) blocker name out of
+ * every candidate; it is filled in here for the single blocker that is
+ * actually reported, after all scan locks have been released:
+ *
+ * - prepared transaction: look up the GID from the xid;
+ * - hot standby feedback: look up the standby's application_name from the
+ * walsender's backend status entry. Matching on proc_number rather than pid
+ * avoids being fooled by pid reuse, and the lookup is a bsearch over a
+ * process-local snapshot; the st_procpid check guards against the proc being
+ * reused since the scan;
+ * - replication slot: copy the slot name (best-effort: empty if the slot has
+ * since been dropped).
+ */
+static void
+FillXidHorizonBlocker(const XidHorizonBlockerCandidate *cand,
+ XidHorizonBlocker *blocker)
+{
+ blocker->type = cand->type;
+ blocker->xid = cand->xid;
+ blocker->pid = cand->pid;
+ blocker->proc_number = cand->proc_number;
+ blocker->name[0] = '\0';
+
+ switch (cand->type)
+ {
+ case XHB_PREPARED_TRANSACTION:
+ GetPreparedTransactionGid(cand->xid, blocker->name);
+ break;
+
+ case XHB_HOT_STANDBY_FEEDBACK:
+ {
+ PgBackendStatus *beentry;
+
+ beentry = pgstat_get_beentry_by_proc_number(cand->proc_number);
+ if (beentry != NULL && beentry->st_procpid == cand->pid &&
+ beentry->st_appname != NULL && beentry->st_appname[0] != '\0')
+ strlcpy(blocker->name, beentry->st_appname,
+ sizeof(blocker->name));
+ break;
+ }
+
+ case XHB_PHYSICAL_REPLICATION_SLOT:
+ case XHB_LOGICAL_REPLICATION_SLOT:
+ {
+ NameData slotname;
+
+ if (ReplicationSlotName(cand->slot_index, &slotname))
+ strlcpy(blocker->name, NameStr(slotname),
+ sizeof(blocker->name));
+ break;
+ }
+
+ default:
+ break;
+ }
+}
+
+/*
+ * Get the highest-priority blocker holding back the xid horizon.
+ *
+ * Returns true and stores the blocker in *blocker if any are found.
+ */
+bool
+GetXidHorizonBlocker(TransactionId horizon, XidHorizonBlocker *blocker)
+{
+ XidHorizonBlockerCandidate *blockers;
+ XidHorizonBlockerCandidate *best = NULL;
+ int nblockers;
+
+ Assert(TransactionIdIsValid(horizon));
+ Assert(blocker != NULL);
+
+ blockers = GetXidHorizonBlockers(horizon, &nblockers);
+ for (int i = 0; i < nblockers; i++)
+ {
+ if (best == NULL || blockers[i].type < best->type)
+ {
+ best = &blockers[i];
+
+ /*
+ * xid-match types are the highest priority (the root cause holding
+ * the horizon), so nothing can outrank them; stop once we find one.
+ */
+ if (best->type <= XHB_PREPARED_TRANSACTION)
+ break;
+ }
+ }
+
+ /*
+ * Resolve the name of the selected blocker only. The expensive GID /
+ * application_name / slot-name lookups thus run once for the reported
+ * blocker instead of for every candidate collected above.
+ */
+ if (best != NULL)
+ FillXidHorizonBlocker(best, blocker);
+
+ pfree(blockers);
+
+ return (best != NULL);
+}
+
/*
* GetMaxSnapshotXidCount -- get max size for snapshot XID array
*
diff --git a/src/include/access/twophase.h b/src/include/access/twophase.h
index 1d2ff42c9b7..fc7294a4e25 100644
--- a/src/include/access/twophase.h
+++ b/src/include/access/twophase.h
@@ -70,6 +70,7 @@ extern void TwoPhaseTransactionGid(Oid subid, TransactionId xid, char *gid_res,
int szgid);
extern bool LookupGXactBySubid(Oid subid);
+extern bool GetPreparedTransactionGid(TransactionId xid, char gid[GIDSIZE]);
extern TransactionId TwoPhaseGetOldestXidInCommit(void);
#endif /* TWOPHASE_H */
diff --git a/src/include/storage/procarray.h b/src/include/storage/procarray.h
index d718a5b542f..ece8a12b7af 100644
--- a/src/include/storage/procarray.h
+++ b/src/include/storage/procarray.h
@@ -14,10 +14,53 @@
#ifndef PROCARRAY_H
#define PROCARRAY_H
+#include "access/xact.h"
+#include "storage/lock.h"
+#include "storage/procnumber.h"
#include "storage/standby.h"
#include "utils/relcache.h"
#include "utils/snapshot.h"
+/*
+ * Type of blocker that is holding back the xid horizon.
+ * Listed in priority order from highest to lowest. Blockers whose xid
+ * matches the horizon (the root cause) are listed before blockers whose
+ * xmin matches (held back by the root cause). Within each group, active
+ * transactions are listed first because they are the most actionable for
+ * the DBA (the running query can be identified and cancelled).
+ */
+typedef enum XidHorizonBlockerType
+{
+ XHB_NONE = 0,
+ /* xid-match types (horizon == proc's xid) */
+ XHB_ACTIVE_TRANSACTION, /* backend running a statement */
+ XHB_IDLE_IN_TRANSACTION, /* backend idle in transaction */
+ XHB_PREPARED_TRANSACTION, /* prepared (two-phase) transaction */
+ /* xmin-match types (horizon == proc's xmin or slot's xmin) */
+ XHB_XMIN_ACTIVE_TRANSACTION, /* backend running a statement */
+ XHB_XMIN_IDLE_IN_TRANSACTION, /* backend idle in transaction */
+ XHB_HOT_STANDBY_FEEDBACK, /* connected standby with hot_standby_feedback */
+ XHB_PHYSICAL_REPLICATION_SLOT, /* physical slot reserving xmin (no
+ * connected standby) */
+ XHB_LOGICAL_REPLICATION_SLOT, /* logical replication slot */
+} XidHorizonBlockerType;
+
+/*
+ * Information about a blocker that is holding back the xid horizon.
+ */
+typedef struct XidHorizonBlocker
+{
+ XidHorizonBlockerType type;
+ TransactionId xid; /* the blocking xid/xmin */
+ int pid; /* backend pid (0 for prepared xacts and
+ * slots) */
+ ProcNumber proc_number; /* backend's proc number, used to look up its
+ * application_name; INVALID_PROC_NUMBER when
+ * there is no associated backend */
+ /* large enough for prepared-txn GID or replication slot name */
+ char name[Max(GIDSIZE, NAMEDATALEN)];
+} XidHorizonBlocker;
+
extern void ProcArrayAdd(PGPROC *proc);
extern void ProcArrayRemove(PGPROC *proc, TransactionId latestXid);
@@ -98,4 +141,7 @@ extern void ProcArraySetReplicationSlotXmin(TransactionId xmin,
extern void ProcArrayGetReplicationSlotXmin(TransactionId *xmin,
TransactionId *catalog_xmin);
+extern bool GetXidHorizonBlocker(TransactionId horizon,
+ XidHorizonBlocker *blocker);
+
#endif /* PROCARRAY_H */
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 8cf40c87043..356e730efb0 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3544,6 +3544,9 @@ XactLockTableWaitInfo
XidBoundsViolation
XidCacheStatus
XidCommitStatus
+XidHorizonBlocker
+XidHorizonBlockerCandidate
+XidHorizonBlockerType
XidStatus
XmlExpr
XmlExprOp
--
2.47.3
From e3932cabf49d44a6bb79561100ab6b824adad9c3 Mon Sep 17 00:00:00 2001
From: Shinya Kato <[email protected]>
Date: Wed, 10 Jun 2026 09:30:46 +0900
Subject: [PATCH v6 2/2] Report oldest xmin blocker when VACUUM cannot remove
tuples
When VACUUM encounters recently-dead tuples that cannot be removed, it
is often unclear what is preventing the xid horizon from advancing.
Using the infrastructure added in the previous patch, VACUUM (VERBOSE)
and log_autovacuum output now report the highest-priority blocker that
is holding back OldestXmin, with identifying details: the backend pid,
the prepared transaction GID, the standby application_name, or the
replication slot name.
Because the horizon was computed earlier, the result is best-effort: the
original blocker may have already committed by the time it is looked up,
in which case a different blocker or none at all may be reported.
Author: Shinya Kato <[email protected]>
Reviewed-by: wenhui qiu <[email protected]>
Reviewed-by: Fujii Masao <[email protected]>
Reviewed-by: Sami Imseih <[email protected]>
Reviewed-by: Dilip Kumar <[email protected]>
Reviewed-by: Japin Li <[email protected]>
Discussion: https://postgr.es/m/CAOzEurSgy-gDtwFmEbj5+R9PL0_G3qYB6nnzJtNStyuf87VSVg@mail.gmail.com
---
src/backend/access/heap/vacuumlazy.c | 64 +++
src/test/modules/test_misc/meson.build | 1 +
.../test_misc/t/014_log_vacuum_blockers.pl | 418 ++++++++++++++++++
3 files changed, 483 insertions(+)
create mode 100644 src/test/modules/test_misc/t/014_log_vacuum_blockers.pl
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 39395aed0d5..c168f52bf8c 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -151,6 +151,7 @@
#include "storage/freespace.h"
#include "storage/latch.h"
#include "storage/lmgr.h"
+#include "storage/procarray.h"
#include "storage/read_stream.h"
#include "utils/injection_point.h"
#include "utils/lsyscache.h"
@@ -1074,6 +1075,69 @@ heap_vacuum_rel(Relation rel, const VacuumParams *params,
vacrel->tuples_deleted,
(int64) vacrel->new_rel_tuples,
vacrel->recently_dead_tuples);
+ if (vacrel->recently_dead_tuples > 0)
+ {
+ XidHorizonBlocker blocker;
+
+ if (GetXidHorizonBlocker(vacrel->cutoffs.OldestXmin, &blocker))
+ {
+ switch (blocker.type)
+ {
+ case XHB_ACTIVE_TRANSACTION:
+ appendStringInfo(&buf,
+ _("oldest xmin blocker: active transaction (pid = %d)\n"),
+ blocker.pid);
+ break;
+ case XHB_IDLE_IN_TRANSACTION:
+ appendStringInfo(&buf,
+ _("oldest xmin blocker: idle in transaction (pid = %d)\n"),
+ blocker.pid);
+ break;
+ case XHB_XMIN_ACTIVE_TRANSACTION:
+ appendStringInfo(&buf,
+ _("oldest xmin blocker: active transaction holding snapshot (pid = %d)\n"),
+ blocker.pid);
+ break;
+ case XHB_XMIN_IDLE_IN_TRANSACTION:
+ appendStringInfo(&buf,
+ _("oldest xmin blocker: idle in transaction holding snapshot (pid = %d)\n"),
+ blocker.pid);
+ break;
+ case XHB_PREPARED_TRANSACTION:
+ if (blocker.name[0] != '\0')
+ appendStringInfo(&buf,
+ _("oldest xmin blocker: prepared transaction (gid = %s)\n"),
+ blocker.name);
+ else
+ appendStringInfo(&buf,
+ _("oldest xmin blocker: prepared transaction\n"));
+ break;
+ case XHB_HOT_STANDBY_FEEDBACK:
+ if (blocker.name[0] != '\0')
+ appendStringInfo(&buf,
+ _("oldest xmin blocker: hot standby feedback (standby name = %s, pid = %d)\n"),
+ blocker.name,
+ blocker.pid);
+ else
+ appendStringInfo(&buf,
+ _("oldest xmin blocker: hot standby feedback (pid = %d)\n"),
+ blocker.pid);
+ break;
+ case XHB_PHYSICAL_REPLICATION_SLOT:
+ appendStringInfo(&buf,
+ _("oldest xmin blocker: physical replication slot (slot name = %s)\n"),
+ blocker.name);
+ break;
+ case XHB_LOGICAL_REPLICATION_SLOT:
+ appendStringInfo(&buf,
+ _("oldest xmin blocker: logical replication slot (slot name = %s)\n"),
+ blocker.name);
+ break;
+ case XHB_NONE:
+ break;
+ }
+ }
+ }
if (vacrel->missed_dead_tuples > 0)
appendStringInfo(&buf,
_("tuples missed: %" PRId64 " dead from %u pages not removed due to cleanup lock contention\n"),
diff --git a/src/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build
index 969e90b396d..15ead367fb5 100644
--- a/src/test/modules/test_misc/meson.build
+++ b/src/test/modules/test_misc/meson.build
@@ -22,6 +22,7 @@ tests += {
't/011_lock_stats.pl',
't/012_ddlutils.pl',
't/013_temp_obj_multisession.pl',
+ 't/014_log_vacuum_blockers.pl',
],
# The injection points are cluster-wide, so disable installcheck
'runningcheck': false,
diff --git a/src/test/modules/test_misc/t/014_log_vacuum_blockers.pl b/src/test/modules/test_misc/t/014_log_vacuum_blockers.pl
new file mode 100644
index 00000000000..9c8a3404cd8
--- /dev/null
+++ b/src/test/modules/test_misc/t/014_log_vacuum_blockers.pl
@@ -0,0 +1,418 @@
+# Copyright (c) 2026, PostgreSQL Global Development Group
+#
+# Validate that VACUUM logs explain why dead tuples could not be removed.
+
+use strict;
+use warnings FATAL => 'all';
+
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Set up a cluster
+my $node = PostgreSQL::Test::Cluster->new('main');
+$node->init(allows_streaming => 'logical');
+$node->append_conf('postgresql.conf', q[
+max_prepared_transactions = 5
+]);
+$node->start;
+
+# Create the hot standby test table and prepare backup/standby early, before
+# any background psql sessions are started. On Windows, background psql
+# sessions that are terminated during earlier tests can leave file handles
+# lingering, which causes pg_ctl start for the standby to fail. By taking
+# the backup and initializing the standby here, the standby start later only
+# needs to launch pg_ctl after all those sessions have been fully cleaned up.
+$node->backup('oldestxmin_hotstandby_bkp');
+my $standby = PostgreSQL::Test::Cluster->new('oldestxmin_standby');
+$standby->init_from_backup($node, 'oldestxmin_hotstandby_bkp',
+ has_streaming => 1);
+$standby->append_conf('postgresql.conf', q[
+hot_standby_feedback = on
+wal_receiver_status_interval = 100ms
+]);
+
+# A second standby that streams through a physical replication slot, used to
+# check that hot standby feedback held via a slot is reported correctly: as
+# "hot standby feedback" while the standby is connected, and as "physical
+# replication slot" once it has disconnected but the slot still reserves xmin.
+$node->safe_psql('postgres',
+ "SELECT pg_create_physical_replication_slot('physical_slot');");
+my $slot_standby = PostgreSQL::Test::Cluster->new('oldestxmin_slot_standby');
+$slot_standby->init_from_backup($node, 'oldestxmin_hotstandby_bkp',
+ has_streaming => 1);
+$slot_standby->append_conf('postgresql.conf', q[
+primary_slot_name = 'physical_slot'
+hot_standby_feedback = on
+wal_receiver_status_interval = 100ms
+]);
+
+
+#
+# Active statement
+#
+my $active_table = 'blocker_active';
+$node->safe_psql('postgres', qq[
+CREATE TABLE $active_table(id int);
+INSERT INTO $active_table VALUES (0);
+]);
+
+my $blocker = $node->background_psql('postgres');
+my $blocker_pid = $blocker->query_safe('SELECT pg_backend_pid();');
+chomp($blocker_pid);
+
+# Start a long-running query in the background that accesses a table
+# This ensures xmin is set (pg_sleep alone doesn't require a snapshot)
+$blocker->query_until(qr//, qq[
+BEGIN;
+SELECT * FROM $active_table, pg_sleep(60);
+]);
+
+# Wait for the blocker to have xmin set
+$node->poll_query_until('postgres', qq[
+SELECT backend_xmin IS NOT NULL
+FROM pg_stat_activity
+WHERE pid = $blocker_pid;
+]);
+
+$node->safe_psql('postgres', "DELETE FROM $active_table;");
+
+my $stderr = '';
+$node->psql('postgres', "VACUUM (VERBOSE) $active_table;", stderr => \$stderr);
+like(
+ $stderr,
+ qr/oldest xmin blocker: active transaction holding snapshot \(pid = $blocker_pid\)/,
+ 'VACUUM VERBOSE reported active transaction holding snapshot as oldest xmin blocker');
+
+# Cleanup
+$node->safe_psql('postgres', qq[
+SELECT pg_terminate_backend($blocker_pid);
+DROP TABLE $active_table;
+]);
+
+
+#
+# Idle in transaction
+#
+my $idle_table = 'blocker_idle';
+$node->safe_psql('postgres', qq[
+CREATE TABLE $idle_table(id int);
+INSERT INTO $idle_table VALUES (0);
+]);
+
+my $idle_blocker = $node->background_psql('postgres');
+my $idle_blocker_pid = $idle_blocker->query_safe('SELECT pg_backend_pid();');
+chomp($idle_blocker_pid);
+
+# Set isolation level to REPEATABLE READ to ensure xmin is set
+$idle_blocker->query_safe(qq[
+BEGIN;
+SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+SELECT * FROM $idle_table;
+]);
+
+$node->safe_psql('postgres', "DELETE FROM $idle_table;");
+
+$stderr = '';
+$node->psql('postgres', "VACUUM (VERBOSE) $idle_table;", stderr => \$stderr);
+like(
+ $stderr,
+ qr/oldest xmin blocker: idle in transaction holding snapshot \(pid = $idle_blocker_pid\)/,
+ 'VACUUM VERBOSE reported idle in transaction holding snapshot as oldest xmin blocker');
+
+# Cleanup
+$idle_blocker->quit;
+$node->safe_psql('postgres', "DROP TABLE $idle_table;");
+
+
+#
+# Serializable transaction (idle in transaction)
+#
+my $serializable_table = 'blocker_serializable';
+$node->safe_psql('postgres', qq[
+CREATE TABLE $serializable_table(id int);
+INSERT INTO $serializable_table VALUES (0);
+]);
+
+my $ser_blocker = $node->background_psql('postgres');
+my $ser_blocker_pid = $ser_blocker->query_safe('SELECT pg_backend_pid();');
+chomp($ser_blocker_pid);
+
+$ser_blocker->query_safe(qq[
+BEGIN;
+SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+SELECT * FROM $serializable_table;
+]);
+
+$node->safe_psql('postgres', "DELETE FROM $serializable_table;");
+
+$stderr = '';
+$node->psql('postgres', "VACUUM (VERBOSE) $serializable_table;", stderr => \$stderr);
+like(
+ $stderr,
+ qr/oldest xmin blocker: idle in transaction holding snapshot \(pid = $ser_blocker_pid\)/,
+ 'VACUUM VERBOSE reported serializable transaction as oldest xmin blocker');
+
+# Cleanup
+$ser_blocker->quit;
+$node->safe_psql('postgres', "DROP TABLE $serializable_table;");
+
+
+#
+# Prefer xid owner over xmin match
+#
+my $prefer_table = 'blocker_prefer_xid_owner';
+$node->safe_psql('postgres', qq[
+CREATE TABLE $prefer_table(id int);
+INSERT INTO $prefer_table VALUES (0);
+]);
+
+my $xid_owner = $node->background_psql('postgres');
+my $xid_owner_pid = $xid_owner->query_safe('SELECT pg_backend_pid();');
+chomp($xid_owner_pid);
+
+$xid_owner->query_safe(qq[
+BEGIN;
+SELECT pg_current_xact_id();
+]);
+
+$node->poll_query_until('postgres', qq[
+SELECT backend_xid IS NOT NULL
+FROM pg_stat_activity
+WHERE pid = $xid_owner_pid;
+]);
+
+my $owner_xid = $node->safe_psql('postgres', qq[
+SELECT backend_xid
+FROM pg_stat_activity
+WHERE pid = $xid_owner_pid;
+]);
+chomp($owner_xid);
+
+my $xmin_holder = $node->background_psql('postgres');
+my $xmin_holder_pid = $xmin_holder->query_safe('SELECT pg_backend_pid();');
+chomp($xmin_holder_pid);
+
+# Start a long-running query that will take a snapshot after xid_owner begins
+$xmin_holder->query_until(qr//, qq[
+BEGIN;
+SELECT * FROM $prefer_table, pg_sleep(60);
+]);
+
+# Ensure xmin_holder's xmin is held back by xid_owner
+$node->poll_query_until('postgres', qq[
+SELECT backend_xmin = '$owner_xid'::xid
+FROM pg_stat_activity
+WHERE pid = $xmin_holder_pid;
+]);
+
+$node->safe_psql('postgres', "DELETE FROM $prefer_table;");
+
+$stderr = '';
+$node->psql('postgres', "VACUUM (VERBOSE) $prefer_table;", stderr => \$stderr);
+like(
+ $stderr,
+ qr/oldest xmin blocker: idle in transaction \(pid = $xid_owner_pid\)/,
+ 'VACUUM VERBOSE preferred xid owner over xmin match');
+
+# Cleanup
+$node->safe_psql('postgres', qq[
+SELECT pg_terminate_backend($xmin_holder_pid);
+SELECT pg_terminate_backend($xid_owner_pid);
+DROP TABLE $prefer_table;
+]);
+
+
+#
+# Prepared transaction
+#
+my $prepared_table = 'blocker_prepared';
+$node->safe_psql('postgres', qq[
+CREATE TABLE $prepared_table(id int);
+INSERT INTO $prepared_table VALUES (0);
+BEGIN;
+PREPARE TRANSACTION 'gx_vacuum_xmin';
+]);
+
+$node->safe_psql('postgres', "DELETE FROM $prepared_table;");
+
+$stderr = '';
+$node->psql('postgres', "VACUUM (VERBOSE) $prepared_table;", stderr => \$stderr);
+like(
+ $stderr,
+ qr/oldest xmin blocker: prepared transaction \(gid = gx_vacuum_xmin\)/,
+ 'VACUUM VERBOSE reported prepared transaction as oldest xmin blocker');
+
+# Cleanup
+$node->safe_psql('postgres', qq[
+ROLLBACK PREPARED 'gx_vacuum_xmin';
+DROP TABLE $prepared_table;
+]);
+
+
+#
+# Logical replication slot
+#
+my $slot_table = 'blocker_slot';
+$node->safe_psql('postgres', qq[
+CREATE TABLE $slot_table(id int);
+SELECT pg_create_logical_replication_slot('logical_slot', 'test_decoding');
+DROP TABLE $slot_table;
+]);
+
+$stderr = '';
+$node->psql('postgres', 'VACUUM (VERBOSE) pg_class;', stderr => \$stderr);
+like(
+ $stderr,
+ qr/oldest xmin blocker: logical replication slot \(slot name = logical_slot\)/,
+ 'VACUUM VERBOSE reported logical replication slot as oldest xmin source');
+
+# Cleanup
+$node->safe_psql('postgres', qq[
+SELECT pg_drop_replication_slot('logical_slot');
+]);
+
+
+#
+# Hot standby feedback
+#
+# The standby was already initialized from a backup taken above. Start it
+# now, after all background psql sessions from earlier tests have been fully
+# cleaned up.
+my $hs_table = 'blocker_hotstandby';
+$node->safe_psql('postgres', qq[
+CREATE TABLE $hs_table(id int);
+INSERT INTO $hs_table VALUES (0);
+]);
+
+$standby->start;
+$node->wait_for_catchup($standby, 'replay', $node->lsn('flush'));
+
+my $standby_reader = $standby->background_psql('postgres');
+my $standby_reader_pid = $standby_reader->query_safe('SELECT pg_backend_pid();');
+chomp($standby_reader_pid);
+
+$standby_reader->query_until(qr//, qq[
+BEGIN;
+SELECT * FROM $hs_table, pg_sleep(60);
+]);
+
+# Wait for hot standby feedback to be sent
+$node->poll_query_until('postgres', q[
+SELECT backend_xmin IS NOT NULL
+FROM pg_stat_replication
+WHERE application_name = 'oldestxmin_standby';
+]);
+
+my $hs_blocker_pid = $node->safe_psql('postgres', q[
+SELECT pid FROM pg_stat_replication
+WHERE application_name = 'oldestxmin_standby';
+]);
+chomp($hs_blocker_pid);
+
+$node->safe_psql('postgres', "DELETE FROM $hs_table;");
+
+$stderr = '';
+$node->psql('postgres', "VACUUM (VERBOSE) $hs_table;", stderr => \$stderr);
+like(
+ $stderr,
+ qr/oldest xmin blocker: hot standby feedback \(standby name = oldestxmin_standby, pid = $hs_blocker_pid\)/,
+ 'VACUUM VERBOSE reported hot standby feedback as oldest xmin blocker');
+
+# Cleanup
+$standby->safe_psql('postgres', "SELECT pg_terminate_backend($standby_reader_pid);");
+$node->safe_psql('postgres', "DROP TABLE $hs_table;");
+$standby->stop;
+
+
+#
+# Hot standby feedback held via a physical replication slot
+#
+# When the standby streams through a physical slot, the feedback xmin is held
+# by the slot rather than the walsender's PGPROC. While the standby is
+# connected this must still be reported as hot standby feedback.
+#
+my $slot_hs_table = 'blocker_slot_hotstandby';
+$node->safe_psql('postgres', qq[
+CREATE TABLE $slot_hs_table(id int);
+INSERT INTO $slot_hs_table VALUES (0);
+]);
+
+$slot_standby->start;
+$node->wait_for_catchup($slot_standby, 'replay', $node->lsn('flush'));
+
+my $slot_reader = $slot_standby->background_psql('postgres');
+my $slot_reader_pid = $slot_reader->query_safe('SELECT pg_backend_pid();');
+chomp($slot_reader_pid);
+
+$slot_reader->query_until(qr//, qq[
+BEGIN;
+SELECT * FROM $slot_hs_table, pg_sleep(60);
+]);
+
+# Wait for hot standby feedback to be sent through the slot. When a physical
+# slot is used the feedback xmin is held on the slot rather than in the
+# walsender's PGPROC, so pg_stat_replication.backend_xmin stays null here;
+# wait on the slot's xmin instead.
+$node->poll_query_until('postgres', q[
+SELECT xmin IS NOT NULL
+FROM pg_replication_slots
+WHERE slot_name = 'physical_slot';
+]);
+
+my $slot_hs_pid = $node->safe_psql('postgres', q[
+SELECT pid FROM pg_stat_replication
+WHERE application_name = 'oldestxmin_slot_standby';
+]);
+chomp($slot_hs_pid);
+
+$node->safe_psql('postgres', "DELETE FROM $slot_hs_table;");
+
+$stderr = '';
+$node->psql('postgres', "VACUUM (VERBOSE) $slot_hs_table;", stderr => \$stderr);
+like(
+ $stderr,
+ qr/oldest xmin blocker: hot standby feedback \(standby name = oldestxmin_slot_standby, pid = $slot_hs_pid\)/,
+ 'VACUUM VERBOSE reported slot-based hot standby feedback as oldest xmin blocker');
+
+
+#
+# Physical replication slot with no connected standby
+#
+# After the standby disconnects, the physical slot keeps reserving the xmin.
+# With no walsender connected, this must be reported as a physical replication
+# slot rather than as hot standby feedback.
+#
+# Terminate the reader's backend before stopping the standby. On Windows,
+# stopping while a backend is still busy in pg_sleep() makes the shutdown
+# checkpoint take long enough that pg_ctl can race against postmaster exit
+# and bail out with "server does not shut down".
+$slot_standby->safe_psql('postgres',
+ "SELECT pg_terminate_backend($slot_reader_pid);");
+$slot_standby->stop;
+
+# Wait for the walsender to exit so the slot is no longer active.
+$node->poll_query_until('postgres', q[
+SELECT NOT EXISTS (
+ SELECT 1 FROM pg_stat_replication
+ WHERE application_name = 'oldestxmin_slot_standby')
+AND NOT (SELECT active FROM pg_replication_slots
+ WHERE slot_name = 'physical_slot');
+]);
+
+$stderr = '';
+$node->psql('postgres', "VACUUM (VERBOSE) $slot_hs_table;", stderr => \$stderr);
+like(
+ $stderr,
+ qr/oldest xmin blocker: physical replication slot \(slot name = physical_slot\)/,
+ 'VACUUM VERBOSE reported physical replication slot as oldest xmin blocker');
+
+# Cleanup
+$node->safe_psql('postgres', qq[
+DROP TABLE $slot_hs_table;
+SELECT pg_drop_replication_slot('physical_slot');
+]);
+
+
+$node->stop;
+done_testing();
--
2.47.3
