what does RUNNING_XACTS nextXid do pg_wal?

All we need is an easy explanation of the problem, so here it is.

---------------------------------------------------------------------
select pg_current_wal_insert_lsn(); --yield C/2F3533D8
begin;
select pg_current_wal_insert_lsn(); --yield C/2F3533D8
update wal set id = id + 1;
select pg_current_wal_insert_lsn(); --yield C/2F3534D8
commit ;
select pg_current_wal_insert_lsn(); --yield  C/2F353500

select 'C/2F353500'::pg_lsn - 'C/2F3533D8'::pg_lsn; --yield 296
select file_name, upper(to_hex(file_offset)) file_offset from pg_walfile_name_offset('C/2F3533D8');
/*
        file_name         | file_offset
--------------------------+-------------
 000000010000000C0000002F | 3533D8
            */

select * from pg_ls_waldir() where name = '000000010000000C0000002F';
/* returning with the previous command.

             name           |   size   |       modification
--------------------------+----------+---------------------------
 000000010000000C0000002F | 16777216 | 2022-03-11 12:20:13+05:30
 */

then run in terminal.

/usr/lib/postgresql/14/bin/pg_waldump -p /var/lib/postgresql/14/main/pg_wal -s C/2F3533D8 -e  C/2F353500#

returns

rmgr: Heap        len (rec/tot):     65/   197, tx:       1506, lsn: C/2F3533D8, prev C/2F3533A0, desc: HOT_UPDATE off 2 xmax 1506 flags 0x00 ; new off 3 xmax 0, blkref #0: rel 1663/24598/41820 blk 0 FPW
rmgr: Standby     len (rec/tot):     54/    54, tx:          0, lsn: C/2F3534A0, prev C/2F3533D8, desc: RUNNING_XACTS nextXid 1507 latestCompletedXid 1505 oldestRunningXid 1506; 1 xacts: 1506
rmgr: Transaction len (rec/tot):     34/    34, tx:       1506, lsn: C/2F3534D8, prev C/2F3534A0, desc: COMMIT 2022-03-11 12:20:03.642221 IST

–there is 3 records, differ with the book(https://prnt.sc/-OC1lF6kUxXM). Then I wonder what’s the second record do?

rmgr: Standby  ... desc: RUNNING_XACTS nextXid

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

That WAL record has nothing to do with your transaction. It is written in the course of a checkpoint.

If wal_level is replica or higher, this record that contains details of the current snapshot (all active transactions and ACCESS EXCLUSIVE locks) is written to WAL. This information is necessary so that queries on the standby know what they can see.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply