Re: pg_xmin_horizon: a system view of everything pinning the xmin horizon

Hi Sami, Scott,

Two things that might help with the shape question.

First, there is committer precedent for both shapes. Sami found Andres's
2023 sketch [1], with one row per database plus a NULL-datname row for the
system-wide horizons. In the 2020 "Expose oldest xmin as SQL function for
monitoring" thread, Tom described the other one [2]: "I was envisioning a
view that would show you *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." So both representations have been asked for, which makes me
think the question may be which one is the base and which is built on
top, not which one to have.

Second, on "a query over the patch's rows produces the summary": I think
that's true with two exceptions, and v7 already documents both. When no
row contributes to a horizon, the horizon advances to a baseline just
past the newest completed xid, "which this view does not display". And
the combined slot xmins "update only when a slot changes", so "a stale
value that no row shows can briefly pin a horizon". Those are the values
a per-horizon summary row would need to report the horizon correctly. If
the function also returned them, as Sami's ComputeXidHorizonsData()
sketch does for the baseline, a per-horizon summary could be built as a
view over the per-source rows, at least on a primary, and both shapes would
be available.

[1]
https://www.postgresql.org/message-id/20231026174136.4et3ktuegmtlgxfs@awork3.anarazel.de
[2] https://www.postgresql.org/message-id/30712.1585854944@sss.pgh.pa.us

Regards,
Surya Poondla