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 ?
If you want to know the current transaction id, then use the function pg_current_xact_id_if_assigned() .
Transactions are identified with a xid (transaction, or “xact” ID). As an optimization, Postgres will only assign a transaction a xid if it starts to modify data because it's only at that point where other processes need to start tracking its changes.
PostgreSQL's txid is a 32-bit unsigned integer, approximately 4.2 billion (thousand millions). If you execute the built-in txid_current() function after a transaction starts, the function returns the current txid as follows.
Tuple header To this end, each version is labeled with its effective «time» ( xmin ) and expiration «time» ( xmax ). Quotation marks denote that a special incrementing counter is used rather than the time itself. And this counter is the transaction identifier.
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 SELECT
s 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.
In particular you should never:
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:
txid_status(...)
and other functions documented as taking an xidBeware: 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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With