Re: -1/0 virtualtransaction

In case this helps anyone else, I found a simple way to get a rough
idea of what's going on, which is to run:

select (select count(distinct virtualtransaction) from pg_locks) as
tx_with_locks, (select count(*) from pg_stat_activity where state =
'active') as active_tx, (select count(*) from pg_locks where
virtualtransaction = '-1/0') as summarized_locks;

I disabled the part of my application that seemed to be causing
problems with too many writes (a background cleanup task) and then
triggered it from a separate process. I can see the number of
transactions with locks climbing when it hits a problematic item,
while the number of active transactions (of course) stays low.

Mike

On Fri, Apr 30, 2021 at 4:53 PM Mike Beachy <mbeachy(at)gmail(dot)com> wrote:
>
> On Fri, Apr 30, 2021 at 7:12 AM Thomas Munro <thomas(dot)munro(at)gmail(dot)com> wrote:
> > But do you have lots of short overlapping transactions so that there
> > is never a moment where there are zero transactions running?
>
> Yeah, that almost certainly explains it.
>
> Thanks very much for the explanation about the summarized locks.
>
> > The number of SERIALIZABLEXACT objects is (max_connections +
> > max_prepared_transactions) * 10. So, you could try increasing
> > max_connections (without increasing the actual number of connections)
> > to see if you can get to a point where you don't see these invalid
> > virtual xids, and then maybe it'll be able to clean up locks more
> > aggressively.
>
> Aha! I hadn't considered that some parameter besides
> max_pred_locks_per_transaction would come into play. I'll give this a
> shot.
>
> Thanks,
> Mike