From: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> |
---|---|
To: | Pg Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | partitioned tables referenced by FKs |
Date: | 2018-11-02 23:41:58 |
Message-ID: | 20181102234158.735b3fevta63msbj@alvherre.pgsql |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
Attachment | Content-Type | Size |
---|---|---|
partitioned-foreign-keys.patch | text/x-diff | 52.6 KB |
From | Date | Subject | |
---|---|---|---|
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 |