Re: MERGE/SPLIT PARTITIONS issues/questions

Hi, Zsolt!

Thank you for your valuable findings.

On Thu, Jul 23, 2026 at 1:59 PM Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com> wrote:
>
> 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.

MERGE/SPLIT partition(s) are DDL operations. We currently don't
support logical decoding of DDLs. So, I suppose we should just skip
logical decoding of inserts into new partition(s). 0001 patch
implements it with some tests and docs.

> 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?

The current approach of partition(s) MERGE/SPLIT is to create new
partition using the parent as the template without attempt to preserve
properties of previous partitions. That approach has been taken for
simplicity. If future we can add different behavior. But I see that
preserving replica identity can publication membership is essential to
continue streaming changes via partition root. 0002 patch implements
preserving these properties (simple case without identity using
index), and error out on mismatch.

> 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?

After 0002, if you publish via partition root, it's not even
necessarily to apply any changes on replica. Replica could continue
use its partition schema. If publish from leaf partitions, then
replica should manually get similar partition(s) MERGE/SPLIT DDL.

> 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?

I agree this behavior is incorrect. The patch 0003 implements copying
values of generated columns "as is". The exclusion are expressions
containing tableoid (system column which will change after completion
of MERGE/SPLIT DDL). Reject this case for now. In future we may
implement recalculation of such generated columns and further
constraints re-validation (if needed).

> 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

This was intended to keep patches simple enough for pg 19. That's
documented that we copy properties from parent, but don't copy from
previous partitions(s) [1][2]. We may implement other options in
further releases.

Links.
1. https://www.postgresql.org/docs/19/sql-altertable.html#SQL-ALTERTABLE-MERGE-PARTITIONS
2. https://www.postgresql.org/docs/19/sql-altertable.html#SQL-ALTERTABLE-SPLIT-PARTITION

------
Regards,
Alexander Korotkov
Supabase