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.parragi@percona.com> wrote:

On Thu, Jul 23, 2026 at 10:29 PM Zsolt Parragi
<zsolt.parragi@percona.com> wrote:

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.

Previously, we assumed that ALTER TABLE ... MERGE PARTITION simply combined the
contents of multiple partitions into a new partition.
However, the generation expressions defined on the partitions may differ from
those of the partitioned table. As a result, if the table contains generated
columns, the data in the newly created partition may not be identical to the
combined contents of the merged partitions.

Therefore, when the partitioned table contains generated columns, we must
reverify NOT NULL constraints, CHECK constraints, and foreign key constraints
for ALTER TABLE ... MERGE PARTITION.

I combined these fixes into one patch.
some of the comments is directly copied from ATRewriteTable.

--
jian
https://www.enterprisedb.com/

As a result, if the table contains generated
columns, the data in the newly created partition may not be identical to the
combined contents of the merged partitions.

Therefore, when the partitioned table contains generated columns, we must
reverify NOT NULL constraints, CHECK constraints, and foreign key constraints
for ALTER TABLE ... MERGE PARTITION.

Yes, we can certainly can patch it this way. But should we? This current behavior is inconsistent with how generated columns behave with other SQL commands. That's why I didn't attach a patch in my previous emails, I think the current way this behaves is wrong.

My proposal would be to reject MERGE if it would cause a difference in generator expressions (or if it causes any other surprising changes), and keep the exact definition of the partition for SPLIT. Otherwise we end up with a surprising behavior in PG19, and if we want to fix it in later releases, it'll be a significant behavior change between major versions for the same command.

On Sat, Aug 1, 2026 at 7:11 PM Zsolt Parragi <zsolt.parragi@percona.com> wrote:

As a result, if the table contains generated
columns, the data in the newly created partition may not be identical to the
combined contents of the merged partitions.

Therefore, when the partitioned table contains generated columns, we must
reverify NOT NULL constraints, CHECK constraints, and foreign key constraints
for ALTER TABLE ... MERGE PARTITION.

Yes, we can certainly can patch it this way. But should we? This
current behavior is inconsistent with how generated columns behave
with other SQL commands. That's why I didn't attach a patch in my
previous emails, I think the current way this behaves is wrong.

My proposal would be to reject MERGE if it would cause a difference in
generator expressions (or if it causes any other surprising changes),
and keep the exact definition of the partition for SPLIT. Otherwise we
end up with a surprising behavior in PG19, and if we want to fix it in
later releases, it'll be a significant behavior change between major
versions for the same command.

For ALTER TABLE pp MERGE PARTITIONS (pp1, pp2) INTO pp12, a CHECK constraint
that exists only on pp2 surely should not apply to the new pp12. Otherwise, that
constraint would also end up being enforced against pp1's data, regardless of
whether pp1 actually satisfies its definition, that would seem weird, IMHO.

IMHO, it makes sense to drop each individual partition's {indexes, constraints,
column DEFAULTs, foreign keys, triggers, reloptions, custom tablespace, table
AM, per-column settings, security labels, ACLs, RLS policies}, and instead have
them inherit/depend on the parent's definitions.

The main reason I favor this approach: regrading the table's depent(indexes,
constraints etc) partitions being merged can differ from one another, so there's
no good justification for favoring any single partition's definitions over the
others.

For this case, ALTER TABLE MERGE PARTITIONS should let the new
partition use the partitioned table's generation expression, i think.

For ALTER TABLE pp MERGE PARTITIONS (pp1, pp2) INTO pp12, a CHECK constraint
that exists only on pp2 surely should not apply to the new pp12. Otherwise, that
constraint would also end up being enforced against pp1's data, regardless of
whether pp1 actually satisfies its definition, that would seem weird, IMHO.

I agree, that's why I proposed failing the MERGE in this situation, and to only allow it to proceed if pp1 and pp2 have he same definition.

For this case, ALTER TABLE MERGE PARTITIONS should let the new
partition use the partitioned table's generation expression, i think.

My issue is that no other ALTER TABLE statement does that.

Consider this scenario:

CREATE FUNCTION f(i int) RETURNS int IMMUTABLE LANGUAGE sql AS 'SELECT i * 2';
CREATE TABLE t (i int, j int, k int, g int GENERATED ALWAYS AS (f(j*k)) STORED);
INSERT INTO t VALUES (1,1,1), (2,2,2), (3,3,3);
SELECT * FROM t;
i | j | k | g
---+---+---+----
1 | 1 | 1 | 2
2 | 2 | 2 | 8
3 | 3 | 3 | 18
CREATE OR REPLACE FUNCTION f(i int) RETURNS int IMMUTABLE LANGUAGE sql AS 'SELECT i * 3'; -- change unrelated column that completely rewrites the table
SELECT * FROM t;
i | j | k | g
---+---+---+----
1 | 1 | 1 | 2
2 | 2 | 2 | 8
3 | 3 | 3 | 18

So we get the same results: it kept the old values. Any unrelated ALTER that doesn't change the generator expression or its dependents will do this: keep the generated values as is, even if it rewrites the relation file.

And what about ALTERs that try to change one of its dependent, and would result in an unintuitive/hidden regeneration of the generated column?

ALTER TABLE t ALTER COLUMN j TYPE smallint;
2026-08-02 07:38:13.368 WEST [1110699] ERROR: cannot alter type of a column used by a generated column
2026-08-02 07:38:13.368 WEST [1110699] DETAIL: Column "j" is used by generated column "g".
2026-08-02 07:38:13.368 WEST [1110699] STATEMENT: ALTER TABLE t ALTER COLUMN j TYPE smallint;
ERROR: cannot alter type of a column used by a generated column
DETAIL: Column "j" is used by generated column "g"

It fails. And even if I alter the type of g directly:

ALTER TABLE t ALTER COLUMN g TYPE text;
ALTER TABLE
postgres=# SELECT * FROM t;
i | j | k | g
---+---+---+----
1 | 1 | 1 | 2
2 | 2 | 2 | 8
3 | 3 | 3 | 18
(3 rows)

It doesn't change. The only ALTER that causes it to change is ALTER TABLE t COLUMN g SET EXPRESSION, which explicitly changes the expression.

This would be the only ALTER TABLE command that behaves differently, every other operation that would possibly change the generated expression in a hidden way either reuses the old values (if it safely can), or errors out (if it can't). It would be fine if this would be called CREATE TABLE AS MERGE PARTITIONS and CREATE TABLE AS SPLIT PARTITIONS, but it's called an ALTER TABLE, not a CREATE TABLE.

And also, think about SPLIT PARTITION: in the split scenario, what reasoning do we have to "reuse the partitioned table's generation expression"? We could very easily reuse the partition's definition, and copy the current values, there's no complex logic to follow there.

Hi, Zsolt!

Thank you for your valuable findings.

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

On Tue, Aug 4, 2026 at 3:26 AM Alexander Korotkov <aekorotkov@gmail.com> wrote:

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).

Copying the value of generated column "as is" can produce data that differs from
what the generated expression would compute if any merged partition's generation
expression differs from the partitioned table's.

For example:
DROP TABLE if exists t, tp_0_1, tp_0_2;
CREATE TABLE t (
id int,
g int GENERATED ALWAYS AS (NULLIF(id, 1)) STORED NOT NULL) PARTITION
BY RANGE (id);
CREATE TABLE tp_0_1 (g int GENERATED ALWAYS AS (id) STORED NOT NULL, id int);
ALTER TABLE t ATTACH PARTITION tp_0_1 FOR VALUES FROM (0) TO (10);
CREATE TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (10) TO (20);
INSERT INTO t VALUES (1), (2);
ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;

Now the generation expression for column g in tp_0_2 is ``NULLIF(id,
1) STORED``,
but the existing data (SELECT g FROM tp_0_2;) does not match what that
expression would compute.

This seems not OK?

On Wed, Aug 5, 2026 at 3:27 AM jian he <jian.universality@gmail.com> wrote:

On Tue, Aug 4, 2026 at 3:26 AM Alexander Korotkov <aekorotkov@gmail.com> wrote:

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).

Copying the value of generated column "as is" can produce data that differs from
what the generated expression would compute if any merged partition's generation
expression differs from the partitioned table's.

For example:
DROP TABLE if exists t, tp_0_1, tp_0_2;
CREATE TABLE t (
id int,
g int GENERATED ALWAYS AS (NULLIF(id, 1)) STORED NOT NULL) PARTITION
BY RANGE (id);
CREATE TABLE tp_0_1 (g int GENERATED ALWAYS AS (id) STORED NOT NULL, id int);
ALTER TABLE t ATTACH PARTITION tp_0_1 FOR VALUES FROM (0) TO (10);
CREATE TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (10) TO (20);
INSERT INTO t VALUES (1), (2);
ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;

