Feedback-ID: i0c21471d:Fastmail
User-Agent: Cyrus-JMAP/3.11.0-alpha0-119-ga8b98d1bd8-fm-20240108.001-ga8b98d1b
MIME-Version: 1.0
Message-Id: <73ab86ca-3fd5-49b3-9c80-73d1525202f1@app.fastmail.com>
In-Reply-To: 
 <TY3PR01MB9889C5D55206DDD978627D07F5752@TY3PR01MB9889.jpnprd01.prod.outlook.com>
References: <5ac50071-f2ed-4ace-a8fd-b892cffd33eb@www.fastmail.com>
 <4710d895-689a-4ae6-b8e3-344286dbf970@app.fastmail.com>
 <da078f11-d2de-420e-9611-f3b7d4dadd9a@eisentraut.org>
 <ZUrEclC-uZV-rGd2@paquier.xyz>
 <96e3b739-d63d-4d35-90a3-7869465b7167@eisentraut.org>
 <ZU1nR5KMZtSTEQDf@paquier.xyz>
 <e02a2c17-22e5-4ba6-b788-de696ab74f1e@app.fastmail.com>
 <CALDaNm098Jkbh+ye6zMj9Ro9j1bBe6FfPV80BFbs1=pUuTJ07g@mail.gmail.com>
 <2736443c-58e3-42df-8cc4-8c66106e3da5@app.fastmail.com>
 <CAA4eK1KtGC-v_CYd8yaCwisrQvoZN=+As90p9hBM29DAxA+Pmg@mail.gmail.com>
 <CANhcyEUdYCR9oSt4HsZRqYZZVXsb4x-HoPGqDSxSeA2SYz-YGg@mail.gmail.com>
 <CAA4eK1KMQNMhSPmsRdrCQpuc=Tsc08h6=XkLNZ5F35Q2REom6g@mail.gmail.com>
 <CANhcyEXjOcnOJKdOfK8Z1+bbymuZzwSjrppbt4FKHbrDD_t7Mg@mail.gmail.com>
 <CANhcyEUCt-g4JLQU3Q3ofFk_Vt-Tqh3ZdXoLcpT8fjz9LY_-ww@mail.gmail.com>
 <bcd3db13-f56e-47e3-8fa7-7f0c6243c8a6@app.fastmail.com>
 <CAA4eK1JRgnhv_ySzuFjN7UaX9qxz5Hqcwew7Fk=+AbJbu0Kd9w@mail.gmail.com>
 <TY3PR01MB98895BA6C1D72CB8582CACC4F5682@TY3PR01MB9889.jpnprd01.prod.outlook.com>
 <aabc91d2-505b-4412-af08-7dae2df1d437@app.fastmail.com>
 <TY3PR01MB9889C362FF76102C88FA1C29F56F2@TY3PR01MB9889.jpnprd01.prod.outlook.com>
 <TY3PR01MB9889C5D55206DDD978627D07F5752@TY3PR01MB9889.jpnprd01.prod.outlook.com>
Date: Tue, 23 Jan 2024 20:58:11 -0300
From: "Euler Taveira" <euler@eulerto.com>
To: "kuroda.hayato@fujitsu.com" <kuroda.hayato@fujitsu.com>,
 "'pgsql-hackers@lists.postgresql.org'" <pgsql-hackers@lists.postgresql.org>
Cc: "Shlok Kyal" <shlok.kyal.oss@gmail.com>,
 "vignesh C" <vignesh21@gmail.com>, "Michael Paquier" <michael@paquier.xyz>,
 "Peter Eisentraut" <peter@eisentraut.org>,
 "Andres Freund" <andres@anarazel.de>,
 "Ashutosh Bapat" <ashutosh.bapat.oss@gmail.com>,
 "Amit Kapila" <amit.kapila16@gmail.com>
Subject: Re: speed up a logical replica setup
Content-Type: multipart/alternative;
 boundary=c9fe0cd53eb642539bb3099448c25018
Archived-At: 
 <https://www.postgresql.org/message-id/73ab86ca-3fd5-49b3-9c80-73d1525202f1%40app.fastmail.com>
Precedence: bulk

--c9fe0cd53eb642539bb3099448c25018
Content-Type: text/plain

On Mon, Jan 22, 2024, at 4:06 AM, Hayato Kuroda (Fujitsu) wrote:
> I analyzed and found a reason. This is because publications are invisible for some transactions.
> 
> As the first place, below operations were executed in this case.
> Tuples were inserted after getting consistent_lsn, but before starting the standby.
> After doing the workload, I confirmed again that the publication was created.
> 
> 1. on primary, logical replication slots were created.
> 2. on primary, another replication slot was created.
> 3. ===on primary, some tuples were inserted. ===
> 4. on standby, a server process was started
> 5. on standby, the process waited until all changes have come.
> 6. on primary, publications were created.
> 7. on standby, subscriptions were created.
> 8. on standby, a replication progress for each subscriptions was set to given LSN (got at step2).
> =====pg_subscriber finished here=====
> 9. on standby, a server process was started again
> 10. on standby, subscriptions were enabled. They referred slots created at step1.
> 11. on primary, decoding was started but ERROR was raised.

Good catch! It is a design flaw.

> In this case, tuples were inserted *before creating publication*.
> So I thought that the decoded transaction could not see the publication because
> it was committed after insertions.
> 
> One solution is to create a publication before creating a consistent slot.
> Changes which came before creating the slot were surely replicated to the standby,
> so upcoming transactions can see the object. We are planning to patch set to fix
> the issue in this approach.

I'll include a similar code in the next patch and also explain why we should
create the publication earlier. (I'm renaming
create_all_logical_replication_slots to setup_publisher and calling
create_publication from there and also adding the proposed GUC checks in it.)


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

--c9fe0cd53eb642539bb3099448c25018
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, Jan 22,=
 2024, at 4:06 AM, Hayato Kuroda (Fujitsu) wrote:<br></div><blockquote t=
ype=3D"cite" id=3D"qt" style=3D""><div>I analyzed and found a reason. Th=
is is because publications are invisible for some transactions.<br></div=
><div><br></div><div>As the first place, below operations were executed =
in this case.<br></div><div>Tuples were inserted after getting consisten=
t_lsn, but before starting the standby.<br></div><div>After doing the wo=
rkload, I confirmed again that the publication was created.<br></div><di=
v><br></div><div>1. on primary, logical replication slots were created.<=
br></div><div>2. on primary, another replication slot was created.<br></=
div><div>3. =3D=3D=3Don primary, some tuples were inserted. =3D=3D=3D<br=
></div><div>4. on standby, a server process was started<br></div><div>5.=
 on standby, the process waited until all changes have come.<br></div><d=
iv>6. on primary, publications were created.<br></div><div>7. on standby=
, subscriptions were created.<br></div><div>8. on standby, a replication=
 progress for each subscriptions was set to given LSN (got at step2).<br=
></div><div>=3D=3D=3D=3D=3Dpg_subscriber finished here=3D=3D=3D=3D=3D<br=
></div><div>9. on standby, a server process was started again<br></div><=
div>10. on standby, subscriptions were enabled. They referred slots crea=
ted at step1.<br></div><div>11. on primary, decoding was started but ERR=
OR was raised.<br></div></blockquote><div><br></div><div>Good catch! It =
is a design flaw.</div><div><br></div><blockquote type=3D"cite" id=3D"qt=
" style=3D""><div>In this case, tuples were inserted *before creating pu=
blication*.<br></div><div>So I thought that the decoded transaction coul=
d not see the publication because<br></div><div>it was committed after i=
nsertions.<br></div><div><br></div><div>One solution is to create a publ=
ication before creating a consistent slot.<br></div><div>Changes which c=
ame before creating the slot were surely replicated to the standby,<br><=
/div><div>so upcoming transactions can see the object. We are planning t=
o patch set to fix<br></div><div>the issue in this approach.<br></div></=
blockquote><div><br></div><div>I'll include a similar code in the next p=
atch and also explain why we should<br></div><div>create the publication=
 earlier. (I'm renaming<br></div><div>create_all_logical_replication_slo=
ts to setup_publisher and calling<br></div><div>create_publication from =
there and also adding the proposed GUC checks in it.)<br></div><div><br>=
</div><div><br></div><div id=3D"sig112409080"><div class=3D"signature">-=
-<br></div><div class=3D"signature">Euler Taveira<br></div><div class=3D=
"signature">EDB&nbsp;&nbsp; <a href=3D"https://www.enterprisedb.com/">ht=
tps://www.enterprisedb.com/</a><br></div></div><div><br></div></body></h=
tml>
--c9fe0cd53eb642539bb3099448c25018--