pg_xmin_horizon: a system view of everything pinning the xmin horizon

Hi all,

On Thu, Jul 16, 2026 at 3:18 PM Scott Ray <scott@scottray.io> wrote:

Hi,

When VACUUM cannot remove dead tuples, working out what is pinning
the xmin horizon means querying pg_stat_activity,
pg_replication_slots, pg_prepared_xacts, and pg_stat_replication,
and combining them correctly. A hand-rolled UNION can only
approximate, because the xmin classification (shared/catalog/data)
and the slots' effective xmins computed by ComputeXidHorizons() and
ReplicationSlotsComputeRequiredXmin() are not exposed anywhere.
Users who don't know all four sources exist sometimes fail to
identify the holder at all [1]. Commercial tooling, including
pganalyze's xmin-horizon check and RDS's postgres_get_av_diag(),
exists in part because this surface is missing from core. It is
also on Andres's wishlist: "Today it takes a lot of knowledge to
find all potential sources of holding back the xmin horizon. We
should have a view showing all the sources of the horizon being
held back." [2]

The attached patch adds a pg_xmin_horizon system view, readable by
pg_read_all_stats, with one row per horizon input:

- kind: 'backend', 'replication_slot', 'prepared_xact', or
'standby_feedback' (a slot-less hot-standby-feedback walsender)
- identity: pid, slot_name, gid (joined from pg_prepared_xacts),
datid, xact_start
- contribution: shared_xmin, catalog_xmin, data_xmin (the row's raw
per-class pin)

Here's sample output from a scenario in which an old prepared
transaction pins the horizon and blocks two logical slots. It also
includes an idle physical slot.

kind | pid | slot_name | gid | datid | shared_xmin | catalog_xmin | data_xmin | xact_start
------------------+-------+---------------+-----------------+-------+-------------+--------------+-----------+-------------------------------
backend | 33810 | | | 5 | 697 | 697 | 697 | 2026-07-07 14:28:05.846981-07
prepared_xact | | | orders_batch_42 | 5 | 697 | 697 | 697 | 2026-07-07 14:27:28.579493-07
replication_slot | | cdc_export | | 5 | 697 | 697 | 697 |
replication_slot | | standby1_slot | | | | | |
replication_slot | | cdc_slot | | 5 | 697 | 697 | |

The prepared transaction owns xid 697, and the backend row, which is
the querying session, shows the same xmin. The idle physical slot
pins nothing. The cdc_export row demonstrates a gap in
pg_replication_slots that this view fills: The cdc_export can't
obtain a snapshot until the prepared transaction resolves, so
although its effective_xmin is set, data.xmin is still invalid, and
pg_replication_slots shows this:

slot_name | xmin | catalog_xmin
---------------+------+--------------
cdc_export | | 697
cdc_slot | | 697
standby1_slot | |

A view like this one was discussed on the "Report oldest xmin source
when autovacuum cannot remove tuples" thread [3], where Shinya's
CF 6188 patches add a VACUUM log line naming the single
highest-priority blocker. The two are complementary, and several
people there (Shinya, Sami, Kyotaro) have anticipated a view like
this. The log line is the durable post-hoc record for one vacuum,
and the view is the live, complete picture. The view shows every
horizon input, including non-binding ones, with the raw values
needed to answer "and what would the horizon become if I removed
this one?" (leave-one-out).

Design notes:

1. The view includes all procs that could plausibly pin something,
including ones that pin nothing, like VACUUM backends,
logical-decoding backends, and idle or invalidated slots. There
is no built-in ranking.

2. A backend row's catalog_xmin and data_xmin apply within its
datid, and its shared_xmin applies cluster-wide. A slot or
standby_feedback row applies cluster-wide. The docs explain
datid scoping.

3. Procs are captured in one ProcArrayLock SHARED pass, and slots
are captured in a separate pass holding
ReplicationSlotControlLock and per-slot spinlocks. Holding both
would risk increasing ProcArrayLock contention. It would also
introduce a new locking pattern, and with it, the danger that a
future patch could acquire the locks in the opposite order and
cause deadlocks. The trade-off is that this view does not
guarantee cross-consistency.

4. The view shows the effective xmins for slots, which is what
ReplicationSlotsComputeRequiredXmin() aggregates, and shows
invalidated slots with NULL xmin columns. This aligns with two
CF 6188 fixes in v7.

5. If queried on a standby, the SRF reports an error.
KnownAssignedXids affect the standby's horizon but are not
available in the procarray. The error prevents providing
incomplete data that might be interpreted as complete. This
aligns with an Assert and comment in CF 6188 v8. A follow-up
patch could add a synthetic row with kind = 'known_assigned_xids'
to fill the gap and remove this error.

6. The patch includes an SQL recipe to compute leave-one-out. Given
the raw xmin values, it isn't always easy to predict how much
the xmin horizon would advance if a row were removed, even if it
has the lowest xmin. If another row's xmin is slightly higher,
then it might move just a little, or it might not move at all if
multiple rows share the lowest xmin. The recipe helps users plan
how to fix problems.

7. The classification logic mirrors ComputeXidHorizons' logic, and
if they are not kept synchronized, the view will lose fidelity.
The patch adds comments to warn future patch authors against
updating one without also updating the other.

8. I expect the CF 6188 patch will land first. We discussed on that
thread the possibility that the log and the view could use a
shared helper function, but whether this happens and when is
TBD.

Tests: a recovery TAP test covers the replication_slot and
standby_feedback rows, the recovery error, tied xmins via an
exported snapshot, and the all-NULL VACUUM backend row; a
regression test covers the view's ACL, backend rows, and the
prepared-xact row. There's an alternate expected file for
installcheck with max_prepared_transactions < 2.

The patch applies to master as of 8f7af125e03.

[1] https://trigger.dev/blog/stopping-xmin-horizon-blocking-postgres-vacuuming
[2] https://wiki.postgresql.org/wiki/User:Andresfreund/Desired_Changes
[3] /messages/by-id/CAOzEurSgy-gDtwFmEbj5+R9PL0_G3qYB6nnzJtNStyuf87VSVg@mail.gmail.com

Thank you for the patch. I reviewed and tested this patch on my tree.
I performed runtime testing of the new pg_xmin_horizon system view
covering different xmin horizon contributors with the following test
cases:

1. Verified that the new pg_xmin_horizon view is created and
accessible after applying the patch.
2. Tested backend transactions by creating long-running transactions
and confirmed that backend contributors are reported with the expected
shared_xmin, catalog_xmin, and data_xmin values.
3. Tested prepared transactions by enabling max_prepared_transactions,
creating prepared transactions using PREPARE TRANSACTION, and verified
that prepared_xact entries appear in pg_xmin_horizon and are removed
immediately after COMMIT PREPARED.
4. Tested physical and logical replication slots. Verified that
replication slot entries appear correctly in pg_xmin_horizon, and that
the reported horizon values are consistent with pg_replication_slots.
And for logical replication slots, I also generated WAL activity and
revalidated the reported horizon values.
5. Compared the information reported by pg_xmin_horizon with the
corresponding information from pg_stat_activity, pg_prepared_xacts,
and pg_replication_slots, and found the reported values to be
consistent.
6. Verified a scenario with multiple simultaneous contributors
(backend transaction, prepared transaction, and logical replication
slot). The view correctly displayed one row for each contributor,
providing a consolidated view of the xmin horizon state.
7. Verified cleanup by committing prepared transactions and dropping
replication slots, confirming that the corresponding entries were
removed from pg_xmin_horizon as expected.

