Re: Add sub-transaction overflow status in pg_stat_activity

On Tue, Dec 7, 2021 at 10:29 AM Nikolay Samokhvalov
<samokhvalov(at)gmail(dot)com> wrote:
>
> 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.

Yeah right.

> 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.

Yeah, we can show this too, although we need to take ProcArrayLock in
the shared mode for reading this, but anyway that will be done on
users request so should not be an issue IMHO.

I will post the updated patch soon along with comments given by
Zhihong Yu and Justin.

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