Now the generation expression for column g in tp_0_2 is ``NULLIF(id,
1) STORED``,
but the existing data (SELECT g FROM tp_0_2;) does not match what that
expression would compute.

This seems not OK?

Actually, this makes me uneasy. What about restricting SPLIT/MERGE to
the case when generated columns matching between source partitions and
parent. This is the only solution I consider appropriate at this
stage of development.

------
Regards,
Alexander Korotkov
Supabase

On Thu, Aug 6, 2026 at 1:02 AM Alexander Korotkov <aekorotkov@gmail.com> wrote:

Actually, this makes me uneasy. What about restricting SPLIT/MERGE to
the case when generated columns matching between source partitions and
parent. This is the only solution I consider appropriate at this
stage of development.

drop table if exists x;
CREATE TABLE x (id int, g int GENERATED ALWAYS AS (NULLIF(tableoid,
18470)) NOT NULL) partition by range(id);
CREATE TABLE x1 PARTITION OF x FOR VALUES FROM (10) TO (20);
CREATE TABLE x2 PARTITION OF x FOR VALUES FROM (20) TO (30);
ALTER TABLE x MERGE PARTITIONS (x1, x2) INTO x12;

It's possible that the new table x12's tableoid is 18470, and
MergePartitionsMoveRows, checkPartitionRowConstraints did nothing
about it.
So at the end of checkPartitionGenExprMatchesParent,
we can use expression_references_system_column(generation_expr) to
guard against such corner case, regardless of the generated column
kind.

Please check the attached diff to address this issue.

expression_references_system_column is a useful helper function that
can be reused in multiple places, so I also added its declaration.
In our context, we can use it in createTableConstraints, which is
better than pull_varattnos i think.
I also did pgindent on tablecmds.c

(I didn't review v2-0001, v2-0002).

Copying the value of generated column "as is" can produce data that differs from
what the generated expression would compute if any merged partition's generation
expression differs from the partitioned table's.

I think this would be probably fine, as we can get the same effect by replacing a function used by the expression, a preexisting condition for many existing cases. But I do agree that requiring the same expression is a better approach.

Also, not directly related to this patch, but now that I looked into this, I can still use tableoids for check constraints with a text cast:

CREATE TABLE t (i int) PARTITION BY RANGE (i);
CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1);
CREATE TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2);
ALTER TABLE t ADD CONSTRAINT cc CHECK (tableoid::regclass::text <> 'tp_0_2');
INSERT INTO t VALUES (0),(1);
ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2; -- SUCCESS, but should ERROR instead?

And another question I realized while looking at differences to other rewrite operators: currently merge/split doesn't fire a rewrite event trigger, but shouldn't it?

For the replication changes: shouldn't we also restrict schema changes? `TABLES IN SCHEMA` can still be problematic if the parent and the specific partitions are in different schemas, they either get published or unpublished.

+      <command>ALTER TABLE ... MERGE PARTITIONS</command> is a schema change and
+      is not itself replicated to logical replication subscribers; to reflect it
+      on a subscriber, run the equivalent command there, or drop and recreate
+      the affected partitions and refresh the subscription.

I think this still results in my original (3) data loss scenario, so I don't think it's a good idea to recommend it.

For example if we MERGE + UPDATE/INSERT on the publisher, the subscriber worker error-loops on the merged partition not existing. We replay the MERGE locally on the subscriber, the worker continues before we have a chance to run REFRESH PUBLICATION and discards the UPDATE/INSERT.

On Fri, Aug 7, 2026 at 6:59 AM Zsolt Parragi <zsolt.parragi@percona.com> wrote:

Copying the value of generated column "as is" can produce data that differs from
what the generated expression would compute if any merged partition's generation
expression differs from the partitioned table's.

I think this would be probably fine, as we can get the same effect by
replacing a function used by the expression, a preexisting condition
for many existing cases. But I do agree that requiring the same
expression is a better approach.

Also, not directly related to this patch, but now that I looked into
this, I can still use tableoids for check constraints with a text
cast:

