Re: Add sub-transaction overflow status in pg_stat_activity

On Fri, Jan 14, 2022 at 1:17 PM Julien Rouhaud <rjuju123@gmail.com> wrote:

> On Thu, Jan 13, 2022 at 10:27:31PM +0000, Bossart, Nathan wrote:
> > Thanks for the new patch!
> >
> > +       <para>
> > +        Returns a record of information about the backend's
> subtransactions.
> > +        The fields returned are <parameter>subxact_count</parameter>
> identifies
> > +        number of active subtransaction and <parameter>subxact_overflow
> > +        </parameter> shows whether the backend's subtransaction cache is
> > +        overflowed or not.
> > +       </para></entry>
> > +       </para></entry>
> >
> > nitpick: There is an extra "</para></entry>" here.
>
> Also the sentence looks a bit weird.  I think something like that would be
> better:
>
> > +        Returns a record of information about the backend's
> subtransactions.
> > +        The fields returned are <parameter>subxact_count</parameter>,
> which
> > +        identifies the number of active subtransaction and
> <parameter>subxact_overflow
> > +        </parameter>, which shows whether the backend's subtransaction
> cache is
> > +        overflowed or not.
>

Thanks for looking into this, I will work on this next week.


> While on the sub-transaction overflow topic, I'm wondering if we should
> also
> raise a warning (maybe optionally) immediately when a backend overflows
> (so in
> GetNewTransactionId()).
>
> Like many I previously had to investigate a slowdown due to sub-transaction
> overflow, and even with the information available in a monitoring view (I
> had
> to rely on a quick hackish extension as I couldn't patch postgres) it's not
> terribly fun to do this way.  On top of that log analyzers like pgBadger
> could
> help to highlight such a problem.
>
> I don't want to derail this thread so let me know if I should start a
> distinct
> discussion for that.
>

Yeah that seems like a good idea.

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