Horton
Menu

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Handle self-referencing FKs correctly in partitioned tables

  2. Restructure foreign key handling code for ATTACH/DETACH

  3. Fix self-referencing foreign keys with partitioned tables

  1. Issue attaching a table to a partitioned table with an auto-referenced foreign key

    Guillaume Lelarge <guillaume@lelarge.info> — 2023-01-06T10:07:50Z

    Hello,

One of our customers has an issue with partitions and foreign keys. He
works on a v13, but the issue is also present on v15.

I attach a SQL script showing the issue, and the results on 13.7, 13.9, and
15.1. But I'll explain the script here, and its behaviour on 13.9.

There is one partitioned table, two partitions and a foreign key. The
foreign key references the same table:

create table t1 (
  c1 bigint not null,
  c1_old bigint null,
  c2 bigint not null,
  c2_old bigint null,
  primary key (c1, c2)
  )
  partition by list (c1);
create table t1_a   partition of t1 for values in (1);
create table t1_def partition of t1 default;
alter table t1 add foreign key (c1_old, c2_old) references t1 (c1, c2) on
delete restrict on update restrict;

I've a SQL function that shows me some information from pg_constraints
(code of the function in the SQL script attached). Here is the result of
this function after creating the table, its partitions, and its foreign key:

select * from show_constraints();
        conname         |   t    |  tref  |       coparent
------------------------+--------+--------+-----------------------
 t1_c1_old_c2_old_fkey  | t1     | t1     |
 t1_c1_old_c2_old_fkey  | t1_a   | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey1 | t1     | t1_a   | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
(5 rows)

The constraint works great :

insert into t1 values(1, NULL, 2, NULL);
insert into t1 values(2, 1,    2, 2);
delete from t1 where c1 = 1;
psql:ticket15010_v3.sql:34: ERROR:  update or delete on table "t1_a"
violates foreign key constraint "t1_c1_old_c2_old_fkey1" on table "t1"
DETAIL:  Key (c1, c2)=(1, 2) is still referenced from table "t1".

This error is normal since the line I want to delete is referenced on the
other line.

If I try to detach the partition, it also gives me an error.

alter table t1 detach partition t1_a;
psql:ticket15010_v3.sql:36: ERROR:  removing partition "t1_a" violates
foreign key constraint "t1_c1_old_c2_old_fkey1"
DETAIL:  Key (c1_old, c2_old)=(1, 2) is still referenced from table "t1".

Sounds good to me too (well, I'd like it to be smarter and find that the
constraint is still good after the detach, but I can understand why it
won't allow it).

The pg_constraint didn't change of course:

select * from show_constraints();
        conname         |   t    |  tref  |       coparent
------------------------+--------+--------+-----------------------
 t1_c1_old_c2_old_fkey  | t1     | t1     |
 t1_c1_old_c2_old_fkey  | t1_a   | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey1 | t1     | t1_a   | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
(5 rows)

Now, I'll delete the whole table contents, and I'll detach the partition:

delete from t1;
alter table t1 detach partition t1_a;

It seems to be working, but the content of pg_constraints is weird:

select * from show_constraints();
        conname         |   t    |  tref  |       coparent
------------------------+--------+--------+-----------------------
 t1_c1_old_c2_old_fkey  | t1     | t1     |
 t1_c1_old_c2_old_fkey  | t1_a   | t1     |
 t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
(4 rows)

I understand why the ('t1_c1_old_c2_old_fkey1', 't1', 't1_a',
't1_c1_old_c2_old_fkey') tuple has gone but I don't understand why the
('t1_c1_old_c2_old_fkey', 't1_a', 't1', NULL) tuple is still there.

Anyway, I attach the partition:

alter table t1 attach partition t1_a for values in (1);

But pg_constraint has not changed:

select * from show_constraints();
        conname         |   t    |  tref  |       coparent
------------------------+--------+--------+-----------------------
 t1_c1_old_c2_old_fkey  | t1     | t1     |
 t1_c1_old_c2_old_fkey  | t1_a   | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
(4 rows)

I was expecting to see the fifth tuple coming back, but alas, no.

And as a result, the foreign key doesn't work anymore:

insert into t1 values(1, NULL, 2, NULL);
insert into t1 values(2, 1,    2, 2);
delete from t1 where c1 = 1;

Well, let's truncate the partitioned table, and drop the partition:

truncate t1;
drop table t1_a;

The content of pg_constraint looks good to me:

select * from show_constraints();
        conname         |   t    |  tref  |       coparent
------------------------+--------+--------+-----------------------
 t1_c1_old_c2_old_fkey  | t1     | t1     |
 t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
(3 rows)

Let's create the partition to see if that works better:

create table t1_a   partition of t1 for values in (1);

select * from show_constraints();
        conname         |   t    |  tref  |       coparent
------------------------+--------+--------+-----------------------
 t1_c1_old_c2_old_fkey  | t1     | t1     |
 t1_c1_old_c2_old_fkey  | t1_a   | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
(4 rows)

insert into t1 values(1, NULL, 2, NULL);
INSERT 0 1
insert into t1 values(2, 1,    2, 2);
INSERT 0 1
delete from t1 where c1 = 1;
DELETE 1

Nope. I still miss the fifth tuple in pg_constraint, which results in a
violated foreign key.

How about dropping the foreign key to create it once more:

truncate t1;
alter table t1 drop constraint t1_c1_old_c2_old_fkey;
select * from show_constraints();
 conname | t | tref | coparent
---------+---+------+----------
(0 rows)

drop table t1_a;
create table t1_a   partition of t1 for values in (1);
alter table t1 add foreign key (c1_old, c2_old) references t1 (c1, c2) on
delete restrict on update restrict;
select * from show_constraints();
        conname         |   t    |  tref  |       coparent
------------------------+--------+--------+-----------------------
 t1_c1_old_c2_old_fkey  | t1     | t1     |
 t1_c1_old_c2_old_fkey  | t1_a   | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey1 | t1     | t1_a   | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
(5 rows)

I have my fifth row back! And now, the foreign key works as it should:

insert into t1 values(1, NULL, 2, NULL);
insert into t1 values(2, 1,    2, 2);
delete from t1 where c1 = 1;
psql:ticket15010_v3.sql:87: ERROR:  update or delete on table "t1_a"
violates foreign key constraint "t1_c1_old_c2_old_fkey1" on table "t1"
DETAIL:  Key (c1, c2)=(1, 2) is still referenced from table "t1".

This is what happens on 13.9 and 15.1. 13.7 shows another weird behaviour,
but I guess I'll stop there. Everything is in the attached files.

I'd love to know if I did something wrong, if I didn't understand
something, or if this is simply a bug.

Thanks.

Regards.


-- 
Guillaume.

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

    Guillaume Lelarge <guillaume@lelarge.info> — 2023-01-17T15:53:27Z

    Quick ping, just to make sure someone can get a look at this issue :)
Thanks.


Le ven. 6 janv. 2023 à 11:07, Guillaume Lelarge <guillaume@lelarge.info> a
écrit :

> Hello,
>
> One of our customers has an issue with partitions and foreign keys. He
> works on a v13, but the issue is also present on v15.
>
> I attach a SQL script showing the issue, and the results on 13.7, 13.9,
> and 15.1. But I'll explain the script here, and its behaviour on 13.9.
>
> There is one partitioned table, two partitions and a foreign key. The
> foreign key references the same table:
>
> create table t1 (
>   c1 bigint not null,
>   c1_old bigint null,
>   c2 bigint not null,
>   c2_old bigint null,
>   primary key (c1, c2)
>   )
>   partition by list (c1);
> create table t1_a   partition of t1 for values in (1);
> create table t1_def partition of t1 default;
> alter table t1 add foreign key (c1_old, c2_old) references t1 (c1, c2) on
> delete restrict on update restrict;
>
> I've a SQL function that shows me some information from pg_constraints
> (code of the function in the SQL script attached). Here is the result of
> this function after creating the table, its partitions, and its foreign key:
>
> select * from show_constraints();
>         conname         |   t    |  tref  |       coparent
> ------------------------+--------+--------+-----------------------
>  t1_c1_old_c2_old_fkey  | t1     | t1     |
>  t1_c1_old_c2_old_fkey  | t1_a   | t1     | t1_c1_old_c2_old_fkey
>  t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
>  t1_c1_old_c2_old_fkey1 | t1     | t1_a   | t1_c1_old_c2_old_fkey
>  t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
> (5 rows)
>
> The constraint works great :
>
> insert into t1 values(1, NULL, 2, NULL);
> insert into t1 values(2, 1,    2, 2);
> delete from t1 where c1 = 1;
> psql:ticket15010_v3.sql:34: ERROR:  update or delete on table "t1_a"
> violates foreign key constraint "t1_c1_old_c2_old_fkey1" on table "t1"
> DETAIL:  Key (c1, c2)=(1, 2) is still referenced from table "t1".
>
> This error is normal since the line I want to delete is referenced on the
> other line.
>
> If I try to detach the partition, it also gives me an error.
>
> alter table t1 detach partition t1_a;
> psql:ticket15010_v3.sql:36: ERROR:  removing partition "t1_a" violates
> foreign key constraint "t1_c1_old_c2_old_fkey1"
> DETAIL:  Key (c1_old, c2_old)=(1, 2) is still referenced from table "t1".
>
> Sounds good to me too (well, I'd like it to be smarter and find that the
> constraint is still good after the detach, but I can understand why it
> won't allow it).
>
> The pg_constraint didn't change of course:
>
> select * from show_constraints();
>         conname         |   t    |  tref  |       coparent
> ------------------------+--------+--------+-----------------------
>  t1_c1_old_c2_old_fkey  | t1     | t1     |
>  t1_c1_old_c2_old_fkey  | t1_a   | t1     | t1_c1_old_c2_old_fkey
>  t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
>  t1_c1_old_c2_old_fkey1 | t1     | t1_a   | t1_c1_old_c2_old_fkey
>  t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
> (5 rows)
>
> Now, I'll delete the whole table contents, and I'll detach the partition:
>
> delete from t1;
> alter table t1 detach partition t1_a;
>
> It seems to be working, but the content of pg_constraints is weird:
>
> select * from show_constraints();
>         conname         |   t    |  tref  |       coparent
> ------------------------+--------+--------+-----------------------
>  t1_c1_old_c2_old_fkey  | t1     | t1     |
>  t1_c1_old_c2_old_fkey  | t1_a   | t1     |
>  t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
>  t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
> (4 rows)
>
> I understand why the ('t1_c1_old_c2_old_fkey1', 't1', 't1_a',
> 't1_c1_old_c2_old_fkey') tuple has gone but I don't understand why the
> ('t1_c1_old_c2_old_fkey', 't1_a', 't1', NULL) tuple is still there.
>
> Anyway, I attach the partition:
>
> alter table t1 attach partition t1_a for values in (1);
>
> But pg_constraint has not changed:
>
> select * from show_constraints();
>         conname         |   t    |  tref  |       coparent
> ------------------------+--------+--------+-----------------------
>  t1_c1_old_c2_old_fkey  | t1     | t1     |
>  t1_c1_old_c2_old_fkey  | t1_a   | t1     | t1_c1_old_c2_old_fkey
>  t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
>  t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
> (4 rows)
>
> I was expecting to see the fifth tuple coming back, but alas, no.
>
> And as a result, the foreign key doesn't work anymore:
>
> insert into t1 values(1, NULL, 2, NULL);
> insert into t1 values(2, 1,    2, 2);
> delete from t1 where c1 = 1;
>
> Well, let's truncate the partitioned table, and drop the partition:
>
> truncate t1;
> drop table t1_a;
>
> The content of pg_constraint looks good to me:
>
> select * from show_constraints();
>         conname         |   t    |  tref  |       coparent
> ------------------------+--------+--------+-----------------------
>  t1_c1_old_c2_old_fkey  | t1     | t1     |
>  t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
>  t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
> (3 rows)
>
> Let's create the partition to see if that works better:
>
> create table t1_a   partition of t1 for values in (1);
>
> select * from show_constraints();
>         conname         |   t    |  tref  |       coparent
> ------------------------+--------+--------+-----------------------
>  t1_c1_old_c2_old_fkey  | t1     | t1     |
>  t1_c1_old_c2_old_fkey  | t1_a   | t1     | t1_c1_old_c2_old_fkey
>  t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
>  t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
> (4 rows)
>
> insert into t1 values(1, NULL, 2, NULL);
> INSERT 0 1
> insert into t1 values(2, 1,    2, 2);
> INSERT 0 1
> delete from t1 where c1 = 1;
> DELETE 1
>
> Nope. I still miss the fifth tuple in pg_constraint, which results in a
> violated foreign key.
>
> How about dropping the foreign key to create it once more:
>
> truncate t1;
> alter table t1 drop constraint t1_c1_old_c2_old_fkey;
> select * from show_constraints();
>  conname | t | tref | coparent
> ---------+---+------+----------
> (0 rows)
>
> drop table t1_a;
> create table t1_a   partition of t1 for values in (1);
> alter table t1 add foreign key (c1_old, c2_old) references t1 (c1, c2) on
> delete restrict on update restrict;
> select * from show_constraints();
>         conname         |   t    |  tref  |       coparent
> ------------------------+--------+--------+-----------------------
>  t1_c1_old_c2_old_fkey  | t1     | t1     |
>  t1_c1_old_c2_old_fkey  | t1_a   | t1     | t1_c1_old_c2_old_fkey
>  t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
>  t1_c1_old_c2_old_fkey1 | t1     | t1_a   | t1_c1_old_c2_old_fkey
>  t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
> (5 rows)
>
> I have my fifth row back! And now, the foreign key works as it should:
>
> insert into t1 values(1, NULL, 2, NULL);
> insert into t1 values(2, 1,    2, 2);
> delete from t1 where c1 = 1;
> psql:ticket15010_v3.sql:87: ERROR:  update or delete on table "t1_a"
> violates foreign key constraint "t1_c1_old_c2_old_fkey1" on table "t1"
> DETAIL:  Key (c1, c2)=(1, 2) is still referenced from table "t1".
>
> This is what happens on 13.9 and 15.1. 13.7 shows another weird behaviour,
> but I guess I'll stop there. Everything is in the attached files.
>
> I'd love to know if I did something wrong, if I didn't understand
> something, or if this is simply a bug.
>
> Thanks.
>
> Regards.
>
>
> --
> Guillaume.
>


-- 
Guillaume.

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

    Guillaume Lelarge <guillaume@lelarge.info> — 2023-03-22T10:14:19Z

    One last ping, hoping someone will have more time now than in january.

Perhaps my test is wrong, but I'd like to know why.

Thanks.

Le mar. 17 janv. 2023 à 16:53, Guillaume Lelarge <guillaume@lelarge.info> a
écrit :

> Quick ping, just to make sure someone can get a look at this issue :)
> Thanks.
>
>
> Le ven. 6 janv. 2023 à 11:07, Guillaume Lelarge <guillaume@lelarge.info>
> a écrit :
>
>> Hello,
>>
>> One of our customers has an issue with partitions and foreign keys. He
>> works on a v13, but the issue is also present on v15.
>>
>> I attach a SQL script showing the issue, and the results on 13.7, 13.9,
>> and 15.1. But I'll explain the script here, and its behaviour on 13.9.
>>
>> There is one partitioned table, two partitions and a foreign key. The
>> foreign key references the same table:
>>
>> create table t1 (
>>   c1 bigint not null,
>>   c1_old bigint null,
>>   c2 bigint not null,
>>   c2_old bigint null,
>>   primary key (c1, c2)
>>   )
>>   partition by list (c1);
>> create table t1_a   partition of t1 for values in (1);
>> create table t1_def partition of t1 default;
>> alter table t1 add foreign key (c1_old, c2_old) references t1 (c1, c2) on
>> delete restrict on update restrict;
>>
>> I've a SQL function that shows me some information from pg_constraints
>> (code of the function in the SQL script attached). Here is the result of
>> this function after creating the table, its partitions, and its foreign key:
>>
>> select * from show_constraints();
>>         conname         |   t    |  tref  |       coparent
>> ------------------------+--------+--------+-----------------------
>>  t1_c1_old_c2_old_fkey  | t1     | t1     |
>>  t1_c1_old_c2_old_fkey  | t1_a   | t1     | t1_c1_old_c2_old_fkey
>>  t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
>>  t1_c1_old_c2_old_fkey1 | t1     | t1_a   | t1_c1_old_c2_old_fkey
>>  t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
>> (5 rows)
>>
>> The constraint works great :
>>
>> insert into t1 values(1, NULL, 2, NULL);
>> insert into t1 values(2, 1,    2, 2);
>> delete from t1 where c1 = 1;
>> psql:ticket15010_v3.sql:34: ERROR:  update or delete on table "t1_a"
>> violates foreign key constraint "t1_c1_old_c2_old_fkey1" on table "t1"
>> DETAIL:  Key (c1, c2)=(1, 2) is still referenced from table "t1".
>>
>> This error is normal since the line I want to delete is referenced on the
>> other line.
>>
>> If I try to detach the partition, it also gives me an error.
>>
>> alter table t1 detach partition t1_a;
>> psql:ticket15010_v3.sql:36: ERROR:  removing partition "t1_a" violates
>> foreign key constraint "t1_c1_old_c2_old_fkey1"
>> DETAIL:  Key (c1_old, c2_old)=(1, 2) is still referenced from table "t1".
>>
>> Sounds good to me too (well, I'd like it to be smarter and find that the
>> constraint is still good after the detach, but I can understand why it
>> won't allow it).
>>
>> The pg_constraint didn't change of course:
>>
>> select * from show_constraints();
>>         conname         |   t    |  tref  |       coparent
>> ------------------------+--------+--------+-----------------------
>>  t1_c1_old_c2_old_fkey  | t1     | t1     |
>>  t1_c1_old_c2_old_fkey  | t1_a   | t1     | t1_c1_old_c2_old_fkey
>>  t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
>>  t1_c1_old_c2_old_fkey1 | t1     | t1_a   | t1_c1_old_c2_old_fkey
>>  t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
>> (5 rows)
>>
>> Now, I'll delete the whole table contents, and I'll detach the partition:
>>
>> delete from t1;
>> alter table t1 detach partition t1_a;
>>
>> It seems to be working, but the content of pg_constraints is weird:
>>
>> select * from show_constraints();
>>         conname         |   t    |  tref  |       coparent
>> ------------------------+--------+--------+-----------------------
>>  t1_c1_old_c2_old_fkey  | t1     | t1     |
>>  t1_c1_old_c2_old_fkey  | t1_a   | t1     |
>>  t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
>>  t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
>> (4 rows)
>>
>> I understand why the ('t1_c1_old_c2_old_fkey1', 't1', 't1_a',
>> 't1_c1_old_c2_old_fkey') tuple has gone but I don't understand why the
>> ('t1_c1_old_c2_old_fkey', 't1_a', 't1', NULL) tuple is still there.
>>
>> Anyway, I attach the partition:
>>
>> alter table t1 attach partition t1_a for values in (1);
>>
>> But pg_constraint has not changed:
>>
>> select * from show_constraints();
>>         conname         |   t    |  tref  |       coparent
>> ------------------------+--------+--------+-----------------------
>>  t1_c1_old_c2_old_fkey  | t1     | t1     |
>>  t1_c1_old_c2_old_fkey  | t1_a   | t1     | t1_c1_old_c2_old_fkey
>>  t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
>>  t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
>> (4 rows)
>>
>> I was expecting to see the fifth tuple coming back, but alas, no.
>>
>> And as a result, the foreign key doesn't work anymore:
>>
>> insert into t1 values(1, NULL, 2, NULL);
>> insert into t1 values(2, 1,    2, 2);
>> delete from t1 where c1 = 1;
>>
>> Well, let's truncate the partitioned table, and drop the partition:
>>
>> truncate t1;
>> drop table t1_a;
>>
>> The content of pg_constraint looks good to me:
>>
>> select * from show_constraints();
>>         conname         |   t    |  tref  |       coparent
>> ------------------------+--------+--------+-----------------------
>>  t1_c1_old_c2_old_fkey  | t1     | t1     |
>>  t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
>>  t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
>> (3 rows)
>>
>> Let's create the partition to see if that works better:
>>
>> create table t1_a   partition of t1 for values in (1);
>>
>> select * from show_constraints();
>>         conname         |   t    |  tref  |       coparent
>> ------------------------+--------+--------+-----------------------
>>  t1_c1_old_c2_old_fkey  | t1     | t1     |
>>  t1_c1_old_c2_old_fkey  | t1_a   | t1     | t1_c1_old_c2_old_fkey
>>  t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
>>  t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
>> (4 rows)
>>
>> insert into t1 values(1, NULL, 2, NULL);
>> INSERT 0 1
>> insert into t1 values(2, 1,    2, 2);
>> INSERT 0 1
>> delete from t1 where c1 = 1;
>> DELETE 1
>>
>> Nope. I still miss the fifth tuple in pg_constraint, which results in a
>> violated foreign key.
>>
>> How about dropping the foreign key to create it once more:
>>
>> truncate t1;
>> alter table t1 drop constraint t1_c1_old_c2_old_fkey;
>> select * from show_constraints();
>>  conname | t | tref | coparent
>> ---------+---+------+----------
>> (0 rows)
>>
>> drop table t1_a;
>> create table t1_a   partition of t1 for values in (1);
>> alter table t1 add foreign key (c1_old, c2_old) references t1 (c1, c2) on
>> delete restrict on update restrict;
>> select * from show_constraints();
>>         conname         |   t    |  tref  |       coparent
>> ------------------------+--------+--------+-----------------------
>>  t1_c1_old_c2_old_fkey  | t1     | t1     |
>>  t1_c1_old_c2_old_fkey  | t1_a   | t1     | t1_c1_old_c2_old_fkey
>>  t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
>>  t1_c1_old_c2_old_fkey1 | t1     | t1_a   | t1_c1_old_c2_old_fkey
>>  t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
>> (5 rows)
>>
>> I have my fifth row back! And now, the foreign key works as it should:
>>
>> insert into t1 values(1, NULL, 2, NULL);
>> insert into t1 values(2, 1,    2, 2);
>> delete from t1 where c1 = 1;
>> psql:ticket15010_v3.sql:87: ERROR:  update or delete on table "t1_a"
>> violates foreign key constraint "t1_c1_old_c2_old_fkey1" on table "t1"
>> DETAIL:  Key (c1, c2)=(1, 2) is still referenced from table "t1".
>>
>> This is what happens on 13.9 and 15.1. 13.7 shows another weird
>> behaviour, but I guess I'll stop there. Everything is in the attached files.
>>
>> I'd love to know if I did something wrong, if I didn't understand
>> something, or if this is simply a bug.
>>
>> Thanks.
>>
>> Regards.
>>
>>
>> --
>> Guillaume.
>>
>
>
> --
> Guillaume.
>


-- 
Guillaume.

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

    Jehan-Guillaume de Rorthais <jgdr@dalibo.com> — 2023-07-07T15:58:59Z

    So I gave a look at this one... And it's a tricky one.

The current policy about DETACHing a partition is to keep/adjust all FK
referencing it or referenced by it.

However, in this exact self-referencing usecase, we can have rows referencing
rows from the same partition OR another one. It seems like an
impossible issue to solve.

Here is an example based on Guillaume's scenario ([c1_old, c2_old] -> [c1, c2]):

  t1:
    t1_a:
      c1 | c1_old | c2 | c2_old
     ----+--------+----+--------
       1 |   NULL |  2 |   NULL
       1 |      1 |  3 |      2
       1 |      2 |  4 |      2 
    t1_b:
      c1 | c1_old | c2 | c2_old
     ----+--------+----+--------
       2 |      1 |  2 |      3

Now, what happens with the FK when we DETACH t1_a?
  * it's not enough t1_a only keeps a self-FK, as it references some
    rows from t1_b:
    (1, 2, 4, 2) -> (2, 1, 2, 3)
  * and t1_a can not only keeps a FK referencing t1 either as it references some
    rows fro itself:
    (1, 1, 3, 2) -> (1, NULL, 2, NULL)

I'm currently not able to think about a constraint we could build to address
this situation after the DETACH.

The only clean way out would be to drop the FK between the old partition and
the partitioned table. But then, it breaks the current policy to keep the
constraint after DETACH. Not mentioning the nightmare to detect this situation
from some other ones.

Thoughts?

On Wed, 22 Mar 2023 11:14:19 +0100
Guillaume Lelarge <guillaume@lelarge.info> wrote:

> One last ping, hoping someone will have more time now than in january.
> 
> Perhaps my test is wrong, but I'd like to know why.
> 
> Thanks.
> 
> Le mar. 17 janv. 2023 à 16:53, Guillaume Lelarge <guillaume@lelarge.info> a
> écrit :
> 
> > Quick ping, just to make sure someone can get a look at this issue :)
> > Thanks.
> >
> >
> > Le ven. 6 janv. 2023 à 11:07, Guillaume Lelarge <guillaume@lelarge.info>
> > a écrit :
> >  
> >> Hello,
> >>
> >> One of our customers has an issue with partitions and foreign keys. He
> >> works on a v13, but the issue is also present on v15.
> >>
> >> I attach a SQL script showing the issue, and the results on 13.7, 13.9,
> >> and 15.1. But I'll explain the script here, and its behaviour on 13.9.
> >>
> >> There is one partitioned table, two partitions and a foreign key. The
> >> foreign key references the same table:
> >>
> >> create table t1 (
> >>   c1 bigint not null,
> >>   c1_old bigint null,
> >>   c2 bigint not null,
> >>   c2_old bigint null,
> >>   primary key (c1, c2)
> >>   )
> >>   partition by list (c1);
> >> create table t1_a   partition of t1 for values in (1);
> >> create table t1_def partition of t1 default;
> >> alter table t1 add foreign key (c1_old, c2_old) references t1 (c1, c2) on
> >> delete restrict on update restrict;
> >>
> >> I've a SQL function that shows me some information from pg_constraints
> >> (code of the function in the SQL script attached). Here is the result of
> >> this function after creating the table, its partitions, and its foreign
> >> key:
> >>
> >> select * from show_constraints();
> >>         conname         |   t    |  tref  |       coparent
> >> ------------------------+--------+--------+-----------------------
> >>  t1_c1_old_c2_old_fkey  | t1     | t1     |
> >>  t1_c1_old_c2_old_fkey  | t1_a   | t1     | t1_c1_old_c2_old_fkey
> >>  t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
> >>  t1_c1_old_c2_old_fkey1 | t1     | t1_a   | t1_c1_old_c2_old_fkey
> >>  t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
> >> (5 rows)
> >>
> >> The constraint works great :
> >>
> >> insert into t1 values(1, NULL, 2, NULL);
> >> insert into t1 values(2, 1,    2, 2);
> >> delete from t1 where c1 = 1;
> >> psql:ticket15010_v3.sql:34: ERROR:  update or delete on table "t1_a"
> >> violates foreign key constraint "t1_c1_old_c2_old_fkey1" on table "t1"
> >> DETAIL:  Key (c1, c2)=(1, 2) is still referenced from table "t1".
> >>
> >> This error is normal since the line I want to delete is referenced on the
> >> other line.
> >>
> >> If I try to detach the partition, it also gives me an error.
> >>
> >> alter table t1 detach partition t1_a;
> >> psql:ticket15010_v3.sql:36: ERROR:  removing partition "t1_a" violates
> >> foreign key constraint "t1_c1_old_c2_old_fkey1"
> >> DETAIL:  Key (c1_old, c2_old)=(1, 2) is still referenced from table "t1".
> >>
> >> Sounds good to me too (well, I'd like it to be smarter and find that the
> >> constraint is still good after the detach, but I can understand why it
> >> won't allow it).
> >>
> >> The pg_constraint didn't change of course:
> >>
> >> select * from show_constraints();
> >>         conname         |   t    |  tref  |       coparent
> >> ------------------------+--------+--------+-----------------------
> >>  t1_c1_old_c2_old_fkey  | t1     | t1     |
> >>  t1_c1_old_c2_old_fkey  | t1_a   | t1     | t1_c1_old_c2_old_fkey
> >>  t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
> >>  t1_c1_old_c2_old_fkey1 | t1     | t1_a   | t1_c1_old_c2_old_fkey
> >>  t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
> >> (5 rows)
> >>
> >> Now, I'll delete the whole table contents, and I'll detach the partition:
> >>
> >> delete from t1;
> >> alter table t1 detach partition t1_a;
> >>
> >> It seems to be working, but the content of pg_constraints is weird:
> >>
> >> select * from show_constraints();
> >>         conname         |   t    |  tref  |       coparent
> >> ------------------------+--------+--------+-----------------------
> >>  t1_c1_old_c2_old_fkey  | t1     | t1     |
> >>  t1_c1_old_c2_old_fkey  | t1_a   | t1     |
> >>  t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
> >>  t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
> >> (4 rows)
> >>
> >> I understand why the ('t1_c1_old_c2_old_fkey1', 't1', 't1_a',
> >> 't1_c1_old_c2_old_fkey') tuple has gone but I don't understand why the
> >> ('t1_c1_old_c2_old_fkey', 't1_a', 't1', NULL) tuple is still there.
> >>
> >> Anyway, I attach the partition:
> >>
> >> alter table t1 attach partition t1_a for values in (1);
> >>
> >> But pg_constraint has not changed:
> >>
> >> select * from show_constraints();
> >>         conname         |   t    |  tref  |       coparent
> >> ------------------------+--------+--------+-----------------------
> >>  t1_c1_old_c2_old_fkey  | t1     | t1     |
> >>  t1_c1_old_c2_old_fkey  | t1_a   | t1     | t1_c1_old_c2_old_fkey
> >>  t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
> >>  t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
> >> (4 rows)
> >>
> >> I was expecting to see the fifth tuple coming back, but alas, no.
> >>
> >> And as a result, the foreign key doesn't work anymore:
> >>
> >> insert into t1 values(1, NULL, 2, NULL);
> >> insert into t1 values(2, 1,    2, 2);
> >> delete from t1 where c1 = 1;
> >>
> >> Well, let's truncate the partitioned table, and drop the partition:
> >>
> >> truncate t1;
> >> drop table t1_a;
> >>
> >> The content of pg_constraint looks good to me:
> >>
> >> select * from show_constraints();
> >>         conname         |   t    |  tref  |       coparent
> >> ------------------------+--------+--------+-----------------------
> >>  t1_c1_old_c2_old_fkey  | t1     | t1     |
> >>  t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
> >>  t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
> >> (3 rows)
> >>
> >> Let's create the partition to see if that works better:
> >>
> >> create table t1_a   partition of t1 for values in (1);
> >>
> >> select * from show_constraints();
> >>         conname         |   t    |  tref  |       coparent
> >> ------------------------+--------+--------+-----------------------
> >>  t1_c1_old_c2_old_fkey  | t1     | t1     |
> >>  t1_c1_old_c2_old_fkey  | t1_a   | t1     | t1_c1_old_c2_old_fkey
> >>  t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
> >>  t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
> >> (4 rows)
> >>
> >> insert into t1 values(1, NULL, 2, NULL);
> >> INSERT 0 1
> >> insert into t1 values(2, 1,    2, 2);
> >> INSERT 0 1
> >> delete from t1 where c1 = 1;
> >> DELETE 1
> >>
> >> Nope. I still miss the fifth tuple in pg_constraint, which results in a
> >> violated foreign key.
> >>
> >> How about dropping the foreign key to create it once more:
> >>
> >> truncate t1;
> >> alter table t1 drop constraint t1_c1_old_c2_old_fkey;
> >> select * from show_constraints();
> >>  conname | t | tref | coparent
> >> ---------+---+------+----------
> >> (0 rows)
> >>
> >> drop table t1_a;
> >> create table t1_a   partition of t1 for values in (1);
> >> alter table t1 add foreign key (c1_old, c2_old) references t1 (c1, c2) on
> >> delete restrict on update restrict;
> >> select * from show_constraints();
> >>         conname         |   t    |  tref  |       coparent
> >> ------------------------+--------+--------+-----------------------
> >>  t1_c1_old_c2_old_fkey  | t1     | t1     |
> >>  t1_c1_old_c2_old_fkey  | t1_a   | t1     | t1_c1_old_c2_old_fkey
> >>  t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
> >>  t1_c1_old_c2_old_fkey1 | t1     | t1_a   | t1_c1_old_c2_old_fkey
> >>  t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
> >> (5 rows)
> >>
> >> I have my fifth row back! And now, the foreign key works as it should:
> >>
> >> insert into t1 values(1, NULL, 2, NULL);
> >> insert into t1 values(2, 1,    2, 2);
> >> delete from t1 where c1 = 1;
> >> psql:ticket15010_v3.sql:87: ERROR:  update or delete on table "t1_a"
> >> violates foreign key constraint "t1_c1_old_c2_old_fkey1" on table "t1"
> >> DETAIL:  Key (c1, c2)=(1, 2) is still referenced from table "t1".
> >>
> >> This is what happens on 13.9 and 15.1. 13.7 shows another weird
> >> behaviour, but I guess I'll stop there. Everything is in the attached
> >> files.
> >>
> >> I'd love to know if I did something wrong, if I didn't understand
> >> something, or if this is simply a bug.
> >>
> >> Thanks.
> >>
> >> Regards.
> >>
> >>
> >> --
> >> Guillaume.
> >>  
> >
> >
> > --
> > Guillaume.
> >  
> 
>

  5. BUG #18156: Self-referential foreign key in partitioned table not enforced on deletes

    PG Bug reporting form <noreply@postgresql.org> — 2023-10-13T14:26:56Z

    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.

  6. Re: BUG #18156: Self-referential foreign key in partitioned table not enforced on deletes

    Matthew Gabeler-Lee <fastcat@gmail.com> — 2024-01-24T17:52:57Z

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

  7. Re: BUG #18156: Self-referential foreign key in partitioned table not enforced on deletes

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2024-07-12T12:36:55Z

    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)

  8. Self referential foreign keys in partitioned table not working as expected

    Luca Vallisa <luca.vallisa@gmail.com> — 2025-04-01T09:06:13Z

    As mentioned at
