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

One question,

I understand that we create/enable triggers on create publication command flow.
I am wondering how this works in case of logical replication using slots.


thanks
Rajesh

On Mon, Nov 28, 2022 at 10:17 AM Zheng Li <zhengli10@gmail.com> wrote:
>
> > > >
> > > > 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