Re: Add sub-transaction overflow status in pg_stat_activity

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.