postgresql - How to interpret PosgreSQL txid_current() value

Question

I have the below psql statements:

Assumption :initial txid: a

select txid_current();
----------------------
a+1

begin;
insert into tab( v1,v2);
insert into tab (v3,v4);
commit;

select txid_current();
----------------------
a+3

Why do I see the transaction ID as a+3 shouldn't it be a+2?

How does txid_current work?

Is there any effective way where I could only return the current txid without the additional increment ?

Answer 1

Key points to understand:

• Everything is in a transaction. If you don't explicitly create one with BEGIN and COMMIT (or ROLLBACK) one is created for you just for that statement.

• Read-only SELECTs don't get a full transaction ID, they only get a virtual transaction ID. So even though it's a transaction, SELECT 1; or whatever doesn't increment the transaction ID counter.

• Calling txid_current() forces the allocation of a transaction ID if one wasn't already allocated. So a read-only transaction will now have a transaction ID, where it previously wouldn't.

Of course, txids are also allocated across sessions. In practice your example above might get txid's of a+1 and a+429 if the database is busy.

It's generally not wise to use the transaction ID for anything at the application level. In particular:

Treat xmin and xmax as internal system level fields, and treat the result of txid_current() as a meaningless numeric value.

Details on correct and incorrect uses for xids

In particular you should never:

• Compare xids by numeric value to draw any sort of conclusion about their ordering;
• Add or subtract transaction IDs;
• Sort transaction IDs;
• Increment or decrement transaction IDs
• Compare a 32-bit xid typed field with a 64-bit bigint epoch-extended xid, even for equality.

So from an application perspective xids are neither monotonic nor ordinal.

You can safely:

• compare two 64-bit epoch-extended xids for equality or inequality; and
• pass xids to txid_status(...) and other functions documented as taking an xid

Beware: PostgreSQL uses 32-bit narrow xids like the xid type, and 64-bit epoch-extended xids typically represented as bigint like those returned by txid_current(). Comparing these for equality will generally seem to work on a new database install, but once the first epoch wraparound has occurred and they'll no longer be equal. Pg doesn't even give you an easy way to see the xid epoch at the SQL level; you have to:

select (txid_current() >> 32) AS xid_epoch;

to get the upper 32 bits of the epoch-extended xid reported by txid_current().

So ... whatever you are trying to do, it's likely that the transaction ID is not the right way to do it.