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

Hi Bharath,

Thanks for the review.

On Monday, August 24th, 2026 at 9:15 AM, Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)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