Re: Handle infinite recursion in logical replication setup

On Mon, Mar 7, 2022 at 10:15 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > > 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.

Hmm okay, so for this topology we will have to connect node1 directly
to node2 as well as to node3 but can not cascade the changes. I was
wondering can it be done without using the extra connection between
node2 to node1? I mean instead of making this a boolean flag that
whether we want local change or remote change, can't we control the
changes based on the origin id? Such that node1 will get the local
changes of node3 but with using the same subscription it will get
changes from node3 which are originated from node2 but it will not
receive the changes which are originated from node1.

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com