During testing, I also observed that creating a logical replication
slot while an unresolved prepared transaction existed caused
pg_create_logical_replication_slot() to wait until the prepared
transaction was resolved. After completing the prepared transaction,
logical slot creation was completed successfully.
I would like to suggest that it may also be worth considering exposing
the database name (datname) in addition to datid for better
observability and easier interpretation by DBAs as a future
enhancement, although the current implementation is functionally
correct.
Based on my testing, the new view behaved as expected and seems good
as it simplifies the inspection of xmin horizon contributors by
consolidating information that would otherwise require querying
multiple system catalogs. Overall, the patch looks good to me.

Regards,
Solai

On Thursday, July 16th, 2026 at 11:56 PM, solai v <solai.cdac@gmail.com> wrote:

I would like to suggest that it may also be worth considering exposing
the database name (datname) in addition to datid for better
observability and easier interpretation by DBAs as a future
enhancement, although the current implementation is functionally
correct.

Thanks for testing the patch.

Adding datname is a good idea and follows the precedent of
pg_stat_activity, pg_replication_slots, and pg_prepared_xacts. The
attached v2 adds datname.

--
Scott Ray

I rebased on 4e68b7e291d. Upstream took TAP 055, so the new TAP is now 056.

--
Scott Ray

Hi all,

On Mon, Aug 3, 2026 at 5:44 AM Scott Ray <scott@scottray.io> wrote:

I rebased on 4e68b7e291d. Upstream took TAP 055, so the new TAP is now 056.

Thank you for addressing my suggestion. And thank you for the updated
patch. I reviewed and tested the v3 patch, including the newly added
datname column. The patch applied cleanly and built successfully on my
tree and verified that the datname column is now included in the
pg_xmin_horizon view definition and is correctly populated for backend
and prepared transaction entries. The reported values were consistent
with the corresponding database information, and the addition improves
the readability of the view by avoiding an extra join to pg_database.
The new column also aligns well with the existing interfaces provided
by pg_stat_activity, pg_replication_slots, and pg_prepared_xacts.
The updated patch looks good to me.

Regards,
Solai

On Tuesday, August 4th, 2026 at 2:42 AM, solai v <solai.cdac@gmail.com> wrote:

The updated patch looks good to me.

Thanks for the review and testing.

Attached is v4. It changes documentation and comments only, so the
code is identical to the v3 you tested. The leave-one-out recipe
was too long and didn't handle database scoping properly, so I
removed it. Also, I rewrote and trimmed some comments and
documented two caveats, one about cross-row consistency and the
other about aggregate slot xmin contribution. Patch v4 applies to
master b5978350222.

--
Scott Ray

Hi,

On Sun, Aug 9, 2026 at 8:46 AM Scott Ray <scott@scottray.io> wrote:

On Tuesday, August 4th, 2026 at 2:42 AM, solai v <solai.cdac@gmail.com> wrote:

The updated patch looks good to me.

Thanks for the review and testing.

Attached is v4. It changes documentation and comments only, so the
code is identical to the v3 you tested. The leave-one-out recipe
was too long and didn't handle database scoping properly, so I
removed it. Also, I rewrote and trimmed some comments and
documented two caveats, one about cross-row consistency and the
other about aggregate slot xmin contribution. Patch v4 applies to
master b5978350222.

This looks good.

Regards,
Solai

Hi Scott,

+1 on the feature.
This is a nice thread with some good review comments.
I applied v4, it builds clean with no new warnings.

A few comments:
1. maintenance.sgml and system-views.sgml contradict each other on datid,
and I feel maintenance.sgml is wrong. It says to consider "only
rows whose datid is the affected database or null", but
ReplicationSlotsComputeRequiredXmin() aggregates catalog_xmin over
every in-use, non-invalidated slot with no filter on s->data.database, and
that folds into the shared and
catalog horizons unconditionally. A logical slot on database A is reported
with datid = A, so it can hold back database B's catalog horizon.
system-views.sgml says the opposite and is right: "Slot and
standby_feedback rows pin the horizon cluster-wide regardless of
datid, and a query filtering by datid will exclude them.", maybe we can fix
this.

2. Would a column distinguishing the xid holder from xmin holders be worth
adding?
Since the SRF folds p->xid and p->xmin into one value, a backend that owns
the oldest xid and a backend that merely snapshotted it come out
identical. For example, one idle-in-transaction backend owning the oldest
xid, with twenty other backends whose backend_xmin equals it, produces
twenty-one indistinguishable rows and system-views.sgml then says "When
several rows tie for the oldest value, removing one of them does not
advance the horizon", which I think inverts the advice for that case, since
terminating the single xid owner is what actually helps.

To be clear, I agree with your argument on the other thread that the view
should list every input rather than nominate one.
My only concern is that the role isn't recorded anywhere, so the fact a DBA
most needs is the one the view can't convey.

3. It would be worth adding a test for the case where a slot's
effective_xmin is set while data.xmin is still invalid, since that's
the state pg_replication_slots cannot show. TAP case (3) asserts a logical
slot with catalog_xmin non-null and data_xmin NULL, but
pg_replication_slots reports the same for that slot.

To reach the divergent state we need CREATE_REPLICATION_SLOT ... LOGICAL
<plugin> (SNAPSHOT 'export') over a replication connection;
pg_create_logical_replication_slot() passes need_full_snapshot = false, so
it never pegs effective_xmin.
Conveniently the state is stable and ReplicationSlotRelease() only clears
effective_xmin when the slot is released, so another session can
compare the two views while the replication connection is held open.

A few minor things:
- The recovery errhint() puts the function name inside the format string.
We can use a placeholder to keep the name
untranslated, like how pg_log_standby_snapshot() does.

- The xact_start assertion in the regression test compares the view's
xact_start against pg_stat_activity.xact_start, which is where the view
sources it, so it exercises the join rather than the value.

Regards,
Surya Poondla

Hi Surya,

Thanks for the review.

On Tuesday, August 18th, 2026 at 4:38 PM, surya poondla <suryapoondla4@gmail.com> wrote:

1. maintenance.sgml and system-views.sgml contradict each other on datid, and I feel maintenance.sgml is wrong.

I agree and have revised maintenance.sgml.

2. Would a column distinguishing the xid holder from xmin holders be worth adding?

Yes, and I added an xid column.

3. It would be worth adding a test for the case where a slot's effective_xmin is set while data.xmin is still invalid
...
Conveniently the state is stable and ReplicationSlotRelease() only clears effective_xmin when the slot is released, so another session can
compare the two views while the replication connection is held open.

