Hi,
On Tue, Sep 29, 2026 at 11:28 AM surya poondla <[email protected]> wrote:
>
> Andres's 2023 sketch [1], with one row per database plus a NULL-datname row
> for the
> system-wide horizons.
> Tom described the other one [2]: *all* the active processes and their related
> xmins, then more entries for all active replication slots, prepared
> xacts, etc etc. Picking out the ones causing trouble is then the user's
> concern.
>
> [1]
> https://www.postgresql.org/message-id/[email protected]
> [2] https://www.postgresql.org/message-id/[email protected]
I have been looking at this. I tend to agree with Tom's thoughts, that
is, core emits all the processes, slots, prepared xacts etc. along
with their xmins, and the summary views could be built by the users.
I'm sure the users will need different flavors of the info such as
what is stopping vacuum per-database and per-cluster, top N blockers,
or, if the oldest blocker goes away, how far the xmin horizon moves,
those only blocking the catalog xmin horizon etc. etc., and they may
even join it with other existing views to get a nice readable format
or such. And, I don't mind optionally having a couple of common
summary SQL views on top of this in core.
I looked at v10 again and it is mostly on the above lines. However, I
think the patch is unnecessarily big at ~1480 LoC (for example, the
tests don't have to be that long, the docs seem to be overly
explaining things, and some code is unnecessarily complicated). And,
the columns it emits seem to be too confusing, there are just too many
xmins (shared, catalog and data horizon).
I think we can simplify it a lot. How about something like the
attached? I could bring it down to ~450 LoC with simplified docs, code
and tests (for the same coverage). For example, the output with this
patch is at [1] vs v10 is at [2], and one can build a summary view
such as [3].
[1]
```
SELECT * FROM pg_xmin_horizon ORDER BY xmin;
kind | pid | slot_name | gid | datid | xmin
-------------------+-------+-----------+--------+-------+------
slot_catalog_xmin | | sub_slot | | | 669
slot_catalog_xmin | | standby1 | | | 677
backend | 12122 | | | 16384 | 679
slot_xmin | | standby1 | | | 681
standby_feedback | 12060 | | | | 683
backend | 12165 | | | 5 | 685
backend | 12189 | | | 5 | 686
prepared_xact | | | order1 | 16384 | 686
backend | 12174 | | | 16384 | 687
(9 rows)
```
[2]
```
SELECT * FROM pg_xmin_horizon ORDER BY age(shared_xmin) DESC NULLS LAST;
kind | pid | slot_name | gid | datid | datname |
xid | shared_xmin | catalog_xmin | data_xmin | xact_start
------------------+-------+-----------+--------+-------+----------+-----+-------------+--------------+-----------+-------------------------------
replication_slot | | sub_slot | | 16384 | db1 |
| 669 | 669 | |
replication_slot | | standby1 | | | |
| 677 | 677 | 681 |
backend | 11880 | | | 16384 | db1 |
| 679 | 679 | 679 | 2026-11-16
11:35:15.71561-08
standby_feedback | 11818 | | | | |
| 683 | 683 | 683 |
backend | 11923 | | | 5 | postgres |
| 685 | 685 | 685 | 2026-11-16
11:35:17.323817-08
backend | 11947 | | | 5 | postgres |
| 686 | 686 | 686 | 2026-11-16
11:35:24.383231-08
prepared_xact | | | order1 | 16384 | db1 |
686 | 686 | 686 | 686 | 2026-11-16
11:35:17.834223-08
backend | 11932 | | | 16384 | db1 |
687 | 687 | 687 | 687 | 2026-11-16
11:35:17.839764-08
(8 rows)
```
[3]
```
-- Show what is stopping vacuum per-database i.e. the oldest blocker.
SELECT DISTINCT ON (d.datname) d.datname, x.kind, x.pid, x.slot_name,
x.gid, x.xmin
FROM pg_database d
JOIN pg_xmin_horizon x
ON x.datid = d.oid OR x.kind NOT IN ('backend',
'prepared_xact', 'slot_catalog_xmin')
ORDER BY d.datname, x.xmin;
datname | kind | pid | slot_name | gid | xmin
-----------+-----------+-------+-----------+-----+------
db1 | backend | 12122 | | | 679
postgres | slot_xmin | | standby1 | | 681
template0 | slot_xmin | | standby1 | | 681
template1 | slot_xmin | | standby1 | | 681
(4 rows)
```
--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com
From 374cbbeffcd756ee5cc0bb857b9f6c986b68bd8e Mon Sep 17 00:00:00 2001
From: Scott Ray <[email protected]>
Date: Thu, 12 Nov 2026 21:34:15 -0800
Subject: [PATCH v11] Add pg_xmin_horizon system view.
Previously, one had to look at pg_stat_activity,
pg_prepared_xacts, pg_replication_slots and pg_stat_replication
separately to find what holds back the xmin horizon.
This commit adds a view with one row for each process, slot,
prepared xact etc. that holds it back, along with its xmin, the
oldest (full) transaction ID it needs the database to keep. The
kind column names the holder, one of backend, prepared_xact,
slot_xmin, slot_catalog_xmin, standby_feedback or primary_xact.
A slot gets one row for its xmin and another for its
catalog_xmin. A standby_feedback row is a WAL sender that holds
the horizon for a standby without a slot. A primary_xact row is a
transaction ID still running on the primary, listed only on a
standby.
All kinds hold back the shared catalogs. A backend or
prepared_xact also holds back the non-shared catalogs and user
tables of its own database, shown in datid. A slot_catalog_xmin
holds back the non-shared catalogs of every database. The other
kinds hold back the non-shared catalogs and user tables of every
database.
Bump catalog version.
Author: Scott Ray <[email protected]>
Discussion: https://postgr.es/m/AT-erSWn0vFw8TYLb84jqyfxgTV21gjwEo4YJDcses8qWtDkQK_pS6g2OIfVh4-L1PPn94w8-tE1mHZactVAgxPJ8vL96MXsyAnUg2Xz-Ks=@scottray.io
---
doc/src/sgml/maintenance.sgml | 4 +
doc/src/sgml/system-views.sgml | 140 +++++++++++++++++
src/backend/access/transam/twophase.c | 25 +++
src/backend/catalog/system_views.sql | 6 +
src/backend/storage/ipc/procarray.c | 198 ++++++++++++++++++++++--
src/include/access/twophase.h | 1 +
src/include/catalog/pg_proc.dat | 10 ++
src/test/recovery/meson.build | 1 +
src/test/recovery/t/061_xmin_horizon.pl | 61 ++++++++
src/test/regress/expected/rules.out | 7 +
src/tools/pgindent/typedefs.list | 2 +
11 files changed, 446 insertions(+), 9 deletions(-)
create mode 100644 src/test/recovery/t/061_xmin_horizon.pl
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index e3f5cedd589..574240ca47a 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -746,6 +746,10 @@ HINT: Execute a database-wide VACUUM in that database.
in the target database in order to avoid future problems.</simpara>
</listitem>
</orderedlist>
+
+ Alternatively, use the
+ <link linkend="view-pg-xmin-horizon"><structname>pg_xmin_horizon</structname></link>
+ view to look at what is holding back the xmin horizon.
</para>
<note>
diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 77202e2c765..be1a22759f1 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -246,6 +246,11 @@
<entry>wait events</entry>
</row>
+ <row>
+ <entry><link linkend="view-pg-xmin-horizon"><structname>pg_xmin_horizon</structname></link></entry>
+ <entry>transactions and replication slots holding back the xmin horizon</entry>
+ </row>
+
</tbody>
</tgroup>
</table>
@@ -5783,4 +5788,139 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
</table>
</sect1>
+ <sect1 id="view-pg-xmin-horizon">
+ <title><structname>pg_xmin_horizon</structname></title>
+
+ <indexterm zone="view-pg-xmin-horizon">
+ <primary>pg_xmin_horizon</primary>
+ </indexterm>
+
+ <para>
+ The view <structname>pg_xmin_horizon</structname> displays everything
+ that holds back the <literal>xmin</literal> horizon and so prevents
+ <command>VACUUM</command> from removing dead row versions and freezing
+ transaction IDs. It contains one row per backend holding a snapshot or
+ transaction ID, per prepared transaction, per <literal>xmin</literal> and
+ <literal>catalog_xmin</literal> of a replication slot, and per WAL sender
+ process holding the <literal>xmin</literal> horizon reported by its
+ standby through <xref linkend="guc-hot-standby-feedback"/> without a
+ replication slot. On a standby, it also contains one row per transaction
+ ID still running on the primary. A backend running a standard
+ <command>VACUUM</command> is not listed, as it does not hold back the
+ <literal>xmin</literal> horizon.
+ </para>
+
+ <table>
+ <title><structname>pg_xmin_horizon</structname> Columns</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>kind</structfield> <type>text</type>
+ </para>
+ <para>
+ Type of the row:
+ <literal>backend</literal>, <literal>prepared_xact</literal>,
+ <literal>slot_xmin</literal>, <literal>slot_catalog_xmin</literal>,
+ <literal>standby_feedback</literal>, or <literal>primary_xact</literal>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>pid</structfield> <type>int4</type>
+ </para>
+ <para>
+ Process ID of the backend or WAL sender process, or null for other rows
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>slot_name</structfield> <type>name</type>
+ </para>
+ <para>
+ Name of the replication slot, or null for other rows
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>gid</structfield> <type>text</type>
+ </para>
+ <para>
+ Global transaction identifier of the prepared transaction, or null for
+ other rows
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>datid</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-database"><structname>pg_database</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ OID of the database the backend is connected to, or in which the
+ prepared transaction was executed. Null for all other rows.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>xmin</structfield> <type>xid8</type>
+ </para>
+ <para>
+ The oldest transaction that this row needs the database to retain.
+ <literal>VACUUM</literal> cannot remove tuples deleted by any later
+ transaction. For a backend, this is the older of its transaction ID
+ and the <literal>xmin</literal> of its snapshot.
+ </para></entry>
+ </row>
+
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ Every row holds back the <literal>xmin</literal> horizon for shared
+ catalogs. A <literal>backend</literal> or
+ <literal>prepared_xact</literal> row also holds it back for the tables of
+ its own database, shown in <structfield>datid</structfield>. All other
+ rows hold it back for the tables of all databases, except that a
+ <literal>slot_catalog_xmin</literal> row holds it back for system
+ catalogs only. A replication slot row has no
+ <structfield>datid</structfield>, because a slot holds back all
+ databases, also when it is a logical slot that belongs to one database.
+ </para>
+
+ <para>
+ On a standby, a <literal>backend</literal> row also holds back the
+ <literal>xmin</literal> horizon for the tables of all databases, whatever
+ its <structfield>datid</structfield>. A <literal>primary_xact</literal>
+ row is a transaction ID still running on the primary, possibly of a
+ subtransaction. The standby does not know its process or database, so
+ query <structname>pg_xmin_horizon</structname> on the primary to find the
+ transaction. Likewise, query it on a standby to find what is behind a
+ <literal>standby_feedback</literal> row or the rows of the replication
+ slot used by that standby.
+ </para>
+
+ <para>
+ By default, the <structname>pg_xmin_horizon</structname> view can be read
+ only by superusers or roles with privileges of the
+ <literal>pg_read_all_stats</literal> role.
+ </para>
+ </sect1>
+
</chapter>
diff --git a/src/backend/access/transam/twophase.c b/src/backend/access/transam/twophase.c
index d89afb09320..1876f4f50d4 100644
--- a/src/backend/access/transam/twophase.c
+++ b/src/backend/access/transam/twophase.c
@@ -901,6 +901,31 @@ TwoPhaseGetXidByVirtualXID(VirtualTransactionId vxid,
return result;
}
+/*
+ * Get the GID of the prepared xact with the given XID, if any.
+ */
+bool
+TwoPhaseGetGid(TransactionId xid, char *gid)
+{
+ bool found = false;
+
+ LWLockAcquire(TwoPhaseStateLock, LW_SHARED);
+ for (int i = 0; i < TwoPhaseState->numPrepXacts; i++)
+ {
+ GlobalTransaction gxact = TwoPhaseState->prepXacts[i];
+
+ if (gxact->valid && XidFromFullTransactionId(gxact->fxid) == xid)
+ {
+ strlcpy(gid, gxact->gid, GIDSIZE);
+ found = true;
+ break;
+ }
+ }
+ LWLockRelease(TwoPhaseStateLock);
+
+ return found;
+}
+
/*
* TwoPhaseGetDummyProcNumber
* Get the dummy proc number for prepared transaction
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 6dce9192ba0..5d69041b505 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1127,6 +1127,12 @@ CREATE VIEW pg_replication_slots AS
FROM pg_get_replication_slots() AS L
LEFT JOIN pg_database D ON (L.datoid = D.oid);
+CREATE VIEW pg_xmin_horizon AS
+ SELECT * FROM pg_get_xmin_horizon();
+
+REVOKE ALL ON pg_xmin_horizon FROM PUBLIC;
+GRANT SELECT ON pg_xmin_horizon TO pg_read_all_stats;
+
CREATE VIEW pg_stat_replication_slots AS
SELECT
s.slot_name,
diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c
index 230663d1352..9dede92c602 100644
--- a/src/backend/storage/ipc/procarray.c
+++ b/src/backend/storage/ipc/procarray.c
@@ -54,10 +54,12 @@
#include "access/xlogutils.h"
#include "catalog/catalog.h"
#include "catalog/pg_authid.h"
+#include "funcapi.h"
#include "miscadmin.h"
#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"
@@ -69,6 +71,7 @@
#include "utils/rel.h"
#include "utils/snapmgr.h"
#include "utils/wait_event.h"
+#include "utils/xid8.h"
#define UINT32_ACCESS_ONCE(var) ((uint32)(*((volatile uint32 *)&(var))))
@@ -260,6 +263,28 @@ typedef struct ComputeXidHorizonsResult
TransactionId temp_oldest_nonremovable;
} ComputeXidHorizonsResult;
+/*
+ * A backend or prepared xact that holds back the horizons.
+ */
+typedef struct XidHorizonProc
+{
+ int pid; /* 0 for a prepared xact */
+ Oid databaseId;
+ bool affectsAllHorizons; /* counts for all databases */
+ TransactionId xmin; /* older of xid and xmin */
+} XidHorizonProc;
+
+/*
+ * Inputs to the horizon computation.
+ */
+typedef struct XidHorizonInputs
+{
+ XidHorizonProc *procs;
+ int nprocs;
+ TransactionId *kaxids; /* xids running on the primary */
+ int nkaxids;
+} XidHorizonInputs;
+
/*
* Return value for GlobalVisHorizonKindForRel().
*/
@@ -374,7 +399,8 @@ static int KnownAssignedXidsGet(TransactionId *xarray, TransactionId xmax);
static int KnownAssignedXidsGetAndSetXmin(TransactionId *xarray,
TransactionId *xmin,
TransactionId xmax);
-static TransactionId KnownAssignedXidsGetOldestXmin(void);
+static TransactionId KnownAssignedXidsGetOldestXmin(TransactionId *xarray,
+ int *nxids);
static void KnownAssignedXidsDisplay(int trace_level);
static void KnownAssignedXidsReset(void);
static inline void ProcArrayEndTransactionInternal(PGPROC *proc, TransactionId latestXid);
@@ -1669,9 +1695,12 @@ TransactionIdIsInProgress(TransactionId xid)
* heap vacuum's call to heap_page_prune_and_freeze() uses a more conservative
* horizon than later when deciding which tuples can be removed - which the
* code doesn't expect (breaking HOT).
+ *
+ * If inputs is not NULL, the inputs to the computation are returned there.
*/
-static void
-ComputeXidHorizons(ComputeXidHorizonsResult *h)
+static pg_always_inline void
+ComputeXidHorizonsInternal(ComputeXidHorizonsResult *h,
+ XidHorizonInputs *inputs)
{
ProcArrayStruct *arrayP = procArray;
TransactionId kaxmin;
@@ -1770,6 +1799,17 @@ ComputeXidHorizons(ComputeXidHorizonsResult *h)
if (statusFlags & (PROC_IN_VACUUM | PROC_IN_LOGICAL_DECODING))
continue;
+ if (inputs != NULL)
+ {
+ XidHorizonProc *p = &inputs->procs[inputs->nprocs++];
+
+ p->pid = proc->pid;
+ p->databaseId = proc->databaseId;
+ p->affectsAllHorizons =
+ (statusFlags & PROC_AFFECTS_ALL_HORIZONS) != 0;
+ p->xmin = xmin;
+ }
+
/* shared tables need to take backends in all databases into account */
h->shared_oldest_nonremovable =
TransactionIdOlder(h->shared_oldest_nonremovable, xmin);
@@ -1808,7 +1848,15 @@ ComputeXidHorizons(ComputeXidHorizonsResult *h)
* after lock is released.
*/
if (in_recovery)
- kaxmin = KnownAssignedXidsGetOldestXmin();
+ {
+ int nkaxids;
+
+ kaxmin = KnownAssignedXidsGetOldestXmin(inputs != NULL ?
+ inputs->kaxids : NULL,
+ &nkaxids);
+ if (inputs != NULL)
+ inputs->nkaxids = nkaxids;
+ }
/*
* No other information from shared state is needed, release the lock
@@ -1897,6 +1945,12 @@ ComputeXidHorizons(ComputeXidHorizonsResult *h)
Assert(!TransactionIdIsValid(h->slot_catalog_xmin) ||
TransactionIdPrecedesOrEquals(h->oldest_considered_running,
h->slot_catalog_xmin));
+}
+
+static void
+ComputeXidHorizons(ComputeXidHorizonsResult *h)
+{
+ ComputeXidHorizonsInternal(h, NULL);
/* update approximate horizons with the computed horizons */
GlobalVisUpdateApply(h);
@@ -1999,6 +2053,119 @@ GetReplicationHorizons(TransactionId *xmin, TransactionId *catalog_xmin)
*catalog_xmin = horizons.slot_catalog_xmin;
}
+#define PG_GET_XMIN_HORIZON_COLS 6
+
+/*
+ * Add one row to the result set.
+ */
+static void
+xmin_horizon_put_row(ReturnSetInfo *rsinfo, FullTransactionId rel,
+ const char *kind, int pid, NameData *slot_name,
+ const char *gid, Oid datid, TransactionId xmin)
+{
+ Datum values[PG_GET_XMIN_HORIZON_COLS] = {0};
+ bool nulls[PG_GET_XMIN_HORIZON_COLS] = {0};
+
+ values[0] = CStringGetTextDatum(kind);
+ values[1] = Int32GetDatum(pid);
+ nulls[1] = (pid == 0);
+ if (slot_name != NULL)
+ values[2] = NameGetDatum(slot_name);
+ else
+ nulls[2] = true;
+ if (gid != NULL)
+ values[3] = CStringGetTextDatum(gid);
+ else
+ nulls[3] = true;
+ values[4] = ObjectIdGetDatum(datid);
+ nulls[4] = !OidIsValid(datid);
+ values[5] = FullTransactionIdGetDatum(FullXidRelativeTo(rel, xmin));
+
+ tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
+}
+
+/*
+ * SQL function listing everything that holds back the xmin horizon.
+ */
+Datum
+pg_get_xmin_horizon(PG_FUNCTION_ARGS)
+{
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ ComputeXidHorizonsResult h;
+ XidHorizonInputs inputs = {0};
+
+ InitMaterializedSRF(fcinfo, 0);
+
+ inputs.procs = palloc_array(XidHorizonProc, procArray->maxProcs);
+ if (RecoveryInProgress())
+ inputs.kaxids = palloc_array(TransactionId, TOTAL_MAX_CACHED_SUBXIDS);
+
+ ComputeXidHorizonsInternal(&h, &inputs);
+
+ for (int i = 0; i < inputs.nprocs; i++)
+ {
+ XidHorizonProc *p = &inputs.procs[i];
+
+ if (p->pid == 0)
+ {
+ char gid[GIDSIZE];
+ bool found;
+
+ /* The prepared xact may have finished by now. */
+ found = TwoPhaseGetGid(p->xmin, gid);
+ xmin_horizon_put_row(rsinfo, h.latest_completed, "prepared_xact",
+ 0, NULL, found ? gid : NULL, p->databaseId,
+ p->xmin);
+ }
+ else if (p->affectsAllHorizons)
+ xmin_horizon_put_row(rsinfo, h.latest_completed,
+ "standby_feedback", p->pid, NULL, NULL,
+ InvalidOid, p->xmin);
+ else
+ xmin_horizon_put_row(rsinfo, h.latest_completed, "backend",
+ p->pid, NULL, NULL, p->databaseId, p->xmin);
+ }
+
+ for (int i = 0; i < inputs.nkaxids; i++)
+ xmin_horizon_put_row(rsinfo, h.latest_completed, "primary_xact", 0,
+ NULL, NULL, InvalidOid, inputs.kaxids[i]);
+
+ LWLockAcquire(ReplicationSlotControlLock, LW_SHARED);
+ for (int i = 0; i < max_replication_slots + max_repack_replication_slots; i++)
+ {
+ ReplicationSlot *s = &ReplicationSlotCtl->replication_slots[i];
+ TransactionId xmin;
+ TransactionId catalog_xmin;
+ NameData name;
+ bool invalidated;
+
+ if (!s->in_use)
+ continue;
+
+ SpinLockAcquire(&s->mutex);
+ xmin = s->effective_xmin;
+ catalog_xmin = s->effective_catalog_xmin;
+ invalidated = (s->data.invalidated != RS_INVAL_NONE);
+ name = s->data.name;
+ SpinLockRelease(&s->mutex);
+
+ /* Invalidated slots do not hold back the horizon. */
+ if (invalidated)
+ continue;
+
+ if (TransactionIdIsValid(xmin))
+ xmin_horizon_put_row(rsinfo, h.latest_completed, "slot_xmin", 0,
+ &name, NULL, InvalidOid, xmin);
+ if (TransactionIdIsValid(catalog_xmin))
+ xmin_horizon_put_row(rsinfo, h.latest_completed,
+ "slot_catalog_xmin", 0, &name, NULL,
+ InvalidOid, catalog_xmin);
+ }
+ LWLockRelease(ReplicationSlotControlLock);
+
+ return (Datum) 0;
+}
+
/*
* GetMaxSnapshotXidCount -- get max size for snapshot XID array
*
@@ -5239,11 +5406,13 @@ KnownAssignedXidsGetAndSetXmin(TransactionId *xarray, TransactionId *xmin,
/*
* Get oldest XID in the KnownAssignedXids array, or InvalidTransactionId
- * if nothing there.
+ * if nothing there. Optionally, also return all the XIDs and their count.
*/
static TransactionId
-KnownAssignedXidsGetOldestXmin(void)
+KnownAssignedXidsGetOldestXmin(TransactionId *xarray, int *nxids)
{
+ TransactionId oldest = InvalidTransactionId;
+ int count = 0;
int head,
tail;
int i;
@@ -5259,11 +5428,22 @@ KnownAssignedXidsGetOldestXmin(void)
for (i = tail; i < head; i++)
{
/* Skip any gaps in the array */
- if (KnownAssignedXidsValid[i])
- return KnownAssignedXids[i];
+ if (!KnownAssignedXidsValid[i])
+ continue;
+
+ /* The array is sorted, so the first XID found is the oldest */
+ if (!TransactionIdIsValid(oldest))
+ oldest = KnownAssignedXids[i];
+
+ if (xarray == NULL)
+ break;
+
+ xarray[count++] = KnownAssignedXids[i];
}
- return InvalidTransactionId;
+ *nxids = count;
+
+ return oldest;
}
/*
diff --git a/src/include/access/twophase.h b/src/include/access/twophase.h
index 1d2ff42c9b7..291b5caa883 100644
--- a/src/include/access/twophase.h
+++ b/src/include/access/twophase.h
@@ -40,6 +40,7 @@ extern TransactionId TwoPhaseGetXidByVirtualXID(VirtualTransactionId vxid,
bool *have_more);
extern PGPROC *TwoPhaseGetDummyProc(FullTransactionId fxid, bool lock_held);
extern int TwoPhaseGetDummyProcNumber(FullTransactionId fxid, bool lock_held);
+extern bool TwoPhaseGetGid(TransactionId xid, char *gid);
extern GlobalTransaction MarkAsPreparing(FullTransactionId fxid, const char *gid,
TimestampTz prepared_at,
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 50fb182cb79..cd03681d42b 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -11739,6 +11739,16 @@
proargmodes => '{o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}',
proargnames => '{slot_name,plugin,slot_type,datoid,temporary,active,active_pid,xmin,catalog_xmin,restart_lsn,confirmed_flush_lsn,wal_status,safe_wal_size,two_phase,two_phase_at,inactive_since,conflicting,invalidation_reason,failover,synced,slotsync_skip_reason}',
prosrc => 'pg_get_replication_slots' },
+{ oid => '8054',
+ descr => 'transactions and replication slots holding back the xmin horizon',
+ proname => 'pg_get_xmin_horizon', prorows => '100', proretset => 't',
+ provolatile => 'v', proparallel => 'r', prorettype => 'record',
+ proargtypes => '',
+ proallargtypes => '{text,int4,name,text,oid,xid8}',
+ proargmodes => '{o,o,o,o,o,o}',
+ proargnames => '{kind,pid,slot_name,gid,datid,xmin}',
+ prosrc => 'pg_get_xmin_horizon',
+ proacl => '{POSTGRES=X,pg_read_all_stats=X}' },
{ oid => '3786', descr => 'set up a logical replication slot',
proname => 'pg_create_logical_replication_slot', provolatile => 'v',
proparallel => 'u', prorettype => 'record',
diff --git a/src/test/recovery/meson.build b/src/test/recovery/meson.build
index b6ac9dd07a7..9f179bfb435 100644
--- a/src/test/recovery/meson.build
+++ b/src/test/recovery/meson.build
@@ -69,6 +69,7 @@ tests += {
't/058_shutdown_crash_restart.pl',
't/059_remote_apply_status_interval.pl',
't/060_multixact_crashed_updater.pl',
+ 't/061_xmin_horizon.pl',
],
},
}
diff --git a/src/test/recovery/t/061_xmin_horizon.pl b/src/test/recovery/t/061_xmin_horizon.pl
new file mode 100644
index 00000000000..8d0045788ee
--- /dev/null
+++ b/src/test/recovery/t/061_xmin_horizon.pl
@@ -0,0 +1,61 @@
+# Copyright (c) 2026, PostgreSQL Global Development Group
+
+# Test that pg_xmin_horizon lists every xmin holder, on primary and standby.
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Cluster;
+use Test::More;
+
+# Autovacuum would add holders of its own.
+my $primary = PostgreSQL::Test::Cluster->new('primary');
+$primary->init(allows_streaming => 'logical');
+$primary->append_conf('postgresql.conf',
+ "autovacuum = off\nmax_prepared_transactions = 1");
+$primary->start;
+$primary->backup('bkp');
+
+# Without a slot, the standby's feedback is held by the WAL sender.
+my $standby = PostgreSQL::Test::Cluster->new('standby');
+$standby->init_from_backup($primary, 'bkp', has_streaming => 1);
+$standby->append_conf('postgresql.conf',
+ "hot_standby_feedback = on\nwal_receiver_status_interval = 1s");
+$standby->start;
+my $snapshot = 'BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT 1';
+my $standby_query = $standby->background_psql('postgres');
+$standby_query->query_safe($snapshot);
+
+# The slot goes first, as its creation waits for running transactions.
+$primary->safe_psql('postgres',
+ "SELECT pg_create_logical_replication_slot('logi', 'pgoutput')");
+my $primary_query = $primary->background_psql('postgres');
+$primary_query->query_safe($snapshot);
+
+# PREPARE also flushes the message, so the standby sees both transactions.
+my $primary_xact = $primary->background_psql('postgres');
+$primary_xact->query_safe(
+ "BEGIN; SELECT pg_logical_emit_message(true, 'test', '')");
+$primary->safe_psql('postgres', "BEGIN; PREPARE TRANSACTION 'p1'");
+$primary->wait_for_replay_catchup($standby);
+
+# pid and XIDs vary between runs. 5 is the OID of postgres.
+my $holders = q{SELECT kind, slot_name, gid, datid FROM pg_xmin_horizon
+ WHERE pid IS DISTINCT FROM pg_backend_pid() ORDER BY 1, 2, 3};
+
+# The feedback arrives asynchronously.
+ok( $primary->poll_query_until(
+ 'postgres', $holders, qq(backend|||5
+backend|||5
+prepared_xact||p1|5
+slot_catalog_xmin|logi||
+standby_feedback|||)),
+ 'holders on primary');
+is( $standby->safe_psql('postgres', $holders),
+ qq(backend|||5
+primary_xact|||
+primary_xact|||),
+ 'holders on standby');
+
+$standby_query->quit;
+$primary_query->quit;
+$primary_xact->quit;
+done_testing();
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index acbe4667d89..6e1bac19a94 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2913,6 +2913,13 @@ pg_wait_events| SELECT type,
name,
description
FROM pg_get_wait_events() pg_get_wait_events(type, name, description);
+pg_xmin_horizon| SELECT kind,
+ pid,
+ slot_name,
+ gid,
+ datid,
+ xmin
+ FROM pg_get_xmin_horizon() pg_get_xmin_horizon(kind, pid, slot_name, gid, datid, xmin);
SELECT tablename, rulename, definition FROM pg_rules
WHERE schemaname = 'pg_catalog'
ORDER BY tablename, rulename;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index f10eb469266..ac6c048e347 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3534,6 +3534,8 @@ XactLockTableWaitInfo
XidBoundsViolation
XidCacheStatus
XidCommitStatus
+XidHorizonInputs
+XidHorizonProc
XidStatus
XmlExpr
XmlExprOp
--
2.47.3