Re: Handle infinite recursion in logical replication setup

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?

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