On Mon, Dec 6, 2021 at 8:16 PM Dilip Kumar <dilipbal...@gmail.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/