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 1r0no9-007uTb-1i for pgsql-general@arkaria.postgresql.org; Wed, 08 Nov 2023 19:01:45 +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 1r0no7-006zWb-Dp for pgsql-general@arkaria.postgresql.org; Wed, 08 Nov 2023 19:01:43 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r0no6-006zWR-Mr for pgsql-general@lists.postgresql.org; Wed, 08 Nov 2023 19:01:43 +0000 Received: from smtp6-g21.free.fr ([212.27.42.6]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r0nny-004bsr-QZ for pgsql-general@lists.postgresql.org; Wed, 08 Nov 2023 19:01:41 +0000 Received: from zimbra59-e10.priv.proxad.net (unknown [172.20.243.209]) by smtp6-g21.free.fr (Postfix) with ESMTP id BCE5678035B for ; Wed, 8 Nov 2023 20:01:31 +0100 (CET) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=free.fr; s=smtp-20201208; t=1699470091; bh=GZut7sqR2Rb6ZnN+J9n8LIMdVkA7YYNLnAEnGjHs0Cc=; h=Date:From:To:In-Reply-To:Subject:From; b=FdfgbWf3Eto4xbKloBXGqFOMdwp+HnUNn1ApDNTwU+WR5crUeOhqxa50EMmisKwMC yq2QF+OG36I4HldKt20VqStUWHOGnr8I5StMQEwi1nmgWFtOS79QlgBgWuerYG4J9J Sdk/zdhs0vJiTVIdIFFA5NQdYNMBB1rES+C3+7Sw3OhSpKykJ4nNpghsRf0UdJV0Yu /6wuQAL6p1vhO9DMsGeiGs+4DMWskS8Pt1yZAGL/CRKKT55bPLBWsHmKqJr6aEzY8e wqDSrCs47g9jyTdmTk3bJdXQGm6CWZfOZGwhNIuC7nCBVg/VSsBWSRqE2Sh+E2T++J 5bgnEdmbH5ZDg== Date: Wed, 8 Nov 2023 20:01:31 +0100 (CET) From: ledieudesmammouths@free.fr To: pgsql-general@lists.postgresql.org Message-ID: <1572490564.304409181.1699470091391.JavaMail.root@zimbra59-e10.priv.proxad.net> In-Reply-To: <769465948.304258951.1699467957521.JavaMail.root@zimbra59-e10.priv.proxad.net> Subject: Logical replication (pgoutput plugin) in streaming mode: peek() always starts from beginning of transaction, not from latest stream block MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_304409180_1453776227.1699470091390" X-Originating-IP: [82.132.225.87] X-Mailer: Zimbra 7.2.0-GA2598 (ZimbraWebClient - GC118 (Win)/7.2.0-GA2598) X-Authenticated-User: ledieudesmammouths@free.fr List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_304409180_1453776227.1699470091390 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit Hi everyone, When using logical replication with the pgoutput plugin, on PG 16,we do the following: 1) SELECT * FROM pg_logical_slot_peek_binary_changes('test_slot_v1', null, null,'publication_names', 'cdc', 'proto_version', '4', 'streaming', 'false') 2) Get LSN of last row (Commit) 3) SELECT * FROM pg_replication_slot_advance('test_slot_v1', ); 4) Repeat. And this works perfectly fine when streaming = false. When turning on streaming the expectation is that the same thing happens, except the the LSN being passed to pg_replication_slot_advance() is for a Stream End record. On the next call to pg_logical_slot_peek_binary_changes() we should get the subsequent Stream Start record. But instead, the stream starts over from the transaction Begin record. Observe: *** Demo starts *** *** Initially there are no changes, peek() returns nothing: *** => SELECT * FROM pg_logical_slot_peek_binary_changes('test_slot_v1', null, null,'publication_names', 'cdc', 'proto_version', '4', 'streaming', 'true') WHERE SUBSTRING(data, 1,1) NOT IN ('\x49', '\x44'); lsn | xid | data -----+-----+------ (0 rows) *** Slot status: *** => SELECT slot_name, active, restart_lsn, confirmed_flush_lsn FROM pg_replication_slots; slot_name | active | restart_lsn | confirmed_flush_lsn --------------+--------+-------------+--------------------- test_slot_v1 | f | 2/98CE060 | 2/98CE060 (1 rows) *** Now make some changes (delete then insert a bunch of records) and call peek() *** *** The predicate filters out Delete and Insert records, leaving Stream Start (\x53 = S), *** *** Relation (\x52 = R), Stream End (\x45 = E), and Stream Commit (\x63 = c) *** abinitio=> SELECT * FROM pg_logical_slot_peek_binary_changes('test_slot_v1', null, null,'publication_names', 'cdc', 'proto_version', '4', 'streaming', 'true') WHERE SUBSTRING(data, 1,1) NOT IN ('\x49', '\x44'); lsn | xid | data ------------+------+-------------------------------------------------------------------------------------------------------------------------------------- 2/A222A20 | 1112 | \x530000045801 2/A222A20 | 1112 | \x52000004590000402a7075626c6963007265706c69636174696f6e5f746573745f7631006400020169640000000017ffffffff006e616d650000000019ffffffff 2/C141BE8 | 1112 | \x45 2/C141C28 | 1112 | \x530000045800 2/DF598D8 | 1112 | \x45 2/DF59950 | 1112 | \x630000045800000000020df59918000000020df599500002aca72900f8a8 2/DF59950 | 1114 | \x530000045a01 2/DF59950 | 1114 | \x520000045a0000402a7075626c6963007265706c69636174696f6e5f746573745f7631006400020169640000000017ffffffff006e616d650000000019ffffffff 2/108918D0 | 1114 | \x45 2/108918D0 | 1114 | \x530000045a00 2/131E1310 | 1114 | \x45 2/131E1310 | 1114 | \x530000045a00 2/137D7768 | 1114 | \x45 2/137E8448 | 1114 | \x630000045a0000000002137e841800000002137e84480002aca729812c96 (14 rows) *** It was a peek() so the status is unchanged: *** => SELECT slot_name, active, restart_lsn, confirmed_flush_lsn FROM pg_replication_slots; slot_name | active | restart_lsn | confirmed_flush_lsn --------------+--------+-------------+--------------------- test_slot_v1 | f | 2/98CE060 | 2/98CE060 (1 rows) *** Now advance the slot to the first Stream End record: *** => SELECT * FROM pg_replication_slot_advance('test_slot_v1', '2/C141BE8'); slot_name | end_lsn --------------+----------- test_slot_v1 | 2/C141BE8 (1 row) *** confirmed_flush_lsn is updated as expected: **** => SELECT slot_name, active, restart_lsn, confirmed_flush_lsn FROM pg_replication_slots; slot_name | active | restart_lsn | confirmed_flush_lsn --------------+--------+-------------+--------------------- test_slot_v1 | f | 2/9B09D10 | 2/C141BE8 (1 rows) *** Now peek() again. It is starting from earlier than confirmed_flush_lsn: *** => SELECT * FROM pg_logical_slot_peek_binary_changes('test_slot_v1', null, null,'publication_names', 'cdc', 'proto_version', '4', 'streaming', 'true') WHERE SUBSTRING(data, 1,1) NOT IN ('\x49', '\x44'); lsn | xid | data ------------+------+-------------------------------------------------------------------------------------------------------------------------------------- 2/A222A20 | 1112 | \x530000045801 2/A222A20 | 1112 | \x52000004590000402a7075626c6963007265706c69636174696f6e5f746573745f7631006400020169640000000017ffffffff006e616d650000000019ffffffff 2/C141BE8 | 1112 | \x45 2/C141C28 | 1112 | \x530000045800 2/DF598D8 | 1112 | \x45 2/DF59950 | 1112 | \x630000045800000000020df59918000000020df599500002aca72900f8a8 2/DF59950 | 1114 | \x530000045a01 2/DF59950 | 1114 | \x520000045a0000402a7075626c6963007265706c69636174696f6e5f746573745f7631006400020169640000000017ffffffff006e616d650000000019ffffffff 2/108918D0 | 1114 | \x45 2/108918D0 | 1114 | \x530000045a00 2/131E1310 | 1114 | \x45 2/131E1310 | 1114 | \x530000045a00 2/137D7768 | 1114 | \x45 2/137E8448 | 1114 | \x630000045a0000000002137e841800000002137e84480002aca729812c96 (14 rows) *** Next advance to the Stream Commit record: *** => SELECT * FROM pg_replication_slot_advance('test_slot_v1', '2/DF59950'); slot_name | end_lsn --------------+----------- test_slot_v1 | 2/DF59950 (1 row) *** This time the peek() starts from the correct LSN: *** => SELECT * FROM pg_logical_slot_peek_binary_changes('test_slot_v1', null, null,'publication_names', 'cdc', 'proto_version', '4', 'streaming', 'true') WHERE SUBSTRING(data, 1,1) NOT IN ('\x49', '\x44'); lsn | xid | data ------------+------+-------------------------------------------------------------------------------------------------------------------------------------- 2/DF59950 | 1114 | \x530000045a01 2/DF59950 | 1114 | \x520000045a0000402a7075626c6963007265706c69636174696f6e5f746573745f7631006400020169640000000017ffffffff006e616d650000000019ffffffff 2/108918D0 | 1114 | \x45 2/108918D0 | 1114 | \x530000045a00 2/131E1310 | 1114 | \x45 2/131E1310 | 1114 | \x530000045a00 2/137D7768 | 1114 | \x45 2/137E8448 | 1114 | \x630000045a0000000002137e841800000002137e84480002aca729812c96 (8 rows) *** End of demo *** The question is whether that is by design or a bug, and if by design maybe someone can explain how this is meant to be used, because it's not clear. It will work eventually if argument upto_nchanges is NULL, because when the transaction completes we get a Stream Commit record and can advance, but in the meantime we'll have ingested a lot of duplicate records we now have to deal with. And if argument upto_nchanges is not NULL we're stuck because peek() will only returns one or more Stream blocks until the number of returned rows exceeds upto_nchanges , and then returns the same blocks over and over again forever because we cannot advance, and never see the Stream Commit record. Thank you. Guillaume. ------=_Part_304409180_1453776227.1699470091390 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable <= div style=3D'font-family: times new roman,new york,times,serif; font-size: = 12pt; color: #000000'>Hi everyone,

When u= sing logical replication with the pgoutput plugin, on PG 16,we do the follo= wing:
  1) SELECT * FROM = pg_logical_slot_peek_binary_changes('test_slot_v1', null, null,'publication= _names', 'cdc', 'proto_version', '4', 'streaming', 'false')
  2) Get LSN of last row (Commit)=
  3) SELECT * FROM pg_replicati= on_slot_advance('test_slot_v1', <Commit LSN>);  
  4) Repeat.

An= d this works perfectly fine when streaming =3D false. When turning on strea= ming the expectation is that the same thing happens, except the the LSN bei= ng passed to pg_replication_slot_adva= nce() is for a Stream End record. On the next call to pg_logical_slot_peek_binary_changes() we should get= the subsequent Stream Start record. But instead, the stream starts over fr= om the transaction Begin record. Observe: 

*** Demo starts ***
*** Initially there are no changes, peek() returns nothi= ng: ***

=3D> SELECT * FROM pg_logical_slot_p= eek_binary_changes('test_slot_v1', null, null,'publication_names', 'cdc', '= proto_version', '4', 'streaming', 'true') WHERE SUBSTRING(data, 1,1) NOT IN= ('\x49', '\x44');
 lsn | xid | data
-----+-----+------
(0 rows)

*** Slot status: ***

=3D> SELECT sl= ot_name, active, restart_lsn, confirmed_flush_lsn FROM pg_replication_slots= ;                    &nbs= p;                     &n= bsp;                    s= lot_name   | active | restart_lsn | confirmed_flush_lsn
--------------+--------+-------------+---------------= ------
 test_slot_v1 | f    &= nbsp; | 2/98CE060   | 2/98CE060
(1 rows)
=

= *** Now make some changes (delete then insert a bunch of records) and call = peek()          ***
*** The predicate filters out Delete and Insert records, leaving Stream St= art (\x53 =3D S),  ***
*** Relation (\x= 52 =3D R), Stream End (\x45 =3D E), and Stream Commit (\x63 =3D c)  &n= bsp;            ***

