5.1 Transaction ID

Whenever a transaction begins, a unique identifier, referred to as a transaction id (txid), is assigned by the transaction manager. 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:

testdb=# BEGIN;
BEGIN
testdb=# SELECT txid_current();
 txid_current 
--------------
          100
(1 row)

PostgreSQL reserves the following three special txids:

  • 0 means Invalid txid.

  • 1 means Bootstrap txid, which is only used in the initialization of the database cluster.

  • 2 means Frozen txid, which is described in Section 5.10.1.

Txids can be compared with each other. For example, at the viewpoint of txid 100, txids that are greater than 100 are ‘in the future’ and are invisible from the txid 100; txids that are less than 100 are ‘in the past’ and are visible (Fig. 5.1 a)).

Fig. 5.1. Transaction ids in PostgreSQL.

Since the txid space is insufficient in practical systems, PostgreSQL treats the txid space as a circle. The previous 2.1 billion txids are ‘in the past’, and the next 2.1 billion txids are ‘in the future’ (Fig. 5.1 b).

Note that the so-called txid wraparound problem is described in Section 5.10.1.

Info

Note that BEGIN command does not be assigned a txid.

In PostgreSQL, when the first command is executed after a BEGIN command executed, a tixd is assigned by the transaction manager, and then the transaction starts.