Re: MERGE/SPLIT PARTITIONS issues/questions

On Mon, Aug 3, 2026 at 6:03 PM Alexander Korotkov <aekorotkov(at)gmail(dot)com> wrote:
>
> On Thu, Jul 23, 2026 at 1:59 PM Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)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