Detach Partition produces a --> SQL-Fehler [XX000]: ERROR: could not find ON INSERT check triggers of foreign key constraint 76908
Hi all
Version: PostgreSQL 16.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit
I want to drop old partitions but keep some few data.
I copy them to a partition called (_old).
A small reproducer….
drop table b cascade;
drop table a;
CREATE TABLE a (
id int not null,
logdate date not null,
fk_b_id int,
fk_b_logdate date
) PARTITION BY RANGE (logdate);
CREATE TABLE a_03 PARTITION OF a
FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');
CREATE TABLE a_02 PARTITION OF a
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
CREATE TABLE a_01 PARTITION OF a
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE a_old PARTITION OF a
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE b (
id int not null,
logdate date not null
) PARTITION BY RANGE (logdate);
CREATE TABLE b_03 PARTITION OF b
FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');
CREATE TABLE b_02 PARTITION OF b
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
CREATE TABLE b_01 PARTITION OF b
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE b_old PARTITION OF b
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
alter table b add primary key (id, logdate);
ALTER TABLE a ADD CONSTRAINT fk_to_b FOREIGN KEY (fk_b_id, fk_b_logdate) REFERENCES b (id, logdate);
insert into b values (0, '2024-02-20');
insert into a values (0, '2024-02-20', 0, '2024-02-20');
alter table a detach partition a_old;
alter table a detach partition a_01;
alter table b detach partition b_old;
alter table b detach partition b_01;
alter table b attach partition b_old FOR VALUES FROM ('2023-01-01') TO ('2024-02-01');
alter table a attach partition a_old FOR VALUES FROM ('2023-01-01') TO ('2024-02-01');
insert into b select * from b_01;
insert into a select * from a_01;
drop table a_01;
drop table b_01;
select * from b_02; -- until here everything is fine.
set constraints all deferred ;
alter table a detach partition a_old; • here this empty partition cannot be detached the error XX000 appears.
alter table a detach partition a_02;
alter table b detach partition b_old;
alter table b detach partition b_02;
alter table b attach partition b_old FOR VALUES FROM ('2023-01-01') TO ('2024-03-01');
alter table a attach partition a_old FOR VALUES FROM ('2023-01-01') TO ('2024-03-01');
insert into b select * from b_02;
insert into a select * from a_02;
drop table a_02;
drop table b_02;
select * from a;
I should always be able to detach the ‘a’ partition empty or not.
Baehler Thomas SBB CFF FFS <thomas.baehler2@sbb.ch> 于2024年4月5日周五 21:56写道：
*Hi all Version: PostgreSQL 16.2 on x86_64-pc-linux-gnu, compiled by gcc
(GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit I want to drop old
partitions but keep some few data. **I copy them to a partition called (_old). *
A small reproducer….
*drop table b cascade;*
*drop table a;*
*CREATE TABLE a (*
* id int not null,*
* logdate date not null,*
* fk_b_id int,*
* fk_b_logdate date *
*) PARTITION BY RANGE (logdate);*
*CREATE TABLE a_03 PARTITION OF a*
* FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');*
*CREATE TABLE a_02 PARTITION OF a*
* FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');*
*CREATE TABLE a_01 PARTITION OF a*
* FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');*
*CREATE TABLE a_old PARTITION OF a*
* FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');*
*CREATE TABLE b (*
* id int not null,*
* logdate date not null*
*) PARTITION BY RANGE (logdate);*
*CREATE TABLE b_03 PARTITION OF b*
* FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');*
*CREATE TABLE b_02 PARTITION OF b*
* FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');*
*CREATE TABLE b_01 PARTITION OF b*
* FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');*
*CREATE TABLE b_old PARTITION OF b*
* FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');*
*alter table b add primary key (id, logdate);*
*ALTER TABLE a ADD CONSTRAINT fk_to_b FOREIGN KEY (fk_b_id, fk_b_logdate)
REFERENCES b (id, logdate);**insert into b values (0, '2024-02-20');*
*insert into a values (0, '2024-02-20', 0, '2024-02-20');*
*alter table a detach partition a_old;*
*alter table a detach partition a_01;*
*alter table b detach partition b_old;*
*alter table b detach partition b_01;*
*alter table b attach partition b_old FOR VALUES FROM ('2023-01-01') TO
('2024-02-01');**alter table a attach partition a_old FOR VALUES FROM ('2023-01-01') TO
('2024-02-01');**insert into b select * from b_01;*
*insert into a select * from a_01;*
*drop table a_01;*
*drop table b_01;*
*select * from b_02; -- until here everything is fine. *
*set constraints all deferred ;*
*alter table a detach partition a_old; **** here this empty partition
cannot be detached the error XX000 appears.**alter table a detach partition a_02;*
*alter table b detach partition b_old;*
*alter table b detach partition b_02;*
*alter table b attach partition b_old FOR VALUES FROM ('2023-01-01') TO
('2024-03-01');**alter table a attach partition a_old FOR VALUES FROM ('2023-01-01') TO
('2024-03-01');**insert into b select * from b_02;*
*insert into a select * from a_02;*
*drop table a_02;*
*drop table b_02;*
*select * from a;*
I should always be able to detach the ‘a’ partition empty or not.
The issue you reported seems to be similar to [1]/messages/by-id/20230705233028.2f554f73@karst. I analyzed this issue
in that thread and privoded a
patch int [2]/messages/by-id/CAHewXNnZkxCwpOW25FXnOF96-5QBLJ-_q5aipvkYtq0DFJavWw@mail.gmail.com -- Tender Wang OpenPie: https://en.openpie.com/. But it had more problem when deattach partition with FK.
[1]: /messages/by-id/20230705233028.2f554f73@karst
[2]: /messages/by-id/CAHewXNnZkxCwpOW25FXnOF96-5QBLJ-_q5aipvkYtq0DFJavWw@mail.gmail.com -- Tender Wang OpenPie: https://en.openpie.com/
/messages/by-id/CAHewXNnZkxCwpOW25FXnOF96-5QBLJ-_q5aipvkYtq0DFJavWw@mail.gmail.com
--
Tender Wang
OpenPie: https://en.openpie.com/