Re: Support logical replication of DDLs

On Fri, 10 Feb 2023 at 21:50, vignesh C <vignesh21(at)gmail(dot)com> wrote:
> The attached v68 version patch has the changes for the same.

I was not sure if we should support ddl replication of
create/alter/drop subscription commands as there might be some data
inconsistency issues in the following cases:
#node1 who is running in port 5432
create publication pub_node1 for all tables with ( PUBLISH = 'insert,
update, delete, truncate');

#node2 who is running in port 5433
create publication pub_node2 for all tables with(PUBLISH = 'insert,
update, delete, truncate', ddl = 'all');
create subscription sub_node2 connection 'dbname=postgres host=node1
port=5432' publication pub_node1;

#node3
create subscription sub_node3 connection 'dbname=postgres host=node2
port=5433 publication pub_node2;

#node1
create table t1(c1 int );

#node2
create table t1(c1 int);
alter subscription sub_node2 refresh publication;

# Additionally this command will be replicated to node3, creating a
subscription sub2_node2 in node3 which will subscribe data from node1
create subscription sub2_node2 connection 'dbname=postgres host=node1
port=5432' publication pub_node1;

After this any insert into t1 from node1 will be replicated to node2
and node3, additionally node2's replicated data(which was replicated
from node1) will also be sent to node3 causing inconsistency. If the
table has unique or primary key constraints, it will lead to an error.

Another option would be to replicate the create subscription in
disabled state and not support few ddl replication of alter
subscription which will connect to publisher like:
1) Alter subscription sub1 enable;
2) Alter subscription sub1 refresh publication;

But in this case also, we will be able to support few alter
subscription commands and not support few alter subscription commands.
I feel it is better that we do not need to support ddl replication of
create/alter/drop subscription command and let users handle the
subscription commands.
Thoughts?

Regards,
Vignesh