1

Recently, I had an issue with one of my database which got XID wraparound issue. So, I have started with single-user mode and ran vacuum. Everything was fine. But, when I checked datfrozenxid its ~1.5billon where as my txid_current is ~70billon. I feel like, its huge different and something is wrong.

The below query, I have used to see the datfrozenxid from pg_database

select datname, datfrozenxid, age(datfrozenxid) from pg_database order by datname;

I have read a XID wraparound scenario from http://www.rummandba.com/2014/02/understanding-xid-wrap-around.html

It make sense to me as per the Example given in above link. But, I have calculated xidWrapLimit, xidStopLimit, xidWarnLimit, xidVacLimit with my statistics.

xidWrapLimit = oldest_datfrozenxid + (MaxTransactionId >> 1);
xidStopLimit = xidWrapLimit - 1000000;
xidWarnLimit = xidStopLimit - 10000000;
(I haven't given all the values here since I am looking only for xidWarnLimit)

So, the xidWarnLimit is 3.747 billon where my current txid_current>70 billion. And strange thing is txid_current is greater than xidWrapLimit which is 3.758 billon.

How to find out, when it will raise the Warning about wraparound warn limit.

(using Pg9.5)

3

So, the xidWarnLimit is 3.747 billon where my current txid_current>70 billion. And strange thing is txid_current is greater than xidWrapLimit which is 3.758 billon.

You are presumably looking at the raw return of txid_current(), which is not a 32-bit value like the XID Limits you are looking at. Instead, it is

a 64-bit format that is extended with an "epoch" counter so it will not wrap around during the life of an installation.

(ref). You can coax a 32-bit value out of txid_current() which you can compare against your xidWrapLimit, xidStopLimit, etc. like this:

SELECT txid_current() % (2^32)::bigint;

H/T to Erwin for this tip. Remember that these XID values wrap around at 2^32.

For what it's worth, I think that blog post you linked to is excessively complicating this topic. To watch out for XID wraparound, you really just need to check:

SELECT max(age(datfrozenxid)) FROM pg_database;

and raise an alarm if that max. age is over 1 Billion or so. And if it gets close to 2 Billion you are in imminent danger! You can use a tool like check_postgres.pl which will handle this check for you with some configurable thresholds.

  • can't I store rows more than 2^32 ? If I have more rows than 2^32, what would be the solution? – Spike Jun 8 '18 at 14:03
  • Yes, you can definitely store more than 2^32 rows. Postgres used to have a page summarizing limits which is gone now, but see the summary in this answer. Multiple rows will simply have the same XID if inserted in the same transaction. And if you tried to insert one row in each of 2^32 transactions, autovacuum would kick in and clean up XIDs for you. – Josh Kupershmidt Jun 8 '18 at 15:30
  • If I inserted one row in each of 2^32 and all are live, what would be the situation once it reached? – Spike Jun 8 '18 at 20:35
  • Read about FrozenTransactionId at postgresql.org/docs/9.4/static/routine-vacuuming.html , I think that's what you're wondering about. – Josh Kupershmidt Jun 8 '18 at 21:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.