0002-enforce-constraint-name-uniqueness-more.patch

diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index b256054..70a1767 100644
*** a/src/backend/catalog/index.c
--- b/src/backend/catalog/index.c
*************** index_create(Relation heapRelation,
*** 842,847 ****
--- 842,853 ----
  	if (shared_relation && tableSpaceId != GLOBALTABLESPACE_OID)
  		elog(ERROR, "shared relations must be placed in pg_global tablespace");
  
+ 	/*
+ 	 * Check for duplicate name (both as to the index, and as to the
+ 	 * associated constraint if any).  Such cases would fail on the relevant
+ 	 * catalogs' unique indexes anyway, but we prefer to give a friendlier
+ 	 * error message.
+ 	 */
  	if (get_relname_relid(indexRelationName, namespaceId))
  	{
  		if ((flags & INDEX_CREATE_IF_NOT_EXISTS) != 0)
*************** index_create(Relation heapRelation,
*** 860,865 ****
--- 866,885 ----
  						indexRelationName)));
  	}
  
+ 	if ((flags & INDEX_CREATE_ADD_CONSTRAINT) != 0 &&
+ 		ConstraintNameIsUsed(CONSTRAINT_RELATION, heapRelationId,
+ 							 namespaceId, indexRelationName))
+ 	{
+ 		/*
+ 		 * INDEX_CREATE_IF_NOT_EXISTS does not apply here, since the
+ 		 * conflicting constraint is not an index.
+ 		 */
+ 		ereport(ERROR,
+ 				(errcode(ERRCODE_DUPLICATE_OBJECT),
+ 				 errmsg("constraint \"%s\" for relation \"%s\" already exists",
+ 						indexRelationName, RelationGetRelationName(heapRelation))));
+ 	}
+ 
  	/*
  	 * construct tuple descriptor for index tuples
  	 */
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index 3d65464..51f16ce 100644
*** a/src/backend/catalog/pg_constraint.c
--- b/src/backend/catalog/pg_constraint.c
*************** ConstraintNameIsUsed(ConstraintCategory 
*** 701,706 ****
--- 701,744 ----
  }
  
  /*
+  * Does any constraint of the given name exist in the given namespace?
+  *
+  * This is used for code that wants to match ChooseConstraintName's rule
+  * that we should avoid autogenerating duplicate constraint names within a
+  * namespace.
+  */
+ bool
+ ConstraintNameExists(const char *conname, Oid namespaceid)
+ {
+ 	bool		found;
+ 	Relation	conDesc;
+ 	SysScanDesc conscan;
+ 	ScanKeyData skey[2];
+ 
+ 	conDesc = heap_open(ConstraintRelationId, AccessShareLock);
+ 
+ 	ScanKeyInit(&skey[0],
+ 				Anum_pg_constraint_conname,
+ 				BTEqualStrategyNumber, F_NAMEEQ,
+ 				CStringGetDatum(conname));
+ 
+ 	ScanKeyInit(&skey[1],
+ 				Anum_pg_constraint_connamespace,
+ 				BTEqualStrategyNumber, F_OIDEQ,
+ 				ObjectIdGetDatum(namespaceid));
+ 
+ 	conscan = systable_beginscan(conDesc, ConstraintNameNspIndexId, true,
+ 								 NULL, 2, skey);
+ 
+ 	found = (HeapTupleIsValid(systable_getnext(conscan)));
+ 
+ 	systable_endscan(conscan);
+ 	heap_close(conDesc, AccessShareLock);
+ 
+ 	return found;
+ }
+ 
+ /*
   * Select a nonconflicting name for a new constraint.
   *
   * The objective here is to choose a name that is unique within the
*************** RenameConstraintById(Oid conId, const ch
*** 899,906 ****
  	con = (Form_pg_constraint) GETSTRUCT(tuple);
  
  	/*
! 	 * We need to check whether the name is already in use --- note that there
! 	 * currently is not a unique index that would catch this.
  	 */
  	if (OidIsValid(con->conrelid) &&
  		ConstraintNameIsUsed(CONSTRAINT_RELATION,
--- 937,943 ----
  	con = (Form_pg_constraint) GETSTRUCT(tuple);
  
  	/*
! 	 * For user-friendliness, check whether the name is already in use.
  	 */
  	if (OidIsValid(con->conrelid) &&
  		ConstraintNameIsUsed(CONSTRAINT_RELATION,
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index d54c78c..ab3d9a0 100644
*** a/src/backend/commands/indexcmds.c
--- b/src/backend/commands/indexcmds.c
*************** makeObjectName(const char *name1, const 
*** 1995,2000 ****
--- 1995,2006 ----
   * except that the label can't be NULL; digits will be appended to the label
   * if needed to create a name that is unique within the specified namespace.
   *
+  * If isconstraint is true, we also avoid choosing a name matching any
+  * existing constraint in the same namespace.  (This is stricter than what
+  * Postgres itself requires, but the SQL standard says that constraint names
+  * should be unique within schemas, so we follow that for autogenerated
+  * constraint names.)
+  *
   * Note: it is theoretically possible to get a collision anyway, if someone
   * else chooses the same name concurrently.  This is fairly unlikely to be
   * a problem in practice, especially if one is holding an exclusive lock on
*************** makeObjectName(const char *name1, const 
*** 2006,2012 ****
   */
  char *
  ChooseRelationName(const char *name1, const char *name2,
! 				   const char *label, Oid namespaceid)
  {
  	int			pass = 0;
  	char	   *relname = NULL;
--- 2012,2019 ----
   */
  char *
  ChooseRelationName(const char *name1, const char *name2,
! 				   const char *label, Oid namespaceid,
! 				   bool isconstraint)
  {
  	int			pass = 0;
  	char	   *relname = NULL;
*************** ChooseRelationName(const char *name1, co
*** 2020,2026 ****
  		relname = makeObjectName(name1, name2, modlabel);
  
  		if (!OidIsValid(get_relname_relid(relname, namespaceid)))
! 			break;
  
  		/* found a conflict, so try a new name component */
  		pfree(relname);
--- 2027,2037 ----
  		relname = makeObjectName(name1, name2, modlabel);
  
  		if (!OidIsValid(get_relname_relid(relname, namespaceid)))
! 		{
! 			if (!isconstraint ||
! 				!ConstraintNameExists(relname, namespaceid))
! 				break;
! 		}
  
  		/* found a conflict, so try a new name component */
  		pfree(relname);
*************** ChooseIndexName(const char *tabname, Oid
*** 2048,2075 ****
  		indexname = ChooseRelationName(tabname,
  									   NULL,
  									   "pkey",
! 									   namespaceId);
  	}
  	else if (exclusionOpNames != NIL)
  	{
  		indexname = ChooseRelationName(tabname,
  									   ChooseIndexNameAddition(colnames),
  									   "excl",
! 									   namespaceId);
  	}
  	else if (isconstraint)
  	{
  		indexname = ChooseRelationName(tabname,
  									   ChooseIndexNameAddition(colnames),
  									   "key",
! 									   namespaceId);
  	}
  	else
  	{
  		indexname = ChooseRelationName(tabname,
  									   ChooseIndexNameAddition(colnames),
  									   "idx",
! 									   namespaceId);
  	}
  
  	return indexname;
--- 2059,2090 ----
  		indexname = ChooseRelationName(tabname,
  									   NULL,
  									   "pkey",
! 									   namespaceId,
! 									   true);
  	}
  	else if (exclusionOpNames != NIL)
  	{
  		indexname = ChooseRelationName(tabname,
  									   ChooseIndexNameAddition(colnames),
  									   "excl",
! 									   namespaceId,
! 									   true);
  	}
  	else if (isconstraint)
  	{
  		indexname = ChooseRelationName(tabname,
  									   ChooseIndexNameAddition(colnames),
  									   "key",
! 									   namespaceId,
! 									   true);
  	}
  	else
  	{
  		indexname = ChooseRelationName(tabname,
  									   ChooseIndexNameAddition(colnames),
  									   "idx",
! 									   namespaceId,
! 									   false);
  	}
  
  	return indexname;
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 656b1b5..f5c1e2a 100644
*** a/src/backend/parser/parse_utilcmd.c
--- b/src/backend/parser/parse_utilcmd.c
*************** generateSerialExtraStmts(CreateStmtConte
*** 440,446 ****
  		sname = ChooseRelationName(cxt->relation->relname,
  								   column->colname,
  								   "seq",
! 								   snamespaceid);
  	}
  
  	ereport(DEBUG1,
--- 440,447 ----
  		sname = ChooseRelationName(cxt->relation->relname,
  								   column->colname,
  								   "seq",
! 								   snamespaceid,
! 								   false);
  	}
  
  	ereport(DEBUG1,
diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h
index 4435310..49df169 100644
*** a/src/include/catalog/pg_constraint.h
--- b/src/include/catalog/pg_constraint.h
*************** extern void RenameConstraintById(Oid con
*** 244,249 ****
--- 244,250 ----
  
  extern bool ConstraintNameIsUsed(ConstraintCategory conCat, Oid objId,
  					 Oid objNamespace, 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 6b83723..1d05a4b 100644
*** a/src/include/commands/defrem.h
--- b/src/include/commands/defrem.h
*************** extern void ReindexMultipleTables(const 
*** 41,47 ****
  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);
  extern bool CheckIndexCompatible(Oid oldId,
  					 const char *accessMethodName,
  					 List *attributeList,
--- 41,48 ----
  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,
! 				   bool isconstraint);
  extern bool CheckIndexCompatible(Oid oldId,
  					 const char *accessMethodName,
  					 List *attributeList,
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 0218c2c..dccc9b2 100644
*** a/src/test/regress/expected/alter_table.out
--- b/src/test/regress/expected/alter_table.out
*************** Check constraints:
*** 2994,2999 ****
--- 2994,3034 ----
  
  DROP TABLE alter2.tt8;
  DROP SCHEMA alter2;
+ --
+ -- Check conflicts between index and CHECK constraint names
+ --
+ CREATE TABLE tt9(c integer);
+ ALTER TABLE tt9 ADD CHECK(c > 1);
+ ALTER TABLE tt9 ADD CHECK(c > 2);  -- picks nonconflicting name
+ ALTER TABLE tt9 ADD CONSTRAINT foo CHECK(c > 3);
+ ALTER TABLE tt9 ADD CONSTRAINT foo CHECK(c > 4);  -- fail, dup name
+ ERROR:  constraint "foo" for relation "tt9" already exists
+ ALTER TABLE tt9 ADD UNIQUE(c);
+ ALTER TABLE tt9 ADD UNIQUE(c);  -- picks nonconflicting name
+ ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key UNIQUE(c);  -- fail, dup name
+ ERROR:  relation "tt9_c_key" already exists
+ ALTER TABLE tt9 ADD CONSTRAINT foo UNIQUE(c);  -- fail, dup name
+ ERROR:  constraint "foo" for relation "tt9" already exists
+ ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key CHECK(c > 5);  -- fail, dup name
+ ERROR:  constraint "tt9_c_key" for relation "tt9" already exists
+ ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key2 CHECK(c > 6);
+ ALTER TABLE tt9 ADD UNIQUE(c);  -- picks nonconflicting name
+ \d tt9
+                 Table "public.tt9"
+  Column |  Type   | Collation | Nullable | Default 
+ --------+---------+-----------+----------+---------
+  c      | integer |           |          | 
+ Indexes:
+     "tt9_c_key" UNIQUE CONSTRAINT, btree (c)
+     "tt9_c_key1" UNIQUE CONSTRAINT, btree (c)
+     "tt9_c_key3" UNIQUE CONSTRAINT, btree (c)
+ Check constraints:
+     "foo" CHECK (c > 3)
+     "tt9_c_check" CHECK (c > 1)
+     "tt9_c_check1" CHECK (c > 2)
+     "tt9_c_key2" CHECK (c > 6)
+ 
+ DROP TABLE tt9;
  -- Check that comments on constraints and indexes are not lost at ALTER TABLE.
  CREATE TABLE comment_test (
    id int,
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 22cf4ef..b904978 100644
*** a/src/test/regress/sql/alter_table.sql
--- b/src/test/regress/sql/alter_table.sql
*************** ALTER TABLE IF EXISTS tt8 SET SCHEMA alt
*** 1865,1870 ****
--- 1865,1888 ----
  DROP TABLE alter2.tt8;
  DROP SCHEMA alter2;
  
+ --
+ -- Check conflicts between index and CHECK constraint names
+ --
+ CREATE TABLE tt9(c integer);
+ ALTER TABLE tt9 ADD CHECK(c > 1);
+ ALTER TABLE tt9 ADD CHECK(c > 2);  -- picks nonconflicting name
+ ALTER TABLE tt9 ADD CONSTRAINT foo CHECK(c > 3);
+ ALTER TABLE tt9 ADD CONSTRAINT foo CHECK(c > 4);  -- fail, dup name
+ ALTER TABLE tt9 ADD UNIQUE(c);
+ ALTER TABLE tt9 ADD UNIQUE(c);  -- picks nonconflicting name
+ ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key UNIQUE(c);  -- fail, dup name
+ ALTER TABLE tt9 ADD CONSTRAINT foo UNIQUE(c);  -- fail, dup name
+ ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key CHECK(c > 5);  -- fail, dup name
+ ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key2 CHECK(c > 6);
+ ALTER TABLE tt9 ADD UNIQUE(c);  -- picks nonconflicting name
+ \d tt9
+ DROP TABLE tt9;
+ 
  
  -- Check that comments on constraints and indexes are not lost at ALTER TABLE.
  CREATE TABLE comment_test (