pg_xmin_horizon: a system view of everything pinning the xmin horizon

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] https://www.postgresql.org/message-id/CAOzEurSgy-gDtwFmEbj5+R9PL0_G3qYB6nnzJtNStyuf87VSVg@mail.gmail.com

--
Scott Ray