partitioned tables referenced by FKs
Alvaro Herrera <alvherre@2ndquadrant.com>
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Pg Hackers <pgsql-hackers@postgresql.org>
Date: 2018-11-02T23:41:58Z
Lists: pgsql-hackers
Attachments
- partitioned-foreign-keys.patch (text/x-diff) patch
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
Commits
-
Support foreign keys that reference partitioned tables
- f56f8f8da6af 12.0 landed
-
Fix partitioned index creation bug with dropped columns
- 7009f1a2df65 11.3 landed
- 126d63122232 12.0 landed
-
Improve psql's \d display of foreign key constraints
- 1af25ca0c2d9 12.0 cited
-
Fix dependency recording bug for partitioned PKs
- 7e7c57bbb2eb 12.0 landed
-
Add index_get_partition convenience function
- a6da0047158b 12.0 landed
-
Restore RI trigger sanity check
- 815b20ae0c6e 12.0 landed
- 04f9b449aa30 11.3 landed
-
Foreign keys on partitioned tables
- 3de241dba86f 11.0 cited