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

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(at)lelarge(dot)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.