Hello, > 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();
I looked more into this. In order to support statements like "ALTER TABLE foo ADD COLUMN bar double precision DEFAULT random();", we have two potential solutions, but both of them are non-trivial to implement: 1. As Euler pointed out, we could decompose the statement on the publisher into multiple statements so that the table rewrite (using volatile function) is handled by a DML sub-command. The decomposition requires changes in parse analysis/transform. We also need new logic to assemble the decomposed DDL commands string back from the parse trees so we can log them for logical replication. 2. Force skipping table rewrite when executing the same command on the subscriber, and let DML replication replicate the table rewrite from the publisher. The problem is table rewrite is not replicated at all today, and it doesn't seem easy to just enable it for logical replication. Table rewrite is an expensive operation involving heap file swap, details can be found in ATRewriteTables(). In light of this, I propose to temporarily block replication of such DDL command on the replication worker until we figure out a better solution. This is implemented in patch 0008-Fail-replication-worker-on-DDL-command-that-rewrites.patch. Notice only DDL statements that rewrite table using a VOLATILE expression will be blocked. I don't see a problem replicating non-volatile expression. Here is the github commit of the same patch: https://github.com/zli236/postgres/commit/1e6115cb99a1286a61cb0a6a088f7476da29d0b9 > 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 tested ALTER TABLE ... ALTER COLUMN ... TYPE. It seems to be working fine. Is there a particular case you're concerned about? > -- > Euler Taveira > EDB https://www.enterprisedb.com/ > Regards, Zheng Li
0008-Fail-replication-worker-on-DDL-command-that-rewrites.patch
Description: Binary data
