Horton

Re: partitioned tables referenced by FKs

Alvaro Herrera <alvherre@2ndquadrant.com>

View thread View on postgr.es
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Date: 2019-03-21T21:54:20Z
Lists: pgsql-hackers

Attachments

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

Commits

  1. Support foreign keys that reference partitioned tables

  2. Fix partitioned index creation bug with dropped columns

  3. Improve psql's \d display of foreign key constraints

  4. Fix dependency recording bug for partitioned PKs

  5. Add index_get_partition convenience function

  6. Restore RI trigger sanity check

  7. Foreign keys on partitioned tables