From: | Luca Vallisa <luca(dot)vallisa(at)gmail(dot)com> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Self referential foreign keys in partitioned table not working as expected |
Date: | 2025-04-01 09:06:13 |
Message-ID: | CAAT=myvsiF-Attja5DcWoUWh21R12R-sfXECY2-3ynt8kaOqjw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Christoph Berg | 2025-04-01 10:18:30 | Re: Self referential foreign keys in partitioned table not working as expected |
Previous Message | David G. Johnston | 2025-03-31 21:01:38 | Re: Download Issue |