I added the test, but CreateReplicationSlot() calls
ReplicationSlotRelease() directly, so the state is not stable without
a pre-existing transaction to block slot creation.

- The recovery errhint() puts the function name inside the format string.

I fixed this too.

- The xact_start assertion in the regression test compares the view's
xact_start against pg_stat_activity.xact_start, which is where the view sources it, so it exercises the join rather than the value.

There is no independent source to compare against, so I kept the
assertion just to exercise the join.

In addition, I corrected these errors:

1. The datfrozenxid section recommended filtering by datid, but only
shared_xmin affects datfrozenxid, and shared_xmin operates across
databases.

2. Non-walsender backends not connected to a database only affect the
shared horizon, but v4 emitted their rows with non-null data_xmin and
catalog_xmin columns.

v5 is attached and applies to master f20c4278342.

--
Scott Ray

Hi,

On Sat, Jul 11, 2026 at 5:22 PM Scott Ray <scott@scottray.io> wrote:

When VACUUM cannot remove dead tuples, working out what is pinning
the xmin horizon means querying pg_stat_activity,
pg_replication_slots, pg_prepared_xacts, and pg_stat_replication,
and combining them correctly. A hand-rolled UNION can only
approximate, because the xmin classification (shared/catalog/data)
and the slots' effective xmins computed by ComputeXidHorizons() and
ReplicationSlotsComputeRequiredXmin() are not exposed anywhere.
Users who don't know all four sources exist sometimes fail to
identify the holder at all [1].

Thanks for working on this. I'm trying to understand why we need this
implemented as a C function rather than a query sitting in
system_views.sql.

If effective xmins are emitted via the replication slots view,
wouldn't that solve the issue where slots holding xmins internally are
blocking the xmin horizon and vacuum overall?

Similarly, backends running vacuum and walsenders performing logical
decoding set special proc flags (PROC_IN_VACUUM /
PROC_IN_LOGICAL_DECODING) that cause vacuum to skip their xmins from
horizon computations. Since pg_stat_activity can't distinguish that
reliably today, can we think of adding such flags there?

If there are other missing pieces, why not emit those from the
existing views to make this feature simpler?

Would this make an SQL-only query joining pg_stat_activity,
pg_prepared_xacts, pg_replication_slots, and pg_database enough?

When the vacuum isn't removing dead tuples (bloat increasing, query
performance degrading) or freezing xids (xid warnings firing), the
user wants to know what's blocking. They check datfrozenxid, look for
long-running or idle transactions, prepared transactions, logical
slots holding catalog vacuum back, physical slots holding user table
vacuum back, and act accordingly. An SQL-only query might work well
for covering most cases, and after emitting the effective xmins and
proc flags it will also cover the case reported above.

Thoughts?

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com

Hi Bharath, Scott.

If there are other missing pieces, why not emit those from the

existing views to make this feature simpler?

Yes, the slots' effective xmins, and the vacuum/logical-decoding proc flags are
the parts of the view
that aren't reconstructible, and I think both should be emitted from the
existing views regardless.

For the slot, pg_get_replication_slots() already copies the whole slot
under the spinlock ("slot_contents = *slot"), so effective_xmin and
effective_catalog_xmin are already in hand.
Exposing them is two more output columns, with no new locking and no
additional shared-memory reads.
A slot can be holding back the data horizon while pg_replication_slots.xmin
reads NULL, and there's no way to see that from SQL today. It takes
CREATE_REPLICATION_SLOT ... (SNAPSHOT 'export') to
get there, pg_create_logical_replication_slot() passes need_full_snapshot =
false and never pegs effective_xmin -- so the window is narrow, but it's
exactly the case a CDC tool exporting a snapshot hits.

For the proc flags I have no strong preference, maybe a derived boolean,
like whether this backend's xmin is actually counted toward the horizon.
That avoids putting internal flag names into a user-visible catalog. I am
also fine with PROC_IN_VACUUM / PROC_IN_LOGICAL_DECODING columns too.

Regarding the C code, I have the following points:
1. pg_stat_activity's xid and xmin are not read live.
pgstat_read_current_status()
walks the status slots, calls
ProcNumberGetTransactionIds() for each active backend, and stores the
results in the cached local status table; later calls return early if that
table already exists.
So within a transaction, repeated reads hand back the same xmins no matter
how old they are, whereas pg_get_xmin_horizon() reads the procarray on
every execution.

2. In autocommit the snapshot is rebuilt per statement, so the staleness is
bounded but that rebuild is itself the second problem: it samples the
procarray once per backend, acquiring and releasing ProcArrayLock on each
call, where the SRF copies every proc under a single acquisition. For a
view whose job is to compare xmins across procs and pick out the oldest,
being current and being one instant both seem worth having.

This is specific to backend rows. Slots and prepared transactions are
already read live, pg_get_replication_slots() reads
shared memory directly and pg_prepared_xact() reads TwoPhaseState, so for
those kinds an SQL join would be ok.

A standby's horizon also folds in the oldest xmin from KnownAssignedXids,
which isn't in the procarray and has no SQL exposure, so that piece needs
the C code too, though a small function would do, and v5 errors during
recovery rather than reporting it.

I think the effective xmins on pg_replication_slots and a horizon flag on
pg_stat_activity are worth emitting regardless, since they're useful on
their own. I'd keep C code only for the backend rows, where the single-pass
read buys something SQL can't.

Lastly, one thing that follows either way: every column the SRF returns
comes from its single procarray pass, but the view then joins
pg_stat_activity for xact_start alone, against that cached snapshot rather
than the pass just taken. So a backend the SRF sees can come back with a
null xact_start, or a stale one from an earlier transaction of the same
pid. Better returned from the SRF.

Regards,
Surya Poondla

Hi Bharath,

Thanks for the review.

On Monday, August 24th, 2026 at 9:15 AM, Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com> wrote:

If effective xmins are emitted via the replication slots view,
wouldn't that solve the issue where slots holding xmins internally are
blocking the xmin horizon and vacuum overall?

Yes, and it makes sense as an independent patch.

Similarly, backends running vacuum and walsenders performing logical
decoding set special proc flags (PROC_IN_VACUUM /
PROC_IN_LOGICAL_DECODING) that cause vacuum to skip their xmins from
horizon computations. Since pg_stat_activity can't distinguish that
reliably today, can we think of adding such flags there?

We could. We would also need PROC_AFFECTS_ALL_HORIZONS. Each one
represents an implementation detail that does not belong in a public
API. For example, PROC_IN_LOGICAL_DECODING identifies processes in
logical decoding without any transaction, because ComputeXidHorizons()
must not ignore an open transaction's snapshot and xid. The flag
means whatever the horizon and snapshot machinery needs it to mean, so
it has no independent meaning to publish.

If there are other missing pieces, why not emit those from the
existing views to make this feature simpler?

Would this make an SQL-only query joining pg_stat_activity,
pg_prepared_xacts, pg_replication_slots, and pg_database enough?

That would suffice for most states. Surya has already pointed out
two gaps, one caused by pg_stat_activity caching and the other about
handling KnownAssignedXids during recovery.

