Thread: Re: BUG #16908: Postgres (12) allows you (re)-attach partitions that violate Foreign Key constraints?
Re: BUG #16908: Postgres (12) allows you (re)-attach partitions that violate Foreign Key constraints?
From
Alvaro Herrera
Date:
On 2021-Mar-02, David Rowley wrote: > Here's a simplifed test case: > > create table p(a int primary key) partition by list(a); > create table p1 partition of p for values in(1); > create table z(a int primary key references p) partition by list(a); > create table z1 partition of z for values in(1); > insert into p values(1); > insert into z values(1); > alter table z detach partition z1; > alter table p detach partition p1; > alter table z attach partition z1 for values in(1); -- shouldn't be allowed. Hmm, I agree that this is misbehaving. However ... > But it is allowed as CloneFkReferencing() calls > tryAttachPartitionForeignKey() which checks for an existing foreign > key to the table. It happens to find one. > > \d z1 > Table "public.z1" > Column | Type | Collation | Nullable | Default > --------+---------+-----------+----------+--------- > a | integer | | not null | > Indexes: > "z1_pkey" PRIMARY KEY, btree (a) > Foreign-key constraints: > "z_a_fkey" FOREIGN KEY (a) REFERENCES p(a) > > I imagine that this foreign key should have been removed when z1 was > detached from z. If it's not then since when we detach p1 from p, we > can only check currently attached partitions on referencing tables to > see if there would be any FK violations. ... I'm not sure that we need to remove the FK from 'z1'; we know well enough that the constraint still stands. The problem IMO occurs when we detach p1 -- that's the step that causes the FK go wrong. I think that DETACH step should throw the error that there are tables referencing the values in the partition. This works correctly when 'z' is a plain table, so what is different regarding the constraint in 'z1' (which is now a plain table)? I'll have a look. -- Álvaro Herrera 39°49'30"S 73°17'W