Re: Add sub-transaction overflow status in pg_stat_activity

On Tue, Nov 15, 2022 at 7:34 PM Robert Haas <robertmhaas(at)gmail(dot)com> 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 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.
>

Even I feel per backend-wise information would be more useful and easy
to use instead of a single summary row. I think It's fine to create a
new view if we do not want to add more members to the existing view.

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com