Re: Support logical replication of DDLs

public inbox for pgsql-general@postgresql.org  
help / color / mirror / Atom feed

From: Masahiko Sawada <sawada.mshk@gmail.com>
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: Zheng Li <zhengli10@gmail.com>
Cc: Japin Li <japinli@hotmail.com>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Dilip Kumar <dilipbalaut@gmail.com>
Cc: rajesh singarapu <rajesh.rs0541@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Support logical replication of DDLs
Date: Mon, 30 May 2022 15:52:29 +0900
Message-ID: <CAD21AoCnGwx2F+Ph3dpoJVq0YR8ke3P59XCs439pW=BRfdzgTQ@mail.gmail.com> (raw)
In-Reply-To: <CAA4eK1JQhz4y-1rYxwFxHYEAN-1JKeO0iT+Nip0N7jJUj_g7RA@mail.gmail.com>
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>
	<CAAD30UK6T8bfW1JMaSSRDSynB6W05HjNrmvSp+tvXp-jdu9xFQ@mail.gmail.com>
	<CAA4eK1JQhz4y-1rYxwFxHYEAN-1JKeO0iT+Nip0N7jJUj_g7RA@mail.gmail.com>

On Fri, May 27, 2022 at 11:03 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Fri, May 27, 2022 at 3:49 AM Zheng Li <zhengli10@gmail.com> wrote:
> >
> > 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”.
> >
> > 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.
> >
>
> FWIW, both these cases could be handled with the deparsing approach,
> and the handling related to the drop of multiple tables where only a
> few are published is already done in the last POC patch shared by Ajin
> [1].
>

Right. So I'm inclined to think that deparsing approach is better from
this point as well as the point mentioned by Álvaro before[1].

Regards,

[1] https://www.postgresql.org/message-id/202204081134.6tcmf5cxl3sz%40alvherre.pgsql

-- 
Masahiko Sawada
EDB:  https://www.enterprisedb.com/

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-general@postgresql.org
  Cc: sawada.mshk@gmail.com, amit.kapila16@gmail.com, zhengli10@gmail.com, japinli@hotmail.com, alvherre@alvh.no-ip.org, dilipbalaut@gmail.com, rajesh.rs0541@gmail.com, pgsql-hackers@lists.postgresql.org
  Subject: Re: Support logical replication of DDLs
  In-Reply-To: <CAD21AoCnGwx2F+Ph3dpoJVq0YR8ke3P59XCs439pW=BRfdzgTQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox