RE: Support logical replication of DDLs

On Thursday, May 26, 2022 1:46 PM Ajin Cherian <itsajin(at)gmail(dot)com> wrote:
>
> On Tue, May 10, 2022 at 9:33 PM Ajin Cherian <itsajin(at)gmail(dot)com> wrote:
> >
> > On Fri, May 6, 2022 at 11:24 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
> wrote:
> > > As we have hacked CreatePublication function for this POC, the
> > > regression tests are not passing but we can easily change it so that
> > > we invoke new functionality with the syntax proposed in this thread
> > > or with some other syntax and we shall do that in the next patch
> > > unless this approach is not worth pursuing.
> > >
> > > This POC is prepared by Ajin Cherian, Hou-San, and me.
> > >
> > > Thoughts?
> > >
> > > [1] -
> > >
> https://www.postgresql.org/message-id/20150215044814.GL3391%40alvh.n
> > > o-ip.org
> >
> > I have updated Amit's patch by including a public action "create" when
> > creating publication which is turned off by default.
> > Now the 'make check' tests pass. I also fixed a problem that failed to
> > create tables when the table has a primary key.
>
> I have updated this "deparse" patch-set to include support for 'drop table':
>

Here is the new version POC patches which add support for 'ALTER TABLE'

For non-rewrite ALTER TABLE command:
we deparse the command and WAL log the deparsed json string with the relid
of the altered table at table_rewrite event trigger. The WALSender decodes
the WAL and sends it to subscriber if the altered table is published. Most
of ALTER TABLE command are supported except some commands(DDL related to
PARTITIONED TABLE ...) that introduced recently which haven't been
supported by the current ddl_deparser, we will support that later.

For table_rewrite ALTER TABLE command:
(ALTER COLUMN TYPE, ADD COLUMN DEFAULT, SET LOGGED, SET ACCESS METHOD)

we deparse the command and WAL log the deparsed json string with the relid
of the altered table at table_rewrite event trigger. The WALSender decodes
the WAL and sends it to subscriber if the altered table is published.
Then, the WALSender will convert the upcoming rewrite INSERTs to UPDATEs
and send them to subscriber so that the data between publisher and
subscriber can always be consistent. Note that the tables that publish
rewrite ddl must have a replica identity configured in order to be able to
replicate the upcoming rewrite UPDATEs.

We do this way because of two reasons:
(1) The data before the rewrite ddl could already be different among
publisher and subscriber. To make sure the extra data in subscriber which
doesn't exist in publisher also get rewritten, we need to let the
subscriber execute the original rewrite ddl to rewrite all the data at
first.

(2) the data after executing rewrite ddl could be different among
publisher and subscriber(due to different functions/operators used during
rewrite), so we need to replicate the rewrite UPDATEs to keep the data
consistent.

Here is the example for the rewrite ddl: ALTER TABLE ADD COLUMN DEFAULT:

A table on the publisher side has rows:
ddl_test(a)
a
----
1
2

The same table on the subscriber side has rows:
ddl_test(a)
a
----
1
2
3
4

-----------------------------------
If we execute "ALTER TABLE ddl_test ADD COLUMN b int DEFAULT random();" on
publisher. The row(1,2) on subscriber will be updated by the rewrite
UPDATE received from publisher. The row(3,4) will be updated by exectuing
the "ADD COLUMN b int DEFAULT random();" on the subscriber.
-----------------------------------

ddl_test(a,b)
a|b
----
1|random num(pub)
2|random num(pub)

The same table on the subscriber side has rows:
ddl_test(a,b)
a|b
----
1|random num(pub)
2|random num(pub)
3|random num(sub)
4|random num(sub)

TO IMPROVE:

This approach could be improved by letting the subscriber try to update
the extra data itself instead of doing fully rewrite ddl and use the
upcoming rewrite UPDATEs to rewrite the rest data. To achieve this, we
could modify the deparsed json string to temporarily remove the rewrite
part and add some logic in subscriber to update the extra data.
Besides, we may not need to send rewrite changes for all type of rewrite
ddl, for example, it seems fine to skip sending rewrite changes for ALTER
TABLE SET LOGGED as the data in the table doesn't actually be changed. We
could use the deparser and event trigger to filter these ddls and skip
sending rewrite changes for them.

Best regards,
Hou zj