CREATE TABLE t (i int) PARTITION BY RANGE (i);
CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1);
CREATE TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2);
ALTER TABLE t ADD CONSTRAINT cc CHECK (tableoid::regclass::text <> 'tp_0_2');
INSERT INTO t VALUES (0),(1);
ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2; --
SUCCESS, but should ERROR instead?

Interesting!

Before we call MergePartitionsMoveRows, we did RestrictSearchPath(),
which will set GUC search_path
to "pg_catalog, pg_temp" temporally, and text_regclass will consider
search_path when resolve object name.

On the other hand, if we unconditionally validate all the partitioned
table's inherited CHECK constraints, it may fail
and the resulting message isn't helpful.
The error message below shows what happens when evaluating all CHECK
constraints during MERGE PARTITIONS.

DROP TABLE IF EXISTS t;
CREATE TABLE t (i int, b text default 't') PARTITION BY RANGE (i);
CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1);
CREATE TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2);
ALTER TABLE t ADD CONSTRAINT cc CHECK (b::regclass::text in ('t',
'tp_0_1', 'tp_0_2', 'tp_1_2'));
INSERT INTO t VALUES (0);
INSERT INTO t VALUES (0, 'tp_0_1'), (1, 'tp_1_2'), (1, 'public.tp_1_2');
ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;
ERROR: relation "t" does not exist

--
jian
https://www.enterprisedb.com/

Jian,
Zsolt,

Thank you both for your valuable catches. Attached is v3 addressing
the points raised.

On Fri, Aug 7, 2026 at 6:42 AM jian he <jian.universality@gmail.com> wrote:

On Fri, Aug 7, 2026 at 6:59 AM Zsolt Parragi <zsolt.parragi@percona.com> wrote:

Copying the value of generated column "as is" can produce data that differs from
what the generated expression would compute if any merged partition's generation
expression differs from the partitioned table's.

I think this would be probably fine, as we can get the same effect by
replacing a function used by the expression, a preexisting condition
for many existing cases. But I do agree that requiring the same
expression is a better approach.

Also, not directly related to this patch, but now that I looked into
this, I can still use tableoids for check constraints with a text
cast:

CREATE TABLE t (i int) PARTITION BY RANGE (i);
CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1);
CREATE TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2);
ALTER TABLE t ADD CONSTRAINT cc CHECK (tableoid::regclass::text <> 'tp_0_2');
INSERT INTO t VALUES (0),(1);
ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2; --
SUCCESS, but should ERROR instead?

Interesting!

Before we call MergePartitionsMoveRows, we did RestrictSearchPath(),
which will set GUC search_path
to "pg_catalog, pg_temp" temporally, and text_regclass will consider
search_path when resolve object name.

On the other hand, if we unconditionally validate all the partitioned
table's inherited CHECK constraints, it may fail
and the resulting message isn't helpful.
The error message below shows what happens when evaluating all CHECK
constraints during MERGE PARTITIONS.

DROP TABLE IF EXISTS t;
CREATE TABLE t (i int, b text default 't') PARTITION BY RANGE (i);
CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1);
CREATE TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2);
ALTER TABLE t ADD CONSTRAINT cc CHECK (b::regclass::text in ('t',
'tp_0_1', 'tp_0_2', 'tp_1_2'));
INSERT INTO t VALUES (0);
INSERT INTO t VALUES (0, 'tp_0_1'), (1, 'tp_1_2'), (1, 'public.tp_1_2');
ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;
ERROR: relation "t" does not exist

I see that virtual generated columns also can lead to the problems.
The revised 0003 rejects the dependency regardless of the generated
column kind, in a new checkPartitionSystemColumnRefs() called before
the new partition is created.

I confirm that CHECK constraints depending on a system column are also
problematic. The revised 0003 rejects CHECK constraints referencing a
system column as well, for the same reason as generated columns.
Since nothing needs re-verification anymore, the machinery that did it
is removed: buildPartitionCheckExprStates(),
checkPartitionRowConstraints(), the AlteredTableInfo.constraints
population, and the work queue entry and arguments that existed only
to carry them.

0002 also refuses to create the new partition in a schema whose FOR
TABLES IN SCHEMA publications differ from those of the source
partitions, since that would silently add the relocated rows to, or
remove them from, such a publication. The check triggers only when a
schema publication is actually involved, so a cross-schema MERGE/SPLIT
is still allowed otherwise; publications FOR ALL TABLES, or covering
the partitioned table itself, keep covering the new partitions and are
unaffected.

