Re: Handle infinite recursion in logical replication setup

On Mon, Mar 7, 2022 at 10:05 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Mon, Mar 7, 2022 at 9:26 AM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> >
> > On Wed, Feb 23, 2022 at 11:59 AM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> > > Here there are two problems for the user: a) incremental
> > > synchronization of table sending both local data and replicated data
> > > by walsender b) Table synchronization of table using copy command
> > > sending both local data and replicated data
> > >
> > > For the first problem "Incremental synchronization of table by
> > > Walsender" can be solved by:
> > > Currently the locally generated data does not have replication origin
> > > associated and the data that has originated from another instance will
> > > have a replication origin associated. We could use this information to
> > > differentiate locally generated data and replicated data and send only
> > > the locally generated data. This "only_local" could be provided as an
> > > option while subscription is created:
> > > ex: CREATE SUBSCRIPTION sub1 CONNECTION 'dbname =postgres port=5433'
> > > PUBLICATION pub1 with (only_local = on);
> >
> > I haven't yet gone through the patch, but I have a question about the
> > idea. Suppose I want to set up a logical replication like,
> > node1->node2->node3->node1. So how would I create the subscriber at
> > node1? only_local=on or off?. I mean on node1, I want the changes
> > from node3 which are generated on node3 or which are replicated from
> > node2 but I do not want changes that are replicated from node1 itself?
> > So if I set only_local=on then node1 will not get the changes
> > replicated from node2, is that right? and If I set only_local=off then
> > it will create the infinite loop again? So how are we protecting
> > against this case?
> >
>
> In the above topology if you want local changes from both node3 and
> node2 then I think the way to get that would be you have to create two
> subscriptions on node1. The first one points to node2 (with
> only_local=off) and the second one points to node3 (with only_local
> =off).
>

Sorry, I intend to say 'only_local=on' at both places in my previous email.

--
With Regards,
Amit Kapila.