Self referential foreign keys in partitioned table not working as expected
As mentioned at
/messages/by-id/18156-a44bc7096f0683e6@postgresql.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.
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
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;
---------------------------------------------------------------------------------------
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:
Show quoted text
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: 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
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
/messages/by-id/18156-a44bc7096f0683e6@postgresql.org
and
/messages/by-id/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/