abinitio=3D> SELECT * FROM pg= _logical_slot_peek_binary_changes('test_slot_v1', null, null,'publication_n= ames', 'cdc', 'proto_version', '4', 'streaming', 'true') WHERE SUBSTRING(da= ta, 1,1) NOT IN ('\x49', '\x44');
  &nb= sp; lsn     | xid  |          =                      = ;                     &nb= sp;            data
------------+------+------------------------------------------------= ---------------------------------------------------------------------------= -----------
 2/A222A20  | 1112 | \= x530000045801
 2/A222A20  | 1112 |= \x52000004590000402a7075626c6963007265706c69636174696f6e5f746573745f763100= 6400020169640000000017ffffffff006e616d650000000019ffffffff
 2/C141BE8  | 1112 | \x45
 2/C141C28  | 1112 | \x530000045800
 2/DF598D8  | 1112 | \x45
=  2/DF59950  | 1112 | \x630000045800000000020df59918000000020df= 599500002aca72900f8a8
 2/DF59950  = | 1114 | \x530000045a01
 2/DF59950 = ; | 1114 | \x520000045a0000402a7075626c6963007265706c69636174696f6e5f746573= 745f7631006400020169640000000017ffffffff006e616d650000000019ffffffff=
 2/108918D0 | 1114 | \x45
 2/108918D0 | 1114 | \x530000045a00
 2/131E1310 | 1114 | \x45
