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

Hi Scott,

Thanks for working on this.

When reviewing the "Report oldest xmin source ..." thread [1], I began to
wonder whether this information would be better exposed through a view
rather than only through logging. I think exposing this information through
a view is the right direction.

Logging has benefits, but a view gives a DBA an opportunity to identify and
resolve the problem before allowing a VACUUM to complete without doing useful
cleanup.

I realize that the patch is already at v6 and that a fair amount of work has
gone into the current approach. However, since this introduces a new system
view, I think it is worth considering the user-facing design a bit more.

Looking at v6, I am not sure that one row per potential horizon input is the
most useful representation, especially if a DBA must construct a potentially
complex query before getting an actionable answer. The questions a DBA needs
answered by this view are:

1/ Which horizon is being held back, and at what XID?

2/ What is currently holding it back?

3/ What action can be taken to allow it to advance?

The view contains the raw information needed to answer these questions, but
leaves the DBA to reconstruct the effective horizons in SQL. I think the
view should do that itself.

The first issue I see is that pg_get_xmin_horizon() does not use
ComputeXidHorizons(), even though that is where the horizons used by VACUUM
are calculated. Instead, it exposes a separately gathered set of per-source
inputs.

The following comment added to ComputeXidHorizons() makes this coupling
explicit:

@@ -1765,7 +1765,8 @@ ComputeXidHorizons(ComputeXidHorizonsResult *h)
/*
* Skip over backends either vacuuming (which is ok with rows being
* removed, as long as pg_subtrans is not truncated) or doing logical
- * decoding (which manages xmin separately, check below).
+ * decoding (which manages xmin separately, check below). Keep this
+ * filter in sync with pg_get_xmin_horizon()'s classifier.
*/

The need for this comment seems like a sign that this logic is happening at
the wrong level. I think the underlying function should expose the effective
horizons directly from ComputeXidHorizons(), along with enough information to
identify their sources. A SQL view can then present those horizons and
determine why each one is being held back.

Perhaps something along these lines, with a stable set of rows representing
the horizons:

```
horizon | datid | xmin | pids | slot_names | gids
```

Would it make sense to drive the view from ComputeXidHorizons() and add the
source attribution on top of those results?

[1] https://www.postgresql.org/message-id/20220211083126.2qb7ij2u6je5c5hm%40alap3.anarazel.de

--
Sami Imseih
Amazon Web Services (AWS)