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