From: | Zheng Li <zhengli10(at)gmail(dot)com> |
---|---|
To: | li jie <ggysxcq(at)gmail(dot)com> |
Cc: | vignesh C <vignesh21(at)gmail(dot)com>, Peter Smith <smithpb2250(at)gmail(dot)com>, Ajin Cherian <itsajin(at)gmail(dot)com>, Dilip Kumar <dilipbalaut(at)gmail(dot)com>, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>, "houzj(dot)fnst(at)fujitsu(dot)com" <houzj(dot)fnst(at)fujitsu(dot)com>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>, Japin Li <japinli(at)hotmail(dot)com>, rajesh singarapu <rajesh(dot)rs0541(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Support logical replication of DDLs |
Date: | 2022-11-25 22:23:09 |
Message-ID: | CAAD30UJnjQkU+3Kbfg1P=-k9uf7o2N3pPB34uu-OXyN3+o_f-g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
Hello,
Thanks for the feedback.
> I have been following this patch for a long time.
> Recently, I started to try to test it. I found several bugs
> here and want to give you feedback.
>
> 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.
Thoughts?
Zheng
From | Date | Subject | |
---|---|---|---|
Next Message | Ron | 2022-11-25 23:45:42 | Practical maximum max_locks_per_transaction? |
Previous Message | Andrus | 2022-11-25 20:56:42 | How to create hot standby of Postgres 12/Debian 10.3 in Windows 11 |
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2022-11-25 22:46:11 | Re: pg_stat_bgwriter.buffers_backend is pretty meaningless (and more?) |
Previous Message | Cary Huang | 2022-11-25 21:41:14 | Re: [PATCH] Simple code cleanup in tuplesort.c. |