> > >
> > > 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
