User-Agent: Cyrus-JMAP/3.7.0-alpha0-387-g7ea99c4045-fm-20220413.002-g7ea99c40
Mime-Version: 1.0
Message-Id: <3c646317-df34-4cb3-9365-14abeada6587@www.fastmail.com>
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>
Date: Thu, 14 Apr 2022 11:15:20 -0300
From: "Euler Taveira" <euler@eulerto.com>
To: "Dilip Kumar" <dilipbalaut@gmail.com>,
 "Amit Kapila" <amit.kapila16@gmail.com>
Cc: "Zheng Li" <zhengli10@gmail.com>,
 "PostgreSQL Hackers" <pgsql-hackers@lists.postgresql.org>,
 "rajesh singarapu" <rajesh.rs0541@gmail.com>
Subject: Re: Support logical replication of DDLs
Content-Type: multipart/alternative;
 boundary=97ac00df980c4323b0adadf1a1aebfec
Archived-At: 
 <https://www.postgresql.org/message-id/3c646317-df34-4cb3-9365-14abeada6587%40www.fastmail.com>
Precedence: bulk

--97ac00df980c4323b0adadf1a1aebfec
Content-Type: text/plain

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/

--97ac00df980c4323b0adadf1a1aebfec
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE html><html><head><title></title><style type=3D"text/css">p.Mso=
Normal,p.MsoNoSpacing{margin:0}</style></head><body><div>On Thu, Apr 14,=
 2022, at 6:26 AM, Dilip Kumar wrote:<br></div><blockquote type=3D"cite"=
 id=3D"qt" style=3D""><div>I agree.&nbsp; But here the bigger question i=
s what is the correct behavior<br></div><div>in case of the Alter Table?=
&nbsp; I mean for example in the publisher the<br></div><div>table gets =
rewritten due to the Access Method change then what should<br></div><div=
>be the behavior of the subscriber.&nbsp; One expected behavior is that =
on<br></div><div>subscriber also the access method gets changed and the =
data remains<br></div><div>the same as on the subscriber table(table can=
 be locally rewritten<br></div><div>based on new AM).&nbsp; Which seems =
quite sensible behavior to me.&nbsp; But if<br></div><div>we want this b=
ehavior then we can not replay the logical messages<br></div><div>genera=
ted by DML WAL because of table rewrite, otherwise we will get<br></div>=
<div>duplicate data, unless we plan to get rid of the current data and j=
ust<br></div><div>get all new data from the publisher. And if we do that=
 then the data<br></div><div>will be as per the latest data in the table=
 based on the publisher, so<br></div><div>I think first we need to defin=
e the correct behavior and then we can<br></div><div>design it according=
ly.<br></div></blockquote><div>You should forbid it. Unless you can deco=
mpose the command into multiple SQL<br></div><div>commands to make it a =
safe operation for logical replication.<br></div><div><br></div><div>Let=
's say you want to add a column with a volatile default.<br></div><div><=
br></div><div>ALTER TABLE foo ADD COLUMN bar double precision DEFAULT ra=
ndom();<br></div><div><br></div><div>If you replicate the DDL command as=
 is, you will have different data<br></div><div>downstream. You should f=
orbid it. However, this operation can be supported if<br></div><div>the =
DDL command is decomposed in multiple steps.<br></div><div><br></div><di=
v>-- add a new column without DEFAULT to avoid rewrite<br></div><div>ALT=
ER TABLE foo ADD COLUMN bar double precision;<br></div><div><br></div><d=
iv>-- future rows could use the DEFAULT expression<br></div><div>-- it a=
lso doesn't rewrite the table<br></div><div>ALTER TABLE foo ALTER COLUMN=
 bar SET DEFAULT random();<br></div><div><br></div><div>-- it effectivel=
y rewrites the table<br></div><div>-- all rows are built from one source=
 node<br></div><div>-- data will be the same on all nodes<br></div><div>=
UPDATE foo SET bar =3D random();<br></div><div><br></div><div>The ALTER =
TABLE ... ALTER COLUMN ... TYPE has a similar issue. This DDL command<br=
></div><div>can be decomposed to avoid the rewrite. If you are changing =
the data type, in<br></div><div>general, you add a new column and update=
s all rows doing the proper conversion.<br></div><div>(If you are updati=
ng in batches, you usually add a trigger to automatically<br></div><div>=
adjust the new column value for INSERTs and UPDATEs. Another case is whe=
n you<br></div><div>are reducing the the typmod (for example, varchar(10=
0) to varchar(20)). In this<br></div><div>case, the DDL command can de d=
ecomposed removing the typmod information (ALTER<br></div><div>TABLE ...=
 ALTER COLUMN ... TYPE varchar) and replacing it with a CHECK<br></div><=
div>constraint.<br></div><div><br></div><div>I didn't review this patch =
in depth but we certainly need to impose some DDL<br></div><div>restrict=
ions if we are replicating DDLs. There are other cases that should be<br=
></div><div>treated accordingly such as a TABLESPACE specification or a =
custom data type.<br></div><div><br></div><div><br></div><div id=3D"sig1=
12409080"><div class=3D"signature">--<br></div><div class=3D"signature">=
Euler Taveira<br></div><div class=3D"signature">EDB&nbsp;&nbsp; <a href=3D=
"https://www.enterprisedb.com/">https://www.enterprisedb.com/</a><br></d=
iv></div><div><br></div></body></html>
--97ac00df980c4323b0adadf1a1aebfec--