Re: Support logical replication of DDLs

> > >
> > > 1. CREATE TABLE LIKE
> > >   I found that this case may be repication incorrectly.
> > >    You can run the following SQL statement:
> > >    ```
> > >    CREATE TABLE ctlt1 (a text CHECK (length(a) > 2) PRIMARY KEY, b text);
> > > ALTER TABLE ctlt1 ALTER COLUMN a SET STORAGE MAIN;
> > > ALTER TABLE ctlt1 ALTER COLUMN b SET STORAGE EXTERNAL;
> > > CREATE TABLE ctlt1_like (LIKE ctlt1 INCLUDING ALL);
> > >    ```
> > >    The ctlt1_like table will not be able to correct the replication.
> > >   I think this is because create table like statement is captured by
> > >   the event trigger to a create table statement and multiple alter table statements.
> > >   There are some overlaps between them, and an error is reported when downstream replication occurs.
> >
> > I looked into this case. The root cause is the statement
> >
> > CREATE TABLE ctlt1_like (LIKE ctlt1 INCLUDING ALL);
> >
> > is executed internally using 3 DDLs:
> > 1. CREATE TABLE ctlt1_like (LIKE ctlt1 INCLUDING ALL); --The top level command
> > 2. ALTER TABLE ctlt1_like ADD CONSTRAINT ctlt1_a_check CHECK
> > (length(a) > 2); --The first subcommand
> > 3. CREATE UNIQUE INDEX ctlt1_like_pkey on ctlt1_like (a); --The second
> > subcommand that creates the primary key index
> >
> > All three commands are captured by the event trigger. The first and
> > second command ends up getting deparsed, WAL-logged and
> > replayed on the subscriber. The replay of the ALTER TABLE command
> > causes a duplicate constraint error. The problem is that
> > while subcommands are captured by event triggers by default, they
> > don't need to be deparsed and WAL-logged for DDL replication.
> > To do that we can pass the isCompleteQuery variable in
> > ProcessUtilitySlow to EventTriggerCollectSimpleCommand() and
> > EventTriggerAlterTableEnd() and make this information available in
> > CollectedCommand so that any subcommands can be skipped.
>
> Attaching the proposed fix in
> v40-0005-Do-not-generate-WAL-log-for-non-top-level-DDL-comman.patch.
> This patch adds a new boolean field isTopLevelCommand to
> CollectedCommand so that non-top level command
> can be skipped in the DDL replication event trigger functions. The
> patch also makes the information available by
> passing the isTopLevel variable in ProcessUtilitySlow to several
> EventTriggerCollect functions such as
> EventTriggerCollectSimpleCommand and EventTriggerAlterTableStart.

Patch v40-0005-Do-not-generate-WAL-log-for-non-top-level-DDL-comman.patch
broke the following test case:

CREATE TABLE product (id int PRIMARY KEY, name text);
CREATE TABLE orders (order_id int PRIMARY KEY, product_id int
REFERENCES product (id));

Because forein key constraint was not deparsed as part of CREATE TABLE
but rather replicated as a non-top-level command (which we
no longer replicate in patch v40-0005), fixed this in the attached patch:

diff --git a/src/backend/commands/ddl_deparse.c
b/src/backend/commands/ddl_deparse.c
index 89f33d451c..d9bb3aab8b 100755
--- a/src/backend/commands/ddl_deparse.c
+++ b/src/backend/commands/ddl_deparse.c
@@ -1087,8 +1087,6 @@ objtree_to_jsonb_rec(ObjTree *tree,
JsonbParseState *state)
  * the given elements list.  The updated list is returned.
  *
  * This works for typed tables, regular tables, and domains.
- *
- * Note that CONSTRAINT_FOREIGN constraints are always ignored.
  */
 static List *
 obtainConstraints(List *elements, Oid relationId, Oid domainId)
@@ -1146,7 +1144,8 @@ obtainConstraints(List *elements, Oid
relationId, Oid domainId)
                                contype = "check";
                                break;
                        case CONSTRAINT_FOREIGN:
-                               continue;               /* not here */
+                               contype = "foreign key";
+                               break;
                        case CONSTRAINT_PRIMARY:
                                contype = "primary key";
                                break;

Regards,
Zheng