public inbox for [email protected]  

help / color / mirror / Atom feed

From: Álvaro Herrera <[email protected]>
To: Adithya Kumaranchath <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: PostgreSQL 16 - Detach partition with FK - Error
Date: Wed, 19 Mar 2025 15:43:45 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <AM9P193MB1793E5BB9004F98EFA01206FDBD92@AM9P193MB1793.EURP193.PROD.OUTLOOK.COM>

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

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/[email protected]

view thread (2+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: PostgreSQL 16 - Detach partition with FK - Error
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox