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.