Re: MERGE/SPLIT PARTITIONS issues/questions

> 4. Earlier I wrote that "the data didn't change"... but generated
> columns can silently change:

This point is a bit worse than my original description, I was able to
break some constraints with it. See attached reproducer scripts:

* merge-dangling-fk.sql results in a foreign key that appears to be
validated but contains dangling entries
* merge-invalid-check.sql breaks a check constraint
* merge-null-assert.sql inserts a NULL value into a NOT NULL column.
Crashes the debug build with an assertion, returns inconsistent data
in production builds.

On Thu, Jul 23, 2026 at 12:59 PM Zsolt Parragi
<zsolt(dot)parragi(at)percona(dot)com> wrote:
>
> Hello,
>
> I have multiple questions and potential issues with MERGE PARTITIONS /
> SPLIT PARTITIONS. I have ideas for fixing some of these problems, but
> not all of them, so I'd like to just discuss them before proposing
> anything specific:
>
> 1. Moved rows are inserted with plain heap inserts, so they are
> decoded as INSERTs into the new partition, with no matching deletes.
> This should either emit matching deletes before, or also skip the
> inserts, as the current behavior seems to break logical replication.
> The latter looks like a better solution to me, but I am not 100% sure
> about it.
>
> 2. Should the new partition inherit direct publication membership from
> the partitions it replaces, especially for a split where this is
> clear? For a merge it's harder to argue about if the original
> partitions are different.
> Similarly what about replica identity?
>
> 3. What's the proper process to propagate a merge/split to a
> subscriber without data loss?
> For now let's assume that we implement the "no generated inserts"
> change I mentioned above, so that it at least works.
> * Everything in sync at the beginning
> * Merge command executed on publisher
> * An UPDATE targeting a merged row is executed on the publishers
> * Subscriber stops: can't execute the UPDATE
> * Subscriber needs a manual MERGE replay, table now exists locally,
> but it is not part of the subscription
> * Apply worker retries the update, sees the table, but it's not part
> of the subscription, so it drops the update
> * User runs REFRESH PUBLICATION with copy_table=false because the data
> is already there, the previous update was lost
>
> So seems like the working approach is either to TRUNCATE before
> REFRESH PUBLICATION, or to manually DROP/CREATE the partitions? Should
> this be documented somewhere?
>
> 4. Earlier I wrote that "the data didn't change"... but generated
> columns can silently change:
>
> CREATE TABLE t (id int, g int GENERATED ALWAYS AS (id * 2) STORED)
> PARTITION BY RANGE (id);
> CREATE TABLE t1 (id int, g int GENERATED ALWAYS AS (id * 100) STORED);
> ALTER TABLE t ATTACH PARTITION t1 FOR VALUES FROM (0) TO (10);
> CREATE TABLE t2 PARTITION OF t FOR VALUES FROM (10) TO (20);
>
> INSERT INTO t VALUES (3), (12);
> -- 3|300 12|24
> SELECT id, g FROM t ORDER BY id;
>
> ALTER TABLE t MERGE PARTITIONS (t1, t2) INTO t12;
> -- 3|6 12|24
> SELECT id, g FROM t ORDER BY id;
>
> Or another example:
>
> CREATE FUNCTION f(i int) RETURNS int IMMUTABLE LANGUAGE sql AS 'SELECT i * 2';
> CREATE TABLE t (id int, g int GENERATED ALWAYS AS (f(id)) STORED)
> PARTITION BY RANGE (id);
> CREATE TABLE t1 PARTITION OF t FOR VALUES FROM (0) TO (10);
> CREATE TABLE t2 PARTITION OF t FOR VALUES FROM (10) TO (20);
> INSERT INTO t VALUES (3), (12);
> -- 3|6 12|24
> SELECT id, g FROM t ORDER BY id;
>
> CREATE OR REPLACE FUNCTION f(i int) RETURNS int IMMUTABLE LANGUAGE sql
> AS 'SELECT i * 100';
>
> VACUUM FULL t; -- same result with unrelated rewriting alter
> -- 3|6 12|24
> SELECT id, g FROM t ORDER BY id;
>
> ALTER TABLE t MERGE PARTITIONS (t1, t2) INTO t12;
> -- 3|300 12|1200
> SELECT id, g FROM t ORDER BY id;
>
> This example is especially interesting because with VACUUM FULL or an
> unrelated rewriting ALTER TABLE, the data remains unchanged, so while
> this is a corner case, it can be surprising for users.
>
> The second example seems fixable to me, even if difficult, but I'm not
> sure what would be a good approach for the first, other than erroring
> out instead?
>
> 5. In (2) I mentioned replication-related inheritance questions, but
> it is much more generic than that, many partition specific details get
> lost silently:
> * indexes
> * constraints
> * different DEFAULTs
> * foreign keys
> * triggers
> * reloptions
> * custom tablespace
> * table AM
> * per column settings
> * security labels
> * ACLs
> * RLS policies
>
> Shouldn't most of these copied into split partitions, and handled
> properly in merges (erroring out in non trivial cases?)
>
> Silently dropping them doesn't seem like a good behavior, as it can
> cause many different issues:
> * dropping foreign keys / checks can cause data integrity issues
> * dropping partition specific sequences can cause later inserts to
> fail or silently fall back to nulls/different values
> * probably many other scenarios I didn't think of
>
> What do you think about the above points, how would you fix them? Are
> (some of) these acceptable as limitations/known issues for the feature
> in 19?