Re: Add a perl function in Cluster.pm to generate WAL

On Mon, Dec 18, 2023, at 2:39 AM, Bharath Rupireddy wrote:

I think you don't understand the suggestion proposed by Michael and Kyotaro. If

you do a comparison with the following SQL commands:

euler=# select pg_walfile_name(pg_current_wal_lsn());

     pg_walfile_name      

--------------------------

000000010000000000000040

(1 row)

euler=# select pg_logical_emit_message(true, 'prefix', 'message4');

pg_logical_emit_message 

-------------------------

0/400000A8

(1 row)

euler=# select pg_switch_wal();

pg_switch_wal 

---------------

0/400000F0

(1 row)

euler=# create table cc (b int);

CREATE TABLE

euler=# drop table cc;

DROP TABLE

euler=# select pg_switch_wal();

pg_switch_wal 

---------------

0/41017C88

(1 row)

euler=# select pg_walfile_name(pg_current_wal_lsn());

     pg_walfile_name      

--------------------------

000000010000000000000041

(1 row)

You get

$ pg_waldump 000000010000000000000040

rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/40000028, prev 0/3F0001C0, desc: RUNNING_XACTS nextXid 295180 latestCompletedXid 295179 oldestRunningXid 295180

rmgr: LogicalMessage len (rec/tot):     65/    65, tx:     295180, lsn: 0/40000060, prev 0/40000028, desc: MESSAGE transactional, prefix "prefix"; payload (8 bytes): 6D 65 73 73 61 67 65 34

rmgr: Transaction len (rec/tot):     46/    46, tx:     295180, lsn: 0/400000A8, prev 0/40000060, desc: COMMIT 2023-12-18 08:35:06.821322 -03

rmgr: XLOG        len (rec/tot):     24/    24, tx:          0, lsn: 0/400000D8, prev 0/400000A8, desc: SWITCH 

$ pg_waldump 000000010000000000000041

rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/41000028, prev 0/400000D8, desc: RUNNING_XACTS nextXid 295181 latestCompletedXid 295180 oldestRunningXid 295181

rmgr: Storage     len (rec/tot):     42/    42, tx:          0, lsn: 0/41000060, prev 0/41000028, desc: CREATE base/33287/88102

rmgr: Heap2       len (rec/tot):     60/    60, tx:     295181, lsn: 0/41000090, prev 0/41000060, desc: NEW_CID rel: 1663/33287/1247, tid: 14/16, cmin: 0, cmax: 4294967295, combo: 4294967295

rmgr: Heap        len (rec/tot):     54/  3086, tx:     295181, lsn: 0/410000D0, prev 0/41000090, desc: INSERT off: 16, flags: 0x00, blkref #0: rel 1663/33287/1247 blk 14 FPW

rmgr: Btree       len (rec/tot):     53/  5133, tx:     295181, lsn: 0/41000CE0, prev 0/410000D0, desc: INSERT_LEAF off: 252, blkref #0: rel 1663/33287/2703 blk 2 FPW

.

.

.

rmgr: Btree       len (rec/tot):     72/    72, tx:     295181, lsn: 0/41016E48, prev 0/41014F00, desc: INSERT_LEAF off: 111, blkref #0: rel 1663/33287/2674 blk 7

rmgr: Heap2       len (rec/tot):     69/    69, tx:     295181, lsn: 0/41016E90, prev 0/41016E48, desc: PRUNE snapshotConflictHorizon: 295177, nredirected: 0, ndead: 7, nunused: 0, redirected: [], dead: [20, 21, 22, 23, 24, 26, 27], unused: [], blkref #0: rel 1663/33287/1249 blk 17

rmgr: Transaction len (rec/tot):    385/   385, tx:     295181, lsn: 0/41016ED8, prev 0/41016E90, desc: INVALIDATION ; inval msgs: catcache 80 catcache 79 catcache 80 catcache 79 catcache 55 catcache 54 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 snapshot 2608 relcache 88102

rmgr: Standby     len (rec/tot):     42/    42, tx:     295181, lsn: 0/41017060, prev 0/41016ED8, desc: LOCK xid 295181 db 33287 rel 88102 

rmgr: Transaction len (rec/tot):    405/   405, tx:     295181, lsn: 0/41017090, prev 0/41017060, desc: COMMIT 2023-12-18 08:35:22.342122 -03; inval msgs: catcache 80 catcache 79 catcache 80 catcache 79 catcache 55 catcache 54 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 snapshot 2608 relcache 88102

rmgr: Standby     len (rec/tot):     42/    42, tx:     295182, lsn: 0/41017228, prev 0/41017090, desc: LOCK xid 295182 db 33287 rel 88102 

rmgr: Heap2       len (rec/tot):     61/    61, tx:     295182, lsn: 0/41017258, prev 0/41017228, desc: PRUNE snapshotConflictHorizon: 295177, nredirected: 0, ndead: 3, nunused: 0, redirected: [], dead: [9, 12, 15], unused: [], blkref #0: rel 1663/33287/2608 blk 3

rmgr: Heap2       len (rec/tot):     60/    60, tx:     295182, lsn: 0/41017298, prev 0/41017258, desc: NEW_CID rel: 1663/33287/1247, tid: 14/17, cmin: 4294967295, cmax: 0, combo: 4294967295

rmgr: Heap        len (rec/tot):     54/    54, tx:     295182, lsn: 0/410172D8, prev 0/41017298, desc: DELETE xmax: 295182, off: 17, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/33287/1247 blk 14

.

.

.

rmgr: Heap2       len (rec/tot):     60/    60, tx:     295182, lsn: 0/410178D8, prev 0/410178A0, desc: NEW_CID rel: 1663/33287/2608, tid: 3/24, cmin: 4294967295, cmax: 2, combo: 4294967295

rmgr: Heap        len (rec/tot):     54/    54, tx:     295182, lsn: 0/41017918, prev 0/410178D8, desc: DELETE xmax: 295182, off: 24, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/33287/2608 blk 3

rmgr: Transaction len (rec/tot):    321/   321, tx:     295182, lsn: 0/41017950, prev 0/41017918, desc: INVALIDATION ; inval msgs: catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 55 catcache 54 relcache 88102 snapshot 2608

rmgr: Transaction len (rec/tot):    469/   469, tx:     295182, lsn: 0/41017A98, prev 0/41017950, desc: COMMIT 2023-12-18 08:35:25.053905 -03; rels: base/33287/88102; dropped stats: 2/33287/88102; inval msgs: catcache 80 catcache 79 catcache 80 catcache 79 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 55 catcache 54 snapshot 2608 snapshot 2608 relcache 88102 snapshot 2608

rmgr: XLOG        len (rec/tot):     24/    24, tx:          0, lsn: 0/41017C70, prev 0/41017A98, desc: SWITCH

The difference is

euler=# select '0/400000A8'::pg_lsn - '0/40000028'::pg_lsn;

?column? 

----------

      128

(1 row)

euler=# select '0/41017A98'::pg_lsn - '0/41000028'::pg_lsn;

?column? 

----------

    96880

(1 row)

It is cheaper.