Self referential foreign keys in partitioned table not working as expected
Luca Vallisa <luca.vallisa@gmail.com>
From: Luca Vallisa <luca.vallisa@gmail.com>
To: pgsql-bugs@lists.postgresql.org
Date: 2025-04-01T09:06:13Z
Lists: pgsql-bugs, pgsql-hackers
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.
Commits
Same data as JSON:
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
Handle self-referencing FKs correctly in partitioned tables
- f51ae3187472 17.5 landed
- c83a38758d2a 18.0 landed
- b3a9c536db67 13.21 landed
- 6ba979cf570c 15.13 landed
- 1817d62ecbf5 16.9 landed
- 1649d153a172 14.18 landed
-
Restructure foreign key handling code for ATTACH/DETACH
- 5914a22f6ea5 17.1 cited
-
Fix self-referencing foreign keys with partitioned tables
- 614a406b4ff1 16.0 cited