Re: Add sub-transaction overflow status in pg_stat_activity

On Mon, Nov 14, 2022 at 4:17 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
> Perhaps this would better be tackled by a new "visibility" view. It could show
> - number of sessions with a snapshot
> - max age of backend xmin
> - pid with max backend xmin
> - number of sessions that suboverflowed
> - pid of the session with the most subxids
> - age of the oldest prepared xact
> - age of the oldest slot
> - age of the oldest walsender
> - ...
>
> Perhaps implemented in SQL, with new functions for accessing the properties we
> don't expose today. That'd address the pg_stat_activity width, while still
> allowing very granular access when necessary. And provide insight into
> something that's way to hard to query right now.

I wouldn't be against a pg_stat_visibility view, but I don't think I'd
want it to just output a single summary row. I think we really need to
give people an easy way to track down which session is the problem;
the existence of the problem is already obvious from the SLRU-related
wait events.

If we moved backend_xid and backend_xmin out to this new view, added
these subtransaction-related things, and allowed for a join on pid, I
could get behind that, but it's probably a bit more painful for users
than just accepting that the view is going to further outgrow the
terminal window. It might be better in the long term because perhaps
we're going to find more things that would fit into this new view, but
I don't know.

--
Robert Haas
EDB: http://www.enterprisedb.com