Re: partitioned tables referenced by FKs

On 2019-Mar-18, Alvaro Herrera wrote:

> A pretty silly bug remains here. Watch:
>
> create table pk (a int primary key) partition by list (a);
> create table pk1 partition of pk for values in (1);
> create table fk (a int references pk);
> insert into pk values (1);
> insert into fk values (1);
> drop table pk cascade;
>
> Note that the DROP of the main PK table is pretty aggressive: since it
> cascades, you want it to drop the constraint on the FK side. This would
> work without a problem if 'pk' was a non-partitioned table, but in this
> case it fails:
>
> alvherre=# drop table pk cascade;
> NOTICE: drop cascades to constraint fk_a_fkey on table fk
> ERROR: removing partition "pk1" violates foreign key constraint "fk_a_fkey1"
> DETALLE: Key (a)=(1) still referenced from table "fk".

Here's v7; known problems have been addressed except the one above.
Jesper's GetCachedPlan() slowness has not been addressed either.

As I said before, I'm thinking of getting rid of the whole business of
checking partitions on the referenced side of an FK at DROP time, and
instead jut forbid the DROP completely if any FKs reference an ancestor
of that partition. We can allow DETACH for the case, which can deal
with scanning the table referenced tuples and remove the appropriate
dependencies.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services