12

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);
CREATE TABLE
# INSERT INTO test VALUES ('test') RETURNING txid_current();
 txid_current 
--------------
   674500
(1 row)
INSERT 0 1
# SELECT xmin FROM test;
  xmin  
--------
 674500
(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. – Daniel Vérité Aug 10 '16 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 Aug 10 '16 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 Aug 10 '16 at 20:31
5

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:

SELECT * FROM test
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, privacy policy and cookie policy

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