Thread: BUG #18156: Self-referential foreign key in partitioned table not enforced on deletes
BUG #18156: Self-referential foreign key in partitioned table not enforced on deletes
From
PG Bug reporting form
Date:
The following bug has been logged on the website:
Bug reference: 18156
Logged by: Matthew Gabeler-Lee
Email address: fastcat@gmail.com
PostgreSQL version: 16.0
Operating system: Linux
Description:
When a partitioned table has a self-referential foreign key, I'm finding
that the key is not enforced during deletes from the table.
A simple repro script:
create table x (
p int4 not null,
i int4 not null,
f int4 null,
primary key (p, i),
foreign key (p, f) references x (p, i)
)
partition by list (p);
create table x1 partition of x for values in (0);
insert into x values (0,1,null), (0,2,1);
delete from x where (p,i) = (0,1);
select * from x;
The final select shows the one row with the clearly violated foreign key
values:
p | i | f
---+---+---
0 | 2 | 1
(1 row)
I've reproduced this with 15.4 and 16.0 using the official docker images,
specifically `postgres/15-alpine` and `postgres/16-alpine`. I haven't tested
older versions as my application requires features new to 15.x. I've tried
with both list and hash partitioning, and it reproduces with both. I can
only reproduce it with self-referential foreign keys and only with
partitioned tables.
I discovered this with a slightly more complex situation involving an `on
delete set null` clause in the FK, but as seen above that is not required to
reproduce the issue.
Re: BUG #18156: Self-referential foreign key in partitioned table not enforced on deletes
From
Matthew Gabeler-Lee
Date:
I've since discovered that this is a regression in PG 15.x, with 14.x the delete command correctly fails with an FK violation error.
Is there anything I can do to help investigating a root cause / fix for this issue?
On Fri, Oct 13, 2023 at 10:27 AM PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 18156
Logged by: Matthew Gabeler-Lee
Email address: fastcat@gmail.com
PostgreSQL version: 16.0
Operating system: Linux
Description:
When a partitioned table has a self-referential foreign key, I'm finding
that the key is not enforced during deletes from the table.
A simple repro script:
create table x (
p int4 not null,
i int4 not null,
f int4 null,
primary key (p, i),
foreign key (p, f) references x (p, i)
)
partition by list (p);
create table x1 partition of x for values in (0);
insert into x values (0,1,null), (0,2,1);
delete from x where (p,i) = (0,1);
select * from x;
The final select shows the one row with the clearly violated foreign key
values:
p | i | f
---+---+---
0 | 2 | 1
(1 row)
I've reproduced this with 15.4 and 16.0 using the official docker images,
specifically `postgres/15-alpine` and `postgres/16-alpine`. I haven't tested
older versions as my application requires features new to 15.x. I've tried
with both list and hash partitioning, and it reproduces with both. I can
only reproduce it with self-referential foreign keys and only with
partitioned tables.
I discovered this with a slightly more complex situation involving an `on
delete set null` clause in the FK, but as seen above that is not required to
reproduce the issue.
Re: BUG #18156: Self-referential foreign key in partitioned table not enforced on deletes
From
Alvaro Herrera
Date:
Hello, I hadn't noticed this report before; I'll have a look at it
soon.
On 2023-Oct-13, PG Bug reporting form wrote:
> When a partitioned table has a self-referential foreign key, I'm finding
> that the key is not enforced during deletes from the table.
Hmm, yeah, I broke this in this commit
Author: Alvaro Herrera <alvherre@alvh.no-ip.org>
Branch: master Release: REL_16_BR [614a406b4] 2022-10-07 19:37:48 +0200
Branch: REL_15_STABLE Release: REL_15_0 [6083132ab] 2022-10-07 19:37:48 +0200
Branch: REL_14_STABLE Release: REL_14_6 [483d26930] 2022-10-07 19:37:48 +0200
Branch: REL_13_STABLE Release: REL_13_9 [7d520e68e] 2022-10-07 19:37:48 +0200
Branch: REL_12_STABLE Release: REL_12_13 [669803af0] 2022-10-07 19:37:48 +0200
Fix self-referencing foreign keys with partitioned tables
Clearly this area needs a lot more work.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"No nos atrevemos a muchas cosas porque son difíciles,
pero son difíciles porque no nos atrevemos a hacerlas" (Séneca)
Re: Issue attaching a table to a partitioned table with an auto-referenced foreign key
From
Alvaro Herrera
Date:
Hello, I've been looking at this bug once again and I think I finally understood what's going on and how to fix it. Ref 1: https://postgr.es/m/20230707175859.17c91538@karst Re: Issue attaching a table to a partitioned table with an auto-referenced foreign key (Guillaume Lelarge) Ref 2: https://postgr.es/m/18156-a44bc7096f0683e6@postgresql.org BUG #18156: Self-referential foreign key in partitioned table not enforced on deletes (Matthew Gabeler-Lee) Ref 3: https://postgr.es/m/myvsiF-Attja5DcWoUWh21R12R-sfXECY2-3ynt8kaOqjw@mail.gmail.com Self referential foreign keys in partitioned table not working as expected (Luca Vallisa) First of all -- apparently we broke this in commit 5914a22f6ea5 (which fixed the other problems that had been reported by G. Lelarge in Ref 1) even worse than how it was before, by having the new functions just skip processing the referenced side altogether. Previously we were at least partially setting up the necessary triggers, at least some of the time. So what the report by Luca is saying is, plain and simple, that the referenced-side action triggers just do not exist, which is why no error is thrown even on the most trivial cases, on the releases that contain that commit (17.1, 16.5, 15.9). The solution I came up with, is to no longer skip creating the referenced-side objects when the FK is self-referencing. But in order for this to work, when cloning FKs to partitions, we must process the referencing side first rather than the referenced side first as we did up to now; if we don't do it that way, the code there gets confused about multiple constraint entries already existing for the same table. Which one gets processed first shouldn't really have any other important effect, unless I have overlooked something. The patch also adds equivalent test cases to what was reported; if I remove the code fix, these cases fail because they no longer report the expected errors. (The changes to the other regression expected fails reflect the triggers that are missing.) We already fixed some bits in 614a406b4ff1 and siblings, but apparently the test cases we added there were insufficient, or we would have detected that we were making things worse in 5914a22f6ea5 :-( Anyway, if people have a chance to give this a look, it would be helpful. -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/ "Linux transformó mi computadora, de una `máquina para hacer cosas', en un aparato realmente entretenido, sobre el cual cada día aprendo algo nuevo" (Jaime Salinas)
Attachment
Re: Issue attaching a table to a partitioned table with an auto-referenced foreign key
From
Alvaro Herrera
Date:
Hello, I've been looking at this bug once again and I think I finally understood what's going on and how to fix it. Ref 1: https://postgr.es/m/20230707175859.17c91538@karst Re: Issue attaching a table to a partitioned table with an auto-referenced foreign key (Guillaume Lelarge) Ref 2: https://postgr.es/m/18156-a44bc7096f0683e6@postgresql.org BUG #18156: Self-referential foreign key in partitioned table not enforced on deletes (Matthew Gabeler-Lee) Ref 3: https://postgr.es/m/myvsiF-Attja5DcWoUWh21R12R-sfXECY2-3ynt8kaOqjw@mail.gmail.com Self referential foreign keys in partitioned table not working as expected (Luca Vallisa) First of all -- apparently we broke this in commit 5914a22f6ea5 (which fixed the other problems that had been reported by G. Lelarge in Ref 1) even worse than how it was before, by having the new functions just skip processing the referenced side altogether. Previously we were at least partially setting up the necessary triggers, at least some of the time. So what the report by Luca is saying is, plain and simple, that the referenced-side action triggers just do not exist, which is why no error is thrown even on the most trivial cases, on the releases that contain that commit (17.1, 16.5, 15.9). The solution I came up with, is to no longer skip creating the referenced-side objects when the FK is self-referencing. But in order for this to work, when cloning FKs to partitions, we must process the referencing side first rather than the referenced side first as we did up to now; if we don't do it that way, the code there gets confused about multiple constraint entries already existing for the same table. Which one gets processed first shouldn't really have any other important effect, unless I have overlooked something. The patch also adds equivalent test cases to what was reported; if I remove the code fix, these cases fail because they no longer report the expected errors. (The changes to the other regression expected fails reflect the triggers that are missing.) We already fixed some bits in 614a406b4ff1 and siblings, but apparently the test cases we added there were insufficient, or we would have detected that we were making things worse in 5914a22f6ea5 :-( Anyway, if people have a chance to give this a look, it would be helpful. -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/ "Linux transformó mi computadora, de una `máquina para hacer cosas', en un aparato realmente entretenido, sobre el cual cada día aprendo algo nuevo" (Jaime Salinas)
Re: Issue attaching a table to a partitioned table with an auto-referenced foreign key
From
Tender Wang
Date:
Alvaro Herrera <alvherre@alvh.no-ip.org> 于2025年5月1日周四 20:17写道：
Hello,
I've been looking at this bug once again and I think I finally
understood what's going on and how to fix it.
Ref 1: https://postgr.es/m/20230707175859.17c91538@karst
Re: Issue attaching a table to a partitioned table with an
auto-referenced foreign key
(Guillaume Lelarge)
Ref 2: https://postgr.es/m/18156-a44bc7096f0683e6@postgresql.org
BUG #18156: Self-referential foreign key in partitioned table not
enforced on deletes
(Matthew Gabeler-Lee)
Ref 3: https://postgr.es/m/myvsiF-Attja5DcWoUWh21R12R-sfXECY2-3ynt8kaOqjw@mail.gmail.com
Self referential foreign keys in partitioned table not working as
expected
(Luca Vallisa)
First of all -- apparently we broke this in commit 5914a22f6ea5 (which
fixed the other problems that had been reported by G. Lelarge in Ref 1)
even worse than how it was before, by having the new functions just skip
processing the referenced side altogether. Previously we were at least
partially setting up the necessary triggers, at least some of the time.
So what the report by Luca is saying is, plain and simple, that the
referenced-side action triggers just do not exist, which is why no error
is thrown even on the most trivial cases, on the releases that contain
that commit (17.1, 16.5, 15.9).
Hmm. I didn't get the same conclusion.
Before commit 5914a22f6ea5, the issue reported by Luca could have happened. Look at the test below on v17.0:
psql (17.0)
Type "help" for help.
postgres=# 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);
CREATE TABLE
CREATE TABLE
INSERT 0 2
DELETE 1
Type "help" for help.
postgres=# 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);
CREATE TABLE
CREATE TABLE
INSERT 0 2
DELETE 1
You can see from the above test that no error was reported.
But if I revert the commit 614a406b4ff1, above test would report error on v16devel:
psql (16devel)
Type "help" for help.
postgres=# 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);
CREATE TABLE
CREATE TABLE
INSERT 0 2
ERROR: update or delete on table "test_1" violates foreign key constraint "test_id_1_parent_id_2_fkey1" on table "test"
DETAIL: Key (id_1, id_2)=(1, 1) is still referenced from table "test".
Type "help" for help.
postgres=# 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);
CREATE TABLE
CREATE TABLE
INSERT 0 2
ERROR: update or delete on table "test_1" violates foreign key constraint "test_id_1_parent_id_2_fkey1" on table "test"
DETAIL: Key (id_1, id_2)=(1, 1) is still referenced from table "test".
Anyway, if people have a chance to give this a look, it would be
helpful.
It's midnight in my time zone. I will look at this tomorrow.
Thanks,
Tender Wang
Re: Issue attaching a table to a partitioned table with an auto-referenced foreign key
From
Tender Wang
Date:
Alvaro Herrera <alvherre@alvh.no-ip.org> 于2025年5月1日周四 20:17写道：
Hello,
I've been looking at this bug once again and I think I finally
understood what's going on and how to fix it.
Ref 1: https://postgr.es/m/20230707175859.17c91538@karst
Re: Issue attaching a table to a partitioned table with an
auto-referenced foreign key
(Guillaume Lelarge)
Ref 2: https://postgr.es/m/18156-a44bc7096f0683e6@postgresql.org
BUG #18156: Self-referential foreign key in partitioned table not
enforced on deletes
(Matthew Gabeler-Lee)
Ref 3: https://postgr.es/m/myvsiF-Attja5DcWoUWh21R12R-sfXECY2-3ynt8kaOqjw@mail.gmail.com
Self referential foreign keys in partitioned table not working as
expected
(Luca Vallisa)
First of all -- apparently we broke this in commit 5914a22f6ea5 (which
fixed the other problems that had been reported by G. Lelarge in Ref 1)
even worse than how it was before, by having the new functions just skip
processing the referenced side altogether. Previously we were at least
partially setting up the necessary triggers, at least some of the time.
So what the report by Luca is saying is, plain and simple, that the
referenced-side action triggers just do not exist, which is why no error
is thrown even on the most trivial cases, on the releases that contain
that commit (17.1, 16.5, 15.9).
Hmm. I didn't get the same conclusion.
Before commit 5914a22f6ea5, the issue reported by Luca could have happened. Look at the test below on v17.0:
psql (17.0)
Type "help" for help.
postgres=# 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);
CREATE TABLE
CREATE TABLE
INSERT 0 2
DELETE 1
Type "help" for help.
postgres=# 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);
CREATE TABLE
CREATE TABLE
INSERT 0 2
DELETE 1
You can see from the above test that no error was reported.
But if I revert the commit 614a406b4ff1, above test would report error on v16devel:
psql (16devel)
Type "help" for help.
postgres=# 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);
CREATE TABLE
CREATE TABLE
INSERT 0 2
ERROR: update or delete on table "test_1" violates foreign key constraint "test_id_1_parent_id_2_fkey1" on table "test"
DETAIL: Key (id_1, id_2)=(1, 1) is still referenced from table "test".
Type "help" for help.
postgres=# 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);
CREATE TABLE
CREATE TABLE
INSERT 0 2
ERROR: update or delete on table "test_1" violates foreign key constraint "test_id_1_parent_id_2_fkey1" on table "test"
DETAIL: Key (id_1, id_2)=(1, 1) is still referenced from table "test".
Anyway, if people have a chance to give this a look, it would be
helpful.
It's midnight in my time zone. I will look at this tomorrow.
Thanks,
Tender Wang
Re: Issue attaching a table to a partitioned table with an auto-referenced foreign key
From
Alvaro Herrera
Date:
On 2025-May-01, Tender Wang wrote: > Hmm. I didn't get the same conclusion. > Before commit 5914a22f6ea5, the issue reported by Luca could have happened. [...] > You can see from the above test that no error was reported. > But if I revert the commit 614a406b4ff1, above test would report error on > v16devel: Yeah, I was mistaken to blame 5914a22f6ea5 for this issue when the real culprit was 614a406b4ff1. Anyway, I pushed the proposed fix to all branches last night, so hopefully it works correctly for all cases now. (As context -- it took me several weeks or months to get FKs on partitioned tables to work. People would make fun at the "spider" diagrams I drew on whiteboards, of the relationships between pg_constraint and pg_trigger entries. And for some reason at no point did the idea of self-referencing FKs occurred to me. I should have realized that the complexity was getting out of hand! At the very least I should have pressed for some more QA help.) Y'all are still on time to test this a bit more before next week's releases ... if I have made things even worse I can still revert the patch. With luck, that won't be necessary. Regards -- Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/ "Java is clearly an example of money oriented programming" (A. Stepanov)
Re: Issue attaching a table to a partitioned table with an auto-referenced foreign key
From
Alvaro Herrera
Date:
On 2025-May-01, Tender Wang wrote: > Hmm. I didn't get the same conclusion. > Before commit 5914a22f6ea5, the issue reported by Luca could have happened. [...] > You can see from the above test that no error was reported. > But if I revert the commit 614a406b4ff1, above test would report error on > v16devel: Yeah, I was mistaken to blame 5914a22f6ea5 for this issue when the real culprit was 614a406b4ff1. Anyway, I pushed the proposed fix to all branches last night, so hopefully it works correctly for all cases now. (As context -- it took me several weeks or months to get FKs on partitioned tables to work. People would make fun at the "spider" diagrams I drew on whiteboards, of the relationships between pg_constraint and pg_trigger entries. And for some reason at no point did the idea of self-referencing FKs occurred to me. I should have realized that the complexity was getting out of hand! At the very least I should have pressed for some more QA help.) Y'all are still on time to test this a bit more before next week's releases ... if I have made things even worse I can still revert the patch. With luck, that won't be necessary. Regards -- Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/ "Java is clearly an example of money oriented programming" (A. Stepanov)
Re: Issue attaching a table to a partitioned table with an auto-referenced foreign key
From
Matthew Gabeler-Lee
Date:
On Sat, May 3, 2025 at 9:09 AM Alvaro Herrera <alvherre@kurilemu.de> wrote: > Y'all are still on time to test this a bit more before next week's > releases ... if I have made things even worse I can still revert the > patch. With luck, that won't be necessary. I was not able to get to testing this before the release, but I was able to test today after the release at least. My earlier test case, and an adjusted version of it that is closer to my production application (notably adding ON DELETE CASCADE to the FK), now passes with 15.13. Thank you :D
Re: Issue attaching a table to a partitioned table with an auto-referenced foreign key
From
Matthew Gabeler-Lee
Date:
On Sat, May 3, 2025 at 9:09 AM Alvaro Herrera <alvherre@kurilemu.de> wrote: > Y'all are still on time to test this a bit more before next week's > releases ... if I have made things even worse I can still revert the > patch. With luck, that won't be necessary. I was not able to get to testing this before the release, but I was able to test today after the release at least. My earlier test case, and an adjusted version of it that is closer to my production application (notably adding ON DELETE CASCADE to the FK), now passes with 15.13. Thank you :D
Re: Issue attaching a table to a partitioned table with an auto-referenced foreign key
From
Alvaro Herrera
Date:
On 2025-May-08, Matthew Gabeler-Lee wrote: > My earlier test case, and an adjusted version of it that is closer to > my production application (notably adding ON DELETE CASCADE to the > FK), now passes with 15.13. Thank you, that's a relief to know. -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/ "Porque Kim no hacía nada, pero, eso sí, con extraordinario éxito" ("Kim", Kipling)