Re: Add sub-transaction overflow status in pg_stat_activity

Hi,

On 2022-11-15 09:04:25 -0500, Robert Haas wrote:
> 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 it'd be more helpful to just have a single row (or maybe a fixed
number of rows) - from what I've observed the main problem people have is
condensing the available information, rather than not having information
available at all.

> 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.

Hence the suggestion to show the pid of the session with the most subxacts. We
probably also should add a bunch of accessor functions for people that want
more detail... But just seeing in one place what's problematic would be the
big get, the rest will be a small percentage of users IME.

Greetings,

Andres Freund