Alvaro Herrera <alvhe...@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-a44bc7096f068...@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-3ynt8kao...@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
