Hi, On Tue, Jul 7, 2026 at 9:17 AM Shinya Kato <[email protected]> wrote: > > On Tue, Jun 30, 2026 at 12:23 AM Bharath Rupireddy > <[email protected]> wrote: > > My main concern is the additional proc array scan and the lock it > > takes. ProcArrayLock is one of the most contended locks in production > > systems with hundreds or thousands of connections. With many workers > > vacuuming concurrently across hundreds of tables, I expect this extra > > lock acquisition to show up in the contention path. > > ComputeXidHorizons() already takes ProcArrayLock and has all the > > information about who is blocking the vacuum, so I think we can > > capture the blocker right there and get the reason almost for free, > > without a second scan. > > Thank you for taking a look. Earlier versions of this patch took > exactly that approach, and Sami showed it can report the wrong PID > [1]. Identifying the root cause needs the complete set of procs and > slots matching the final horizon, which ComputeXidHorizons() does not > know until its loop has ended. > > On the overhead, the extra scan runs only when the log line is > actually emitted (VACUUM VERBOSE, or log_autovacuum_min_duration > exceeded) and only when dead tuples were in fact left unremoved. One > additional LW_SHARED acquisition per logged vacuum, on top of the many > ProcArrayLock acquisitions a vacuum already performs, should not be > visible in the contention path. > > > The other concern is timing. The patch reports the blocker after the > > vacuum has finished. With hundreds of GB of tables and indexes, where > > vacuum can run for hours, reporting up front is far more useful than > > learning at the end that vacuum couldn't remove the tuples. If we > > capture the blocker when ComputeXidHorizons() computes the horizon, we > > can tell the user early, while there's still a chance to act on it. > > Whether the blocker is worth reporting is only known after the heap > scan, since it depends on whether any dead tuples were actually left > unremovable. For seeing the blocker while a long vacuum is still > running, I think a live view (for example pg_stat_progress_vacuum or a > SQL-callable function) fits better than a log line. The 0001 patch > keeps the infrastructure independent of vacuum so that such a view can > be built on it as a follow-up, which Sami also suggested in [1]. > > > I wrote a patch on the same idea about a month ago, then got busy and > > only noticed this thread now. If it helps, I'm happy to post it. > > Please feel free to post it.
Sorry for the delay, I spent time on this. Thank you Shinya-san for the off-list chat. When VACUUM runs for minutes to hours on large tables, this information is genuinely useful. Today, it is difficult to tell what is keeping the oldest xmin horizon from advancing until it is already held back to an unsafe degree, and even then VACUUM only warns that the cutoff for removing and freezing tuples is far in the past and lists the general possibilities (open transactions, prepared transactions, replication slots), without naming what is actually responsible. As a result, a long-running VACUUM can pass without the user getting a chance to act in time, and afterwards there is no record of why it could not prune XIDs or clean up bloat, which makes it hard to answer customer questions as basic as "what exactly held the database's VACUUM back?". The way I think about approaching this problem is: 1/ we can only ever report a possible cause, whether we capture it at the start or reconstruct it at the end with a separate scan, since a transaction or slot holding the xmin back may go away right after; given that it is best-effort either way, I would rather report it as soon as VACUUM computes its cutoffs, where it is most useful, so the user can act while the VACUUM is still running and fix things for the next relation in the same cycle or the next cycle, rather than learn at the end that the run achieved nothing, 2/ the reporting stays cheap and adds no contention on hot locks such as ProcArrayLock, and 3/ it carries the minimum actionable detail, namely the backend PID for a running transaction, the GID for a prepared transaction, the slot name for a replication slot, and the walsender PID for standby feedback. With this context, I attached two patches. 0001 reports the category of the blocker (running transaction, prepared transaction, standby feedback, replication slot, logical replication slot). It is computed inside ComputeXidHorizons(), which already holds ProcArrayLock, so there is no extra lock and no additional scan, and it is exposed in pg_stat_progress_vacuum, in VACUUM (VERBOSE) and in the autovacuum log when the run exceeds log_autovacuum_min_duration, and as extra detail on the "far in the past" warning. 0002 adds the specific identity (backend PID, prepared-xact GID, or slot name) to that warning only. Finding the identity needs TwoPhaseStateLock or ReplicationSlotControlLock, so it is done only for the warning and not for routine progress reporting. We already seem to have agreement on capturing the blocker during ComputeXidHorizons() [1] [2]. [1] https://www.postgresql.org/message-id/3bnBUxwx2npXqvHL0trI11LOOvzQ7LI0GzWqbaj5SJnk7DTb1uzStGveKwj0JJmBW4ebzGIF3az7of4I4rQeaO_PRqDnnClCduPyjM6gPgM%3D%40scottray.io [2] https://www.postgresql.org/message-id/CAOYmi%2BmKfzcj%3DGbtDhyu49kGwoN5811FqPzFfgvS7R6mzVs4aQ%40mail.gmail.com Thoughts? -- Bharath Rupireddy Amazon Web Services: https://aws.amazon.com
From 03ec92f07f524406b8d690e139428c3ed9bd1218 Mon Sep 17 00:00:00 2001 From: Bharath Rupireddy <[email protected]> Date: Tue, 14 Jul 2026 16:44:35 +0000 Subject: [PATCH v1 1/2] Report what holds back the oldest xmin horizon during VACUUM Today it is difficult to tell what is keeping the oldest xmin from advancing until it is already held back to an unsafe degree, and even then VACUUM only warns that the cutoff for removing and freezing tuples is far in the past and lists the general possibilities, without naming what is actually responsible. So a long-running VACUUM can pass without a chance to act in time, and afterwards there is no record of why it could not prune XIDs or clean up bloat. Report the category of the blocker: a running transaction, a prepared transaction, standby feedback, a replication slot, or a logical replication slot. It is computed inside ComputeXidHorizons(), which already walks the proc array under ProcArrayLock and applies the slot xmins, so there is no extra lock and no additional scan. It is a best-effort hint, taken when the XID horizons are computed, so the reported transaction or slot may have changed or gone away by the time it is read, and when several share the oldest xmin only one is named. The blocker is exposed as a new oldest_xmin_blocker column in pg_stat_progress_vacuum, in VACUUM (VERBOSE) and in the autovacuum log, and as extra detail on the existing "cutoff for removing and freezing tuples is far in the past" warning. This commit needs a catalog version bump. --- doc/src/sgml/monitoring.sgml | 63 +++++++ src/backend/access/heap/vacuumlazy.c | 11 ++ src/backend/catalog/system_views.sql | 8 +- src/backend/commands/vacuum.c | 46 ++++- src/backend/storage/ipc/procarray.c | 116 ++++++++++++- src/include/commands/progress.h | 1 + src/include/commands/vacuum.h | 8 + src/include/storage/procarray.h | 31 ++++ src/test/recovery/meson.build | 1 + .../t/055_vacuum_oldest_xmin_blocker.pl | 163 ++++++++++++++++++ src/test/regress/expected/rules.out | 10 +- src/tools/pgindent/typedefs.list | 2 + 12 files changed, 446 insertions(+), 14 deletions(-) create mode 100644 src/test/recovery/t/055_vacuum_oldest_xmin_blocker.pl diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index d1a20d001e9..59425c83351 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -7737,6 +7737,69 @@ FROM pg_stat_get_backend_idset() AS backendid; </itemizedlist> </para></entry> </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>oldest_xmin_blocker</structfield> <type>text</type> + </para> + <para> + What was holding the oldest xmin horizon back when this vacuum computed + its cutoffs, and thus what may be preventing dead tuples from being + removed. The value is <literal>NULL</literal> when nothing older than + this vacuum's own xmin was found. Possible values are: + <itemizedlist> + <listitem> + <para> + <literal>running transaction</literal>: an in-progress transaction + (possibly idle or long-running) is holding back the horizon (see + <link linkend="monitoring-pg-stat-activity-view"><structname>pg_stat_activity</structname></link>). + </para> + </listitem> + <listitem> + <para> + <literal>prepared transaction</literal>: a prepared (two-phase) + transaction that has not yet been committed or rolled back (see + <link linkend="view-pg-prepared-xacts"><structname>pg_prepared_xacts</structname></link>). + </para> + </listitem> + <listitem> + <para> + <literal>standby feedback</literal>: the oldest xmin a standby + reported through <xref linkend="guc-hot-standby-feedback"/>, reserved + via the walsender's process entry rather than a slot. (When the + standby uses a replication slot, the xmin is reserved on the slot and + is reported as <literal>replication slot</literal> instead.) + </para> + </listitem> + <listitem> + <para> + <literal>replication slot</literal>: a replication slot's + <structfield>xmin</structfield> (see + <link linkend="view-pg-replication-slots"><structname>pg_replication_slots</structname></link>). + </para> + </listitem> + <listitem> + <para> + <literal>logical replication slot</literal>: a slot's + <structfield>catalog_xmin</structfield>, held for logical decoding + (see + <link linkend="view-pg-replication-slots"><structname>pg_replication_slots</structname></link>). + </para> + </listitem> + </itemizedlist> + <note> + <para> + This value is what vacuum saw when it computed its xmin cutoffs, so + the responsible transaction or slot may have changed or gone away by + the time it is read. Only the oldest one is reported; once it is + gone, the next oldest one (if any) is reported instead. For example, + if a long-running transaction and a replication slot are both holding + xmins back, the one with the older xmin is reported, and the other + only after the first has ended. + </para> + </note> + </para></entry> + </row> </tbody> </tgroup> </table> diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c index 39395aed0d5..f171a7b1af4 100644 --- a/src/backend/access/heap/vacuumlazy.c +++ b/src/backend/access/heap/vacuumlazy.c @@ -839,6 +839,13 @@ heap_vacuum_rel(Relation rel, const VacuumParams *params, ? PROGRESS_VACUUM_MODE_AGGRESSIVE : PROGRESS_VACUUM_MODE_NORMAL); + /* + * Report what held OldestXmin back, so watchers of + * pg_stat_progress_vacuum can see why dead tuples aren't being removed. + */ + pgstat_progress_update_param(PROGRESS_VACUUM_OLDEST_XMIN_BLOCKER, + vacrel->cutoffs.oldest_xmin_blocker); + if (verbose) { if (vacrel->aggressive) @@ -1084,6 +1091,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->cutoffs.oldest_xmin_blocker != OLDEST_XMIN_BLOCKER_NONE) + appendStringInfo(&buf, + _("oldest xmin held back by: %s\n"), + vacuum_oldest_xmin_blocker_name(vacrel->cutoffs.oldest_xmin_blocker)); if (frozenxid_updated) { diff = (int32) (vacrel->NewRelfrozenXid - diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 090281a03dd..d30d4cb2ea7 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -1353,7 +1353,13 @@ CREATE VIEW pg_stat_progress_vacuum AS CASE S.param13 WHEN 1 THEN 'manual' WHEN 2 THEN 'autovacuum' WHEN 3 THEN 'autovacuum_wraparound' - ELSE NULL END AS started_by + ELSE NULL END AS started_by, + CASE S.param14 WHEN 1 THEN 'running transaction' + WHEN 2 THEN 'prepared transaction' + WHEN 3 THEN 'standby feedback' + WHEN 4 THEN 'replication slot' + WHEN 5 THEN 'logical replication slot' + ELSE NULL END AS oldest_xmin_blocker FROM pg_stat_get_progress_info('VACUUM') AS S LEFT JOIN pg_database D ON S.datid = D.oid; diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index 38539a6fd3d..a5a95df0ae3 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -132,6 +132,31 @@ static double compute_parallel_delay(void); static VacOptValue get_vacoptval_from_boolean(DefElem *def); static bool vac_tid_reaped(ItemPointer itemptr, void *state); +/* + * Names for the OldestXminBlocker categories, indexed by enum value, used in + * log lines and pg_stat_progress_vacuum. Keep in sync with the enum in + * procarray.h. + */ +static const char *const OldestXminBlockerNames[] = { + [OLDEST_XMIN_BLOCKER_NONE] = "none", + [OLDEST_XMIN_BLOCKER_RUNNING_XACT] = "running transaction", + [OLDEST_XMIN_BLOCKER_PREPARED_XACT] = "prepared transaction", + [OLDEST_XMIN_BLOCKER_STANDBY_FEEDBACK] = "standby feedback", + [OLDEST_XMIN_BLOCKER_REPLICATION_SLOT] = "replication slot", + [OLDEST_XMIN_BLOCKER_LOGICAL_SLOT] = "logical replication slot", +}; + +/* + * Returns the name for an oldest-xmin blocker category. + */ +const char * +vacuum_oldest_xmin_blocker_name(OldestXminBlocker blocker) +{ + if (blocker < 0 || blocker >= lengthof(OldestXminBlockerNames)) + return "unknown"; + return OldestXminBlockerNames[blocker]; +} + /* * GUC check function to ensure GUC value specified is within the allowable * range. @@ -1139,7 +1164,8 @@ 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 = + GetOldestNonRemovableTransactionIdExt(rel, &cutoffs->oldest_xmin_blocker); Assert(TransactionIdIsNormal(cutoffs->OldestXmin)); @@ -1169,10 +1195,20 @@ vacuum_get_cutoffs(Relation rel, const VacuumParams *params, if (safeOldestMxact < FirstMultiXactId) safeOldestMxact = FirstMultiXactId; if (TransactionIdPrecedes(cutoffs->OldestXmin, safeOldestXmin)) - ereport(WARNING, - (errmsg("cutoff for removing and freezing tuples is far in the past"), - errhint("Close open transactions soon to avoid wraparound problems.\n" - "You might also need to commit or roll back old prepared transactions, or drop stale replication slots."))); + { + if (cutoffs->oldest_xmin_blocker != OLDEST_XMIN_BLOCKER_NONE) + ereport(WARNING, + (errmsg("cutoff for removing and freezing tuples is far in the past"), + errdetail("The oldest xmin horizon is currently held back by: %s.", + vacuum_oldest_xmin_blocker_name(cutoffs->oldest_xmin_blocker)), + errhint("Close open transactions soon to avoid wraparound problems.\n" + "You might also need to commit or roll back old prepared transactions, or drop stale replication slots."))); + else + ereport(WARNING, + (errmsg("cutoff for removing and freezing tuples is far in the past"), + errhint("Close open transactions soon to avoid wraparound problems.\n" + "You might also need to commit or roll back old prepared transactions, or drop stale replication slots."))); + } if (MultiXactIdPrecedes(cutoffs->OldestMxact, safeOldestMxact)) ereport(WARNING, (errmsg("cutoff for freezing multixacts is far in the past"), diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c index 60336b31803..2997bbcbb3a 100644 --- a/src/backend/storage/ipc/procarray.c +++ b/src/backend/storage/ipc/procarray.c @@ -1671,7 +1671,7 @@ TransactionIdIsInProgress(TransactionId xid) * code doesn't expect (breaking HOT). */ static void -ComputeXidHorizons(ComputeXidHorizonsResult *h) +ComputeXidHorizons(ComputeXidHorizonsResult *h, XminHorizonBlockers *b) { ProcArrayStruct *arrayP = procArray; TransactionId kaxmin; @@ -1681,6 +1681,14 @@ ComputeXidHorizons(ComputeXidHorizonsResult *h) /* inferred after ProcArrayLock is released */ h->catalog_oldest_nonremovable = InvalidTransactionId; + /* no blocker identified yet, for callers that asked for one */ + if (b) + { + b->shared = OLDEST_XMIN_BLOCKER_NONE; + b->data = OLDEST_XMIN_BLOCKER_NONE; + b->catalog = OLDEST_XMIN_BLOCKER_NONE; + } + LWLockAcquire(ProcArrayLock, LW_SHARED); h->latest_completed = TransamVariables->latestCompletedXid; @@ -1735,6 +1743,7 @@ ComputeXidHorizons(ComputeXidHorizonsResult *h) int8 statusFlags = ProcGlobal->statusFlags[index]; TransactionId xid; TransactionId xmin; + OldestXminBlocker this_blocker = OLDEST_XMIN_BLOCKER_NONE; /* Fetch xid just once - see GetNewTransactionId */ xid = UINT32_ACCESS_ONCE(other_xids[index]); @@ -1770,6 +1779,27 @@ ComputeXidHorizons(ComputeXidHorizonsResult *h) if (statusFlags & (PROC_IN_VACUUM | PROC_IN_LOGICAL_DECODING)) continue; + /* + * Classify this proc for whichever horizon it limits below. A + * prepared xact has no backend, so its dummy PGPROC has pid 0. + * PROC_AFFECTS_ALL_HORIZONS marks a walsender that reserves an xmin + * via its PGPROC entry rather than a slot, i.e. hot_standby_feedback + * with no slot (see ProcessStandbyHSFeedbackMessage). Anything else + * is a running transaction. + */ + if (b) + { + if (proc->pid == 0) + this_blocker = OLDEST_XMIN_BLOCKER_PREPARED_XACT; + else if (statusFlags & PROC_AFFECTS_ALL_HORIZONS) + this_blocker = OLDEST_XMIN_BLOCKER_STANDBY_FEEDBACK; + else + this_blocker = OLDEST_XMIN_BLOCKER_RUNNING_XACT; + + if (TransactionIdPrecedes(xmin, h->shared_oldest_nonremovable)) + b->shared = this_blocker; + } + /* shared tables need to take backends in all databases into account */ h->shared_oldest_nonremovable = TransactionIdOlder(h->shared_oldest_nonremovable, xmin); @@ -1798,6 +1828,8 @@ ComputeXidHorizons(ComputeXidHorizonsResult *h) (statusFlags & PROC_AFFECTS_ALL_HORIZONS) || in_recovery) { + if (b && TransactionIdPrecedes(xmin, h->data_oldest_nonremovable)) + b->data = this_blocker; h->data_oldest_nonremovable = TransactionIdOlder(h->data_oldest_nonremovable, xmin); } @@ -1820,8 +1852,12 @@ ComputeXidHorizons(ComputeXidHorizonsResult *h) { h->oldest_considered_running = TransactionIdOlder(h->oldest_considered_running, kaxmin); + if (b && TransactionIdPrecedes(kaxmin, h->shared_oldest_nonremovable)) + b->shared = OLDEST_XMIN_BLOCKER_RUNNING_XACT; h->shared_oldest_nonremovable = TransactionIdOlder(h->shared_oldest_nonremovable, kaxmin); + if (b && TransactionIdPrecedes(kaxmin, h->data_oldest_nonremovable)) + b->data = OLDEST_XMIN_BLOCKER_RUNNING_XACT; h->data_oldest_nonremovable = TransactionIdOlder(h->data_oldest_nonremovable, kaxmin); /* temp relations cannot be accessed in recovery */ @@ -1833,8 +1869,16 @@ ComputeXidHorizons(ComputeXidHorizonsResult *h) h->data_oldest_nonremovable)); /* - * Check whether there are replication slots requiring an older xmin. + * Check whether there are replication slots requiring an older xmin. A + * slot's xmin backs up both the shared and data horizons. */ + if (b && TransactionIdIsValid(h->slot_xmin)) + { + if (TransactionIdPrecedes(h->slot_xmin, h->shared_oldest_nonremovable)) + b->shared = OLDEST_XMIN_BLOCKER_REPLICATION_SLOT; + if (TransactionIdPrecedes(h->slot_xmin, h->data_oldest_nonremovable)) + b->data = OLDEST_XMIN_BLOCKER_REPLICATION_SLOT; + } h->shared_oldest_nonremovable = TransactionIdOlder(h->shared_oldest_nonremovable, h->slot_xmin); h->data_oldest_nonremovable = @@ -1848,10 +1892,27 @@ ComputeXidHorizons(ComputeXidHorizonsResult *h) * that also can contain catalogs. */ h->shared_oldest_nonremovable_raw = h->shared_oldest_nonremovable; + h->catalog_oldest_nonremovable = h->data_oldest_nonremovable; + if (b) + { + /* + * The catalog horizon starts from the data horizon; so does its + * blocker. + */ + b->catalog = b->data; + if (TransactionIdIsValid(h->slot_catalog_xmin)) + { + if (TransactionIdPrecedes(h->slot_catalog_xmin, + h->shared_oldest_nonremovable)) + b->shared = OLDEST_XMIN_BLOCKER_LOGICAL_SLOT; + if (TransactionIdPrecedes(h->slot_catalog_xmin, + h->catalog_oldest_nonremovable)) + b->catalog = OLDEST_XMIN_BLOCKER_LOGICAL_SLOT; + } + } h->shared_oldest_nonremovable = TransactionIdOlder(h->shared_oldest_nonremovable, h->slot_catalog_xmin); - h->catalog_oldest_nonremovable = h->data_oldest_nonremovable; h->catalog_oldest_nonremovable = TransactionIdOlder(h->catalog_oldest_nonremovable, h->slot_catalog_xmin); @@ -1945,7 +2006,7 @@ GetOldestNonRemovableTransactionId(Relation rel) { ComputeXidHorizonsResult horizons; - ComputeXidHorizons(&horizons); + ComputeXidHorizons(&horizons, NULL); switch (GlobalVisHorizonKindForRel(rel)) { @@ -1963,6 +2024,47 @@ GetOldestNonRemovableTransactionId(Relation rel) return InvalidTransactionId; } +/* + * Like GetOldestNonRemovableTransactionId(), but also reports what is holding + * the returned horizon back, for diagnostics such as VACUUM logging; see + * OldestXminBlocker. The blocker comes from the same horizon as the returned + * xid, so a logical slot's catalog_xmin is blamed only for catalog and shared + * relations, never for a plain user table. + */ +TransactionId +GetOldestNonRemovableTransactionIdExt(Relation rel, OldestXminBlocker *blocker) +{ + ComputeXidHorizonsResult horizons; + XminHorizonBlockers blockers; + + ComputeXidHorizons(&horizons, &blockers); + + switch (GlobalVisHorizonKindForRel(rel)) + { + case VISHORIZON_SHARED: + *blocker = blockers.shared; + return horizons.shared_oldest_nonremovable; + case VISHORIZON_CATALOG: + *blocker = blockers.catalog; + return horizons.catalog_oldest_nonremovable; + case VISHORIZON_DATA: + *blocker = blockers.data; + return horizons.data_oldest_nonremovable; + case VISHORIZON_TEMP: + + /* + * The temp horizon is bounded by this session's own xid, so + * nothing external holds it back. + */ + *blocker = OLDEST_XMIN_BLOCKER_NONE; + return horizons.temp_oldest_nonremovable; + } + + /* just to prevent compiler warnings */ + *blocker = OLDEST_XMIN_BLOCKER_NONE; + return InvalidTransactionId; +} + /* * Return the oldest transaction id any currently running backend might still * consider running. This should not be used for visibility / pruning @@ -1974,7 +2076,7 @@ GetOldestTransactionIdConsideredRunning(void) { ComputeXidHorizonsResult horizons; - ComputeXidHorizons(&horizons); + ComputeXidHorizons(&horizons, NULL); return horizons.oldest_considered_running; } @@ -1987,7 +2089,7 @@ GetReplicationHorizons(TransactionId *xmin, TransactionId *catalog_xmin) { ComputeXidHorizonsResult horizons; - ComputeXidHorizons(&horizons); + ComputeXidHorizons(&horizons, NULL); /* * Don't want to use shared_oldest_nonremovable here, as that contains the @@ -4214,7 +4316,7 @@ GlobalVisUpdate(void) ComputeXidHorizonsResult horizons; /* updates the horizons as a side-effect */ - ComputeXidHorizons(&horizons); + ComputeXidHorizons(&horizons, NULL); } /* diff --git a/src/include/commands/progress.h b/src/include/commands/progress.h index 2a12920c75f..fae4bd6b9e1 100644 --- a/src/include/commands/progress.h +++ b/src/include/commands/progress.h @@ -31,6 +31,7 @@ #define PROGRESS_VACUUM_DELAY_TIME 10 #define PROGRESS_VACUUM_MODE 11 #define PROGRESS_VACUUM_STARTED_BY 12 +#define PROGRESS_VACUUM_OLDEST_XMIN_BLOCKER 13 /* Phases of vacuum (as advertised via PROGRESS_VACUUM_PHASE) */ #define PROGRESS_VACUUM_PHASE_SCAN_HEAP 1 diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h index 956d9cea36d..9f107fd8262 100644 --- a/src/include/commands/vacuum.h +++ b/src/include/commands/vacuum.h @@ -23,6 +23,7 @@ #include "catalog/pg_type.h" #include "parser/parse_node.h" #include "storage/buf.h" +#include "storage/procarray.h" #include "utils/relcache.h" /* @@ -278,6 +279,12 @@ struct VacuumCutoffs TransactionId OldestXmin; MultiXactId OldestMxact; + /* + * A best-effort hint about what held OldestXmin back at the time it was + * computed, for diagnostics only. See OldestXminBlocker. + */ + OldestXminBlocker oldest_xmin_blocker; + /* * FreezeLimit is the Xid below which all Xids are definitely frozen or * removed in pages VACUUM scans and cleanup locks. @@ -386,6 +393,7 @@ extern void vac_update_relstats(Relation relation, extern bool vacuum_get_cutoffs(Relation rel, const VacuumParams *params, struct VacuumCutoffs *cutoffs); extern bool vacuum_xid_failsafe_check(const struct VacuumCutoffs *cutoffs); +extern const char *vacuum_oldest_xmin_blocker_name(OldestXminBlocker blocker); extern void vac_update_datfrozenxid(void); extern void vacuum_delay_point(bool is_analyze); extern bool vacuum_is_permitted_for_relation(Oid relid, Form_pg_class reltuple, diff --git a/src/include/storage/procarray.h b/src/include/storage/procarray.h index d718a5b542f..2df5e494faa 100644 --- a/src/include/storage/procarray.h +++ b/src/include/storage/procarray.h @@ -18,6 +18,35 @@ #include "utils/relcache.h" #include "utils/snapshot.h" +/* + * What is holding the oldest-xmin horizon back, that is, what stops VACUUM + * from removing more dead tuples. A best-effort hint: the backend or slot may + * change right after we look. + */ +typedef enum OldestXminBlocker +{ + OLDEST_XMIN_BLOCKER_NONE = 0, /* nothing older than this backend */ + OLDEST_XMIN_BLOCKER_RUNNING_XACT, /* a running transaction's xmin/xid */ + OLDEST_XMIN_BLOCKER_PREPARED_XACT, /* a prepared (two-phase) transaction */ + OLDEST_XMIN_BLOCKER_STANDBY_FEEDBACK, /* hot_standby_feedback with no + * slot */ + OLDEST_XMIN_BLOCKER_REPLICATION_SLOT, /* a slot's xmin */ + OLDEST_XMIN_BLOCKER_LOGICAL_SLOT, /* a slot's catalog_xmin (logical + * decoding) */ +} OldestXminBlocker; + +/* + * What holds each visibility horizon back, filled in by ComputeXidHorizons() + * alongside the horizons themselves. Kept out of ComputeXidHorizonsResult, and + * optional there, so the common callers that only want the horizons neither + * carry nor compute this. See OldestXminBlocker. + */ +typedef struct XminHorizonBlockers +{ + OldestXminBlocker shared; + OldestXminBlocker data; + OldestXminBlocker catalog; +} XminHorizonBlockers; extern void ProcArrayAdd(PGPROC *proc); extern void ProcArrayRemove(PGPROC *proc, TransactionId latestXid); @@ -51,6 +80,8 @@ extern RunningTransactions GetRunningTransactionData(void); extern bool TransactionIdIsInProgress(TransactionId xid); extern TransactionId GetOldestNonRemovableTransactionId(Relation rel); +extern TransactionId GetOldestNonRemovableTransactionIdExt(Relation rel, + OldestXminBlocker *blocker); extern TransactionId GetOldestTransactionIdConsideredRunning(void); extern TransactionId GetOldestActiveTransactionId(bool inCommitOnly, bool allDbs); diff --git a/src/test/recovery/meson.build b/src/test/recovery/meson.build index ad0d85f4189..eb9d55383e0 100644 --- a/src/test/recovery/meson.build +++ b/src/test/recovery/meson.build @@ -63,6 +63,7 @@ tests += { 't/052_checkpoint_segment_missing.pl', 't/053_standby_login_event_trigger.pl', 't/054_unlogged_sequence_promotion.pl', + 't/055_vacuum_oldest_xmin_blocker.pl', ], }, } diff --git a/src/test/recovery/t/055_vacuum_oldest_xmin_blocker.pl b/src/test/recovery/t/055_vacuum_oldest_xmin_blocker.pl new file mode 100644 index 00000000000..e01fa62d2fe --- /dev/null +++ b/src/test/recovery/t/055_vacuum_oldest_xmin_blocker.pl @@ -0,0 +1,163 @@ +# Copyright (c) 2026, PostgreSQL Global Development Group + +# Test that VACUUM reports the category of what holds the oldest xmin horizon +# back. The category is a best-effort hint, so we only assert the expected +# category, not any exact transaction or slot. + +use strict; +use warnings FATAL => 'all'; +use PostgreSQL::Test::Cluster; +use PostgreSQL::Test::Utils; +use Test::More; + +my $node = PostgreSQL::Test::Cluster->new('primary'); +$node->init(allows_streaming => 'logical'); +$node->append_conf('postgresql.conf', qq{ +autovacuum = off +max_prepared_transactions = 5 +}); +$node->start; + +$node->safe_psql('postgres', + "CREATE TABLE t (id int); INSERT INTO t VALUES (1)"); + +# Advance a few XIDs so a concurrent transaction's xmin is older than this +# session's and the blocker gets reported. +sub advance_xids +{ + $node->safe_psql('postgres', + "SELECT txid_current() FROM generate_series(1, 5)"); +} + +# Vacuum a relation and return the output that names the blocker category. +sub vacuum_verbose +{ + my $rel = shift // 't'; + my ($stdout, $stderr); + advance_xids(); + $node->psql('postgres', "VACUUM (VERBOSE) $rel", + stdout => \$stdout, stderr => \$stderr); + return $stderr; +} + +# Nothing holds the horizon back: no blocker line. +{ + my $log = vacuum_verbose(); + unlike($log, qr/oldest xmin held back by/, + 'no blocker reported when nothing holds the horizon back'); +} + +# Running transaction. +{ + my $bg = $node->background_psql('postgres'); + $bg->query_safe("BEGIN"); + $bg->query_safe("SELECT txid_current()"); + + my $log = vacuum_verbose(); + like($log, qr/oldest xmin held back by: running transaction/, + 'blocker reported: running transaction'); + + $bg->query_safe("ROLLBACK"); + $bg->quit; +} + +# Prepared transaction. +{ + $node->safe_psql('postgres', qq{ + BEGIN; + INSERT INTO t VALUES (2); + PREPARE TRANSACTION 'hold_xmin'; + }); + + my $log = vacuum_verbose(); + like($log, qr/oldest xmin held back by: prepared transaction/, + 'blocker reported: prepared transaction'); + + $node->safe_psql('postgres', "ROLLBACK PREPARED 'hold_xmin'"); +} + +# Logical slot's catalog_xmin: not reported for a plain user table, but +# reported for a catalog relation. +{ + $node->safe_psql('postgres', + "SELECT pg_create_logical_replication_slot('s', 'test_decoding')"); + + # Creating the slot reserves its catalog_xmin synchronously; make sure it + # is set before relying on it as the cause. + is($node->safe_psql('postgres', + "SELECT catalog_xmin IS NOT NULL FROM pg_replication_slots WHERE slot_name = 's'"), + 't', 'logical slot has a catalog_xmin'); + + my $log = vacuum_verbose(); + unlike($log, qr/oldest xmin held back by: logical replication slot/, + 'user table not held back by logical slot catalog_xmin'); + + my $catlog = vacuum_verbose('pg_class'); + like($catlog, qr/oldest xmin held back by: logical replication slot/, + 'catalog relation held back by logical slot catalog_xmin'); + + $node->safe_psql('postgres', "SELECT pg_drop_replication_slot('s')"); +} + +# Physical slot with a reserved xmin. +{ + $node->safe_psql('postgres', + "SELECT pg_create_physical_replication_slot('ps', true)"); + + $node->backup('backup1'); + my $standby = PostgreSQL::Test::Cluster->new('standby'); + $standby->init_from_backup($node, 'backup1', has_streaming => 1); + $standby->append_conf('postgresql.conf', qq{ +primary_slot_name = 'ps' +hot_standby_feedback = on +wal_receiver_status_interval = 1 +}); + $standby->start; + + $node->poll_query_until('postgres', qq{ + SELECT xmin IS NOT NULL FROM pg_replication_slots + WHERE slot_name = 'ps'; + }) or die "timed out waiting for slot xmin"; + + # Stop the standby so only the slot's frozen xmin holds the horizon. + $standby->stop; + + my $log = vacuum_verbose(); + like($log, qr/oldest xmin held back by: replication slot/, + 'blocker reported: replication slot'); + + $node->safe_psql('postgres', "SELECT pg_drop_replication_slot('ps')"); +} + +# hot_standby_feedback without a slot: held by the walsender proc on the +# primary, reported as standby feedback rather than a slot. +{ + $node->backup('backup2'); + my $standby = PostgreSQL::Test::Cluster->new('standby_fb'); + $standby->init_from_backup($node, 'backup2', has_streaming => 1); + $standby->append_conf('postgresql.conf', qq{ +hot_standby_feedback = on +wal_receiver_status_interval = 1 +}); + $standby->start; + + # Hold an old snapshot on the standby so its feedback pins an xmin. + my $bg = $standby->background_psql('postgres'); + $bg->query_safe("BEGIN ISOLATION LEVEL REPEATABLE READ"); + $bg->query_safe("SELECT 1"); + + $node->poll_query_until('postgres', qq{ + SELECT backend_xmin IS NOT NULL FROM pg_stat_replication; + }) or die "timed out waiting for standby feedback xmin"; + + my $log = vacuum_verbose(); + like($log, qr/oldest xmin held back by: standby feedback/, + 'blocker reported: standby feedback'); + + $bg->query_safe("ROLLBACK"); + $bg->quit; + $standby->stop; +} + +$node->stop; +done_testing(); diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 6a3341356da..4b0d61273ef 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2213,7 +2213,15 @@ pg_stat_progress_vacuum| SELECT s.pid, WHEN 2 THEN 'autovacuum'::text WHEN 3 THEN 'autovacuum_wraparound'::text ELSE NULL::text - END AS started_by + END AS started_by, + CASE s.param14 + WHEN 1 THEN 'running transaction'::text + WHEN 2 THEN 'prepared transaction'::text + WHEN 3 THEN 'standby feedback'::text + WHEN 4 THEN 'replication slot'::text + WHEN 5 THEN 'logical replication slot'::text + ELSE NULL::text + END AS oldest_xmin_blocker FROM (pg_stat_get_progress_info('VACUUM'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20) LEFT JOIN pg_database d ON ((s.datid = d.oid))); pg_stat_recovery| SELECT promote_triggered, diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 56c1f997f88..95284065e19 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -1903,6 +1903,7 @@ OkeysState OldMultiXactReader OldToNewMapping OldToNewMappingData +OldestXminBlocker OnCommitAction OnCommitItem OnConflictAction @@ -3549,6 +3550,7 @@ XidBoundsViolation XidCacheStatus XidCommitStatus XidStatus +XminHorizonBlockers XmlExpr XmlExprOp XmlOptionType -- 2.47.3
From 8142c388ee96cc8bf4980cc784cc36b80ab7e7f2 Mon Sep 17 00:00:00 2001 From: Bharath Rupireddy <[email protected]> Date: Tue, 14 Jul 2026 16:44:35 +0000 Subject: [PATCH v1 2/2] Name the specific blocker holding back VACUUM's oldest xmin The previous commit reports the category of the blocker. That is enough to know which subsystem to look at, but when the horizon is dangerously old the user needs the exact thing to act on. So when OldestXmin is held back to an unsafe degree and VACUUM warns that its cutoff is far in the past, name the specific blocker in that warning: the backend PID for a running transaction, the GID for a prepared transaction, the slot name for a replication slot, and the walsender PID for standby feedback. Finding the identity needs TwoPhaseStateLock or ReplicationSlotControlLock, so it is done only for the warning, not for the routine progress and verbose reporting the previous commit added. The PID comes from the proc that sets the horizon, captured under the ProcArrayLock ComputeXidHorizons() already holds. The GID and slot name are looked up from the captured xid at warning time, outside that lock, and are best-effort: if the holder is gone by then, only the category is reported. --- src/backend/access/heap/vacuumlazy.c | 9 +- src/backend/access/transam/twophase.c | 33 +++++ src/backend/commands/vacuum.c | 78 ++++++++++-- src/backend/replication/slot.c | 43 +++++++ src/backend/storage/ipc/procarray.c | 74 +++++++---- src/include/access/twophase.h | 1 + src/include/commands/vacuum.h | 6 +- src/include/replication/slot.h | 2 + src/include/storage/procarray.h | 20 ++- .../t/055_vacuum_oldest_xmin_blocker.pl | 117 ++++++++++++++---- src/tools/pgindent/typedefs.list | 1 + 11 files changed, 317 insertions(+), 67 deletions(-) diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c index f171a7b1af4..ad7bf17508d 100644 --- a/src/backend/access/heap/vacuumlazy.c +++ b/src/backend/access/heap/vacuumlazy.c @@ -844,7 +844,7 @@ heap_vacuum_rel(Relation rel, const VacuumParams *params, * pg_stat_progress_vacuum can see why dead tuples aren't being removed. */ pgstat_progress_update_param(PROGRESS_VACUUM_OLDEST_XMIN_BLOCKER, - vacrel->cutoffs.oldest_xmin_blocker); + vacrel->cutoffs.oldest_xmin_blocker.kind); if (verbose) { @@ -1091,10 +1091,9 @@ 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->cutoffs.oldest_xmin_blocker != OLDEST_XMIN_BLOCKER_NONE) - appendStringInfo(&buf, - _("oldest xmin held back by: %s\n"), - vacuum_oldest_xmin_blocker_name(vacrel->cutoffs.oldest_xmin_blocker)); + if (vacrel->cutoffs.oldest_xmin_blocker.kind != OLDEST_XMIN_BLOCKER_NONE) + appendStringInfo(&buf, _("oldest xmin held back by: %s\n"), + vacuum_oldest_xmin_blocker_name(vacrel->cutoffs.oldest_xmin_blocker.kind)); if (frozenxid_updated) { diff = (int32) (vacrel->NewRelfrozenXid - diff --git a/src/backend/access/transam/twophase.c b/src/backend/access/transam/twophase.c index 1035e8b3fc7..d0985e83c59 100644 --- a/src/backend/access/transam/twophase.c +++ b/src/backend/access/transam/twophase.c @@ -850,6 +850,39 @@ TwoPhaseGetGXact(FullTransactionId fxid, bool lock_held) return result; } +/* + * Look up the GID of a prepared transaction by its XID, for diagnostics such + * as reporting what holds VACUUM's horizon back. + * + * Best-effort: returns false rather than erroring when no match is found, + * since this is only for reporting and the transaction may have committed or + * aborted by the time we look. + */ +bool +TwoPhaseGetGidByXid(TransactionId xid, char *gid, int gidsize) +{ + bool found = false; + + if (!TransactionIdIsValid(xid)) + return false; + + LWLockAcquire(TwoPhaseStateLock, LW_SHARED); + for (int i = 0; i < TwoPhaseState->numPrepXacts; i++) + { + GlobalTransaction gxact = TwoPhaseState->prepXacts[i]; + + if (XidFromFullTransactionId(gxact->fxid) == xid) + { + strlcpy(gid, gxact->gid, gidsize); + found = true; + break; + } + } + LWLockRelease(TwoPhaseStateLock); + + return found; +} + /* * TwoPhaseGetXidByVirtualXID * Lookup VXID among xacts prepared since last startup. diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index a5a95df0ae3..7b8ad6ac1af 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -33,6 +33,7 @@ #include "access/multixact.h" #include "access/tableam.h" #include "access/transam.h" +#include "access/twophase.h" #include "access/xact.h" #include "catalog/namespace.h" #include "catalog/pg_database.h" @@ -48,6 +49,7 @@ #include "postmaster/autovacuum.h" #include "postmaster/bgworker_internals.h" #include "postmaster/interrupt.h" +#include "replication/slot.h" #include "storage/bufmgr.h" #include "storage/lmgr.h" #include "storage/pmsignal.h" @@ -157,6 +159,54 @@ vacuum_oldest_xmin_blocker_name(OldestXminBlocker blocker) return OldestXminBlockerNames[blocker]; } +/* + * Append a blocker's identity to buf, to follow its category name, and return + * true, or return false when there is nothing more specific to add. The 2PC + * and slot lookups are best-effort. + */ +bool +vacuum_oldest_xmin_blocker_ident(const OldestXminBlockerInfo *blocker, + StringInfo buf) +{ + if (blocker->kind == OLDEST_XMIN_BLOCKER_RUNNING_XACT) + { + appendStringInfo(buf, " (pid %d)", blocker->pid); + return true; + } + else if (blocker->kind == OLDEST_XMIN_BLOCKER_STANDBY_FEEDBACK) + { + appendStringInfo(buf, + " (walsender pid %d; the transaction runs on the standby)", + blocker->pid); + return true; + } + else if (blocker->kind == OLDEST_XMIN_BLOCKER_PREPARED_XACT) + { + char gid[GIDSIZE]; + + if (TwoPhaseGetGidByXid(blocker->xid, gid, sizeof(gid))) + { + appendStringInfo(buf, " '%s'", gid); + return true; + } + } + else if (blocker->kind == OLDEST_XMIN_BLOCKER_REPLICATION_SLOT || + blocker->kind == OLDEST_XMIN_BLOCKER_LOGICAL_SLOT) + { + char slotname[NAMEDATALEN]; + bool catalog = (blocker->kind == OLDEST_XMIN_BLOCKER_LOGICAL_SLOT); + + if (ReplicationSlotNameForXmin(blocker->xid, catalog, + slotname, sizeof(slotname))) + { + appendStringInfo(buf, " (slot \"%s\")", slotname); + return true; + } + } + + return false; +} + /* * GUC check function to ensure GUC value specified is within the allowable * range. @@ -1196,18 +1246,22 @@ vacuum_get_cutoffs(Relation rel, const VacuumParams *params, safeOldestMxact = FirstMultiXactId; if (TransactionIdPrecedes(cutoffs->OldestXmin, safeOldestXmin)) { - if (cutoffs->oldest_xmin_blocker != OLDEST_XMIN_BLOCKER_NONE) - ereport(WARNING, - (errmsg("cutoff for removing and freezing tuples is far in the past"), - errdetail("The oldest xmin horizon is currently held back by: %s.", - vacuum_oldest_xmin_blocker_name(cutoffs->oldest_xmin_blocker)), - errhint("Close open transactions soon to avoid wraparound problems.\n" - "You might also need to commit or roll back old prepared transactions, or drop stale replication slots."))); - else - ereport(WARNING, - (errmsg("cutoff for removing and freezing tuples is far in the past"), - errhint("Close open transactions soon to avoid wraparound problems.\n" - "You might also need to commit or roll back old prepared transactions, or drop stale replication slots."))); + OldestXminBlockerInfo *blocker = &cutoffs->oldest_xmin_blocker; + StringInfoData ident; + + /* identity to append after the category name, empty if none */ + initStringInfo(&ident); + if (blocker->kind != OLDEST_XMIN_BLOCKER_NONE) + vacuum_oldest_xmin_blocker_ident(blocker, &ident); + + ereport(WARNING, + (errmsg("cutoff for removing and freezing tuples is far in the past"), + blocker->kind != OLDEST_XMIN_BLOCKER_NONE ? + errdetail("The oldest xmin horizon is currently held back by: %s%s.", + vacuum_oldest_xmin_blocker_name(blocker->kind), ident.data) : 0, + errhint("Close open transactions soon to avoid wraparound problems.\n" + "You might also need to commit or roll back old prepared transactions, or drop stale replication slots."))); + pfree(ident.data); } if (MultiXactIdPrecedes(cutoffs->OldestMxact, safeOldestMxact)) ereport(WARNING, diff --git a/src/backend/replication/slot.c b/src/backend/replication/slot.c index d7fb9f5a67f..0311739f28d 100644 --- a/src/backend/replication/slot.c +++ b/src/backend/replication/slot.c @@ -1293,6 +1293,49 @@ ReplicationSlotsComputeRequiredXmin(bool already_locked) LWLockRelease(ReplicationSlotControlLock); } +/* + * Find a slot whose xmin (catalog_xmin, when catalog is true) equals the given + * xid, for diagnostics such as reporting what holds VACUUM's horizon back. The + * catalog flag keeps the named slot consistent with the blocker's category. + * + * Best-effort: returns false rather than erroring when no match is found, + * since this is only for reporting and the slot may have advanced by the time + * we look. When several slots share the xid, names just the first found. + */ +bool +ReplicationSlotNameForXmin(TransactionId xid, bool catalog, + char *name, int namesize) +{ + bool found = false; + + if (!TransactionIdIsValid(xid)) + return false; + + LWLockAcquire(ReplicationSlotControlLock, LW_SHARED); + for (int i = 0; i < max_replication_slots + max_repack_replication_slots; i++) + { + ReplicationSlot *s = &ReplicationSlotCtl->replication_slots[i]; + TransactionId slot_xmin; + + if (!s->in_use) + continue; + + SpinLockAcquire(&s->mutex); + slot_xmin = catalog ? s->effective_catalog_xmin : s->effective_xmin; + SpinLockRelease(&s->mutex); + + if (xid == slot_xmin) + { + strlcpy(name, NameStr(s->data.name), namesize); + found = true; + break; + } + } + LWLockRelease(ReplicationSlotControlLock); + + return found; +} + /* * Compute the oldest restart LSN across all slots and inform xlog module. * diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c index 2997bbcbb3a..baa37fd7745 100644 --- a/src/backend/storage/ipc/procarray.c +++ b/src/backend/storage/ipc/procarray.c @@ -1613,6 +1613,15 @@ TransactionIdIsInProgress(TransactionId xid) return false; } +/* Build an oldest-xmin blocker; the caller fills in the pid when relevant. */ +static inline OldestXminBlockerInfo +make_xmin_blocker(OldestXminBlocker kind, TransactionId xid) +{ + OldestXminBlockerInfo b = {kind, 0, xid}; + + return b; +} + /* * Determine XID horizons. @@ -1683,11 +1692,7 @@ ComputeXidHorizons(ComputeXidHorizonsResult *h, XminHorizonBlockers *b) /* no blocker identified yet, for callers that asked for one */ if (b) - { - b->shared = OLDEST_XMIN_BLOCKER_NONE; - b->data = OLDEST_XMIN_BLOCKER_NONE; - b->catalog = OLDEST_XMIN_BLOCKER_NONE; - } + MemSet(b, 0, sizeof(*b)); LWLockAcquire(ProcArrayLock, LW_SHARED); @@ -1743,7 +1748,8 @@ ComputeXidHorizons(ComputeXidHorizonsResult *h, XminHorizonBlockers *b) int8 statusFlags = ProcGlobal->statusFlags[index]; TransactionId xid; TransactionId xmin; - OldestXminBlocker this_blocker = OLDEST_XMIN_BLOCKER_NONE; + OldestXminBlockerInfo this_blocker; + bool xid_owner = false; /* Fetch xid just once - see GetNewTransactionId */ xid = UINT32_ACCESS_ONCE(other_xids[index]); @@ -1785,18 +1791,39 @@ ComputeXidHorizons(ComputeXidHorizonsResult *h, XminHorizonBlockers *b) * PROC_AFFECTS_ALL_HORIZONS marks a walsender that reserves an xmin * via its PGPROC entry rather than a slot, i.e. hot_standby_feedback * with no slot (see ProcessStandbyHSFeedbackMessage). Anything else - * is a running transaction. + * is a running transaction. We keep the PID (for the running xact and + * the walsender) or the xid (for the prepared xact, to resolve its + * GID later), whichever names it. + * + * xid_owner is true when this proc's own xid reaches the horizon, + * rather than a snapshot xmin that observes that xid. When several + * procs share the same value, the owner is preferred, since ending it + * is more likely to let the horizon advance. For example, backend A + * holds xid 100 while backends B, C, D, E run with xmin 100; all five + * are at 100, and A is reported. */ if (b) { + xid_owner = TransactionIdIsValid(xid) && xid == xmin; + if (proc->pid == 0) - this_blocker = OLDEST_XMIN_BLOCKER_PREPARED_XACT; + { + this_blocker.kind = OLDEST_XMIN_BLOCKER_PREPARED_XACT; + this_blocker.xid = xid; + } else if (statusFlags & PROC_AFFECTS_ALL_HORIZONS) - this_blocker = OLDEST_XMIN_BLOCKER_STANDBY_FEEDBACK; + { + this_blocker.kind = OLDEST_XMIN_BLOCKER_STANDBY_FEEDBACK; + this_blocker.pid = proc->pid; + } else - this_blocker = OLDEST_XMIN_BLOCKER_RUNNING_XACT; + { + this_blocker.kind = OLDEST_XMIN_BLOCKER_RUNNING_XACT; + this_blocker.pid = proc->pid; + } - if (TransactionIdPrecedes(xmin, h->shared_oldest_nonremovable)) + if (TransactionIdPrecedes(xmin, h->shared_oldest_nonremovable) || + (xid_owner && xmin == h->shared_oldest_nonremovable)) b->shared = this_blocker; } @@ -1828,7 +1855,8 @@ ComputeXidHorizons(ComputeXidHorizonsResult *h, XminHorizonBlockers *b) (statusFlags & PROC_AFFECTS_ALL_HORIZONS) || in_recovery) { - if (b && TransactionIdPrecedes(xmin, h->data_oldest_nonremovable)) + if (b && (TransactionIdPrecedes(xmin, h->data_oldest_nonremovable) || + (xid_owner && xmin == h->data_oldest_nonremovable))) b->data = this_blocker; h->data_oldest_nonremovable = TransactionIdOlder(h->data_oldest_nonremovable, xmin); @@ -1853,11 +1881,11 @@ ComputeXidHorizons(ComputeXidHorizonsResult *h, XminHorizonBlockers *b) h->oldest_considered_running = TransactionIdOlder(h->oldest_considered_running, kaxmin); if (b && TransactionIdPrecedes(kaxmin, h->shared_oldest_nonremovable)) - b->shared = OLDEST_XMIN_BLOCKER_RUNNING_XACT; + b->shared = make_xmin_blocker(OLDEST_XMIN_BLOCKER_RUNNING_XACT, InvalidTransactionId); h->shared_oldest_nonremovable = TransactionIdOlder(h->shared_oldest_nonremovable, kaxmin); if (b && TransactionIdPrecedes(kaxmin, h->data_oldest_nonremovable)) - b->data = OLDEST_XMIN_BLOCKER_RUNNING_XACT; + b->data = make_xmin_blocker(OLDEST_XMIN_BLOCKER_RUNNING_XACT, InvalidTransactionId); h->data_oldest_nonremovable = TransactionIdOlder(h->data_oldest_nonremovable, kaxmin); /* temp relations cannot be accessed in recovery */ @@ -1875,9 +1903,11 @@ ComputeXidHorizons(ComputeXidHorizonsResult *h, XminHorizonBlockers *b) if (b && TransactionIdIsValid(h->slot_xmin)) { if (TransactionIdPrecedes(h->slot_xmin, h->shared_oldest_nonremovable)) - b->shared = OLDEST_XMIN_BLOCKER_REPLICATION_SLOT; + b->shared = make_xmin_blocker(OLDEST_XMIN_BLOCKER_REPLICATION_SLOT, + h->slot_xmin); if (TransactionIdPrecedes(h->slot_xmin, h->data_oldest_nonremovable)) - b->data = OLDEST_XMIN_BLOCKER_REPLICATION_SLOT; + b->data = make_xmin_blocker(OLDEST_XMIN_BLOCKER_REPLICATION_SLOT, + h->slot_xmin); } h->shared_oldest_nonremovable = TransactionIdOlder(h->shared_oldest_nonremovable, h->slot_xmin); @@ -1904,10 +1934,12 @@ ComputeXidHorizons(ComputeXidHorizonsResult *h, XminHorizonBlockers *b) { if (TransactionIdPrecedes(h->slot_catalog_xmin, h->shared_oldest_nonremovable)) - b->shared = OLDEST_XMIN_BLOCKER_LOGICAL_SLOT; + b->shared = make_xmin_blocker(OLDEST_XMIN_BLOCKER_LOGICAL_SLOT, + h->slot_catalog_xmin); if (TransactionIdPrecedes(h->slot_catalog_xmin, h->catalog_oldest_nonremovable)) - b->catalog = OLDEST_XMIN_BLOCKER_LOGICAL_SLOT; + b->catalog = make_xmin_blocker(OLDEST_XMIN_BLOCKER_LOGICAL_SLOT, + h->slot_catalog_xmin); } } h->shared_oldest_nonremovable = @@ -2032,7 +2064,7 @@ GetOldestNonRemovableTransactionId(Relation rel) * relations, never for a plain user table. */ TransactionId -GetOldestNonRemovableTransactionIdExt(Relation rel, OldestXminBlocker *blocker) +GetOldestNonRemovableTransactionIdExt(Relation rel, OldestXminBlockerInfo *blocker) { ComputeXidHorizonsResult horizons; XminHorizonBlockers blockers; @@ -2056,12 +2088,12 @@ GetOldestNonRemovableTransactionIdExt(Relation rel, OldestXminBlocker *blocker) * The temp horizon is bounded by this session's own xid, so * nothing external holds it back. */ - *blocker = OLDEST_XMIN_BLOCKER_NONE; + *blocker = make_xmin_blocker(OLDEST_XMIN_BLOCKER_NONE, InvalidTransactionId); return horizons.temp_oldest_nonremovable; } /* just to prevent compiler warnings */ - *blocker = OLDEST_XMIN_BLOCKER_NONE; + *blocker = make_xmin_blocker(OLDEST_XMIN_BLOCKER_NONE, InvalidTransactionId); return InvalidTransactionId; } diff --git a/src/include/access/twophase.h b/src/include/access/twophase.h index 1d2ff42c9b7..30d83248350 100644 --- a/src/include/access/twophase.h +++ b/src/include/access/twophase.h @@ -38,6 +38,7 @@ extern void PostPrepare_Twophase(void); extern TransactionId TwoPhaseGetXidByVirtualXID(VirtualTransactionId vxid, bool *have_more); +extern bool TwoPhaseGetGidByXid(TransactionId xid, char *gid, int gidsize); extern PGPROC *TwoPhaseGetDummyProc(FullTransactionId fxid, bool lock_held); extern int TwoPhaseGetDummyProcNumber(FullTransactionId fxid, bool lock_held); diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h index 9f107fd8262..1ad53afa09b 100644 --- a/src/include/commands/vacuum.h +++ b/src/include/commands/vacuum.h @@ -281,9 +281,9 @@ struct VacuumCutoffs /* * A best-effort hint about what held OldestXmin back at the time it was - * computed, for diagnostics only. See OldestXminBlocker. + * computed, for diagnostics only. See OldestXminBlockerInfo. */ - OldestXminBlocker oldest_xmin_blocker; + OldestXminBlockerInfo oldest_xmin_blocker; /* * FreezeLimit is the Xid below which all Xids are definitely frozen or @@ -394,6 +394,8 @@ extern bool vacuum_get_cutoffs(Relation rel, const VacuumParams *params, struct VacuumCutoffs *cutoffs); extern bool vacuum_xid_failsafe_check(const struct VacuumCutoffs *cutoffs); extern const char *vacuum_oldest_xmin_blocker_name(OldestXminBlocker blocker); +extern bool vacuum_oldest_xmin_blocker_ident(const OldestXminBlockerInfo *blocker, + StringInfo buf); extern void vac_update_datfrozenxid(void); extern void vacuum_delay_point(bool is_analyze); extern bool vacuum_is_permitted_for_relation(Oid relid, Form_pg_class reltuple, diff --git a/src/include/replication/slot.h b/src/include/replication/slot.h index 9b29444cbca..6b6bbc54175 100644 --- a/src/include/replication/slot.h +++ b/src/include/replication/slot.h @@ -357,6 +357,8 @@ extern bool ReplicationSlotValidateNameInternal(const char *name, extern void ReplicationSlotReserveWal(void); extern void ReplicationSlotsComputeRequiredXmin(bool already_locked); extern void ReplicationSlotsComputeRequiredLSN(void); +extern bool ReplicationSlotNameForXmin(TransactionId xid, bool catalog, + char *name, int namesize); extern XLogRecPtr ReplicationSlotsComputeLogicalRestartLSN(void); extern bool ReplicationSlotsCountDBSlots(Oid dboid, int *nslots, int *nactive); extern bool CheckLogicalSlotExists(void); diff --git a/src/include/storage/procarray.h b/src/include/storage/procarray.h index 2df5e494faa..f3dc07c6906 100644 --- a/src/include/storage/procarray.h +++ b/src/include/storage/procarray.h @@ -35,6 +35,18 @@ typedef enum OldestXminBlocker * decoding) */ } OldestXminBlocker; +/* + * A blocker's kind plus what we can cheaply name it by: a backend or walsender + * pid, or an xid to look up a GID or slot name later. Unused fields are left + * at 0 or InvalidTransactionId. + */ +typedef struct OldestXminBlockerInfo +{ + OldestXminBlocker kind; + int pid; + TransactionId xid; +} OldestXminBlockerInfo; + /* * What holds each visibility horizon back, filled in by ComputeXidHorizons() * alongside the horizons themselves. Kept out of ComputeXidHorizonsResult, and @@ -43,9 +55,9 @@ typedef enum OldestXminBlocker */ typedef struct XminHorizonBlockers { - OldestXminBlocker shared; - OldestXminBlocker data; - OldestXminBlocker catalog; + OldestXminBlockerInfo shared; + OldestXminBlockerInfo data; + OldestXminBlockerInfo catalog; } XminHorizonBlockers; extern void ProcArrayAdd(PGPROC *proc); @@ -81,7 +93,7 @@ extern RunningTransactions GetRunningTransactionData(void); extern bool TransactionIdIsInProgress(TransactionId xid); extern TransactionId GetOldestNonRemovableTransactionId(Relation rel); extern TransactionId GetOldestNonRemovableTransactionIdExt(Relation rel, - OldestXminBlocker *blocker); + OldestXminBlockerInfo *blocker); extern TransactionId GetOldestTransactionIdConsideredRunning(void); extern TransactionId GetOldestActiveTransactionId(bool inCommitOnly, bool allDbs); diff --git a/src/test/recovery/t/055_vacuum_oldest_xmin_blocker.pl b/src/test/recovery/t/055_vacuum_oldest_xmin_blocker.pl index e01fa62d2fe..3b59fbf2707 100644 --- a/src/test/recovery/t/055_vacuum_oldest_xmin_blocker.pl +++ b/src/test/recovery/t/055_vacuum_oldest_xmin_blocker.pl @@ -10,16 +10,28 @@ use PostgreSQL::Test::Cluster; use PostgreSQL::Test::Utils; use Test::More; +# Consuming this many XIDs pushes the horizon past autovacuum_freeze_max_age, +# so VACUUM emits the warning that names the specific blocker. +my $freeze_max_age = 100000; +my $xids_to_consume = $freeze_max_age + 10000; + my $node = PostgreSQL::Test::Cluster->new('primary'); $node->init(allows_streaming => 'logical'); $node->append_conf('postgresql.conf', qq{ autovacuum = off +autovacuum_freeze_max_age = $freeze_max_age max_prepared_transactions = 5 }); $node->start; -$node->safe_psql('postgres', - "CREATE TABLE t (id int); INSERT INTO t VALUES (1)"); +$node->safe_psql('postgres', qq{ + CREATE TABLE t (id int); + INSERT INTO t VALUES (1); + CREATE PROCEDURE consume_xids(n int) AS \$\$ + BEGIN + FOR i IN 1..n LOOP PERFORM txid_current(); COMMIT; END LOOP; + END \$\$ LANGUAGE plpgsql; +}); # Advance a few XIDs so a concurrent transaction's xmin is older than this # session's and the blocker gets reported. @@ -40,6 +52,41 @@ sub vacuum_verbose return $stderr; } +# Push the horizon far into the past and VACUUM, so the warning fires and names +# the specific blocker. Returns the server log written during the VACUUM. +sub vacuum_far_in_past +{ + my $rel = shift // 't'; + my $logstart = -s $node->logfile; + $node->safe_psql('postgres', "CALL consume_xids($xids_to_consume)"); + $node->safe_psql('postgres', "VACUUM $rel"); + return slurp_file($node->logfile, $logstart); +} + +# Reset relfrozenxid so a prior holder doesn't keep the warning firing. +sub reset_table +{ + $node->safe_psql('postgres', "VACUUM FREEZE t"); +} + +# Open n backends that hold a snapshot whose xmin is the current oldest xid, +# without owning that xid themselves. The blocker that owns the xid must be +# reported ahead of these. Returns the session handles. +sub open_snapshot_holders +{ + my ($n) = @_; + my @sessions; + + for (1 .. $n) + { + my $s = $node->background_psql('postgres'); + $s->query_safe("BEGIN ISOLATION LEVEL REPEATABLE READ"); + $s->query_safe("SELECT 1"); + push @sessions, $s; + } + return @sessions; +} + # Nothing holds the horizon back: no blocker line. { my $log = vacuum_verbose(); @@ -47,21 +94,32 @@ sub vacuum_verbose 'no blocker reported when nothing holds the horizon back'); } -# Running transaction. +# Running transaction: named by its backend PID. It owns the oldest xid; the +# other backends only see that xid as their xmin, so the one that owns it must +# be reported ahead of them, whatever the proc array order. { my $bg = $node->background_psql('postgres'); $bg->query_safe("BEGIN"); $bg->query_safe("SELECT txid_current()"); + my $pid = $bg->query_safe("SELECT pg_backend_pid()"); - my $log = vacuum_verbose(); - like($log, qr/oldest xmin held back by: running transaction/, - 'blocker reported: running transaction'); + my @holders = open_snapshot_holders(3); + + my $log = vacuum_far_in_past(); + like($log, + qr/held back by: running transaction \(pid $pid\)/, + 'blocker reported: the xid owner, not a snapshot holder'); + $_->query_safe("ROLLBACK"), $_->quit for @holders; $bg->query_safe("ROLLBACK"); $bg->quit; + reset_table(); } -# Prepared transaction. +# Prepared transaction: named by its identifier. Like the running case, its own +# xid is the oldest; the other backends only see that xid as their xmin. The +# prepared xact must be reported ahead of them, otherwise the wrong category +# (running transaction) and PID would be reported. { $node->safe_psql('postgres', qq{ BEGIN; @@ -69,15 +127,20 @@ sub vacuum_verbose PREPARE TRANSACTION 'hold_xmin'; }); - my $log = vacuum_verbose(); - like($log, qr/oldest xmin held back by: prepared transaction/, - 'blocker reported: prepared transaction'); + my @holders = open_snapshot_holders(3); + + my $log = vacuum_far_in_past(); + like($log, + qr/held back by: prepared transaction 'hold_xmin'/, + 'blocker reported: the prepared xact, not a snapshot holder'); + $_->query_safe("ROLLBACK"), $_->quit for @holders; $node->safe_psql('postgres', "ROLLBACK PREPARED 'hold_xmin'"); + reset_table(); } -# Logical slot's catalog_xmin: not reported for a plain user table, but -# reported for a catalog relation. +# Logical slot's catalog_xmin: not reported for a plain user table, but names +# the slot for a catalog relation. { $node->safe_psql('postgres', "SELECT pg_create_logical_replication_slot('s', 'test_decoding')"); @@ -92,14 +155,16 @@ sub vacuum_verbose unlike($log, qr/oldest xmin held back by: logical replication slot/, 'user table not held back by logical slot catalog_xmin'); - my $catlog = vacuum_verbose('pg_class'); - like($catlog, qr/oldest xmin held back by: logical replication slot/, - 'catalog relation held back by logical slot catalog_xmin'); + my $catlog = vacuum_far_in_past('pg_class'); + like($catlog, + qr/held back by: logical replication slot \(slot "s"\)/, + 'catalog relation held back by logical slot, slot named'); $node->safe_psql('postgres', "SELECT pg_drop_replication_slot('s')"); + reset_table(); } -# Physical slot with a reserved xmin. +# Physical slot with a reserved xmin: named by the slot. { $node->safe_psql('postgres', "SELECT pg_create_physical_replication_slot('ps', true)"); @@ -122,15 +187,17 @@ wal_receiver_status_interval = 1 # Stop the standby so only the slot's frozen xmin holds the horizon. $standby->stop; - my $log = vacuum_verbose(); - like($log, qr/oldest xmin held back by: replication slot/, - 'blocker reported: replication slot'); + my $log = vacuum_far_in_past(); + like($log, + qr/held back by: replication slot \(slot "ps"\)/, + 'blocker reported: replication slot, slot named'); $node->safe_psql('postgres', "SELECT pg_drop_replication_slot('ps')"); + reset_table(); } # hot_standby_feedback without a slot: held by the walsender proc on the -# primary, reported as standby feedback rather than a slot. +# primary, so named by the walsender PID, noting the xact runs on the standby. { $node->backup('backup2'); my $standby = PostgreSQL::Test::Cluster->new('standby_fb'); @@ -149,14 +216,18 @@ wal_receiver_status_interval = 1 $node->poll_query_until('postgres', qq{ SELECT backend_xmin IS NOT NULL FROM pg_stat_replication; }) or die "timed out waiting for standby feedback xmin"; + my $wspid = $node->safe_psql('postgres', + "SELECT pid FROM pg_stat_replication LIMIT 1"); - my $log = vacuum_verbose(); - like($log, qr/oldest xmin held back by: standby feedback/, - 'blocker reported: standby feedback'); + my $log = vacuum_far_in_past(); + like($log, + qr/held back by: standby feedback \(walsender pid $wspid; the transaction runs on the standby\)/, + 'blocker reported: standby feedback with walsender PID'); $bg->query_safe("ROLLBACK"); $bg->quit; $standby->stop; + reset_table(); } $node->stop; diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 95284065e19..6fbe57051c2 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -1904,6 +1904,7 @@ OldMultiXactReader OldToNewMapping OldToNewMappingData OldestXminBlocker +OldestXminBlockerInfo OnCommitAction OnCommitItem OnConflictAction -- 2.47.3