&nbs= p;2/131E1310 | 1114 | \x530000045a00
 2= /137D7768 | 1114 | \x45
 2/137E8448 | 1= 114 | \x630000045a0000000002137e841800000002137e84480002aca729812c96=
(14 rows)

*** It was a peek() so the status is unchanged= : ***

=3D> SELECT slot_name, active, restart_lsn, confirmed_flush_= lsn FROM pg_replication_slots;            &nb= sp;                     &= nbsp;                    =        slot_name   | active | restart_lsn | = confirmed_flush_lsn
--------------+--------+= -------------+---------------------
 t= est_slot_v1 | f      | 2/98CE060   | 2/98CE060
(1 rows)

*** No= w advance the slot to the first Stream End record: ***

=3D> SELECT * FROM pg_rep= lication_slot_advance('test_slot_v1', '2/C141BE8');      &nb= sp;                     &= nbsp;                    =                     &nbs= p;      slot_name   |  end_lsn
--------------+-----------
=  test_slot_v1 | 2/C141BE8
(1 row)

***&nbs= p;confirmed_flush_lsn is updated as expected: ***= *

=3D> SELECT slot_name, active, restart_lsn, confirmed_flush_lsn FROM pg= _replication_slots;
  slot_name  &= nbsp;| active | restart_lsn | confirmed_flush_lsn
--------------+--------+-------------+---------------------
 test_slot_v1 | f      | 2/9B09D= 10   | 2/C141BE8
<= font size=3D"2">(1 rows)

<= /div>
*** Now peek() again. It is starting from ear= lier than confirmed_flush_lsn: ***