Agreed that the previous wording recommended something that runs into
your data-loss scenario. The paragraph now just states the facts: if
changes are published for the partitioned table itself, subscribers
are unaffected and may keep their own partition layout; otherwise the
new partition is not part of the subscription until it is refreshed,
and changes made in the meantime are not applied – so refreshing
without copying its data would silently lose them.

On the rewrite event trigger: MERGE/SPLIT doesn't fire table_rewrite,
and I don't think it should. table_rewrite reports a single table
that keeps its identity while getting a new relfilenode. MERGE turns
N partitions into one new relation and SPLIT one into N, dropping the
originals, so there is no single "table being rewritten" to report.
The commands are still visible to ddl_command_start/ddl_command_end as
an ALTER TABLE.

------
Regards,
Alexander Korotkov
Supabase

Hello

v3 looks good to me, I only have two nitpick comments:

1. MergePartitionsMoveRows now has a stale comment ("We also verify check constraints againsy these rows")

2. I am unsure of the usefulness of some of the error hints, for example:
+ errhint("Set the replica identity of the new partition explicitly after the operation."));

The hint is true, the user has to set up replica identity after the merge if he needs it, but the operation can't be executed as-is, so the user first have to solve the current situation. I also don't have a better idea how to explain this without an overly long error hint, so maybe it's good as is.

On Mon, Aug 3, 2026 at 6:03 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:

On Thu, Jul 23, 2026 at 1:59 PM Zsolt Parragi <zsolt.parragi@percona.com> wrote:

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.

I'm worried that despite the documentation, users might find this
surprising -- and by the time they realize it happened, it might be
too late. For example, in the following SQL, before the SPLIT
partition, Carol can't see the secret row when querying parent or
leaf, but after the split, she can query the leaf partition directly
(holding the same data as what she previously queried) and she can see
the secret row

CREATE ROLE carol LOGIN;
GRANT pg_read_all_data TO carol;
CREATE TABLE events3 (id int, secret boolean, data text) PARTITION BY
RANGE (id);
CREATE TABLE ev3_0_100 PARTITION OF events3 FOR VALUES FROM (0) TO (100);
INSERT INTO events3 VALUES (1,false,'public-row'), (2,true,'TOP-SECRET-row');
ALTER TABLE events3 ENABLE ROW LEVEL SECURITY;
CREATE POLICY hide_secret_parent ON events3 FOR SELECT USING (secret = false);
ALTER TABLE ev3_0_100 ENABLE ROW LEVEL SECURITY;
CREATE POLICY hide_secret_leaf ON ev3_0_100 FOR SELECT USING (secret = false);
SET ROLE carol;
SELECT * FROM events3 ORDER BY id;
SELECT * FROM ev3_0_100 ORDER BY id;
RESET ROLE;
ALTER TABLE events3 SPLIT PARTITION ev3_0_100 INTO
(PARTITION ev3_0_50 FOR VALUES FROM (0) TO (50),
PARTITION ev3_50_100 FOR VALUES FROM (50) TO (100));
SET ROLE carol;
SELECT * FROM events3 ORDER BY id;
SELECT * FROM ev3_0_50 ORDER BY id;
RESET ROLE;

The user needs to add RLS to the new leaf partitions if they want the
same level of security, but I'm not sure that's intuitive.

Also, for merging partitions, if you merge two partitions that have
the same RLS, after merging, the new merged partition doesn't have
that RLS policy -- that seems confusing too

GRANT pg_read_all_data TO carol;
CREATE TABLE events (id int, secret boolean, data text) PARTITION BY RANGE (id);
CREATE TABLE ev_a PARTITION OF events FOR VALUES FROM (0) TO (50);
CREATE TABLE ev_b PARTITION OF events FOR VALUES FROM (50) TO (100);
INSERT INTO events VALUES (10, false, 'A-public'), (20, true, 'A-SECRET'),
(60, false, 'B-public'), (70, true, 'B-SECRET');
ALTER TABLE events ENABLE ROW LEVEL SECURITY;
CREATE POLICY hide_secret ON events FOR SELECT USING (secret = false);
ALTER TABLE ev_a ENABLE ROW LEVEL SECURITY;
CREATE POLICY hide_secret ON ev_a FOR SELECT USING (secret = false);
ALTER TABLE ev_b ENABLE ROW LEVEL SECURITY;
CREATE POLICY hide_secret ON ev_b FOR SELECT USING (secret = false);
SET ROLE carol;
SELECT * FROM events ORDER BY id;
SELECT * FROM ev_a ORDER BY id;
SELECT * FROM ev_b ORDER BY id;
RESET ROLE;
ALTER TABLE events MERGE PARTITIONS (ev_a, ev_b) INTO ev_merged;
SET ROLE carol;
SELECT * FROM events ORDER BY id;
SELECT * FROM ev_merged ORDER BY id;
RESET ROLE;

