Re: Add sub-transaction overflow status in pg_stat_activity

On Fri, Jan 14, 2022 at 1:17 PM Julien Rouhaud <rjuju123(at)gmail(dot)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