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

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