- Melanie

I'm worried that despite the documentation, users might find this
surprising -- and by the time they realize it happened, it might be
too late.

This was one of my reasons for mentioning it. Printing out at least a WARNING for them would make them more visible (but it still has the problem that the mistake already happened - what if the user didn't dump the settings before splitting?), or it could be even an ERROR by default that would require an extra clause to override. But either of those requires at least the code to detect these issues.

My other worry is that it could be also confusing if we have silently different behavior in 19 and 20. Let's say all of these will be implemented in PG20 and later. And then a dba has to deal with some merge/split on a PG19 server, and doesn't realize that some settings are now missing, because it works differently in 20/21/... So maybe even with support in later versions, it would require something like INCLUDING ALL?

On 12 Aug 2026, at 22:48, Melanie Plageman <melanieplageman@gmail.com> wrote:
On Mon, Aug 3, 2026 at 6:03 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:

On Thu, Jul 23, 2026 at 1:59 PM Zsolt Parragi <zsolt.parragi@percona.com> wrote:

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.

I'm worried that despite the documentation, users might find this
surprising -- and by the time they realize it happened, it might be
too late.

Apart from the obviously dangerous ones like RLS and ACL, silently dropping the
table AM may induce side-effects which are hard for us to even reason about
since they are external to the core code. AFAICT we don't document that a
table can move out of the TAM, if even briefly.

I don't disagree with limiting scope to make a patch reviewable in a first
version, but I think this should do so by rejecting any cases where options are
silently dropped instead. What if the code checks both partitions for being
equal to the parent, and only allow a MERGE when all parameters can be kept due
to them being equal?

It's true that the behaviour is documented, but I don't think it's entirely
easy to grasp as the list of things being dropped is incomplete with an "etc":

"But extended statistics, security policies, etc, won't be copied from
the partitioned table."

...

The user needs to add RLS to the new leaf partitions if they want the
same level of security, but I'm not sure that's intuitive.

It's not, and it quite easily will leave the data without the intended
protection during a window.

Also, for merging partitions, if you merge two partitions that have
the same RLS, after merging, the new merged partition doesn't have
that RLS policy -- that seems confusing too

I would rank this as even more unintuitive than the previous case, as a user I
would expect the new partition to have the shared policy.

Could we make this safe by restricting to the cases where partitions match the
parent and we can make them not drop characteristics? If we want to expand
which differences can be handled in a safe manner in 20 then we can revisit,
rather than being very lax now and try to restrict later.

--
Daniel Gustafsson

On Wed, Aug 12, 2026 at 04:48:36PM -0400, Melanie Plageman wrote:

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.

I'm worried that despite the documentation, users might find this
surprising -- and by the time they realize it happened, it might be
too late.

[...]

The user needs to add RLS to the new leaf partitions if they want the
same level of security, but I'm not sure that's intuitive.

Also, for merging partitions, if you merge two partitions that have
the same RLS, after merging, the new merged partition doesn't have
that RLS policy -- that seems confusing too

+1. I'm looking at the current form of the documentation:

It is the user's responsibility to setup ACL on the new partition.

Does this mean that the merged partition is accessible to PUBLIC at first?
Or that it's not accessible to anyone? I think this could be explained in
greater detail.

Constraints, column defaults, column generation expressions, identity
columns, indexes, and triggers are copied from the partitioned table to
the new partition. But extended statistics, security policies, etc,
won't be copied from the partitioned table.

I think the "etc" is doing a lot of heavy lifting here. Does this mean
that only the things in the first list are handled, and everything else is
not?

When partitions are merged, any objects depending on this partition,
such as constraints, triggers, extended statistics, etc, will be
dropped.

Which partition does "this partition" refer to?

Eventually, we will drop all the merged partitions (using RESTRICT
mode) too; therefore, if any objects are still dependent on them, ALTER
TABLE MERGE PARTITION would fail.

I think this would be clearer if we had specific terms for the partitions
involved. For example, we could call the partitions that are getting
merged "source partitions", and the result of the merge the "merged
partition" or "destination partition". To me, the above sentence sounds
like we are dropping the destination/merged partition, but I'm pretty sure
that's not what it means.

Much of the above applies to SPLIT PARTITION as well. I'm sympathetic to
the idea of keeping things restricted at first to make the project more
feasible, but this is a pretty lengthy set of limitations that IMHO
deserves more prominence in the documentation (maybe even a warning). I
think it'd also be a good idea to call out that these limitations by go
away in future releases.

I haven't looked at the patches, but the size of the patches, and the fact
there there are apparently still rather large problems, does make me
somewhat concerned about this feature's readiness for v19.

--
nathan

On Fri, Aug 14, 2026 at 10:08 AM Daniel Gustafsson <daniel@yesql.se> wrote:

On 12 Aug 2026, at 22:48, Melanie Plageman <melanieplageman@gmail.com> wrote:

The user needs to add RLS to the new leaf partitions if they want the
same level of security, but I'm not sure that's intuitive.

It's not, and it quite easily will leave the data without the intended
protection during a window.

Also, for merging partitions, if you merge two partitions that have
the same RLS, after merging, the new merged partition doesn't have
that RLS policy -- that seems confusing too

I would rank this as even more unintuitive than the previous case, as a user I
would expect the new partition to have the shared policy.

Could we make this safe by restricting to the cases where partitions match the
parent and we can make them not drop characteristics? If we want to expand
which differences can be handled in a safe manner in 20 then we can revisit,
rather than being very lax now and try to restrict later.

Yes, I don't think it makes sense to silently drop the properties in
19 and then start automatically propagating them in 20. That seems
like it will be really confusing for users that have scripts to, for
example, recreate ACLs for the merged or split partition(s) when using
19.

- Melanie

On Fri, Aug 14, 2026 at 5:51 PM Melanie Plageman
<melanieplageman@gmail.com> wrote:

On Fri, Aug 14, 2026 at 10:08 AM Daniel Gustafsson <daniel@yesql.se> wrote:

On 12 Aug 2026, at 22:48, Melanie Plageman <melanieplageman@gmail.com> wrote:

The user needs to add RLS to the new leaf partitions if they want the
same level of security, but I'm not sure that's intuitive.

It's not, and it quite easily will leave the data without the intended
protection during a window.

Also, for merging partitions, if you merge two partitions that have
the same RLS, after merging, the new merged partition doesn't have
that RLS policy -- that seems confusing too

I would rank this as even more unintuitive than the previous case, as a user I
would expect the new partition to have the shared policy.

Could we make this safe by restricting to the cases where partitions match the
parent and we can make them not drop characteristics? If we want to expand
which differences can be handled in a safe manner in 20 then we can revisit,
rather than being very lax now and try to restrict later.

Yes, I don't think it makes sense to silently drop the properties in
19 and then start automatically propagating them in 20. That seems
like it will be really confusing for users that have scripts to, for
example, recreate ACLs for the merged or split partition(s) when using
19.

I agree that this kind of changing behavior is not acceptable. My
proposal is to reject partitions with row-level security/policies for
19. Then we could add automatic copy of row-level security/policies
for 20. If changing one behavior to another incompatible behavior is
not acceptable, but changing from ERRCODE_FEATURE_NOT_SUPPORTED to new
behavior seems acceptable (new releases support more features). Or
alternatively we could add copying of row-level security/policies as
an option in SQL statement for 20.

SPLIT/MERGE partition(s) seemed like not so complex feature, but many
aspects like this arise. It would be nice if we could come with some
restricted version for 19, and expand it for 20 and later releases
(rather than re-trying large patchset for 20).

Attached 0004 implements check that source partition doesn't have
ow-level security/policies.

0003 also have integrated edits proposed by Zsolt [1].

Links.
1. /messages/by-id/CAN4CZFMNhEF85h7h1su30h9E4cExGKpSSViZ2EqggNqAX+Xtng@mail.gmail.com

------
Regards,
Alexander Korotkov
Supabase