Re: Issue attaching a table to a partitioned table with an auto-referenced foreign key - Mailing list pgsql-bugs
| From | Tender Wang |
|---|---|
| Subject | Re: Issue attaching a table to a partitioned table with an auto-referenced foreign key |
| Date | |
| Msg-id | CAHewXNmXCiquhZbbr51XAW6GEXbUnRGfqv27B7TgLSPTS+i-kw@mail.gmail.com Whole thread Raw |
| In response to | Re: Issue attaching a table to a partitioned table with an auto-referenced foreign key (Alvaro Herrera <alvherre@alvh.no-ip.org>) |
| Responses |
Re: Issue attaching a table to a partitioned table with an auto-referenced foreign key
Re: Issue attaching a table to a partitioned table with an auto-referenced foreign key |
| List | pgsql-bugs |
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
pgsql-bugs by date: