MIME-Version: 1.0
References: 
 <CAAD30ULtoGp8L_GKbV15Wnm+X5r=SE7MOnYHuqBr396m26jJSA@mail.gmail.com>
 <202203162206.7spggyktx63e@alvherre.pgsql>
 <CAAD30UKRUusq8JyyHzAv71=ncN22OE8OkOOyAWvRHW3wXNjyyA@mail.gmail.com>
 <CAAD30UKTp87+kvGZYL3M2Suxq=WEvFUG24ZRT0yT9rqdkP=uMA@mail.gmail.com>
 <MEYP282MB1669863D5C31D7F6A1D996D8B6139@MEYP282MB1669.AUSP282.PROD.OUTLOOK.COM>
 <CAAD30UKc=GiGQzE8H7+Ofo18hwMOfK4qUm_KUyw6c09q4JvA5Q@mail.gmail.com>
 <MEYP282MB16691E383140844437FB0633B6139@MEYP282MB1669.AUSP282.PROD.OUTLOOK.COM>
 <CAAD30U+ZTBXLH0wWsW9+Zu2RECGKeaQNynLs7wKA0o86w8C-fw@mail.gmail.com>
 <MEYP282MB166926E46397CBFC113B4A7EB6189@MEYP282MB1669.AUSP282.PROD.OUTLOOK.COM>
 <CAA4eK1J4AekmEKgmfp6e-zZz4M02m7w7uxvC2tjqmjF-LDSGDA@mail.gmail.com>
 <CAAD30UKvv5=k6BY+JAF1fWzrYNbGcB0DEdNi1FMokULzOwSxcQ@mail.gmail.com>
 <CAAD30U+CRgUgkAg33KzNBKwCbsgiSc5z3NYvxNzEfS0Zg2S1WA@mail.gmail.com>
 <CAD21AoAv_wsBEK8jcqjBpatspiP=5E+qLokw9zCESBSvCAiRMg@mail.gmail.com>
In-Reply-To: 
 <CAD21AoAv_wsBEK8jcqjBpatspiP=5E+qLokw9zCESBSvCAiRMg@mail.gmail.com>
From: Zheng Li <zhengli10@gmail.com>
Date: Thu, 26 May 2022 18:19:21 -0400
Message-ID: 
 <CAAD30UK6T8bfW1JMaSSRDSynB6W05HjNrmvSp+tvXp-jdu9xFQ@mail.gmail.com>
Subject: Re: Support logical replication of DDLs
To: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: Amit Kapila <amit.kapila16@gmail.com>, Japin Li <japinli@hotmail.com>,
	Alvaro Herrera <alvherre@alvh.no-ip.org>,
 Dilip Kumar <dilipbalaut@gmail.com>,
	rajesh singarapu <rajesh.rs0541@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAAD30UK6T8bfW1JMaSSRDSynB6W05HjNrmvSp%2BtvXp-jdu9xFQ%40mail.gmail.com>
Precedence: bulk

Hi Masahiko,

> Thank you for updating the patches!
>
> I've not looked at these patches in-depth yet but with this approach,
> what do you think we can handle the DDL syntax differences between
> major versions? DDL syntax or behavior could be changed by future
> changes and I think we need to somehow deal with the differences. For

> example, if the user uses logical replication for major version
> upgrade, the publisher is older than the subscriber. We might have to
> rewrite the DDL before applying to the subscriber because the DDL
> executed on the publisher no longer work on a new PostgreSQL version

I don't think we will allow this kind of situation to happen in the
first place for
backward compatibility. If a DDL no longer works on a new version of
PostgreSQL, the user will have to change the application code as well.
So even if it happens for
whatever reason, we could either
1. fail the apply worker and let the user fix such DDL because they'll
have to fix the application code anyway when this happens.
2. add guard rail logic in the apply worker to automatically fix such
DDL if possible, knowing the version of the source and target. Similar
logic must have been implemented for pg_dump/restore/upgrade.

> or we might have to add some options to the DDL before the application
> in order to keep the same behavior. This seems to require a different
> solution from what the patch does for the problem you mentioned such

> as "DDL involving multiple tables where only some tables are
> replicated=E2=80=9D.

First of all, this case can only happen when the customer chooses to
only replicate a subset of the tables in a database in which case
table level DDL replication is chosen instead of database level DDL
replication (where all tables
and DDLs are replicated). I think the solution would be:
1. make best effort to detect such DDLs on the publisher and avoid
logging of such DDLs in table level DDL replication.
2. apply worker will fail to replay such command due to missing
objects if such DDLs didn't get filtered on the publisher for some
reason. This should be rare and I think it's OK even if it happens,
we'll find out
why and fix it.

Regards,
Zheng