Re: Handle infinite recursion in logical replication setup

On Fri, Mar 11, 2022 at 4:28 PM kuroda(dot)hayato(at)fujitsu(dot)com
<kuroda(dot)hayato(at)fujitsu(dot)com> wrote:
>
> Hi Vegnesh,
>
> While considering about second problem, I was very confusing about it.
> I'm happy if you answer my question.
>
> > 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.
>
> IIUC srreplicateddata represents whether the subscribed data is not
> generated from the publisher, but another node.
> My first impression was that the name 'srreplicateddata' is not friendly
> because all subscribed data is replicated from publisher.
> Also I was not sure how value of the column was set.
> IIUC a filtering by replication origins is done in publisher node
> and subscriber node cannot know
> whether some data are really filtered or not.
> If we distinguish by subscriber option publish_local_only,
> it cannot reproduce your example because same subscriber have different 'srreplicateddata'.

Let's consider an existing Multi master logical replication setup
between Node1 and Node2 that is created using the following steps:
a) Node1 - Publication publishing employee table - pub1
b) Node2 - Subscription subscribing from publication pub1 with
publish_local_only - sub1_pub1_node1
c) Node2 - Publication publishing employee table - pub2
d) Node1 - Subscription subscribing from publication pub2 with
publish_local_only - sub2_pub2_node2

To create a subscription in node3, we will be using the following steps:
a) Node2 - Publication publishing employee table. - pub3
b) Node3 - Subscription subscribing from publication in Node2 with
publish_local_only - sub3_pub3_node2

When we create a subscription in Node3, Node3 will connect to
Node2(this will not be done in Node3) and check if the employee table
is present in pg_subscription_rel, in our case Node2 will have
employee table present in pg_subscription_rel (sub1_pub1_node1
subscribing to employee table from pub1 in Node1). As employee table
is being subscribed in node2 from node1, we will throw an error like
below:
postgres=# create subscription sub2 CONNECTION 'dbname =postgres port
= 9999' publication pub2 with (publish_local_only=on);
ERROR: CREATE/ALTER SUBSCRIPTION with publish_local_only and
copy_data as true is not allowed when the publisher might have
replicated data, table:public.t1 might have replicated data in the
publisher
HINT: Use CREATE/ALTER SUBSCRIPTION with copy_data = off or force

I was initially planning to add srreplicateddata field but I have
changed it slightly to keep the design simple. Now we just check if
the relation is present in pg_subscription_rel and throw an error if
copy_data and publish_local_only option is specified. The changes for
the same are available at [1].

[1] - https://www.postgresql.org/message-id/CALDaNm0V%2B%3Db%3DCeZJNAAUO2PmSXH5QzNX3jADXb-0hGO_jVj0vA%40mail.gmail.com
Thoughts?

Regards,
Vignesh