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