Re: Help understanding SIReadLock growing without bound on completed transaction

On Fri, May 22, 2020 at 3:15 PM, Thomas Munro < thomas(dot)munro(at)gmail(dot)com > wrote:

>
>
>
> Predicate locks are released by ClearOldPredicateLocks(), which releases
> SERIALIZABLEXACTs once they are no longer interesting. It has a
> conservative idea of what is no longer interesting: it waits until the
> lowest xmin across active serializable snapshots is >= the transaction's
> finishedBefore xid, which was the system's next xid (an xid that hasn't
> been used yet*) at the time the SERIALIZABLEXACT committed. One
> implication of this scheme is that SERIALIZABLEXACTs are cleaned up in
> commit order. If you somehow got into a state where a few of them were
> being kept around for a long time, but others committed later were being
> cleaned up (which I suppose must be the case or your system would be
> complaining about running out of SERIALIZABLEXACTs), that might imply that
> there is a rare leak somewhere in this scheme. In the past I have wondered
> if there might be a problem with wraparound in the xid tracking for
> finished transactions, but I haven't worked out the details (transaction
> ID wraparound is both figuratively and literally the Ground Hog Day of
> PostgreSQL bug surfaces).
>
>
>
>

Thanks for the detailed reply, Thomas.  Is SERIALIZABLEXACT transaction ID wraparound the same as global xid wraparound?  The max transaction age in the db is ~197M [1] so I don't think we've gotten close to global wraparound lately.

Would it be helpful to cross-post this thread to pgsql-bugs or further investigate on my end

-Mike

[1] superhuman@ production => select datname, datfrozenxid, age(datfrozenxid) from pg_catalog.pg_database;

datname | datfrozenxid | age

---------------+--------------+-----------

cloudsqladmin | 4173950091 | 169089900

template0 | 4266855294 | 76184697

postgres | 4173951306 | 169088685

template1 | 4266855860 | 76184131

superhuman | 4145766807 | 197273184