User-Agent: Cyrus-JMAP/3.5.0-alpha0-273-g8500d2492d-fm-20210323.002-g8500d249
Mime-Version: 1.0
Message-Id: <b02b43c7-3b84-4ca4-8218-94ea486c2fce@www.fastmail.com>
In-Reply-To: 
 <CAH2L28tvKGLO9ZRFsaA5msXqZzF6wHgxgXNvnCQ2dRQfs1T8XQ@mail.gmail.com>
References: 
 <CA+HiwqH+9NK5m-=BOYpzjH5UEpeWjT_3HEm4XBYNrda88naGMQ@mail.gmail.com>
 <8cf1ea71-b609-00e8-b8f0-c85ba61b7312@pgmasters.net>
 <4dbad191-9b40-4bb6-8940-d18f2e93c3ce@www.fastmail.com>
 <ME3P282MB16678160F5EA09DCB962474EB6B69@ME3P282MB1667.AUSP282.PROD.OUTLOOK.COM>
 <5b9fceed-8f91-467f-a64e-2134c5c3f496@www.fastmail.com>
 <YBjc/SVkHTcCpxbR@paquier.xyz>
 <MEYP282MB16699D041638CE2F8284701FB6B59@MEYP282MB1669.AUSP282.PROD.OUTLOOK.COM>
 <MEYP282MB1669F5063EC084C0C5854EB1B6B59@MEYP282MB1669.AUSP282.PROD.OUTLOOK.COM>
 <52edc9d2-8e52-4cde-9a15-991961b78387@www.fastmail.com>
 <CACawEhW0NUMHHAQjVP_xM8gWXKvy9haK9nKhC-H=Qn2yTCv-=g@mail.gmail.com>
 <9d5ef845-391a-4c66-b298-0f594d9e6828@www.fastmail.com>
 <f6566ad9-8979-4056-a4ce-24b09f389ede@www.fastmail.com>
 <CAH2L28tjTYctr+eu6Vg5U6iihVhe6P+qKnB_diMGy4=CfwXzdg@mail.gmail.com>
 <CAH2L28sS8=jY6Lj2-xVu6exxQ9gK8uGZr9jf64zDw9hacMrkyA@mail.gmail.com>
 <7e4b5c48-a3d3-4b7e-b455-2d50c60a0ab7@www.fastmail.com>
 <57373e8b-1264-cd37-404e-8edbcf7884cc@enterprisedb.com>
 <d7483e04-7c5f-4881-a9a0-2363b2e52f78@www.fastmail.com>
 <CAH2L28tvKGLO9ZRFsaA5msXqZzF6wHgxgXNvnCQ2dRQfs1T8XQ@mail.gmail.com>
Date: Mon, 29 Mar 2021 10:16:44 -0300
From: "Euler Taveira" <euler@eulerto.com>
To: "Rahila Syed" <rahilasyed90@gmail.com>
Cc: "Peter Eisentraut" <peter.eisentraut@enterprisedb.com>,
 =?UTF-8?Q?=C3=96nder_Kalac=C4=B1?= <onderkalaci@gmail.com>,
 japin <japinli@hotmail.com>, "Michael Paquier" <michael@paquier.xyz>,
 "David Steele" <david@pgmasters.net>, "Craig Ringer" <craig@2ndquadrant.com>,
 "Tomas Vondra" <tomas.vondra@2ndquadrant.com>,
 "Amit Langote" <amitlangote09@gmail.com>,
 "PostgreSQL Hackers" <pgsql-hackers@lists.postgresql.org>
Subject: Re: row filtering for logical replication
Content-Type: multipart/mixed;
 boundary=5026b941bc8e42daab5a9f4d7c50b86d
Archived-At: 
 <https://www.postgresql.org/message-id/b02b43c7-3b84-4ca4-8218-94ea486c2fce%40www.fastmail.com>
Precedence: bulk

--5026b941bc8e42daab5a9f4d7c50b86d
Content-Type: multipart/alternative;
 boundary=0623f1262c0f4ee0b06f41fde1b373ba

--0623f1262c0f4ee0b06f41fde1b373ba
Content-Type: text/plain

