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

On Tue, 2 Mar 2021 at 10:27, PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
> I can then reattach the partition of measurement_drilldown_y2006m02 whilst
> the measurement partition is still detached - thus violating the FK
> constraint on the measurement_drilldown table:
>
> ```
> ALTER TABLE measurement_drilldown
> ATTACH PARTITION measurement_drilldown_y2006m02
> FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
> ```
>
> This results in a situation where the measurement_drilldown table contains
> data with the foreign key constraint not enforced -

> So the question I have is, is this by design?

It looks like a bug to me.

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.

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've copied in Alvaro to see what his thoughts are.

David