[MASSMAIL] 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.