Postgres Pro
Downloads
Home   >   mailing lists

Thread: PostgreSQL 16 - Detach partition with FK - Error

PostgreSQL 16 - Detach partition with FK - Error

From
Adithya Kumaranchath
Date:
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

Re: PostgreSQL 16 - Detach partition with FK - Error

From
Álvaro Herrera
Date:
On 2025-Mar-19, Adithya Kumaranchath wrote:

> --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".removingpartition "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".
 

If I understand the example correctly, this is the expected behavior.
Here you're detaching a partition from the referenced table, which
contains rows that are still referenced from the constrained table.  
If we allowed this detach to continue, you would have rows in
table1_child that do not have corresponding rows in table1, in other
words you would have created an invalid primary key.

The fact that your 15 install did not throw an error is probably a bug.
I do get an error in the latest 15, though, so perhaps this is one of
those that was fixed along the way.  What exact 15.x version were you
running?

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"The ability of users to misuse tools is, of course, legendary" (David Steele)
https://postgr.es/m/11b38a96-6ded-4668-b772-40f992132797@pgmasters.net