Change the names generated for child foreign key constraints. When a foreign key constraint is placed on a partitioned table, we actually make two pg_constraint entries associated with that table. (I have my doubts about the wisdom of that, but it's been like that since v12 and post-feature-freeze is no time to be messing with such entrenched decisions.) The second "child" entry always had a name generated according to the default rule, "table_column(s)_fkey[nnn]", even if the primary entry had an unrelated user-specified name. The trouble with doing that is that the default name could collide with the user-specified name of some other constraint on the same table. While we were willing to adjust the generated name to avoid collisions, that only helps if it's made second; if it's made first then creation of the other constraint would fail, potentially causing dump/reload or pg_upgrade failures.
The core of the problem here is that we're infringing on user namespace, so I doubt that there's any 100% solution other than to find a way to not need the "child" entry. In the meantime, it seems like it'd be an improvement to make the child's name be the name of the parent constraint with an underscore and digit(s) appended as necessary to make it unique. This rule can in theory fail in the same way, but it seems much less probable; for one thing, this rule is guaranteed not to match primary entries having auto-generated names. (While an auto-generated primary name isn't user-specified to begin with, it acts like that during dump/reload, so collisions against such names are definitely possible.) An additional bonus, visible in some of the regression test cases that change here, arises from the fact that some error messages cite the child constraint's name not the parent's. In the previous approach the two names could be completely unrelated, leading to user confusion --- the more so since psql's \d command hides child constraints. With this approach it's hopefully much clearer which constraint-the-user-knows-about is failing. However, that does mean that there's user-visible behavior change occurring here, making it seem like not something to back-patch. I feel it's not too late for v18, though. Reported-by: Kirill Reshke <reshkekir...@gmail.com> Author: Tom Lane <t...@sss.pgh.pa.us> Reviewed-by: Alvaro Herrera <alvhe...@kurilemu.de> Discussion: https://postgr.es/m/caldssphgitjptfzemjn-y2x+q-5qchsxasmsj1-e8mqjlkh...@mail.gmail.com Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/3db61db48ef5b8898f7e85f98548fdec79d76524 Modified Files -------------- src/backend/catalog/pg_constraint.c | 9 ++- src/backend/commands/tablecmds.c | 10 +-- .../expected/detach-partition-concurrently-2.out | 2 +- .../expected/detach-partition-concurrently-4.out | 2 +- src/test/isolation/expected/fk-partitioned-1.out | 14 ++-- src/test/isolation/expected/fk-partitioned-2.out | 4 +- src/test/regress/expected/constraints.out | 6 +- src/test/regress/expected/foreign_key.out | 82 +++++++++++----------- src/test/regress/expected/without_overlaps.out | 8 +-- src/test/regress/sql/foreign_key.sql | 4 +- 10 files changed, 74 insertions(+), 67 deletions(-)
