Re: BUG #16908: Postgres (12) allows you (re)-attach partitions that violate Foreign Key constraints?

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