Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rFC6y-008jzS-Uu for pgsql-hackers@arkaria.postgresql.org; Mon, 18 Dec 2023 11:48:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rFC6x-008hiu-29 for pgsql-hackers@arkaria.postgresql.org; Mon, 18 Dec 2023 11:48:39 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rFC6v-008hil-H6 for pgsql-hackers@lists.postgresql.org; Mon, 18 Dec 2023 11:48:38 +0000 Received: from out2-smtp.messagingengine.com ([66.111.4.26]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rFC6r-00Cc2m-0P for pgsql-hackers@lists.postgresql.org; Mon, 18 Dec 2023 11:48:36 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.46]) by mailout.nyi.internal (Postfix) with ESMTP id DFBD55C00A2; Mon, 18 Dec 2023 06:48:29 -0500 (EST) Received: from imap49 ([10.202.2.99]) by compute2.internal (MEProxy); Mon, 18 Dec 2023 06:48:29 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=eulerto.com; h= cc:cc:content-type:content-type:date:date:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:subject :subject:to:to; s=fm3; t=1702900109; x=1702986509; bh=CpoDBLkoOc pEOelX2yh+vIARNQNWPokmktKWeEVLkoU=; b=IxOp6fi2F/OJJaPJ2oIyuWueJU Cj+xafqbO1OIJDFXaZlQyrBOvvdUxdeQrz8PHdOHRJVzjiQqxdnVBDeuE7bM4wvE nINQ3peVGES+lgsbRDZxIcs36uFaOoXpiJJVtTojxkbZofByI2NfZi+ZfzdEqnVA 2do/EYcttmBg1gqSys58e6L5ZHiRf/rTJOFHoyK2mrSkDo2+WPWKtz99bpLZ3yDR 0F+zUk+lehNpl7ZV+6I03TM7GfKRbxj7nBz4GWtooMZO/2sm09zRENPmEERoBGjg fsUqnIgWk4pCPLRT4MXC3DAEBIGNz7mE5cwA/J8/gEmP7jKWNjNuGS5RV00Q== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:subject:subject:to :to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s= fm2; t=1702900109; x=1702986509; bh=CpoDBLkoOcpEOelX2yh+vIARNQNW PokmktKWeEVLkoU=; b=PhxtIhUBVB/TTScRj22F1T/BtbOyhTMQmKwUfl+R/1Mn zI02HispLMOXt5tIlDUd6mOBtPLq2JtbnxlegmanX2IwjY5I0RZLZPVcnH3u6Gbe B6EoDd/ki7sKeI3rryW6iJu1eGDzB/51/35p7w0HfRvBWAzXV7TrS5PMR2gx6gJB D963QcKKmIdIxbEqiUINeICjjSmqyXgL/mi8Pq/60GA4FU9Wnav5cTnPQuXz8xuK 7tWIOoyIZigCNETtxTkeCe+vgrWlqzS5wFJO6ejQMO0HNFWb+Q7kYkPgcU+VKTVG I9mrgABB5KQ5on/bTLSY4hoxKWQWxcJl5ZPM1wtDMw== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvkedrvddtkedgfeduucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepofgfggfkjghffffhvfevufgtsegrtderreerredtnecuhfhrohhmpedfgfhu lhgvrhcuvfgrvhgvihhrrgdfuceovghulhgvrhesvghulhgvrhhtohdrtghomheqnecugg ftrfgrthhtvghrnhepgfffjedviedvkedviedvteduieekudeivedthffhuddvteegleet ieffkeeghfdvnecuffhomhgrihhnpegvnhhtvghrphhrihhsvggusgdrtghomhenucevlh hushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegvuhhlvghrsegv uhhlvghrthhordgtohhm X-ME-Proxy: Feedback-ID: i0c21471d:Fastmail Received: by mailuser.nyi.internal (Postfix, from userid 501) id 5A26C15A0092; Mon, 18 Dec 2023 06:48:29 -0500 (EST) X-Mailer: MessagingEngine.com Webmail Interface User-Agent: Cyrus-JMAP/3.9.0-alpha0-1283-g327e3ec917-fm-20231207.002-g327e3ec9 MIME-Version: 1.0 Message-Id: <3a6978d6-e4fc-4014-b5f7-45db53d8fa53@app.fastmail.com> In-Reply-To: References: <20230615.134015.1421242753635015365.horikyota.ntt@gmail.com> Date: Mon, 18 Dec 2023 08:48:09 -0300 From: "Euler Taveira" To: "Bharath Rupireddy" , "Michael Paquier" Cc: "Kyotaro Horiguchi" , "Alvaro Herrera" , "Andrew Dunstan" , pgsql-hackers@lists.postgresql.org Subject: Re: Add a perl function in Cluster.pm to generate WAL Content-Type: multipart/alternative; boundary=522ff8e477dd40188a87a5382b869c5f List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --522ff8e477dd40188a87a5382b869c5f Content-Type: text/plain On Mon, Dec 18, 2023, at 2:39 AM, Bharath Rupireddy wrote: > Rebase needed, attached v3 patch. 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. -- Euler Taveira EDB https://www.enterprisedb.com/ --522ff8e477dd40188a87a5382b869c5f Content-Type: text/html Content-Transfer-Encoding: quoted-printable
On Mon, Dec 18,= 2023, at 2:39 AM, Bharath Rupireddy wrote:
Rebase needed, attached v3 patch.

