Re: Add sub-transaction overflow status in pg_stat_activity

On Mon, Dec 6, 2021 at 8:16 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:

> If the subtransaction cache is overflowed in some of the transactions
> then it will affect all the concurrent queries as they need to access
> the SLRU for checking the visibility of each tuple. But currently
> there is no way to identify whether in any backend subtransaction is
> overflowed or what is the current active subtransaction count.

I think it's a good idea – had the same need when recently researching
various issues with subtransactions [1], needed to patch Postgres in
benchmarking environments. To be fair, there is a way to understand that
the overflowed state is reached for PG 13+ – on standbys, observe reads in
Subtrans in pg_stat_slru. But of course, it's an indirect way.

I see that the patch adds two new columns to pg_stat_activity:
subxact_count and subxact_overflowed. This should be helpful to have.
Additionally, exposing the lastOverflowedXid value would be also good for
troubleshooting of subtransaction edge and corner cases – a bug recently
fixed in all current versions [2] was really tricky to troubleshoot in
production because this value is not visible to DBAs.

[1]
https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful
[2] https://commitfest.postgresql.org/36/3399/