what does RUNNING_XACTS nextXid do pg_wal?

by

The question:

---------------------------------------------------------------------
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

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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.


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 Comment