I think you don't understand the su= ggestion proposed by Michael and Kyotaro. If
you do a comp= arison with the following SQL commands:

eul= er=3D# select pg_walfile_name(pg_current_wal_lsn());
 = ;    pg_walfile_name      <= br>
--------------------------
00000001000000000= 0000040
(1 row)

euler=3D# sel= ect pg_logical_emit_message(true, 'prefix', 'message4');
p= g_logical_emit_message 
-------------------------
=
0/400000A8
(1 row)

euler=3D# select pg_switch_wal();
pg_switch_wal 
=
---------------
0/400000F0
(1 row= )

euler=3D# create table cc (b int);
CREATE TABLE
euler=3D# drop table cc;
DROP TABLE
euler=3D# select pg_switch_wal();
pg_switch_wal 
---------------
0/410= 17C88
(1 row)

euler=3D# selec= t pg_walfile_name(pg_current_wal_lsn());
   = ;  pg_walfile_name      
--------------------------
000000010000000000000041
<= /div>
(1 row)

You get
$ pg_waldump 000000010000000000000040
rmgr: St= andby     len (rec/tot):     50/=     50, tx:       &nbs= p;  0, lsn: 0/40000028, prev 0/3F0001C0, desc: RUNNING_XACTS nextXi= d 295180 latestCompletedXid 295179 oldestRunningXid 295180
rmgr: LogicalMessage len (rec/tot):     65/ &n= bsp;  65, tx:     295180, lsn: 0/40000060, prev= 0/40000028, desc: MESSAGE transactional, prefix "prefix"; payload (8 by= tes): 6D 65 73 73 61 67 65 34
rmgr: Transaction len (rec/t= ot):     46/    46, tx:  &n= bsp;  295180, lsn: 0/400000A8, prev 0/40000060, desc: COMMIT 2023-1= 2-18 08:35:06.821322 -03
rmgr: XLOG    = ;    len (rec/tot):     24/ &nbs= p;  24, tx:          0= , lsn: 0/400000D8, prev 0/400000A8, desc: SWITCH 
$ pg_waldump 000000010000000000000041
rmgr: St= andby     len (rec/tot):     50/=     50, tx:       &nbs= p;  0, lsn: 0/41000028, prev 0/400000D8, desc: RUNNING_XACTS nextXi= d 295181 latestCompletedXid 295180 oldestRunningXid 295181
rmgr: Storage     len (rec/tot):   &= nbsp; 42/    42, tx:      &= nbsp;   0, lsn: 0/41000060, prev 0/41000028, desc: CREATE base= /33287/88102
rmgr: Heap2     &nbs= p; len (rec/tot):     60/    60, tx:&= nbsp;    295181, lsn: 0/41000090, prev 0/41000060, desc: = NEW_CID rel: 1663/33287/1247, tid: 14/16, cmin: 0, cmax: 4294967295, com= bo: 4294967295
rmgr: Heap     &nb= sp;  len (rec/tot):     54/  3086, tx:&nbs= p;    295181, lsn: 0/410000D0, prev 0/41000090, desc: INS= ERT off: 16, flags: 0x00, blkref #0: rel 1663/33287/1247 blk 14 FPW
<= /div>
rmgr: Btree       len (rec/tot):=      53/  5133, tx:     295= 181, lsn: 0/41000CE0, prev 0/410000D0, desc: INSERT_LEAF off: 252, blkre= f #0: rel 1663/33287/2703 blk 2 FPW
.
.
<= /div>
.
rmgr: Btree      = ; len (rec/tot):     72/    72, tx:&n= bsp;    295181, lsn: 0/41016E48, prev 0/41014F00, desc: I= NSERT_LEAF off: 111, blkref #0: rel 1663/33287/2674 blk 7
= rmgr: Heap2       len (rec/tot): &nbs= p;   69/    69, tx:     295= 181, lsn: 0/41016E90, prev 0/41016E48, desc: PRUNE snapshotConflictHoriz= on: 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/41016E= D8, prev 0/41016E90, desc: INVALIDATION ; inval msgs: catcache 80 catcac= he 79 catcache 80 catcache 79 catcache 55 catcache 54 catcache 7 catcach= e 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 ca= tcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 snapshot= 2608 relcache 88102
rmgr: Standby    = len (rec/tot):     42/    42, tx:&nb= sp;    295181, lsn: 0/41017060, prev 0/41016ED8, desc: LO= CK xid 295181 db 33287 rel 88102 
rmgr: Transaction l= en (rec/tot):    405/   405, tx:  &nb= sp;  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 ca= tcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache= 6 catcache 7 catcache 6 catcache 7 catcache 6 snapshot 2608 relcache 88= 102
rmgr: Standby     len (rec/tot):&n= bsp;    42/    42, tx:   &n= bsp; 295182, lsn: 0/41017228, prev 0/41017090, desc: LOCK xid 295182 db = 33287 rel 88102 
rmgr: Heap2    &= nbsp;  len (rec/tot):     61/   = 61, tx:     295182, lsn: 0/41017258, prev 0/4101722= 8, 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   &nb= sp;   len (rec/tot):     60/  &n= bsp; 60, tx:     295182, lsn: 0/41017298, prev 0/410= 17258, desc: NEW_CID rel: 1663/33287/1247, tid: 14/17, cmin: 4294967295,= cmax: 0, combo: 4294967295
rmgr: Heap   &n= bsp;    len (rec/tot):     54/ &= nbsp;  54, tx:     295182, lsn: 0/410172D8, pre= v 0/41017298, desc: DELETE xmax: 295182, off: 17, infobits: [KEYS_UPDATE= D], flags: 0x00, blkref #0: rel 1663/33287/1247 blk 14
.
.
.
rmgr: Heap2  &nbs= p;    len (rec/tot):     60/ &nb= sp;  60, tx:     295182, lsn: 0/410178D8, prev = 0/410178A0, desc: NEW_CID rel: 1663/33287/2608, tid: 3/24, cmin: 4294967= 295, cmax: 2, combo: 4294967295
rmgr: Heap  &nbs= p;     len (rec/tot):     54/&nb= sp;   54, tx:     295182, lsn: 0/41017918,= prev 0/410178D8, desc: DELETE xmax: 295182, off: 24, infobits: [KEYS_UP= DATED], 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, d= esc: INVALIDATION ; inval msgs: catcache 7 catcache 6 catcache 7 catcach= e 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 ca= tcache 7 catcache 6 catcache 7 catcache 6 catcache 55 catcache 54 relcac= he 88102 snapshot 2608
rmgr: Transaction len (rec/tot):&nb= sp;   469/   469, tx:     295182= , lsn: 0/41017A98, prev 0/41017950, desc: COMMIT 2023-12-18 08:35:25.053= 905 -03; rels: base/33287/88102; dropped stats: 2/33287/88102; inval msg= s: catcache 80 catcache 79 catcache 80 catcache 79 catcache 7 catcache 6= catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catca= che 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 (re= c/tot):     24/    24, tx:  = ;        0, lsn: 0/41017C70, prev 0/4= 1017A98, desc: SWITCH

The difference is
=

euler=3D# select '0/400000A8'::pg_lsn - '0/400= 00028'::pg_lsn;
?column? 
----------
      128
(1 row)
=

euler=3D# select '0/41017A98'::pg_lsn - '0/410= 00028'::pg_lsn;
?column? 
----------
    96880
(1 row)


It is cheaper.


--
Euler Taveira
EDB   https://ww= w.enterprisedb.com/

--522ff8e477dd40188a87a5382b869c5f--