Self referential foreign keys in partitioned table not working as expected

Поиск
Список
Период
Сортировка
От Luca Vallisa
Тема Self referential foreign keys in partitioned table not working as expected
Дата
Msg-id CAAT=myvsiF-Attja5DcWoUWh21R12R-sfXECY2-3ynt8kaOqjw@mail.gmail.com
обсуждение исходный текст
Ответы Re: Self referential foreign keys in partitioned table not working as expected
Список pgsql-bugs
As mentioned at https://www.postgresql.org/message-id/18156-a44bc7096f0683e6%40postgresql.org this is a regression introduced in version 15.X and still present in 17.4.

I'm running the postgres:17.4-alpine docker official image.

-------------------------------------------------------------------------------------
REPRO
-------------------------------------------------------------------------------------

drop table if exists test;

create table test (
    id_1 int4 not null,
    id_2 int4 not null,
    parent_id_1 int4 null,
    primary key (id_1, id_2),
    foreign key (parent_id_1, id_2) references test(id_1, id_2)
) partition by list(id_1);

create table test_1 partition of test for values in (1);
create table test_9 partition of test for values in (9);

insert into test values (1, 1, null), (1, 2, 1);

-- doesn't trigger an error
-- delete from test where (id_1, id_2) = (1, 1);

-- doesn't trigger an error
-- update test set id_1 = 9 where (id_1, id_2) = (1, 1);

-------------------------------------------------------------------------------------

Both deletion and update runs smoothly.
Also, potential cascade delete and cascade update rules are ignored.





В списке pgsql-bugs по дате отправления: