Re: Handle infinite recursion in logical replication setup

On Wed, Mar 2, 2022 at 3:59 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Wed, Feb 23, 2022 at 11:59 AM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> >
> ...
> ...
> > I have attached a basic patch for this, if the idea is accepted, I
> > will work further to test more scenarios, add documentation, and test
> > and post an updated patch.
> > For the second problem, Table synchronization of table including local
> > data and replicated data using copy command.
> >
> > Let us consider the following scenario:
> > a) node1 publishing to node2 b) node2 publishing to node1. Here in
> > this case node1 will have replicated data from node2 and vice versa.
> >
> > In the above if user wants to include node3 to subscribe data from
> > node2. Users will have to create a subscription in node3 to get the
> > data from node2. During table synchronization we send the complete
> > table data from node2 to node3. Node2 will have local data from node2
> > and also replicated data from node1. Currently we don't have an option
> > to differentiate between the locally generated data and replicated
> > data in the heap which will cause infinite recursion as described
> > above.
> >
> > To handle this if user has specified only_local option, we could throw
> > a warning or error out while creating subscription in this case, we
> > could have a column srreplicateddata in pg_subscription_rel which
> > could indicate if the table has any replicated data or not:
> > postgres=# select * from pg_subscription_rel;
> > srsubid | srrelid | srsubstate | srsublsn | srreplicateddata
> > ---------+---------+------------+-----------+------------------
> > 16389 | 16384 | r | 0/14A4640 | t
> > 16389 | 16385 | r | 0/14A4690 | f
> > (1 row)
> >
> > In the above example, srreplicateddata with true indicates, tabel t1
> > whose relid is 16384 has replicated data and the other row having
> > srreplicateddata as false indicates table t2 whose relid is 16385
> > does not have replicated data.
> > When creating a new subscription, the subscriber will connect to the
> > publisher and check if the relation has replicated data by checking
> > srreplicateddata in pg_subscription_rel table.
> > If the table has any replicated data, log a warning or error for this.
> >
>
> If you want to give the error in this case, then I think we need to
> provide an option to the user to allow copy. One possibility could be
> to extend existing copy_data option as 'false', 'true', 'force'. For
> 'false', there shouldn't be any change, for 'true', if 'only_local'
> option is also set and the new column indicates replicated data then
> give an error, for 'force', we won't give an error even if the
> conditions as mentioned for 'true' case are met, rather we will allow
> copy in this case.

When a subscription is created with publish_local_only and copy_data,
it will connect to the publisher and check if the published tables
have also been subscribed from other nodes by checking if the entry is
present in pg_subscription_rel and throw an error if present. The
attached v4-0002-Support-force-option-for-copy_data-check-and-thro.patch
has the implementation for the same.
Thoughts?

Regards,
Vignesh