Do subtransactions need to assign their own XID?

Subtransaction, also known as “nested transaction”, is a transaction started by instruction within the scope of an already started transaction. This feature allows users to partially rollback a transaction, which is helpful in many cases: fewer steps need to be repeated to retry the action if some error occurs.

Then, do subtransactions need to assign their own transaction IDs? Is there a risk of running out of transaction IDs for too many subtransactions?

Redrock Postgres implemented subtransactions based on the undo record position, these subtransactions are only temporary marks in a transaction, they do not need to assign their own transaction IDs, and have no risk to the system.

There are following cases using subtransactions:

• The SQL Command SAVEPOINT defines a new savepoint within the current transaction.
• BEGIN / EXCEPTION WHEN .. / END blocks in PL/pgSQL procedure.

Let’s start a transaction block, use SAVEPOINT to create multiple subtransactions in the transaction, and insert a record into the table test1 in each subtransaction.

CREATE TABLE test1(i integer);

BEGIN;

SELECT pg_current_xact_id();
 pg_current_xact_id
--------------------
           (6,15,1)

INSERT INTO test1 VALUES (1);
SAVEPOINT s1;
INSERT INTO test1 VALUES (2);
SAVEPOINT s2;
SAVEPOINT s3;
INSERT INTO test1 VALUES (3);
COMMIT;

Let’s query the system columns rowxid, rowtime of the tuples in the table to view the transaction ID used by each subtransaction.

SELECT ctid, rowxid, rowtime, i FROM test1;
 ctid  |  rowxid  | rowtime | i 
-------+----------+---------+---
 (0,1) | (6,15,1) |     946 | 1
 (0,2) | (6,15,1) |     946 | 2
 (0,3) | (6,15,1) |     946 | 3

In the above example, we see that the subtransactions use the same transaction ID as the transaction block, and they do not need to be assigned a separate transaction ID.