Transaction ID and Snapshot information functions

1. Overview

I recently investigated one internal issue which was related with snapshot and found there were some changes on transaction id and snapshot information functions in PostgreSQL. Here, I am trying to share what I have learned.

Before PostgreSQL 13, all transaction id and snapshot related public functions were named as txid_xxx_yyy, such as,
txid_current(), which returns the current toplevel transaction ID.
txid_current_if_assigned(), which is similar to txid_current() but doesn’t assign a new xid if there isn’t one.
txid_current_snapshot(), which returns current snapshot in txid format with only top-transaction XIDs.
txid_status(), which reports the status of a recent transaction ID.

Started from PostgreSQL 13, the naming convention of these snapshot public functions txid_xxx_yyy has been changed to something like, pg_xxx_xact_yyy correspondingly. For example, txid_current() is replaced by pg_current_xact_id(), and txid_current_if_assigned() has been renamed to pg_current_xact_id_if_assigned(), etc.

commit 4c04be9b05ad2ec5acd27c3417bf075c13cab134 (HEAD -> xid8funcs)
Author: Thomas Munro <tmunro@postgresql.org>
Date:   Tue Apr 7 11:33:56 2020 +1200

    Introduce xid8-based functions to replace txid_XXX.
    
    The txid_XXX family of fmgr functions exposes 64 bit transaction IDs to
    users as int8.  Now that we have an SQL type xid8 for FullTransactionId,
    define a new set of functions including pg_current_xact_id() and
    pg_current_snapshot() based on that.  Keep the old functions around too,
    for now.
    
    It's a bit sneaky to use the same C functions for both, but since the
    binary representation is identical except for the signedness of the
    type, and since older functions are the ones using the wrong signedness,
    and since we'll presumably drop the older ones after a reasonable period
    of time, it seems reasonable to switch to FullTransactionId internally
    and share the code for both.

Introduce xid8-based functions to replace txid_XXX.

An official documentation regarding these functions can be found at Transaction ID and Snapshot Information Functions at Table 9.76. Transaction ID and Snapshot Information Functions.

2. how to use snapshot public functions

Since PostgreSQL has provided us so many public functions for end users to check the transaction id and snapshot information in details, sometimes, we need to know how to use these funtions in a simple query to have better understanding of the ongoing transactions, visibilities, and snapshots. Here, I have some simple examples to share.

first, let’s create a simple table like below,
postgres=# create table tbl01 (a int, b text);
CREATE TABLE

to find out the current transaction id, you can simply do select pg_current_xact_id(); in a psql console,
postgres=# select pg_current_xact_id();
pg_current_xact_id

(1 row)
Here, the number 734 is the current transaction id. Each time, when you run such a query, the transaction id will increase by one.

postgres=# select pg_current_xact_id();
pg_current_xact_id

(1 row)

If you want to know the current transaction id, then use function pg_current_xact_id_if_assigned(). Obversely, as the document indicated, if you are not within a transaction, this function won’t return any transaction id.
postgres=# select pg_current_xact_id_if_assigned();
pg_current_xact_id_if_assigned

(1 row)

However, if start a transaction with begin, followed a simple insert query like below, and then run the function pg_current_xact_id_if_assigned() again, you should be able to find out your current transaction id.
postgres=# begin ;
BEGIN
postgres=# insert into tbl01 values(1,’hello world’);
INSERT 0 1
postgres=# select pg_current_xact_id_if_assigned();
pg_current_xact_id_if_assigned

(1 row)

The function pg_current_snapshot() will return current top level snapshot in a format like, xmin:xmax:xid1,xid2. For example,
postgres=*# select pg_current_snapshot();
pg_current_snapshot

736:736:
(1 row
In this case, the PostgreSQL server has only one ongoing transaction which is 736.

One of the use cases for end user is that a user may want to check the tuples visibilities using pg_current_snapshot and pg_current_xact_id_if_assigned. For example, start two psql consoles with begin and followed by one simple insert operation, then check the current transaction id and current snapshots.

postgres=*# select pg_current_xact_id_if_assigned();
pg_current_xact_id_if_assigned

(1 row)

postgres=*# select pg_current_snapshot();
pg_current_snapshot

736:739:737
(1 row)
In the first console, it tells that the current transactions id is 736, and there is another ongoing transaction 737 and any tuples update made by transaction 737 is not visibile to this console yet.

postgres=*# select pg_current_xact_id_if_assigned();
pg_current_xact_id_if_assigned

(1 row)

postgres=*# select pg_current_snapshot();
pg_current_snapshot

736:739:736
(1 row)

If you check the assigned transaction id and current snapshot in the second console, it will tell you a similar information. With these two public fucntions and plus an extension pageinspect, it can help debug the tuple visibilities issue.

3. Summary

In this blog, we discussed the changes made for those transaction id and snapshot public functions, and shared a few simple queries about how to use these snapshots related public funtions. It might be useful if you want to have a better understanding of the ongoing transactions and snapshots.