Hi all, Issue: After upgrading to PostgreSQL version 16.6 from 15. I see a different behavior when detaching partitions with FK. Scenario 1 works on 15 but stopped working on 16.x
CREATE TABLE table1 ( enti_id varchar(75) NOT NULL, archive_dt date NOT NULL DEFAULT '9999-01-01'::date, CONSTRAINT table1_pk PRIMARY KEY (enti_id, archive_dt) ) PARTITION BY RANGE (archive_dt); CREATE TABLE IF NOT EXISTS table1_202401 PARTITION OF table1 FOR VALUES FROM ('2024-01-01') TO ('2024-02-01'); CREATE TABLE IF NOT EXISTS table1_202402 PARTITION OF table1 FOR VALUES FROM ('2024-02-01') TO ('2024-03-01'); --table1_child CREATE TABLE table1_child ( enti_id varchar(75) NOT NULL, parnt_id varchar(75) NOT NULL, archive_dt date NOT NULL DEFAULT '9999-01-01'::date, CONSTRAINT table1_child_pk PRIMARY KEY (enti_id, archive_dt) ) PARTITION BY RANGE (archive_dt); CREATE INDEX table1_child_idx ON ONLY table1_child USING btree (parnt_id); ALTER TABLE table1_child ADD CONSTRAINT table1_child_fk1 FOREIGN KEY (parnt_id,archive_dt) REFERENCES table1(enti_id,archive_dt) ON UPDATE CASCADE; CREATE TABLE IF NOT EXISTS table1_child_202401 PARTITION OF table1_child FOR VALUES FROM ('2024-01-01') TO ('2024-02-01'); CREATE TABLE IF NOT EXISTS table1_child_202402 PARTITION OF table1_child FOR VALUES FROM ('2024-02-01') TO ('2024-03-01'); --Insert into table1 INSERT INTO table1 (enti_id, archive_dt) VALUES('a6955e39-22eb-48e2-bd59-cad9650a4f6b', '2024-01-11'); INSERT INTO table1 (enti_id, archive_dt) VALUES('a6955e39-22eb-48e2-bd59-cad9650a4f6b', '2024-01-12'); INSERT INTO table1 (enti_id, archive_dt) VALUES('a6955e39-22eb-48e2-bd59-cad9650a4f6b', '2024-02-11'); INSERT INTO table1 (enti_id, archive_dt) VALUES('a6955e39-22eb-48e2-bd59-cad9650a4f6b', '2024-02-12'); --Insert into table1_child INSERT INTO table1_child (enti_id, parnt_id, archive_dt) VALUES('a6955e39-22eb-48e2-bd59-cad9650a4f6b', 'a6955e39-22eb-48e2-bd59-cad9650a4f6b','2024-01-11'); INSERT INTO table1_child (enti_id, parnt_id, archive_dt) VALUES('a6955e39-22eb-48e2-bd59-cad9650a4f6b', 'a6955e39-22eb-48e2-bd59-cad9650a4f6b','2024-01-12'); INSERT INTO table1_child (enti_id, parnt_id, archive_dt) VALUES('a6955e39-22eb-48e2-bd59-cad9650a4f6b', 'a6955e39-22eb-48e2-bd59-cad9650a4f6b', '2024-02-11'); INSERT INTO table1_child (enti_id, parnt_id, archive_dt) VALUES('a6955e39-22eb-48e2-bd59-cad9650a4f6b', 'a6955e39-22eb-48e2-bd59-cad9650a4f6b','2024-02-12'); --Scenario 1: Detach parent partition alter table table1 detach partition table1_202402 ERROR: Key (parnt_id, archive_dt)=(a6955e39-22eb-48e2-bd59-cad9650a4f6b, 2024-02-11) is still referenced from table "table1_child_202402".removing partition "table1_202402" violates foreign key constraint "table1_child_202402_parnt_id_archive_dt_fkey1" ERROR: removing partition "table1_202402" violates foreign key constraint "table1_child_202402_parnt_id_archive_dt_fkey1" SQL state: 23503 Detail: Key (parnt_id, archive_dt)=(a6955e39-22eb-48e2-bd59-cad9650a4f6b, 2024-02-11) is still referenced from table "table1_child_202402". --Sceanrio 2: detach partition from child --detach partition from child select * from table1 alter table table1_child detach partition table1_child_202402 alter table table1_child_202402 drop constraint table1_child_fk1 alter table table1 detach partition table1_202402 Scenario 2 works on 16 and seems to be the logical way but was wondering if this was a change. Thanks, Ad