Обсуждение: Self referential foreign keys in partitioned table not working as expected
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);
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.
Re: Self referential foreign keys in partitioned table not working as expected
От
Christoph Berg
Дата:
Re: Luca Vallisa > 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); > > insert into test values (1, 1, null), (1, 2, 1); Multi-column foreign keys where one column is NULL effectively disable the FK, this is not specific to partitioned tables. They works as designed, but best avoid them. Christoph
Re: Self referential foreign keys in partitioned table not working as expected
От
Luca Vallisa
Дата:
Thanks for the reply.
I've realized I messed up with the script.
Please refer to the following one.
---------------------------------------------------------------------------------------
drop table if exists test;
create table test (
id_1 int4 not null,
id_2 int4 not null,
parent_id_2 int4 null,
primary key (id_1, id_2),
foreign key (id_1, parent_id_2) references test (id_1, id_2)
)
--partition by list (id_1); /** uncomment this line **/
--create table test_1 partition of test for values in (1); /** uncomment this line **/
--create table test_9 partition of test for values in (9) /** uncomment this line **/
;
insert into test values (1, 1, null), (1, 2, 1);
delete from test where (id_1, id_2) = (1, 1);
--update test set id_2 = 9 where (id_1, id_2) = (1, 1);
select * from test;
drop table if exists test;
create table test (
id_1 int4 not null,
id_2 int4 not null,
parent_id_2 int4 null,
primary key (id_1, id_2),
foreign key (id_1, parent_id_2) references test (id_1, id_2)
)
--partition by list (id_1); /** uncomment this line **/
--create table test_1 partition of test for values in (1); /** uncomment this line **/
--create table test_9 partition of test for values in (9) /** uncomment this line **/
;
insert into test values (1, 1, null), (1, 2, 1);
delete from test where (id_1, id_2) = (1, 1);
--update test set id_2 = 9 where (id_1, id_2) = (1, 1);
select * from test;
drop table if exists test;
---------------------------------------------------------------------------------------
The provided version throws an error.
If you uncomment the 3 lines (working with partition) the error is not thrown during the delete (or the update).
Luca
Il giorno mar 1 apr 2025 alle ore 12:18 Christoph Berg <myon@debian.org> ha scritto:
Re: Luca Vallisa
> 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);
>
> insert into test values (1, 1, null), (1, 2, 1);
Multi-column foreign keys where one column is NULL effectively disable
the FK, this is not specific to partitioned tables. They works as
designed, but best avoid them.
Christoph
Re: Self referential foreign keys in partitioned table not working as expected
От
Christoph Berg
Дата:
Re: Luca Vallisa
> The provided version throws an error.
Ok, I can confirm this.
This throws an error like it should:
create table test (
id_1 int4 not null,
id_2 int4 not null,
parent_id_2 int4 null,
primary key (id_1, id_2),
foreign key (id_1, parent_id_2) references test (id_1, id_2)
);
insert into test values (1, 1, null), (1, 2, 1);
delete from test where (id_1, id_2) = (1, 1);
On a partitioned table, it does not throw the error:
create table test (
id_1 int4 not null,
id_2 int4 not null,
parent_id_2 int4 null,
primary key (id_1, id_2),
foreign key (id_1, parent_id_2) references test (id_1, id_2)
) partition by list (id_1);
create table test_1 partition of test for values in (1);
insert into test values (1, 1, null), (1, 2, 1);
delete from test where (id_1, id_2) = (1, 1);
Christoph
Re: Self referential foreign keys in partitioned table not working as expected
От
Álvaro Herrera
Дата:
On 2025-Apr-01, Christoph Berg wrote: > Re: Luca Vallisa > > The provided version throws an error. > > Ok, I can confirm this. > On a partitioned table, it does not throw the error: > > create table test ( > id_1 int4 not null, > id_2 int4 not null, > parent_id_2 int4 null, > primary key (id_1, id_2), > foreign key (id_1, parent_id_2) references test (id_1, id_2) > ) partition by list (id_1); > create table test_1 partition of test for values in (1); > insert into test values (1, 1, null), (1, 2, 1); > delete from test where (id_1, id_2) = (1, 1); FWIW I didn't give closure on this thread, but AFAICT this is the same bug that was reported in https://postgr.es/m/18156-a44bc7096f0683e6@postgresql.org and https://postgr.es/m/CAECtzeWHCA+6tTcm2Oh2+g7fURUJpLZb-=pRXgeWJ-Pi+VU=_w@mail.gmail.com That was fixed a couple of months ago. The above script throws an error as it should. -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/