Allow some exclusion constraints on partitions

Previously we only allowed unique B-tree constraints on partitions
(and only if the constraint included all the partition keys).  But we
could allow exclusion constraints with the same restriction.  We also
require that those columns be compared for equality, not something
like &&.

Author: Paul A. Jungwirth <pj@illuminatedcomputing.com>
Reviewed-by: Ronan Dunklau <ronan.dunklau@aiven.io>
Reviewed-by: Peter Eisentraut <peter@eisentraut.org>
Discussion: https://www.postgresql.org/message-id/flat/ec8b1d9b-502e-d1f8-e909-1bf9dffe6fa5@illuminatedcomputing.com

2 files changed, 38 insertions, 27 deletions

diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 02250ae74be..baf3e6e57a5 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -712,11 +712,6 @@ DefineIndex(Oid relationId,
 					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 					 errmsg("cannot create index on partitioned table \"%s\" concurrently",
 							RelationGetRelationName(rel))));
-		if (stmt->excludeOpNames)
-			ereport(ERROR,
-					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-					 errmsg("cannot create exclusion constraints on partitioned table \"%s\"",
-							RelationGetRelationName(rel))));
 	}
 
 	/*
@@ -923,15 +918,16 @@ DefineIndex(Oid relationId,
 		index_check_primary_key(rel, indexInfo, is_alter_table, stmt);
 
 	/*
-	 * If this table is partitioned and we're creating a unique index or a
-	 * primary key, make sure that the partition key is a subset of the
-	 * index's columns.  Otherwise it would be possible to violate uniqueness
-	 * by putting values that ought to be unique in different partitions.
+	 * If this table is partitioned and we're creating a unique index, primary
+	 * key, or exclusion constraint, make sure that the partition key is a
+	 * subset of the index's columns.  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))
+	if (partitioned && (stmt->unique || stmt->excludeOpNames))
 	{
 		PartitionKey key = RelationGetPartitionKey(rel);
 		const char *constraint_type;
@@ -941,7 +937,7 @@ DefineIndex(Oid relationId,
 			constraint_type = "PRIMARY KEY";
 		else if (stmt->unique)
 			constraint_type = "UNIQUE";
-		else if (stmt->excludeOpNames != NIL)
+		else if (stmt->excludeOpNames)
 			constraint_type = "EXCLUDE";
 		else
 		{
@@ -984,11 +980,11 @@ DefineIndex(Oid relationId,
 			 * We'll need to be able to identify the equality operators
 			 * associated with index columns, too.  We know what to do with
 			 * btree opclasses; if there are ever any other index types that
-			 * support unique indexes, this logic will need extension.
+			 * support unique indexes, this logic will need extension. But if
+			 * we have an exclusion constraint, it already knows the
+			 * operators, so we don't have to infer them.
 			 */
-			if (accessMethodId == BTREE_AM_OID)
-				eq_strategy = BTEqualStrategyNumber;
-			else
+			if (stmt->unique && accessMethodId != BTREE_AM_OID)
 				ereport(ERROR,
 						(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 						 errmsg("cannot match partition key to an index using access method \"%s\"",
@@ -1019,17 +1015,38 @@ DefineIndex(Oid relationId,
 															&idx_opfamily,
 															&idx_opcintype))
 					{
-						Oid			idx_eqop;
+						Oid			idx_eqop = InvalidOid;
+
+						if (stmt->unique)
+							idx_eqop = get_opfamily_member(idx_opfamily,
+														   idx_opcintype,
+														   idx_opcintype,
+														   BTEqualStrategyNumber);
+						else if (stmt->excludeOpNames)
+							idx_eqop = indexInfo->ii_ExclusionOps[j];
+						Assert(idx_eqop);
 
-						idx_eqop = get_opfamily_member(idx_opfamily,
-													   idx_opcintype,
-													   idx_opcintype,
-													   eq_strategy);
 						if (ptkey_eqop == idx_eqop)
 						{
 							found = true;
 							break;
 						}
+						else if (stmt->excludeOpNames)
+						{
+							/*
+							 * We found a match, but it's not an equality
+							 * operator. Instead of failing below with an
+							 * error message about a missing column, fail now
+							 * and explain that the operator is wrong.
+							 */
+							Form_pg_attribute att = TupleDescAttr(RelationGetDescr(rel), key->partattrs[i] - 1);
+
+							ereport(ERROR,
+									(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+									 errmsg("cannot match partition key to index on column \"%s\" using non-equal operator \"%s\"",
+											NameStr(att->attname),
+											get_opname(indexInfo->ii_ExclusionOps[j]))));
+						}
 					}
 				}
 			}
@@ -1101,7 +1118,7 @@ DefineIndex(Oid relationId,
 			constraint_type = "PRIMARY KEY";
 		else if (stmt->unique)
 			constraint_type = "UNIQUE";
-		else if (stmt->excludeOpNames != NIL)
+		else if (stmt->excludeOpNames)
 			constraint_type = "EXCLUDE";
 		else
 		{
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index d67580fc77a..e48e9e99d36 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -900,12 +900,6 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
 						 errmsg("exclusion constraints are not supported on foreign tables"),
 						 parser_errposition(cxt->pstate,
 											constraint->location)));
-			if (cxt->ispartitioned)
-				ereport(ERROR,
-						(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-						 errmsg("exclusion constraints are not supported on partitioned tables"),
-						 parser_errposition(cxt->pstate,
-											constraint->location)));
 			cxt->ixconstraints = lappend(cxt->ixconstraints, constraint);
 			break;