Date: Sun, 31 Dec 2017 16:43:59 -0300
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Foreign keys and partitioned tables
Message-ID: <20171231194359.cvojcour423ulha4@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
User-Agent: NeoMutt/20170306-137-4415bd-dirty (1.8.0)
Precedence: bulk

This patch enables foreign key constraints to and from partitioned
tables.

Naturally, FKs that reference a partitioned table require unique
constraints, and therefore they shares the restrictions of those: in my
proposed patch, it is only possible if the partition keys are part of
the unique constraint.  That's not explicitly checked by the FK code,
but rather just an property emergent of previous patches.

As far as I can tell, no documentation changes are needed, since AFAICS
we don't claim anywhere that FKs are not supported for partitioned
tables.

pg_dump support is not yet correct here, but otherwise this feature
should work as intended, and all tests pass for me.

I haven't gone exhaustively over things such as partitions created in
odd ways, dropped columns, match partial, etc, so bugs, holes and
non-working corner cases are still expected, but please do report any
you find.

This patch removes all the ONLY markers from queries in ri_triggers.c.
That makes the queries work for the new use case, but I haven't figured
if it breaks things for other use cases.  I suppose not, since regular
inheritance isn't supposed to allow foreign keys in the first place, but
I haven't dug any further.

Patch 0001 attached here corresponds to a squashed version of patches in
other threads; it's here just for convenience.  The patch to be reviewed
for this thread is just 0002 and corresponding functionality.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services