PostgreSQL – How to Compare xmin and txid_current() After Transaction ID Wraparound

postgresqlpostgresql-9.5transaction

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.

Best Answer

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:

Related Question