MERGE/SPLIT PARTITIONS issues/questions - Mailing list pgsql-bugs

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?