On Mon, Mar 29, 2021, at 6:45 AM, Rahila Syed wrote:
> While running some tests on v13 patches, I noticed that, in case the published table data 
> already exists on the subscriber database before creating the subscription, at the time of
> CREATE subscription/table synchronization, an error as seen as follows 
> 
> With the patch:
> 
> 2021-03-29 14:32:56.265 IST [78467] STATEMENT:  CREATE_REPLICATION_SLOT "pg_16406_sync_16390_6944995860755251708" LOGICAL pgoutput USE_SNAPSHOT
> 2021-03-29 14:32:56.279 IST [78467] LOG:  could not send data to client: Broken pipe
> 2021-03-29 14:32:56.279 IST [78467] STATEMENT:  COPY (SELECT aid, bid, abalance, filler FROM public.pgbench_accounts WHERE (aid > 0)) TO STDOUT
> 2021-03-29 14:32:56.279 IST [78467] FATAL:  connection to client lost
> 2021-03-29 14:32:56.279 IST [78467] STATEMENT:  COPY (SELECT aid, bid, abalance, filler FROM public.pgbench_accounts WHERE (aid > 0)) TO STDOUT
> 2021-03-29 14:33:01.302 IST [78470] LOG:  logical decoding found consistent point at 0/4E2B8460
> 2021-03-29 14:33:01.302 IST [78470] DETAIL:  There are no running transactions.
Rahila, I tried to reproduce this issue with the attached script but no luck. I always get

> Without the patch:
> 
> 2021-03-29 15:05:01.581 IST [79029] ERROR:  duplicate key value violates unique constraint "pgbench_branches_pkey"
> 2021-03-29 15:05:01.581 IST [79029] DETAIL:  Key (bid)=(1) already exists.
> 2021-03-29 15:05:01.581 IST [79029] CONTEXT:  COPY pgbench_branches, line 1
> 2021-03-29 15:05:01.583 IST [78538] LOG:  background worker "logical replication worker" (PID 79029) exited with exit code 1
> 2021-03-29 15:05:06.593 IST [79031] LOG:  logical replication table synchronization worker for subscription "test_sub2", table "pgbench_branches" has started
... this message. The code that reports this error is from the COPY command.
Row filter modifications has no control over it. It seems somehow your
subscriber close the replication connection causing this issue. Can you
reproduce it consistently? If so, please share your steps.


--
Euler Taveira
EDB   https://www.enterprisedb.com/

--0623f1262c0f4ee0b06f41fde1b373ba
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE html><html><head><title></title><style type=3D"text/css">p.Mso=
Normal,p.MsoNoSpacing{margin:0}</style></head><body><div>On Mon, Mar 29,=
 2021, at 6:45 AM, Rahila Syed wrote:<br></div><blockquote type=3D"cite"=
 id=3D"qt" style=3D""><div dir=3D"ltr"><div dir=3D"ltr"><div>While runni=
ng some tests on v13 patches, I noticed that, in case the published tabl=
e data&nbsp;<br></div><div>already exists on the subscriber database bef=
ore creating the subscription, at the time of<br></div><div><div>CREATE =
subscription/table synchronization, an error as seen as follows&nbsp;<br=
></div><div><br></div><div>With the patch:<br></div><div><br></div><div>=
2021-03-29 14:32:56.265 IST [78467] STATEMENT: &nbsp;CREATE_REPLICATION_=
SLOT "pg_16406_sync_16390_6944995860755251708" LOGICAL pgoutput USE_SNAP=
SHOT<br></div><div>2021-03-29 14:32:56.279 IST [78467] LOG: &nbsp;could =
not send data to client: Broken pipe<br></div><div>2021-03-29 14:32:56.2=
79 IST [78467] STATEMENT: &nbsp;COPY (SELECT aid, bid, abalance, filler =
FROM public.pgbench_accounts WHERE (aid &gt; 0)) TO STDOUT<br></div><div=
>2021-03-29 14:32:56.279 IST [78467] FATAL: &nbsp;connection to client l=
ost<br></div><div>2021-03-29 14:32:56.279 IST [78467] STATEMENT: &nbsp;C=
OPY (SELECT aid, bid, abalance, filler FROM public.pgbench_accounts WHER=
E (aid &gt; 0)) TO STDOUT<br></div><div>2021-03-29 14:33:01.302 IST [784=
70] LOG: &nbsp;logical decoding found consistent point at 0/4E2B8460<br>=
</div><div>2021-03-29 14:33:01.302 IST [78470] DETAIL: &nbsp;There are n=
o running transactions.<br></div></div></div></div></blockquote><div>Rah=
ila, I tried to reproduce this issue with the attached script but no luc=
k. I always get<br></div><div><br></div><blockquote type=3D"cite" id=3D"=
qt" style=3D""><div dir=3D"ltr"><div dir=3D"ltr"><div><div>Without the p=
atch:<br></div><div><br></div><div>2021-03-29 15:05:01.581 IST [79029] E=
RROR: &nbsp;duplicate key value violates unique constraint "pgbench_bran=
ches_pkey"<br></div><div>2021-03-29 15:05:01.581 IST [79029] DETAIL: &nb=
sp;Key (bid)=3D(1) already exists.<br></div><div>2021-03-29 15:05:01.581=
 IST [79029] CONTEXT: &nbsp;COPY pgbench_branches, line 1<br></div><div>=
2021-03-29 15:05:01.583 IST [78538] LOG: &nbsp;background worker "logica=
l replication worker" (PID 79029) exited with exit code 1<br></div><div>=
2021-03-29 15:05:06.593 IST [79031] LOG: &nbsp;logical replication table=
 synchronization worker for subscription "test_sub2", table "pgbench_bra=
nches" has started<br></div></div></div></div></blockquote><div>... this=
 message. The code that reports this error is from the COPY command.<br>=
</div><div>Row filter modifications has no control over it. It seems som=
ehow your<br></div><div>subscriber close the replication connection caus=
ing this issue. Can you<br></div><div>reproduce it consistently? If so, =
please share your steps.<br></div><div><br></div><div><br></div><div id=3D=
"sig112409080"><div class=3D"signature">--<br></div><div class=3D"signat=
ure">Euler Taveira<br></div><div class=3D"signature">EDB&nbsp;&nbsp; <a =
href=3D"https://www.enterprisedb.com/">https://www.enterprisedb.com/</a>=
<br></div></div><div><br></div></body></html>
--0623f1262c0f4ee0b06f41fde1b373ba--

--5026b941bc8e42daab5a9f4d7c50b86d
Content-Disposition: attachment;filename="test-row-filter-pgbench.sh"
Content-Type: application/x-shellscript; name="test-row-filter-pgbench.sh"
Content-Transfer-Encoding: BASE64

IyEvYmluL2Jhc2gKCmV4cG9ydCBMQU5HPUMKCnNldCAteAoKUEdQQVRIPS9ob21lL2V1bGVy
L3BnbHItcm93LWZpbHRlci12MTMvYmluClBHREFUQTE9L3RtcC9wZ2RhdGExClBHREFUQTI9
L3RtcC9wZ2RhdGEyClBHUE9SVDE9OTk4OApQR1BPUlQyPTk5NzcKUEdEQVRBQkFTRT1wb3N0
Z3JlcwoKUEdTQ0FMRUZBQ1RPUj01ClBHQ0xJRU5UUz1gZXhwciAkUEdTQ0FMRUZBQ1RPUiAt
IDJgClBHVEVTVERVUkFUSU9OPTEwCgppZiBbIC1mICRQR0RBVEExL3Bvc3RtYXN0ZXIucGlk
IF07IHRoZW4KCSRQR1BBVEgvcGdfY3RsIHN0b3AgLUQgJFBHREFUQTEKZmkKCmlmIFsgLWYg
JFBHREFUQTIvcG9zdG1hc3Rlci5waWQgXTsgdGhlbgoJJFBHUEFUSC9wZ19jdGwgc3RvcCAt
RCAkUEdEQVRBMgpmaQoKcm0gLXJmICRQR0RBVEExCnJtIC1yZiAkUEdEQVRBMgoKJFBHUEFU
SC9pbml0ZGIgLUQgJFBHREFUQTEKJFBHUEFUSC9pbml0ZGIgLUQgJFBHREFUQTIKCmVjaG8g
IndhbF9sZXZlbCA9IGxvZ2ljYWwiID4+ICRQR0RBVEExL3Bvc3RncmVzcWwuY29uZgplY2hv
ICJtYXhfd2FsX3NpemUgPSA1R0IiID4+ICRQR0RBVEExL3Bvc3RncmVzcWwuY29uZgplY2hv
ICJsb2dnaW5nX2NvbGxlY3RvciA9IG9uIiA+PiAkUEdEQVRBMS9wb3N0Z3Jlc3FsLmNvbmYK
ZWNobyAibG9nX2ZpbGVuYW1lID0gJ3Bvc3RncmVzcWwubG9nJyIgPj4gJFBHREFUQTEvcG9z
dGdyZXNxbC5jb25mCmVjaG8gImxvZ19taW5fbWVzc2FnZXMgPSBkZWJ1ZzIiID4+ICRQR0RB
VEExL3Bvc3RncmVzcWwuY29uZgplY2hvICJwb3J0ID0gJFBHUE9SVDEiID4+ICRQR0RBVEEx
L3Bvc3RncmVzcWwuY29uZgojZWNobyAibG9nX3N0YXRlbWVudCA9IGFsbCIgPj4gJFBHREFU
QTEvcG9zdGdyZXNxbC5jb25mCmVjaG8gImxvZ19yZXBsaWNhdGlvbl9jb21tYW5kcyA9IG9u
IiA+PiAkUEdEQVRBMS9wb3N0Z3Jlc3FsLmNvbmYKCmVjaG8gIndhbF9sZXZlbCA9IGxvZ2lj
YWwiID4+ICRQR0RBVEEyL3Bvc3RncmVzcWwuY29uZgplY2hvICJtYXhfd2FsX3NpemUgPSA1
R0IiID4+ICRQR0RBVEEyL3Bvc3RncmVzcWwuY29uZgplY2hvICJsb2dnaW5nX2NvbGxlY3Rv
ciA9IG9uIiA+PiAkUEdEQVRBMi9wb3N0Z3Jlc3FsLmNvbmYKZWNobyAibG9nX2ZpbGVuYW1l
ID0gJ3Bvc3RncmVzcWwubG9nJyIgPj4gJFBHREFUQTIvcG9zdGdyZXNxbC5jb25mCmVjaG8g
InBvcnQgPSAkUEdQT1JUMiIgPj4gJFBHREFUQTIvcG9zdGdyZXNxbC5jb25mCiNlY2hvICJs
b2dfc3RhdGVtZW50ID0gYWxsIiA+PiAkUEdEQVRBMi9wb3N0Z3Jlc3FsLmNvbmYKZWNobyAi
bG9nX3JlcGxpY2F0aW9uX2NvbW1hbmRzID0gb24iID4+ICRQR0RBVEEyL3Bvc3RncmVzcWwu
Y29uZgoKJFBHUEFUSC9wZ19jdGwgc3RhcnQgLXcgLUQgJFBHREFUQTEKJFBHUEFUSC9wZ19j
dGwgc3RhcnQgLXcgLUQgJFBHREFUQTIKCiRQR1BBVEgvcGdiZW5jaCAtaSAtcyAkUEdTQ0FM
RUZBQ1RPUiAtcCAkUEdQT1JUMSAkUEdEQVRBQkFTRQoKIyBhZGQgcHJpbWFyeSBrZXkgdG8g
cGdiZW5jaF9oaXN0b3J5CiRQR1BBVEgvcHNxbCAtWCAtYyAiQkVHSU47IEFMVEVSIFRBQkxF
IHBnYmVuY2hfaGlzdG9yeSBBREQgQ09MVU1OIGlkIHNlcmlhbDsgVVBEQVRFIHBnYmVuY2hf
aGlzdG9yeSBTRVQgaWQgPSBuZXh0dmFsKCdwZ2JlbmNoX2hpc3RvcnlfaWRfc2VxJyk7IEFM
VEVSIFRBQkxFIHBnYmVuY2hfaGlzdG9yeSBBREQgUFJJTUFSWSBLRVkoaWQpOyBDT01NSVQ7
IiAtcCAkUEdQT1JUMSAkUEdEQVRBQkFTRQoKIyRQR1BBVEgvcGdfZHVtcCAtcyAtcCAkUEdQ
T1JUMSAkUEdEQVRBQkFTRSB8ICRQR1BBVEgvcHNxbCAtZiAtIC1wICRQR1BPUlQyICRQR0RB
VEFCQVNFCiRQR1BBVEgvcGdfZHVtcCAtLW5vLXB1YmxpY2F0aW9ucyAtcCAkUEdQT1JUMSAk
UEdEQVRBQkFTRSB8ICRQR1BBVEgvcHNxbCAtZiAtIC1wICRQR1BPUlQyICRQR0RBVEFCQVNF
CgokUEdQQVRIL3BzcWwgLVggLWMgIkNSRUFURSBQVUJMSUNBVElPTiBwdWJfYmVuY2ggRk9S
IFRBQkxFIHBnYmVuY2hfYWNjb3VudHMgV0hFUkUgKGFpZCA+IDApLCBwZ2JlbmNoX2JyYW5j
aGVzLCBwZ2JlbmNoX3RlbGxlcnMsIHBnYmVuY2hfaGlzdG9yeSIgLXAgJFBHUE9SVDEgJFBH
REFUQUJBU0UKCiRQR1BBVEgvcHNxbCAtWCAtYyAiQ1JFQVRFIFNVQlNDUklQVElPTiBzdWJf
YmVuY2ggQ09OTkVDVElPTiAncG9ydD0kUEdQT1JUMSBkYm5hbWU9JFBHREFUQUJBU0UnIFBV
QkxJQ0FUSU9OIHB1Yl9iZW5jaCIgLXAgJFBHUE9SVDIgJFBHREFUQUJBU0UKCiRQR1BBVEgv
cGdiZW5jaCAtVCAkUEdURVNURFVSQVRJT04gLWMgJFBHQ0xJRU5UUyAtcCAkUEdQT1JUMSAk
UEdEQVRBQkFTRQoKJFBHUEFUSC9wc3FsIC1YIC1jICJTRUxFQ1QgcGdfY3VycmVudF93YWxf
bHNuKCkgPD0gd3JpdGVfbHNuIEFORCBzdGF0ZSA9ICdzdHJlYW1pbmcnIEZST00gcGdfY2F0
YWxvZy5wZ19zdGF0X3JlcGxpY2F0aW9uIFdIRVJFIGFwcGxpY2F0aW9uX25hbWUgPSAnc3Vi
MSciIC1wICRQR1BPUlQxICRQR0RBVEFCQVNFCnNsZWVwIDIKJFBHUEFUSC9wc3FsIC1YIC1j
ICJTRUxFQ1QgcGdfY3VycmVudF93YWxfbHNuKCkgPD0gd3JpdGVfbHNuIEFORCBzdGF0ZSA9
ICdzdHJlYW1pbmcnIEZST00gcGdfY2F0YWxvZy5wZ19zdGF0X3JlcGxpY2F0aW9uIFdIRVJF
IGFwcGxpY2F0aW9uX25hbWUgPSAnc3ViMSciIC1wICRQR1BPUlQxICRQR0RBVEFCQVNFCnNs
ZWVwIDIKJFBHUEFUSC9wc3FsIC1YIC1jICJTRUxFQ1QgcGdfY3VycmVudF93YWxfbHNuKCkg
PD0gd3JpdGVfbHNuIEFORCBzdGF0ZSA9ICdzdHJlYW1pbmcnIEZST00gcGdfY2F0YWxvZy5w
Z19zdGF0X3JlcGxpY2F0aW9uIFdIRVJFIGFwcGxpY2F0aW9uX25hbWUgPSAnc3ViMSciIC1w
ICRQR1BPUlQxICRQR0RBVEFCQVNFCgokUEdQQVRIL3BnX2N0bCBzdG9wIC1EICRQR0RBVEEx
CiRQR1BBVEgvcGdfY3RsIHN0b3AgLUQgJFBHREFUQTIKCmdyZXAgRVJST1IgJFBHREFUQTIv
bG9nL3Bvc3RncmVzcWwubG9nCmdyZXAgJ0Jyb2tlbiBwaXBlJyAkUEdEQVRBMi9sb2cvcG9z
dGdyZXNxbC5sb2cK

--5026b941bc8e42daab5a9f4d7c50b86d--