Subject: Re: Add sub-transaction overflow status in pg_stat_activity

From: Julien Rouhaud <[email-masked]>
Date: Fri, 14 Jan 2022 15:47:26 +0800

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.

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.

---

From: Dilip Kumar <[email-masked]>
Date: Fri, 14 Jan 2022 21:18:59 +0530

> > +        </parameter>, which shows whether the backend's subtransaction

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

> I don't want to derail this thread so let me know if I should start a

Yeah that seems like a good idea.

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