Re: partitioned tables referenced by FKs

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
Cc: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: partitioned tables referenced by FKs
Date: 2019-03-19 09:53:59
Message-ID: da1d5f5e-6a7d-ad7a-4339-46e4ad57ba03@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
v6-pre_drop_class_check-troubles-fix.patch text/plain 5.0 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Chris Travers 2019-03-19 09:59:38 Re: [HACKERS] Custom compression methods
Previous Message pantilimonov misha 2019-03-19 09:53:49 [GSoC] application ideas