From: Euler Taveira <euler@eulerto.com>
To: Dilip Kumar <dilipbalaut@gmail.com>
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: Zheng Li <zhengli10@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: rajesh singarapu <rajesh.rs0541@gmail.com>
Subject: Re: Support logical replication of DDLs
Date: Thu, 14 Apr 2022 11:15:20 -0300
Message-ID: <3c646317-df34-4cb3-9365-14abeada6587@www.fastmail.com> (raw)
In-Reply-To: <CAFiTN-vz2V=u43Zkr7Y9Lz0w7G5qsQk=fKnc1x3ZcBO9_f0gbg@mail.gmail.com>
References: <CAAD30U+pVmfKwUKy8cbZOnUXyguJ-uBNejwD75Kyo=OjdQGJ9g@mail.gmail.com>
<CAFiTN-tM1zOk50xoeP5vdzaO0U_iQ3u8oZ2P1L7J=0ka+c7Utw@mail.gmail.com>
<CAAD30ULtoGp8L_GKbV15Wnm+X5r=SE7MOnYHuqBr396m26jJSA@mail.gmail.com>
<CAFiTN-uuE9PiZK-zCTR2ch+Knb15js+=Ot1nKUMK5_5JVK4yrw@mail.gmail.com>
<CAFiTN-sJB3Si2CXSfM8AeHk0K7binpY9r21WKmkd8y7Ebao7ZQ@mail.gmail.com>
<CAFiTN-tqqr88EO6OHstDmmqxp2ZtZeROwkfFARuS2k+8CREPJA@mail.gmail.com>
<CAAD30UJ8v7RAxfLLx-nVO9COKNeBiKVzcwu1LdCe5dZJU2bUDA@mail.gmail.com>
<CAFiTN-u9McY9Df2dUOX3u326LMJAY8uP6YE1K8GdpYp95x=S5Q@mail.gmail.com>
<CAA4eK1KGUyAh5_UG=RvhbeQh-aHvtgb4tsZY-5fb1Yw29FSrHw@mail.gmail.com>
<CAFiTN-vz2V=u43Zkr7Y9Lz0w7G5qsQk=fKnc1x3ZcBO9_f0gbg@mail.gmail.com>
On Thu, Apr 14, 2022, at 6:26 AM, Dilip Kumar wrote:
> I agree. But here the bigger question is what is the correct behavior
> in case of the Alter Table? I mean for example in the publisher the
> table gets rewritten due to the Access Method change then what should
> be the behavior of the subscriber. One expected behavior is that on
> subscriber also the access method gets changed and the data remains
> the same as on the subscriber table(table can be locally rewritten
> based on new AM). Which seems quite sensible behavior to me. But if
> we want this behavior then we can not replay the logical messages
> generated by DML WAL because of table rewrite, otherwise we will get
> duplicate data, unless we plan to get rid of the current data and just
> get all new data from the publisher. And if we do that then the data
> will be as per the latest data in the table based on the publisher, so
> I think first we need to define the correct behavior and then we can
> design it accordingly.
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 de 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.
--
Euler Taveira
EDB https://www.enterprisedb.com/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: euler@eulerto.com, dilipbalaut@gmail.com, amit.kapila16@gmail.com, zhengli10@gmail.com, pgsql-hackers@lists.postgresql.org, rajesh.rs0541@gmail.com
Subject: Re: Support logical replication of DDLs
In-Reply-To: <3c646317-df34-4cb3-9365-14abeada6587@www.fastmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox