Foreign keys and partitioned tables
Alvaro Herrera <alvherre@2ndquadrant.com>
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Pg Hackers <pgsql-hackers@postgresql.org>
Date: 2017-12-31T19:43:59Z
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
Commits
-
Restore erroneously removed ONLY from PK check
- 7d7c99790b2a 11.0 landed
-
Foreign keys on partitioned tables
- 3de241dba86f 11.0 landed
-
Don't clone internal triggers to partitions
- 851f4b4e1430 11.0 landed
-
Pass correct TupDesc to ri_NullCheck() in Assert
- cd5005bc12d0 11.0 landed