Fully enforce uniqueness of constraint names.

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.

Discussion: https://postgr.es/m/0c1001d4428f$0942b430$1bc81c90$@webkr.de

4 files changed, 11 insertions, 5 deletions

diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 0585e9b3bad..a365807dab1 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	201809031
+#define CATALOG_VERSION_NO	201809042
 
 #endif
diff --git a/src/include/catalog/indexing.h b/src/include/catalog/indexing.h
index 24915824caa..254fbef1f78 100644
--- a/src/include/catalog/indexing.h
+++ b/src/include/catalog/indexing.h
@@ -121,8 +121,8 @@ DECLARE_UNIQUE_INDEX(pg_collation_oid_index, 3085, on pg_collation using btree(o
 
 DECLARE_INDEX(pg_constraint_conname_nsp_index, 2664, on pg_constraint using btree(conname name_ops, connamespace oid_ops));
 #define ConstraintNameNspIndexId  2664
-DECLARE_INDEX(pg_constraint_conrelid_index, 2665, on pg_constraint using btree(conrelid oid_ops));
-#define ConstraintRelidIndexId	2665
+DECLARE_UNIQUE_INDEX(pg_constraint_conrelid_contypid_conname_index, 2665, on pg_constraint using btree(conrelid oid_ops, contypid oid_ops, conname name_ops));
+#define ConstraintRelidTypidNameIndexId	2665
 DECLARE_INDEX(pg_constraint_contypid_index, 2666, on pg_constraint using btree(contypid oid_ops));
 #define ConstraintTypidIndexId	2666
 DECLARE_UNIQUE_INDEX(pg_constraint_oid_index, 2667, on pg_constraint using btree(oid oid_ops));
diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h
index 9d209c9d190..66b3f13f74a 100644
--- a/src/include/catalog/pg_constraint.h
+++ b/src/include/catalog/pg_constraint.h
@@ -39,6 +39,10 @@ CATALOG(pg_constraint,2606,ConstraintRelationId)
 	 * global lock to generate a globally unique name for a nameless
 	 * constraint.  We associate a namespace with constraint names only for
 	 * SQL-spec compatibility.
+	 *
+	 * However, we do require conname to be unique among the constraints of a
+	 * single relation or domain.  This is enforced by a unique index on
+	 * conrelid + contypid + conname.
 	 */
 	NameData	conname;		/* name of this constraint */
 	Oid			connamespace;	/* OID of namespace containing constraint */
@@ -233,7 +237,8 @@ extern void RemoveConstraintById(Oid conId);
 extern void RenameConstraintById(Oid conId, const char *newname);
 
 extern bool ConstraintNameIsUsed(ConstraintCategory conCat, Oid objId,
-					 Oid objNamespace, const char *conname);
+					 const char *conname);
+extern bool ConstraintNameExists(const char *conname, Oid namespaceid);
 extern char *ChooseConstraintName(const char *name1, const char *name2,
 					 const char *label, Oid namespaceid,
 					 List *others);
diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h
index 6b837236d4d..1d05a4bcdc8 100644
--- a/src/include/commands/defrem.h
+++ b/src/include/commands/defrem.h
@@ -41,7 +41,8 @@ extern void ReindexMultipleTables(const char *objectName, ReindexObjectType obje
 extern char *makeObjectName(const char *name1, const char *name2,
 			   const char *label);
 extern char *ChooseRelationName(const char *name1, const char *name2,
-				   const char *label, Oid namespaceid);
+				   const char *label, Oid namespaceid,
+				   bool isconstraint);
 extern bool CheckIndexCompatible(Oid oldId,
 					 const char *accessMethodName,
 					 List *attributeList,