Unique indexes & constraints on partitioned tables

From 311cdeeae542a3f4cc20bf2308488756b8d3da80 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Mon, 6 Nov 2017 17:04:55 +0100
Subject: [PATCH] allow indexes on partitioned tables to be unique

---
 doc/src/sgml/ref/alter_table.sgml          |   9 +-
 doc/src/sgml/ref/create_table.sgml         |  16 +++-
 src/backend/bootstrap/bootparse.y          |   2 +
 src/backend/catalog/index.c                |  28 +++++-
 src/backend/catalog/toasting.c             |   4 +-
 src/backend/commands/indexcmds.c           |  80 ++++++++++++++--
 src/backend/commands/tablecmds.c           |  12 ++-
 src/backend/parser/parse_utilcmd.c         |  31 +------
 src/backend/tcop/utility.c                 |   1 +
 src/include/catalog/index.h                |   5 +-
 src/include/commands/defrem.h              |   1 +
 src/include/parser/parse_utilcmd.h         |   3 +-
 src/test/regress/expected/alter_table.out  |   8 --
 src/test/regress/expected/create_table.out |  12 ---
 src/test/regress/expected/indexing.out     | 142 ++++++++++++++++++++++++++++-
 src/test/regress/sql/alter_table.sql       |   2 -
 src/test/regress/sql/create_table.sql      |   8 --
 src/test/regress/sql/indexing.sql          |  73 ++++++++++++++-
 18 files changed, 355 insertions(+), 82 deletions(-)

diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 0a2f3e3646..ee6a45c9ad 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -782,8 +782,9 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
       This form attaches an existing table (which might itself be partitioned)
       as a partition of the target table. The table can be attached
       as a partition for specific values using <literal>FOR VALUES
-      </literal> or as a default partition by using <literal>DEFAULT
-      </literal>.  For each index in the target table, a corresponding
+      </literal> or as a default partition by using
+      <literal>DEFAULT</literal>.
+      For each index in the target table, a corresponding
       one will be created in the attached table; or, if an equivalent
       index already exists, will be attached to the target table's index,
       as if <command>ALTER INDEX ATTACH</command> had been executed.
@@ -798,8 +799,10 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
       as the target table and no more; moreover, the column types must also
       match.  Also, it must have all the <literal>NOT NULL</literal> and
       <literal>CHECK</literal> constraints of the target table.  Currently
-      <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and
       <literal>FOREIGN KEY</literal> constraints are not considered.
+      <literal>UNIQUE</literal> and <literal>PRIMARY KEY</literal> constraints
+      from the parent table will be created in the partition, if they don't
+      already exist.
       If any of the <literal>CHECK</literal> constraints of the table being
       attached is marked <literal>NO INHERIT</literal>, the command will fail;
       such a constraint must be recreated without the <literal>NO INHERIT</literal>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index a0c9a6d257..98ab39473b 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -546,8 +546,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
      </para>
 
      <para>
-      Partitioned tables do not support <literal>UNIQUE</literal>,
-      <literal>PRIMARY KEY</literal>, <literal>EXCLUDE</literal>, or
+      Partitioned tables do not support
+      <literal>EXCLUDE</literal>, or
       <literal>FOREIGN KEY</literal> constraints; however, you can define
       these constraints on individual partitions.
      </para>
@@ -786,6 +786,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
       primary key constraint defined for the table.  (Otherwise it
       would just be the same constraint listed twice.)
      </para>
+
+     <para>
+      When used on partitioned tables, <literal>UNIQUE<literal> constraints
+      must include all the columns of the partition key.
+     <para>
     </listitem>
    </varlistentry>
 
@@ -814,6 +819,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
       about the design of the schema, since a primary key implies that other
       tables can rely on this set of columns as a unique identifier for rows.
      </para>
+
+     <para>
+      <literal>PRIMARY KEY</literal> constraints share the restrictions that
+      <literal>UNIQUE</literal> constraints have when placed on partitioned
+      tables.
+     <para>
+
     </listitem>
    </varlistentry>
 
diff --git a/src/backend/bootstrap/bootparse.y b/src/backend/bootstrap/bootparse.y
index 95835ac1e7..fb14f558ec 100644
--- a/src/backend/bootstrap/bootparse.y
+++ b/src/backend/bootstrap/bootparse.y
@@ -322,6 +322,7 @@ Boot_DeclareIndexStmt:
 								stmt,
 								$4,
 								InvalidOid,
+								InvalidOid,
 								false,
 								false,
 								false,
@@ -367,6 +368,7 @@ Boot_DeclareUniqueIndexStmt:
 								stmt,
 								$5,
 								InvalidOid,
+								InvalidOid,
 								false,
 								false,
 								false,
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index a62fe158ce..58c126f17f 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -677,6 +677,8 @@ UpdateIndexRelation(Oid indexoid,
  *		nonzero to specify a preselected OID.
  * parentIndexRelid: if creating an index partition, the OID of the
  *		parent index; otherwise InvalidOid.
+ * parentConstraintId: if creating a constraint on a partition, the OID
+ *		of the constraint in the parent; otherwise InvalidOid.
  * relFileNode: normally, pass InvalidOid to get new storage.  May be
  *		nonzero to attach an existing valid build.
  * indexInfo: same info executor uses to insert into the index
@@ -708,6 +710,7 @@ UpdateIndexRelation(Oid indexoid,
  *		(only if INDEX_CREATE_ADD_CONSTRAINT is set)
  * allow_system_table_mods: allow table to be a system catalog
  * is_internal: if true, post creation hook for new index
+ * constraintId: if not NULL, receives OID of created constraint
  *
  * Returns the OID of the created index.
  */
@@ -716,6 +719,7 @@ index_create(Relation heapRelation,
 			 const char *indexRelationName,
 			 Oid indexRelationId,
 			 Oid parentIndexRelid,
+			 Oid parentConstraintId,
 			 Oid relFileNode,
 			 IndexInfo *indexInfo,
 			 List *indexColNames,
@@ -728,7 +732,8 @@ index_create(Relation heapRelation,
 			 bits16 flags,
 			 bits16 constr_flags,
 			 bool allow_system_table_mods,
-			 bool is_internal)
+			 bool is_internal,
+			 Oid *constraintId)
 {
 	Oid			heapRelationId = RelationGetRelid(heapRelation);
 	Relation	pg_class;
@@ -971,6 +976,7 @@ index_create(Relation heapRelation,
 		if ((flags & INDEX_CREATE_ADD_CONSTRAINT) != 0)
 		{
 			char		constraintType;
+			ObjectAddress localaddr;
 
 			if (isprimary)
 				constraintType = CONSTRAINT_PRIMARY;
@@ -984,14 +990,17 @@ index_create(Relation heapRelation,
 				constraintType = 0; /* keep compiler quiet */
 			}
 
-			index_constraint_create(heapRelation,
+			localaddr = index_constraint_create(heapRelation,
 									indexRelationId,
+									parentConstraintId,
 									indexInfo,
 									indexRelationName,
 									constraintType,
 									constr_flags,
 									allow_system_table_mods,
 									is_internal);
+			if (constraintId)
+				*constraintId = localaddr.objectId;
 		}
 		else
 		{
@@ -1160,6 +1169,8 @@ index_create(Relation heapRelation,
  *
  * heapRelation: table owning the index (must be suitably locked by caller)
  * indexRelationId: OID of the index
+ * parentConstraintId: if constraint is on a partition, the OID of the
+ *		constraint in the parent.
  * indexInfo: same info executor uses to insert into the index
  * constraintName: what it say (generally, should match name of index)
  * constraintType: one of CONSTRAINT_PRIMARY, CONSTRAINT_UNIQUE, or
@@ -1177,6 +1188,7 @@ index_create(Relation heapRelation,
 ObjectAddress
 index_constraint_create(Relation heapRelation,
 						Oid indexRelationId,
+						Oid parentConstraintId,
 						IndexInfo *indexInfo,
 						const char *constraintName,
 						char constraintType,
@@ -1274,6 +1286,18 @@ index_constraint_create(Relation heapRelation,
 	recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL);
 
 	/*
+	 * Also, if this is a constraint on a partition, mark it as depending
+	 * on the constraint in the parent.
+	 */
+	if (OidIsValid(parentConstraintId))
+	{
+		ObjectAddress	third;
+
+		ObjectAddressSet(third, ConstraintRelationId, parentConstraintId);
+		recordDependencyOn(&referenced, &third, DEPENDENCY_INTERNAL);
+	}
+
+	/*
 	 * If the constraint is deferrable, create the deferred uniqueness
 	 * checking trigger.  (The trigger will be given an internal dependency on
 	 * the constraint by CreateTrigger.)
diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c
index 09582a8d52..94e167d775 100644
--- a/src/backend/catalog/toasting.c
+++ b/src/backend/catalog/toasting.c
@@ -329,13 +329,13 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
 	coloptions[1] = 0;
 
 	index_create(toast_rel, toast_idxname, toastIndexOid, InvalidOid,
-				 InvalidOid,
+				 InvalidOid, InvalidOid,
 				 indexInfo,
 				 list_make2("chunk_id", "chunk_seq"),
 				 BTREE_AM_OID,
 				 rel->rd_rel->reltablespace,
 				 collationObjectId, classObjectId, coloptions, (Datum) 0,
-				 INDEX_CREATE_IS_PRIMARY, 0, true, true);
+				 INDEX_CREATE_IS_PRIMARY, 0, true, true, NULL);
 
 	heap_close(toast_rel, NoLock);
 
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index e925351056..e5b2c1d1e4 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -299,6 +299,8 @@ CheckIndexCompatible(Oid oldId,
  *		nonzero to specify a preselected OID for the index.
  * 'parentIndexId': the OID of the parent index; InvalidOid if not the child
  *		of a partitioned index.
+ * 'parentConstraintId': the OID of the parent constraint; InvalidOid if not
+ *		the child of a constraint (only used when recursing)
  * 'is_alter_table': this is due to an ALTER rather than a CREATE operation.
  * 'check_rights': check for CREATE rights in namespace and tablespace.  (This
  *		should be true except when ALTER is deleting/recreating an index.)
@@ -315,6 +317,7 @@ DefineIndex(Oid relationId,
 			IndexStmt *stmt,
 			Oid indexRelationId,
 			Oid parentIndexId,
+			Oid	parentConstraintId,
 			bool is_alter_table,
 			bool check_rights,
 			bool check_not_in_use,
@@ -329,6 +332,7 @@ DefineIndex(Oid relationId,
 	Oid			accessMethodId;
 	Oid			namespaceId;
 	Oid			tablespaceId;
+	Oid			createdConstraintId;
 	List	   *indexColNames;
 	Relation	rel;
 	Relation	indexRelation;
@@ -426,20 +430,11 @@ DefineIndex(Oid relationId,
 					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 					 errmsg("cannot create index on partitioned table \"%s\" concurrently",
 							RelationGetRelationName(rel))));
-		if (stmt->unique)
-			ereport(ERROR,
-					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-					 errmsg("cannot create unique index on partitioned table \"%s\"",
-							RelationGetRelationName(rel))));
 		if (stmt->excludeOpNames)
 			ereport(ERROR,
 					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 					 errmsg("cannot create exclusion constraints on partitioned table \"%s\"",
 							RelationGetRelationName(rel))));
-		if (stmt->primary || stmt->isconstraint)
-			ereport(ERROR,
-					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-					 errmsg("cannot create constraints on partitioned tables")));
 	}
 
 	/*
@@ -637,6 +632,68 @@ DefineIndex(Oid relationId,
 		index_check_primary_key(rel, indexInfo, is_alter_table);
 
 	/*
+	 * If this table is partitioned and we're creating a unique index or a
+	 * primary key, make sure that the indexed columns are part of the
+	 * partition key.  Otherwise it would be possible to violate uniqueness by
+	 * putting values that ought to be unique in different partitions.
+	 *
+	 * We could lift this limitation if we had global indexes, but those have
+	 * their own problems, so this is a useful feature combination.
+	 */
+	if (partitioned && (stmt->unique || stmt->primary))
+	{
+		PartitionKey key = rel->rd_partkey;
+		int			i;
+
+		/*
+		 * A partitioned table can have unique indexes, as long as all the
+		 * columns in the partition key appear in the unique key.  A
+		 * partition-local index can enforce global uniqueness iff the PK
+		 * value completely determines the partition that a row is in.
+		 *
+		 * Thus, verify that all the columns in the partition key appear
+		 * in the unique key definition.
+		 */
+		for (i = 0; i < key->partnatts; i++)
+		{
+			bool	found = false;
+			int		j;
+
+			/*
+			 * It may be possible to support UNIQUE constraints when partition
+			 * keys are expressions, but is it worth it?  Give up for now.
+			 */
+			if (key->partattrs[i] == 0)
+				ereport(ERROR,
+						(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+						 /* XXX reformulate error message? */
+						 errmsg("UNIQUE constraints are not supported on partitioned tables using expressions as partition keys")));
+
+			for (j = 0; j < indexInfo->ii_NumIndexAttrs; j++)
+			{
+				if (key->partattrs[i] == indexInfo->ii_KeyAttrNumbers[j])
+				{
+					found = true;
+					break;
+				}
+			}
+			if (!found)
+			{
+				Form_pg_attribute att;
+
+				att = TupleDescAttr(RelationGetDescr(rel), key->partattrs[i] - 1);
+				ereport(ERROR,
+						(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+						 errmsg("insufficient columns in UNIQUE constraint definition"),
+						 errdetail("UNIQUE constraint on table \"%s\" does not include column \"%s\" which is part of the partition key.",
+								RelationGetRelationName(rel),
+								NameStr(att->attname))));
+			}
+		}
+	}
+
+
+	/*
 	 * We disallow indexes on system columns other than OID.  They would not
 	 * necessarily get updated correctly, and they don't seem useful anyway.
 	 */
@@ -733,12 +790,14 @@ DefineIndex(Oid relationId,
 
 	indexRelationId =
 		index_create(rel, indexRelationName, indexRelationId, parentIndexId,
+					 parentConstraintId,
 					 stmt->oldNode, indexInfo, indexColNames,
 					 accessMethodId, tablespaceId,
 					 collationObjectId, classObjectId,
 					 coloptions, reloptions,
 					 flags, constr_flags,
-					 allowSystemTableMods, !check_rights);
+					 allowSystemTableMods, !check_rights,
+					 &createdConstraintId);
 
 	ObjectAddressSet(address, RelationRelationId, indexRelationId);
 
@@ -850,6 +909,7 @@ DefineIndex(Oid relationId,
 					DefineIndex(childRelid, childStmt,
 								InvalidOid,			/* no predefined OID */
 								indexRelationId,	/* this is our child */
+								createdConstraintId,
 								false, check_rights, check_not_in_use,
 								false, quiet);
 				}
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 47cb7c13ef..b69b9d3cde 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -938,17 +938,20 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
 			Relation	idxRel = index_open(lfirst_oid(cell), AccessShareLock);
 			AttrNumber *attmap;
 			IndexStmt  *idxstmt;
+			Oid			constraintOid;
 
 			attmap = convert_tuples_by_name_map(RelationGetDescr(rel),
 												RelationGetDescr(parent),
 												gettext_noop("could not convert row type"));
 			idxstmt =
 				generateClonedIndexStmt(NULL, RelationGetRelid(rel), idxRel,
-										attmap, RelationGetDescr(rel)->natts);
+										attmap, RelationGetDescr(rel)->natts,
+										&constraintOid);
 			DefineIndex(RelationGetRelid(rel),
 						idxstmt,
 						InvalidOid,
 						RelationGetRelid(idxRel),
+						constraintOid,
 						false, false, false, false, false);
 
 			index_close(idxRel, AccessShareLock);
@@ -6873,6 +6876,7 @@ ATExecAddIndex(AlteredTableInfo *tab, Relation rel,
 						  stmt,
 						  InvalidOid,	/* no predefined OID */
 						  InvalidOid,	/* no parent index */
+						  InvalidOid,	/* no parent constraint */
 						  true, /* is_alter_table */
 						  check_rights,
 						  false,	/* check_not_in_use - we did it already */
@@ -6965,6 +6969,7 @@ ATExecAddIndexConstraint(AlteredTableInfo *tab, Relation rel,
 
 	address = index_constraint_create(rel,
 									  index_oid,
+									  InvalidOid,
 									  indexInfo,
 									  constraintName,
 									  constraintType,
@@ -14166,12 +14171,15 @@ ATExecAttachPartition(List **wqueue, Relation rel, PartitionCmd *cmd)
 			if (!found)
 			{
 				IndexStmt  *stmt;
+				Oid			constraintOid;
 
 				stmt = generateClonedIndexStmt(NULL, RelationGetRelid(attachrel),
 											   idxRel, attmap,
-											   RelationGetDescr(rel)->natts);
+											   RelationGetDescr(rel)->natts,
+											   &constraintOid);
 				DefineIndex(RelationGetRelid(attachrel), stmt, InvalidOid,
 							RelationGetRelid(idxRel),
+							constraintOid,
 							false, false, false, false, false);
 			}
 
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 45f6ec2820..917b9bd636 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -704,12 +704,6 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
 							 errmsg("primary key constraints are not supported on foreign tables"),
 							 parser_errposition(cxt->pstate,
 												constraint->location)));
-				if (cxt->ispartitioned)
-					ereport(ERROR,
-							(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-							 errmsg("primary key constraints are not supported on partitioned tables"),
-							 parser_errposition(cxt->pstate,
-												constraint->location)));
 				/* FALL THRU */
 
 			case CONSTR_UNIQUE:
@@ -719,12 +713,6 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
 							 errmsg("unique constraints are not supported on foreign tables"),
 							 parser_errposition(cxt->pstate,
 												constraint->location)));
-				if (cxt->ispartitioned)
-					ereport(ERROR,
-							(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-							 errmsg("unique constraints are not supported on partitioned tables"),
-							 parser_errposition(cxt->pstate,
-												constraint->location)));
 				if (constraint->keys == NIL)
 					constraint->keys = list_make1(makeString(column->colname));
 				cxt->ixconstraints = lappend(cxt->ixconstraints, constraint);
@@ -821,12 +809,6 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
 						 errmsg("primary key constraints are not supported on foreign tables"),
 						 parser_errposition(cxt->pstate,
 											constraint->location)));
-			if (cxt->ispartitioned)
-				ereport(ERROR,
-						(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-						 errmsg("primary key constraints are not supported on partitioned tables"),
-						 parser_errposition(cxt->pstate,
-											constraint->location)));
 			cxt->ixconstraints = lappend(cxt->ixconstraints, constraint);
 			break;
 
@@ -837,12 +819,6 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
 						 errmsg("unique constraints are not supported on foreign tables"),
 						 parser_errposition(cxt->pstate,
 											constraint->location)));
-			if (cxt->ispartitioned)
-				ereport(ERROR,
-						(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-						 errmsg("unique constraints are not supported on partitioned tables"),
-						 parser_errposition(cxt->pstate,
-											constraint->location)));
 			cxt->ixconstraints = lappend(cxt->ixconstraints, constraint);
 			break;
 
@@ -1184,7 +1160,7 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
 			/* Build CREATE INDEX statement to recreate the parent_index */
 			index_stmt = generateClonedIndexStmt(cxt->relation, InvalidOid,
 												 parent_index,
-												 attmap, tupleDesc->natts);
+												 attmap, tupleDesc->natts, NULL);
 
 			/* Copy comment on index, if requested */
 			if (table_like_clause->options & CREATE_TABLE_LIKE_COMMENTS)
@@ -1267,7 +1243,7 @@ transformOfType(CreateStmtContext *cxt, TypeName *ofTypename)
  */
 IndexStmt *
 generateClonedIndexStmt(RangeVar *heapRel, Oid heapRelid, Relation source_idx,
-						const AttrNumber *attmap, int attmap_length)
+						const AttrNumber *attmap, int attmap_length, Oid *constraintOid)
 {
 	Oid			source_relid = RelationGetRelid(source_idx);
 	HeapTuple	ht_idxrel;
@@ -1365,6 +1341,9 @@ generateClonedIndexStmt(RangeVar *heapRel, Oid heapRelid, Relation source_idx,
 			HeapTuple	ht_constr;
 			Form_pg_constraint conrec;
 
+			if (constraintOid)
+				*constraintOid = constraintId;
+
 			ht_constr = SearchSysCache1(CONSTROID,
 										ObjectIdGetDatum(constraintId));
 			if (!HeapTupleIsValid(ht_constr))
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 8b5fd95a96..974b2f2c33 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1337,6 +1337,7 @@ ProcessUtilitySlow(ParseState *pstate,
 									stmt,
 									InvalidOid, /* no predefined OID */
 									InvalidOid, /* no parent index */
+									InvalidOid, /* no parent constraint */
 									false,	/* is_alter_table */
 									true,	/* check_rights */
 									true,	/* check_not_in_use */
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index 112d69debc..9a35ad6c0d 100644
--- a/src/include/catalog/index.h
+++ b/src/include/catalog/index.h
@@ -54,6 +54,7 @@ extern Oid index_create(Relation heapRelation,
 			 const char *indexRelationName,
 			 Oid indexRelationId,
 			 Oid parentIndexRelid,
+			 Oid parentConstraintId,
 			 Oid relFileNode,
 			 IndexInfo *indexInfo,
 			 List *indexColNames,
@@ -66,7 +67,8 @@ extern Oid index_create(Relation heapRelation,
 			 bits16 flags,
 			 bits16 constr_flags,
 			 bool allow_system_table_mods,
-			 bool is_internal);
+			 bool is_internal,
+			 Oid *constraintId);
 
 #define	INDEX_CONSTR_CREATE_MARK_AS_PRIMARY	(1 << 0)
 #define	INDEX_CONSTR_CREATE_DEFERRABLE		(1 << 1)
@@ -76,6 +78,7 @@ extern Oid index_create(Relation heapRelation,
 
 extern ObjectAddress index_constraint_create(Relation heapRelation,
 						Oid indexRelationId,
+						Oid parentConstraintId,
 						IndexInfo *indexInfo,
 						const char *constraintName,
 						char constraintType,
diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h
index 35f50c1175..1435a31940 100644
--- a/src/include/commands/defrem.h
+++ b/src/include/commands/defrem.h
@@ -26,6 +26,7 @@ extern ObjectAddress DefineIndex(Oid relationId,
 			IndexStmt *stmt,
 			Oid indexRelationId,
 			Oid parentIndexId,
+			Oid parentConstraintId,
 			bool is_alter_table,
 			bool check_rights,
 			bool check_not_in_use,
diff --git a/src/include/parser/parse_utilcmd.h b/src/include/parser/parse_utilcmd.h
index 599f0e8e29..c130b3a93a 100644
--- a/src/include/parser/parse_utilcmd.h
+++ b/src/include/parser/parse_utilcmd.h
@@ -29,6 +29,7 @@ extern PartitionBoundSpec *transformPartitionBound(ParseState *pstate, Relation
 						PartitionBoundSpec *spec);
 extern IndexStmt *generateClonedIndexStmt(RangeVar *heapRel, Oid heapOid,
 						Relation source_idx,
-						const AttrNumber *attmap, int attmap_length);
+						const AttrNumber *attmap, int attmap_length,
+						Oid *constraintOid);
 
 #endif							/* PARSE_UTILCMD_H */
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 517fb080bd..2caf930242 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -3290,14 +3290,6 @@ CREATE TABLE partitioned (
 	a int,
 	b int
 ) PARTITION BY RANGE (a, (a+b+1));
-ALTER TABLE partitioned ADD UNIQUE (a);
-ERROR:  unique constraints are not supported on partitioned tables
-LINE 1: ALTER TABLE partitioned ADD UNIQUE (a);
-                                    ^
-ALTER TABLE partitioned ADD PRIMARY KEY (a);
-ERROR:  primary key constraints are not supported on partitioned tables
-LINE 1: ALTER TABLE partitioned ADD PRIMARY KEY (a);
-                                    ^
 ALTER TABLE partitioned ADD FOREIGN KEY (a) REFERENCES blah;
 ERROR:  foreign key constraints are not supported on partitioned tables
 LINE 1: ALTER TABLE partitioned ADD FOREIGN KEY (a) REFERENCES blah;
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 8e745402ae..866cc99b9f 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -276,12 +276,6 @@ CREATE TABLE partitioned (
 ) PARTITION BY LIST (a1, a2);	-- fail
 ERROR:  cannot use "list" partition strategy with more than one column
 -- unsupported constraint type for partitioned tables
-CREATE TABLE partitioned (
-	a int PRIMARY KEY
-) PARTITION BY RANGE (a);
-ERROR:  primary key constraints are not supported on partitioned tables
-LINE 2:  a int PRIMARY KEY
-               ^
 CREATE TABLE pkrel (
 	a int PRIMARY KEY
 );
@@ -293,12 +287,6 @@ LINE 2:  a int REFERENCES pkrel(a)
                ^
 DROP TABLE pkrel;
 CREATE TABLE partitioned (
-	a int UNIQUE
-) PARTITION BY RANGE (a);
-ERROR:  unique constraints are not supported on partitioned tables
-LINE 2:  a int UNIQUE
-               ^
-CREATE TABLE partitioned (
 	a int,
 	EXCLUDE USING gist (a WITH &&)
 ) PARTITION BY RANGE (a);
diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out
index 1b391c2017..c702cb02aa 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -25,8 +25,6 @@ drop table idxpart;
 -- Some unsupported features
 create table idxpart (a int, b int, c text) partition by range (a);
 create table idxpart1 partition of idxpart for values from (0) to (10);
-create unique index on idxpart (a);
-ERROR:  cannot create unique index on partitioned table "idxpart"
 create index concurrently on idxpart (a);
 ERROR:  cannot create index on partitioned table "idxpart" concurrently
 drop table idxpart;
@@ -404,6 +402,146 @@ select attrelid::regclass, attname, attnum from pg_attribute
 (7 rows)
 
 drop table idxpart;
+--
+-- Constraint-related indexes
+--
+-- Verify that it works to add primary key / unique to partitioned tables
+create table idxpart (a int primary key, b int) partition by range (a);
+\d idxpart
+              Table "public.idxpart"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           | not null | 
+ b      | integer |           |          | 
+Partition key: RANGE (a)
+Indexes:
+    "idxpart_pkey" PRIMARY KEY, btree (a)
+Number of partitions: 0
+
+drop table idxpart;
+-- but not if you fail to use the full partition key
+create table idxpart (a int unique, b int) partition by range (a, b);
+ERROR:  insufficient columns in UNIQUE constraint definition
+DETAIL:  UNIQUE constraint on table "idxpart" does not include column "b" which is part of the partition key.
+create table idxpart (a int, b int unique) partition by range (a, b);
+ERROR:  insufficient columns in UNIQUE constraint definition
+DETAIL:  UNIQUE constraint on table "idxpart" does not include column "a" which is part of the partition key.
+create table idxpart (a int primary key, b int) partition by range (b, a);
+ERROR:  insufficient columns in UNIQUE constraint definition
+DETAIL:  UNIQUE constraint on table "idxpart" does not include column "b" which is part of the partition key.
+create table idxpart (a int, b int primary key) partition by range (b, a);
+ERROR:  insufficient columns in UNIQUE constraint definition
+DETAIL:  UNIQUE constraint on table "idxpart" does not include column "a" which is part of the partition key.
+-- OK if you use them in some other order
+create table idxpart (a int, b int, c text, primary key  (a, b, c)) partition by range (b, c, a);
+drop table idxpart;
+create table idxpart (a int primary key, b int) partition by range ((b + a));
+ERROR:  UNIQUE constraints are not supported on partitioned tables using expressions as partition keys
+-- not other types of index-based constraints
+create table idxpart (a int, exclude (a with = )) partition by range (a);
+ERROR:  exclusion constraints are not supported on partitioned tables
+LINE 1: create table idxpart (a int, exclude (a with = )) partition ...
+                                     ^
+-- It works to add primary keys after the partitioned table is created
+create table idxpart (a int, b int, c text) partition by range (a, b);
+alter table idxpart add primary key (a);	-- not an incomplete one tho
+ERROR:  insufficient columns in UNIQUE constraint definition
+DETAIL:  UNIQUE constraint on table "idxpart" does not include column "b" which is part of the partition key.
+alter table idxpart add primary key (a, b);
+\d idxpart
+              Table "public.idxpart"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           | not null | 
+ b      | integer |           | not null | 
+ c      | text    |           |          | 
+Partition key: RANGE (a, b)
+Indexes:
+    "idxpart_pkey" PRIMARY KEY, btree (a, b)
+Number of partitions: 0
+
+create table idxpart1 partition of idxpart for values from (0, 0) to (1000, 1000);
+\d idxpart1
+              Table "public.idxpart1"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           | not null | 
+ b      | integer |           | not null | 
+ c      | text    |           |          | 
+Partition of: idxpart FOR VALUES FROM (0, 0) TO (1000, 1000)
+Indexes:
+    "idxpart1_pkey" PRIMARY KEY, btree (a, b)
+
+drop table idxpart;
+-- It works to add unique constraints after the partitioned table is created
+create table idxpart (a int, b int) partition by range (a, b);
+alter table idxpart add unique (a);			-- ... nope
+ERROR:  insufficient columns in UNIQUE constraint definition
+DETAIL:  UNIQUE constraint on table "idxpart" does not include column "b" which is part of the partition key.
+alter table idxpart add unique (b, a);
+\d idxpart
+              Table "public.idxpart"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           |          | 
+ b      | integer |           |          | 
+Partition key: RANGE (a, b)
+Indexes:
+    "idxpart_b_a_key" UNIQUE CONSTRAINT, btree (b, a)
+Number of partitions: 0
+
+drop table idxpart;
+-- Exclusion constraints cannot be added
+create table idxpart (a int, b int) partition by range (a);
+alter table idxpart add exclude (a with =);
+ERROR:  exclusion constraints are not supported on partitioned tables
+LINE 1: alter table idxpart add exclude (a with =);
+                                ^
+drop table idxpart;
+-- When (sub)partitions are created, they also contain the constraint
+create table idxpart (a int, b int, primary key (a, b)) partition by range (a, b);
+create table idxpart1 partition of idxpart for values from (1, 1) to (10, 10);
+create table idxpart2 partition of idxpart for values from (10, 10) to (20, 20)
+  partition by range (b);
+create table idxpart21 partition of idxpart2 for values from (10) to (15);
+create table idxpart22 partition of idxpart2 for values from (15) to (20);
+create table idxpart3 (b int not null, a int not null);
+alter table idxpart attach partition idxpart3 for values from (20, 20) to (30, 30);
+select conname, contype, conrelid::regclass, conindid::regclass, conkey
+  from pg_constraint where conrelid::regclass::text like 'idxpart%'
+  order by conname;
+    conname     | contype | conrelid  |    conindid    | conkey 
+----------------+---------+-----------+----------------+--------
+ idxpart1_pkey  | p       | idxpart1  | idxpart1_pkey  | {1,2}
+ idxpart21_pkey | p       | idxpart21 | idxpart21_pkey | {1,2}
+ idxpart22_pkey | p       | idxpart22 | idxpart22_pkey | {1,2}
+ idxpart2_pkey  | p       | idxpart2  | idxpart2_pkey  | {1,2}
+ idxpart3_pkey  | p       | idxpart3  | idxpart3_pkey  | {2,1}
+ idxpart_pkey   | p       | idxpart   | idxpart_pkey   | {1,2}
+(6 rows)
+
+drop table idxpart;
+-- multi-layer partitioning honors the prohibition.  So this fails:
+create table idxpart (a int, b int, primary key (a)) partition by range (a);
+create table idxpart2 partition of idxpart for values from (0) to (1000) partition by range (b);
+ERROR:  insufficient columns in UNIQUE constraint definition
+DETAIL:  UNIQUE constraint on table "idxpart2" does not include column "b" which is part of the partition key.
+drop table idxpart;
+-- but this works:
+create table idxpart (a int, b int, primary key (a, b)) partition by range (a);
+create table idxpart2 partition of idxpart for values from (0) to (1000) partition by range (b);
+create table idxpart21 partition of idxpart2 for values from (0) to (1000);
+select conname, contype, conrelid::regclass, conindid::regclass, conkey
+  from pg_constraint where conrelid::regclass::text like 'idxpart%'
+  order by conname;
+    conname     | contype | conrelid  |    conindid    | conkey 
+----------------+---------+-----------+----------------+--------
+ idxpart21_pkey | p       | idxpart21 | idxpart21_pkey | {1,2}
+ idxpart2_pkey  | p       | idxpart2  | idxpart2_pkey  | {1,2}
+ idxpart_pkey   | p       | idxpart   | idxpart_pkey   | {1,2}
+(3 rows)
+
+drop table idxpart;
 -- intentionally leave some objects around
 create table idxpart (a int) partition by range (a);
 create table idxpart1 partition of idxpart for values from (0) to (100);
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index af25ee9e77..ed0bb7845b 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -2016,8 +2016,6 @@ CREATE TABLE partitioned (
 	a int,
 	b int
 ) PARTITION BY RANGE (a, (a+b+1));
-ALTER TABLE partitioned ADD UNIQUE (a);
-ALTER TABLE partitioned ADD PRIMARY KEY (a);
 ALTER TABLE partitioned ADD FOREIGN KEY (a) REFERENCES blah;
 ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH &&);
 
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index 8f9991ef18..fefccf21a2 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -294,10 +294,6 @@ CREATE TABLE partitioned (
 ) PARTITION BY LIST (a1, a2);	-- fail
 
 -- unsupported constraint type for partitioned tables
-CREATE TABLE partitioned (
-	a int PRIMARY KEY
-) PARTITION BY RANGE (a);
-
 CREATE TABLE pkrel (
 	a int PRIMARY KEY
 );
@@ -307,10 +303,6 @@ CREATE TABLE partitioned (
 DROP TABLE pkrel;
 
 CREATE TABLE partitioned (
-	a int UNIQUE
-) PARTITION BY RANGE (a);
-
-CREATE TABLE partitioned (
 	a int,
 	EXCLUDE USING gist (a WITH &&)
 ) PARTITION BY RANGE (a);
diff --git a/src/test/regress/sql/indexing.sql b/src/test/regress/sql/indexing.sql
index f764b29b39..e76c524f81 100644
--- a/src/test/regress/sql/indexing.sql
+++ b/src/test/regress/sql/indexing.sql
@@ -14,7 +14,6 @@ drop table idxpart;
 -- Some unsupported features
 create table idxpart (a int, b int, c text) partition by range (a);
 create table idxpart1 partition of idxpart for values from (0) to (10);
-create unique index on idxpart (a);
 create index concurrently on idxpart (a);
 drop table idxpart;
 
@@ -172,6 +171,78 @@ select attrelid::regclass, attname, attnum from pg_attribute
   order by attrelid::regclass, attnum;
 drop table idxpart;
 
+--
+-- Constraint-related indexes
+--
+
+-- Verify that it works to add primary key / unique to partitioned tables
+create table idxpart (a int primary key, b int) partition by range (a);
+\d idxpart
+drop table idxpart;
+
+-- but not if you fail to use the full partition key
+create table idxpart (a int unique, b int) partition by range (a, b);
+create table idxpart (a int, b int unique) partition by range (a, b);
+create table idxpart (a int primary key, b int) partition by range (b, a);
+create table idxpart (a int, b int primary key) partition by range (b, a);
+
+-- OK if you use them in some other order
+create table idxpart (a int, b int, c text, primary key  (a, b, c)) partition by range (b, c, a);
+drop table idxpart;
+
+create table idxpart (a int primary key, b int) partition by range ((b + a));
+-- not other types of index-based constraints
+create table idxpart (a int, exclude (a with = )) partition by range (a);
+
+-- It works to add primary keys after the partitioned table is created
+create table idxpart (a int, b int, c text) partition by range (a, b);
+alter table idxpart add primary key (a);	-- not an incomplete one tho
+alter table idxpart add primary key (a, b);
+\d idxpart
+create table idxpart1 partition of idxpart for values from (0, 0) to (1000, 1000);
+\d idxpart1
+drop table idxpart;
+
+-- It works to add unique constraints after the partitioned table is created
+create table idxpart (a int, b int) partition by range (a, b);
+alter table idxpart add unique (a);			-- ... nope
+alter table idxpart add unique (b, a);
+\d idxpart
+drop table idxpart;
+
+-- Exclusion constraints cannot be added
+create table idxpart (a int, b int) partition by range (a);
+alter table idxpart add exclude (a with =);
+drop table idxpart;
+
+-- When (sub)partitions are created, they also contain the constraint
+create table idxpart (a int, b int, primary key (a, b)) partition by range (a, b);
+create table idxpart1 partition of idxpart for values from (1, 1) to (10, 10);
+create table idxpart2 partition of idxpart for values from (10, 10) to (20, 20)
+  partition by range (b);
+create table idxpart21 partition of idxpart2 for values from (10) to (15);
+create table idxpart22 partition of idxpart2 for values from (15) to (20);
+create table idxpart3 (b int not null, a int not null);
+alter table idxpart attach partition idxpart3 for values from (20, 20) to (30, 30);
+select conname, contype, conrelid::regclass, conindid::regclass, conkey
+  from pg_constraint where conrelid::regclass::text like 'idxpart%'
+  order by conname;
+drop table idxpart;
+
+-- multi-layer partitioning honors the prohibition.  So this fails:
+create table idxpart (a int, b int, primary key (a)) partition by range (a);
+create table idxpart2 partition of idxpart for values from (0) to (1000) partition by range (b);
+drop table idxpart;
+
+-- but this works:
+create table idxpart (a int, b int, primary key (a, b)) partition by range (a);
+create table idxpart2 partition of idxpart for values from (0) to (1000) partition by range (b);
+create table idxpart21 partition of idxpart2 for values from (0) to (1000);
+select conname, contype, conrelid::regclass, conindid::regclass, conkey
+  from pg_constraint where conrelid::regclass::text like 'idxpart%'
+  order by conname;
+drop table idxpart;
+
 -- intentionally leave some objects around
 create table idxpart (a int) partition by range (a);
 create table idxpart1 partition of idxpart for values from (0) to (100);
-- 
2.11.0