https://www.postgresql.org/message-id/18156-a44bc7096f0683e6%40postgresql.org
this is a regression introduced in version 15.X and still present in 17.4.

I'm running the postgres:17.4-alpine docker official image.

-------------------------------------------------------------------------------------
REPRO
-------------------------------------------------------------------------------------

drop table if exists test;

create table test (
    id_1 int4 not null,
    id_2 int4 not null,
    parent_id_1 int4 null,
    primary key (id_1, id_2),
    foreign key (parent_id_1, id_2) references test(id_1, id_2)
) partition by list(id_1);

create table test_1 partition of test for values in (1);
create table test_9 partition of test for values in (9);

insert into test values (1, 1, null), (1, 2, 1);

-- doesn't trigger an error
-- delete from test where (id_1, id_2) = (1, 1);

-- doesn't trigger an error
-- update test set id_1 = 9 where (id_1, id_2) = (1, 1);

-------------------------------------------------------------------------------------

Both deletion and update runs smoothly.
Also, potential cascade delete and cascade update rules are ignored.

  9. Re: Self referential foreign keys in partitioned table not working as expected

    Christoph Berg <myon@debian.org> — 2025-04-01T10:18:30Z

    Re: Luca Vallisa
> create table test (
>     id_1 int4 not null,
>     id_2 int4 not null,
>     parent_id_1 int4 null,
>     primary key (id_1, id_2),
>     foreign key (parent_id_1, id_2) references test(id_1, id_2)
> ) partition by list(id_1);
> 
> insert into test values (1, 1, null), (1, 2, 1);

