5.1 Transaction ID

At the start of every transaction, the transaction manager assigns a unique identifier known as a transaction ID (txid). PostgreSQL’s txid is a 32-bit unsigned integer, with a maximum value of approximately 4.2 billion (thousand millions).

Executing the built-in txid_current() function after a transaction begins returns the current txid, as shown below:

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 (Figure 5.1 a)).

Figure 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’ (Figure 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.