Hi, On Mon, Aug 31, 2026 at 6:47 PM Scott Ray <[email protected]> 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
