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.