Re: Handle infinite recursion in logical replication setup

On Mon, Mar 7, 2022 at 12:48 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
> On Mon, Mar 7, 2022 at 11:45 AM Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
> >
> > On Mon, Mar 7, 2022 at 4:20 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> > >
> > > On Mon, Mar 7, 2022 at 10:26 AM Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
> > > >
> > > > Hi Vignesh, I also have not looked at the patch yet, but I have what
> > > > seems like a very fundamental (and possibly dumb) question...
> > > >
> > > > Basically, I do not understand the choice of syntax for setting things up.
> > > >
> > > > IMO that "only-local" option sounds very similar to the other
> > > > PUBLICATION ("publish") options which decide the kinds of things that
> > > > will be published. So it feels more natural for me to think of the
> > > > publisher as being the one to decide what will be published.
> > > >
> > > > e.g.
> > > >
> > > > option 1:
> > > > CREATE PUBLICATION p1 FOR TABLE t1;
> > > > CREATE SUBSCRITION s1 ... FOR PUBLICATION p1 WITH (only_local = true);
> > > >
> > > > option 2:
> > > > CREATE PUBLICATION p1 FOR TABLE t1 WEHRE (publish = 'only_local');
> > > > CREATE SUBSCRITION s1 ... FOR PUBLICATION p1;
> > > >
> > > > ~~
> > > >
> > > > IIUC the patch is using option 1. My first impression was it feels
> > > > back-to-front for the SUBSCRIPTION telling the PUBLICATION what to
> > > > publish.
> > > >
> > > > So, why does the patch use syntax option 1?
> > >
> > > I felt the advantage with keeping it at the subscription side is that,
> > > the subscriber from one node can subscribe with only_local option on
> > > and a different subscriber from a different node can subscribe with
> > > only_local option as off. This might not be possible with having the
> > > option at publisher side. Having it at the subscriber side might give
> > > more flexibility for the user.
> > >
> >
> > OK. Option 2 needs two publications for that scenario. IMO it's more
> > intuitive this way, but maybe you wanted to avoid the extra
> > publications?
>
> Yes, I wanted to avoid the extra publication creation that you pointed
> out. Option 1 can handle this scenario without creating the extra
> publications:
> node0: CREATE PUBLICATION p1 FOR TABLE t1;
> node1: CREATE SUBSCRIPTION s1 ... FOR PUBLICATION p1 with (only_local = on);
> node2: CREATE SUBSCRIPTION s1 ... FOR PUBLICATION p1 with (only_local = off);
>
> I'm ok with both the approaches, now that this scenario can be handled
> by using both the options. i.e providing only_local option as an
> option while creating publication or providing only_local option as an
> option while creating subscription as Peter has pointed out at [1].
> option 1:
> CREATE PUBLICATION p1 FOR TABLE t1;
> CREATE SUBSCRITION s1 ... FOR PUBLICATION p1 WITH (only_local = true);
>
> option 2:
> CREATE PUBLICATION p1 FOR TABLE t1 WITH (publish = 'only_local');
> CREATE SUBSCRITION s1 ... FOR PUBLICATION p1;
>
> Shall we get a few opinions on this and take it in that direction?
>

Allowing multiple publications will lead to a lot of duplicate data in
catalogs like pg_publication_rel, so, I don't see how option-2 can be
beneficial?

--
With Regards,
Amit Kapila.