Re: PostgreSQL 16 - Detach partition with FK - Error

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