Besides its regular columns, Postgres tables also have various system columns available. One of them, xmin, stores the transaction ID used to create a row. Its data type is xid, a four byte integer that wraps around at some point (i.e. not necessarily unique). The function txid_current() in turn returns the current transaction ID, but as bigint, because it "is extended with an "epoch" counter so it will not wrap around during the life of an installation" (to quote the manual).

If transactions wraparound did not yet happen, both values seem to match:

# CREATE TABLE test (label text);
# INSERT INTO test VALUES ('test') RETURNING txid_current();
(1 row)
# SELECT xmin FROM test;
(1 row)

But I wonder: are these two values always comparable? As far as I understand, txid_current() will continue to deliver unique values after transaction ID wraparound (at most 2^32 transactions) and xmin will start from zero. This means both start to return different values at that point?

And if this is true, is there a way to extract regular xid of a txid_current() result so that it would match xmin entries in a table (e.g. casting txid_current() to integer)?

Edit: Make it clearer that I care about what happens after a transaction ID wraparound, which very likely happens long before 2^32 transactions. Thanks to Daniel Vérité for noting this in the comments.

  • 1
    You're ignoring the fact that the system will VACUUM FREEZE and overwrite the xmin on rows long before the 2^32 wraparound. Check out Freezing Your Tuples Off for an overview on the subject. Commented Aug 10, 2016 at 13:01
  • True, I left this fact out of the question, thanks for pointing to it. And indeed freezing will happen long before 2^32. However, even with old xmin becoming frozen the question still stands how newer (regular) xmin compare to a then executed txid_current().
    – tomka
    Commented Aug 10, 2016 at 15:36
  • 1
    It is worth noting that PostgreSQL will shut down if there are less than 1 milion transactions left until wraparound.
    – user103153
    Commented Aug 10, 2016 at 20:31

1 Answer 1


You can strip the added epoch to match the value in xmin, i.e. extract the 4-byte integer from the bigint. Since xmin is type xid and not (signed!) integer, we compare the text representation instead:

WHERE  xmin::text = (txid_current() % (2^32)::bigint)::text;

Detailed explanation:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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