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, for example,
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.

A detailed changes log is showing below about the reasons.

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.

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 functions in a simple query to help us have a better understanding of the ongoing transactions, visibilities, and snapshots. Here are 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 run select pg_current_xact_id(); in a psql console,

postgres=# select pg_current_xact_id();
 pg_current_xact_id 
--------------------
                734
(1 row)

Here, the number 734 is the current transaction id. Each time, when you run such a query, the transaction id will be increased by one.

postgres=# select pg_current_xact_id();
 pg_current_xact_id 
--------------------
                735
(1 row)

If you want to know the current transaction id, then use the 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)

To make the function has a meaningful output, start a transaction with begin, followed a simple insert query like below, and then run the function pg_current_xact_id_if_assigned() again, now, you should be able to find out the 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 
--------------------------------
                            736
(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 example above, 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 a 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 
--------------------------------
                            736
(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 visible to this console.

postgres=*# select pg_current_xact_id_if_assigned();
 pg_current_xact_id_if_assigned 
--------------------------------
                            737
(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 functions and plus an extension pageinspect, it can help you 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 transaction id and snapshots related public functions. I hope it can help when someone wants to have a better understanding of the ongoing transactions and snapshots.

David Zhang

A software developer specialized in C/C++ programming with experience in hardware, firmware, software, database, network, and system architecture. Now, working in HighGo Software Inc, as a senior PostgreSQL architect.