public inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedReport oldest xmin source when autovacuum cannot remove tuples
18+ messages / 7 participants
[nested] [flat]
* Report oldest xmin source when autovacuum cannot remove tuples
@ 2025-10-31 06:31 Shinya Kato <shinya11.kato@gmail.com>
0 siblings, 3 replies; 18+ messages in thread
From: Shinya Kato @ 2025-10-31 06:31 UTC (permalink / raw)
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Hi hackers,
I am proposing to add the reason for the oldest xmin to VACUUM logs.
This feature would be useful for identifying why dead tuples cannot be
removed, thereby helping to diagnose and prevent table bloat.
The current logs only indicate that dead tuples could not be reclaimed
due to the oldest xmin, but they do not reveal the underlying reason.
To identify the cause, it is necessary to query multiple views:
pg_stat_activity (for active transactions), pg_prepared_xacts (for
prepared statements), pg_replication_slots (for replication slots),
and pg_stat_replication (for hot standby feedback). However, because
the data in these views is volatile, it is difficult to retroactively
determine what was holding the oldest xmin at the specific time the
log message was generated.
This PoC patch addresses this problem. The implementation now outputs
the reason for the oldest xmin and, where applicable, the backend PID.
This information was originally discarded when calculating the oldest
xmin horizon, and the computation required to retrieve these reasons
is considered reasonable.
The patch is attached. What do you think?
--
Best regards,
Shinya Kato
NTT OSS Center
Attachments:
[application/octet-stream] v1-0001-Report-oldest-xmin-source-when-autovacuum-cannot-.patch (20.6K, 2-v1-0001-Report-oldest-xmin-source-when-autovacuum-cannot-.patch)
download | inline diff:
From ef0ce0d3fa45d639ae9d08a5550163b08c80c21f Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Wed, 29 Oct 2025 21:04:52 +0900
Subject: [PATCH v1] Report oldest xmin source when autovacuum cannot remove
tuples
Autovacuum logging now tells which backend or mechanism kept OldestXmin
from advancing when dead tuples remain, improving diagnosis of bloating
and wraparound hazards. ProcArray now records an OldestXminSource for
each computed horizon, and VACUUM retrieves it through the new
GetOldestNonRemovableTransactionIdWithReason() helper. The log output
names active transactions (with pid), hot standby feedback, prepared
transactions, replication slots, or otherwise labels the cause as
"other".
Author: Shinya Kato <shinya11.kato@gmail.com>
Reviewed-by:
Discussion: https://postgr.es/m/
---
src/backend/access/heap/vacuumlazy.c | 37 ++++
src/backend/commands/vacuum.c | 4 +-
src/backend/storage/ipc/procarray.c | 165 +++++++++++++---
src/include/commands/vacuum.h | 4 +
src/include/storage/procarray.h | 21 ++
src/test/modules/test_misc/meson.build | 1 +
.../t/010_autovacuum_oldest_xmin_reason.pl | 186 ++++++++++++++++++
7 files changed, 388 insertions(+), 30 deletions(-)
create mode 100644 src/test/modules/test_misc/t/010_autovacuum_oldest_xmin_reason.pl
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index d2b031fdd06..e4e7c6ee674 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -1047,6 +1047,43 @@ heap_vacuum_rel(Relation rel, const VacuumParams params,
appendStringInfo(&buf,
_("removable cutoff: %u, which was %d XIDs old when operation ended\n"),
vacrel->cutoffs.OldestXmin, diff);
+ /*
+ * If there are dead tuples not yet removable, report what
+ * determined the OldestXmin horizon to aid diagnostics.
+ */
+ if (vacrel->recently_dead_tuples > 0)
+ {
+ int pid = vacrel->cutoffs.oldest_xmin_info.backend_pid;
+ bool include_pid = pid > 0;
+
+ switch (vacrel->cutoffs.oldest_xmin_info.source)
+ {
+ case OLDESTXMIN_SOURCE_ACTIVE_TRANSACTION:
+ msgfmt = include_pid ?
+ _("oldest xmin source: active transaction (pid=%d)\n") :
+ _("oldest xmin source: active transaction\n");
+ break;
+ case OLDESTXMIN_SOURCE_HOT_STANDBY_FEEDBACK:
+ msgfmt = include_pid ?
+ _("oldest xmin source: hot standby feedback (pid=%d)\n") :
+ _("oldest xmin source: hot standby feedback\n");
+ break;
+ case OLDESTXMIN_SOURCE_PREPARED_TRANSACTION:
+ msgfmt = _("oldest xmin source: prepared transaction\n");
+ break;
+ case OLDESTXMIN_SOURCE_REPLICATION_SLOT:
+ msgfmt = _("oldest xmin source: replication slot\n");
+ break;
+ default:
+ msgfmt = _("oldest xmin source: other\n");
+ break;
+ }
+
+ if (include_pid)
+ appendStringInfo(&buf, msgfmt, pid);
+ else
+ appendStringInfoString(&buf, msgfmt);
+ }
if (frozenxid_updated)
{
diff = (int32) (vacrel->NewRelfrozenXid -
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index ed03e3bd50d..1a7ce91f6f6 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -1130,7 +1130,9 @@ vacuum_get_cutoffs(Relation rel, const VacuumParams params,
* that only one vacuum process can be working on a particular table at
* any time, and that each vacuum is always an independent transaction.
*/
- cutoffs->OldestXmin = GetOldestNonRemovableTransactionId(rel);
+ cutoffs->OldestXmin =
+ GetOldestNonRemovableTransactionIdWithReason(rel,
+ &cutoffs->oldest_xmin_info);
Assert(TransactionIdIsNormal(cutoffs->OldestXmin));
diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c
index 200f72c6e25..5fe266234b1 100644
--- a/src/backend/storage/ipc/procarray.c
+++ b/src/backend/storage/ipc/procarray.c
@@ -241,6 +241,12 @@ typedef struct ComputeXidHorizonsResult
* session's temporary tables.
*/
TransactionId temp_oldest_nonremovable;
+
+ /* Identify what forced each of the horizons above. */
+ OldestXminInfo shared_oldest_nonremovable_info;
+ OldestXminInfo catalog_oldest_nonremovable_info;
+ OldestXminInfo data_oldest_nonremovable_info;
+ OldestXminInfo temp_oldest_nonremovable_info;
} ComputeXidHorizonsResult;
/*
@@ -1622,6 +1628,46 @@ TransactionIdIsInProgress(TransactionId xid)
return false;
}
+/*
+ * Store horizon provenance in *info if caller requested it.
+ *
+ * Callers pass NULL when they are not interested in tracking the source.
+ */
+static inline void
+OldestXminInfoSet(OldestXminInfo *info, OldestXminSource source,
+ int backend_pid)
+{
+ if (info == NULL)
+ return;
+
+ info->source = source;
+ info->backend_pid = backend_pid;
+}
+
+/*
+ * Update a tracked OldestXmin horizon with a candidate xid and source.
+ *
+ * If the candidate is older than *target, adopt it and remember why.
+ */
+static inline void
+UpdateOldestXmin(TransactionId *target, OldestXminInfo *info,
+ TransactionId candidate, OldestXminSource source,
+ int backend_pid)
+{
+ TransactionId old;
+ TransactionId new_horizon;
+
+ if (!TransactionIdIsValid(candidate))
+ return;
+
+ old = *target;
+ new_horizon = TransactionIdOlder(old, candidate);
+ if (TransactionIdEquals(new_horizon, old))
+ return;
+
+ *target = new_horizon;
+ OldestXminInfoSet(info, source, backend_pid);
+}
/*
* Determine XID horizons.
@@ -1689,6 +1735,8 @@ ComputeXidHorizons(ComputeXidHorizonsResult *h)
/* inferred after ProcArrayLock is released */
h->catalog_oldest_nonremovable = InvalidTransactionId;
+ OldestXminInfoSet(&h->catalog_oldest_nonremovable_info,
+ OLDESTXMIN_SOURCE_OTHER, 0);
LWLockAcquire(ProcArrayLock, LW_SHARED);
@@ -1710,6 +1758,10 @@ ComputeXidHorizons(ComputeXidHorizonsResult *h)
h->oldest_considered_running = initial;
h->shared_oldest_nonremovable = initial;
h->data_oldest_nonremovable = initial;
+ OldestXminInfoSet(&h->shared_oldest_nonremovable_info,
+ OLDESTXMIN_SOURCE_OTHER, 0);
+ OldestXminInfoSet(&h->data_oldest_nonremovable_info,
+ OLDESTXMIN_SOURCE_OTHER, 0);
/*
* Only modifications made by this backend affect the horizon for
@@ -1724,9 +1776,17 @@ ComputeXidHorizons(ComputeXidHorizonsResult *h)
* latestCompletedXid.
*/
if (TransactionIdIsValid(MyProc->xid))
+ {
h->temp_oldest_nonremovable = MyProc->xid;
+ OldestXminInfoSet(&h->temp_oldest_nonremovable_info,
+ OLDESTXMIN_SOURCE_ACTIVE_TRANSACTION, MyProcPid);
+ }
else
+ {
h->temp_oldest_nonremovable = initial;
+ OldestXminInfoSet(&h->temp_oldest_nonremovable_info,
+ OLDESTXMIN_SOURCE_OTHER, 0);
+ }
}
/*
@@ -1744,6 +1804,8 @@ ComputeXidHorizons(ComputeXidHorizonsResult *h)
int8 statusFlags = ProcGlobal->statusFlags[index];
TransactionId xid;
TransactionId xmin;
+ OldestXminSource candidate_source;
+ int candidate_pid;
/* Fetch xid just once - see GetNewTransactionId */
xid = UINT32_ACCESS_ONCE(other_xids[index]);
@@ -1768,8 +1830,8 @@ ComputeXidHorizons(ComputeXidHorizonsResult *h)
* backends are protected even without this check, it can't hurt to
* include them here as well..
*/
- h->oldest_considered_running =
- TransactionIdOlder(h->oldest_considered_running, xmin);
+ UpdateOldestXmin(&h->oldest_considered_running, NULL, xmin,
+ OLDESTXMIN_SOURCE_OTHER, 0);
/*
* Skip over backends either vacuuming (which is ok with rows being
@@ -1780,8 +1842,17 @@ ComputeXidHorizons(ComputeXidHorizonsResult *h)
continue;
/* shared tables need to take backends in all databases into account */
- h->shared_oldest_nonremovable =
- TransactionIdOlder(h->shared_oldest_nonremovable, xmin);
+ candidate_pid = proc->pid;
+ if (proc->pid == 0)
+ candidate_source = OLDESTXMIN_SOURCE_PREPARED_TRANSACTION;
+ else if (statusFlags & PROC_AFFECTS_ALL_HORIZONS)
+ candidate_source = OLDESTXMIN_SOURCE_HOT_STANDBY_FEEDBACK;
+ else
+ candidate_source = OLDESTXMIN_SOURCE_ACTIVE_TRANSACTION;
+
+ UpdateOldestXmin(&h->shared_oldest_nonremovable,
+ &h->shared_oldest_nonremovable_info,
+ xmin, candidate_source, candidate_pid);
/*
* Normally sessions in other databases are ignored for anything but
@@ -1807,8 +1878,9 @@ ComputeXidHorizons(ComputeXidHorizonsResult *h)
(statusFlags & PROC_AFFECTS_ALL_HORIZONS) ||
in_recovery)
{
- h->data_oldest_nonremovable =
- TransactionIdOlder(h->data_oldest_nonremovable, xmin);
+ UpdateOldestXmin(&h->data_oldest_nonremovable,
+ &h->data_oldest_nonremovable_info,
+ xmin, candidate_source, candidate_pid);
}
}
@@ -1827,12 +1899,14 @@ ComputeXidHorizons(ComputeXidHorizonsResult *h)
if (in_recovery)
{
- h->oldest_considered_running =
- TransactionIdOlder(h->oldest_considered_running, kaxmin);
- h->shared_oldest_nonremovable =
- TransactionIdOlder(h->shared_oldest_nonremovable, kaxmin);
- h->data_oldest_nonremovable =
- TransactionIdOlder(h->data_oldest_nonremovable, kaxmin);
+ UpdateOldestXmin(&h->oldest_considered_running, NULL,
+ kaxmin, OLDESTXMIN_SOURCE_OTHER, 0);
+ UpdateOldestXmin(&h->shared_oldest_nonremovable,
+ &h->shared_oldest_nonremovable_info,
+ kaxmin, OLDESTXMIN_SOURCE_OTHER, 0);
+ UpdateOldestXmin(&h->data_oldest_nonremovable,
+ &h->data_oldest_nonremovable_info,
+ kaxmin, OLDESTXMIN_SOURCE_OTHER, 0);
/* temp relations cannot be accessed in recovery */
}
@@ -1844,10 +1918,12 @@ ComputeXidHorizons(ComputeXidHorizonsResult *h)
/*
* Check whether there are replication slots requiring an older xmin.
*/
- h->shared_oldest_nonremovable =
- TransactionIdOlder(h->shared_oldest_nonremovable, h->slot_xmin);
- h->data_oldest_nonremovable =
- TransactionIdOlder(h->data_oldest_nonremovable, h->slot_xmin);
+ UpdateOldestXmin(&h->shared_oldest_nonremovable,
+ &h->shared_oldest_nonremovable_info,
+ h->slot_xmin, OLDESTXMIN_SOURCE_REPLICATION_SLOT, 0);
+ UpdateOldestXmin(&h->data_oldest_nonremovable,
+ &h->data_oldest_nonremovable_info,
+ h->slot_xmin, OLDESTXMIN_SOURCE_REPLICATION_SLOT, 0);
/*
* The only difference between catalog / data horizons is that the slot's
@@ -1857,13 +1933,16 @@ ComputeXidHorizons(ComputeXidHorizonsResult *h)
* that also can contain catalogs.
*/
h->shared_oldest_nonremovable_raw = h->shared_oldest_nonremovable;
- h->shared_oldest_nonremovable =
- TransactionIdOlder(h->shared_oldest_nonremovable,
- h->slot_catalog_xmin);
+ UpdateOldestXmin(&h->shared_oldest_nonremovable,
+ &h->shared_oldest_nonremovable_info,
+ h->slot_catalog_xmin,
+ OLDESTXMIN_SOURCE_REPLICATION_SLOT, 0);
h->catalog_oldest_nonremovable = h->data_oldest_nonremovable;
- h->catalog_oldest_nonremovable =
- TransactionIdOlder(h->catalog_oldest_nonremovable,
- h->slot_catalog_xmin);
+ h->catalog_oldest_nonremovable_info = h->data_oldest_nonremovable_info;
+ UpdateOldestXmin(&h->catalog_oldest_nonremovable,
+ &h->catalog_oldest_nonremovable_info,
+ h->slot_catalog_xmin,
+ OLDESTXMIN_SOURCE_REPLICATION_SLOT, 0);
/*
* It's possible that slots backed up the horizons further than
@@ -1951,25 +2030,53 @@ GlobalVisHorizonKindForRel(Relation rel)
*/
TransactionId
GetOldestNonRemovableTransactionId(Relation rel)
+{
+ /* Delegate to the WithReason variant to avoid duplicated logic */
+ return GetOldestNonRemovableTransactionIdWithReason(rel, NULL);
+}
+
+/*
+ * Return horizon like GetOldestNonRemovableTransactionId(), and also classify
+ * the reason that determined that horizon at the time of computation.
+ */
+TransactionId
+GetOldestNonRemovableTransactionIdWithReason(Relation rel,
+ OldestXminInfo *out_info)
{
ComputeXidHorizonsResult horizons;
+ TransactionId target = InvalidTransactionId;
+ GlobalVisHorizonKind kind;
+ const OldestXminInfo *source_info = NULL;
+
+ if (out_info != NULL)
+ OldestXminInfoSet(out_info, OLDESTXMIN_SOURCE_OTHER, 0);
ComputeXidHorizons(&horizons);
- switch (GlobalVisHorizonKindForRel(rel))
+ kind = GlobalVisHorizonKindForRel(rel);
+ switch (kind)
{
case VISHORIZON_SHARED:
- return horizons.shared_oldest_nonremovable;
+ target = horizons.shared_oldest_nonremovable;
+ source_info = &horizons.shared_oldest_nonremovable_info;
+ break;
case VISHORIZON_CATALOG:
- return horizons.catalog_oldest_nonremovable;
+ target = horizons.catalog_oldest_nonremovable;
+ source_info = &horizons.catalog_oldest_nonremovable_info;
+ break;
case VISHORIZON_DATA:
- return horizons.data_oldest_nonremovable;
+ target = horizons.data_oldest_nonremovable;
+ source_info = &horizons.data_oldest_nonremovable_info;
+ break;
case VISHORIZON_TEMP:
- return horizons.temp_oldest_nonremovable;
+ target = horizons.temp_oldest_nonremovable;
+ source_info = &horizons.temp_oldest_nonremovable_info;
+ break;
}
- /* just to prevent compiler warnings */
- return InvalidTransactionId;
+ if (out_info != NULL && TransactionIdIsValid(target) && source_info != NULL)
+ *out_info = *source_info;
+ return target;
}
/*
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index 1f3290c7fbf..025c22eabd2 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -24,6 +24,7 @@
#include "parser/parse_node.h"
#include "storage/buf.h"
#include "storage/lock.h"
+#include "storage/procarray.h"
#include "utils/relcache.h"
/*
@@ -288,6 +289,9 @@ struct VacuumCutoffs
*/
TransactionId FreezeLimit;
MultiXactId MultiXactCutoff;
+
+ /* What decided OldestXmin at acquisition time */
+ OldestXminInfo oldest_xmin_info;
};
/*
diff --git a/src/include/storage/procarray.h b/src/include/storage/procarray.h
index 2f4ae06c279..fb459f9b6c9 100644
--- a/src/include/storage/procarray.h
+++ b/src/include/storage/procarray.h
@@ -20,6 +20,25 @@
#include "utils/snapshot.h"
+/*
+ * Identifies what determined a relation's OldestXmin horizon.
+ * Used by autovacuum to report why dead tuples were not removable.
+ */
+typedef enum OldestXminSource
+{
+ OLDESTXMIN_SOURCE_ACTIVE_TRANSACTION,
+ OLDESTXMIN_SOURCE_HOT_STANDBY_FEEDBACK,
+ OLDESTXMIN_SOURCE_PREPARED_TRANSACTION,
+ OLDESTXMIN_SOURCE_REPLICATION_SLOT,
+ OLDESTXMIN_SOURCE_OTHER
+} OldestXminSource;
+
+typedef struct OldestXminInfo
+{
+ OldestXminSource source;
+ int backend_pid;
+} OldestXminInfo;
+
extern Size ProcArrayShmemSize(void);
extern void ProcArrayShmemInit(void);
extern void ProcArrayAdd(PGPROC *proc);
@@ -54,6 +73,8 @@ extern RunningTransactions GetRunningTransactionData(void);
extern bool TransactionIdIsInProgress(TransactionId xid);
extern TransactionId GetOldestNonRemovableTransactionId(Relation rel);
+extern TransactionId GetOldestNonRemovableTransactionIdWithReason(Relation rel,
+ OldestXminInfo *info);
extern TransactionId GetOldestTransactionIdConsideredRunning(void);
extern TransactionId GetOldestActiveTransactionId(bool inCommitOnly,
bool allDbs);
diff --git a/src/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build
index f258bf1ccd9..b42fed91184 100644
--- a/src/test/modules/test_misc/meson.build
+++ b/src/test/modules/test_misc/meson.build
@@ -18,6 +18,7 @@ tests += {
't/007_catcache_inval.pl',
't/008_replslot_single_user.pl',
't/009_log_temp_files.pl',
+ 't/010_autovacuum_oldest_xmin_reason.pl',
],
},
}
diff --git a/src/test/modules/test_misc/t/010_autovacuum_oldest_xmin_reason.pl b/src/test/modules/test_misc/t/010_autovacuum_oldest_xmin_reason.pl
new file mode 100644
index 00000000000..5818f143182
--- /dev/null
+++ b/src/test/modules/test_misc/t/010_autovacuum_oldest_xmin_reason.pl
@@ -0,0 +1,186 @@
+# Copyright (c) 2025, PostgreSQL Global Development Group
+#
+# Validate that autovacuum 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', qq[
+max_prepared_transactions = 5
+autovacuum_naptime = 1s
+autovacuum_vacuum_cost_delay = 0
+log_autovacuum_min_duration = 0
+]);
+$node->start;
+
+# Helper to wait for autovacuum to complete
+sub wait_for_autovacuum
+{
+ my ($relname) = @_;
+ my $vacuumed = $node->poll_query_until(
+ 'postgres', qq[
+ SELECT last_autovacuum IS NOT NULL
+ FROM pg_stat_user_tables
+ WHERE relname = '$relname';
+ ], 't');
+ ok($vacuumed, "autovacuum completed on $relname")
+ or BAIL_OUT("timed out waiting for autovacuum on $relname");
+}
+
+#
+# Active transaction
+#
+my $active_table = 'av_oldestxmin_active';
+$node->safe_psql('postgres', qq[
+CREATE TABLE $active_table(id int)
+ WITH (autovacuum_vacuum_scale_factor = 0,
+ autovacuum_vacuum_threshold = 1);
+INSERT INTO $active_table SELECT generate_series(1, 10);
+]);
+
+my $offset = -s $node->logfile;
+
+my $blocker = $node->background_psql('postgres');
+$blocker->query_safe(qq[
+BEGIN;
+SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+SELECT * FROM $active_table;
+]);
+
+my $blocker_pid = $blocker->query_safe('SELECT pg_backend_pid();');
+chomp($blocker_pid);
+
+$node->safe_psql('postgres', "DELETE FROM $active_table;");
+
+wait_for_autovacuum($active_table);
+
+ok( $node->wait_for_log(
+ qr/automatic vacuum of table "\Qpostgres.public.$active_table\E":.*oldest xmin source: active transaction \(pid=$blocker_pid\)/s,
+ $offset),
+ 'autovacuum reported active transaction as oldest xmin source');
+
+$blocker->query_safe('ROLLBACK;');
+$blocker->quit;
+$node->safe_psql('postgres', "DROP TABLE $active_table;");
+
+#
+# Prepared transaction
+#
+my $prepared_table = 'av_oldestxmin_prepared';
+$node->safe_psql('postgres', qq[
+CREATE TABLE $prepared_table(id int)
+ WITH (autovacuum_vacuum_scale_factor = 0,
+ autovacuum_vacuum_threshold = 1);
+INSERT INTO $prepared_table SELECT generate_series(1, 10);
+]);
+
+$node->safe_psql('postgres', qq[
+BEGIN;
+PREPARE TRANSACTION 'gx_autovac_xmin';
+]);
+
+$offset = -s $node->logfile;
+
+$node->safe_psql('postgres', "DELETE FROM $prepared_table;");
+
+wait_for_autovacuum($prepared_table);
+
+ok( $node->wait_for_log(
+ qr/automatic vacuum of table "\Qpostgres.public.$prepared_table\E":.*oldest xmin source: prepared transaction/s,
+ $offset),
+ 'autovacuum reported prepared transaction as oldest xmin source');
+
+$node->safe_psql('postgres', "ROLLBACK PREPARED 'gx_autovac_xmin';");
+$node->safe_psql('postgres', "DROP TABLE $prepared_table;");
+
+#
+# Replication slot
+#
+my $slot_table = 'av_oldestxmin_slot';
+$node->safe_psql('postgres', qq[
+CREATE TABLE $slot_table(id int);
+]);
+
+$node->safe_psql('postgres',q[
+SELECT pg_create_logical_replication_slot('autovac_oldestxmin_slot', 'test_decoding');
+]);
+$node->safe_psql('postgres', "DROP TABLE $slot_table;");
+
+my $slotstderr = '';
+$node->psql('postgres',
+ 'VACUUM (VERBOSE) pg_catalog.pg_class;',
+ stderr => \$slotstderr
+);
+like(
+ $slotstderr,
+ qr/oldest xmin source: replication slot/,
+ 'VACUUM VERBOSE reported replication slot as oldest xmin source');
+
+$node->safe_psql('postgres', q[
+SELECT pg_drop_replication_slot('autovac_oldestxmin_slot');
+]);
+
+#
+# Hot standby feedback
+#
+my $hs_table = 'av_oldestxmin_hotstandby';
+$node->safe_psql('postgres', qq[
+CREATE TABLE $hs_table(id int)
+ WITH (autovacuum_vacuum_scale_factor = 0,
+ autovacuum_vacuum_threshold = 1);
+INSERT INTO $hs_table SELECT generate_series(1, 10);
+]);
+
+# Set up a hot standby
+$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', qq[
+hot_standby_feedback = on
+wal_receiver_status_interval = 1s
+]);
+$standby->start;
+$node->wait_for_catchup($standby, 'replay', $node->lsn('flush'));
+
+my $standby_reader = $standby->background_psql('postgres');
+$standby_reader->query_safe('BEGIN;');
+
+$node->poll_query_until('postgres', q[
+SELECT bool_or(backend_xmin IS NOT NULL)
+FROM pg_stat_replication;
+], 't');
+
+my $hs_pid = $node->safe_psql('postgres', q[
+SELECT pid FROM pg_stat_replication
+WHERE backend_xmin IS NOT NULL
+ORDER BY pid
+LIMIT 1;
+]);
+chomp($hs_pid);
+
+$offset = -s $node->logfile;
+
+$node->safe_psql('postgres', "DELETE FROM $hs_table;");
+
+wait_for_autovacuum($hs_table);
+
+ok( $node->wait_for_log(
+ qr/oldest xmin source: hot standby feedback \(pid=$hs_pid\)/,
+ $offset),
+ 'autovacuum reported hot standby feedback as oldest xmin source');
+
+$standby_reader->query_safe('ROLLBACK;');
+$standby_reader->quit;
+$node->safe_psql('postgres', "DROP TABLE $hs_table;");
+
+$standby->stop;
+$node->stop;
+done_testing();
--
2.47.3
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Report oldest xmin source when autovacuum cannot remove tuples
@ 2025-10-31 08:00 wenhui qiu <qiuwenhuifx@gmail.com>
parent: Shinya Kato <shinya11.kato@gmail.com>
2 siblings, 1 reply; 18+ messages in thread
From: wenhui qiu @ 2025-10-31 08:00 UTC (permalink / raw)
To: Shinya Kato <shinya11.kato@gmail.com>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
HI
Thank you for your path ,This path is extremely helpful.
> +/*
> + * Identifies what determined a relation's OldestXmin horizon.
> + * Used by autovacuum to report why dead tuples were not removable.
> + */
> +typedef enum OldestXminSource
> +{
> + OLDESTXMIN_SOURCE_ACTIVE_TRANSACTION,
> + OLDESTXMIN_SOURCE_HOT_STANDBY_FEEDBACK,
> + OLDESTXMIN_SOURCE_PREPARED_TRANSACTION,
> + OLDESTXMIN_SOURCE_REPLICATION_SLOT,
> + OLDESTXMIN_SOURCE_OTHER
> +} OldestXminSource;
> +
> +typedef struct OldestXminInfo
> +{
> + OldestXminSource source;
> + int backend_pid;
> +} OldestXminInfo;
I have a question for like this
one session
begin;
select * from table_a
not commit or not closed session
It is in OLDESTXMIN_SOURCE_ACTIVE_TRANSACTION type ?
Thank
On Fri, Oct 31, 2025 at 2:32 PM Shinya Kato <shinya11.kato@gmail.com> wrote:
> Hi hackers,
>
> I am proposing to add the reason for the oldest xmin to VACUUM logs.
> This feature would be useful for identifying why dead tuples cannot be
> removed, thereby helping to diagnose and prevent table bloat.
>
> The current logs only indicate that dead tuples could not be reclaimed
> due to the oldest xmin, but they do not reveal the underlying reason.
> To identify the cause, it is necessary to query multiple views:
> pg_stat_activity (for active transactions), pg_prepared_xacts (for
> prepared statements), pg_replication_slots (for replication slots),
> and pg_stat_replication (for hot standby feedback). However, because
> the data in these views is volatile, it is difficult to retroactively
> determine what was holding the oldest xmin at the specific time the
> log message was generated.
>
> This PoC patch addresses this problem. The implementation now outputs
> the reason for the oldest xmin and, where applicable, the backend PID.
> This information was originally discarded when calculating the oldest
> xmin horizon, and the computation required to retrieve these reasons
> is considered reasonable.
>
> The patch is attached. What do you think?
>
> --
> Best regards,
> Shinya Kato
> NTT OSS Center
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Report oldest xmin source when autovacuum cannot remove tuples
@ 2025-11-04 01:29 Shinya Kato <shinya11.kato@gmail.com>
parent: wenhui qiu <qiuwenhuifx@gmail.com>
0 siblings, 0 replies; 18+ messages in thread
From: Shinya Kato @ 2025-11-04 01:29 UTC (permalink / raw)
To: wenhui qiu <qiuwenhuifx@gmail.com>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Fri, Oct 31, 2025 at 5:01 PM wenhui qiu <qiuwenhuifx@gmail.com> wrote:
>
> HI
> Thank you for your path ,This path is extremely helpful.
Thank you!
> > +/*
> > + * Identifies what determined a relation's OldestXmin horizon.
> > + * Used by autovacuum to report why dead tuples were not removable.
> > + */
> > +typedef enum OldestXminSource
> > +{
> > + OLDESTXMIN_SOURCE_ACTIVE_TRANSACTION,
> > + OLDESTXMIN_SOURCE_HOT_STANDBY_FEEDBACK,
> > + OLDESTXMIN_SOURCE_PREPARED_TRANSACTION,
> > + OLDESTXMIN_SOURCE_REPLICATION_SLOT,
> > + OLDESTXMIN_SOURCE_OTHER
> > +} OldestXminSource;
> > +
> > +typedef struct OldestXminInfo
> > +{
> > + OldestXminSource source;
> > + int backend_pid;
> > +} OldestXminInfo;
> I have a question for like this
> one session
> begin;
> select * from table_a
> not commit or not closed session
> It is in OLDESTXMIN_SOURCE_ACTIVE_TRANSACTION type ?
Exactly. Looking at 010_autovacuum_oldest_xmin_reason.pl should make
it clear which logs are output in which cases. (I just noticed there
seems to be a case where the test fails. I need to fix that.)
--
Best regards,
Shinya Kato
NTT OSS Center
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Report oldest xmin source when autovacuum cannot remove tuples
@ 2025-11-04 15:58 Fujii Masao <masao.fujii@gmail.com>
parent: Shinya Kato <shinya11.kato@gmail.com>
2 siblings, 1 reply; 18+ messages in thread
From: Fujii Masao @ 2025-11-04 15:58 UTC (permalink / raw)
To: Shinya Kato <shinya11.kato@gmail.com>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Fri, Oct 31, 2025 at 3:32 PM Shinya Kato <shinya11.kato@gmail.com> wrote:
>
> Hi hackers,
>
> I am proposing to add the reason for the oldest xmin to VACUUM logs.
> This feature would be useful for identifying why dead tuples cannot be
> removed, thereby helping to diagnose and prevent table bloat.
+1
I like this idea. Thanks for working on this!
> This PoC patch addresses this problem. The implementation now outputs
> the reason for the oldest xmin and, where applicable, the backend PID.
> This information was originally discarded when calculating the oldest
> xmin horizon, and the computation required to retrieve these reasons
> is considered reasonable.
>
> The patch is attached. What do you think?
According to cfbot, the 010_autovacuum_oldest_xmin_reason regression test
passes on some platforms but fails on others (see [1]), so it doesn't
appear stable.
When I set up a primary and standby with hot_standby_feedback enabled,
then created an old prepared transaction expected to prevent dead tuples
from being vacuumed, VACUUM VERBOSE reported "hot standby feedback"
instead of "prepared transaction" as the oldest xmin source. This isn't a bug
since both xmins are the same in this case. But it may be confusing?
Would it be better to report "prepared transaction" in such cases?
+ case OLDESTXMIN_SOURCE_ACTIVE_TRANSACTION:
+ msgfmt = include_pid ?
+ _("oldest xmin source: active transaction (pid=%d)\n") :
+ _("oldest xmin source: active transaction\n");
+ break;
+ case OLDESTXMIN_SOURCE_HOT_STANDBY_FEEDBACK:
+ msgfmt = include_pid ?
+ _("oldest xmin source: hot standby feedback (pid=%d)\n") :
+ _("oldest xmin source: hot standby feedback\n");
In these two cases, the PID should always be non-zero, so the message
formats without (pid=%d) might not be necessary.
+ /* Identify what forced each of the horizons above. */
+ OldestXminInfo shared_oldest_nonremovable_info;
+ OldestXminInfo catalog_oldest_nonremovable_info;
+ OldestXminInfo data_oldest_nonremovable_info;
+ OldestXminInfo temp_oldest_nonremovable_info;
It might be good to add a comment explaining why we track
sources only for these four oldest xmins, and not for others
like oldest_considered_running.
+ TransactionId old;
+ TransactionId new_horizon;
+
+ if (!TransactionIdIsValid(candidate))
+ return;
The TransactionIdIsValid(candidate) check may be redundant,
since TransactionIdOlder(old, candidate) already performs
the same validation.
- switch (GlobalVisHorizonKindForRel(rel))
+ kind = GlobalVisHorizonKindForRel(rel);
+ switch (kind)
This change doesn't seem necessary.
Regards,
[1] https://cirrus-ci.com/task/6063548834512896
--
Fujii Masao
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Report oldest xmin source when autovacuum cannot remove tuples
@ 2025-11-11 10:40 wenhui qiu <qiuwenhuifx@gmail.com>
parent: Fujii Masao <masao.fujii@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: wenhui qiu @ 2025-11-11 10:40 UTC (permalink / raw)
To: Shinya Kato <shinya11.kato@gmail.com>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Hi
Thank you for your response , In this context, that session state is idle
in transaction .If we could further distinguish between active sessions
that are still running and idle sessions, that would be clearer.
Thanks
On Tue, Nov 4, 2025 at 11:59 PM Fujii Masao <masao.fujii@gmail.com> wrote:
> On Fri, Oct 31, 2025 at 3:32 PM Shinya Kato <shinya11.kato@gmail.com>
> wrote:
> >
> > Hi hackers,
> >
> > I am proposing to add the reason for the oldest xmin to VACUUM logs.
> > This feature would be useful for identifying why dead tuples cannot be
> > removed, thereby helping to diagnose and prevent table bloat.
>
> +1
>
> I like this idea. Thanks for working on this!
>
>
> > This PoC patch addresses this problem. The implementation now outputs
> > the reason for the oldest xmin and, where applicable, the backend PID.
> > This information was originally discarded when calculating the oldest
> > xmin horizon, and the computation required to retrieve these reasons
> > is considered reasonable.
> >
> > The patch is attached. What do you think?
>
> According to cfbot, the 010_autovacuum_oldest_xmin_reason regression test
> passes on some platforms but fails on others (see [1]), so it doesn't
> appear stable.
>
>
> When I set up a primary and standby with hot_standby_feedback enabled,
> then created an old prepared transaction expected to prevent dead tuples
> from being vacuumed, VACUUM VERBOSE reported "hot standby feedback"
> instead of "prepared transaction" as the oldest xmin source. This isn't a
> bug
> since both xmins are the same in this case. But it may be confusing?
> Would it be better to report "prepared transaction" in such cases?
>
>
> + case OLDESTXMIN_SOURCE_ACTIVE_TRANSACTION:
> + msgfmt = include_pid ?
> + _("oldest xmin source: active transaction (pid=%d)\n") :
> + _("oldest xmin source: active transaction\n");
> + break;
> + case OLDESTXMIN_SOURCE_HOT_STANDBY_FEEDBACK:
> + msgfmt = include_pid ?
> + _("oldest xmin source: hot standby feedback (pid=%d)\n") :
> + _("oldest xmin source: hot standby feedback\n");
>
> In these two cases, the PID should always be non-zero, so the message
> formats without (pid=%d) might not be necessary.
>
>
> + /* Identify what forced each of the horizons above. */
> + OldestXminInfo shared_oldest_nonremovable_info;
> + OldestXminInfo catalog_oldest_nonremovable_info;
> + OldestXminInfo data_oldest_nonremovable_info;
> + OldestXminInfo temp_oldest_nonremovable_info;
>
> It might be good to add a comment explaining why we track
> sources only for these four oldest xmins, and not for others
> like oldest_considered_running.
>
>
> + TransactionId old;
> + TransactionId new_horizon;
> +
> + if (!TransactionIdIsValid(candidate))
> + return;
>
> The TransactionIdIsValid(candidate) check may be redundant,
> since TransactionIdOlder(old, candidate) already performs
> the same validation.
>
>
> - switch (GlobalVisHorizonKindForRel(rel))
> + kind = GlobalVisHorizonKindForRel(rel);
> + switch (kind)
>
> This change doesn't seem necessary.
>
> Regards,
>
> [1] https://cirrus-ci.com/task/6063548834512896
>
> --
> Fujii Masao
>
>
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Report oldest xmin source when autovacuum cannot remove tuples
@ 2025-11-15 00:25 Sami Imseih <samimseih@gmail.com>
parent: wenhui qiu <qiuwenhuifx@gmail.com>
0 siblings, 3 replies; 18+ messages in thread
From: Sami Imseih @ 2025-11-15 00:25 UTC (permalink / raw)
To: wenhui qiu <qiuwenhuifx@gmail.com>; +Cc: Shinya Kato <shinya11.kato@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Thanks for starting this thread! This is a very useful
feature that users will find beneficial to easily narrow
down the reason the xmin horizon is being held back,
and take action.
Adding this information to the vacuum logging is useful, but
I can see this information being exposed in a view as well in
the future.
I have a few comments:
A few minor ones:
1/ pid should be declared as "pid_t"
2/ last value of an enum should be have a traling comma
+typedef enum OldestXminSource
+{
+ OLDESTXMIN_SOURCE_ACTIVE_TRANSACTION,
+ OLDESTXMIN_SOURCE_HOT_STANDBY_FEEDBACK,
+ OLDESTXMIN_SOURCE_PREPARED_TRANSACTION,
+ OLDESTXMIN_SOURCE_REPLICATION_SLOT,
+ OLDESTXMIN_SOURCE_OTHER
+} OldestXminSource;
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.
This takes what you are doing in v1 inside ComputeXidHorizons()
into a new routine. I think this is a cleaner approach.
5/ Also, I think we should also include tests for serializable
transactions
What do you think?
--
Sami Imseih
Amazon Web Services (AWS)
From 53915bc1fd06790fc112cb2ac9e4b9caa742cf92 Mon Sep 17 00:00:00 2001
From: Sami Imseih <simseih@amazon.com>
Date: Fri, 14 Nov 2025 18:15:25 -0600
Subject: [PATCH 1/1] sketch of cutoff reasons
---
src/backend/access/heap/vacuumlazy.c | 5 +++
src/backend/storage/ipc/procarray.c | 60 ++++++++++++++++++++++++++++
src/include/storage/procarray.h | 1 +
3 files changed, 66 insertions(+)
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index deb9a3dc0d1..df1df6b0733 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -152,6 +152,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"
@@ -1047,6 +1048,10 @@ heap_vacuum_rel(Relation rel, const VacuumParams params,
appendStringInfo(&buf,
_("removable cutoff: %u, which was %d XIDs old when operation ended\n"),
vacrel->cutoffs.OldestXmin, diff);
+
+ if (vacrel->recently_dead_tuples > 0)
+ BackendXidFindCutOffReason(vacrel->cutoffs.OldestXmin);
+
if (frozenxid_updated)
{
diff = (int32) (vacrel->NewRelfrozenXid -
diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c
index 200f72c6e25..45dfe8a9be2 100644
--- a/src/backend/storage/ipc/procarray.c
+++ b/src/backend/storage/ipc/procarray.c
@@ -3244,6 +3244,66 @@ BackendXidGetPid(TransactionId xid)
return result;
}
+void
+BackendXidFindCutOffReason(TransactionId xid)
+{
+ ProcArrayStruct *arrayP = procArray;
+ TransactionId *other_xids = ProcGlobal->xids;
+ bool found_reason = false;
+
+ Assert(xid != InvalidTransactionId);
+
+ LWLockAcquire(ProcArrayLock, LW_SHARED);
+
+ elog(NOTICE, ">>>>>> looking up reason for %d", xid);
+
+ for (int index = 0; index < arrayP->numProcs; index++)
+ {
+ int pgprocno = arrayP->pgprocnos[index];
+ PGPROC *proc = &allProcs[pgprocno];
+
+ /* Case 1: xid matches the session's backend XID */
+ if (other_xids[index] == xid)
+ {
+ if (proc->pid == 0)
+ /* with a prepared transaction */
+ elog(NOTICE, ">>>>>> prepared transaction proc->statusFlags %u", proc->statusFlags);
+ else
+ /* or a write transaction */
+ elog(NOTICE, ">>>>>> xid: transaction BackendXidGetPid = %d proc->statusFlags %u", proc->pid, proc->statusFlags);
+
+ found_reason = true;
+ break;
+ }
+
+ /* Case 2: xid matches xmin */
+ if (proc->xmin == xid)
+ {
+ /* or affects horizons, which is due to hot_standby_feedback */
+ if (proc->statusFlags & PROC_AFFECTS_ALL_HORIZONS)
+ {
+ elog(NOTICE, ">>>>>> hot_standby_feedback == pid of walreceiver %d proc->statusFlags %u", proc->pid, proc->statusFlags);
+ found_reason = true;
+ break;
+ }
+
+ /* or a read-only transaction */
+ elog(NOTICE, ">>>>>> xmin: transaction BackendXidGetPid = %d proc->statusFlags = %u", proc->pid, proc->statusFlags);
+ found_reason = true;
+ break;
+ }
+ }
+
+ /*
+ * we failed to find reason, so it's likely a logical replication slot, or
+ * some other reason
+ */
+ if (!found_reason)
+ elog(NOTICE, ">>>>>> other reasons, including logical replication slot");
+
+ LWLockRelease(ProcArrayLock);
+}
+
/*
* IsBackendPid -- is a given pid a running backend
*
diff --git a/src/include/storage/procarray.h b/src/include/storage/procarray.h
index 2f4ae06c279..b0cdeedb848 100644
--- a/src/include/storage/procarray.h
+++ b/src/include/storage/procarray.h
@@ -71,6 +71,7 @@ extern void ProcNumberGetTransactionIds(int procNumber, TransactionId *xid,
extern PGPROC *BackendPidGetProc(int pid);
extern PGPROC *BackendPidGetProcWithLock(int pid);
extern int BackendXidGetPid(TransactionId xid);
+extern void BackendXidFindCutOffReason(TransactionId xid);
extern bool IsBackendPid(int pid);
extern VirtualTransactionId *GetCurrentVirtualXIDs(TransactionId limitXmin,
--
2.50.1 (Apple Git-155)
Attachments:
[text/plain] 0001-sketch-of-cutoff-reasons.txt (3.9K, 2-0001-sketch-of-cutoff-reasons.txt)
download | inline diff:
From 53915bc1fd06790fc112cb2ac9e4b9caa742cf92 Mon Sep 17 00:00:00 2001
From: Sami Imseih <simseih@amazon.com>
Date: Fri, 14 Nov 2025 18:15:25 -0600
Subject: [PATCH 1/1] sketch of cutoff reasons
---
src/backend/access/heap/vacuumlazy.c | 5 +++
src/backend/storage/ipc/procarray.c | 60 ++++++++++++++++++++++++++++
src/include/storage/procarray.h | 1 +
3 files changed, 66 insertions(+)
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index deb9a3dc0d1..df1df6b0733 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -152,6 +152,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"
@@ -1047,6 +1048,10 @@ heap_vacuum_rel(Relation rel, const VacuumParams params,
appendStringInfo(&buf,
_("removable cutoff: %u, which was %d XIDs old when operation ended\n"),
vacrel->cutoffs.OldestXmin, diff);
+
+ if (vacrel->recently_dead_tuples > 0)
+ BackendXidFindCutOffReason(vacrel->cutoffs.OldestXmin);
+
if (frozenxid_updated)
{
diff = (int32) (vacrel->NewRelfrozenXid -
diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c
index 200f72c6e25..45dfe8a9be2 100644
--- a/src/backend/storage/ipc/procarray.c
+++ b/src/backend/storage/ipc/procarray.c
@@ -3244,6 +3244,66 @@ BackendXidGetPid(TransactionId xid)
return result;
}
+void
+BackendXidFindCutOffReason(TransactionId xid)
+{
+ ProcArrayStruct *arrayP = procArray;
+ TransactionId *other_xids = ProcGlobal->xids;
+ bool found_reason = false;
+
+ Assert(xid != InvalidTransactionId);
+
+ LWLockAcquire(ProcArrayLock, LW_SHARED);
+
+ elog(NOTICE, ">>>>>> looking up reason for %d", xid);
+
+ for (int index = 0; index < arrayP->numProcs; index++)
+ {
+ int pgprocno = arrayP->pgprocnos[index];
+ PGPROC *proc = &allProcs[pgprocno];
+
+ /* Case 1: xid matches the session's backend XID */
+ if (other_xids[index] == xid)
+ {
+ if (proc->pid == 0)
+ /* with a prepared transaction */
+ elog(NOTICE, ">>>>>> prepared transaction proc->statusFlags %u", proc->statusFlags);
+ else
+ /* or a write transaction */
+ elog(NOTICE, ">>>>>> xid: transaction BackendXidGetPid = %d proc->statusFlags %u", proc->pid, proc->statusFlags);
+
+ found_reason = true;
+ break;
+ }
+
+ /* Case 2: xid matches xmin */
+ if (proc->xmin == xid)
+ {
+ /* or affects horizons, which is due to hot_standby_feedback */
+ if (proc->statusFlags & PROC_AFFECTS_ALL_HORIZONS)
+ {
+ elog(NOTICE, ">>>>>> hot_standby_feedback == pid of walreceiver %d proc->statusFlags %u", proc->pid, proc->statusFlags);
+ found_reason = true;
+ break;
+ }
+
+ /* or a read-only transaction */
+ elog(NOTICE, ">>>>>> xmin: transaction BackendXidGetPid = %d proc->statusFlags = %u", proc->pid, proc->statusFlags);
+ found_reason = true;
+ break;
+ }
+ }
+
+ /*
+ * we failed to find reason, so it's likely a logical replication slot, or
+ * some other reason
+ */
+ if (!found_reason)
+ elog(NOTICE, ">>>>>> other reasons, including logical replication slot");
+
+ LWLockRelease(ProcArrayLock);
+}
+
/*
* IsBackendPid -- is a given pid a running backend
*
diff --git a/src/include/storage/procarray.h b/src/include/storage/procarray.h
index 2f4ae06c279..b0cdeedb848 100644
--- a/src/include/storage/procarray.h
+++ b/src/include/storage/procarray.h
@@ -71,6 +71,7 @@ extern void ProcNumberGetTransactionIds(int procNumber, TransactionId *xid,
extern PGPROC *BackendPidGetProc(int pid);
extern PGPROC *BackendPidGetProcWithLock(int pid);
extern int BackendXidGetPid(TransactionId xid);
+extern void BackendXidFindCutOffReason(TransactionId xid);
extern bool IsBackendPid(int pid);
extern VirtualTransactionId *GetCurrentVirtualXIDs(TransactionId limitXmin,
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Report oldest xmin source when autovacuum cannot remove tuples
@ 2025-11-17 01:43 wenhui qiu <qiuwenhuifx@gmail.com>
parent: Sami Imseih <samimseih@gmail.com>
2 siblings, 0 replies; 18+ messages in thread
From: wenhui qiu @ 2025-11-17 01:43 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
> 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.
I like your idea , I think we also could consider introducing a GUC
parameter in the future, which would terminate sessions blocking vacuum
operations when the table's age reaches vacuum_failsafe_age.
Thanks
On Sat, Nov 15, 2025 at 8:25 AM Sami Imseih <samimseih@gmail.com> wrote:
> Thanks for starting this thread! This is a very useful
> feature that users will find beneficial to easily narrow
> down the reason the xmin horizon is being held back,
> and take action.
>
> Adding this information to the vacuum logging is useful, but
> I can see this information being exposed in a view as well in
> the future.
>
> I have a few comments:
>
> A few minor ones:
>
> 1/ pid should be declared as "pid_t"
>
> 2/ last value of an enum should be have a traling comma
> +typedef enum OldestXminSource
> +{
> + OLDESTXMIN_SOURCE_ACTIVE_TRANSACTION,
> + OLDESTXMIN_SOURCE_HOT_STANDBY_FEEDBACK,
> + OLDESTXMIN_SOURCE_PREPARED_TRANSACTION,
> + OLDESTXMIN_SOURCE_REPLICATION_SLOT,
> + OLDESTXMIN_SOURCE_OTHER
> +} OldestXminSource;
>
> 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.
>
> This takes what you are doing in v1 inside ComputeXidHorizons()
> into a new routine. I think this is a cleaner approach.
>
> 5/ Also, I think we should also include tests for serializable
> transactions
>
>
> What do you think?
>
> --
>
> Sami Imseih
> Amazon Web Services (AWS)
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Report oldest xmin source when autovacuum cannot remove tuples
@ 2025-11-17 02:51 Dilip Kumar <dilipbalaut@gmail.com>
parent: Sami Imseih <samimseih@gmail.com>
2 siblings, 1 reply; 18+ messages in thread
From: Dilip Kumar @ 2025-11-17 02:51 UTC (permalink / raw)
To: Sami Imseih <samimseih@gmail.com>; +Cc: wenhui qiu <qiuwenhuifx@gmail.com>; Shinya Kato <shinya11.kato@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Sat, Nov 15, 2025 at 5:56 AM Sami Imseih <samimseih@gmail.com> wrote:
>
> Thanks for starting this thread! This is a very useful
> feature that users will find beneficial to easily narrow
> down the reason the xmin horizon is being held back,
> and take action.
+1 for the idea. In BackendXidFindCutOffReason() you have directly
reported using NOTICE I believe that is just to show the idea and you
are planning to append this to the main message? Apart from that we
are looping the whole pgprocarray, however it is only done when we are
vacuuming with verbose mode so might not be that bad.
--
Regards,
Dilip Kumar
Google
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Report oldest xmin source when autovacuum cannot remove tuples
@ 2025-11-17 18:35 Sami Imseih <samimseih@gmail.com>
parent: Dilip Kumar <dilipbalaut@gmail.com>
0 siblings, 0 replies; 18+ messages in thread
From: Sami Imseih @ 2025-11-17 18:35 UTC (permalink / raw)
To: Dilip Kumar <dilipbalaut@gmail.com>; +Cc: wenhui qiu <qiuwenhuifx@gmail.com>; Shinya Kato <shinya11.kato@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
> > Thanks for starting this thread! This is a very useful
> > feature that users will find beneficial to easily narrow
> > down the reason the xmin horizon is being held back,
> > and take action.
>
> +1 for the idea. In BackendXidFindCutOffReason() you have directly
> reported using NOTICE I believe that is just to show the idea and you
> are planning to append this to the main message?
Yes, the NOTICE is just for demonstration. Some more work is needed
to integrate the output into the vacuum log.
> Apart from that we are looping the whole pgprocarray, however it is
> only done when we are vacuuming with verbose mode so might not
> be that bad.
The extra procarray loop will occur during VACUUM VERBOSE
or when a vacuum exceeds log_autovacuum_min_duration, 10
minutes by default.
I do think however, we should be more selective when to
do this work. We should only care about emitting this
information in autovacuum logging if the cutoffs->OldestXmid
did not advance between consecutive vacuums. This will
mean we we will need to track the last cutoff value in relation stats
(pg_stat_user_tables), but I think having this value in
stats will be useful on its own actually, as users can use
it to track tables with stalled cutoffs even if they don't
have sufficient autovacuum logging.
What do you think?
--
Sami Imseih
Amazon Web Services (AWS)
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Report oldest xmin source when autovacuum cannot remove tuples
@ 2025-12-18 00:30 Andres Freund <andres@anarazel.de>
parent: Shinya Kato <shinya11.kato@gmail.com>
2 siblings, 0 replies; 18+ messages in thread
From: Andres Freund @ 2025-12-18 00:30 UTC (permalink / raw)
To: Shinya Kato <shinya11.kato@gmail.com>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Hi,
On 2025-10-31 15:31:40 +0900, Shinya Kato wrote:
> The patch is attached. What do you think?
The added tests never seem to pass in CI:
https://cirrus-ci.com/github/postgresql-cfbot/postgresql/cf%2F6188
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Report oldest xmin source when autovacuum cannot remove tuples
@ 2025-12-26 05:34 Shinya Kato <shinya11.kato@gmail.com>
parent: Sami Imseih <samimseih@gmail.com>
2 siblings, 1 reply; 18+ messages in thread
From: Shinya Kato @ 2025-12-26 05:34 UTC (permalink / raw)
To: Sami Imseih <samimseih@gmail.com>; +Cc: wenhui qiu <qiuwenhuifx@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
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. However, I’m hesitant to proceed with this idea
for the following reasons:
- The original proposal extends ComputeXidHorizons(), which is always
calculated, so there is almost no additional overhead.
- Your proposal incurs additional cost. Furthermore, the time lag
between the execution of ComputeXidHorizons() and
BackendXidFindCutOffReason() could lead to inaccurate logging.
- I don't believe it is necessary to distinguish between active
transactions and "idle in transaction." These states can change
rapidly, and as long as we have the PID, we can check the current
status via pg_stat_activity.
- Your comment made me realize that it might be appropriate to expose
the oldest xmin in the pg_stat_{all,user,sys}_tables views, rather
than just logging it. In that case, we would need to calculate the
oldest xmin horizon every time. This might be a topic for a separate
thread, but we could consider adding columns such as:
- pg_stat_{all,user,sys}_tables.last_vacuum_oldest_xmin (xid)
- pg_stat_{all,user,sys}_tables.last_vacuum_oldest_xmin_source (text)
--
Best regards,
Shinya Kato
NTT OSS Center
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Report oldest xmin source when autovacuum cannot remove tuples
@ 2026-03-16 07:59 wenhui qiu <qiuwenhuifx@gmail.com>
parent: Shinya Kato <shinya11.kato@gmail.com>
0 siblings, 1 reply; 18+ 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] 18+ messages in thread
* Re: Report oldest xmin source when autovacuum cannot remove tuples
@ 2026-03-16 11:19 Japin Li <japinli@hotmail.com>
parent: wenhui qiu <qiuwenhuifx@gmail.com>
0 siblings, 1 reply; 18+ 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] 18+ messages in thread
* Re: Report oldest xmin source when autovacuum cannot remove tuples
@ 2026-05-22 13:05 Shinya Kato <shinya11.kato@gmail.com>
parent: Japin Li <japinli@hotmail.com>
0 siblings, 1 reply; 18+ 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] 18+ messages in thread
* Re: Report oldest xmin source when autovacuum cannot remove tuples
@ 2026-05-23 01:40 Japin Li <japinli@hotmail.com>
parent: Shinya Kato <shinya11.kato@gmail.com>
0 siblings, 1 reply; 18+ 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] 18+ messages in thread
* Re: Report oldest xmin source when autovacuum cannot remove tuples
@ 2026-05-23 05:22 Shinya Kato <shinya11.kato@gmail.com>
parent: Japin Li <japinli@hotmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: Shinya Kato @ 2026-05-23 05:22 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>
On Sat, May 23, 2026 at 10:40 AM Japin Li <japinli@hotmail.com> wrote:
> 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.
Thanks for the review! You're right, prepared transactions also have
pid 0. Fixed the comment to mention that.
Additionally, I forgot to update meson.build, which caused the tests
to fail. I have fixed that in the attached patch.
--
Best regards,
Shinya Kato
NTT OSS Center
Attachments:
[application/octet-stream] v4-0001-Report-oldest-xmin-blocker-when-VACUUM-cannot-rem.patch (27.0K, 2-v4-0001-Report-oldest-xmin-blocker-when-VACUUM-cannot-rem.patch)
download | inline diff:
From 81bb77f83faa5350d16a06bd56e748e0221e7724 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 v4] 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 | 40 +++
src/test/modules/test_misc/meson.build | 3 +-
.../test_misc/t/014_log_vacuum_blockers.pl | 314 ++++++++++++++++++
src/tools/pgindent/typedefs.list | 2 +
8 files changed, 718 insertions(+), 1 deletion(-)
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..cbde3b99723 100644
--- a/src/include/storage/procarray.h
+++ b/src/include/storage/procarray.h
@@ -14,10 +14,47 @@
#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 prepared xacts and
+ * 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 +135,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..507ac92fa3b 100644
--- a/src/test/modules/test_misc/meson.build
+++ b/src/test/modules/test_misc/meson.build
@@ -21,7 +21,8 @@ tests += {
't/010_index_concurrently_upsert.pl',
't/011_lock_stats.pl',
't/012_ddlutils.pl',
- 't/013_temp_obj_multisession.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..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] 18+ messages in thread
* Re: Report oldest xmin source when autovacuum cannot remove tuples
@ 2026-05-23 06:00 Shinya Kato <shinya11.kato@gmail.com>
parent: Shinya Kato <shinya11.kato@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: Shinya Kato @ 2026-05-23 06:00 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>
On Sat, May 23, 2026 at 2:22 PM Shinya Kato <shinya11.kato@gmail.com> wrote:
> Additionally, I forgot to update meson.build, which caused the tests
> to fail. I have fixed that in the attached patch.
Oops, I made a slight mistake. Fixed.
--
Best regards,
Shinya Kato
NTT OSS Center
Attachments:
[application/octet-stream] v5-0001-Report-oldest-xmin-blocker-when-VACUUM-cannot-rem.patch (26.9K, 2-v5-0001-Report-oldest-xmin-blocker-when-VACUUM-cannot-rem.patch)
download | inline diff:
From 142e6598d2eb939574ec2f60721ff44a77b97238 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 v5] 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 | 40 +++
src/test/modules/test_misc/meson.build | 1 +
.../test_misc/t/014_log_vacuum_blockers.pl | 314 ++++++++++++++++++
src/tools/pgindent/typedefs.list | 2 +
8 files changed, 717 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..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..cbde3b99723 100644
--- a/src/include/storage/procarray.h
+++ b/src/include/storage/procarray.h
@@ -14,10 +14,47 @@
#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 prepared xacts and
+ * 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 +135,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..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..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] 18+ messages in thread
* Re: Report oldest xmin source when autovacuum cannot remove tuples
@ 2026-05-23 10:17 wenhui qiu <qiuwenhuifx@gmail.com>
parent: Shinya Kato <shinya11.kato@gmail.com>
0 siblings, 0 replies; 18+ messages in thread
From: wenhui qiu @ 2026-05-23 10:17 UTC (permalink / raw)
To: Shinya Kato <shinya11.kato@gmail.com>; +Cc: Japin Li <japinli@hotmail.com>; Sami Imseih <samimseih@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Hi
Thank you updating the path,LGTM
Thanks
^ permalink raw reply [nested|flat] 18+ messages in thread
end of thread, other threads:[~2026-05-23 10:17 UTC | newest]
Thread overview: 18+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-10-31 06:31 Report oldest xmin source when autovacuum cannot remove tuples Shinya Kato <shinya11.kato@gmail.com>
2025-10-31 08:00 ` wenhui qiu <qiuwenhuifx@gmail.com>
2025-11-04 01:29 ` Shinya Kato <shinya11.kato@gmail.com>
2025-11-04 15:58 ` Fujii Masao <masao.fujii@gmail.com>
2025-11-11 10:40 ` wenhui qiu <qiuwenhuifx@gmail.com>
2025-11-15 00:25 ` Sami Imseih <samimseih@gmail.com>
2025-11-17 01:43 ` wenhui qiu <qiuwenhuifx@gmail.com>
2025-11-17 02:51 ` Dilip Kumar <dilipbalaut@gmail.com>
2025-11-17 18:35 ` Sami Imseih <samimseih@gmail.com>
2025-12-26 05:34 ` 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>
2026-05-23 05:22 ` Shinya Kato <shinya11.kato@gmail.com>
2026-05-23 06:00 ` Shinya Kato <shinya11.kato@gmail.com>
2026-05-23 10:17 ` wenhui qiu <qiuwenhuifx@gmail.com>
2025-12-18 00:30 ` Andres Freund <andres@anarazel.de>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox