Feedback-ID: i0c21471d:Fastmail
User-Agent: Cyrus-JMAP/3.9.0-alpha0-236-g06c0f70e43-fm-20230313.001-g06c0f70e
Mime-Version: 1.0
Message-Id: <78149fa6-4c77-4128-8518-197a631c29c3@app.fastmail.com>
In-Reply-To: 
 <CAA4eK1Kw8WhioNekkeffQJ4=YM=zi+OhHv_N5aXy1zGybGPKbA@mail.gmail.com>
References: <db02e6773adb4dbcb5b9bb3803ebe340@amazon.com>
 <20230318120638.3z7usvxwncs47tdy@alvherre.pgsql>
 <e378fb636a694c81b354d3c405f0179d@amazon.com>
 <22c275cc-a2d5-4cee-ac81-bbb6950248b8@app.fastmail.com>
 <CAA4eK1Kw8WhioNekkeffQJ4=YM=zi+OhHv_N5aXy1zGybGPKbA@mail.gmail.com>
Date: Wed, 22 Mar 2023 18:17:49 -0300
From: "Euler Taveira" <euler@eulerto.com>
To: "Amit Kapila" <amit.kapila16@gmail.com>
Cc: "Sachin Kumar" <ssetiya@amazon.com>,
 "Alvaro Herrera" <alvherre@alvh.no-ip.org>,
 "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Subject: Re: Initial Schema Sync for Logical Replication
Content-Type: multipart/alternative;
 boundary=66bca0f0d7424f7490ac2c4cc3a49268
Archived-At: <https://www.postgresql.org/message-id/78149fa6-4c77-4128-8518-197a631c29c3%40app.fastmail.com>
Precedence: bulk

--66bca0f0d7424f7490ac2c4cc3a49268
Content-Type: text/plain

On Tue, Mar 21, 2023, at 8:18 AM, Amit Kapila wrote:
> Now, how do we avoid these problems even if we have our own version of
> functionality similar to pg_dump for selected objects? I guess we will
> face similar problems. If so, we may need to deny schema sync in any
> such case.
There are 2 approaches for initial DDL synchronization:

1) generate the DDL command on the publisher, stream it and apply it as-is on
the subscriber;
2) generate a DDL representation (JSON, for example) on the publisher, stream
it, transform it into a DDL command on subscriber and apply it.

The option (1) is simpler and faster than option (2) because it does not
require an additional step (transformation). However, option (2) is more
flexible than option (1) because it allow you to create a DDL command even if a
feature was removed from the subscriber and the publisher version is less than
the subscriber version or a feature was added to the publisher and the
publisher version is greater than the subscriber version. Of course there are
exceptions and it should forbid the transformation (in this case, it can be
controlled by the protocol version -- LOGICALREP_PROTO_FOOBAR_VERSION_NUM). A
decision must be made: simple/restrict vs complex/flexible.

One of the main use cases for logical replication is migration (X -> Y where X
< Y). Postgres generally does not remove features but it might happen (such as
WITH OIDS syntax) and it would break the DDL replication (option 1). In the
downgrade case (X -> Y where X > Y), it might break the DDL replication if a
new syntax is introduced in X. Having said that, IMO option (1) is fragile if
we want to support DDL replication between different Postgres versions. It
might eventually work but there is no guarantee.

Per discussion [1], I think if we agree that the Alvaro's DDL deparse patch is
the way to go with DDL replication, it seems wise that it should be used for
initial DDL synchronization as well.


[1] https://www.postgresql.org/message-id/CAA4eK1%2Bw_dFytBiv3RxbOL76_noMzmX0QGTc8uS%3Dbc2WaPVoow%40mail.gmail.com


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

--66bca0f0d7424f7490ac2c4cc3a49268
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 Tue, Mar 21,=
 2023, at 8:18 AM, Amit Kapila wrote:<br></div><blockquote type=3D"cite"=
 id=3D"qt" style=3D""><div>Now, how do we avoid these problems even if w=
e have our own version of<br></div><div>functionality similar to pg_dump=
 for selected objects? I guess we will<br></div><div>face similar proble=
ms. If so, we may need to deny schema sync in any<br></div><div>such cas=
e.<br></div></blockquote><div>There are 2 approaches for initial DDL syn=
chronization:<br></div><div><br></div><div>1) generate the DDL command o=
n the publisher, stream it and apply it as-is on<br></div><div>the subsc=
riber;<br></div><div>2) generate a DDL representation (JSON, for example=
) on the publisher, stream<br></div><div>it, transform it into a DDL com=
mand on subscriber and apply it.<br></div><div><br></div><div>The option=
 (1) is simpler and faster than option (2) because it does not<br></div>=
<div>require an additional step (transformation). However, option (2) is=
 more<br></div><div>flexible than option (1) because it allow you to cre=
ate a DDL command even if a<br></div><div>feature was removed from the s=
ubscriber and the publisher version is less than<br></div><div>the subsc=
riber version or a feature was added to the publisher and the<br></div><=
div>publisher version is greater than the subscriber version. Of course =
there are<br></div><div>exceptions and it should forbid the transformati=
on (in this case, it can be<br></div><div>controlled by the protocol ver=
sion -- LOGICALREP_PROTO_FOOBAR_VERSION_NUM). A<br></div><div>decision m=
ust be made: simple/restrict vs complex/flexible.<br></div><div><br></di=
v><div>One of the main use cases for logical replication is migration (X=
 -&gt; Y where X<br></div><div>&lt; Y). Postgres generally does not remo=
ve features but it might happen (such as<br></div><div>WITH OIDS syntax)=
 and it would break the DDL replication (option 1). In the<br></div><div=
>downgrade case (X -&gt; Y where X &gt; Y), it might break the DDL repli=
cation if a<br></div><div>new syntax is introduced in X. Having said tha=
t, IMO option (1) is fragile if<br></div><div>we want to support DDL rep=
lication between different Postgres versions. It<br></div><div>might eve=
ntually work but there is no guarantee.<br></div><div><br></div><div>Per=
 discussion [1], I think if we agree that the Alvaro's DDL deparse patch=
 is<br></div><div>the way to go with DDL replication, it seems wise that=
 it should be used for<br></div><div>initial DDL synchronization as well=
.<br></div><div><br></div><div><br></div><div>[1] <a href=3D"https://www=
.postgresql.org/message-id/CAA4eK1%2Bw_dFytBiv3RxbOL76_noMzmX0QGTc8uS%3D=
bc2WaPVoow%40mail.gmail.com">https://www.postgresql.org/message-id/CAA4e=
K1%2Bw_dFytBiv3RxbOL76_noMzmX0QGTc8uS%3Dbc2WaPVoow%40mail.gmail.com</a><=
br></div><div><br></div><div><br></div><div><br></div><div id=3D"sig1124=
09080"><div class=3D"signature">--<br></div><div class=3D"signature">Eul=
er Taveira<br></div><div class=3D"signature">EDB&nbsp;&nbsp; <a href=3D"=
https://www.enterprisedb.com/">https://www.enterprisedb.com/</a><br></di=
v></div><div><br></div></body></html>
--66bca0f0d7424f7490ac2c4cc3a49268--