Multi-column foreign keys where one column is NULL effectively disable
the FK, this is not specific to partitioned tables. They works as
designed, but best avoid them.

Christoph

  10. Re: Self referential foreign keys in partitioned table not working as expected

    Luca Vallisa <luca.vallisa@gmail.com> — 2025-04-01T10:24:46Z

    Thanks for the reply.

I've realized I messed up with the script.

Please refer to the following one.

---------------------------------------------------------------------------------------

drop table if exists test;
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); /** uncomment this line **/
--create table test_1 partition of test for values in (1); /** uncomment
this line **/
--create table test_9 partition of test for values in (9) /** uncomment
this line **/
;

insert into test values (1, 1, null), (1, 2, 1);
delete from test where (id_1, id_2) = (1, 1);
--update test set id_2 = 9 where (id_1, id_2) = (1, 1);
select * from test;
drop table if exists test;

---------------------------------------------------------------------------------------


The provided version throws an error.
If you uncomment the 3 lines (working with partition) the error is not
thrown during the delete (or the update).

Luca

Il giorno mar 1 apr 2025 alle ore 12:18 Christoph Berg <myon@debian.org> ha
scritto:

> Re: Luca Vallisa
> > create table test (
> >     id_1 int4 not null,
> >     id_2 int4 not null,
> >     parent_id_1 int4 null,
> >     primary key (id_1, id_2),
> >     foreign key (parent_id_1, id_2) references test(id_1, id_2)
> > ) partition by list(id_1);
> >
> > insert into test values (1, 1, null), (1, 2, 1);
>
> Multi-column foreign keys where one column is NULL effectively disable
> the FK, this is not specific to partitioned tables. They works as
> designed, but best avoid them.
>
> Christoph
>

  11. Re: Self referential foreign keys in partitioned table not working as expected

    Christoph Berg <myon@debian.org> — 2025-04-01T10:46:02Z

    Re: Luca Vallisa
