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.gmail.com -- Best regards, Shinya Kato NTT OSS Center
From e827cccf204946ad088f20f0340160c635740148 Mon Sep 17 00:00:00 2001 From: Shinya Kato <[email protected]> Date: Thu, 5 Feb 2026 13:35:37 +0900 Subject: [PATCH v2] Report oldest xmin blocker when VACUUM cannot remove tuples When VACUUM encounters recently-dead tuples that cannot be removed, it is often unclear what is preventing the xid horizon from advancing. This patch adds diagnostic information to the VACUUM VERBOSE log output identifying the blocker that is holding back OldestXmin. The new GetXidHorizonBlocker() function in procarray.c searches for the source of the horizon: active transactions, idle-in-transaction sessions, prepared transactions, hot standby feedback via walsenders, and logical replication slots. When recently_dead_tuples > 0, VACUUM VERBOSE now reports the highest-priority blocker with identifying details (pid, prepared transaction GID, standby application name, or slot name). Because the horizon was computed earlier, the original blocker may have already committed by the time the check runs. The result is therefore best-effort: it may report a different blocker or none at all. Author: Shinya Kato <[email protected]> Reviewed-by: wenhui qiu <[email protected]> Reviewed-by: Fujii Masao <[email protected]> Reviewed-by: Sami Imseih <[email protected]> Reviewed-by: Dilip Kumar <[email protected]> Discussion: https://postgr.es/m/CAOzEurSgy-gDtwFmEbj5+R9PL0_G3qYB6nnzJtNStyuf87VSVg@mail.gmail.com --- src/backend/access/heap/vacuumlazy.c | 59 ++++ src/backend/access/transam/twophase.c | 39 +++ src/backend/storage/ipc/procarray.c | 262 +++++++++++++++ src/include/access/twophase.h | 1 + src/include/storage/procarray.h | 38 +++ src/test/modules/test_misc/meson.build | 1 + .../test_misc/t/011_log_vacuum_blockers.pl | 314 ++++++++++++++++++ src/tools/pgindent/typedefs.list | 2 + 8 files changed, 716 insertions(+) create mode 100644 src/test/modules/test_misc/t/011_log_vacuum_blockers.pl diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c index 4be267ff657..7b69727cd26 100644 --- a/src/backend/access/heap/vacuumlazy.c +++ b/src/backend/access/heap/vacuumlazy.c @@ -150,6 +150,7 @@ #include "storage/bufmgr.h" #include "storage/freespace.h" #include "storage/lmgr.h" +#include "storage/procarray.h" #include "storage/read_stream.h" #include "utils/lsyscache.h" #include "utils/pg_rusage.h" @@ -1058,6 +1059,64 @@ heap_vacuum_rel(Relation rel, const VacuumParams params, vacrel->tuples_deleted, (int64) vacrel->new_rel_tuples, vacrel->recently_dead_tuples); + if (vacrel->recently_dead_tuples > 0) + { + XidHorizonBlocker blocker; + + if (GetXidHorizonBlocker(vacrel->cutoffs.OldestXmin, &blocker)) + { + switch (blocker.type) + { + case XHB_ACTIVE_TRANSACTION: + appendStringInfo(&buf, + _("oldest xmin blocker: active transaction (pid = %d)\n"), + blocker.pid); + break; + case XHB_IDLE_IN_TRANSACTION: + appendStringInfo(&buf, + _("oldest xmin blocker: idle in transaction (pid = %d)\n"), + blocker.pid); + break; + case XHB_XMIN_ACTIVE_TRANSACTION: + appendStringInfo(&buf, + _("oldest xmin blocker: active transaction holding snapshot (pid = %d)\n"), + blocker.pid); + break; + case XHB_XMIN_IDLE_IN_TRANSACTION: + appendStringInfo(&buf, + _("oldest xmin blocker: idle in transaction holding snapshot (pid = %d)\n"), + blocker.pid); + break; + case XHB_PREPARED_TRANSACTION: + if (blocker.name[0] != '\0') + appendStringInfo(&buf, + _("oldest xmin blocker: prepared transaction (gid = %s)\n"), + blocker.name); + else + appendStringInfo(&buf, + _("oldest xmin blocker: prepared transaction\n")); + break; + case XHB_HOT_STANDBY_FEEDBACK: + if (blocker.name[0] != '\0') + appendStringInfo(&buf, + _("oldest xmin blocker: hot standby feedback (standby name = %s, pid = %d)\n"), + blocker.name, + blocker.pid); + else + appendStringInfo(&buf, + _("oldest xmin blocker: hot standby feedback (pid = %d)\n"), + blocker.pid); + break; + case XHB_REPLICATION_SLOT: + appendStringInfo(&buf, + _("oldest xmin blocker: logical replication slot (slot name = %s)\n"), + blocker.name); + break; + case XHB_NONE: + break; + } + } + } if (vacrel->missed_dead_tuples > 0) appendStringInfo(&buf, _("tuples missed: %" PRId64 " dead from %u pages not removed due to cleanup lock contention\n"), diff --git a/src/backend/access/transam/twophase.c b/src/backend/access/transam/twophase.c index eabc4d48208..6c6cad61336 100644 --- a/src/backend/access/transam/twophase.c +++ b/src/backend/access/transam/twophase.c @@ -2816,6 +2816,45 @@ LookupGXactBySubid(Oid subid) return found; } +/* + * GetPreparedTransactionGid + * Get the GID for the prepared transaction with the given XID. + * + * Returns true when a matching prepared transaction is found. gid will be + * set to an empty string when no match is found. + */ +bool +GetPreparedTransactionGid(TransactionId xid, char gid[GIDSIZE]) +{ + bool found = false; + + Assert(TransactionIdIsValid(xid)); + + gid[0] = '\0'; + + if (max_prepared_xacts == 0 || TwoPhaseState == NULL) + return false; + + LWLockAcquire(TwoPhaseStateLock, LW_SHARED); + for (int i = 0; i < TwoPhaseState->numPrepXacts; i++) + { + GlobalTransaction gxact = TwoPhaseState->prepXacts[i]; + + if (!gxact->valid) + continue; + + if (!TransactionIdEquals(XidFromFullTransactionId(gxact->fxid), xid)) + continue; + + strlcpy(gid, gxact->gid, GIDSIZE); + found = true; + break; + } + LWLockRelease(TwoPhaseStateLock); + + return found; +} + /* * TwoPhaseGetOldestXidInCommit * Return the oldest transaction ID from prepared transactions that are diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c index 301f54fb5a8..244e2d800fa 100644 --- a/src/backend/storage/ipc/procarray.c +++ b/src/backend/storage/ipc/procarray.c @@ -58,14 +58,17 @@ #include "pgstat.h" #include "postmaster/bgworker.h" #include "port/pg_lfind.h" +#include "replication/slot.h" #include "storage/proc.h" #include "storage/procarray.h" #include "utils/acl.h" +#include "utils/backend_status.h" #include "utils/builtins.h" #include "utils/injection_point.h" #include "utils/lsyscache.h" #include "utils/rel.h" #include "utils/snapmgr.h" +#include "utils/wait_event.h" #define UINT32_ACCESS_ONCE(var) ((uint32)(*((volatile uint32 *)&(var)))) @@ -2005,6 +2008,265 @@ GetReplicationHorizons(TransactionId *xmin, TransactionId *catalog_xmin) *catalog_xmin = horizons.slot_catalog_xmin; } +/* + * Fetch the standby's application_name as reported by the walsender backend. + */ +static void +GetStandbyAppname(int pid, char *name, Size len) +{ + int nbackends = pgstat_fetch_stat_numbackends(); + + name[0] = '\0'; + + for (int i = 1; i <= nbackends; i++) + { + LocalPgBackendStatus *local_beentry; + PgBackendStatus *beentry; + + local_beentry = pgstat_get_local_beentry_by_index(i); + if (local_beentry == NULL) + continue; + + beentry = &local_beentry->backendStatus; + if (beentry->st_procpid != pid) + continue; + + if (beentry->st_appname && beentry->st_appname[0] != '\0') + strlcpy(name, beentry->st_appname, len); + return; + } +} + +/* + * Return XidHorizonBlockerType for a backend whose xid matches the horizon. + */ +static inline XidHorizonBlockerType +XidHorizonBlockerTypeForBackend(const PGPROC *proc) +{ + if (proc->wait_event_info == WAIT_EVENT_CLIENT_READ) + return XHB_IDLE_IN_TRANSACTION; + return XHB_ACTIVE_TRANSACTION; +} + +/* + * Return XidHorizonBlockerType for a backend whose xmin matches the horizon. + */ +static inline XidHorizonBlockerType +XidHorizonBlockerTypeForXminBackend(const PGPROC *proc) +{ + if (proc->wait_event_info == WAIT_EVENT_CLIENT_READ) + return XHB_XMIN_IDLE_IN_TRANSACTION; + return XHB_XMIN_ACTIVE_TRANSACTION; +} + +/* + * Find the blockers that are holding back the given xid horizon. + * + * This function searches for what is preventing the given horizon from being + * advanced to allow removal of dead tuples. It checks: + * 1. Active transactions (running statements) + * 2. Idle-in-transaction sessions + * 3. Prepared transactions + * 4. Hot standby feedback + * 5. Logical replication slots + * + * Because the horizon was computed earlier, the original blocker may have + * already committed by the time this function runs. The result is therefore + * best-effort: it may return a different blocker, or no blocker at all. + * + * Returns a palloc'd array of blockers and stores the number of entries in + * *nblockers. The array may be empty if no blocker is found. + * + * Note: the same underlying cause (e.g. a physical replication slot with + * hot_standby_feedback) can appear as both an XHB_HOT_STANDBY_FEEDBACK entry + * (from the walsender PGPROC) and an XHB_REPLICATION_SLOT entry (from the + * slot's xmin). Callers that consume all entries should be prepared for such + * duplicates. + */ +static XidHorizonBlocker * +GetXidHorizonBlockers(TransactionId horizon, int *nblockers) +{ + ProcArrayStruct *arrayP = procArray; + TransactionId *other_xids = ProcGlobal->xids; + XidHorizonBlocker *result; + int count = 0; + int max_blockers; + + Assert(TransactionIdIsValid(horizon)); + Assert(nblockers != NULL); + + /* + * Allocate enough space for every PGPROC plus all replication slots. This + * is a generous upper bound (typically only 0-2 entries are returned), + * but keeps the logic simple for a diagnostic function that runs + * infrequently. + */ + max_blockers = arrayP->maxProcs + max_replication_slots; + result = palloc0_array(XidHorizonBlocker, max_blockers); + + LWLockAcquire(ProcArrayLock, LW_SHARED); + + for (int index = 0; index < arrayP->numProcs; index++) + { + int pgprocno = arrayP->pgprocnos[index]; + PGPROC *proc = &allProcs[pgprocno]; + int8 statusFlags = ProcGlobal->statusFlags[index]; + TransactionId proc_xid; + TransactionId proc_xmin; + XidHorizonBlockerType candidate_type = XHB_NONE; + int candidate_pid = 0; + TransactionId candidate_xid = InvalidTransactionId; + + /* + * Skip over backends either vacuuming (which is ok with rows being + * removed, as long as pg_subtrans is not truncated), doing logical + * decoding (which manages xmin separately, check below), or myself. + */ + if (statusFlags & (PROC_IN_VACUUM | PROC_IN_LOGICAL_DECODING) || + proc == MyProc) + continue; + + /* Fetch xid just once - see GetNewTransactionId */ + proc_xid = UINT32_ACCESS_ONCE(other_xids[index]); + proc_xmin = UINT32_ACCESS_ONCE(proc->xmin); + + /* Check if this proc's xid matches */ + if (TransactionIdEquals(proc_xid, horizon)) + { + if (proc->pid == 0) + { + candidate_type = XHB_PREPARED_TRANSACTION; + candidate_pid = 0; + candidate_xid = proc_xid; + } + else + { + candidate_type = XidHorizonBlockerTypeForBackend(proc); + candidate_pid = proc->pid; + candidate_xid = proc_xid; + } + } + /* Check if this proc's xmin matches */ + else if (TransactionIdEquals(proc_xmin, horizon)) + { + if (statusFlags & PROC_AFFECTS_ALL_HORIZONS) + { + candidate_type = XHB_HOT_STANDBY_FEEDBACK; + candidate_pid = proc->pid; + candidate_xid = proc_xmin; + } + else + { + candidate_type = XidHorizonBlockerTypeForXminBackend(proc); + candidate_pid = proc->pid; + candidate_xid = proc_xmin; + } + } + + /* + * If we found a candidate, record it. Candidates are collected in + * ProcArray order; callers can reorder if needed. + */ + if (candidate_type != XHB_NONE) + { + XidHorizonBlocker *dst; + + dst = &result[count++]; + dst->type = candidate_type; + dst->pid = candidate_pid; + dst->xid = candidate_xid; + } + } + + LWLockRelease(ProcArrayLock); + + /* + * Now that ProcArrayLock is released, fetch any extra details we want to + * attach to blockers, such as prepared transaction GIDs and standby + * application names. + */ + for (int i = 0; i < count; i++) + { + if (result[i].type == XHB_PREPARED_TRANSACTION) + GetPreparedTransactionGid(result[i].xid, result[i].name); + else if (result[i].type == XHB_HOT_STANDBY_FEEDBACK) + GetStandbyAppname(result[i].pid, result[i].name, + sizeof(result[i].name)); + } + + /* + * Also check replication slots. + */ + if (max_replication_slots > 0) + { + LWLockAcquire(ReplicationSlotControlLock, LW_SHARED); + + for (int i = 0; i < max_replication_slots; i++) + { + ReplicationSlot *s = &ReplicationSlotCtl->replication_slots[i]; + TransactionId slot_xmin; + TransactionId slot_catalog_xmin; + + if (!s->in_use) + continue; + + SpinLockAcquire(&s->mutex); + slot_xmin = s->data.xmin; + slot_catalog_xmin = s->data.catalog_xmin; + SpinLockRelease(&s->mutex); + + if (TransactionIdEquals(slot_xmin, horizon) || + TransactionIdEquals(slot_catalog_xmin, horizon)) + { + XidHorizonBlocker *dst; + + dst = &result[count++]; + dst->type = XHB_REPLICATION_SLOT; + dst->pid = 0; + dst->xid = TransactionIdIsValid(slot_xmin) ? + slot_xmin : + slot_catalog_xmin; + strlcpy(dst->name, NameStr(s->data.name), sizeof(dst->name)); + } + } + + LWLockRelease(ReplicationSlotControlLock); + } + + *nblockers = count; + return result; +} + +/* + * Get the highest-priority blocker holding back the xid horizon. + * + * Returns true and stores the blocker in *blocker if any are found. + */ +bool +GetXidHorizonBlocker(TransactionId horizon, XidHorizonBlocker *blocker) +{ + XidHorizonBlocker *blockers; + XidHorizonBlocker *best = NULL; + int nblockers; + + Assert(TransactionIdIsValid(horizon)); + Assert(blocker != NULL); + + blockers = GetXidHorizonBlockers(horizon, &nblockers); + for (int i = 0; i < nblockers; i++) + { + if (best == NULL || blockers[i].type < best->type) + best = &blockers[i]; + } + + if (best != NULL) + *blocker = *best; + + pfree(blockers); + + return (best != NULL); +} + /* * GetMaxSnapshotXidCount -- get max size for snapshot XID array * diff --git a/src/include/access/twophase.h b/src/include/access/twophase.h index e312514ba87..b076a155484 100644 --- a/src/include/access/twophase.h +++ b/src/include/access/twophase.h @@ -68,6 +68,7 @@ extern void TwoPhaseTransactionGid(Oid subid, TransactionId xid, char *gid_res, int szgid); extern bool LookupGXactBySubid(Oid subid); +extern bool GetPreparedTransactionGid(TransactionId xid, char gid[GIDSIZE]); extern TransactionId TwoPhaseGetOldestXidInCommit(void); #endif /* TWOPHASE_H */ diff --git a/src/include/storage/procarray.h b/src/include/storage/procarray.h index 3a8593f87ba..4c13f4df12b 100644 --- a/src/include/storage/procarray.h +++ b/src/include/storage/procarray.h @@ -14,11 +14,46 @@ #ifndef PROCARRAY_H #define PROCARRAY_H +#include "access/xact.h" #include "storage/lock.h" #include "storage/standby.h" #include "utils/relcache.h" #include "utils/snapshot.h" +/* + * Type of blocker that is holding back the xid horizon. + * Listed in priority order from highest to lowest. Blockers whose xid + * matches the horizon (the root cause) are listed before blockers whose + * xmin matches (held back by the root cause). Within each group, active + * transactions are listed first because they are the most actionable for + * the DBA (the running query can be identified and cancelled). + */ +typedef enum XidHorizonBlockerType +{ + XHB_NONE = 0, + /* xid-match types (horizon == proc's xid) */ + XHB_ACTIVE_TRANSACTION, /* backend running a statement */ + XHB_IDLE_IN_TRANSACTION, /* backend idle in transaction */ + XHB_PREPARED_TRANSACTION, /* prepared (two-phase) transaction */ + /* xmin-match types (horizon == proc's xmin or slot's xmin) */ + XHB_XMIN_ACTIVE_TRANSACTION, /* backend running a statement */ + XHB_XMIN_IDLE_IN_TRANSACTION, /* backend idle in transaction */ + XHB_HOT_STANDBY_FEEDBACK, /* walsender with hot_standby_feedback */ + XHB_REPLICATION_SLOT, /* logical replication slot */ +} XidHorizonBlockerType; + +/* + * Information about a blocker that is holding back the xid horizon. + */ +typedef struct XidHorizonBlocker +{ + XidHorizonBlockerType type; + TransactionId xid; /* the blocking xid/xmin */ + int pid; /* backend pid (0 for slots) */ + /* large enough for prepared-txn GID or replication slot name */ + char name[Max(GIDSIZE, NAMEDATALEN)]; +} XidHorizonBlocker; + extern Size ProcArrayShmemSize(void); extern void ProcArrayShmemInit(void); @@ -98,4 +133,7 @@ extern void ProcArraySetReplicationSlotXmin(TransactionId xmin, extern void ProcArrayGetReplicationSlotXmin(TransactionId *xmin, TransactionId *catalog_xmin); +extern bool GetXidHorizonBlocker(TransactionId horizon, + XidHorizonBlocker *blocker); + #endif /* PROCARRAY_H */ diff --git a/src/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build index 6e8db1621a7..d3b92e20737 100644 --- a/src/test/modules/test_misc/meson.build +++ b/src/test/modules/test_misc/meson.build @@ -19,6 +19,7 @@ tests += { 't/008_replslot_single_user.pl', 't/009_log_temp_files.pl', 't/010_index_concurrently_upsert.pl', + 't/011_log_vacuum_blockers.pl', ], # The injection points are cluster-wide, so disable installcheck 'runningcheck': false, diff --git a/src/test/modules/test_misc/t/011_log_vacuum_blockers.pl b/src/test/modules/test_misc/t/011_log_vacuum_blockers.pl new file mode 100644 index 00000000000..2e26f0f3beb --- /dev/null +++ b/src/test/modules/test_misc/t/011_log_vacuum_blockers.pl @@ -0,0 +1,314 @@ +# Copyright (c) 2026, PostgreSQL Global Development Group +# +# Validate that VACUUM logs explain why dead tuples could not be removed. + +use strict; +use warnings FATAL => 'all'; + +use PostgreSQL::Test::Cluster; +use PostgreSQL::Test::Utils; +use Test::More; + +# Set up a cluster +my $node = PostgreSQL::Test::Cluster->new('main'); +$node->init(allows_streaming => 'logical'); +$node->append_conf('postgresql.conf', q[ +max_prepared_transactions = 5 +]); +$node->start; + +# Create the hot standby test table and prepare backup/standby early, before +# any background psql sessions are started. On Windows, background psql +# sessions that are terminated during earlier tests can leave file handles +# lingering, which causes pg_ctl start for the standby to fail. By taking +# the backup and initializing the standby here, the standby start later only +# needs to launch pg_ctl after all those sessions have been fully cleaned up. +$node->backup('oldestxmin_hotstandby_bkp'); +my $standby = PostgreSQL::Test::Cluster->new('oldestxmin_standby'); +$standby->init_from_backup($node, 'oldestxmin_hotstandby_bkp', + has_streaming => 1); +$standby->append_conf('postgresql.conf', q[ +hot_standby_feedback = on +wal_receiver_status_interval = 100ms +]); + + +# +# Active statement +# +my $active_table = 'blocker_active'; +$node->safe_psql('postgres', qq[ +CREATE TABLE $active_table(id int); +INSERT INTO $active_table VALUES (0); +]); + +my $blocker = $node->background_psql('postgres'); +my $blocker_pid = $blocker->query_safe('SELECT pg_backend_pid();'); +chomp($blocker_pid); + +# Start a long-running query in the background that accesses a table +# This ensures xmin is set (pg_sleep alone doesn't require a snapshot) +$blocker->query_until(qr//, qq[ +BEGIN; +SELECT * FROM $active_table, pg_sleep(60); +]); + +# Wait for the blocker to have xmin set +$node->poll_query_until('postgres', qq[ +SELECT backend_xmin IS NOT NULL +FROM pg_stat_activity +WHERE pid = $blocker_pid; +]); + +$node->safe_psql('postgres', "DELETE FROM $active_table;"); + +my $stderr = ''; +$node->psql('postgres', "VACUUM (VERBOSE) $active_table;", stderr => \$stderr); +like( + $stderr, + qr/oldest xmin blocker: active transaction holding snapshot \(pid = $blocker_pid\)/, + 'VACUUM VERBOSE reported active transaction holding snapshot as oldest xmin blocker'); + +# Cleanup +$node->safe_psql('postgres', qq[ +SELECT pg_terminate_backend($blocker_pid); +DROP TABLE $active_table; +]); + + +# +# Idle in transaction +# +my $idle_table = 'blocker_idle'; +$node->safe_psql('postgres', qq[ +CREATE TABLE $idle_table(id int); +INSERT INTO $idle_table VALUES (0); +]); + +my $idle_blocker = $node->background_psql('postgres'); +my $idle_blocker_pid = $idle_blocker->query_safe('SELECT pg_backend_pid();'); +chomp($idle_blocker_pid); + +# Set isolation level to REPEATABLE READ to ensure xmin is set +$idle_blocker->query_safe(qq[ +BEGIN; +SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; +SELECT * FROM $idle_table; +]); + +$node->safe_psql('postgres', "DELETE FROM $idle_table;"); + +$stderr = ''; +$node->psql('postgres', "VACUUM (VERBOSE) $idle_table;", stderr => \$stderr); +like( + $stderr, + qr/oldest xmin blocker: idle in transaction holding snapshot \(pid = $idle_blocker_pid\)/, + 'VACUUM VERBOSE reported idle in transaction holding snapshot as oldest xmin blocker'); + +# Cleanup +$idle_blocker->quit; +$node->safe_psql('postgres', "DROP TABLE $idle_table;"); + + +# +# Serializable transaction (idle in transaction) +# +my $serializable_table = 'blocker_serializable'; +$node->safe_psql('postgres', qq[ +CREATE TABLE $serializable_table(id int); +INSERT INTO $serializable_table VALUES (0); +]); + +my $ser_blocker = $node->background_psql('postgres'); +my $ser_blocker_pid = $ser_blocker->query_safe('SELECT pg_backend_pid();'); +chomp($ser_blocker_pid); + +$ser_blocker->query_safe(qq[ +BEGIN; +SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; +SELECT * FROM $serializable_table; +]); + +$node->safe_psql('postgres', "DELETE FROM $serializable_table;"); + +$stderr = ''; +$node->psql('postgres', "VACUUM (VERBOSE) $serializable_table;", stderr => \$stderr); +like( + $stderr, + qr/oldest xmin blocker: idle in transaction holding snapshot \(pid = $ser_blocker_pid\)/, + 'VACUUM VERBOSE reported serializable transaction as oldest xmin blocker'); + +# Cleanup +$ser_blocker->quit; +$node->safe_psql('postgres', "DROP TABLE $serializable_table;"); + + +# +# Prefer xid owner over xmin match +# +my $prefer_table = 'blocker_prefer_xid_owner'; +$node->safe_psql('postgres', qq[ +CREATE TABLE $prefer_table(id int); +INSERT INTO $prefer_table VALUES (0); +]); + +my $xid_owner = $node->background_psql('postgres'); +my $xid_owner_pid = $xid_owner->query_safe('SELECT pg_backend_pid();'); +chomp($xid_owner_pid); + +$xid_owner->query_safe(qq[ +BEGIN; +SELECT pg_current_xact_id(); +]); + +$node->poll_query_until('postgres', qq[ +SELECT backend_xid IS NOT NULL +FROM pg_stat_activity +WHERE pid = $xid_owner_pid; +]); + +my $owner_xid = $node->safe_psql('postgres', qq[ +SELECT backend_xid +FROM pg_stat_activity +WHERE pid = $xid_owner_pid; +]); +chomp($owner_xid); + +my $xmin_holder = $node->background_psql('postgres'); +my $xmin_holder_pid = $xmin_holder->query_safe('SELECT pg_backend_pid();'); +chomp($xmin_holder_pid); + +# Start a long-running query that will take a snapshot after xid_owner begins +$xmin_holder->query_until(qr//, qq[ +BEGIN; +SELECT * FROM $prefer_table, pg_sleep(60); +]); + +# Ensure xmin_holder's xmin is held back by xid_owner +$node->poll_query_until('postgres', qq[ +SELECT backend_xmin = '$owner_xid'::xid +FROM pg_stat_activity +WHERE pid = $xmin_holder_pid; +]); + +$node->safe_psql('postgres', "DELETE FROM $prefer_table;"); + +$stderr = ''; +$node->psql('postgres', "VACUUM (VERBOSE) $prefer_table;", stderr => \$stderr); +like( + $stderr, + qr/oldest xmin blocker: idle in transaction \(pid = $xid_owner_pid\)/, + 'VACUUM VERBOSE preferred xid owner over xmin match'); + +# Cleanup +$node->safe_psql('postgres', qq[ +SELECT pg_terminate_backend($xmin_holder_pid); +SELECT pg_terminate_backend($xid_owner_pid); +DROP TABLE $prefer_table; +]); + + +# +# Prepared transaction +# +my $prepared_table = 'blocker_prepared'; +$node->safe_psql('postgres', qq[ +CREATE TABLE $prepared_table(id int); +INSERT INTO $prepared_table VALUES (0); +BEGIN; +PREPARE TRANSACTION 'gx_vacuum_xmin'; +]); + +$node->safe_psql('postgres', "DELETE FROM $prepared_table;"); + +$stderr = ''; +$node->psql('postgres', "VACUUM (VERBOSE) $prepared_table;", stderr => \$stderr); +like( + $stderr, + qr/oldest xmin blocker: prepared transaction \(gid = gx_vacuum_xmin\)/, + 'VACUUM VERBOSE reported prepared transaction as oldest xmin blocker'); + +# Cleanup +$node->safe_psql('postgres', qq[ +ROLLBACK PREPARED 'gx_vacuum_xmin'; +DROP TABLE $prepared_table; +]); + + +# +# Logical replication slot +# +my $slot_table = 'blocker_slot'; +$node->safe_psql('postgres', qq[ +CREATE TABLE $slot_table(id int); +SELECT pg_create_logical_replication_slot('logical_slot', 'test_decoding'); +DROP TABLE $slot_table; +]); + +$stderr = ''; +$node->psql('postgres', 'VACUUM (VERBOSE) pg_class;', stderr => \$stderr); +like( + $stderr, + qr/oldest xmin blocker: logical replication slot \(slot name = logical_slot\)/, + 'VACUUM VERBOSE reported logical replication slot as oldest xmin source'); + +# Cleanup +$node->safe_psql('postgres', qq[ +SELECT pg_drop_replication_slot('logical_slot'); +]); + + +# +# Hot standby feedback +# +# The standby was already initialized from a backup taken above. Start it +# now, after all background psql sessions from earlier tests have been fully +# cleaned up. +my $hs_table = 'blocker_hotstandby'; +$node->safe_psql('postgres', qq[ +CREATE TABLE $hs_table(id int); +INSERT INTO $hs_table VALUES (0); +]); + +$standby->start; +$node->wait_for_catchup($standby, 'replay', $node->lsn('flush')); + +my $standby_reader = $standby->background_psql('postgres'); +my $standby_reader_pid = $standby_reader->query_safe('SELECT pg_backend_pid();'); +chomp($standby_reader_pid); + +$standby_reader->query_until(qr//, qq[ +BEGIN; +SELECT * FROM $hs_table, pg_sleep(60); +]); + +# Wait for hot standby feedback to be sent +$node->poll_query_until('postgres', q[ +SELECT backend_xmin IS NOT NULL +FROM pg_stat_replication +WHERE application_name = 'oldestxmin_standby'; +]); + +my $hs_blocker_pid = $node->safe_psql('postgres', q[ +SELECT pid FROM pg_stat_replication +WHERE application_name = 'oldestxmin_standby'; +]); +chomp($hs_blocker_pid); + +$node->safe_psql('postgres', "DELETE FROM $hs_table;"); + +$stderr = ''; +$node->psql('postgres', "VACUUM (VERBOSE) $hs_table;", stderr => \$stderr); +like( + $stderr, + qr/oldest xmin blocker: hot standby feedback \(standby name = oldestxmin_standby, pid = $hs_blocker_pid\)/, + 'VACUUM VERBOSE reported hot standby feedback as oldest xmin blocker'); + +# Cleanup +$standby->safe_psql('postgres', "SELECT pg_terminate_backend($standby_reader_pid);"); +$node->safe_psql('postgres', "DROP TABLE $hs_table;"); + + +$standby->stop; +$node->stop; +done_testing(); diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 9f5ee8fd482..976f505c758 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -3431,6 +3431,8 @@ XactLockTableWaitInfo XidBoundsViolation XidCacheStatus XidCommitStatus +XidHorizonBlocker +XidHorizonBlockerType XidStatus XmlExpr XmlExprOp -- 2.47.3