=3D> SE= LECT * FROM pg_logical_slot_peek_binary_changes('test_slot_v1', null, null,= 'publication_names', 'cdc', 'proto_version', '4', 'streaming', 'true') WHER= E SUBSTRING(data, 1,1) NOT IN ('\x49', '\x44');
    lsn     | xid  |      =                      = ;                     &nb= sp;                data
------------+------+-----------------------------------= ---------------------------------------------------------------------------= ------------------------
 2/A222A20&nbs= p; | 1112 | \x530000045801
 2/A222A20&n= bsp; | 1112 | \x52000004590000402a7075626c6963007265706c69636174696f6e5f746= 573745f7631006400020169640000000017ffffffff006e616d650000000019ffffffff
 2/C141BE8  | 1112 | \x45
 2/C141C28  | 1112 | \x530000045800
 2/DF598D8  | 1112 | \x45
 2/DF59950  | 1112 | \x630000045800000000020df59918= 000000020df599500002aca72900f8a8
 2/DF5= 9950  | 1114 | \x530000045a01
 2= /DF59950  | 1114 | \x520000045a0000402a7075626c6963007265706c696361746= 96f6e5f746573745f7631006400020169640000000017ffffffff006e616d650000000019ff= ffffff
 2/108918D0 | 1114 | \x45=
 2/108918D0 | 1114 | \x530000045a00
 2/131E1310 | 1114 | \x45
 2/131E1310 | 1114 | \x530000045a00
 2/137D7768 | 1114 | \x45
 = 2/137E8448 | 1114 | \x630000045a0000000002137e841800000002137e84480002aca72= 9812c96
(14 rows)

*** Next advance to the Stream C= ommit record: ***

=3D> SELECT * FROM pg_replication_slot_advance('test_slot_v1',= '2/DF59950');                 = ;                     &nb= sp;                     &= nbsp;                slot_name = ;  |  end_lsn
--------------+-----= ------
 test_slot_v1 | 2/DF59950=
(1 row)

=
*** This time the peek() starts from the correct L= SN: ***

= =3D> SELECT * FROM pg_logical_slot_peek_binary_changes('test_slot_v1', n= ull, null,'publication_names', 'cdc', 'proto_version', '4', 'streaming', 't= rue') WHERE SUBSTRING(data, 1,1) NOT IN ('\x49', '\x44');
    lsn     | xid  |  &nbs= p;                     &n= bsp;                     =                    data
------------+------+-------------------------= ---------------------------------------------------------------------------= ----------------------------------
 2= /DF59950  | 1114 | \x530000045a01
 = ;2/DF59950  | 1114 | \x520000045a0000402a7075626c6963007265706c6963617= 4696f6e5f746573745f7631006400020169640000000017ffffffff006e616d650000000019= ffffffff
 2/108918D0 | 1114 | \x45
 2/108918D0 | 1114 | \x530000045a00<= /div>
 2/131E1310 | 1114 | \x45
 2/131E1310 | 1114 | \x530000045a00
 2/137D7768 | 1114 | \x45
&nbs= p;2/137E8448 | 1114 | \x630000045a0000000002137e841800000002137e84480002aca= 729812c96
(8 rows)

*** End of demo ***
=

The question is whether that= is by design or a bug, and if by design maybe someone can explain how this= is meant to be used, because it's not clear. It will work eventually if ar= gument upto_nchanges  is NULL, because when the transaction completes= we get a Stream Commit record and can advance, but in the meantime we'll h= ave ingested a lot of duplicate records we now have to deal with. And if&nb= sp; argument upto_nchanges is not NULL we're stuck because peek() will only returns one or m= ore Stream blocks until the number of returned rows exceeds upto_nc= hanges , and then returns= the same blocks over and over again forever because we cannot advance, and= never see the Stream Commit record.

<= /div>
Thank you.

Guillaume.
------=_Part_304409180_1453776227.1699470091390--