> The provided version throws an error.

Ok, I can confirm this.

This throws an error like it should:

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)
);
insert into test values (1, 1, null), (1, 2, 1);
delete from test where (id_1, id_2) = (1, 1);

On a partitioned table, it does not throw the error:

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

Christoph

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

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2025-05-01T12:14:50Z

    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)

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

    Tender Wang <tndrwang@gmail.com> — 2025-05-01T15:21:27Z

    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

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

    Álvaro Herrera <alvherre@kurilemu.de> — 2025-05-03T13:09:23Z

    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)

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

    Matthew Gabeler-Lee <fastcat@gmail.com> — 2025-05-08T17:14:56Z

    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

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

    Álvaro Herrera <alvherre@kurilemu.de> — 2025-05-08T17:35:46Z

    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)

  17. Re: Self referential foreign keys in partitioned table not working as expected

    Álvaro Herrera <alvherre@kurilemu.de> — 2025-07-23T09:27:04Z

    On 2025-Apr-01, Christoph Berg wrote:

> Re: Luca Vallisa
> > The provided version throws an error.
> 
> Ok, I can confirm this.

> On a partitioned table, it does not throw the error:
> 
> 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);

FWIW I didn't give closure on this thread, but AFAICT this is the same
bug that was reported in
https://postgr.es/m/18156-a44bc7096f0683e6@postgresql.org
and
https://postgr.es/m/CAECtzeWHCA+6tTcm2Oh2+g7fURUJpLZb-=pRXgeWJ-Pi+VU=_w@mail.gmail.com

That was fixed a couple of months ago.  The above script throws an error
as it should.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/