It would not make this feature simpler. It would take the irreducible
complexity of the horizon computation and spread it across two
existing views and a new query that would largely transcribe
ComputeXidHorizons() in SQL.

Tom Lane faced a similar choice when designing pg_blocking_pids(),
namely whether to add columns to pg_locks and implement the feature
as a query on top, or to write a dedicated C function, and he
decided the former would be too painful. In the commit message for
52f5d57, he wrote: "Given some more columns in the view than there
are today, you could imagine handling parallel-query cases with a
4-way join; but ugh."

Surya has endorsed some of your suggestions and raised others. I will
reply to him separately and attach v6 to that email.

--
Scott Ray

Hi Surya,

Thanks for the second review.

On Monday, August 24th, 2026 at 9:21 PM, surya poondla <suryapoondla4@gmail.com> wrote:

Yes, the slots' effective xmins, and the vacuum/logical-decoding proc
flags are the parts of the view that aren't reconstructible, and I
think both should be emitted from the existing views regardless.

I responded to these ideas in my reply to Bharath here:
/messages/by-id/_v-TXkivtfKDQiGSqD554fcsG2mFRvO20t4g2Msycyd-ruyWwO0NrIetxDISwd-o1vWi3zbwht90cnZWeUAa4KlM6kDFUylyNik9irEIap0=@scottray.io

This is specific to backend rows. Slots and prepared transactions are
already read live, pg_get_replication_slots() reads
shared memory directly and pg_prepared_xact() reads TwoPhaseState, so for
those kinds an SQL join would be ok.
[...]
I'd keep C code only for the backend rows, where the single-pass
read buys something SQL can't.

Implementing the horizon logic - not just the gather - in C instead
of SQL buys us three things:

1. It keeps the scope of the feature small, because C can access
every horizon input without adding columns to other views. In the
future, the SRF can read KnownAssignedXids and any new horizon inputs
without new SQL-callable functions.

2. It publishes the answer to "how does each source affect the
xmin horizons?" without exposing intermediate steps. I explained
in my reply to Bharath why we shouldn't add raw flags like
PROC_IN_LOGICAL_DECODING as columns to existing views. I'll explain
below why we shouldn't add derived booleans either.

3. Mirroring the horizon logic in the same language as the original
code eases the tasks of reviewing the new code and keeping
the copies synchronized.

For the proc flags I have no strong preference, maybe a derived boolean,
like whether this backend's xmin is actually counted toward the horizon.
That avoids putting internal flag names into a user-visible catalog.

"Whether this backend's xmin is actually counted toward the horizon"
depends on which horizon one picks. For example, a backend in another
database counts toward the shared horizon but not toward the current
database's data or catalog horizons. The view needs per-class
contributions, which pg_get_xmin_horizon() already provides. Delegating
part of the work to pg_stat_activity, which would publish a half answer,
offers only disadvantages compared to v5.

Lastly, one thing that follows either way: every column the SRF returns
comes from its single procarray pass, but the view then joins
pg_stat_activity for xact_start alone, against that cached snapshot rather
than the pass just taken. So a backend the SRF sees can come back with a
null xact_start, or a stale one from an earlier transaction of the same
pid. Better returned from the SRF.

Thanks for pointing this out. The attached v6 applies your suggestion.
pg_get_xmin_horizon()'s header comment documents a race reading the
transaction start, and the new helper's header comment explains why
it omits walsender timestamps.

--
Scott Ray

Hi,

On Mon, Aug 31, 2026 at 6:47 PM Scott Ray <scott@scottray.io> wrote:

If effective xmins are emitted via the replication slots view,
wouldn't that solve the issue where slots holding xmins internally are
blocking the xmin horizon and vacuum overall?

Yes, and it makes sense as an independent patch.

I will not go in that direction because the same argument below (no
independent meaning to publish for effective xmins) applies. Instead,
let's focus on moving this ahead.

Similarly, backends running vacuum and walsenders performing logical
decoding set special proc flags (PROC_IN_VACUUM /
PROC_IN_LOGICAL_DECODING) that cause vacuum to skip their xmins from
horizon computations. Since pg_stat_activity can't distinguish that
reliably today, can we think of adding such flags there?

We could. We would also need PROC_AFFECTS_ALL_HORIZONS. Each one
represents an implementation detail that does not belong in a public
API. For example, PROC_IN_LOGICAL_DECODING identifies processes in
logical decoding without any transaction, because ComputeXidHorizons()
must not ignore an open transaction's snapshot and xid. The flag
means whatever the horizon and snapshot machinery needs it to mean, so
it has no independent meaning to publish.

It looks reasonable to not burden the existing views by adding more
and more columns which most of the time serve one specific purpose,
that is for vacuum.

If there are other missing pieces, why not emit those from the
existing views to make this feature simpler?

Would this make an SQL-only query joining pg_stat_activity,
pg_prepared_xacts, pg_replication_slots, and pg_database enough?

That would suffice for most states. Surya has already pointed out
two gaps, one caused by pg_stat_activity caching and the other about
handling KnownAssignedXids during recovery.

I have a comment on the ability to know the vacuum blockers on
standbys and cascading standbys as well. At present, it's only the one
oldest xmin that reaches the primary from standbys and cascading
standbys. I think having pg_xmin_horizon report similar information on
the standby, say, the long-running transaction or long-running
analytical query or replication slot, for example, could be a valuable
addition and will be useful in many situations.

It would not make this feature simpler. It would take the irreducible
complexity of the horizon computation and spread it across two
existing views and a new query that would largely transcribe
ComputeXidHorizons() in SQL.

Tom Lane faced a similar choice when designing pg_blocking_pids(),
namely whether to add columns to pg_locks and implement the feature
as a query on top, or to write a dedicated C function, and he
decided the former would be too painful. In the commit message for
52f5d57, he wrote: "Given some more columns in the view than there
are today, you could imagine handling parallel-query cases with a
4-way join; but ugh."

Fair enough. This sounds like a reasonable argument to me unless
anyone thinks otherwise. There seems to be some degree of information
bloat being added by this patch. I mean, most of the columns are
already derivable from elsewhere today. We must try to address this
point. This may be okay given we compute things in one pass of
proc-array/replication slots. Let's see what others have to say about
this.

Surya has endorsed some of your suggestions and raised others. I will
reply to him separately and attach v6 to that email.

I plan to review this feature for PG20. When I last checked, the
following were some design thoughts off the top of my head:

1/ Supporting pg_xmin_horizon on standbys.

2/ pg_xmin_horizon needs to have similar checks as
HAS_PGSTAT_PERMISSIONS for disallowing roles that are not owners of
the sessions (unprivileged?) from unnecessarily scanning the
proc-array. I haven't spent enough time checking whether this can
happen with pg_read_all_stats predefined roles, but worth verifying.

3/ The patch was around 1400+ LOC and hard to review. Could you try to
separate it out?

