Re: partitioned tables referenced by FKs

Hi,

Thanks for updating the patch. I'll reply to other parts separately.

On 2019/03/19 7:02, 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".
>
> The reason is that the "pre drop check" that's done before allow a drop
> of the partition doesn't realize that the constraint is also being
> dropped (and so the check ought to be skipped). If you were to do just
> "DROP TABLE pk1" then this complaint would be correct, since it would
> leave the constraint in an invalid state. But here, it's bogus and
> annoying. You can DELETE the matching values from table FK and then the
> DROP works. Here's another problem caused by the same misbehavior:
>
> alvherre=# drop table pk, fk;
> ERROR: removing partition "pk1" violates foreign key constraint "fk_a_fkey1"
> DETALLE: Key (a)=(1) still referenced from table "fk".
>
> Note here we want to get rid of table 'fk' completely. If you split it
> up in a DROP of fk, followed by a DROP of pk, it works.
>
> I'm not yet sure what's the best way to attack this. Maybe the
> "pre-drop check" needs a completely different approach. The simplest
> approach is to prohibit a table drop or detach for any partitioned table
> that's referenced by a foreign key, but that seems obnoxious and
> inconvenient.

Agreed.

Will it suffice or be OK if we skipped invoking the pre-drop callback for
objects that are being "indirectly" dropped? I came up with the attached
patch to resolve this problem, if that idea has any merit. We also get
slightly better error message as seen the expected/foreign_key.out changes.

Thanks,
Amit