public inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedRe: Report oldest xmin source when autovacuum cannot remove tuples
6+ messages / 3 participants
[nested] [flat]
* Re: Report oldest xmin source when autovacuum cannot remove tuples
@ 2026-01-22 06:58 wenhui qiu <qiuwenhuifx@gmail.com>
2026-02-05 04:39 ` Re: Report oldest xmin source when autovacuum cannot remove tuples Shinya Kato <shinya11.kato@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: wenhui qiu @ 2026-01-22 06:58 UTC (permalink / raw)
To: Sami Imseih <samimseih@gmail.com>; +Cc: Shinya Kato <shinya11.kato@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Hi Sami
> Alternatively, this information might be better exposed in a new system
> view, showing the "PID", "XID," and "reason" a transaction is blocking
VACUUM.
> This approach is more proactive as a DBA can continuously monitor for
> blocking reasons and take action before it becomes an issue.
Yes ,If this approach is acceptable, then as a reference for the standby
conflict-handling and query replay mechanism, we could consider introducing
a GUC parameter to terminate PID that blocks VACUUM freeze when the
relation age is approaching the vacuum_failsafe_age threshold.
Thanks
On Tue, Jan 6, 2026 at 6:02 AM Sami Imseih <samimseih@gmail.com> wrote:
> > Thank you all for the review comments, and sorry for the late reply.
> > I will address the review comments in order.
> >
> > On Sat, Nov 15, 2025 at 9:25 AM Sami Imseih <samimseih@gmail.com> wrote:
> > > More importantly:
> > >
> > > 3/ As mentioned earlier in the thread, the "idle-in-transaction"
> > > transactions is not being reported correctly, particularly for write
> > > tansactions. I think that is an important missing case. The reason
> > > for this is the cutoff xmin is not being looked up against the current
> > > list of xid's, so we are not blaming the correct pid.
> > >
> > > 4/
> > > Thinking about point 3 above, I began to wonder if this
> > > whole thing can be simplified with inspiration. Looking at the
> > > existing BackendXidGetPid(), I think it can.
> > >
> > > Based on BackendXidGetPid(), I tried a new routine called
> > > BackendXidFindCutOffReason() which can take in the cutoff xmin,
> > > passed in by vacuum and can walk though the proc array and
> > > determine the reason. We don't need to touch ComputeXidHorizons()
> > > to make this work, it seems to me. This comes with an additional
> > > walk though the procarray holding a shared lock, but I don't think
> > > this will be an issue.
> > >
> > > Attached is a rough sketch of BackendXidFindCutOffReason()
> > > For now, I just added NOTICE messages which will log with
> > > VACUUM (verbose) for testing.
> >
> > Thanks for the revised proposal! Your approach is clear and makes the
> > code easier to read.
>
> My approach is focused on correctness rather than simplicity.
>
> The current logic in ComputeXidHorizons can report the wrong PID for
> blocking transactions. For example:
>
> 1. start a pgbench with 5 clients for some time ( i.e. 5 minutes )
> ```
> pgbench -i -s50
> pgbench -c5 -T300
> ````
>
> 2. start a long running transaction that consumes an XID
> ```
> postgres=# begin;
> BEGIN
> postgres=*# SELECT txid_current(), pg_backend_pid();
> txid_current | pg_backend_pid
> --------------+----------------
> 3665231 | 266601
> (1 row)
> ```
>
> 3. run a vacuum
> ```
> postgres=# vacuum verbose pgbench_accounts ;
> INFO: vacuuming "postgres.public.pgbench_accounts"
> INFO: finished vacuuming "postgres.public.pgbench_accounts": index scans:
> 0
> pages: 0 removed, 59833 remain, 59043 scanned (98.68% of total), 0
> eagerly scanned
> tuples: 0 removed, 1045578 remain, 35425 are dead but not yet removable
> removable cutoff: 4301694, which was 35981 XIDs old when operation ended
> oldest xmin source: active transaction (pid=267064)
> frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
> visibility map: 0 pages set all-visible, 0 pages set all-frozen (0
> were all-visible)
> index scan bypassed: 405 pages from table (0.68% of total) have 1494
> dead item identifiers
> avg read rate: 1320.425 MB/s, avg write rate: 2.404 MB/s
> buffer usage: 67745 hits, 49974 reads, 91 dirtied
> WAL usage: 1 records, 0 full page images, 299 bytes, 0 full page image
> bytes, 0 buffers full
> system usage: CPU: user: 0.22 s, system: 0.06 s, elapsed: 0.29 s
> VACUUM
> ```
>
> VACUUM reports the oldest-XID source PID as 267090 , but the correct
> PID is 267064. This happens because the ComputeXidHorizons loop picks
> the first PID it encounters with the cutoff XID, even if other backends
> have the same XID. There’s no reliable way within that loop to identify
> the actual blocking transaction.
>
> ```
> postgres=# SELECT pid, datname, usename, state, backend_xmin,
> backend_xid, substr(query, 1, 20) as query
> FROM pg_stat_activity;
> pid | datname | usename | state | backend_xmin |
> backend_xid | query
>
> --------+----------+----------+---------------------+--------------+-------------+----------------------
> 267064 | postgres | postgres | active | 4301694 |
> 4351291 | UPDATE pgbench_branc
> 267069 | postgres | postgres | active | 4301694 |
> 4351332 | UPDATE pgbench_telle
> 267067 | postgres | postgres | active | 4301694 |
> 4351299 | UPDATE pgbench_branc
> 267070 | postgres | postgres | active | 4301694 |
> 4351279 | UPDATE pgbench_branc
> 267068 | postgres | postgres | active | |
> 4351325 | UPDATE pgbench_telle
> 267066 | postgres | postgres | active | 4301694 |
> 4351327 | UPDATE pgbench_branc
> 267065 | postgres | postgres | active | 4301694 |
> 4351292 | UPDATE pgbench_branc
> 267077 | postgres | postgres | active | 4301694 |
> 4351303 | UPDATE pgbench_branc
> 266606 | postgres | postgres | active | 4301694 |
> | SELECT pid, datname,
> 267071 | postgres | postgres | active | |
> | BEGIN;
> 267072 | postgres | postgres | active | 4301694 |
> 4351300 | UPDATE pgbench_telle
> 267073 | postgres | postgres | active | 4301694 |
> 4351258 | UPDATE pgbench_branc
> 267075 | postgres | postgres | idle | |
> | END;
> 267074 | postgres | postgres | active | 4301694 |
> 4351319 | UPDATE pgbench_branc
> 267076 | postgres | postgres | active | |
> 4351248 | END;
> 267084 | postgres | postgres | active | 4301694 |
> 4351330 | UPDATE pgbench_telle
> 267078 | postgres | postgres | active | 4301694 |
> 4351260 | UPDATE pgbench_branc
> 267082 | postgres | postgres | active | |
> 4351309 | END;
> 267081 | postgres | postgres | active | |
> 4351270 | UPDATE pgbench_branc
> 267083 | postgres | postgres | active | |
> 4351313 | END;
> 267080 | postgres | postgres | active | 4301694 |
> 4351311 | UPDATE pgbench_branc
> 267079 | postgres | postgres | active | 4301694 |
> 4351318 | UPDATE pgbench_branc
> 267086 | postgres | postgres | active | 4301694 |
> 4351335 | UPDATE pgbench_branc
> 267085 | postgres | postgres | active | |
> | BEGIN;
> 267090 | postgres | postgres | idle in transaction | |
> 4301694 | SELECT txid_current( ************
> ```
>
> > - Your proposal incurs additional cost. Furthermore, the time lag
> > between the execution of ComputeXidHorizons() and
> > BackendXidFindCutOffReason() could lead to inaccurate logging.
>
> While scanning the proc array adds some overhead, it could be limited
> to cases where multiple VACUUMs are stuck on the same cutoff XID, but
> we will need to track the last cutoff-xmin to make that possible.
>
> Alternatively, this information might be better exposed in a new system
> view, showing the "PID", "XID," and "reason" a transaction is blocking
> VACUUM.
> This approach is more proactive as a DBA can continuously monitor for
> blocking reasons and take action before it becomes an issue.
>
> --
> Sami Imseih
> Amazon Web Services (AWS)
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Report oldest xmin source when autovacuum cannot remove tuples
2026-01-22 06:58 Re: Report oldest xmin source when autovacuum cannot remove tuples wenhui qiu <qiuwenhuifx@gmail.com>
@ 2026-02-05 04:39 ` Shinya Kato <shinya11.kato@gmail.com>
2026-03-16 07:59 ` Re: Report oldest xmin source when autovacuum cannot remove tuples wenhui qiu <qiuwenhuifx@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Shinya Kato @ 2026-02-05 04:39 UTC (permalink / raw)
To: wenhui qiu <qiuwenhuifx@gmail.com>; +Cc: Sami Imseih <samimseih@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
HI,
Sorry for the late reply. I've updated the patch to follow Sami's
recommended approach.
Overview:
- Instead of modifying ComputeXidHorizons(), this patch introduces two
new functions: GetXidHorizonBlockers() and GetXidHorizonBlocker().
- GetXidHorizonBlockers() retrieves all potential blockers. This API
design leaves open the possibility of exposing this information
through a dynamic statistics view in the future [0].
- GetXidHorizonBlocker() selects the highest-priority blocker from the
candidates returned by GetXidHorizonBlockers().
- Priority is defined in the XidHorizonBlockerType enum. By
distinguishing whether the blocker matches the horizon via xid or
xmin, the appropriate blocker is selected.
Changes addressed from review comments:
- Fixed unstable regression test (Fujii-san's and Andres's comments).
- When multiple blockers share the same horizon, the blocker with the
highest priority is now selected for output (Fujii-san's comment).
- Removed unnecessary code (Fujii-san's comment).
- Distinguished between active transactions and idle-in-transaction
sessions, and added tests for both (Sami's and Wenhui's comments).
- Added a trailing comma to the last value of the enum (Sami's comment).
- Added a new function GetXidHorizonBlockers(), modeled after
BackendXidGetPid(), instead of modifying ComputeXidHorizons() (Sami's
comment).
- Added a test for a SERIALIZABLE transaction (Sami's comment).
Not addressed:
- Did not switch from int to pid_t for the pid type, because int is
used consistently throughout the PostgreSQL codebase for this purpose
(Sami's comment).
Other changes:
- Changed the TAP test to use VACUUM (VERBOSE) instead of autovacuum.
[0] https://www.postgresql.org/message-id/CAAaqYe9Dy9sicKg3xzCQUMK3VLdEP39g9nMGZheqtFYfNiO5Bg%40mail.gma...
--
Best regards,
Shinya Kato
NTT OSS Center
Attachments:
[application/x-patch] v2-0001-Report-oldest-xmin-blocker-when-VACUUM-cannot-rem.patch (26.9K, 2-v2-0001-Report-oldest-xmin-blocker-when-VACUUM-cannot-rem.patch)
download | inline diff:
From e827cccf204946ad088f20f0340160c635740148 Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Thu, 5 Feb 2026 13:35:37 +0900
Subject: [PATCH v2] 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.
This patch adds diagnostic information to the VACUUM VERBOSE log
output identifying the blocker that is holding back OldestXmin.
The new GetXidHorizonBlocker() function in procarray.c searches for
the source of the horizon: active transactions, idle-in-transaction
sessions, prepared transactions, hot standby feedback via walsenders,
and logical replication slots. When recently_dead_tuples > 0, VACUUM
VERBOSE now reports the highest-priority blocker with identifying
details (pid, prepared transaction GID, standby application name, or
slot name).
Because the horizon was computed earlier, the original blocker may
have already committed by the time the check runs. The result is
therefore best-effort: it may report a different blocker or none at
all.
Author: Shinya Kato <shinya11.kato@gmail.com>
Reviewed-by: wenhui qiu <qiuwenhuifx@gmail.com>
Reviewed-by: Fujii Masao <masao.fujii@gmail.com>
Reviewed-by: Sami Imseih <samimseih@gmail.com>
Reviewed-by: Dilip Kumar <dilipbalaut@gmail.com>
Discussion: https://postgr.es/m/CAOzEurSgy-gDtwFmEbj5+R9PL0_G3qYB6nnzJtNStyuf87VSVg@mail.gmail.com
---
src/backend/access/heap/vacuumlazy.c | 59 ++++
src/backend/access/transam/twophase.c | 39 +++
src/backend/storage/ipc/procarray.c | 262 +++++++++++++++
src/include/access/twophase.h | 1 +
src/include/storage/procarray.h | 38 +++
src/test/modules/test_misc/meson.build | 1 +
.../test_misc/t/011_log_vacuum_blockers.pl | 314 ++++++++++++++++++
src/tools/pgindent/typedefs.list | 2 +
8 files changed, 716 insertions(+)
create mode 100644 src/test/modules/test_misc/t/011_log_vacuum_blockers.pl
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 4be267ff657..7b69727cd26 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -150,6 +150,7 @@
#include "storage/bufmgr.h"
#include "storage/freespace.h"
#include "storage/lmgr.h"
+#include "storage/procarray.h"
#include "storage/read_stream.h"
#include "utils/lsyscache.h"
#include "utils/pg_rusage.h"
@@ -1058,6 +1059,64 @@ 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_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/backend/access/transam/twophase.c b/src/backend/access/transam/twophase.c
index eabc4d48208..6c6cad61336 100644
--- a/src/backend/access/transam/twophase.c
+++ b/src/backend/access/transam/twophase.c
@@ -2816,6 +2816,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 301f54fb5a8..244e2d800fa 100644
--- a/src/backend/storage/ipc/procarray.c
+++ b/src/backend/storage/ipc/procarray.c
@@ -58,14 +58,17 @@
#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 "utils/acl.h"
+#include "utils/backend_status.h"
#include "utils/builtins.h"
#include "utils/injection_point.h"
#include "utils/lsyscache.h"
#include "utils/rel.h"
#include "utils/snapmgr.h"
+#include "utils/wait_event.h"
#define UINT32_ACCESS_ONCE(var) ((uint32)(*((volatile uint32 *)&(var))))
@@ -2005,6 +2008,265 @@ GetReplicationHorizons(TransactionId *xmin, TransactionId *catalog_xmin)
*catalog_xmin = horizons.slot_catalog_xmin;
}
+/*
+ * Fetch the standby's application_name as reported by the walsender backend.
+ */
+static void
+GetStandbyAppname(int pid, char *name, Size len)
+{
+ int nbackends = pgstat_fetch_stat_numbackends();
+
+ name[0] = '\0';
+
+ for (int i = 1; i <= nbackends; i++)
+ {
+ LocalPgBackendStatus *local_beentry;
+ PgBackendStatus *beentry;
+
+ local_beentry = pgstat_get_local_beentry_by_index(i);
+ if (local_beentry == NULL)
+ continue;
+
+ beentry = &local_beentry->backendStatus;
+ if (beentry->st_procpid != pid)
+ continue;
+
+ if (beentry->st_appname && beentry->st_appname[0] != '\0')
+ strlcpy(name, beentry->st_appname, len);
+ return;
+ }
+}
+
+/*
+ * 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. Logical replication slots
+ *
+ * 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 blockers and stores the number of entries in
+ * *nblockers. The array may be empty if no blocker is found.
+ *
+ * Note: the same underlying cause (e.g. a physical replication slot with
+ * hot_standby_feedback) can appear as both an XHB_HOT_STANDBY_FEEDBACK entry
+ * (from the walsender PGPROC) and an XHB_REPLICATION_SLOT entry (from the
+ * slot's xmin). Callers that consume all entries should be prepared for such
+ * duplicates.
+ */
+static XidHorizonBlocker *
+GetXidHorizonBlockers(TransactionId horizon, int *nblockers)
+{
+ ProcArrayStruct *arrayP = procArray;
+ TransactionId *other_xids = ProcGlobal->xids;
+ XidHorizonBlocker *result;
+ int count = 0;
+ int max_blockers;
+
+ Assert(TransactionIdIsValid(horizon));
+ Assert(nblockers != NULL);
+
+ /*
+ * 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);
+
+ 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;
+ XidHorizonBlockerType candidate_type = XHB_NONE;
+ int candidate_pid = 0;
+ TransactionId candidate_xid = InvalidTransactionId;
+
+ /*
+ * 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);
+
+ /* Check if this proc's xid matches */
+ if (TransactionIdEquals(proc_xid, horizon))
+ {
+ if (proc->pid == 0)
+ {
+ candidate_type = XHB_PREPARED_TRANSACTION;
+ candidate_pid = 0;
+ candidate_xid = proc_xid;
+ }
+ else
+ {
+ candidate_type = XidHorizonBlockerTypeForBackend(proc);
+ candidate_pid = proc->pid;
+ candidate_xid = proc_xid;
+ }
+ }
+ /* Check if this proc's xmin matches */
+ else if (TransactionIdEquals(proc_xmin, horizon))
+ {
+ if (statusFlags & PROC_AFFECTS_ALL_HORIZONS)
+ {
+ candidate_type = XHB_HOT_STANDBY_FEEDBACK;
+ candidate_pid = proc->pid;
+ candidate_xid = proc_xmin;
+ }
+ else
+ {
+ candidate_type = XidHorizonBlockerTypeForXminBackend(proc);
+ candidate_pid = proc->pid;
+ candidate_xid = proc_xmin;
+ }
+ }
+
+ /*
+ * If we found a candidate, record it. Candidates are collected in
+ * ProcArray order; callers can reorder if needed.
+ */
+ if (candidate_type != XHB_NONE)
+ {
+ XidHorizonBlocker *dst;
+
+ dst = &result[count++];
+ dst->type = candidate_type;
+ dst->pid = candidate_pid;
+ dst->xid = candidate_xid;
+ }
+ }
+
+ LWLockRelease(ProcArrayLock);
+
+ /*
+ * Now that ProcArrayLock is released, fetch any extra details we want to
+ * attach to blockers, such as prepared transaction GIDs and standby
+ * application names.
+ */
+ for (int i = 0; i < count; i++)
+ {
+ if (result[i].type == XHB_PREPARED_TRANSACTION)
+ GetPreparedTransactionGid(result[i].xid, result[i].name);
+ else if (result[i].type == XHB_HOT_STANDBY_FEEDBACK)
+ GetStandbyAppname(result[i].pid, result[i].name,
+ sizeof(result[i].name));
+ }
+
+ /*
+ * Also check replication slots.
+ */
+ 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;
+
+ if (!s->in_use)
+ continue;
+
+ SpinLockAcquire(&s->mutex);
+ slot_xmin = s->data.xmin;
+ slot_catalog_xmin = s->data.catalog_xmin;
+ SpinLockRelease(&s->mutex);
+
+ if (TransactionIdEquals(slot_xmin, horizon) ||
+ TransactionIdEquals(slot_catalog_xmin, horizon))
+ {
+ XidHorizonBlocker *dst;
+
+ dst = &result[count++];
+ dst->type = XHB_REPLICATION_SLOT;
+ dst->pid = 0;
+ dst->xid = TransactionIdIsValid(slot_xmin) ?
+ slot_xmin :
+ slot_catalog_xmin;
+ strlcpy(dst->name, NameStr(s->data.name), sizeof(dst->name));
+ }
+ }
+
+ LWLockRelease(ReplicationSlotControlLock);
+ }
+
+ *nblockers = count;
+ return result;
+}
+
+/*
+ * 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)
+{
+ XidHorizonBlocker *blockers;
+ XidHorizonBlocker *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];
+ }
+
+ if (best != NULL)
+ *blocker = *best;
+
+ 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 e312514ba87..b076a155484 100644
--- a/src/include/access/twophase.h
+++ b/src/include/access/twophase.h
@@ -68,6 +68,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 3a8593f87ba..4c13f4df12b 100644
--- a/src/include/storage/procarray.h
+++ b/src/include/storage/procarray.h
@@ -14,11 +14,46 @@
#ifndef PROCARRAY_H
#define PROCARRAY_H
+#include "access/xact.h"
#include "storage/lock.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, /* walsender with hot_standby_feedback */
+ XHB_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 slots) */
+ /* large enough for prepared-txn GID or replication slot name */
+ char name[Max(GIDSIZE, NAMEDATALEN)];
+} XidHorizonBlocker;
+
extern Size ProcArrayShmemSize(void);
extern void ProcArrayShmemInit(void);
@@ -98,4 +133,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/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build
index 6e8db1621a7..d3b92e20737 100644
--- a/src/test/modules/test_misc/meson.build
+++ b/src/test/modules/test_misc/meson.build
@@ -19,6 +19,7 @@ tests += {
't/008_replslot_single_user.pl',
't/009_log_temp_files.pl',
't/010_index_concurrently_upsert.pl',
+ 't/011_log_vacuum_blockers.pl',
],
# The injection points are cluster-wide, so disable installcheck
'runningcheck': false,
diff --git a/src/test/modules/test_misc/t/011_log_vacuum_blockers.pl b/src/test/modules/test_misc/t/011_log_vacuum_blockers.pl
new file mode 100644
index 00000000000..2e26f0f3beb
--- /dev/null
+++ b/src/test/modules/test_misc/t/011_log_vacuum_blockers.pl
@@ -0,0 +1,314 @@
+# 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
+]);
+
+
+#
+# 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;
+$node->stop;
+done_testing();
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 9f5ee8fd482..976f505c758 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3431,6 +3431,8 @@ XactLockTableWaitInfo
XidBoundsViolation
XidCacheStatus
XidCommitStatus
+XidHorizonBlocker
+XidHorizonBlockerType
XidStatus
XmlExpr
XmlExprOp
--
2.47.3
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Report oldest xmin source when autovacuum cannot remove tuples
2026-01-22 06:58 Re: Report oldest xmin source when autovacuum cannot remove tuples wenhui qiu <qiuwenhuifx@gmail.com>
2026-02-05 04:39 ` Re: Report oldest xmin source when autovacuum cannot remove tuples Shinya Kato <shinya11.kato@gmail.com>
@ 2026-03-16 07:59 ` wenhui qiu <qiuwenhuifx@gmail.com>
2026-03-16 11:19 ` Re: Report oldest xmin source when autovacuum cannot remove tuples Japin Li <japinli@hotmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: wenhui qiu @ 2026-03-16 07:59 UTC (permalink / raw)
To: Shinya Kato <shinya11.kato@gmail.com>; +Cc: Sami Imseih <samimseih@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
HI Shinya
> typedef enum XidHorizonBlockerType
> {
> XHB_NONE = 0,
> XHB_ACTIVE_TRANSACTION,
> XHB_IDLE_IN_TRANSACTION,
> XHB_PREPARED_TRANSACTION,
> XHB_XMIN_ACTIVE_TRANSACTION,
> XHB_XMIN_IDLE_IN_TRANSACTION,
> XHB_HOT_STANDBY_FEEDBACK,
> XHB_REPLICATION_SLOT,
> }
Thank you for your working on this ,I have another small suggestion
The priority ordering encoded in XidHorizonBlockerType determines which
blocker gets reported when multiple candidates exist. In particular:
ACTIVE_TRANSACTION
IDLE_IN_TRANSACTION
PREPARED_TRANSACTION
Prepared transactions are currently ranked after idle-in-transaction
sessions. Operationally, prepared transactions are often harder for DBAs to
resolve than idle sessions, so it might be worth clarifying the rationale
behind this ordering or reconsidering whether prepared transactions should
have higher priority.
> typedef enum XidHorizonBlockerType
> {
> XHB_NONE = 0,
> XHB_ACTIVE_TRANSACTION,
> XHB_PREPARED_TRANSACTION,
> XHB_IDLE_IN_TRANSACTION,
> XHB_XMIN_ACTIVE_TRANSACTION,
> XHB_XMIN_IDLE_IN_TRANSACTION,
> XHB_HOT_STANDBY_FEEDBACK,
> XHB_REPLICATION_SLOT,
> }
Another one:
Currently GetXidHorizonBlocker() selects only one blocker (based on the
enum priority) even though multiple independent sources could hold back the
xmin horizon simultaneously. For example, it is possible to have both a
prepared transaction and a replication slot preventing the horizon from
advancing.
Have you considered reporting all detected blockers instead of just the
highest-priority one? Returning only a single entry might hide other
relevant blockers from the user.
Thanks
On Thu, Feb 5, 2026 at 12:40 PM Shinya Kato <shinya11.kato@gmail.com> wrote:
> HI,
>
> Sorry for the late reply. I've updated the patch to follow Sami's
> recommended approach.
>
> Overview:
> - Instead of modifying ComputeXidHorizons(), this patch introduces two
> new functions: GetXidHorizonBlockers() and GetXidHorizonBlocker().
> - GetXidHorizonBlockers() retrieves all potential blockers. This API
> design leaves open the possibility of exposing this information
> through a dynamic statistics view in the future [0].
> - GetXidHorizonBlocker() selects the highest-priority blocker from the
> candidates returned by GetXidHorizonBlockers().
> - Priority is defined in the XidHorizonBlockerType enum. By
> distinguishing whether the blocker matches the horizon via xid or
> xmin, the appropriate blocker is selected.
>
> Changes addressed from review comments:
> - Fixed unstable regression test (Fujii-san's and Andres's comments).
> - When multiple blockers share the same horizon, the blocker with the
> highest priority is now selected for output (Fujii-san's comment).
> - Removed unnecessary code (Fujii-san's comment).
> - Distinguished between active transactions and idle-in-transaction
> sessions, and added tests for both (Sami's and Wenhui's comments).
> - Added a trailing comma to the last value of the enum (Sami's comment).
> - Added a new function GetXidHorizonBlockers(), modeled after
> BackendXidGetPid(), instead of modifying ComputeXidHorizons() (Sami's
> comment).
> - Added a test for a SERIALIZABLE transaction (Sami's comment).
>
> Not addressed:
> - Did not switch from int to pid_t for the pid type, because int is
> used consistently throughout the PostgreSQL codebase for this purpose
> (Sami's comment).
>
> Other changes:
> - Changed the TAP test to use VACUUM (VERBOSE) instead of autovacuum.
>
> [0]
> https://www.postgresql.org/message-id/CAAaqYe9Dy9sicKg3xzCQUMK3VLdEP39g9nMGZheqtFYfNiO5Bg%40mail.gma...
>
>
>
> --
> Best regards,
> Shinya Kato
> NTT OSS Center
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Report oldest xmin source when autovacuum cannot remove tuples
2026-01-22 06:58 Re: Report oldest xmin source when autovacuum cannot remove tuples wenhui qiu <qiuwenhuifx@gmail.com>
2026-02-05 04:39 ` Re: Report oldest xmin source when autovacuum cannot remove tuples Shinya Kato <shinya11.kato@gmail.com>
2026-03-16 07:59 ` Re: Report oldest xmin source when autovacuum cannot remove tuples wenhui qiu <qiuwenhuifx@gmail.com>
@ 2026-03-16 11:19 ` Japin Li <japinli@hotmail.com>
2026-05-22 13:05 ` Re: Report oldest xmin source when autovacuum cannot remove tuples Shinya Kato <shinya11.kato@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Japin Li @ 2026-03-16 11:19 UTC (permalink / raw)
To: wenhui qiu <qiuwenhuifx@gmail.com>; +Cc: Shinya Kato <shinya11.kato@gmail.com>; Sami Imseih <samimseih@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Mon, 16 Mar 2026 at 15:59, wenhui qiu <qiuwenhuifx@gmail.com> wrote:
> HI Shinya
>> typedef enum XidHorizonBlockerType
>> {
>> XHB_NONE = 0,
>> XHB_ACTIVE_TRANSACTION,
>> XHB_IDLE_IN_TRANSACTION,
>> XHB_PREPARED_TRANSACTION,
>> XHB_XMIN_ACTIVE_TRANSACTION,
>> XHB_XMIN_IDLE_IN_TRANSACTION,
>> XHB_HOT_STANDBY_FEEDBACK,
>> XHB_REPLICATION_SLOT,
>> }
> Thank you for your working on this ,I have another small suggestion
> The priority ordering encoded in XidHorizonBlockerType determines which blocker gets reported when multiple candidates
> exist. In particular:
>
> ACTIVE_TRANSACTION
> IDLE_IN_TRANSACTION
> PREPARED_TRANSACTION
>
> Prepared transactions are currently ranked after idle-in-transaction sessions. Operationally, prepared transactions are
> often harder for DBAs to resolve than idle sessions, so it might be worth clarifying the rationale behind this ordering
> or reconsidering whether prepared transactions should have higher priority.
Agreed. Explaining the reason for this priority is very helpful.
>> typedef enum XidHorizonBlockerType
>> {
>> XHB_NONE = 0,
>> XHB_ACTIVE_TRANSACTION,
>> XHB_PREPARED_TRANSACTION,
>> XHB_IDLE_IN_TRANSACTION,
>> XHB_XMIN_ACTIVE_TRANSACTION,
>> XHB_XMIN_IDLE_IN_TRANSACTION,
>> XHB_HOT_STANDBY_FEEDBACK,
>> XHB_REPLICATION_SLOT,
>> }
> Another one:
> Currently GetXidHorizonBlocker() selects only one blocker (based on the enum priority) even though multiple independent
> sources could hold back the xmin horizon simultaneously. For example, it is possible to have both a prepared transaction
> and a replication slot preventing the horizon from advancing.
> Have you considered reporting all detected blockers instead of just the highest-priority one? Returning only a single
> entry might hide other relevant blockers from the user.
>
I'm also curious — why don't we list all the blockers? Did I miss anything?
> Thanks
>
> On Thu, Feb 5, 2026 at 12:40 PM Shinya Kato <shinya11.kato@gmail.com> wrote:
>
> HI,
>
> Sorry for the late reply. I've updated the patch to follow Sami's
> recommended approach.
>
> Overview:
> - Instead of modifying ComputeXidHorizons(), this patch introduces two
> new functions: GetXidHorizonBlockers() and GetXidHorizonBlocker().
> - GetXidHorizonBlockers() retrieves all potential blockers. This API
> design leaves open the possibility of exposing this information
> through a dynamic statistics view in the future [0].
> - GetXidHorizonBlocker() selects the highest-priority blocker from the
> candidates returned by GetXidHorizonBlockers().
> - Priority is defined in the XidHorizonBlockerType enum. By
> distinguishing whether the blocker matches the horizon via xid or
> xmin, the appropriate blocker is selected.
>
> Changes addressed from review comments:
> - Fixed unstable regression test (Fujii-san's and Andres's comments).
> - When multiple blockers share the same horizon, the blocker with the
> highest priority is now selected for output (Fujii-san's comment).
> - Removed unnecessary code (Fujii-san's comment).
> - Distinguished between active transactions and idle-in-transaction
> sessions, and added tests for both (Sami's and Wenhui's comments).
> - Added a trailing comma to the last value of the enum (Sami's comment).
> - Added a new function GetXidHorizonBlockers(), modeled after
> BackendXidGetPid(), instead of modifying ComputeXidHorizons() (Sami's
> comment).
> - Added a test for a SERIALIZABLE transaction (Sami's comment).
>
> Not addressed:
> - Did not switch from int to pid_t for the pid type, because int is
> used consistently throughout the PostgreSQL codebase for this purpose
> (Sami's comment).
>
> Other changes:
> - Changed the TAP test to use VACUUM (VERBOSE) instead of autovacuum.
>
> [0] https://www.postgresql.org/message-id/CAAaqYe9Dy9sicKg3xzCQUMK3VLdEP39g9nMGZheqtFYfNiO5Bg%40mail.gma...
>
> --
> Best regards,
> Shinya Kato
> NTT OSS Center
--
Regards,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Report oldest xmin source when autovacuum cannot remove tuples
2026-01-22 06:58 Re: Report oldest xmin source when autovacuum cannot remove tuples wenhui qiu <qiuwenhuifx@gmail.com>
2026-02-05 04:39 ` Re: Report oldest xmin source when autovacuum cannot remove tuples Shinya Kato <shinya11.kato@gmail.com>
2026-03-16 07:59 ` Re: Report oldest xmin source when autovacuum cannot remove tuples wenhui qiu <qiuwenhuifx@gmail.com>
2026-03-16 11:19 ` Re: Report oldest xmin source when autovacuum cannot remove tuples Japin Li <japinli@hotmail.com>
@ 2026-05-22 13:05 ` Shinya Kato <shinya11.kato@gmail.com>
2026-05-23 01:40 ` Re: Report oldest xmin source when autovacuum cannot remove tuples Japin Li <japinli@hotmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Shinya Kato @ 2026-05-22 13:05 UTC (permalink / raw)
To: Japin Li <japinli@hotmail.com>; +Cc: wenhui qiu <qiuwenhuifx@gmail.com>; Sami Imseih <samimseih@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Thank you for your feedback!
On Mon, Mar 16, 2026 at 8:19 PM Japin Li <japinli@hotmail.com> wrote:
>
> On Mon, 16 Mar 2026 at 15:59, wenhui qiu <qiuwenhuifx@gmail.com> wrote:
> > HI Shinya
> >> typedef enum XidHorizonBlockerType
> >> {
> >> XHB_NONE = 0,
> >> XHB_ACTIVE_TRANSACTION,
> >> XHB_IDLE_IN_TRANSACTION,
> >> XHB_PREPARED_TRANSACTION,
> >> XHB_XMIN_ACTIVE_TRANSACTION,
> >> XHB_XMIN_IDLE_IN_TRANSACTION,
> >> XHB_HOT_STANDBY_FEEDBACK,
> >> XHB_REPLICATION_SLOT,
> >> }
> > Thank you for your working on this ,I have another small suggestion
> > The priority ordering encoded in XidHorizonBlockerType determines which blocker gets reported when multiple candidates
> > exist. In particular:
> >
> > ACTIVE_TRANSACTION
> > IDLE_IN_TRANSACTION
> > PREPARED_TRANSACTION
> >
> > Prepared transactions are currently ranked after idle-in-transaction sessions. Operationally, prepared transactions are
> > often harder for DBAs to resolve than idle sessions, so it might be worth clarifying the rationale behind this ordering
> > or reconsidering whether prepared transactions should have higher priority.
>
> Agreed. Explaining the reason for this priority is very helpful.
We always pick a blocker from the xid-match group first (it is the
transaction actually holding the horizon, while the xmin-match entries
are just held back by it). Within the xid-match group, the
active/idle/prepared order never matters: a given xid is owned by only
one backend, so when the horizon equals a proc's xid there is only one
matching entry, and it is exactly one of active, idle, or prepared. So
moving prepared ahead of idle would not change which blocker we
report.
> >> typedef enum XidHorizonBlockerType
> >> {
> >> XHB_NONE = 0,
> >> XHB_ACTIVE_TRANSACTION,
> >> XHB_PREPARED_TRANSACTION,
> >> XHB_IDLE_IN_TRANSACTION,
> >> XHB_XMIN_ACTIVE_TRANSACTION,
> >> XHB_XMIN_IDLE_IN_TRANSACTION,
> >> XHB_HOT_STANDBY_FEEDBACK,
> >> XHB_REPLICATION_SLOT,
> >> }
> > Another one:
> > Currently GetXidHorizonBlocker() selects only one blocker (based on the enum priority) even though multiple independent
> > sources could hold back the xmin horizon simultaneously. For example, it is possible to have both a prepared transaction
> > and a replication slot preventing the horizon from advancing.
> > Have you considered reporting all detected blockers instead of just the highest-priority one? Returning only a single
> > entry might hide other relevant blockers from the user.
> >
>
> I'm also curious — why don't we list all the blockers? Did I miss anything?
I did think about this, but I would like to keep reporting one blocker
in the VACUUM log, for two reasons.
First, the log can get very large. In Sami's earlier example [0], a
pgbench run had many backends all sharing the same xmin while only one
idle-in-transaction backend actually owned the cutoff xid. Reporting
every blocker would print 20+ lines, almost all of them just victims
of the same root cause, which makes the log harder to read, not
easier.
Second, the one blocker we report is the root cause (the xid owner).
Once the DBA resolves it, the next VACUUM will show the next blocker
if one remains.
This is also why the code is split into GetXidHorizonBlockers(), which
already collects every candidate, and GetXidHorizonBlocker(), which
picks the highest-priority one for the log. The "show everything" case
is what I would like to expose later through a dynamic statistics
view, where a full list makes more sense than in a VACUUM log line.
I've rebased the patch.
P.S. It might be better to use bottom posting instead of top posting [1].
[0] https://www.postgresql.org/message-id/CAA5RZ0sjMgMo4Xg-niyyF-CpkQ_CK6uOfNKYT%3D9RmiBkAxQkbQ%40mail.g...
[1] https://wiki.postgresql.org/wiki/Mailing_Lists#Email_etiquette_mechanics
--
Best regards,
Shinya Kato
NTT OSS Center
Attachments:
[application/octet-stream] v3-0001-Report-oldest-xmin-blocker-when-VACUUM-cannot-rem.patch (27.1K, 2-v3-0001-Report-oldest-xmin-blocker-when-VACUUM-cannot-rem.patch)
download | inline diff:
From 3895d6c637cbcae1cdcc269b03b39f8ce011341c Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Fri, 22 May 2026 21:35:12 +0900
Subject: [PATCH v3] 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.
This patch adds diagnostic information to the VACUUM VERBOSE log
output identifying the blocker that is holding back OldestXmin.
The new GetXidHorizonBlocker() function in procarray.c searches for
the source of the horizon: active transactions, idle-in-transaction
sessions, prepared transactions, hot standby feedback via walsenders,
and logical replication slots. When recently_dead_tuples > 0, VACUUM
VERBOSE now reports the highest-priority blocker with identifying
details (pid, prepared transaction GID, standby application name, or
slot name).
Because the horizon was computed earlier, the original blocker may
have already committed by the time the check runs. The result is
therefore best-effort: it may report a different blocker or none at
all.
Author: Shinya Kato <shinya11.kato@gmail.com>
Reviewed-by: wenhui qiu <qiuwenhuifx@gmail.com>
Reviewed-by: Fujii Masao <masao.fujii@gmail.com>
Reviewed-by: Sami Imseih <samimseih@gmail.com>
Reviewed-by: Dilip Kumar <dilipbalaut@gmail.com>
Reviewed-by: Japin Li <japinli@hotmail.com>
Discussion: https://postgr.es/m/CAOzEurSgy-gDtwFmEbj5+R9PL0_G3qYB6nnzJtNStyuf87VSVg@mail.gmail.com
---
src/backend/access/heap/vacuumlazy.c | 59 ++++
src/backend/access/transam/twophase.c | 39 +++
src/backend/storage/ipc/procarray.c | 261 +++++++++++++++
src/include/access/twophase.h | 1 +
src/include/storage/procarray.h | 39 +++
src/test/modules/test_misc/meson.build | 7 +-
.../test_misc/t/014_log_vacuum_blockers.pl | 314 ++++++++++++++++++
src/tools/pgindent/typedefs.list | 2 +
8 files changed, 719 insertions(+), 3 deletions(-)
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..a841225bb4e 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,64 @@ 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_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/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 9299bcebbda..d836d24d25d 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"
@@ -1999,6 +2001,265 @@ GetReplicationHorizons(TransactionId *xmin, TransactionId *catalog_xmin)
*catalog_xmin = horizons.slot_catalog_xmin;
}
+/*
+ * Fetch the standby's application_name as reported by the walsender backend.
+ */
+static void
+GetStandbyAppname(int pid, char *name, Size len)
+{
+ int nbackends = pgstat_fetch_stat_numbackends();
+
+ name[0] = '\0';
+
+ for (int i = 1; i <= nbackends; i++)
+ {
+ LocalPgBackendStatus *local_beentry;
+ PgBackendStatus *beentry;
+
+ local_beentry = pgstat_get_local_beentry_by_index(i);
+ if (local_beentry == NULL)
+ continue;
+
+ beentry = &local_beentry->backendStatus;
+ if (beentry->st_procpid != pid)
+ continue;
+
+ if (beentry->st_appname && beentry->st_appname[0] != '\0')
+ strlcpy(name, beentry->st_appname, len);
+ return;
+ }
+}
+
+/*
+ * 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. Logical replication slots
+ *
+ * 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 blockers and stores the number of entries in
+ * *nblockers. The array may be empty if no blocker is found.
+ *
+ * Note: the same underlying cause (e.g. a physical replication slot with
+ * hot_standby_feedback) can appear as both an XHB_HOT_STANDBY_FEEDBACK entry
+ * (from the walsender PGPROC) and an XHB_REPLICATION_SLOT entry (from the
+ * slot's xmin). Callers that consume all entries should be prepared for such
+ * duplicates.
+ */
+static XidHorizonBlocker *
+GetXidHorizonBlockers(TransactionId horizon, int *nblockers)
+{
+ ProcArrayStruct *arrayP = procArray;
+ TransactionId *other_xids = ProcGlobal->xids;
+ XidHorizonBlocker *result;
+ int count = 0;
+ int max_blockers;
+
+ Assert(TransactionIdIsValid(horizon));
+ Assert(nblockers != NULL);
+
+ /*
+ * 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);
+
+ 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;
+ XidHorizonBlockerType candidate_type = XHB_NONE;
+ int candidate_pid = 0;
+ TransactionId candidate_xid = InvalidTransactionId;
+
+ /*
+ * 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);
+
+ /* Check if this proc's xid matches */
+ if (TransactionIdEquals(proc_xid, horizon))
+ {
+ if (proc->pid == 0)
+ {
+ candidate_type = XHB_PREPARED_TRANSACTION;
+ candidate_pid = 0;
+ candidate_xid = proc_xid;
+ }
+ else
+ {
+ candidate_type = XidHorizonBlockerTypeForBackend(proc);
+ candidate_pid = proc->pid;
+ candidate_xid = proc_xid;
+ }
+ }
+ /* Check if this proc's xmin matches */
+ else if (TransactionIdEquals(proc_xmin, horizon))
+ {
+ if (statusFlags & PROC_AFFECTS_ALL_HORIZONS)
+ {
+ candidate_type = XHB_HOT_STANDBY_FEEDBACK;
+ candidate_pid = proc->pid;
+ candidate_xid = proc_xmin;
+ }
+ else
+ {
+ candidate_type = XidHorizonBlockerTypeForXminBackend(proc);
+ candidate_pid = proc->pid;
+ candidate_xid = proc_xmin;
+ }
+ }
+
+ /*
+ * If we found a candidate, record it. Candidates are collected in
+ * ProcArray order; callers can reorder if needed.
+ */
+ if (candidate_type != XHB_NONE)
+ {
+ XidHorizonBlocker *dst;
+
+ dst = &result[count++];
+ dst->type = candidate_type;
+ dst->pid = candidate_pid;
+ dst->xid = candidate_xid;
+ }
+ }
+
+ LWLockRelease(ProcArrayLock);
+
+ /*
+ * Now that ProcArrayLock is released, fetch any extra details we want to
+ * attach to blockers, such as prepared transaction GIDs and standby
+ * application names.
+ */
+ for (int i = 0; i < count; i++)
+ {
+ if (result[i].type == XHB_PREPARED_TRANSACTION)
+ GetPreparedTransactionGid(result[i].xid, result[i].name);
+ else if (result[i].type == XHB_HOT_STANDBY_FEEDBACK)
+ GetStandbyAppname(result[i].pid, result[i].name,
+ sizeof(result[i].name));
+ }
+
+ /*
+ * Also check replication slots.
+ */
+ 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;
+
+ if (!s->in_use)
+ continue;
+
+ SpinLockAcquire(&s->mutex);
+ slot_xmin = s->data.xmin;
+ slot_catalog_xmin = s->data.catalog_xmin;
+ SpinLockRelease(&s->mutex);
+
+ if (TransactionIdEquals(slot_xmin, horizon) ||
+ TransactionIdEquals(slot_catalog_xmin, horizon))
+ {
+ XidHorizonBlocker *dst;
+
+ dst = &result[count++];
+ dst->type = XHB_REPLICATION_SLOT;
+ dst->pid = 0;
+ dst->xid = TransactionIdIsValid(slot_xmin) ?
+ slot_xmin :
+ slot_catalog_xmin;
+ strlcpy(dst->name, NameStr(s->data.name), sizeof(dst->name));
+ }
+ }
+
+ LWLockRelease(ReplicationSlotControlLock);
+ }
+
+ *nblockers = count;
+ return result;
+}
+
+/*
+ * 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)
+{
+ XidHorizonBlocker *blockers;
+ XidHorizonBlocker *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];
+ }
+
+ if (best != NULL)
+ *blocker = *best;
+
+ 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 ec89c448220..2689eec7daf 100644
--- a/src/include/storage/procarray.h
+++ b/src/include/storage/procarray.h
@@ -14,10 +14,46 @@
#ifndef PROCARRAY_H
#define PROCARRAY_H
+#include "access/xact.h"
+#include "storage/lock.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, /* walsender with hot_standby_feedback */
+ XHB_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 slots) */
+ /* 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 +134,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/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build
index 969e90b396d..c4593511558 100644
--- a/src/test/modules/test_misc/meson.build
+++ b/src/test/modules/test_misc/meson.build
@@ -19,9 +19,10 @@ tests += {
't/008_replslot_single_user.pl',
't/009_log_temp_files.pl',
't/010_index_concurrently_upsert.pl',
- 't/011_lock_stats.pl',
- 't/012_ddlutils.pl',
- 't/013_temp_obj_multisession.pl',
+ 't/011_log_vacuum_blockers.pl',
+ 't/012_lock_stats.pl',
+ 't/013_ddlutils.pl',
+ 't/014_temp_obj_multisession.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..2e26f0f3beb
--- /dev/null
+++ b/src/test/modules/test_misc/t/014_log_vacuum_blockers.pl
@@ -0,0 +1,314 @@
+# 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
+]);
+
+
+#
+# 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;
+$node->stop;
+done_testing();
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 8cf40c87043..83b880b426e 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3544,6 +3544,8 @@ XactLockTableWaitInfo
XidBoundsViolation
XidCacheStatus
XidCommitStatus
+XidHorizonBlocker
+XidHorizonBlockerType
XidStatus
XmlExpr
XmlExprOp
--
2.47.3
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Report oldest xmin source when autovacuum cannot remove tuples
2026-01-22 06:58 Re: Report oldest xmin source when autovacuum cannot remove tuples wenhui qiu <qiuwenhuifx@gmail.com>
2026-02-05 04:39 ` Re: Report oldest xmin source when autovacuum cannot remove tuples Shinya Kato <shinya11.kato@gmail.com>
2026-03-16 07:59 ` Re: Report oldest xmin source when autovacuum cannot remove tuples wenhui qiu <qiuwenhuifx@gmail.com>
2026-03-16 11:19 ` Re: Report oldest xmin source when autovacuum cannot remove tuples Japin Li <japinli@hotmail.com>
2026-05-22 13:05 ` Re: Report oldest xmin source when autovacuum cannot remove tuples Shinya Kato <shinya11.kato@gmail.com>
@ 2026-05-23 01:40 ` Japin Li <japinli@hotmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Japin Li @ 2026-05-23 01:40 UTC (permalink / raw)
To: Shinya Kato <shinya11.kato@gmail.com>; +Cc: wenhui qiu <qiuwenhuifx@gmail.com>; Sami Imseih <samimseih@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Fri, 22 May 2026 at 22:05, Shinya Kato <shinya11.kato@gmail.com> wrote:
> Thank you for your feedback!
>
> On Mon, Mar 16, 2026 at 8:19 PM Japin Li <japinli@hotmail.com> wrote:
>>
>> On Mon, 16 Mar 2026 at 15:59, wenhui qiu <qiuwenhuifx@gmail.com> wrote:
>> > HI Shinya
>> >> typedef enum XidHorizonBlockerType
>> >> {
>> >> XHB_NONE = 0,
>> >> XHB_ACTIVE_TRANSACTION,
>> >> XHB_IDLE_IN_TRANSACTION,
>> >> XHB_PREPARED_TRANSACTION,
>> >> XHB_XMIN_ACTIVE_TRANSACTION,
>> >> XHB_XMIN_IDLE_IN_TRANSACTION,
>> >> XHB_HOT_STANDBY_FEEDBACK,
>> >> XHB_REPLICATION_SLOT,
>> >> }
>> > Thank you for your working on this ,I have another small suggestion
>> > The priority ordering encoded in XidHorizonBlockerType determines which blocker gets reported when multiple candidates
>> > exist. In particular:
>> >
>> > ACTIVE_TRANSACTION
>> > IDLE_IN_TRANSACTION
>> > PREPARED_TRANSACTION
>> >
>> > Prepared transactions are currently ranked after idle-in-transaction sessions. Operationally, prepared transactions are
>> > often harder for DBAs to resolve than idle sessions, so it might be worth clarifying the rationale behind this ordering
>> > or reconsidering whether prepared transactions should have higher priority.
>>
>> Agreed. Explaining the reason for this priority is very helpful.
>
> We always pick a blocker from the xid-match group first (it is the
> transaction actually holding the horizon, while the xmin-match entries
> are just held back by it). Within the xid-match group, the
> active/idle/prepared order never matters: a given xid is owned by only
> one backend, so when the horizon equals a proc's xid there is only one
> matching entry, and it is exactly one of active, idle, or prepared. So
> moving prepared ahead of idle would not change which blocker we
> report.
>
>> >> typedef enum XidHorizonBlockerType
>> >> {
>> >> XHB_NONE = 0,
>> >> XHB_ACTIVE_TRANSACTION,
>> >> XHB_PREPARED_TRANSACTION,
>> >> XHB_IDLE_IN_TRANSACTION,
>> >> XHB_XMIN_ACTIVE_TRANSACTION,
>> >> XHB_XMIN_IDLE_IN_TRANSACTION,
>> >> XHB_HOT_STANDBY_FEEDBACK,
>> >> XHB_REPLICATION_SLOT,
>> >> }
>> > Another one:
>> > Currently GetXidHorizonBlocker() selects only one blocker (based on the enum priority) even though multiple independent
>> > sources could hold back the xmin horizon simultaneously. For example, it is possible to have both a prepared transaction
>> > and a replication slot preventing the horizon from advancing.
>> > Have you considered reporting all detected blockers instead of just the highest-priority one? Returning only a single
>> > entry might hide other relevant blockers from the user.
>> >
>>
>> I'm also curious — why don't we list all the blockers? Did I miss anything?
>
> I did think about this, but I would like to keep reporting one blocker
> in the VACUUM log, for two reasons.
>
> First, the log can get very large. In Sami's earlier example [0], a
> pgbench run had many backends all sharing the same xmin while only one
> idle-in-transaction backend actually owned the cutoff xid. Reporting
> every blocker would print 20+ lines, almost all of them just victims
> of the same root cause, which makes the log harder to read, not
> easier.
>
> Second, the one blocker we report is the root cause (the xid owner).
> Once the DBA resolves it, the next VACUUM will show the next blocker
> if one remains.
>
> This is also why the code is split into GetXidHorizonBlockers(), which
> already collects every candidate, and GetXidHorizonBlocker(), which
> picks the highest-priority one for the log. The "show everything" case
> is what I would like to expose later through a dynamic statistics
> view, where a full list makes more sense than in a VACUUM log line.
>
>
> I've rebased the patch.
>
Thanks for updating the patch. LGTM. Just one nitpick.
+ int pid; /* backend pid (0 for slots) */
Per the code, the prepared transaction is also associated with a PID of zero.
--
Regards,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-05-23 01:40 UTC | newest]
Thread overview: 6+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-01-22 06:58 Re: Report oldest xmin source when autovacuum cannot remove tuples wenhui qiu <qiuwenhuifx@gmail.com>
2026-02-05 04:39 ` Shinya Kato <shinya11.kato@gmail.com>
2026-03-16 07:59 ` wenhui qiu <qiuwenhuifx@gmail.com>
2026-03-16 11:19 ` Japin Li <japinli@hotmail.com>
2026-05-22 13:05 ` Shinya Kato <shinya11.kato@gmail.com>
2026-05-23 01:40 ` Japin Li <japinli@hotmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox