Re: partitioned tables referenced by FKs

From: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
To: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
Cc: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: partitioned tables referenced by FKs
Date: 2019-03-21 21:54:20
Message-ID: 20190321215420.GA22766@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
v7-0001-Rework-deleteObjectsInList-to-allow-objtype-speci.patch text/x-diff 2.2 KB
v7-0002-support-FKs-referencing-partitioned-tables.patch text/x-diff 91.7 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2019-03-21 22:18:24 Re: partitioned tables referenced by FKs
Previous Message Tom Lane 2019-03-21 21:10:06 Re: Performance issue in foreign-key-aware join estimation