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

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