4/ I'm thinking of having a dedicated docs page, or as part of the
routine vacuuming page, a separate section covering all the possible
vacuum blockers that we know of so far (especially the two missing
ones: effective xmins and backends with special proc flags) that this
feature helps expose. This will be a good direction. I prefer to have
this as the 0001 patch. Next patches would each add one blocker kind:
0002 transactions and tests, 0003 replication slots and tests, 0004
prepared transactions and tests, 0005 standby support and tests. And I
prefer to keep all the tests in one TAP test file rather than
spreading them across both SQL and TAP.

5/ How about having a more impactful and user-readable name such as
pg_vacuum_blockers or similar instead of pg_xmin_horizon?

6/ It's also better to report the top N blockers (replication slot,
prepared transaction, long-running query, idle transaction, and so on)
in one pass of proc-array/replication slots.

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com

Hi Bharath,

Thanks for the comments.

On Monday, August 31st, 2026 at 8:15 PM, Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com> wrote:

I think having pg_xmin_horizon report similar information on
the standby, say, the long-running transaction or long-running
analytical query or replication slot, for example, could be a valuable
addition and will be useful in many situations.

I agree, and in my first message in this thread, I suggested adding
standby support in a follow-up patch.

There seems to be some degree of information
bloat being added by this patch. I mean, most of the columns are
already derivable from elsewhere today. We must try to address this
point. This may be okay given we compute things in one pass of
proc-array/replication slots. Let's see what others have to say about
this.

Please explain specifically where you see information bloat.

2/ pg_xmin_horizon needs to have similar checks as
HAS_PGSTAT_PERMISSIONS for disallowing roles that are not owners of
the sessions (unprivileged?) from unnecessarily scanning the
proc-array. I haven't spent enough time checking whether this can
happen with pg_read_all_stats predefined roles, but worth verifying.

The view requires pg_read_all_stats or equivalent permissions, as I
noted in my first email, the commit message, and the view documentation.
src/test/regress/sql/xmin_horizon.sql verifies that accessing the view
without the required permissions raises an error. The SRF requires
the same.

3/ The patch was around 1400+ LOC and hard to review. Could you try to
separate it out?

4/ I'm thinking of having a dedicated docs page, or as part of the
routine vacuuming page, a separate section covering all the possible
vacuum blockers that we know of so far (especially the two missing
ones: effective xmins and backends with special proc flags) that this
feature helps expose. This will be a good direction. I prefer to have
this as the 0001 patch. Next patches would each add one blocker kind:
0002 transactions and tests, 0003 replication slots and tests, 0004
prepared transactions and tests, 0005 standby support and tests.

According to the wiki, "Normally changes should be submitted as a
single patch that includes every file touched. If the patch is large
and can be logically separated into distinct and separately
commit-able sections for easier review, with a clear order they get
applied in described when applicable, that can be more straightforward
for reviewers to work with for more complicated patches." [1]https://wiki.postgresql.org/wiki/Submitting_a_Patch
First, this patch is not large by PG standards. Second, the feature
contains no "separately commit-able sections", notwithstanding the
division you proposed. Your patch 0002 would introduce pg_xmin_horizon,
but the view would silently omit rows until at least 0004.

The patch documents "the two missing ones" you mentioned and every
holder class. For a broader reorganization of the vacuum
documentation, I recommend starting a new thread.

And I
prefer to keep all the tests in one TAP test file rather than
spreading them across both SQL and TAP.

I followed the guidance in src/test/perl/README, which says
to prefer pg_regress when possible and reserve TAP for what pg_regress
cannot express.

5/ How about having a more impactful and user-readable name such as
pg_vacuum_blockers or similar instead of pg_xmin_horizon?

The names of system views denote what they contain, and pg_xmin_horizon
reports horizon inputs, including rows that block nothing, so
pg_vacuum_blockers would misdescribe the contents.

6/ It's also better to report the top N blockers (replication slot,
prepared transaction, long-running query, idle transaction, and so on)
in one pass of proc-array/replication slots.

The view uses one pass over the procarray and another over the slot
array. A user who wants "the top N blockers" can write
ORDER BY age(shared_xmin) DESC NULLS LAST with a LIMIT.

[1]: https://wiki.postgresql.org/wiki/Submitting_a_Patch

--
Scott Ray

Hi,

On Fri, Sep 4, 2026 at 8:25 PM Scott Ray <scott@scottray.io> wrote:

I think having pg_xmin_horizon report similar information on
the standby, say, the long-running transaction or long-running
analytical query or replication slot, for example, could be a valuable
addition and will be useful in many situations.

I agree, and in my first message in this thread, I suggested adding
standby support in a follow-up patch.

My bad, I missed that. Thanks for clarifying.

There seems to be some degree of information
bloat being added by this patch. I mean, most of the columns are
already derivable from elsewhere today. We must try to address this
point. This may be okay given we compute things in one pass of
proc-array/replication slots. Let's see what others have to say about
this.

Please explain specifically where you see information bloat.

I meant the pid, database OID, database name etc. that the new view
offers look like bloat, but that seems okay because we collect all of
them over a single proc-array scan and having all these together in
this view will avoid joins with other existing views. So, I would not
call it information bloat anymore.

2/ pg_xmin_horizon needs to have similar checks as
HAS_PGSTAT_PERMISSIONS for disallowing roles that are not owners of
the sessions (unprivileged?) from unnecessarily scanning the
proc-array. I haven't spent enough time checking whether this can
happen with pg_read_all_stats predefined roles, but worth verifying.

The view requires pg_read_all_stats or equivalent permissions, as I
noted in my first email, the commit message, and the view documentation.
src/test/regress/sql/xmin_horizon.sql verifies that accessing the view
without the required permissions raises an error. The SRF requires
the same.

Thanks for clarifying this. The difference with pg_stat_activity is
that it emits some columns like pid, database OID, database name, xid,
xmin etc. that are visible to roles not owning the session (a role set
in the current session without pg_read_all_stats that was not used to
login) [1]-- no pg_read_all_stats CREATE ROLE alice LOGIN;. That is probably okay.

I plan to review the v6 patch soon.

[1]: -- no pg_read_all_stats CREATE ROLE alice LOGIN;
-- no pg_read_all_stats
CREATE ROLE alice LOGIN;

-- connect to a database as alice
psql -d postgres -U alice

-- shows pid, datid, datname etc. even without pg_read_all_stats
SELECT * FROM pg_stat_activity;

-- permission denied
SELECT * FROM pg_xmin_horizon;

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com

Hi Scott,

I reviewed the v6 and have some comments.

pgstat_get_xact_start_by_proc_number() reads BackendStatusArray directly,
so the value is no longer taken from a cached snapshot that could predate
the procarray pass by an arbitrary amount.
The residual race you document in the header comment is a much smaller
window, and the change-count retry loop and the pid <= 0 guard both look
right. I also checked that p->procNumber = pgprocno is the right
ProcNumber for indexing the status array, and that a prepared xact's dummy
proc can't reach the array out of bounds, the pid <= 0 early return catches
it before the bounds check does.

