Postgres Pro
Downloads
Home   >   mailing lists

Re: Help understanding SIReadLock growing without bound oncompleted transaction - Mailing list pgsql-general

From Mike Klaas
Subject Re: Help understanding SIReadLock growing without bound oncompleted transaction
Date
Msg-id kao3yp20.7dbea4d8-f0ac-4b61-9057-52a708419841@we.are.superhuman.com
Whole thread Raw
In response to Re: Help understanding SIReadLock growing without bound oncompleted transaction  ("Mike Klaas" <mike@superhuman.com>)
List pgsql-general
Tree view
On second look, it does seems the xid crossed the 2^32 mark recently, since most tables have a frozenxid close to 4b and the current xid is ~50m:

SELECT relname, age(relfrozenxid), relfrozenxid FROM pg_class WHERE relkind = 'r' and relname not like 'pg%' order by relname;
          relname          |    age    | relfrozenxid
---------------------------+-----------+--------------
<table name>              | 107232506 |   4237961815
<table name>              |  93692362 |   4251501959
<table name>              | 183484103 |   4161710218
<table name>              |  50760536 |   4294433785
<table name>              |  58821410 |   4286372911
<table name>              | 117427283 |   4227767038
<table name>              |  94541111 |   4250653210
…

select max(backend_xid::text), min(backend_xmin::text) from pg_stat_activity where state='active';

   max    |   min    

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

 50350294 | 50350065


-Mike


On Tue, May 26, 2020 at 8:42 AM, Mike Klaas <mike@superhuman.com> wrote:
On Fri, May 22, 2020 at 3:15 PM, Thomas Munro <thomas.munro@gmail.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


pgsql-general by date:

Previous
From: "Mike Klaas"
Date:
Subject: Re: Help understanding SIReadLock growing without bound oncompleted transaction
Next
From: David Gauthier
Date:
Subject: Advise on how to install pl/perl on existing DB.