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.