The maintenance.sgml rewrite is better than what I suggested. Sorting all
rows by age(shared_xmin) DESC NULLS LAST, and explaining that
datfrozenxid can never advance past the shared horizon and that every ow's
shared_xmin feeds it including backends in other databases, avoids the
datid scoping question entirely rather than trying to describe it.
The datfrozenxid clarification is a good catch too.

One question. pg_stat_get_activity() gates st_xact_start_timestamp behind
HAS_PGSTAT_PERMISSIONS, but the new helper applies no such check.
That isn't reachable today, since both the view and the SRF require
pg_read_all_stats, and such a caller already passes that test for every
backend.
But it does mean the SRF omits a check pg_stat_activity applies. Bharath
pointed out one asymmetry already i.e pg_stat_activity shows pid, datid,
xid and xmin to unprivileged non-owners while
pg_xmin_horizon shows nothing, and this would be one in the other direction
if the ACL were ever loosened or EXECUTE granted directly.
Would it be worth applying HAS_PGSTAT_PERMISSIONS per row in the SRF?

Regards,
Surya Poondla

Hi,

On Fri, Sep 11, 2026 at 5:40 PM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:

I plan to review the v6 patch soon.

I reviewed the v6 patch and here are some comments. Apologies if some
of these have already been discussed. Feel free to point me to the
related discussions on any of these. I haven't reviewed the tests yet.
I will do so after the code-related comments are addressed.

1/ Good catch, and the new wording looks right. datfrozenxid can end
up older than the oldest relfrozenxid in the database, because the
computation starts from the oldest xid vacuum is allowed to remove,
not from the tables. I recommend proposing this separately and
probably back-patch it, but fine to keep it here too.

     aggressive VACUUM).  Similarly, the
     <structfield>datfrozenxid</structfield> column of a database's
     <structname>pg_database</structname> row is a lower bound on the
unfrozen XIDs
-    appearing in that database &mdash; it is just the minimum of the
-    per-table <structfield>relfrozenxid</structfield> values within
the database.
+    appearing in that database &mdash; it is no newer than, and normally
+    equal to, the minimum of the per-table
+    <structfield>relfrozenxid</structfield> values within the database.

2/ In both of the following places, can we have a sample query and
output with all the per-class rows present? IMO, that works better and
readily usable for those looking at it.

+       including rows for backends in other databases.  Sort all rows by
+       <literal>age(shared_xmin)</literal> descending
+       to find the oldest pinners.  Use <literal>NULLS LAST</literal>, because
+       a row with a null <structfield>shared_xmin</structfield> pins
+       nothing.</simpara>

+   To find the row binding a horizon class, order the applicable rows by
+   descending <function>age()</function> of the class column, placing
+   null values last.
+   <type>xid</type> has no ordering operators, so
+   <function>age()</function> serves as the wraparound-safe sort key.

3/ A few wording nits, mostly about matching what the docs already
say. The steps that follow say "old prepared transactions",
"long-running open transactions" and "old replication slots".
system-views.sgml says "backend currently holding a snapshot or
transaction ID". monitoring.sgml calls this the xmin horizon, and
describes the fourth kind as a standby's xmin horizon reported by
hot_standby_feedback, written without hyphens as the docs do
everywhere. And for shared, the docs' usual phrase is shared catalogs.

How about changing "snapshot-holding backend" to "backend holding a
snapshot or transaction ID", "per-class xmin contribution" to the
column names shared_xmin, catalog_xmin and data_xmin (or to xmin
horizons), "hot-standby-feedback sender" to "hot standby feedback
sender", and "cluster-wide shared horizon" to the horizon for shared
catalogs? Same comments applicable elsewhere in the patch.

+      <simpara>Identify the source.  Query
+       <link linkend="view-pg-xmin-horizon"><structname>pg_xmin_horizon</structname></link>,
+       which reports one row per snapshot-holding backend, replication slot,
+       prepared transaction, or hot-standby-feedback sender with each row's
+       per-class <structfield>xmin</structfield> contribution.  The warning
+       is driven by the database's <structfield>datfrozenxid</structfield>,
+       which can never advance past the cluster-wide shared horizon, and
+       every row's <structfield>shared_xmin</structfield> feeds that horizon,
+       including rows for backends in other databases.  Sort all rows by
+       <literal>age(shared_xmin)</literal> descending
+       to find the oldest pinners.  Use <literal>NULLS LAST</literal>, because

4/ The steps for finding the cause of the wraparound warnings
currently point at pg_stat_activity, pg_prepared_xacts and
pg_replication_slots, and the patch replaces those with the new view.
The intent makes sense, since the whole point of pg_xmin_horizon is to
be the first place to look. But the existing methods are not wrong
either, and there are scripts and runbooks built around them. So how
about mentioning both, something like "You can find these in
pg_xmin_horizon or existing views"? There will be no surprises for
those relying on the existing views, and they can move to the new one
if they wish.

- <simpara>End long-running open transactions. You can find these
by checking
- <link linkend="monitoring-pg-stat-activity-view"><structname>pg_stat_activity</structname></link>
for rows where
- <literal>age(backend_xid)</literal> or
<literal>age(backend_xmin)</literal> is
- large. Such transactions should be committed or rolled back,
or the session
- can be terminated using
<literal>pg_terminate_backend</literal>.</simpara>

+      <simpara>End long-running open transactions by committing, rolling back,
+       or terminating the session using
+       <literal>pg_terminate_backend</literal>.</simpara>
+     </listitem>
+     <listitem>

5/ How about "sources holding back the cluster's xmin horizon,
preventing VACUUM from removing dead row versions and clearing old
XIDs"? Matches what monitoring.sgml and maintenance.sgml say. Same
comment applies to the other places in the patch that say removal
horizon.

+     <row>
+      <entry><link
linkend="view-pg-xmin-horizon"><structname>pg_xmin_horizon</structname></link></entry>
+      <entry>sources holding back the cluster's VACUUM removal horizon</entry>
+     </row>

+   and reports each row's raw per-class <structfield>xmin</structfield>
+   contributions to the cluster's <command>VACUUM</command> removal horizon.
+   A row whose per-class <structfield>xmin</structfield> columns are all
+   null does not currently pin the horizon.

6/ "physical-replication sender" is a new word here, and calling it a
sender is confusing when what it holds is an xmin the standby sent to
it. How about "a standby_feedback row represents a WAL sender process
without a replication slot, holding the xmin horizon sent by its
standby through hot_standby_feedback"?

+       The row's kind: <literal>backend</literal>,
+       <literal>replication_slot</literal>, <literal>prepared_xact</literal>,
+       or <literal>standby_feedback</literal>.  A
+       <literal>standby_feedback</literal> row represents a
physical-replication
+       sender propagating <link
linkend="guc-hot-standby-feedback"><varname>hot_standby_feedback</varname></link>
+       without a replication slot.  A sender that does use a slot is reported
+       through that slot's <literal>replication_slot</literal> row instead.

7/ Why join with pg_prepared_xacts here? The point of pg_xmin_horizon
was to collect most of the things in C to avoid joining existing
views. The join also means gid reads null if the prepared transaction
ends in between, for a row the view still reports. Why not return gid
from the function itself, same as xact_start?

