Foreign keys and partitioned tables

From: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
To: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Foreign keys and partitioned tables
Date: 2017-12-31 19:43:59
Message-ID: 20171231194359.cvojcour423ulha4@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2017-12-31 19:49:08 Re: Foreign keys and partitioned tables
Previous Message Peter Geoghegan 2017-12-31 19:31:06 Re: TODO list (was Re: Contributing with code)