Horton

Thread

Commits

  1. Restructure foreign key handling code for ATTACH/DETACH

  1. Detach Partition produces a --> SQL-Fehler [XX000]: ERROR: could not find ON INSERT check triggers of foreign key constraint 76908

    Baehler Thomas SBB CFF FFS <thomas.baehler2@sbb.ch> — 2024-04-05T13:40:45Z

    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.

  2. Re: Detach Partition produces a --> SQL-Fehler [XX000]: ERROR: could not find ON INSERT check triggers of foreign key constraint 76908

    Tender Wang <tndrwang@gmail.com> — 2024-04-07T10:10:47Z

    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]. I analyzed this issue
in that thread and privoded a
patch int [2]. But it had more problem when deattach partition with FK.


[1] https://www.postgresql.org/message-id/20230705233028.2f554f73%40karst
[2]
https://www.postgresql.org/message-id/CAHewXNnZkxCwpOW25FXnOF96-5QBLJ-_q5aipvkYtq0DFJavWw%40mail.gmail.com
-- 
Tender Wang
OpenPie:  https://en.openpie.com/