Re: Support logical replication of DDLs - Mailing list pgsql-hackers

On Sat, Feb 11, 2023 at 3:31 AM vignesh C <vignesh21@gmail.com> wrote:
>
> On Fri, 10 Feb 2023 at 21:50, vignesh C <vignesh21@gmail.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?
>

So essentially, node3 is subscribed 2x from the same table in node1

node1 --> node2
|                | ddl
|                V
+---------> node3

I think the suggested options are:

option #1. If you support CREATE SUBSCRIPTION DDL replication then you
can end up with the conflict troubles you described above

option #2. If you support CREATE SUBSCRIPTION DDL replication but only
make sure it is disabled first then your above scenario might be OK
but you will also need to *not* allow DDL replication of the ALTER
SUBSCRIPTION which might cause it to become re-enabled. IMO adding
tricky rules is just inviting more problems.

option #3. Do nothing, don't support it. +1 but see below for a
variation of this

~

Actually, I am sort of expecting lots of potential difficulties with
DDL replication and this CREATE SUBSCRIPTION problem is just one of
them. IMO it would be a mistake to try and make the first version of
these patches try to do *everything*. E.g. Why invent tricky solutions
to problems without yet knowing user expectations/requirements?

Therefore, my first impression is to do a generic option #4:

option #4. This is a variation of "do nothing". My suggestion is you
can still replicate every DDL via logical replication messages but
just don't actually *apply* anything on the subscriber side for the
commands which are problematic (like this one is). Instead of
applying, the subscriber can just log a NOTICE about each command that
was skipped. This will make it easier for the user to know what didn’t
happen, but they can just cut/paste that command from the NOTICE if
they really want to. Also, this option #4 is deliberately generic,
which means you can do the same for every kind of DDL that proves too
difficult to automate in the first version (I doubt CREATE
SUBSCRIPTION will be the only example).

The option #4 result might look like this:

----
test_sub=# create subscription sub1 connection 'dbname=test_pub'
publication pub1;
NOTICE: created replication slot "sub1" on publisher CREATE SUBSCRIPTION
NOTICE: subscription "sub1" skipping DDL: create subscription
sub_node2 connection 'dbname=postgres host=node1 port=5432'
publication pub_node1;
...
----

(And if it turns out users really do want this then it can be
revisited in later patch versions)

------
Kind Regards,
Peter Smith.
Fujitsu Australia