Hello,
When working on the patch to fix another FK violation [1], I found that FK
constraints were not properly cloned to partition not directly hooked to the
root table.
CloneFkReferenced takes care to avoid inherited constraints to clone top-level
constraints only:
/*
* Search for any constraints where this partition is in the referenced
* side. However, we must ignore any constraint whose parent constraint
* is also going to be cloned, to avoid duplicates. [...]
*/
But it seems the top-level constraints are actually never cloned neither.
Surprisingly, the comment explains how this should be done in two steps, but
the code corrupted the first step by skipping inherited constraints and lacks
the second step:
* [...]to avoid duplicates. So do it in two
* steps: first construct the list of constraints to clone, then go over
* that list cloning those whose parents are not in the list. (We must
* not rely on the parent being seen first, since the catalog scan could
* return children first.)
*/
Please, find in attachment a proposal patch to fix this FK violation.
Regards,
[1] https://www.postgresql.org/message-id/20200204183906.115f693e%40firost