It's been true for a long time that we expect names of table and domain constraints to be unique among the constraints of that table or domain. However, the enforcement of that has been pretty haphazard, and it missed some corner cases such as creating a CHECK constraint and then an index constraint of the same name (as per recent report from André Hänsel). Also, due to the lack of an actual unique index enforcing this, duplicates could be created through race conditions.
Moreover, the code that searches pg_constraint has been quite inconsistent about how to handle duplicate names if one did occur: some places checked and threw errors if there was more than one match, while others just processed the first match they came to.
To fix, create a unique index on (conrelid, contypid, conname). Since either conrelid or contypid is zero, this will separately enforce uniqueness of constraint names among constraints of any one table and any one domain. (If we ever implement SQL assertions, and put them into this catalog, more thought might be needed. But it'd be at least as reasonable to put them into a new catalog; having overloaded this one catalog with two kinds of constraints was a mistake already IMO.) This index can replace the existing non-unique index on conrelid, though we need to keep the one on contypid for query performance reasons.
Having done that, we can simplify the logic in various places that either coped with duplicates or neglected to, as well as potentially improve lookup performance when searching for a constraint by name.
Also, as per our usual practice, install a preliminary check so that you get something more friendly than a unique-index violation report in the case complained of by André. And teach ChooseIndexName to avoid choosing autogenerated names that would draw such a failure.
While it's not possible to make such a change in the back branches, it doesn't seem quite too late to put this into v11, so do so.
17b7c302b5 Fully enforce uniqueness of constraint names.
doc/src/sgml/ref/alter_index.sgml | 3 +
doc/src/sgml/ref/alter_table.sgml | 37 +++--
doc/src/sgml/ref/create_table.sgml | 24 +++
src/backend/catalog/heap.c | 190 +++++++++++-----------
src/backend/catalog/index.c | 20 +++
src/backend/catalog/pg_constraint.c | 254 +++++++++++++++---------------
src/backend/commands/indexcmds.c | 27 +++-
src/backend/commands/tablecmds.c | 138 ++++++++--------
src/backend/commands/typecmds.c | 85 +++++-----
src/backend/parser/parse_utilcmd.c | 3 +-
src/backend/utils/cache/relcache.c | 10 +-
src/include/catalog/catversion.h | 2 +-
src/include/catalog/indexing.h | 4 +-
src/include/catalog/pg_constraint.h | 7 +-
src/include/commands/defrem.h | 3 +-
src/test/regress/expected/alter_table.out | 35 ++++
src/test/regress/sql/alter_table.sql | 18 +++
17 files changed, 494 insertions(+), 366 deletions(-)