I have the below psql statements:
Assumption :initial txid: a
insert into tab( v1,v2);
insert into tab (v3,v4);
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 ?
Key points to understand:
Everything is in a transaction. If you don't explicitly create one with
ROLLBACK) one is created for you just for that statement.
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.
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.
Do not use the transaction ID for anything at the application level. It is an internal system level field. Whatever you are trying to do, it's likely that the transaction ID is not the right way to do it.