|From:||Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>|
|To:||Pg Hackers <pgsql-hackers(at)postgresql(dot)org>|
|Subject:||partitioned tables referenced by FKs|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
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).
There are tests that should cover all the basic features. pg_upgrade
tests work (containing relevant tables, as regress/foreign_key.sql
leaves them behind for this reason: partitioned-references-partitioned).
There is one requisite feature still missing from this patch: when a
partition on the referenced side is detached or dropped, we must ensure
no referencing row remains on the other side. For this, I have an
(unmerged and thus unsubmitted here) new ri_triggers.c routine
RI_Inverted_Initial_Check (need to come up with better name, heh) that
verifies this, invoked at DETACH/DROP time.
Also, some general code cleanup and documentation patching is needed.
I'm posting this now so that I can take my hands off it for a few days;
will return to post an updated version at some point before next
commitfest. I wanted to have this ready for this commitfest, but RL
dictated otherwise. This patch took a *very long time* to write ... I
wrote three different recursion models for this.
One thing I realized while writing this, is that my commit
3de241dba86f ("Foreign keys on partitioned tables") put function
CloneForeignKeyConstraints() in catalog/pg_constraint.c that should
really have been in tablecmds.c. In this patch I produced some siblings
of that function still in pg_constraint.c, but I intend to move the
whole lot to tablecmds.c before commit.
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
|Next Message||Alvaro Herrera||2018-11-02 23:45:12||Re: partitioned tables referenced by FKs|
|Previous Message||Michael Paquier||2018-11-02 23:27:30||Re: INSTALL file|