public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adithya Kumaranchath <[email protected]>
To: [email protected] <[email protected]>
Subject: PostgreSQL 16 - Detach partition with FK - Error
Date: Wed, 19 Mar 2025 12:07:02 +0000
Message-ID: <AM9P193MB1793E5BB9004F98EFA01206FDBD92@AM9P193MB1793.EURP193.PROD.OUTLOOK.COM> (raw)

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

view thread (2+ messages)  latest in thread

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]
  Subject: Re: PostgreSQL 16 - Detach partition with FK - Error
  In-Reply-To: <AM9P193MB1793E5BB9004F98EFA01206FDBD92@AM9P193MB1793.EURP193.PROD.OUTLOOK.COM>

* 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