Re: Issue attaching a table to a partitioned table with an auto-referenced foreign key

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