From: | Zheng Li <zhengli10(at)gmail(dot)com> |
---|---|
To: | Euler Taveira <euler(at)eulerto(dot)com> |
Cc: | Dilip Kumar <dilipbalaut(at)gmail(dot)com>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, rajesh singarapu <rajesh(dot)rs0541(at)gmail(dot)com> |
Subject: | Re: Support logical replication of DDLs |
Date: | 2022-04-14 20:31:15 |
Message-ID: | CAAD30ULkkGsFD_rUs1p5ReMuvrSk8vX0W0P17RGa-V3b+7JLFw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
> You should forbid it. Unless you can decompose the command into multiple SQL
> commands to make it a safe operation for logical replication.
>
> Let's say you want to add a column with a volatile default.
>
> ALTER TABLE foo ADD COLUMN bar double precision DEFAULT random();
>
> If you replicate the DDL command as is, you will have different data
> downstream. You should forbid it. However, this operation can be supported if
> the DDL command is decomposed in multiple steps.
>
> -- add a new column without DEFAULT to avoid rewrite
> ALTER TABLE foo ADD COLUMN bar double precision;
>
> -- future rows could use the DEFAULT expression
> -- it also doesn't rewrite the table
> ALTER TABLE foo ALTER COLUMN bar SET DEFAULT random();
>
> -- it effectively rewrites the table
> -- all rows are built from one source node
> -- data will be the same on all nodes
> UPDATE foo SET bar = random();
>
> The ALTER TABLE ... ALTER COLUMN ... TYPE has a similar issue. This DDL command
> can be decomposed to avoid the rewrite. If you are changing the data type, in
> general, you add a new column and updates all rows doing the proper conversion.
> (If you are updating in batches, you usually add a trigger to automatically
> adjust the new column value for INSERTs and UPDATEs. Another case is when you
> are reducing the the typmod (for example, varchar(100) to varchar(20)). In this
> case, the DDL command can be decomposed removing the typmod information (ALTER
> TABLE ... ALTER COLUMN ... TYPE varchar) and replacing it with a CHECK
> constraint.
>
> I didn't review this patch in depth but we certainly need to impose some DDL
> restrictions if we are replicating DDLs. There are other cases that should be
> treated accordingly such as a TABLESPACE specification or a custom data type.
This is helpful. Thanks.
Zheng
From | Date | Subject | |
---|---|---|---|
Next Message | Zheng Li | 2022-04-15 02:08:58 | Re: Support logical replication of DDLs |
Previous Message | Tom Lane | 2022-04-14 17:24:07 | Re: ERROR: XX000: cache lookup failed for type 75083631 |
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2022-04-14 20:37:06 | Re: CLUSTER on partitioned index |
Previous Message | Gilles Darold | 2022-04-14 20:27:46 | Re: [Proposal] vacuumdb --schema only |