+    FROM pg_get_xmin_horizon() c
+        LEFT JOIN pg_prepared_xacts p
+            ON c.kind = 'prepared_xact' AND p.transaction = c.xid

+       <structfield>gid</structfield> <type>text</type>
+      </para>
+      <para>
+       The global transaction identifier for <literal>prepared_xact</literal>
+       rows, joined in from
+       <link linkend="view-pg-prepared-xacts"><structname>pg_prepared_xacts</structname></link>.
+       Null for all other kinds.
+      </para></entry>
+      </para></entry>

8/ How about using "<literal>replication_slot</literal> and
<literal>standby_feedback</literal> rows pin the horizon ..."?

+       Slot and <literal>standby_feedback</literal> rows pin the horizon
+       cluster-wide regardless of

9/ This seems unnecessary. The previous sentence already says these
rows pin cluster-wide regardless of datid, so the note about filtering
on datid adds little.

+       <structfield>datid</structfield>, and a query filtering
+       by <structfield>datid</structfield> will exclude them.
+      </para></entry>

10/ "The transaction ID this row owns, or null if it owns none" reads
a bit confusing. How about "Transaction identifier of this row's
source, if any; see <xref linkend="transaction-id"/>" similar to
pg_stat_activity.backend_xid?

+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>xid</structfield> <type>xid</type>
+      </para>
+      <para>
+       The transaction ID this row owns, or null if it owns none; see
+       <xref linkend="transaction-id"/>.  For

11/ How about listing all the columns here, shared_xmin, catalog_xmin
and data_xmin, for clarity?

+       <literal>standby_feedback</literal> rows.  A row with a null
+       <structfield>xid</structfield> can still pin the horizon through
+       the <structfield>*_xmin</structfield> columns.

12/ Same point as the per-class one above. How about "The transaction
ID this row pins for the shared xmin horizon. Null if it does not pin
that horizon", and likewise for catalog_xmin and data_xmin? The
data_xmin entry then reads "pins none of the three horizons and shows
null in shared_xmin, catalog_xmin and data_xmin".

+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>shared_xmin</structfield> <type>xid</type>
+      </para>
+      <para>
+       The transaction ID this row pins for the shared horizon class.
+       Null if it does not pin that class.
+      </para></entry>
+     </row>

13/ How about following the existing columns here, something like
"Time when this row's transaction was started, or, for prepared_xact
rows, the time at which the transaction was prepared for commit",
similar to pg_stat_activity.xact_start and pg_prepared_xacts.prepared?

+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>xact_start</structfield> <type>timestamptz</type>
+      </para>
+      <para>
+       Time when the row's transaction was started, or, for
+       <literal>prepared_xact</literal> rows, when the transaction was
+       prepared.  Null for <literal>replication_slot</literal> rows,
+       <literal>standby_feedback</literal> rows, and
+       <literal>backend</literal> rows belonging to walsender processes.
+      </para></entry>
+     </row>

14/ This paragraph took me a couple of passes to follow although I
understand how these contribute to overall XID horizon in
ComputeXidHorizons(). How about something shorter, in the order of
what a reader needs?

For replication_slot rows, shared_xmin and catalog_xmin report the
older of the slot's xmin and catalog_xmin, while data_xmin reports the
slot's xmin alone, because a slot's catalog_xmin applies to catalog
rows only, not to dead row versions in user tables. These are the
slot's effective xmins (the values the server uses when it computes
the horizon), so while a slot is being created they can briefly differ
from the ones shown in pg_replication_slots. The server also keeps a
single oldest value across all slots, recomputed only when a slot
changes, so for a short while it can be older than every value shown
here, and a horizon can be held back by a value that no row reports.
An invalidated slot holds back nothing and reports null in all three
columns.

+  <para>
+   For <literal>replication_slot</literal> rows,
+   <structfield>shared_xmin</structfield> and
+   <structfield>catalog_xmin</structfield> both report the older of the
+   slot's <structfield>xmin</structfield> and
+   <structfield>catalog_xmin</structfield>.  The
+   <structfield>data_xmin</structfield> column reports the slot's

15/ This one is hard to follow. How about a short example query
instead of describing the sort in prose, as in comment 3, and
splitting the rest so each paragraph does one thing?

To find the row holding back an xmin horizon, use a query such as:
<< query here >>
xid has no ordering operators, so the sort uses age(), which is
wraparound-safe. Which rows apply depends on the column and on datid,
as described above.

When several rows report the same oldest value, that xmin horizon
cannot advance until all of them release it, so resolving only one of
them has no effect. If one of those rows has an xid equal to that
value, the horizon has to wait for that transaction to finish.

When no row reports a value for an xmin horizon, it advances on its
own to a point just past the newest completed transaction, which this
view does not show. Note also that backend rows and replication slot
rows are gathered at slightly different times, so a single query is
not a snapshot of the whole cluster at one instant. See <xref
linkend="vacuum-for-wraparound"/> for the steps to take when an xmin
horizon is held back.

+  <para>
+   To find the row binding a horizon class, order the applicable rows by
+   descending <function>age()</function> of the class column, placing
+   null values last.
+   <type>xid</type> has no ordering operators, so

16/ How about "Querying pg_xmin_horizon on a standby is not supported
and raises an error, because the view cannot report the primary's
running transactions during recovery"?

+   <para>
+    Querying <structname>pg_xmin_horizon</structname> on a hot standby is not
+    supported and will raise an error.
+   </para>

17/ How about defining a macro for this flag test similar to PROC_*
and using it in all three places, instead of a comment asking future
authors to keep them in sync?

-                * decoding (which manages xmin separately, check below).
+                * decoding (which manages xmin separately, check
below).  Keep this
+                * filter in sync with pg_get_xmin_horizon()'s classifier.
                 */
                if (statusFlags & (PROC_IN_VACUUM | PROC_IN_LOGICAL_DECODING))
                        continue;

18/ GetXidHorizonProcs() acquires ProcArrayLock once and gathers all
procs in a single pass, whereas pg_stat_activity takes the lock once
per backend, so each snapshot does N acquisitions and releases. I
measured this by keeping ~500 backends connected with pgbench, then
running each view repeatedly from one backend while a separate
4-client pgbench write workload ran alongside. Query exec time is
0.304 ms vs 1.335 ms for pg_xmin_horizon and pg_stat_activity
respectively (4.4x), and the write workload TPS reduces by 11.7% vs
27.4%. Connection latency is also lower since connection setup takes
the lock exclusive, measuring 2.21 ms with no polling, 2.55 ms with
pg_xmin_horizon polling, and 3.09 ms with pg_stat_activity polling.

19/ The comment mostly says what the exact code does incl. variable
names, and the one thing it adds, that the rows are mutually
consistent, is not what the shared lock gives. How about the
following?

