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.

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)



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
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)



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

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".


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


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

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".


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
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)



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)