Horton

Re: partitioned tables referenced by FKs

Corey Huinker <corey.huinker@gmail.com>

View thread View on postgr.es
From: Corey Huinker <corey.huinker@gmail.com>
To: alvherre@2ndquadrant.com
Cc: pgsql-hackers@postgresql.org
Date: 2018-11-05T05:53:33Z
Lists: pgsql-hackers 
On Fri, Nov 2, 2018 at 7:42 PM Alvaro Herrera <alvherre@2ndquadrant.com>
wrote:

> Here's a patch to allow partitioned tables to be referenced by foreign
> keys.  Current state is WIP, but everything should work; see below for
> the expected exception.
>
> The design is very simple: have one pg_constraint row for each partition
> on each side, each row pointing to the topmost table on the other side;
> triggers appear on each leaf partition (and naturally they don't appear
> on any intermediate partitioned table).
>

This is an important and much needed feature!

Based on my extremely naive reading of this code, I have two perhaps
equally naive questions:

1. it seems that we will continue to to per-row RI checks for inserts and
updates. However, there already exists a bulk check in RI_Initial_Check().
Could we modify this bulk check to do RI checks on a per-statement basis
rather than a per-row basis?

2. If #1 is possible, is the overhead of transitions tables too great for
the single-row case?

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