/*
* Get the xid, xmin and other horizon inputs of every proc, in one pass over
* the proc array.
*
* These are the same per-proc inputs that ComputeXidHorizons() uses. Processes
* cannot enter or leave the array during the pass, but a process can install a
* new xmin or get an xid while the pass is still running, see
* GetSnapshotData() and GetNewTransactionId(), so the values returned can be
* from different moments.
*
* There is a race here. The lock is released before returning, so by the time
* the caller reads an entry the process may have ended its transaction or
* exited altogether.
*
* The caller is responsible for freeing the returned array.
*/

20/ How about returning both the array and its length as out
parameters, void GetXidHorizonProcs(XidHorizonProc **procs, int
*nprocs)?

21/ The count variable looks redundant. Nothing is skipped in the
loop, so count always ends up equal to arrayP->numProcs, which could
be used directly.

22/ With pgstat_get_beentry_by_proc_number(), the new function is not
needed unless I'm missing something else. It does the change count
retry internally, so something like the following is enough.

PgBackendStatus *beentry = pgstat_get_beentry_by_proc_number(p->procNumber);

if (beentry != NULL && beentry->st_procpid == pid &&
beentry->st_backendType != B_WAL_SENDER)
r->xact_start = beentry->st_xact_start_timestamp;

23/ Nit: How about using consistent wording here?
int pid; /* Backend's process ID; 0 if prepared xact */
int procNumber; /* ProcNumber, which is also the
* BackendStatusArray index; not valid for
* prepared xact dummy procs */
24/ Nit: proisstrict has no effect for a function with no arguments,
and pg_get_aios(), pg_lock_status() and pg_prepared_xact() all leave
it out. How about dropping it? The descr could also read like its
neighbours, something such as 'information about sources holding back
the cluster's xmin horizons'.

+{ oid => '8054',
+  descr => 'per-source inputs to the cluster xmin horizons',
+  proname => 'pg_get_xmin_horizon', prorows => '100', proisstrict => 'f',
+  proretset => 't', provolatile => 'v', proparallel => 'r',
prorettype => 'record',
+  proargtypes => '',
+  proallargtypes => '{text,int4,name,oid,xid,xid,xid,xid,timestamptz}',
+  proargmodes => '{o,o,o,o,o,o,o,o,o}',
+  proargnames =>
'{kind,pid,slot_name,datid,xid,shared_xmin,catalog_xmin,data_xmin,xact_start}',
+  prosrc => 'pg_get_xmin_horizon', proacl =>
'{POSTGRES=X,pg_read_all_stats=X}' },

25/ How about merging the second and third passes in
pg_get_xmin_horizon(), something like the attached diff on top of v6?
Emitting rows while holding ReplicationSlotControlLock seems okay, the
same way pg_get_replication_slots() already does it. Is there a reason
not to do this?

26/ Nit: How about something like the following to be clearer?

* procarrayfuncs.c
* SQL-callable functions related to POSTGRES process array code.
*

27/ For a parallel query, and for concurrent REPACK with decoding
workers, the leader and every worker gets its own pinning row. Nothing
in the view itself links a worker row back to its leader, though
joining pg_stat_activity on pid gives leader_pid for anyone who wants
it. How about a doc note saying so?

28/ Decoding through pg_logical_slot_get_changes() shows up as an
ordinary backend row with an xmin of its own, which is worth knowing
for anyone looking at what holds the horizon back. How about a doc
note saying so?

29/ The note in the docs about a backend running VACUUM showing null
in shared_xmin, catalog_xmin and data_xmin holds only for lazy VACUUM,
which is the one path that sets PROC_IN_VACUUM. VACUUM FULL and
CLUSTER report an xid and all three xmins, and ANALYZE reports all
three xmins with a null xid. How about clarifying this in the docs?

statement xid shared_xmin catalog_xmin data_xmin
lazy VACUUM null null, null, null
VACUUM FULL 668 668, 668, 668
CLUSTER 681 681, 681, 681
ANALYZE null 681, 681, 681

Thanks for reading this far.

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com

Hi Bharath,

Thanks for the measurements. I raised the single-pass point earlier but
without numbers, so it's good to see it quantified.

Regarding your point 22, pgstat_get_beentry_by_proc_number() calls
pgstat_get_local_beentry_by_proc_number(), which calls
pgstat_read_current_status() and then searches localBackendStatusTable,
so it reads the cached per-transaction snapshot rather than
BackendStatusArray.
Two consequences: the xact_start value would again come from a snapshot
that can predate the procarray pass by an arbitrary
amount, which is what I asked Scott to fix and why the new helper reads the
array directly. And building that snapshot is the loop that takes
ProcArrayLock once per active backend, which is the cost your point 18
measures.

The change-count retry does happen, but only while the snapshot is being
built. After that the caller is reading a local copy, so it doesn't buy
freshness.

Regards,
Surya Poondla

Hi Scott,

Thanks for working on this.

When reviewing the "Report oldest xmin source ..." thread [1]/messages/by-id/20220211083126.2qb7ij2u6je5c5hm@alap3.anarazel.de, I began to
wonder whether this information would be better exposed through a view
rather than only through logging. I think exposing this information through
a view is the right direction.

Logging has benefits, but a view gives a DBA an opportunity to identify and
resolve the problem before allowing a VACUUM to complete without doing useful
cleanup.

I realize that the patch is already at v6 and that a fair amount of work has
gone into the current approach. However, since this introduces a new system
view, I think it is worth considering the user-facing design a bit more.

Looking at v6, I am not sure that one row per potential horizon input is the
most useful representation, especially if a DBA must construct a potentially
complex query before getting an actionable answer. The questions a DBA needs
answered by this view are:

1/ Which horizon is being held back, and at what XID?

2/ What is currently holding it back?

3/ What action can be taken to allow it to advance?

The view contains the raw information needed to answer these questions, but
leaves the DBA to reconstruct the effective horizons in SQL. I think the
view should do that itself.

The first issue I see is that pg_get_xmin_horizon() does not use
ComputeXidHorizons(), even though that is where the horizons used by VACUUM
are calculated. Instead, it exposes a separately gathered set of per-source
inputs.

The following comment added to ComputeXidHorizons() makes this coupling
explicit:

@@ -1765,7 +1765,8 @@ ComputeXidHorizons(ComputeXidHorizonsResult *h)
  /*
  * Skip over backends either vacuuming (which is ok with rows being
  * removed, as long as pg_subtrans is not truncated) or doing logical
- * decoding (which manages xmin separately, check below).
+ * decoding (which manages xmin separately, check below).  Keep this
+ * filter in sync with pg_get_xmin_horizon()'s classifier.
  */

The need for this comment seems like a sign that this logic is happening at
the wrong level. I think the underlying function should expose the effective
horizons directly from ComputeXidHorizons(), along with enough information to
identify their sources. A SQL view can then present those horizons and
determine why each one is being held back.

Perhaps something along these lines, with a stable set of rows representing
the horizons:

```
horizon | datid | xmin | pids | slot_names | gids
```

Would it make sense to drive the view from ComputeXidHorizons() and add the
source attribution on top of those results?

[1]: /messages/by-id/20220211083126.2qb7ij2u6je5c5hm@alap3.anarazel.de

--
Sami Imseih
Amazon Web Services (AWS)