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

On Tue, May 10, 2022 at 9:33 PM Ajin Cherian <itsajin@gmail.com> wrote:
>
> On Fri, May 6, 2022 at 11:24 PM Amit Kapila <amit.kapila16@gmail.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.no-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':

Only the drop command of those tables in the publication is replicated.

This is achieved with two event trigger handlers, 'command start' and
'command end' for drop table commands.

The 'command start' event handler logs a ddl message with the relids
of the tables that are dropped which the output plugin (pgoutput)
stores in its internal data structure after verifying that it is for a
table that is part of the publication. Later the 'command end' event
handler
sends the actual drop message. Pgoutput on receiving the command end,
only sends out the drop command only if it is for one of the
relids marked for deleting. The reason we have to do this is because,
once the logical decoder receives the 'command end' message,
 the relid of the table is no longer valid as it has been deleted as
part of invalidations received for the drop table command.
It is no longer possible to verify if the table is part of the
publication list or not. To make this possible, I have added two more
elements
to the ddl xlog and ddl message, (relid and cmdtype).

We could have also handled all this on the subscriber side as well,
but that would mean sending spurious ddl messages for tables that
are not part of the publication.

Example:

publisher:

postgres=# create table test (a int, b text);
CREATE TABLE
postgres=# create table test1 (a int, b text);
CREATE TABLE
postgres=# create table test2 (a int, b text);
CREATE TABLE
postgres=# create publication tap_pub for TABLE test, test1 with
(publish='ddl'); CREATE PUBLICATION
postgres=# \d
       List of relations
 Schema | Name  | Type  | Owner
--------+-------+-------+-------
 public | test  | table | ajin
 public | test1 | table | ajin
 public | test2 | table | ajin
(3 rows)

subscriber:

postgres=# create table test (a int, b text);
CREATE TABLE
postgres=# create table test1 (a int, b text);
CREATE TABLE
postgres=# create table test2 (a int, b text);
CREATE TABLE
postgres=# CREATE SUBSCRIPTION sub CONNECTION '<CONNINFO>' PUBLICATION tap_pub;
NOTICE:  created replication slot "sub" on publisher
CREATE SUBSCRIPTION
postgres=# \d
       List of relations
 Schema | Name  | Type  | Owner
--------+-------+-------+-------
 public | test  | table | ajin
 public | test1 | table | ajin
 public | test2 | table | ajin
(3 rows)

publisher:

postgres=# drop table test1;
DROP TABLE
postgres=# \d
       List of relations
 Schema | Name  | Type  | Owner
--------+-------+-------+-------
 public | test  | table | ajin
 public | test2 | table | ajin
(2 rows)

subscriber:

postgres=# \d
       List of relations
 Schema | Name  | Type  | Owner
--------+-------+-------+-------
 public | test  | table | ajin
 public | test2 | table | ajin
(2 rows)
>>>> DROP table replicated.

publisher:

postgres=# drop table test2;
DROP TABLE
postgres=# \d
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 public | test | table | ajin
(1 row)


subscriber:

postgres=# \d
       List of relations
 Schema | Name  | Type  | Owner
--------+-------+-------+-------
 public | test  | table | ajin
 public | test2 | table | ajin
(2 rows)

>>>DROP table not replicated since table not in publication list.

regards,
Ajin Cherian