multi-column range partition constraint

From d31d6d885a600cafc74b3068388905ae9e635ab0 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Tue, 2 May 2017 11:03:54 +0900
Subject: [PATCH] Emit "correct" range partition constraint expression

Currently emitted expression does not always describe the constraint
correctly, especially in the case of multi-column range key.

Code surrounding get_qual_for_*() has been rearranged a little to
move common code to a couple of new functions.

Original issue reported by Olaf Gawenda (olaf.gw@googlemail.com)
---
 src/backend/catalog/partition.c       | 620 ++++++++++++++++++++++------------
 src/include/nodes/pg_list.h           |  10 +
 src/test/regress/expected/inherit.out |  90 +++++
 src/test/regress/expected/insert.out  |  59 ++++
 src/test/regress/sql/inherit.sql      |  18 +
 src/test/regress/sql/insert.sql       |  43 +++
 6 files changed, 619 insertions(+), 221 deletions(-)

diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c
index e0d2665a91..25f51e3278 100644
--- a/src/backend/catalog/partition.c
+++ b/src/backend/catalog/partition.c
@@ -118,10 +118,19 @@ static int32 qsort_partition_list_value_cmp(const void *a, const void *b,
 static int32 qsort_partition_rbound_cmp(const void *a, const void *b,
 						   void *arg);
 
-static List *get_qual_for_list(PartitionKey key, PartitionBoundSpec *spec);
-static List *get_qual_for_range(PartitionKey key, PartitionBoundSpec *spec);
 static Oid get_partition_operator(PartitionKey key, int col,
 					   StrategyNumber strategy, bool *need_relabel);
+static Expr* make_partition_op_expr(PartitionKey key, int keynum,
+					   uint16 strategy, Expr *arg1, Expr *arg2);
+static void get_range_key_properties(PartitionKey key, int keynum,
+						 PartitionRangeDatum *ldatum,
+						 PartitionRangeDatum *udatum,
+						 ListCell **partexprs_item,
+						 Expr **keyCol,
+						 Const **lower_val, Const **upper_val,
+						 NullTest **nulltest);
+static List *get_qual_for_list(PartitionKey key, PartitionBoundSpec *spec);
+static List *get_qual_for_range(PartitionKey key, PartitionBoundSpec *spec);
 static List *generate_partition_qual(Relation rel);
 
 static PartitionRangeBound *make_one_range_bound(PartitionKey key, int index,
@@ -1146,6 +1155,123 @@ RelationGetPartitionDispatchInfo(Relation rel, int lockmode,
 /* Module-local functions */
 
 /*
+ * get_partition_operator
+ *
+ * Return oid of the operator of given strategy for a given partition key
+ * column.
+ */
+static Oid
+get_partition_operator(PartitionKey key, int col, StrategyNumber strategy,
+					   bool *need_relabel)
+{
+	Oid			operoid;
+
+	/*
+	 * First check if there exists an operator of the given strategy, with
+	 * this column's type as both its lefttype and righttype, in the
+	 * partitioning operator family specified for the column.
+	 */
+	operoid = get_opfamily_member(key->partopfamily[col],
+								  key->parttypid[col],
+								  key->parttypid[col],
+								  strategy);
+
+	/*
+	 * If one doesn't exist, we must resort to using an operator in the same
+	 * opreator family but with the operator class declared input type.  It is
+	 * OK to do so, because the column's type is known to be binary-coercible
+	 * with the operator class input type (otherwise, the operator class in
+	 * question would not have been accepted as the partitioning operator
+	 * class).  We must however inform the caller to wrap the non-Const
+	 * expression with a RelabelType node to denote the implicit coercion. It
+	 * ensures that the resulting expression structurally matches similarly
+	 * processed expressions within the optimizer.
+	 */
+	if (!OidIsValid(operoid))
+	{
+		operoid = get_opfamily_member(key->partopfamily[col],
+									  key->partopcintype[col],
+									  key->partopcintype[col],
+									  strategy);
+		*need_relabel = true;
+	}
+	else
+		*need_relabel = false;
+
+	if (!OidIsValid(operoid))
+		elog(ERROR, "could not find operator for partitioning");
+
+	return operoid;
+}
+
+/*
+ * make_partition_op_expr
+ *		Returns an Expr for the given partition key column with arg1 and
+ *		arg2 as its leftop and rightop, respectively
+ */
+static Expr*
+make_partition_op_expr(PartitionKey key, int keynum,
+					   uint16 strategy, Expr *arg1, Expr *arg2)
+{
+	Oid		operoid;
+	bool	need_relabel = false;
+	Expr   *result = NULL;
+
+	/* Get the correct btree operator for this partitioning column */
+	operoid = get_partition_operator(key, keynum, strategy, &need_relabel);
+
+	/*
+	 * Chosen operator may be such that the non-Const operand needs to
+	 * be coerced, so apply the same; see the comment in
+	 * get_partition_operator().
+	 */
+	if (!IsA(arg1, Const) &&
+		(need_relabel ||
+		 key->partcollation[keynum] != key->parttypcoll[keynum]))
+		arg1 = (Expr *) makeRelabelType(arg1,
+										key->partopcintype[keynum],
+										-1,
+										key->partcollation[keynum],
+										COERCE_EXPLICIT_CAST);
+
+	/* Generate the actual expression */
+	switch (key->strategy)
+	{
+		case PARTITION_STRATEGY_LIST:
+		{
+			ScalarArrayOpExpr *saopexpr;
+
+			/* Build leftop = ANY (rightop) */
+			saopexpr = makeNode(ScalarArrayOpExpr);
+			saopexpr->opno = operoid;
+			saopexpr->opfuncid = get_opcode(operoid);
+			saopexpr->useOr = true;
+			saopexpr->inputcollid = key->partcollation[0];
+			saopexpr->args = list_make2(arg1, arg2);
+			saopexpr->location = -1;
+
+			result = (Expr *) saopexpr;
+			break;
+		}
+
+		case PARTITION_STRATEGY_RANGE:
+			result = make_opclause(operoid,
+								   BOOLOID,
+								   false,
+								   arg1, arg2,
+								   InvalidOid,
+								   key->partcollation[keynum]);
+			break;
+
+		default:
+			elog(ERROR, "invalid partitioning strategy");
+			break;
+	}
+
+	return result;
+}
+
+/*
  * get_qual_for_list
  *
  * Returns a list of expressions to use as a list partition's constraint.
@@ -1155,14 +1281,12 @@ get_qual_for_list(PartitionKey key, PartitionBoundSpec *spec)
 {
 	List	   *result;
 	ArrayExpr  *arr;
-	ScalarArrayOpExpr *opexpr;
+	Expr	   *opexpr;
 	ListCell   *cell,
 			   *prev,
 			   *next;
 	Expr	   *keyCol;
-	Oid			operoid;
-	bool		need_relabel,
-				list_has_null = false;
+	bool		list_has_null = false;
 	NullTest   *nulltest1 = NULL,
 			   *nulltest2 = NULL;
 
@@ -1233,24 +1357,9 @@ get_qual_for_list(PartitionKey key, PartitionBoundSpec *spec)
 	arr->multidims = false;
 	arr->location = -1;
 
-	/* Get the correct btree equality operator */
-	operoid = get_partition_operator(key, 0, BTEqualStrategyNumber,
-									 &need_relabel);
-	if (need_relabel || key->partcollation[0] != key->parttypcoll[0])
-		keyCol = (Expr *) makeRelabelType(keyCol,
-										  key->partopcintype[0],
-										  -1,
-										  key->partcollation[0],
-										  COERCE_EXPLICIT_CAST);
-
-	/* Build leftop = ANY (rightop) */
-	opexpr = makeNode(ScalarArrayOpExpr);
-	opexpr->opno = operoid;
-	opexpr->opfuncid = get_opcode(operoid);
-	opexpr->useOr = true;
-	opexpr->inputcollid = key->partcollation[0];
-	opexpr->args = list_make2(keyCol, arr);
-	opexpr->location = -1;
+	/* Generate the main expression, i.e., keyCol = ANY (arr) */
+	opexpr = make_partition_op_expr(key, 0, BTEqualStrategyNumber,
+									keyCol, (Expr *) arr);
 
 	if (nulltest1)
 		result = list_make2(nulltest1, opexpr);
@@ -1268,9 +1377,95 @@ get_qual_for_list(PartitionKey key, PartitionBoundSpec *spec)
 }
 
 /*
+ * get_range_key_properties
+ *		Returns range partition key information for a given column
+ *
+ * On return, *lower_val and *upper_val contain either a valid Const
+ * expression or NULL.  *nulltest contains a NullTest expression, or
+ * NULL.
+ */
+static void
+get_range_key_properties(PartitionKey key, int keynum,
+						 PartitionRangeDatum *ldatum,
+						 PartitionRangeDatum *udatum,
+						 ListCell **partexprs_item,
+						 Expr **keyCol,
+						 Const **lower_val, Const **upper_val,
+						 NullTest **nulltest)
+{
+	/* Partition key expression for this column */
+	if (key->partattrs[keynum] != 0)
+	{
+		*keyCol = (Expr *) makeVar(1,
+								  key->partattrs[keynum],
+								  key->parttypid[keynum],
+								  key->parttypmod[keynum],
+								  key->parttypcoll[keynum],
+								  0);
+	}
+	else
+	{
+		*keyCol = copyObject(lfirst(*partexprs_item));
+		*partexprs_item = lnext(*partexprs_item);
+	}
+
+	/*
+	 * A range-partitioned table does not allow partition keys to be null.
+	 * For simple columns, their NOT NULL constraint suffices for the
+	 * enforcement of non-nullability.  But for the expression keys, which
+	 * are still nullable, we must emit a IS NOT NULL expression.
+	 */
+	if (!IsA(*keyCol, Var))
+	{
+		*nulltest = makeNode(NullTest);
+		(*nulltest)->arg = *keyCol;
+		(*nulltest)->nulltesttype = IS_NOT_NULL;
+		(*nulltest)->argisrow = false;
+		(*nulltest)->location = -1;
+	}
+	else
+		*nulltest = NULL;
+
+	if (!ldatum->infinite)
+		*lower_val = (Const *) ldatum->value;
+	else
+		*lower_val = NULL;
+
+	if (!udatum->infinite)
+		*upper_val = (Const *) udatum->value;
+	else
+		*upper_val = NULL;
+}
+
+/*
  * get_qual_for_range
  *
- * Get a list of OpExpr's to use as a range partition's constraint.
+ * Get a list of expressions to use as a range partition's constraint.
+ * Expressions in the list are implicitly ANDed.
+ *
+ * For a multi-column range partition key, say (a, b, c), with (al, bl, cl)
+ * as the lower bound tuple and (au, bu, cu) as the upper bound tuple, we
+ * generate an expression tree of the following form:
+ *
+ *	(a > al OR (a = al AND b > bl) OR (a = al AND b = bl AND c >= cl))
+ *		AND
+ *	(a < au OR (a = au AND b < bu) OR (a = au AND b = bu AND c < cu))
+ *
+ * If b were an expression key instead of a simple column, we also append
+ * (b IS NOT NULL) to the AND's argument list.
+ *
+ * It is often the case that a prefix of lower and upper bound tuples contains
+ * the same values, for example, (al = au), in which case, we will emit an
+ * expression tree of the following form:
+ *
+ *	(a = al)
+ *		AND
+ *	(b > bl OR (b = bl AND c >= cl))
+ *		AND
+ *	(b < bu) OR (b = bu AND c < cu))
+ *
+ * In most common cases with only one partition column, say a, the following
+ * expression tree will be generated: a >= al AND a < au
  */
 static List *
 get_qual_for_range(PartitionKey key, PartitionBoundSpec *spec)
@@ -1278,239 +1473,222 @@ get_qual_for_range(PartitionKey key, PartitionBoundSpec *spec)
 	List	   *result = NIL;
 	ListCell   *cell1,
 			   *cell2,
-			   *partexprs_item;
-	int			i;
+			   *partexprs_item,
+				partexprs_item_saved;
+	int			i,
+				j;
+	PartitionRangeDatum *ldatum,
+			   *udatum;
+	Expr	   *keyCol;
+	Const	   *lower_val,
+			   *upper_val;
+	NullTest   *nulltest;
+	List	   *lower_or_arms,
+			   *upper_or_arms;
+	int			maxnum_or_arms,
+				num_or_arms;
+	ListCell   *lower_or_start_datum,
+			   *upper_or_start_datum;
+
+	lower_or_start_datum = list_head(spec->lowerdatums);
+	upper_or_start_datum = list_head(spec->upperdatums);
+	maxnum_or_arms = key->partnatts;
 
 	/*
-	 * Iterate over columns of the key, emitting an OpExpr for each using the
-	 * corresponding lower and upper datums as constant operands.
+	 * Iterate over the key columns and check if the corresponding lower and
+	 * upper datums are equal using the btree equality operator for the
+	 * column's type.  If equal, we emit single keyCol = common_value
+	 * expression.  Starting from the first column for which the corresponding
+	 * lower and upper bound datums are not equal, we generate OR expressions
+	 * as shown in the function's header comment.
 	 */
 	i = 0;
 	partexprs_item = list_head(key->partexprs);
 	forboth(cell1, spec->lowerdatums, cell2, spec->upperdatums)
 	{
-		PartitionRangeDatum *ldatum = lfirst(cell1),
-				   *udatum = lfirst(cell2);
-		Expr	   *keyCol;
-		Const	   *lower_val = NULL,
-				   *upper_val = NULL;
 		EState	   *estate;
 		MemoryContext oldcxt;
 		Expr	   *test_expr;
 		ExprState  *test_exprstate;
 		Datum		test_result;
 		bool		isNull;
-		bool		need_relabel = false;
-		Oid			operoid;
-		NullTest   *nulltest;
 
-		/* Left operand */
-		if (key->partattrs[i] != 0)
-		{
-			keyCol = (Expr *) makeVar(1,
-									  key->partattrs[i],
-									  key->parttypid[i],
-									  key->parttypmod[i],
-									  key->parttypcoll[i],
-									  0);
-		}
-		else
-		{
-			keyCol = copyObject(lfirst(partexprs_item));
-			partexprs_item = lnext(partexprs_item);
-		}
+		ldatum = lfirst(cell1);
+		udatum = lfirst(cell2);
 
 		/*
-		 * Emit a IS NOT NULL expression for non-Var keys, because whereas
-		 * simple attributes are covered by NOT NULL constraints, expression
-		 * keys are still nullable which is not acceptable in case of range
-		 * partitioning.
+		 * Since get_range_key_properties() modifies partexprs_item, and we
+		 * might need to start over from the previous expression in the
+		 * later part of this functiom, save away the current value.
 		 */
-		if (!IsA(keyCol, Var))
-		{
-			nulltest = makeNode(NullTest);
-			nulltest->arg = keyCol;
-			nulltest->nulltesttype = IS_NOT_NULL;
-			nulltest->argisrow = false;
-			nulltest->location = -1;
+		if (partexprs_item)
+			partexprs_item_saved = *partexprs_item;
+		get_range_key_properties(key, i, ldatum, udatum,
+								 &partexprs_item,
+								 &keyCol,
+								 &lower_val, &upper_val,
+								 &nulltest);
+
+		if (nulltest)
 			result = lappend(result, nulltest);
-		}
 
 		/*
-		 * Stop at this column if either of lower or upper datum is infinite,
-		 * but do emit an OpExpr for the non-infinite datum.
+		 * If either or both of lower_val and upper_val is NULL, they are
+		 * unequal.
 		 */
-		if (!ldatum->infinite)
-			lower_val = (Const *) ldatum->value;
-		if (!udatum->infinite)
-			upper_val = (Const *) udatum->value;
+		if (!lower_val || !upper_val)
+			break;
+
+		/* Create the test expression */
+		estate = CreateExecutorState();
+		oldcxt = MemoryContextSwitchTo(estate->es_query_cxt);
+		test_expr = make_partition_op_expr(key, i, BTEqualStrategyNumber,
+										   (Expr *) lower_val,
+										   (Expr *) upper_val);
+		fix_opfuncids((Node *) test_expr);
+		test_exprstate = ExecInitExpr(test_expr, NULL);
+		test_result = ExecEvalExprSwitchContext(test_exprstate,
+										  GetPerTupleExprContext(estate),
+												&isNull);
+		MemoryContextSwitchTo(oldcxt);
+		FreeExecutorState(estate);
+
+		/* If not equal, go generate the OR expressions */
+		if (!DatumGetBool(test_result))
+			break;
+
+		/* Equal, so generate keyCol = lower_val expression */
 
 		/*
-		 * If lower_val and upper_val are both finite and happen to be equal,
-		 * emit only (keyCol = lower_val) for this column, because all rows in
-		 * this partition could only ever contain this value (ie, lower_val)
-		 * in the current partitioning column.  We must consider further
-		 * columns because the above condition does not fully constrain the
-		 * rows of this partition.
+		 * This can never be true for the last key column, but it's
+		 * better to make sure.  This is because such a range
+		 * partition would never be allowed to be defined (it would
+		 * have an empty range otherwise).
 		 */
-		if (lower_val && upper_val)
-		{
-			/* Get the correct btree equality operator for the test */
-			operoid = get_partition_operator(key, i, BTEqualStrategyNumber,
-											 &need_relabel);
-
-			/* Create the test expression */
-			estate = CreateExecutorState();
-			oldcxt = MemoryContextSwitchTo(estate->es_query_cxt);
-			test_expr = make_opclause(operoid,
-									  BOOLOID,
-									  false,
-									  (Expr *) lower_val,
-									  (Expr *) upper_val,
-									  InvalidOid,
-									  key->partcollation[i]);
-			fix_opfuncids((Node *) test_expr);
-			test_exprstate = ExecInitExpr(test_expr, NULL);
-			test_result = ExecEvalExprSwitchContext(test_exprstate,
-											  GetPerTupleExprContext(estate),
-													&isNull);
-			MemoryContextSwitchTo(oldcxt);
-			FreeExecutorState(estate);
+		if (i == key->partnatts - 1)
+			elog(ERROR, "invalid range bound specification");
+
+		result = lappend(result,
+						 make_partition_op_expr(key, i, BTEqualStrategyNumber,
+												keyCol, (Expr *) lower_val));
+
+		i++;
+	}
 
-			if (DatumGetBool(test_result))
+	/* First pair of lower_val and upper_val that are not equal. */
+	lower_or_start_datum = cell1;
+	upper_or_start_datum = cell2;
+
+	/* OR will have as many arms as there are key columns left. */
+	maxnum_or_arms = key->partnatts - i;
+
+	num_or_arms = 0;
+	lower_or_arms = upper_or_arms = NIL;
+	while (num_or_arms < maxnum_or_arms)
+	{
+		List		*lower_or_arm_args = NIL,
+					*upper_or_arm_args = NIL;
+
+		j = i;
+		partexprs_item = &partexprs_item_saved;
+		for_both_cell(cell1, lower_or_start_datum, cell2, upper_or_start_datum)
+		{
+			ldatum = lfirst(cell1);
+			udatum = lfirst(cell2);
+			get_range_key_properties(key, j, ldatum, udatum,
+									 &partexprs_item,
+									 &keyCol,
+									 &lower_val, &upper_val,
+									 &nulltest);
+
+			if (nulltest)
+				result = lappend(result, nulltest);
+
+			if (lower_val)
 			{
-				/* This can never be, but it's better to make sure */
-				if (i == key->partnatts - 1)
-					elog(ERROR, "invalid range bound specification");
-
-				if (need_relabel || key->partcollation[i] != key->parttypcoll[i])
-					keyCol = (Expr *) makeRelabelType(keyCol,
-													  key->partopcintype[i],
-													  -1,
-													  key->partcollation[i],
-													  COERCE_EXPLICIT_CAST);
-				result = lappend(result,
-								 make_opclause(operoid,
-											   BOOLOID,
-											   false,
-											   keyCol,
-											   (Expr *) lower_val,
-											   InvalidOid,
-											   key->partcollation[i]));
-
-				/* Go over to consider the next column. */
-				i++;
-				continue;
+				uint16	strategy;
+
+				/*
+				 * For the non-last columns of this arm, use the equality
+				 * operator.
+				 */
+				if (j - i < num_or_arms)
+					strategy = BTEqualStrategyNumber;
+				else
+					/* Consider that the lower bound is inclusive */
+					strategy = (j == key->partnatts - 1)
+										? BTGreaterEqualStrategyNumber
+										: BTGreaterStrategyNumber;
+
+				lower_or_arm_args = lappend(lower_or_arm_args,
+											make_partition_op_expr(key, j,
+																   strategy,
+																   keyCol,
+														(Expr *) lower_val));
 			}
-		}
 
-		/*
-		 * We can say here that lower_val != upper_val.  Emit expressions
-		 * (keyCol >= lower_val) and (keyCol < upper_val), then stop.
-		 */
-		if (lower_val)
-		{
-			operoid = get_partition_operator(key, i,
-											 BTGreaterEqualStrategyNumber,
-											 &need_relabel);
-
-			if (need_relabel || key->partcollation[i] != key->parttypcoll[i])
-				keyCol = (Expr *) makeRelabelType(keyCol,
-												  key->partopcintype[i],
-												  -1,
-												  key->partcollation[i],
-												  COERCE_EXPLICIT_CAST);
-			result = lappend(result,
-							 make_opclause(operoid,
-										   BOOLOID,
-										   false,
-										   keyCol,
-										   (Expr *) lower_val,
-										   InvalidOid,
-										   key->partcollation[i]));
-		}
+			if (upper_val)
+			{
+				uint16	strategy;
 
-		if (upper_val)
-		{
-			operoid = get_partition_operator(key, i,
-											 BTLessStrategyNumber,
-											 &need_relabel);
-
-			if (need_relabel || key->partcollation[i] != key->parttypcoll[i])
-				keyCol = (Expr *) makeRelabelType(keyCol,
-												  key->partopcintype[i],
-												  -1,
-												  key->partcollation[i],
-												  COERCE_EXPLICIT_CAST);
-
-			result = lappend(result,
-							 make_opclause(operoid,
-										   BOOLOID,
-										   false,
-										   keyCol,
-										   (Expr *) upper_val,
-										   InvalidOid,
-										   key->partcollation[i]));
-		}
+				/*
+				 * For the non-last columns of this arm, use the equality
+				 * operator.
+				 */
+				if (j - i < num_or_arms)
+					strategy = BTEqualStrategyNumber;
+				else
+					strategy = BTLessStrategyNumber;
 
-		/*
-		 * We can stop at this column, because we would not have checked the
-		 * next column when routing a given row into this partition.
-		 */
-		break;
-	}
+				upper_or_arm_args = lappend(upper_or_arm_args,
+											make_partition_op_expr(key, j,
+																   strategy,
+																   keyCol,
+														(Expr *) upper_val));
 
-	return result;
-}
+			}
 
-/*
- * get_partition_operator
- *
- * Return oid of the operator of given strategy for a given partition key
- * column.
- */
-static Oid
-get_partition_operator(PartitionKey key, int col, StrategyNumber strategy,
-					   bool *need_relabel)
-{
-	Oid			operoid;
+			/* Did we generate enough of OR's arguments? */
+			++j;
+			if (j - i > num_or_arms)
+				break;
+		}
 
-	/*
-	 * First check if there exists an operator of the given strategy, with
-	 * this column's type as both its lefttype and righttype, in the
-	 * partitioning operator family specified for the column.
-	 */
-	operoid = get_opfamily_member(key->partopfamily[col],
-								  key->parttypid[col],
-								  key->parttypid[col],
-								  strategy);
+		/* One arm of the OR expression for each of lower and upper bounds */
+		if (lower_or_arm_args != NIL)
+			lower_or_arms = lappend(lower_or_arms,
+							 list_length(lower_or_arm_args) > 1
+							  ? makeBoolExpr(AND_EXPR, lower_or_arm_args, -1)
+							  : linitial(lower_or_arm_args));
+
+		if (upper_or_arm_args != NIL)
+			upper_or_arms = lappend(upper_or_arms,
+							 list_length(upper_or_arm_args) > 1
+							  ? makeBoolExpr(AND_EXPR, upper_or_arm_args, -1)
+							  : linitial(upper_or_arm_args));
+		++num_or_arms;
+	}
 
 	/*
-	 * If one doesn't exist, we must resort to using an operator in the same
-	 * opreator family but with the operator class declared input type.  It is
-	 * OK to do so, because the column's type is known to be binary-coercible
-	 * with the operator class input type (otherwise, the operator class in
-	 * question would not have been accepted as the partitioning operator
-	 * class).  We must however inform the caller to wrap the non-Const
-	 * expression with a RelabelType node to denote the implicit coercion. It
-	 * ensures that the resulting expression structurally matches similarly
-	 * processed expressions within the optimizer.
+	 * Generate the OR expressions for each of lower and upper bounds (if
+	 * required), and append to the list of implicitly ANDed list of
+	 * expressions.
+	 *
+	 * At least one of lower and upper bound constraint should be present.
 	 */
-	if (!OidIsValid(operoid))
-	{
-		operoid = get_opfamily_member(key->partopfamily[col],
-									  key->partopcintype[col],
-									  key->partopcintype[col],
-									  strategy);
-		*need_relabel = true;
-	}
-	else
-		*need_relabel = false;
-
-	if (!OidIsValid(operoid))
-		elog(ERROR, "could not find operator for partitioning");
+	Assert(lower_or_arms != NIL || upper_or_arms != NIL);
+	if (lower_or_arms != NIL)
+		result = lappend(result,
+						 list_length(lower_or_arms) > 1
+							? makeBoolExpr(OR_EXPR, lower_or_arms, -1)
+							: linitial(lower_or_arms));
+	if (upper_or_arms != NIL)
+		result = lappend(result,
+						 list_length(upper_or_arms) > 1
+							? makeBoolExpr(OR_EXPR, upper_or_arms, -1)
+							: linitial(upper_or_arms));
 
-	return operoid;
+	return result;
 }
 
 /*
diff --git a/src/include/nodes/pg_list.h b/src/include/nodes/pg_list.h
index 9df7fb30d3..011054f745 100644
--- a/src/include/nodes/pg_list.h
+++ b/src/include/nodes/pg_list.h
@@ -183,6 +183,16 @@ list_length(const List *l)
 		 (cell1) = lnext(cell1), (cell2) = lnext(cell2))
 
 /*
+ * for_both_cell -
+ *	  a convenience macro which loops through two lists starting from the
+ *	  specified cells of each
+ */
+#define for_both_cell(cell1, initcell1, cell2, initcell2)	\
+	for ((cell1) = (initcell1), (cell2) = (initcell2);		\
+		 (cell1) != NULL && (cell2) != NULL;				\
+		 (cell1) = lnext(cell1), (cell2) = lnext(cell2))
+
+/*
  * forthree -
  *	  the same for three lists
  */
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 6163ed8117..af7090ba0d 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1828,3 +1828,93 @@ explain (costs off) select * from range_list_parted where a >= 30;
 
 drop table list_parted;
 drop table range_list_parted;
+-- check that constraint exclusion is able to cope with the partition
+-- constraint emitted for multi-column range partitioned tables
+create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
+create table mcrparted0 partition of mcrparted for values from (unbounded, unbounded, unbounded) to (1, 1, 1);
+create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to (10, 10, 10);
+create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
+create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20);
+create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (unbounded, unbounded, unbounded);
+explain (costs off) select * from mcrparted where a = 0;	-- scans mcrparted0
+          QUERY PLAN          
+------------------------------
+ Append
+   ->  Seq Scan on mcrparted0
+         Filter: (a = 0)
+(3 rows)
+
+explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5;	-- scans mcrparted1
+                 QUERY PLAN                  
+---------------------------------------------
+ Append
+   ->  Seq Scan on mcrparted1
+         Filter: ((a = 10) AND (abs(b) < 5))
+(3 rows)
+
+explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5;	-- scans mcrparted1, mcrparted2
+                 QUERY PLAN                  
+---------------------------------------------
+ Append
+   ->  Seq Scan on mcrparted1
+         Filter: ((a = 10) AND (abs(b) = 5))
+   ->  Seq Scan on mcrparted2
+         Filter: ((a = 10) AND (abs(b) = 5))
+(5 rows)
+
+explain (costs off) select * from mcrparted where abs(b) = 5;	-- scans all partitions
+          QUERY PLAN          
+------------------------------
+ Append
+   ->  Seq Scan on mcrparted0
+         Filter: (abs(b) = 5)
+   ->  Seq Scan on mcrparted1
+         Filter: (abs(b) = 5)
+   ->  Seq Scan on mcrparted2
+         Filter: (abs(b) = 5)
+   ->  Seq Scan on mcrparted3
+         Filter: (abs(b) = 5)
+   ->  Seq Scan on mcrparted5
+         Filter: (abs(b) = 5)
+(11 rows)
+
+explain (costs off) select * from mcrparted where a > -1;	-- scans all partitions
+             QUERY PLAN              
+-------------------------------------
+ Append
+   ->  Seq Scan on mcrparted0
+         Filter: (a > '-1'::integer)
+   ->  Seq Scan on mcrparted1
+         Filter: (a > '-1'::integer)
+   ->  Seq Scan on mcrparted2
+         Filter: (a > '-1'::integer)
+   ->  Seq Scan on mcrparted3
+         Filter: (a > '-1'::integer)
+   ->  Seq Scan on mcrparted4
+         Filter: (a > '-1'::integer)
+   ->  Seq Scan on mcrparted5
+         Filter: (a > '-1'::integer)
+(13 rows)
+
+explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c > 10;	-- scans mcrparted4
+                        QUERY PLAN                         
+-----------------------------------------------------------
+ Append
+   ->  Seq Scan on mcrparted4
+         Filter: ((c > 10) AND (a = 20) AND (abs(b) = 10))
+(3 rows)
+
+explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5
+               QUERY PLAN                
+-----------------------------------------
+ Append
+   ->  Seq Scan on mcrparted3
+         Filter: ((c > 20) AND (a = 20))
+   ->  Seq Scan on mcrparted4
+         Filter: ((c > 20) AND (a = 20))
+   ->  Seq Scan on mcrparted5
+         Filter: ((c > 20) AND (a = 20))
+(7 rows)
+
+drop table mcrparted;
diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out
index 6f34b1c640..c68b31415d 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -435,3 +435,62 @@ revoke all on key_desc from someone_else;
 revoke all on key_desc_1 from someone_else;
 drop role someone_else;
 drop table key_desc, key_desc_1;
+-- check multi-column range partitioning expression enforces the same
+-- constraint as what tuple-routing would determine it to be
+create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
+create table mcrparted0 partition of mcrparted for values from (unbounded, unbounded, unbounded) to (1, 1, 1);
+create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to (10, 10, 10);
+create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
+create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20);
+create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (unbounded, unbounded, unbounded);
+-- routed to mcrparted0
+insert into mcrparted values (0, 1, 1);
+insert into mcrparted0 values (0, 1, 1);
+-- routed to mcparted1
+insert into mcrparted values (9, 1000, 1);
+insert into mcrparted1 values (9, 1000, 1);
+insert into mcrparted values (10, 5, 1);
+insert into mcrparted1 values (10, 5, 1);
+-- routed to mcparted2
+insert into mcrparted values (10, 6, 1000);
+insert into mcrparted2 values (10, 6, 1000);
+insert into mcrparted values (10, 10, 9);
+insert into mcrparted2 values (10, 10, 9);
+-- no partition exists, nor does mcrparted2 accept it
+insert into mcrparted values (10, 10, 1000);
+ERROR:  no partition of relation "mcrparted" found for row
+DETAIL:  Partition key of the failing row contains (a, abs(b), c) = (10, 10, 1000).
+insert into mcrparted2 values (10, 10, 1000);
+ERROR:  new row for relation "mcrparted2" violates partition constraint
+DETAIL:  Failing row contains (10, 10, 1000).
+-- nor does mcrparted3
+insert into mcrparted3 values (10, 10, 1000);
+ERROR:  new row for relation "mcrparted3" violates partition constraint
+DETAIL:  Failing row contains (10, 10, 1000).
+-- routed to mcrparted5
+insert into mcrparted values (20, 20, 20);
+insert into mcrparted5 values (20, 20, 20);
+insert into mcrparted4 values (20, 20, 20);	-- error
+ERROR:  new row for relation "mcrparted4" violates partition constraint
+DETAIL:  Failing row contains (20, 20, 20).
+-- check rows
+select tableoid::regclass::text, * from mcrparted order by 1;
+  tableoid  | a  |  b   |  c   
+------------+----+------+------
+ mcrparted0 |  0 |    1 |    1
+ mcrparted0 |  0 |    1 |    1
+ mcrparted1 |  9 | 1000 |    1
+ mcrparted1 |  9 | 1000 |    1
+ mcrparted1 | 10 |    5 |    1
+ mcrparted1 | 10 |    5 |    1
+ mcrparted2 | 10 |    6 | 1000
+ mcrparted2 | 10 |    6 | 1000
+ mcrparted2 | 10 |   10 |    9
+ mcrparted2 | 10 |   10 |    9
+ mcrparted5 | 20 |   20 |   20
+ mcrparted5 | 20 |   20 |   20
+(12 rows)
+
+-- cleanup
+drop table mcrparted;
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index d43b75c4a7..7f34f43ec0 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -643,3 +643,21 @@ explain (costs off) select * from range_list_parted where a >= 30;
 
 drop table list_parted;
 drop table range_list_parted;
+
+-- check that constraint exclusion is able to cope with the partition
+-- constraint emitted for multi-column range partitioned tables
+create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
+create table mcrparted0 partition of mcrparted for values from (unbounded, unbounded, unbounded) to (1, 1, 1);
+create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to (10, 10, 10);
+create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
+create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20);
+create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (unbounded, unbounded, unbounded);
+explain (costs off) select * from mcrparted where a = 0;	-- scans mcrparted0
+explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5;	-- scans mcrparted1
+explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5;	-- scans mcrparted1, mcrparted2
+explain (costs off) select * from mcrparted where abs(b) = 5;	-- scans all partitions
+explain (costs off) select * from mcrparted where a > -1;	-- scans all partitions
+explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c > 10;	-- scans mcrparted4
+explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5
+drop table mcrparted;
diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql
index 020854c960..88509f9cce 100644
--- a/src/test/regress/sql/insert.sql
+++ b/src/test/regress/sql/insert.sql
@@ -289,3 +289,46 @@ revoke all on key_desc from someone_else;
 revoke all on key_desc_1 from someone_else;
 drop role someone_else;
 drop table key_desc, key_desc_1;
+
+-- check multi-column range partitioning expression enforces the same
+-- constraint as what tuple-routing would determine it to be
+create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
+create table mcrparted0 partition of mcrparted for values from (unbounded, unbounded, unbounded) to (1, 1, 1);
+create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to (10, 10, 10);
+create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
+create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20);
+create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (unbounded, unbounded, unbounded);
+
+-- routed to mcrparted0
+insert into mcrparted values (0, 1, 1);
+insert into mcrparted0 values (0, 1, 1);
+
+-- routed to mcparted1
+insert into mcrparted values (9, 1000, 1);
+insert into mcrparted1 values (9, 1000, 1);
+insert into mcrparted values (10, 5, 1);
+insert into mcrparted1 values (10, 5, 1);
+
+-- routed to mcparted2
+insert into mcrparted values (10, 6, 1000);
+insert into mcrparted2 values (10, 6, 1000);
+insert into mcrparted values (10, 10, 9);
+insert into mcrparted2 values (10, 10, 9);
+
+-- no partition exists, nor does mcrparted2 accept it
+insert into mcrparted values (10, 10, 1000);
+insert into mcrparted2 values (10, 10, 1000);
+-- nor does mcrparted3
+insert into mcrparted3 values (10, 10, 1000);
+
+-- routed to mcrparted5
+insert into mcrparted values (20, 20, 20);
+insert into mcrparted5 values (20, 20, 20);
+insert into mcrparted4 values (20, 20, 20);	-- error
+
+-- check rows
+select tableoid::regclass::text, * from mcrparted order by 1;
+
+-- cleanup
+drop table mcrparted;
-- 
2.11.0

From 2bca3f25e5ac17631200fb5d76684d5805a76d77 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Mon, 8 May 2017 13:29:19 +0900
Subject: [PATCH 1/3] Disallow finite value after UNBOUNDED in range bounds

It does not make sense to have a finite lower or upper bound value
for a column, if an earlier column is unconstrained, i.e., has
UNBOUNDED specified.
---
 src/backend/parser/parse_utilcmd.c         | 34 ++++++++++++++++++++++++++++--
 src/test/regress/expected/create_table.out |  7 ++++++
 src/test/regress/sql/create_table.sql      |  5 +++++
 3 files changed, 44 insertions(+), 2 deletions(-)

diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index e187409f6f..3abb655ea6 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -3358,6 +3358,9 @@ transformPartitionBound(ParseState *pstate, Relation parent, Node *bound)
 		int			i,
 					j;
 		char	   *colname;
+		PartitionRangeDatum *ldatum,
+					   *rdatum;
+		bool		seen_unbounded;
 
 		if (spec->strategy != PARTITION_STRATEGY_RANGE)
 			ereport(ERROR,
@@ -3376,12 +3379,39 @@ transformPartitionBound(ParseState *pstate, Relation parent, Node *bound)
 					(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
 					 errmsg("TO must specify exactly one value per partitioning column")));
 
+		/*
+		 * Check that no finite value follows a UNBOUNDED literal in either of
+		 * lower and upper bound lists.
+		 */
+		seen_unbounded = false;
+		foreach(cell1, spec->lowerdatums)
+		{
+			ldatum = (PartitionRangeDatum *) lfirst(cell1);
+			if (ldatum->infinite)
+				seen_unbounded = true;
+			else if (seen_unbounded)
+				ereport(ERROR,
+						(errcode(ERRCODE_DATATYPE_MISMATCH),
+						 errmsg("cannot specify finite value after UNBOUNDED"),
+						 parser_errposition(pstate, exprLocation((Node *) ldatum))));
+		}
+		seen_unbounded = false;
+		foreach(cell1, spec->upperdatums)
+		{
+			rdatum = (PartitionRangeDatum *) lfirst(cell1);
+			if (rdatum->infinite)
+				seen_unbounded = true;
+			else if (seen_unbounded)
+				ereport(ERROR,
+						(errcode(ERRCODE_DATATYPE_MISMATCH),
+						 errmsg("cannot specify finite value after UNBOUNDED"),
+						 parser_errposition(pstate, exprLocation((Node *) rdatum))));
+		}
+
 		i = j = 0;
 		result_spec->lowerdatums = result_spec->upperdatums = NIL;
 		forboth(cell1, spec->lowerdatums, cell2, spec->upperdatums)
 		{
-			PartitionRangeDatum *ldatum,
-					   *rdatum;
 			Node	   *value;
 			A_Const    *lcon = NULL,
 					   *rcon = NULL;
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index dda0d7ee5d..15d4ce591c 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -505,6 +505,13 @@ ERROR:  TO must specify exactly one value per partitioning column
 -- cannot specify null values in range bounds
 CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM (null) TO (unbounded);
 ERROR:  cannot specify NULL in range bound
+-- cannot specify finite values after UNBOUNDED has been specified
+CREATE TABLE range_parted_multicol (a int, b int, c int) PARTITION BY RANGE (a, b, c);
+CREATE TABLE fail_part PARTITION OF range_parted_multicol FOR VALUES FROM (1, UNBOUNDED, 1) TO (UNBOUNDED, 1, 1);
+ERROR:  cannot specify finite value after UNBOUNDED
+LINE 1: ...ge_parted_multicol FOR VALUES FROM (1, UNBOUNDED, 1) TO (UNB...
+                                                             ^
+DROP TABLE range_parted_multicol;
 -- check if compatible with the specified parent
 -- cannot create as partition of a non-partitioned table
 CREATE TABLE unparted (
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index caf5ddb58b..95035c5947 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -473,6 +473,11 @@ CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM ('a') TO ('z',
 -- cannot specify null values in range bounds
 CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM (null) TO (unbounded);
 
+-- cannot specify finite values after UNBOUNDED has been specified
+CREATE TABLE range_parted_multicol (a int, b int, c int) PARTITION BY RANGE (a, b, c);
+CREATE TABLE fail_part PARTITION OF range_parted_multicol FOR VALUES FROM (1, UNBOUNDED, 1) TO (UNBOUNDED, 1, 1);
+DROP TABLE range_parted_multicol;
+
 -- check if compatible with the specified parent
 
 -- cannot create as partition of a non-partitioned table
-- 
2.11.0

From 30a895dfc08f61c0c9b4d8be2456a0e095c04bf6 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Tue, 2 May 2017 11:03:54 +0900
Subject: [PATCH 2/3] Emit "correct" range partition constraint expression

Currently emitted expression does not always describe the constraint
correctly, especially in the case of multi-column range key.

Code surrounding get_qual_for_*() has been rearranged a little to
move common code to a couple of new functions.

Original issue reported by Olaf Gawenda (olaf.gw@googlemail.com)
---
 src/backend/catalog/partition.c       | 625 ++++++++++++++++++++++------------
 src/include/nodes/pg_list.h           |  14 +
 src/test/regress/expected/inherit.out |  90 +++++
 src/test/regress/expected/insert.out  |  59 ++++
 src/test/regress/sql/inherit.sql      |  18 +
 src/test/regress/sql/insert.sql       |  43 +++
 6 files changed, 631 insertions(+), 218 deletions(-)

diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c
index 8641ae16a2..bd4f0b67a4 100644
--- a/src/backend/catalog/partition.c
+++ b/src/backend/catalog/partition.c
@@ -118,10 +118,19 @@ static int32 qsort_partition_list_value_cmp(const void *a, const void *b,
 static int32 qsort_partition_rbound_cmp(const void *a, const void *b,
 						   void *arg);
 
-static List *get_qual_for_list(PartitionKey key, PartitionBoundSpec *spec);
-static List *get_qual_for_range(PartitionKey key, PartitionBoundSpec *spec);
 static Oid get_partition_operator(PartitionKey key, int col,
 					   StrategyNumber strategy, bool *need_relabel);
+static Expr* make_partition_op_expr(PartitionKey key, int keynum,
+					   uint16 strategy, Expr *arg1, Expr *arg2);
+static void get_range_key_properties(PartitionKey key, int keynum,
+						 PartitionRangeDatum *ldatum,
+						 PartitionRangeDatum *udatum,
+						 ListCell **partexprs_item,
+						 Expr **keyCol,
+						 Const **lower_val, Const **upper_val,
+						 NullTest **nulltest);
+static List *get_qual_for_list(PartitionKey key, PartitionBoundSpec *spec);
+static List *get_qual_for_range(PartitionKey key, PartitionBoundSpec *spec);
 static List *generate_partition_qual(Relation rel);
 
 static PartitionRangeBound *make_one_range_bound(PartitionKey key, int index,
@@ -1146,6 +1155,123 @@ RelationGetPartitionDispatchInfo(Relation rel, int lockmode,
 /* Module-local functions */
 
 /*
+ * get_partition_operator
+ *
+ * Return oid of the operator of given strategy for a given partition key
+ * column.
+ */
+static Oid
+get_partition_operator(PartitionKey key, int col, StrategyNumber strategy,
+					   bool *need_relabel)
+{
+	Oid			operoid;
+
+	/*
+	 * First check if there exists an operator of the given strategy, with
+	 * this column's type as both its lefttype and righttype, in the
+	 * partitioning operator family specified for the column.
+	 */
+	operoid = get_opfamily_member(key->partopfamily[col],
+								  key->parttypid[col],
+								  key->parttypid[col],
+								  strategy);
+
+	/*
+	 * If one doesn't exist, we must resort to using an operator in the same
+	 * opreator family but with the operator class declared input type.  It is
+	 * OK to do so, because the column's type is known to be binary-coercible
+	 * with the operator class input type (otherwise, the operator class in
+	 * question would not have been accepted as the partitioning operator
+	 * class).  We must however inform the caller to wrap the non-Const
+	 * expression with a RelabelType node to denote the implicit coercion. It
+	 * ensures that the resulting expression structurally matches similarly
+	 * processed expressions within the optimizer.
+	 */
+	if (!OidIsValid(operoid))
+	{
+		operoid = get_opfamily_member(key->partopfamily[col],
+									  key->partopcintype[col],
+									  key->partopcintype[col],
+									  strategy);
+		*need_relabel = true;
+	}
+	else
+		*need_relabel = false;
+
+	if (!OidIsValid(operoid))
+		elog(ERROR, "could not find operator for partitioning");
+
+	return operoid;
+}
+
+/*
+ * make_partition_op_expr
+ *		Returns an Expr for the given partition key column with arg1 and
+ *		arg2 as its leftop and rightop, respectively
+ */
+static Expr*
+make_partition_op_expr(PartitionKey key, int keynum,
+					   uint16 strategy, Expr *arg1, Expr *arg2)
+{
+	Oid		operoid;
+	bool	need_relabel = false;
+	Expr   *result = NULL;
+
+	/* Get the correct btree operator for this partitioning column */
+	operoid = get_partition_operator(key, keynum, strategy, &need_relabel);
+
+	/*
+	 * Chosen operator may be such that the non-Const operand needs to
+	 * be coerced, so apply the same; see the comment in
+	 * get_partition_operator().
+	 */
+	if (!IsA(arg1, Const) &&
+		(need_relabel ||
+		 key->partcollation[keynum] != key->parttypcoll[keynum]))
+		arg1 = (Expr *) makeRelabelType(arg1,
+										key->partopcintype[keynum],
+										-1,
+										key->partcollation[keynum],
+										COERCE_EXPLICIT_CAST);
+
+	/* Generate the actual expression */
+	switch (key->strategy)
+	{
+		case PARTITION_STRATEGY_LIST:
+		{
+			ScalarArrayOpExpr *saopexpr;
+
+			/* Build leftop = ANY (rightop) */
+			saopexpr = makeNode(ScalarArrayOpExpr);
+			saopexpr->opno = operoid;
+			saopexpr->opfuncid = get_opcode(operoid);
+			saopexpr->useOr = true;
+			saopexpr->inputcollid = key->partcollation[0];
+			saopexpr->args = list_make2(arg1, arg2);
+			saopexpr->location = -1;
+
+			result = (Expr *) saopexpr;
+			break;
+		}
+
+		case PARTITION_STRATEGY_RANGE:
+			result = make_opclause(operoid,
+								   BOOLOID,
+								   false,
+								   arg1, arg2,
+								   InvalidOid,
+								   key->partcollation[keynum]);
+			break;
+
+		default:
+			elog(ERROR, "invalid partitioning strategy");
+			break;
+	}
+
+	return result;
+}
+
+/*
  * get_qual_for_list
  *
  * Returns a list of expressions to use as a list partition's constraint.
@@ -1155,14 +1281,12 @@ get_qual_for_list(PartitionKey key, PartitionBoundSpec *spec)
 {
 	List	   *result;
 	ArrayExpr  *arr;
-	ScalarArrayOpExpr *opexpr;
+	Expr	   *opexpr;
 	ListCell   *cell,
 			   *prev,
 			   *next;
 	Expr	   *keyCol;
-	Oid			operoid;
-	bool		need_relabel,
-				list_has_null = false;
+	bool		list_has_null = false;
 	NullTest   *nulltest1 = NULL,
 			   *nulltest2 = NULL;
 
@@ -1233,24 +1357,9 @@ get_qual_for_list(PartitionKey key, PartitionBoundSpec *spec)
 	arr->multidims = false;
 	arr->location = -1;
 
-	/* Get the correct btree equality operator */
-	operoid = get_partition_operator(key, 0, BTEqualStrategyNumber,
-									 &need_relabel);
-	if (need_relabel || key->partcollation[0] != key->parttypcoll[0])
-		keyCol = (Expr *) makeRelabelType(keyCol,
-										  key->partopcintype[0],
-										  -1,
-										  key->partcollation[0],
-										  COERCE_EXPLICIT_CAST);
-
-	/* Build leftop = ANY (rightop) */
-	opexpr = makeNode(ScalarArrayOpExpr);
-	opexpr->opno = operoid;
-	opexpr->opfuncid = get_opcode(operoid);
-	opexpr->useOr = true;
-	opexpr->inputcollid = key->partcollation[0];
-	opexpr->args = list_make2(keyCol, arr);
-	opexpr->location = -1;
+	/* Generate the main expression, i.e., keyCol = ANY (arr) */
+	opexpr = make_partition_op_expr(key, 0, BTEqualStrategyNumber,
+									keyCol, (Expr *) arr);
 
 	if (nulltest1)
 		result = list_make2(nulltest1, opexpr);
@@ -1268,9 +1377,100 @@ get_qual_for_list(PartitionKey key, PartitionBoundSpec *spec)
 }
 
 /*
+ * get_range_key_properties
+ *		Returns range partition key information for a given column
+ *
+ * On return, *lower_val and *upper_val contain either a valid Const
+ * expression or NULL.  *nulltest contains a NullTest expression, or
+ * NULL.
+ */
+static void
+get_range_key_properties(PartitionKey key, int keynum,
+						 PartitionRangeDatum *ldatum,
+						 PartitionRangeDatum *udatum,
+						 ListCell **partexprs_item,
+						 Expr **keyCol,
+						 Const **lower_val, Const **upper_val,
+						 NullTest **nulltest)
+{
+	/* Partition key expression for this column */
+	if (key->partattrs[keynum] != 0)
+	{
+		*keyCol = (Expr *) makeVar(1,
+								  key->partattrs[keynum],
+								  key->parttypid[keynum],
+								  key->parttypmod[keynum],
+								  key->parttypcoll[keynum],
+								  0);
+	}
+	else
+	{
+		*keyCol = copyObject(lfirst(*partexprs_item));
+		*partexprs_item = lnext(*partexprs_item);
+	}
+
+	/*
+	 * A range-partitioned table does not allow partition keys to be null.
+	 * For simple columns, their NOT NULL constraint suffices for the
+	 * enforcement of non-nullability.  But for the expression keys, which
+	 * are still nullable, we must emit a IS NOT NULL expression.
+	 */
+	if (!IsA(*keyCol, Var))
+	{
+		*nulltest = makeNode(NullTest);
+		(*nulltest)->arg = *keyCol;
+		(*nulltest)->nulltesttype = IS_NOT_NULL;
+		(*nulltest)->argisrow = false;
+		(*nulltest)->location = -1;
+	}
+	else
+		*nulltest = NULL;
+
+	if (!ldatum->infinite)
+		*lower_val = (Const *) ldatum->value;
+	else
+		*lower_val = NULL;
+
+	if (!udatum->infinite)
+		*upper_val = (Const *) udatum->value;
+	else
+		*upper_val = NULL;
+}
+
+/*
  * get_qual_for_range
  *
- * Get a list of OpExpr's to use as a range partition's constraint.
+ * Get a list of expressions to use as a range partition's constraint.
+ * If there are multiple expressions, they are to be considered implicitly
+ * ANDed.
+ *
+ * For a multi-column range partition key, say (a, b, c), with (al, bl, cl)
+ * as the lower bound tuple and (au, bu, cu) as the upper bound tuple, we
+ * generate an expression tree of the following form:
+ *
+ *	(a > al OR (a = al AND b > bl) OR (a = al AND b = bl AND c >= cl))
+ *		AND
+ *	(a < au OR (a = au AND b < bu) OR (a = au AND b = bu AND c < cu))
+ *
+ * If b were an expression key instead of a simple column, we also append
+ * (b IS NOT NULL) to the AND's argument list.
+ *
+ * It is often the case that a prefix of lower and upper bound tuples contains
+ * the same values, for example, (al = au), in which case, we will emit an
+ * expression tree of the following form:
+ *
+ *	(a = al)
+ *		AND
+ *	(b > bl OR (b = bl AND c >= cl))
+ *		AND
+ *	(b < bu) OR (b = bu AND c < cu))
+ *
+ * In most common cases with only one partition column, say a, the following
+ * expression tree will be generated: a >= al AND a < au
+ *
+ * If all values of both lower and upper bounds are UNBOUNDED, the partition
+ * does not really have a constraint, except the IS NOT NULL constraint for
+ * any expression keys.  We append a constant-true expression in that case.
  */
 static List *
 get_qual_for_range(PartitionKey key, PartitionBoundSpec *spec)
@@ -1278,239 +1478,228 @@ get_qual_for_range(PartitionKey key, PartitionBoundSpec *spec)
 	List	   *result = NIL;
 	ListCell   *cell1,
 			   *cell2,
-			   *partexprs_item;
-	int			i;
+			   *partexprs_item,
+				partexprs_item_saved;
+	int			i,
+				j;
+	PartitionRangeDatum *ldatum,
+			   *udatum;
+	Expr	   *keyCol;
+	Const	   *lower_val,
+			   *upper_val;
+	NullTest   *nulltest;
+	List	   *lower_or_arms,
+			   *upper_or_arms;
+	int			maxnum_or_arms,
+				num_or_arms;
+	ListCell   *lower_or_start_datum,
+			   *upper_or_start_datum;
+
+	lower_or_start_datum = list_head(spec->lowerdatums);
+	upper_or_start_datum = list_head(spec->upperdatums);
+	maxnum_or_arms = key->partnatts;
 
 	/*
-	 * Iterate over columns of the key, emitting an OpExpr for each using the
-	 * corresponding lower and upper datums as constant operands.
+	 * Iterate over the key columns and check if the corresponding lower and
+	 * upper datums are equal using the btree equality operator for the
+	 * column's type.  If equal, we emit single keyCol = common_value
+	 * expression.  Starting from the first column for which the corresponding
+	 * lower and upper bound datums are not equal, we generate OR expressions
+	 * as shown in the function's header comment.
 	 */
 	i = 0;
 	partexprs_item = list_head(key->partexprs);
 	forboth(cell1, spec->lowerdatums, cell2, spec->upperdatums)
 	{
-		PartitionRangeDatum *ldatum = lfirst(cell1),
-				   *udatum = lfirst(cell2);
-		Expr	   *keyCol;
-		Const	   *lower_val = NULL,
-				   *upper_val = NULL;
 		EState	   *estate;
 		MemoryContext oldcxt;
 		Expr	   *test_expr;
 		ExprState  *test_exprstate;
 		Datum		test_result;
 		bool		isNull;
-		bool		need_relabel = false;
-		Oid			operoid;
-		NullTest   *nulltest;
 
-		/* Left operand */
-		if (key->partattrs[i] != 0)
-		{
-			keyCol = (Expr *) makeVar(1,
-									  key->partattrs[i],
-									  key->parttypid[i],
-									  key->parttypmod[i],
-									  key->parttypcoll[i],
-									  0);
-		}
-		else
-		{
-			keyCol = copyObject(lfirst(partexprs_item));
-			partexprs_item = lnext(partexprs_item);
-		}
+		ldatum = lfirst(cell1);
+		udatum = lfirst(cell2);
 
 		/*
-		 * Emit a IS NOT NULL expression for non-Var keys, because whereas
-		 * simple attributes are covered by NOT NULL constraints, expression
-		 * keys are still nullable which is not acceptable in case of range
-		 * partitioning.
+		 * Since get_range_key_properties() modifies partexprs_item, and we
+		 * might need to start over from the previous expression in the
+		 * later part of this functiom, save away the current value.
 		 */
-		if (!IsA(keyCol, Var))
-		{
-			nulltest = makeNode(NullTest);
-			nulltest->arg = keyCol;
-			nulltest->nulltesttype = IS_NOT_NULL;
-			nulltest->argisrow = false;
-			nulltest->location = -1;
+		if (partexprs_item)
+			partexprs_item_saved = *partexprs_item;
+		get_range_key_properties(key, i, ldatum, udatum,
+								 &partexprs_item,
+								 &keyCol,
+								 &lower_val, &upper_val,
+								 &nulltest);
+
+		if (nulltest)
 			result = lappend(result, nulltest);
-		}
 
 		/*
-		 * Stop at this column if either of lower or upper datum is infinite,
-		 * but do emit an OpExpr for the non-infinite datum.
+		 * If either or both of lower_val and upper_val is NULL, they are
+		 * unequal, because being NULL means the column is unbounded in the
+		 * respective direction.
 		 */
-		if (!ldatum->infinite)
-			lower_val = (Const *) ldatum->value;
-		if (!udatum->infinite)
-			upper_val = (Const *) udatum->value;
+		if (!lower_val || !upper_val)
+			break;
 
-		/*
-		 * If lower_val and upper_val are both finite and happen to be equal,
-		 * emit only (keyCol = lower_val) for this column, because all rows in
-		 * this partition could only ever contain this value (ie, lower_val)
-		 * in the current partitioning column.  We must consider further
-		 * columns because the above condition does not fully constrain the
-		 * rows of this partition.
-		 */
-		if (lower_val && upper_val)
-		{
-			/* Get the correct btree equality operator for the test */
-			operoid = get_partition_operator(key, i, BTEqualStrategyNumber,
-											 &need_relabel);
-
-			/* Create the test expression */
-			estate = CreateExecutorState();
-			oldcxt = MemoryContextSwitchTo(estate->es_query_cxt);
-			test_expr = make_opclause(operoid,
-									  BOOLOID,
-									  false,
-									  (Expr *) lower_val,
-									  (Expr *) upper_val,
-									  InvalidOid,
-									  key->partcollation[i]);
-			fix_opfuncids((Node *) test_expr);
-			test_exprstate = ExecInitExpr(test_expr, NULL);
-			test_result = ExecEvalExprSwitchContext(test_exprstate,
-											  GetPerTupleExprContext(estate),
-													&isNull);
-			MemoryContextSwitchTo(oldcxt);
-			FreeExecutorState(estate);
+		/* Create the test expression */
+		estate = CreateExecutorState();
+		oldcxt = MemoryContextSwitchTo(estate->es_query_cxt);
+		test_expr = make_partition_op_expr(key, i, BTEqualStrategyNumber,
+										   (Expr *) lower_val,
+										   (Expr *) upper_val);
+		fix_opfuncids((Node *) test_expr);
+		test_exprstate = ExecInitExpr(test_expr, NULL);
+		test_result = ExecEvalExprSwitchContext(test_exprstate,
+										  GetPerTupleExprContext(estate),
+												&isNull);
+		MemoryContextSwitchTo(oldcxt);
+		FreeExecutorState(estate);
+
+		/* If not equal, go generate the OR expressions */
+		if (!DatumGetBool(test_result))
+			break;
 
-			if (DatumGetBool(test_result))
-			{
-				/* This can never be, but it's better to make sure */
-				if (i == key->partnatts - 1)
-					elog(ERROR, "invalid range bound specification");
-
-				if (need_relabel || key->partcollation[i] != key->parttypcoll[i])
-					keyCol = (Expr *) makeRelabelType(keyCol,
-													  key->partopcintype[i],
-													  -1,
-													  key->partcollation[i],
-													  COERCE_EXPLICIT_CAST);
-				result = lappend(result,
-								 make_opclause(operoid,
-											   BOOLOID,
-											   false,
-											   keyCol,
-											   (Expr *) lower_val,
-											   InvalidOid,
-											   key->partcollation[i]));
-
-				/* Go over to consider the next column. */
-				i++;
-				continue;
-			}
-		}
+		/* Equal, so generate keyCol = lower_val expression */
 
 		/*
-		 * We can say here that lower_val != upper_val.  Emit expressions
-		 * (keyCol >= lower_val) and (keyCol < upper_val), then stop.
+		 * This can never be true for the last key column, because such a
+		 * range partition would never be allowed to be defined (it would
+		 * have an empty range otherwise).
 		 */
-		if (lower_val)
-		{
-			operoid = get_partition_operator(key, i,
-											 BTGreaterEqualStrategyNumber,
-											 &need_relabel);
-
-			if (need_relabel || key->partcollation[i] != key->parttypcoll[i])
-				keyCol = (Expr *) makeRelabelType(keyCol,
-												  key->partopcintype[i],
-												  -1,
-												  key->partcollation[i],
-												  COERCE_EXPLICIT_CAST);
-			result = lappend(result,
-							 make_opclause(operoid,
-										   BOOLOID,
-										   false,
-										   keyCol,
-										   (Expr *) lower_val,
-										   InvalidOid,
-										   key->partcollation[i]));
-		}
+		if (i == key->partnatts - 1)
+			elog(ERROR, "invalid range bound specification");
+
+		result = lappend(result,
+						 make_partition_op_expr(key, i, BTEqualStrategyNumber,
+												keyCol, (Expr *) lower_val));
+
+		i++;
+	}
 
-		if (upper_val)
+	/* First pair of lower_val and upper_val that are not equal. */
+	lower_or_start_datum = cell1;
+	upper_or_start_datum = cell2;
+
+	/* OR will have as many arms as there are key columns left. */
+	maxnum_or_arms = key->partnatts - i;
+
+	num_or_arms = 0;
+	lower_or_arms = upper_or_arms = NIL;
+	while (num_or_arms < maxnum_or_arms)
+	{
+		List		*lower_or_arm_args = NIL,
+					*upper_or_arm_args = NIL;
+
+		j = i;
+		partexprs_item = &partexprs_item_saved;
+		for_both_cell(cell1, lower_or_start_datum, cell2, upper_or_start_datum)
 		{
-			operoid = get_partition_operator(key, i,
-											 BTLessStrategyNumber,
-											 &need_relabel);
+			ldatum = lfirst(cell1);
+			udatum = lfirst(cell2);
+			get_range_key_properties(key, j, ldatum, udatum,
+									 &partexprs_item,
+									 &keyCol,
+									 &lower_val, &upper_val,
+									 &nulltest);
+
+			if (nulltest)
+				result = lappend(result, nulltest);
+
+			if (lower_val)
+			{
+				uint16	strategy;
 
-			if (need_relabel || key->partcollation[i] != key->parttypcoll[i])
-				keyCol = (Expr *) makeRelabelType(keyCol,
-												  key->partopcintype[i],
-												  -1,
-												  key->partcollation[i],
-												  COERCE_EXPLICIT_CAST);
+				/*
+				 * For the non-last columns of this arm, use the equality
+				 * operator.
+				 */
+				if (j - i < num_or_arms)
+					strategy = BTEqualStrategyNumber;
+				else
+					/* Consider that the lower bound is inclusive */
+					strategy = (j == key->partnatts - 1)
+										? BTGreaterEqualStrategyNumber
+										: BTGreaterStrategyNumber;
+
+				lower_or_arm_args = lappend(lower_or_arm_args,
+											make_partition_op_expr(key, j,
+																   strategy,
+																   keyCol,
+														(Expr *) lower_val));
+			}
 
-			result = lappend(result,
-							 make_opclause(operoid,
-										   BOOLOID,
-										   false,
-										   keyCol,
-										   (Expr *) upper_val,
-										   InvalidOid,
-										   key->partcollation[i]));
-		}
+			if (upper_val)
+			{
+				uint16	strategy;
 
-		/*
-		 * We can stop at this column, because we would not have checked the
-		 * next column when routing a given row into this partition.
-		 */
-		break;
-	}
+				/*
+				 * For the non-last columns of this arm, use the equality
+				 * operator.
+				 */
+				if (j - i < num_or_arms)
+					strategy = BTEqualStrategyNumber;
+				else
+					strategy = BTLessStrategyNumber;
 
-	return result;
-}
+				upper_or_arm_args = lappend(upper_or_arm_args,
+											make_partition_op_expr(key, j,
+																   strategy,
+																   keyCol,
+														(Expr *) upper_val));
 
-/*
- * get_partition_operator
- *
- * Return oid of the operator of given strategy for a given partition key
- * column.
- */
-static Oid
-get_partition_operator(PartitionKey key, int col, StrategyNumber strategy,
-					   bool *need_relabel)
-{
-	Oid			operoid;
+			}
 
-	/*
-	 * First check if there exists an operator of the given strategy, with
-	 * this column's type as both its lefttype and righttype, in the
-	 * partitioning operator family specified for the column.
-	 */
-	operoid = get_opfamily_member(key->partopfamily[col],
-								  key->parttypid[col],
-								  key->parttypid[col],
-								  strategy);
+			/* Did we generate enough of OR's arguments? */
+			++j;
+			if (j - i > num_or_arms)
+				break;
+		}
+
+		/* One arm of the OR expression for each of lower and upper bounds */
+		if (lower_or_arm_args != NIL)
+			lower_or_arms = lappend(lower_or_arms,
+							 list_length(lower_or_arm_args) > 1
+							  ? makeBoolExpr(AND_EXPR, lower_or_arm_args, -1)
+							  : linitial(lower_or_arm_args));
+
+		if (upper_or_arm_args != NIL)
+			upper_or_arms = lappend(upper_or_arms,
+							 list_length(upper_or_arm_args) > 1
+							  ? makeBoolExpr(AND_EXPR, upper_or_arm_args, -1)
+							  : linitial(upper_or_arm_args));
+		++num_or_arms;
+	}
 
 	/*
-	 * If one doesn't exist, we must resort to using an operator in the same
-	 * opreator family but with the operator class declared input type.  It is
-	 * OK to do so, because the column's type is known to be binary-coercible
-	 * with the operator class input type (otherwise, the operator class in
-	 * question would not have been accepted as the partitioning operator
-	 * class).  We must however inform the caller to wrap the non-Const
-	 * expression with a RelabelType node to denote the implicit coercion. It
-	 * ensures that the resulting expression structurally matches similarly
-	 * processed expressions within the optimizer.
+	 * Generate the OR expressions for each of lower and upper bounds (if
+	 * required), and append to the list of implicitly ANDed list of
+	 * expressions.
+	 *
+	 * If both lower_or_arms and upper_or_arms are empty, we append a
+	 * constant-true expression.  That happens if all of the literal values
+	 * in both the lower and upper bound lists are UNBOUNDED.
 	 */
-	if (!OidIsValid(operoid))
+	if (lower_or_arms == NIL && upper_or_arms == NIL)
+		result = lappend(result, makeBoolConst(true, false));
+	else
 	{
-		operoid = get_opfamily_member(key->partopfamily[col],
-									  key->partopcintype[col],
-									  key->partopcintype[col],
-									  strategy);
-		*need_relabel = true;
+		if (lower_or_arms != NIL)
+			result = lappend(result,
+							 list_length(lower_or_arms) > 1
+								? makeBoolExpr(OR_EXPR, lower_or_arms, -1)
+								: linitial(lower_or_arms));
+		if (upper_or_arms != NIL)
+			result = lappend(result,
+							 list_length(upper_or_arms) > 1
+								? makeBoolExpr(OR_EXPR, upper_or_arms, -1)
+								: linitial(upper_or_arms));
 	}
-	else
-		*need_relabel = false;
-
-	if (!OidIsValid(operoid))
-		elog(ERROR, "could not find operator for partitioning");
 
-	return operoid;
+	return result;
 }
 
 /*
diff --git a/src/include/nodes/pg_list.h b/src/include/nodes/pg_list.h
index 9df7fb30d3..3313b051ce 100644
--- a/src/include/nodes/pg_list.h
+++ b/src/include/nodes/pg_list.h
@@ -183,6 +183,20 @@ list_length(const List *l)
 		 (cell1) = lnext(cell1), (cell2) = lnext(cell2))
 
 /*
+ * for_both_cell -
+ *	  a convenience macro which loops through two lists starting from the
+ *	  specified cells of each. This macro loops through both lists at the same
+ *	  time, stopping when either list runs out of elements.  Depending on the
+ *	  requirements of the call site, it may also be wise to assert that the
+ *	  lengths of the two lists are equal, and initcell1 and initcell2 are at
+ *	  the same position in the respective lists.
+ */
+#define for_both_cell(cell1, initcell1, cell2, initcell2)	\
+	for ((cell1) = (initcell1), (cell2) = (initcell2);		\
+		 (cell1) != NULL && (cell2) != NULL;				\
+		 (cell1) = lnext(cell1), (cell2) = lnext(cell2))
+
+/*
  * forthree -
  *	  the same for three lists
  */
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 6163ed8117..af7090ba0d 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1828,3 +1828,93 @@ explain (costs off) select * from range_list_parted where a >= 30;
 
 drop table list_parted;
 drop table range_list_parted;
+-- check that constraint exclusion is able to cope with the partition
+-- constraint emitted for multi-column range partitioned tables
+create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
+create table mcrparted0 partition of mcrparted for values from (unbounded, unbounded, unbounded) to (1, 1, 1);
+create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to (10, 10, 10);
+create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
+create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20);
+create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (unbounded, unbounded, unbounded);
+explain (costs off) select * from mcrparted where a = 0;	-- scans mcrparted0
+          QUERY PLAN          
+------------------------------
+ Append
+   ->  Seq Scan on mcrparted0
+         Filter: (a = 0)
+(3 rows)
+
+explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5;	-- scans mcrparted1
+                 QUERY PLAN                  
+---------------------------------------------
+ Append
+   ->  Seq Scan on mcrparted1
+         Filter: ((a = 10) AND (abs(b) < 5))
+(3 rows)
+
+explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5;	-- scans mcrparted1, mcrparted2
+                 QUERY PLAN                  
+---------------------------------------------
+ Append
+   ->  Seq Scan on mcrparted1
+         Filter: ((a = 10) AND (abs(b) = 5))
+   ->  Seq Scan on mcrparted2
+         Filter: ((a = 10) AND (abs(b) = 5))
+(5 rows)
+
+explain (costs off) select * from mcrparted where abs(b) = 5;	-- scans all partitions
+          QUERY PLAN          
+------------------------------
+ Append
+   ->  Seq Scan on mcrparted0
+         Filter: (abs(b) = 5)
+   ->  Seq Scan on mcrparted1
+         Filter: (abs(b) = 5)
+   ->  Seq Scan on mcrparted2
+         Filter: (abs(b) = 5)
+   ->  Seq Scan on mcrparted3
+         Filter: (abs(b) = 5)
+   ->  Seq Scan on mcrparted5
+         Filter: (abs(b) = 5)
+(11 rows)
+
+explain (costs off) select * from mcrparted where a > -1;	-- scans all partitions
+             QUERY PLAN              
+-------------------------------------
+ Append
+   ->  Seq Scan on mcrparted0
+         Filter: (a > '-1'::integer)
+   ->  Seq Scan on mcrparted1
+         Filter: (a > '-1'::integer)
+   ->  Seq Scan on mcrparted2
+         Filter: (a > '-1'::integer)
+   ->  Seq Scan on mcrparted3
+         Filter: (a > '-1'::integer)
+   ->  Seq Scan on mcrparted4
+         Filter: (a > '-1'::integer)
+   ->  Seq Scan on mcrparted5
+         Filter: (a > '-1'::integer)
+(13 rows)
+
+explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c > 10;	-- scans mcrparted4
+                        QUERY PLAN                         
+-----------------------------------------------------------
+ Append
+   ->  Seq Scan on mcrparted4
+         Filter: ((c > 10) AND (a = 20) AND (abs(b) = 10))
+(3 rows)
+
+explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5
+               QUERY PLAN                
+-----------------------------------------
+ Append
+   ->  Seq Scan on mcrparted3
+         Filter: ((c > 20) AND (a = 20))
+   ->  Seq Scan on mcrparted4
+         Filter: ((c > 20) AND (a = 20))
+   ->  Seq Scan on mcrparted5
+         Filter: ((c > 20) AND (a = 20))
+(7 rows)
+
+drop table mcrparted;
diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out
index 6f34b1c640..c68b31415d 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -435,3 +435,62 @@ revoke all on key_desc from someone_else;
 revoke all on key_desc_1 from someone_else;
 drop role someone_else;
 drop table key_desc, key_desc_1;
+-- check multi-column range partitioning expression enforces the same
+-- constraint as what tuple-routing would determine it to be
+create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
+create table mcrparted0 partition of mcrparted for values from (unbounded, unbounded, unbounded) to (1, 1, 1);
+create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to (10, 10, 10);
+create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
+create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20);
+create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (unbounded, unbounded, unbounded);
+-- routed to mcrparted0
+insert into mcrparted values (0, 1, 1);
+insert into mcrparted0 values (0, 1, 1);
+-- routed to mcparted1
+insert into mcrparted values (9, 1000, 1);
+insert into mcrparted1 values (9, 1000, 1);
+insert into mcrparted values (10, 5, 1);
+insert into mcrparted1 values (10, 5, 1);
+-- routed to mcparted2
+insert into mcrparted values (10, 6, 1000);
+insert into mcrparted2 values (10, 6, 1000);
+insert into mcrparted values (10, 10, 9);
+insert into mcrparted2 values (10, 10, 9);
+-- no partition exists, nor does mcrparted2 accept it
+insert into mcrparted values (10, 10, 1000);
+ERROR:  no partition of relation "mcrparted" found for row
+DETAIL:  Partition key of the failing row contains (a, abs(b), c) = (10, 10, 1000).
+insert into mcrparted2 values (10, 10, 1000);
+ERROR:  new row for relation "mcrparted2" violates partition constraint
+DETAIL:  Failing row contains (10, 10, 1000).
+-- nor does mcrparted3
+insert into mcrparted3 values (10, 10, 1000);
+ERROR:  new row for relation "mcrparted3" violates partition constraint
+DETAIL:  Failing row contains (10, 10, 1000).
+-- routed to mcrparted5
+insert into mcrparted values (20, 20, 20);
+insert into mcrparted5 values (20, 20, 20);
+insert into mcrparted4 values (20, 20, 20);	-- error
+ERROR:  new row for relation "mcrparted4" violates partition constraint
+DETAIL:  Failing row contains (20, 20, 20).
+-- check rows
+select tableoid::regclass::text, * from mcrparted order by 1;
+  tableoid  | a  |  b   |  c   
+------------+----+------+------
+ mcrparted0 |  0 |    1 |    1
+ mcrparted0 |  0 |    1 |    1
+ mcrparted1 |  9 | 1000 |    1
+ mcrparted1 |  9 | 1000 |    1
+ mcrparted1 | 10 |    5 |    1
+ mcrparted1 | 10 |    5 |    1
+ mcrparted2 | 10 |    6 | 1000
+ mcrparted2 | 10 |    6 | 1000
+ mcrparted2 | 10 |   10 |    9
+ mcrparted2 | 10 |   10 |    9
+ mcrparted5 | 20 |   20 |   20
+ mcrparted5 | 20 |   20 |   20
+(12 rows)
+
+-- cleanup
+drop table mcrparted;
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index d43b75c4a7..7f34f43ec0 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -643,3 +643,21 @@ explain (costs off) select * from range_list_parted where a >= 30;
 
 drop table list_parted;
 drop table range_list_parted;
+
+-- check that constraint exclusion is able to cope with the partition
+-- constraint emitted for multi-column range partitioned tables
+create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
+create table mcrparted0 partition of mcrparted for values from (unbounded, unbounded, unbounded) to (1, 1, 1);
+create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to (10, 10, 10);
+create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
+create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20);
+create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (unbounded, unbounded, unbounded);
+explain (costs off) select * from mcrparted where a = 0;	-- scans mcrparted0
+explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5;	-- scans mcrparted1
+explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5;	-- scans mcrparted1, mcrparted2
+explain (costs off) select * from mcrparted where abs(b) = 5;	-- scans all partitions
+explain (costs off) select * from mcrparted where a > -1;	-- scans all partitions
+explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c > 10;	-- scans mcrparted4
+explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5
+drop table mcrparted;
diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql
index 020854c960..88509f9cce 100644
--- a/src/test/regress/sql/insert.sql
+++ b/src/test/regress/sql/insert.sql
@@ -289,3 +289,46 @@ revoke all on key_desc from someone_else;
 revoke all on key_desc_1 from someone_else;
 drop role someone_else;
 drop table key_desc, key_desc_1;
+
+-- check multi-column range partitioning expression enforces the same
+-- constraint as what tuple-routing would determine it to be
+create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
+create table mcrparted0 partition of mcrparted for values from (unbounded, unbounded, unbounded) to (1, 1, 1);
+create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to (10, 10, 10);
+create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
+create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20);
+create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (unbounded, unbounded, unbounded);
+
+-- routed to mcrparted0
+insert into mcrparted values (0, 1, 1);
+insert into mcrparted0 values (0, 1, 1);
+
+-- routed to mcparted1
+insert into mcrparted values (9, 1000, 1);
+insert into mcrparted1 values (9, 1000, 1);
+insert into mcrparted values (10, 5, 1);
+insert into mcrparted1 values (10, 5, 1);
+
+-- routed to mcparted2
+insert into mcrparted values (10, 6, 1000);
+insert into mcrparted2 values (10, 6, 1000);
+insert into mcrparted values (10, 10, 9);
+insert into mcrparted2 values (10, 10, 9);
+
+-- no partition exists, nor does mcrparted2 accept it
+insert into mcrparted values (10, 10, 1000);
+insert into mcrparted2 values (10, 10, 1000);
+-- nor does mcrparted3
+insert into mcrparted3 values (10, 10, 1000);
+
+-- routed to mcrparted5
+insert into mcrparted values (20, 20, 20);
+insert into mcrparted5 values (20, 20, 20);
+insert into mcrparted4 values (20, 20, 20);	-- error
+
+-- check rows
+select tableoid::regclass::text, * from mcrparted order by 1;
+
+-- cleanup
+drop table mcrparted;
-- 
2.11.0

From bc5d684664e3269183057a8bbae13b37fe4bf6e2 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Mon, 8 May 2017 10:21:19 +0900
Subject: [PATCH 3/3] Add pg_get_partition_constraintdef

To externally display a partition's implicit constraint.
---
 src/backend/catalog/partition.c            | 29 ++++++++++++++++++++++++
 src/backend/utils/adt/ruleutils.c          | 36 ++++++++++++++++++++++++++++++
 src/bin/psql/describe.c                    |  8 ++++++-
 src/include/catalog/partition.h            |  1 +
 src/include/catalog/pg_proc.h              |  2 ++
 src/test/regress/expected/create_table.out | 27 ++++++++++++++++++++++
 src/test/regress/expected/foreign_data.out |  3 +++
 src/test/regress/sql/create_table.sql      |  9 ++++++++
 8 files changed, 114 insertions(+), 1 deletion(-)

diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c
index bd4f0b67a4..5093b59391 100644
--- a/src/backend/catalog/partition.c
+++ b/src/backend/catalog/partition.c
@@ -978,6 +978,35 @@ RelationGetPartitionQual(Relation rel)
 }
 
 /*
+ * get_partition_qual_relid
+ *
+ * Returns an expression tree describing the passed-in relation's partition
+ * constraint.
+ */
+Expr *
+get_partition_qual_relid(Oid relid)
+{
+	Relation	rel = heap_open(relid, AccessShareLock);
+	Expr	   *result = NULL;
+	List	   *and_args;
+
+	/* Do the work only if this relation is a partition. */
+	if (rel->rd_rel->relispartition)
+	{
+		and_args = generate_partition_qual(rel);
+		if (list_length(and_args) > 1)
+			result = makeBoolExpr(AND_EXPR, and_args, -1);
+		else
+			result = linitial(and_args);
+	}
+
+	/* Keep the lock. */
+	heap_close(rel, NoLock);
+
+	return result;
+}
+
+/*
  * Append OIDs of rel's partitions to the list 'partoids' and for each OID,
  * append pointer rel to the list 'parents'.
  */
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index cbde1fff01..0472cc5ac0 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -24,6 +24,7 @@
 #include "access/sysattr.h"
 #include "catalog/dependency.h"
 #include "catalog/indexing.h"
+#include "catalog/partition.h"
 #include "catalog/pg_aggregate.h"
 #include "catalog/pg_am.h"
 #include "catalog/pg_authid.h"
@@ -1729,6 +1730,41 @@ pg_get_partkeydef_worker(Oid relid, int prettyFlags,
 }
 
 /*
+ * pg_get_partition_constraintdef
+ *
+ * Returns partition constraint expression as a string for the input relation
+ */
+Datum
+pg_get_partition_constraintdef(PG_FUNCTION_ARGS)
+{
+	Oid		relationId = PG_GETARG_OID(0);
+	Expr   *constr_expr;
+	int		prettyFlags;
+	List   *context;
+	char   *consrc;
+	StringInfoData buf;
+
+	constr_expr = get_partition_qual_relid(relationId);
+
+	/* Quick exit if not a partition */
+	if (constr_expr == NULL)
+		PG_RETURN_NULL();
+
+	/*
+	 * Deparse the constraint expression and return in in the form of a CHECK
+	 * constraint.
+	 */
+	prettyFlags = PRETTYFLAG_INDENT;
+	context = deparse_context_for(get_relation_name(relationId), relationId);
+	consrc = deparse_expression_pretty((Node *) constr_expr, context, false,
+									   false, prettyFlags, 0);
+	initStringInfo(&buf);
+	appendStringInfo(&buf, "CHECK (%s)", consrc);
+
+	PG_RETURN_TEXT_P(string_to_text(buf.data));
+}
+
+/*
  * pg_get_constraintdef
  *
  * Returns the definition for the constraint, ie, everything that needs to
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index dbfc7339e5..d04f843d0e 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1858,9 +1858,11 @@ describeOneTableDetails(const char *schemaname,
 		PGresult   *result;
 		char	   *parent_name;
 		char	   *partdef;
+		char	   *partconstraintdef;
 
 		printfPQExpBuffer(&buf,
-			 "SELECT inhparent::pg_catalog.regclass, pg_get_expr(c.relpartbound, inhrelid)"
+			 "SELECT inhparent::pg_catalog.regclass, pg_get_expr(c.relpartbound, inhrelid),"
+			 "		 pg_get_partition_constraintdef(inhrelid)"
 			 " FROM pg_catalog.pg_class c"
 			 " JOIN pg_catalog.pg_inherits"
 			 " ON c.oid = inhrelid"
@@ -1873,9 +1875,13 @@ describeOneTableDetails(const char *schemaname,
 		{
 			parent_name = PQgetvalue(result, 0, 0);
 			partdef = PQgetvalue(result, 0, 1);
+			partconstraintdef = PQgetvalue(result, 0, 2);
 			printfPQExpBuffer(&tmpbuf, _("Partition of: %s %s"), parent_name,
 						  partdef);
 			printTableAddFooter(&cont, tmpbuf.data);
+			printfPQExpBuffer(&tmpbuf, _("Partition constraint: %s"),
+							  partconstraintdef);
+			printTableAddFooter(&cont, tmpbuf.data);
 			PQclear(result);
 		}
 	}
diff --git a/src/include/catalog/partition.h b/src/include/catalog/partition.h
index 421644ca77..25fb0a0440 100644
--- a/src/include/catalog/partition.h
+++ b/src/include/catalog/partition.h
@@ -80,6 +80,7 @@ extern List *get_qual_from_partbound(Relation rel, Relation parent, Node *bound)
 extern List *map_partition_varattnos(List *expr, int target_varno,
 						Relation partrel, Relation parent);
 extern List *RelationGetPartitionQual(Relation rel);
+extern Expr *get_partition_qual_relid(Oid relid);
 
 /* For tuple routing */
 extern PartitionDispatch *RelationGetPartitionDispatchInfo(Relation rel,
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 82562add43..aa0ce791c0 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -1992,6 +1992,8 @@ DATA(insert OID = 3415 (  pg_get_statisticsextdef	   PGNSP PGUID 12 1 0 0 0 f f
 DESCR("index description");
 DATA(insert OID = 3352 (  pg_get_partkeydef    PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_partkeydef _null_ _null_ _null_ ));
 DESCR("partition key description");
+DATA(insert OID = 3403 (  pg_get_partition_constraintdef    PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_partition_constraintdef _null_ _null_ _null_ ));
+DESCR("partition constraint description");
 DATA(insert OID = 1662 (  pg_get_triggerdef    PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_triggerdef _null_ _null_ _null_ ));
 DESCR("trigger description");
 DATA(insert OID = 1387 (  pg_get_constraintdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_constraintdef _null_ _null_ _null_ ));
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 15d4ce591c..cb8c86a018 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -546,6 +546,7 @@ CREATE TABLE part_forced_oids PARTITION OF oids_parted FOR VALUES FROM (1) TO (1
 --------+---------+-----------+----------+---------+---------+--------------+-------------
  a      | integer |           | not null |         | plain   |              | 
 Partition of: oids_parted FOR VALUES FROM (1) TO (10)
+Partition constraint: CHECK (((a >= 1) AND (a < 10)))
 Has OIDs: yes
 
 DROP TABLE oids_parted, part_forced_oids;
@@ -650,6 +651,7 @@ CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
  a      | text    |           |          | 
  b      | integer |           | not null | 1
 Partition of: parted FOR VALUES IN ('b')
+Partition constraint: CHECK (((a IS NOT NULL) AND (a = ANY (ARRAY['b'::text]))))
 Check constraints:
     "check_a" CHECK (length(a) > 0)
     "part_b_b_check" CHECK (b >= 0)
@@ -662,11 +664,24 @@ Check constraints:
  a      | text    |           |          | 
  b      | integer |           | not null | 0
 Partition of: parted FOR VALUES IN ('c')
+Partition constraint: CHECK (((a IS NOT NULL) AND (a = ANY (ARRAY['c'::text]))))
 Partition key: RANGE (b)
 Check constraints:
     "check_a" CHECK (length(a) > 0)
 Number of partitions: 1 (Use \d+ to list them.)
 
+-- a level-2 partition's constraint will include the parent's expressions
+\d part_c_1_10
+            Table "public.part_c_1_10"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | text    |           |          | 
+ b      | integer |           | not null | 0
+Partition of: part_c FOR VALUES FROM (1) TO (10)
+Partition constraint: CHECK (((a IS NOT NULL) AND (a = ANY (ARRAY['c'::text])) AND (b >= 1) AND (b < 10)))
+Check constraints:
+    "check_a" CHECK (length(a) > 0)
+
 -- Show partition count in the parent's describe output
 -- Tempted to include \d+ output listing partitions with bound info but
 -- output could vary depending on the order in which partition oids are
@@ -682,6 +697,18 @@ Check constraints:
     "check_a" CHECK (length(a) > 0)
 Number of partitions: 3 (Use \d+ to list them.)
 
+-- show that an unbounded range partition has a CHECK (true) constraint
+CREATE TABLE range_parted4 (a int) PARTITION BY RANGE (a);
+CREATE TABLE unbounded_range_part PARTITION OF range_parted4 FOR VALUES FROM (UNBOUNDED) TO (UNBOUNDED);
+\d unbounded_range_part
+        Table "public.unbounded_range_part"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           | not null | 
+Partition of: range_parted4 FOR VALUES FROM (UNBOUNDED) TO (UNBOUNDED)
+Partition constraint: CHECK (true)
+
+drop table range_parted4;
 -- cleanup
 DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3;
 -- comments on partitioned tables columns
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index 1c7a7593f9..0009173382 100644
--- a/src/test/regress/expected/foreign_data.out
+++ b/src/test/regress/expected/foreign_data.out
@@ -1783,6 +1783,7 @@ Partitions: pt2_1 FOR VALUES IN (1)
  c2     | text    |           |          |         |             | extended |              | 
  c3     | date    |           |          |         |             | plain    |              | 
 Partition of: pt2 FOR VALUES IN (1)
+Partition constraint: CHECK (((c1 IS NOT NULL) AND (c1 = ANY (ARRAY[1]))))
 Server: s0
 FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
 
@@ -1853,6 +1854,7 @@ Partitions: pt2_1 FOR VALUES IN (1)
  c2     | text    |           |          |         |             | extended |              | 
  c3     | date    |           |          |         |             | plain    |              | 
 Partition of: pt2 FOR VALUES IN (1)
+Partition constraint: CHECK (((c1 IS NOT NULL) AND (c1 = ANY (ARRAY[1]))))
 Server: s0
 FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
 
@@ -1880,6 +1882,7 @@ Partitions: pt2_1 FOR VALUES IN (1)
  c2     | text    |           |          |         |             | extended |              | 
  c3     | date    |           | not null |         |             | plain    |              | 
 Partition of: pt2 FOR VALUES IN (1)
+Partition constraint: CHECK (((c1 IS NOT NULL) AND (c1 = ANY (ARRAY[1]))))
 Check constraints:
     "p21chk" CHECK (c2 <> ''::text)
 Server: s0
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index 95035c5947..76b079dc42 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -603,12 +603,21 @@ CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
 -- Both partition bound and partition key in describe output
 \d part_c
 
+-- a level-2 partition's constraint will include the parent's expressions
+\d part_c_1_10
+
 -- Show partition count in the parent's describe output
 -- Tempted to include \d+ output listing partitions with bound info but
 -- output could vary depending on the order in which partition oids are
 -- returned.
 \d parted
 
+-- show that an unbounded range partition has a CHECK (true) constraint
+CREATE TABLE range_parted4 (a int) PARTITION BY RANGE (a);
+CREATE TABLE unbounded_range_part PARTITION OF range_parted4 FOR VALUES FROM (UNBOUNDED) TO (UNBOUNDED);
+\d unbounded_range_part
+drop table range_parted4;
+
 -- cleanup
 DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3;
 
-- 
2.11.0

From 17ae801b3f3fa5e89ab9f2332a825382281522ad Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Tue, 2 May 2017 11:03:54 +0900
Subject: [PATCH 1/2] Emit "correct" range partition constraint expression

Currently emitted expression does not always describe the constraint
correctly, especially in the case of multi-column range key.

Code surrounding get_qual_for_*() has been rearranged a little to
move common code to a couple of new functions.

Original issue reported by Olaf Gawenda (olaf.gw@googlemail.com)
---
 src/backend/catalog/partition.c       | 733 ++++++++++++++++++++++++----------
 src/include/nodes/pg_list.h           |  14 +
 src/test/regress/expected/inherit.out |  90 +++++
 src/test/regress/expected/insert.out  |  59 +++
 src/test/regress/sql/inherit.sql      |  18 +
 src/test/regress/sql/insert.sql       |  43 ++
 6 files changed, 748 insertions(+), 209 deletions(-)

diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c
index 8641ae16a2..b05ffd2d90 100644
--- a/src/backend/catalog/partition.c
+++ b/src/backend/catalog/partition.c
@@ -118,10 +118,19 @@ static int32 qsort_partition_list_value_cmp(const void *a, const void *b,
 static int32 qsort_partition_rbound_cmp(const void *a, const void *b,
 						   void *arg);
 
-static List *get_qual_for_list(PartitionKey key, PartitionBoundSpec *spec);
-static List *get_qual_for_range(PartitionKey key, PartitionBoundSpec *spec);
 static Oid get_partition_operator(PartitionKey key, int col,
 					   StrategyNumber strategy, bool *need_relabel);
+static Expr* make_partition_op_expr(PartitionKey key, int keynum,
+					   uint16 strategy, Expr *arg1, Expr *arg2);
+static void get_range_key_properties(PartitionKey key, int keynum,
+						 PartitionRangeDatum *ldatum,
+						 PartitionRangeDatum *udatum,
+						 ListCell **partexprs_item,
+						 Expr **keyCol,
+						 Const **lower_val, Const **upper_val,
+						 NullTest **nulltest);
+static List *get_qual_for_list(PartitionKey key, PartitionBoundSpec *spec);
+static List *get_qual_for_range(PartitionKey key, PartitionBoundSpec *spec);
 static List *generate_partition_qual(Relation rel);
 
 static PartitionRangeBound *make_one_range_bound(PartitionKey key, int index,
@@ -1146,6 +1155,123 @@ RelationGetPartitionDispatchInfo(Relation rel, int lockmode,
 /* Module-local functions */
 
 /*
+ * get_partition_operator
+ *
+ * Return oid of the operator of given strategy for a given partition key
+ * column.
+ */
+static Oid
+get_partition_operator(PartitionKey key, int col, StrategyNumber strategy,
+					   bool *need_relabel)
+{
+	Oid			operoid;
+
+	/*
+	 * First check if there exists an operator of the given strategy, with
+	 * this column's type as both its lefttype and righttype, in the
+	 * partitioning operator family specified for the column.
+	 */
+	operoid = get_opfamily_member(key->partopfamily[col],
+								  key->parttypid[col],
+								  key->parttypid[col],
+								  strategy);
+
+	/*
+	 * If one doesn't exist, we must resort to using an operator in the same
+	 * opreator family but with the operator class declared input type.  It is
+	 * OK to do so, because the column's type is known to be binary-coercible
+	 * with the operator class input type (otherwise, the operator class in
+	 * question would not have been accepted as the partitioning operator
+	 * class).  We must however inform the caller to wrap the non-Const
+	 * expression with a RelabelType node to denote the implicit coercion. It
+	 * ensures that the resulting expression structurally matches similarly
+	 * processed expressions within the optimizer.
+	 */
+	if (!OidIsValid(operoid))
+	{
+		operoid = get_opfamily_member(key->partopfamily[col],
+									  key->partopcintype[col],
+									  key->partopcintype[col],
+									  strategy);
+		*need_relabel = true;
+	}
+	else
+		*need_relabel = false;
+
+	if (!OidIsValid(operoid))
+		elog(ERROR, "could not find operator for partitioning");
+
+	return operoid;
+}
+
+/*
+ * make_partition_op_expr
+ *		Returns an Expr for the given partition key column with arg1 and
+ *		arg2 as its leftop and rightop, respectively
+ */
+static Expr*
+make_partition_op_expr(PartitionKey key, int keynum,
+					   uint16 strategy, Expr *arg1, Expr *arg2)
+{
+	Oid		operoid;
+	bool	need_relabel = false;
+	Expr   *result = NULL;
+
+	/* Get the correct btree operator for this partitioning column */
+	operoid = get_partition_operator(key, keynum, strategy, &need_relabel);
+
+	/*
+	 * Chosen operator may be such that the non-Const operand needs to
+	 * be coerced, so apply the same; see the comment in
+	 * get_partition_operator().
+	 */
+	if (!IsA(arg1, Const) &&
+		(need_relabel ||
+		 key->partcollation[keynum] != key->parttypcoll[keynum]))
+		arg1 = (Expr *) makeRelabelType(arg1,
+										key->partopcintype[keynum],
+										-1,
+										key->partcollation[keynum],
+										COERCE_EXPLICIT_CAST);
+
+	/* Generate the actual expression */
+	switch (key->strategy)
+	{
+		case PARTITION_STRATEGY_LIST:
+		{
+			ScalarArrayOpExpr *saopexpr;
+
+			/* Build leftop = ANY (rightop) */
+			saopexpr = makeNode(ScalarArrayOpExpr);
+			saopexpr->opno = operoid;
+			saopexpr->opfuncid = get_opcode(operoid);
+			saopexpr->useOr = true;
+			saopexpr->inputcollid = key->partcollation[0];
+			saopexpr->args = list_make2(arg1, arg2);
+			saopexpr->location = -1;
+
+			result = (Expr *) saopexpr;
+			break;
+		}
+
+		case PARTITION_STRATEGY_RANGE:
+			result = make_opclause(operoid,
+								   BOOLOID,
+								   false,
+								   arg1, arg2,
+								   InvalidOid,
+								   key->partcollation[keynum]);
+			break;
+
+		default:
+			elog(ERROR, "invalid partitioning strategy");
+			break;
+	}
+
+	return result;
+}
+
+/*
  * get_qual_for_list
  *
  * Returns a list of expressions to use as a list partition's constraint.
@@ -1155,14 +1281,12 @@ get_qual_for_list(PartitionKey key, PartitionBoundSpec *spec)
 {
 	List	   *result;
 	ArrayExpr  *arr;
-	ScalarArrayOpExpr *opexpr;
+	Expr	   *opexpr;
 	ListCell   *cell,
 			   *prev,
 			   *next;
 	Expr	   *keyCol;
-	Oid			operoid;
-	bool		need_relabel,
-				list_has_null = false;
+	bool		list_has_null = false;
 	NullTest   *nulltest1 = NULL,
 			   *nulltest2 = NULL;
 
@@ -1233,24 +1357,9 @@ get_qual_for_list(PartitionKey key, PartitionBoundSpec *spec)
 	arr->multidims = false;
 	arr->location = -1;
 
-	/* Get the correct btree equality operator */
-	operoid = get_partition_operator(key, 0, BTEqualStrategyNumber,
-									 &need_relabel);
-	if (need_relabel || key->partcollation[0] != key->parttypcoll[0])
-		keyCol = (Expr *) makeRelabelType(keyCol,
-										  key->partopcintype[0],
-										  -1,
-										  key->partcollation[0],
-										  COERCE_EXPLICIT_CAST);
-
-	/* Build leftop = ANY (rightop) */
-	opexpr = makeNode(ScalarArrayOpExpr);
-	opexpr->opno = operoid;
-	opexpr->opfuncid = get_opcode(operoid);
-	opexpr->useOr = true;
-	opexpr->inputcollid = key->partcollation[0];
-	opexpr->args = list_make2(keyCol, arr);
-	opexpr->location = -1;
+	/* Generate the main expression, i.e., keyCol = ANY (arr) */
+	opexpr = make_partition_op_expr(key, 0, BTEqualStrategyNumber,
+									keyCol, (Expr *) arr);
 
 	if (nulltest1)
 		result = list_make2(nulltest1, opexpr);
@@ -1268,9 +1377,100 @@ get_qual_for_list(PartitionKey key, PartitionBoundSpec *spec)
 }
 
 /*
+ * get_range_key_properties
+ *		Returns range partition key information for a given column
+ *
+ * On return, *lower_val and *upper_val contain either a valid Const
+ * expression or NULL.  *nulltest contains a NullTest expression, or
+ * NULL.
+ */
+static void
+get_range_key_properties(PartitionKey key, int keynum,
+						 PartitionRangeDatum *ldatum,
+						 PartitionRangeDatum *udatum,
+						 ListCell **partexprs_item,
+						 Expr **keyCol,
+						 Const **lower_val, Const **upper_val,
+						 NullTest **nulltest)
+{
+	/* Partition key expression for this column */
+	if (key->partattrs[keynum] != 0)
+	{
+		*keyCol = (Expr *) makeVar(1,
+								  key->partattrs[keynum],
+								  key->parttypid[keynum],
+								  key->parttypmod[keynum],
+								  key->parttypcoll[keynum],
+								  0);
+	}
+	else
+	{
+		*keyCol = copyObject(lfirst(*partexprs_item));
+		*partexprs_item = lnext(*partexprs_item);
+	}
+
+	/*
+	 * A range-partitioned table does not allow partition keys to be null.
+	 * For simple columns, their NOT NULL constraint suffices for the
+	 * enforcement of non-nullability.  But for the expression keys, which
+	 * are still nullable, we must emit a IS NOT NULL expression.
+	 */
+	if (!IsA(*keyCol, Var))
+	{
+		*nulltest = makeNode(NullTest);
+		(*nulltest)->arg = *keyCol;
+		(*nulltest)->nulltesttype = IS_NOT_NULL;
+		(*nulltest)->argisrow = false;
+		(*nulltest)->location = -1;
+	}
+	else
+		*nulltest = NULL;
+
+	if (!ldatum->infinite)
+		*lower_val = (Const *) ldatum->value;
+	else
+		*lower_val = NULL;
+
+	if (!udatum->infinite)
+		*upper_val = (Const *) udatum->value;
+	else
+		*upper_val = NULL;
+}
+
+/*
  * get_qual_for_range
  *
- * Get a list of OpExpr's to use as a range partition's constraint.
+ * Get a list of expressions to use as a range partition's constraint.
+ * If there are multiple expressions, they are to be considered implicitly
+ * ANDed.
+ *
+ * For a multi-column range partition key, say (a, b, c), with (al, bl, cl)
+ * as the lower bound tuple and (au, bu, cu) as the upper bound tuple, we
+ * generate an expression tree of the following form:
+ *
+ *	(a > al OR (a = al AND b > bl) OR (a = al AND b = bl AND c >= cl))
+ *		AND
+ *	(a < au OR (a = au AND b < bu) OR (a = au AND b = bu AND c < cu))
+ *
+ * If b were an expression key instead of a simple column, we also append
+ * (b IS NOT NULL) to the AND's argument list.
+ *
+ * It is often the case that a prefix of lower and upper bound tuples contains
+ * the same values, for example, (al = au), in which case, we will emit an
+ * expression tree of the following form:
+ *
+ *	(a = al)
+ *		AND
+ *	(b > bl OR (b = bl AND c >= cl))
+ *		AND
+ *	(b < bu) OR (b = bu AND c < cu))
+ *
+ * In most common cases with only one partition column, say a, the following
+ * expression tree will be generated: a >= al AND a < au
+ *
+ * If all values of both lower and upper bounds are UNBOUNDED, the partition
+ * does not really have a constraint, except the IS NOT NULL constraint for
+ * any expression keys.  We append a constant-true expression in that case.
  */
 static List *
 get_qual_for_range(PartitionKey key, PartitionBoundSpec *spec)
@@ -1278,239 +1478,354 @@ get_qual_for_range(PartitionKey key, PartitionBoundSpec *spec)
 	List	   *result = NIL;
 	ListCell   *cell1,
 			   *cell2,
-			   *partexprs_item;
-	int			i;
+			   *partexprs_item,
+				partexprs_item_saved;
+	int			i,
+				j,
+				k,
+				l;
+	PartitionRangeDatum *ldatum,
+			   *udatum;
+	Expr	   *keyCol;
+	Const	   *lower_val,
+			   *upper_val;
+	NullTest   *nulltest;
+	List	   *lower_or_arms,
+			   *upper_or_arms;
+	List	   *lower_or_prev_arm_args,
+			   *upper_or_prev_arm_args;
+	int			maxnum_or_arms,
+				num_or_arms;
+	ListCell   *lower_or_start_datum,
+			   *upper_or_start_datum;
+	bool		nulltest_generated[PARTITION_MAX_KEYS];
+
+	lower_or_start_datum = list_head(spec->lowerdatums);
+	upper_or_start_datum = list_head(spec->upperdatums);
+	maxnum_or_arms = key->partnatts;
+	memset(nulltest_generated, 0, sizeof(nulltest_generated));
 
 	/*
-	 * Iterate over columns of the key, emitting an OpExpr for each using the
-	 * corresponding lower and upper datums as constant operands.
+	 * Iterate over the key columns and check if the corresponding lower and
+	 * upper datums are equal using the btree equality operator for the
+	 * column's type.  If equal, we emit single keyCol = common_value
+	 * expression.  Starting from the first column for which the corresponding
+	 * lower and upper bound datums are not equal, we generate OR expressions
+	 * as shown in the function's header comment.
 	 */
 	i = 0;
 	partexprs_item = list_head(key->partexprs);
 	forboth(cell1, spec->lowerdatums, cell2, spec->upperdatums)
 	{
-		PartitionRangeDatum *ldatum = lfirst(cell1),
-				   *udatum = lfirst(cell2);
-		Expr	   *keyCol;
-		Const	   *lower_val = NULL,
-				   *upper_val = NULL;
 		EState	   *estate;
 		MemoryContext oldcxt;
 		Expr	   *test_expr;
 		ExprState  *test_exprstate;
 		Datum		test_result;
 		bool		isNull;
-		bool		need_relabel = false;
-		Oid			operoid;
-		NullTest   *nulltest;
 
-		/* Left operand */
-		if (key->partattrs[i] != 0)
-		{
-			keyCol = (Expr *) makeVar(1,
-									  key->partattrs[i],
-									  key->parttypid[i],
-									  key->parttypmod[i],
-									  key->parttypcoll[i],
-									  0);
-		}
-		else
-		{
-			keyCol = copyObject(lfirst(partexprs_item));
-			partexprs_item = lnext(partexprs_item);
-		}
+		ldatum = lfirst(cell1);
+		udatum = lfirst(cell2);
 
 		/*
-		 * Emit a IS NOT NULL expression for non-Var keys, because whereas
-		 * simple attributes are covered by NOT NULL constraints, expression
-		 * keys are still nullable which is not acceptable in case of range
-		 * partitioning.
+		 * Since get_range_key_properties() modifies partexprs_item, and we
+		 * might need to start over from the previous expression in the
+		 * later part of this functiom, save away the current value.
 		 */
-		if (!IsA(keyCol, Var))
+		if (partexprs_item)
+			partexprs_item_saved = *partexprs_item;
+		get_range_key_properties(key, i, ldatum, udatum,
+								 &partexprs_item,
+								 &keyCol,
+								 &lower_val, &upper_val,
+								 &nulltest);
+
+		if (nulltest && !nulltest_generated[i])
 		{
-			nulltest = makeNode(NullTest);
-			nulltest->arg = keyCol;
-			nulltest->nulltesttype = IS_NOT_NULL;
-			nulltest->argisrow = false;
-			nulltest->location = -1;
 			result = lappend(result, nulltest);
+			nulltest_generated[i] = true;
 		}
 
 		/*
-		 * Stop at this column if either of lower or upper datum is infinite,
-		 * but do emit an OpExpr for the non-infinite datum.
+		 * If either or both of lower_val and upper_val is NULL, they are
+		 * unequal, because being NULL means the column is unbounded in the
+		 * respective direction.
 		 */
-		if (!ldatum->infinite)
-			lower_val = (Const *) ldatum->value;
-		if (!udatum->infinite)
-			upper_val = (Const *) udatum->value;
+		if (!lower_val || !upper_val)
+			break;
+
+		/* Create the test expression */
+		estate = CreateExecutorState();
+		oldcxt = MemoryContextSwitchTo(estate->es_query_cxt);
+		test_expr = make_partition_op_expr(key, i, BTEqualStrategyNumber,
+										   (Expr *) lower_val,
+										   (Expr *) upper_val);
+		fix_opfuncids((Node *) test_expr);
+		test_exprstate = ExecInitExpr(test_expr, NULL);
+		test_result = ExecEvalExprSwitchContext(test_exprstate,
+										  GetPerTupleExprContext(estate),
+												&isNull);
+		MemoryContextSwitchTo(oldcxt);
+		FreeExecutorState(estate);
+
+		/* If not equal, go generate the OR expressions */
+		if (!DatumGetBool(test_result))
+			break;
+
+		/* Equal, so generate keyCol = lower_val expression */
 
 		/*
-		 * If lower_val and upper_val are both finite and happen to be equal,
-		 * emit only (keyCol = lower_val) for this column, because all rows in
-		 * this partition could only ever contain this value (ie, lower_val)
-		 * in the current partitioning column.  We must consider further
-		 * columns because the above condition does not fully constrain the
-		 * rows of this partition.
+		 * This can never be true for the last key column, because such a
+		 * range partition would never be allowed to be defined (it would
+		 * have an empty range otherwise).
 		 */
-		if (lower_val && upper_val)
+		if (i == key->partnatts - 1)
+			elog(ERROR, "invalid range bound specification");
+
+		result = lappend(result,
+						 make_partition_op_expr(key, i, BTEqualStrategyNumber,
+												keyCol, (Expr *) lower_val));
+
+		i++;
+	}
+
+	/* First pair of lower_val and upper_val that are not equal. */
+	lower_or_start_datum = cell1;
+	upper_or_start_datum = cell2;
+
+	/* OR will have as many arms as there are key columns left. */
+	k = l = i;
+	maxnum_or_arms = key->partnatts - i;
+	num_or_arms = 0;
+	lower_or_arms = upper_or_arms = NIL;
+	lower_or_prev_arm_args = upper_or_prev_arm_args = NIL;
+	while (num_or_arms < maxnum_or_arms)
+	{
+		List	*lower_or_arm_args = NIL,
+				*upper_or_arm_args = NIL;
+		Expr	*lower_or_arm,
+				*upper_or_arm;
+
+		j = i;
+		partexprs_item = &partexprs_item_saved;
+		for_both_cell(cell1, lower_or_start_datum, cell2, upper_or_start_datum)
 		{
-			/* Get the correct btree equality operator for the test */
-			operoid = get_partition_operator(key, i, BTEqualStrategyNumber,
-											 &need_relabel);
-
-			/* Create the test expression */
-			estate = CreateExecutorState();
-			oldcxt = MemoryContextSwitchTo(estate->es_query_cxt);
-			test_expr = make_opclause(operoid,
-									  BOOLOID,
-									  false,
-									  (Expr *) lower_val,
-									  (Expr *) upper_val,
-									  InvalidOid,
-									  key->partcollation[i]);
-			fix_opfuncids((Node *) test_expr);
-			test_exprstate = ExecInitExpr(test_expr, NULL);
-			test_result = ExecEvalExprSwitchContext(test_exprstate,
-											  GetPerTupleExprContext(estate),
-													&isNull);
-			MemoryContextSwitchTo(oldcxt);
-			FreeExecutorState(estate);
+			ldatum = lfirst(cell1);
+			udatum = lfirst(cell2);
+			get_range_key_properties(key, j, ldatum, udatum,
+									 &partexprs_item,
+									 &keyCol,
+									 &lower_val, &upper_val,
+									 &nulltest);
+
+			if (nulltest && !nulltest_generated[j])
+			{
+				result = lappend(result, nulltest);
+				nulltest_generated[j] = true;
+			}
 
-			if (DatumGetBool(test_result))
+			if (lower_val)
 			{
-				/* This can never be, but it's better to make sure */
-				if (i == key->partnatts - 1)
-					elog(ERROR, "invalid range bound specification");
-
-				if (need_relabel || key->partcollation[i] != key->parttypcoll[i])
-					keyCol = (Expr *) makeRelabelType(keyCol,
-													  key->partopcintype[i],
-													  -1,
-													  key->partcollation[i],
-													  COERCE_EXPLICIT_CAST);
-				result = lappend(result,
-								 make_opclause(operoid,
-											   BOOLOID,
-											   false,
-											   keyCol,
-											   (Expr *) lower_val,
-											   InvalidOid,
-											   key->partcollation[i]));
-
-				/* Go over to consider the next column. */
-				i++;
-				continue;
+				uint16	strategy;
+
+				/*
+				 * For the non-last columns of this arm, use the equality
+				 * operator.
+				 */
+				if (j - i < num_or_arms)
+					strategy = BTEqualStrategyNumber;
+				else
+					/* Consider that the lower bound is inclusive */
+					strategy = (j == key->partnatts - 1)
+										? BTGreaterEqualStrategyNumber
+										: BTGreaterStrategyNumber;
+
+				lower_or_arm_args = lappend(lower_or_arm_args,
+											make_partition_op_expr(key, j,
+																   strategy,
+																   keyCol,
+														(Expr *) lower_val));
 			}
+
+			if (upper_val)
+			{
+				uint16	strategy;
+
+				/*
+				 * For the non-last columns of this arm, use the equality
+				 * operator.
+				 */
+				if (j - i < num_or_arms)
+					strategy = BTEqualStrategyNumber;
+				else
+					strategy = BTLessStrategyNumber;
+
+				upper_or_arm_args = lappend(upper_or_arm_args,
+											make_partition_op_expr(key, j,
+																   strategy,
+																   keyCol,
+														(Expr *) upper_val));
+
+			}
+
+			/* Did we generate enough of OR's arguments? */
+			++j;
+			if (j - i > num_or_arms)
+				break;
 		}
 
 		/*
-		 * We can say here that lower_val != upper_val.  Emit expressions
-		 * (keyCol >= lower_val) and (keyCol < upper_val), then stop.
+		 * Check if we needn't really add these as separate arms of the
+		 * respective OR expressions.  For example, if this arm didn't add
+		 * an OpExpr for the next column because it is unbounded, then we
+		 * don't really need to add this as the new arm.  Instead we modify
+		 * the previous arm's last OpExpr to consider equality by replacing
+		 * its opno with appropriate operator.
 		 */
-		if (lower_val)
+		if (lower_or_arm_args != NIL)
 		{
-			operoid = get_partition_operator(key, i,
-											 BTGreaterEqualStrategyNumber,
-											 &need_relabel);
-
-			if (need_relabel || key->partcollation[i] != key->parttypcoll[i])
-				keyCol = (Expr *) makeRelabelType(keyCol,
-												  key->partopcintype[i],
-												  -1,
-												  key->partcollation[i],
-												  COERCE_EXPLICIT_CAST);
-			result = lappend(result,
-							 make_opclause(operoid,
-										   BOOLOID,
-										   false,
-										   keyCol,
-										   (Expr *) lower_val,
-										   InvalidOid,
-										   key->partcollation[i]));
-		}
+			bool	consider_cur_arm = true;
 
-		if (upper_val)
-		{
-			operoid = get_partition_operator(key, i,
-											 BTLessStrategyNumber,
-											 &need_relabel);
+			/*
+			 * List length comparison with previous suffices to conclude
+			 * that no new column was introduced in this arm.
+			 */
+			if (lower_or_prev_arm_args != NIL &&
+				list_length(lower_or_arm_args) ==
+				list_length(lower_or_prev_arm_args))
+			{
+				OpExpr   *prev_arm_last,
+						 *cur_arm_last;
+				List	 *op_infos;
+				ListCell *opic;
+				OpBtreeInterpretation *cur_op_intp;
+				Oid		newopno;
 
-			if (need_relabel || key->partcollation[i] != key->parttypcoll[i])
-				keyCol = (Expr *) makeRelabelType(keyCol,
-												  key->partopcintype[i],
-												  -1,
-												  key->partcollation[i],
-												  COERCE_EXPLICIT_CAST);
+				prev_arm_last = (OpExpr *) llast(lower_or_prev_arm_args);
+				cur_arm_last = (OpExpr *) llast(lower_or_arm_args);
 
-			result = lappend(result,
-							 make_opclause(operoid,
-										   BOOLOID,
-										   false,
-										   keyCol,
-										   (Expr *) upper_val,
-										   InvalidOid,
-										   key->partcollation[i]));
+				/*
+				 * Must choose the correct replacement operator, that is,
+				 * figure out the opfamily, lefttype and righttype of the
+				 * current operator, which when fed into get_opfamily_member()
+				 * with the new strategy will give us the intended operator.
+				 */
+				op_infos = get_op_btree_interpretation(cur_arm_last->opno);
+				foreach(opic, op_infos)
+				{
+					OpBtreeInterpretation *intp = lfirst(opic);
+
+					if (intp->opfamily_id == key->partopfamily[k-1])
+					{
+						cur_op_intp = intp;
+						break;
+					}
+				}
+
+				newopno = get_opfamily_member(cur_op_intp->opfamily_id,
+											  cur_op_intp->oplefttype,
+											  cur_op_intp->oprighttype,
+											  BTGreaterEqualStrategyNumber);
+				if (!OidIsValid(newopno))
+					elog(ERROR, "no >= operator found in opfamily %u",
+						 cur_op_intp->opfamily_id);
+				prev_arm_last->opno = newopno;
+				consider_cur_arm = false;
+			}
+
+			lower_or_prev_arm_args = lower_or_arm_args;
+
+			if (consider_cur_arm)
+			{
+				lower_or_arm = list_length(lower_or_arm_args) > 1
+							  ? makeBoolExpr(AND_EXPR, lower_or_arm_args, -1)
+							  : linitial(lower_or_arm_args);
+				lower_or_arms = lappend(lower_or_arms, lower_or_arm);
+				++k;	/* latest column in the lower OR expression */
+			}
 		}
 
-		/*
-		 * We can stop at this column, because we would not have checked the
-		 * next column when routing a given row into this partition.
-		 */
-		break;
-	}
+		if (upper_or_arm_args != NIL)
+		{
+			bool	consider_cur_arm = true;
 
-	return result;
-}
+			/* Check if the new arm didn't add check on a new column */
+			if (upper_or_prev_arm_args != NIL &&
+				list_length(upper_or_arm_args) ==
+				list_length(upper_or_prev_arm_args))
+			{
+				OpExpr   *prev_arm_last,
+						 *cur_arm_last;
+				List	 *op_infos;
+				ListCell *opic;
+				OpBtreeInterpretation *cur_op_intp;
+				Oid		newopno;
+
+				prev_arm_last = (OpExpr *) llast(upper_or_prev_arm_args);
+				cur_arm_last = (OpExpr *) llast(upper_or_arm_args);
+				op_infos = get_op_btree_interpretation(cur_arm_last->opno);
+				foreach(opic, op_infos)
+				{
+					OpBtreeInterpretation *intp = lfirst(opic);
 
-/*
- * get_partition_operator
- *
- * Return oid of the operator of given strategy for a given partition key
- * column.
- */
-static Oid
-get_partition_operator(PartitionKey key, int col, StrategyNumber strategy,
-					   bool *need_relabel)
-{
-	Oid			operoid;
+					if (intp->opfamily_id == key->partopfamily[l-1])
+					{
+						cur_op_intp = intp;
+						break;
+					}
+				}
 
-	/*
-	 * First check if there exists an operator of the given strategy, with
-	 * this column's type as both its lefttype and righttype, in the
-	 * partitioning operator family specified for the column.
-	 */
-	operoid = get_opfamily_member(key->partopfamily[col],
-								  key->parttypid[col],
-								  key->parttypid[col],
-								  strategy);
+				newopno = get_opfamily_member(cur_op_intp->opfamily_id,
+											  cur_op_intp->oplefttype,
+											  cur_op_intp->oprighttype,
+											  BTLessEqualStrategyNumber);
+				if (!OidIsValid(newopno))
+					elog(ERROR, "no <= operator found in opfamily %u",
+						 cur_op_intp->opfamily_id);
+				prev_arm_last->opno = newopno;
+				consider_cur_arm = false;
+			}
+			upper_or_prev_arm_args = upper_or_arm_args;
+
+			if (consider_cur_arm)
+			{
+				upper_or_arm = list_length(upper_or_arm_args) > 1
+							  ? makeBoolExpr(AND_EXPR, upper_or_arm_args, -1)
+							  : linitial(upper_or_arm_args);
+				upper_or_arms = lappend(upper_or_arms, upper_or_arm);
+				++l;	/* latest column in the upper OR expression */
+			}
+		}
+
+		++num_or_arms;
+	}
 
 	/*
-	 * If one doesn't exist, we must resort to using an operator in the same
-	 * opreator family but with the operator class declared input type.  It is
-	 * OK to do so, because the column's type is known to be binary-coercible
-	 * with the operator class input type (otherwise, the operator class in
-	 * question would not have been accepted as the partitioning operator
-	 * class).  We must however inform the caller to wrap the non-Const
-	 * expression with a RelabelType node to denote the implicit coercion. It
-	 * ensures that the resulting expression structurally matches similarly
-	 * processed expressions within the optimizer.
+	 * Generate the OR expressions for each of lower and upper bounds (if
+	 * required), and append to the list of implicitly ANDed list of
+	 * expressions.
+	 *
+	 * If both lower_or_arms and upper_or_arms are empty, we append a
+	 * constant-true expression.  That happens if all of the literal values
+	 * in both the lower and upper bound lists are UNBOUNDED.
 	 */
-	if (!OidIsValid(operoid))
+	if (lower_or_arms == NIL && upper_or_arms == NIL)
+		result = lappend(result, makeBoolConst(true, false));
+	else
 	{
-		operoid = get_opfamily_member(key->partopfamily[col],
-									  key->partopcintype[col],
-									  key->partopcintype[col],
-									  strategy);
-		*need_relabel = true;
+		if (lower_or_arms != NIL)
+			result = lappend(result,
+							 list_length(lower_or_arms) > 1
+								? makeBoolExpr(OR_EXPR, lower_or_arms, -1)
+								: linitial(lower_or_arms));
+		if (upper_or_arms != NIL)
+			result = lappend(result,
+							 list_length(upper_or_arms) > 1
+								? makeBoolExpr(OR_EXPR, upper_or_arms, -1)
+								: linitial(upper_or_arms));
 	}
-	else
-		*need_relabel = false;
 
-	if (!OidIsValid(operoid))
-		elog(ERROR, "could not find operator for partitioning");
-
-	return operoid;
+	return result;
 }
 
 /*
diff --git a/src/include/nodes/pg_list.h b/src/include/nodes/pg_list.h
index 9df7fb30d3..3313b051ce 100644
--- a/src/include/nodes/pg_list.h
+++ b/src/include/nodes/pg_list.h
@@ -183,6 +183,20 @@ list_length(const List *l)
 		 (cell1) = lnext(cell1), (cell2) = lnext(cell2))
 
 /*
+ * for_both_cell -
+ *	  a convenience macro which loops through two lists starting from the
+ *	  specified cells of each. This macro loops through both lists at the same
+ *	  time, stopping when either list runs out of elements.  Depending on the
+ *	  requirements of the call site, it may also be wise to assert that the
+ *	  lengths of the two lists are equal, and initcell1 and initcell2 are at
+ *	  the same position in the respective lists.
+ */
+#define for_both_cell(cell1, initcell1, cell2, initcell2)	\
+	for ((cell1) = (initcell1), (cell2) = (initcell2);		\
+		 (cell1) != NULL && (cell2) != NULL;				\
+		 (cell1) = lnext(cell1), (cell2) = lnext(cell2))
+
+/*
  * forthree -
  *	  the same for three lists
  */
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 6163ed8117..af7090ba0d 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1828,3 +1828,93 @@ explain (costs off) select * from range_list_parted where a >= 30;
 
 drop table list_parted;
 drop table range_list_parted;
+-- check that constraint exclusion is able to cope with the partition
+-- constraint emitted for multi-column range partitioned tables
+create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
+create table mcrparted0 partition of mcrparted for values from (unbounded, unbounded, unbounded) to (1, 1, 1);
+create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to (10, 10, 10);
+create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
+create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20);
+create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (unbounded, unbounded, unbounded);
+explain (costs off) select * from mcrparted where a = 0;	-- scans mcrparted0
+          QUERY PLAN          
+------------------------------
+ Append
+   ->  Seq Scan on mcrparted0
+         Filter: (a = 0)
+(3 rows)
+
+explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5;	-- scans mcrparted1
+                 QUERY PLAN                  
+---------------------------------------------
+ Append
+   ->  Seq Scan on mcrparted1
+         Filter: ((a = 10) AND (abs(b) < 5))
+(3 rows)
+
+explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5;	-- scans mcrparted1, mcrparted2
+                 QUERY PLAN                  
+---------------------------------------------
+ Append
+   ->  Seq Scan on mcrparted1
+         Filter: ((a = 10) AND (abs(b) = 5))
+   ->  Seq Scan on mcrparted2
+         Filter: ((a = 10) AND (abs(b) = 5))
+(5 rows)
+
+explain (costs off) select * from mcrparted where abs(b) = 5;	-- scans all partitions
+          QUERY PLAN          
+------------------------------
+ Append
+   ->  Seq Scan on mcrparted0
+         Filter: (abs(b) = 5)
+   ->  Seq Scan on mcrparted1
+         Filter: (abs(b) = 5)
+   ->  Seq Scan on mcrparted2
+         Filter: (abs(b) = 5)
+   ->  Seq Scan on mcrparted3
+         Filter: (abs(b) = 5)
+   ->  Seq Scan on mcrparted5
+         Filter: (abs(b) = 5)
+(11 rows)
+
+explain (costs off) select * from mcrparted where a > -1;	-- scans all partitions
+             QUERY PLAN              
+-------------------------------------
+ Append
+   ->  Seq Scan on mcrparted0
+         Filter: (a > '-1'::integer)
+   ->  Seq Scan on mcrparted1
+         Filter: (a > '-1'::integer)
+   ->  Seq Scan on mcrparted2
+         Filter: (a > '-1'::integer)
+   ->  Seq Scan on mcrparted3
+         Filter: (a > '-1'::integer)
+   ->  Seq Scan on mcrparted4
+         Filter: (a > '-1'::integer)
+   ->  Seq Scan on mcrparted5
+         Filter: (a > '-1'::integer)
+(13 rows)
+
+explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c > 10;	-- scans mcrparted4
+                        QUERY PLAN                         
+-----------------------------------------------------------
+ Append
+   ->  Seq Scan on mcrparted4
+         Filter: ((c > 10) AND (a = 20) AND (abs(b) = 10))
+(3 rows)
+
+explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5
+               QUERY PLAN                
+-----------------------------------------
+ Append
+   ->  Seq Scan on mcrparted3
+         Filter: ((c > 20) AND (a = 20))
+   ->  Seq Scan on mcrparted4
+         Filter: ((c > 20) AND (a = 20))
+   ->  Seq Scan on mcrparted5
+         Filter: ((c > 20) AND (a = 20))
+(7 rows)
+
+drop table mcrparted;
diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out
index 6f34b1c640..02429a37e3 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -435,3 +435,62 @@ revoke all on key_desc from someone_else;
 revoke all on key_desc_1 from someone_else;
 drop role someone_else;
 drop table key_desc, key_desc_1;
+-- check multi-column range partitioning expression enforces the same
+-- constraint as what tuple-routing would determine it to be
+create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
+create table mcrparted0 partition of mcrparted for values from (unbounded, unbounded, unbounded) to (1, unbounded, unbounded);
+create table mcrparted1 partition of mcrparted for values from (2, 1, unbounded) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 6, unbounded) to (10, unbounded, unbounded);
+create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
+create table mcrparted4 partition of mcrparted for values from (21, unbounded, unbounded) to (30, 20, unbounded);
+create table mcrparted5 partition of mcrparted for values from (30, 21, 20) to (unbounded, unbounded, unbounded);
+-- routed to mcrparted0
+insert into mcrparted values (0, 1, 1);
+insert into mcrparted0 values (0, 1, 1);
+-- routed to mcparted1
+insert into mcrparted values (9, 1000, 1);
+insert into mcrparted1 values (9, 1000, 1);
+insert into mcrparted values (10, 5, -1);
+insert into mcrparted1 values (10, 5, -1);
+insert into mcrparted values (2, 1, 0);
+insert into mcrparted1 values (2, 1, 0);
+-- routed to mcparted2
+insert into mcrparted values (10, 6, 1000);
+insert into mcrparted2 values (10, 6, 1000);
+insert into mcrparted values (10, 1000, 1000);
+insert into mcrparted2 values (10, 1000, 1000);
+-- no partition exists, nor does mcrparted3 accept it
+insert into mcrparted values (11, 1, -1);
+ERROR:  no partition of relation "mcrparted" found for row
+DETAIL:  Partition key of the failing row contains (a, abs(b), c) = (11, 1, -1).
+insert into mcrparted3 values (11, 1, -1);
+ERROR:  new row for relation "mcrparted3" violates partition constraint
+DETAIL:  Failing row contains (11, 1, -1).
+-- routed to mcrparted5
+insert into mcrparted values (30, 21, 20);
+insert into mcrparted5 values (30, 21, 20);
+insert into mcrparted4 values (30, 21, 20);	-- error
+ERROR:  new row for relation "mcrparted4" violates partition constraint
+DETAIL:  Failing row contains (30, 21, 20).
+-- check rows
+select tableoid::regclass::text, * from mcrparted order by 1;
+  tableoid  | a  |  b   |  c   
+------------+----+------+------
+ mcrparted0 |  0 |    1 |    1
+ mcrparted0 |  0 |    1 |    1
+ mcrparted1 |  9 | 1000 |    1
+ mcrparted1 |  9 | 1000 |    1
+ mcrparted1 | 10 |    5 |   -1
+ mcrparted1 | 10 |    5 |   -1
+ mcrparted1 |  2 |    1 |    0
+ mcrparted1 |  2 |    1 |    0
+ mcrparted2 | 10 |    6 | 1000
+ mcrparted2 | 10 |    6 | 1000
+ mcrparted2 | 10 | 1000 | 1000
+ mcrparted2 | 10 | 1000 | 1000
+ mcrparted5 | 30 |   21 |   20
+ mcrparted5 | 30 |   21 |   20
+(14 rows)
+
+-- cleanup
+drop table mcrparted;
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index d43b75c4a7..7f34f43ec0 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -643,3 +643,21 @@ explain (costs off) select * from range_list_parted where a >= 30;
 
 drop table list_parted;
 drop table range_list_parted;
+
+-- check that constraint exclusion is able to cope with the partition
+-- constraint emitted for multi-column range partitioned tables
+create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
+create table mcrparted0 partition of mcrparted for values from (unbounded, unbounded, unbounded) to (1, 1, 1);
+create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to (10, 10, 10);
+create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
+create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20);
+create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (unbounded, unbounded, unbounded);
+explain (costs off) select * from mcrparted where a = 0;	-- scans mcrparted0
+explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5;	-- scans mcrparted1
+explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5;	-- scans mcrparted1, mcrparted2
+explain (costs off) select * from mcrparted where abs(b) = 5;	-- scans all partitions
+explain (costs off) select * from mcrparted where a > -1;	-- scans all partitions
+explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c > 10;	-- scans mcrparted4
+explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5
+drop table mcrparted;
diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql
index 020854c960..db8967bad7 100644
--- a/src/test/regress/sql/insert.sql
+++ b/src/test/regress/sql/insert.sql
@@ -289,3 +289,46 @@ revoke all on key_desc from someone_else;
 revoke all on key_desc_1 from someone_else;
 drop role someone_else;
 drop table key_desc, key_desc_1;
+
+-- check multi-column range partitioning expression enforces the same
+-- constraint as what tuple-routing would determine it to be
+create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
+create table mcrparted0 partition of mcrparted for values from (unbounded, unbounded, unbounded) to (1, unbounded, unbounded);
+create table mcrparted1 partition of mcrparted for values from (2, 1, unbounded) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 6, unbounded) to (10, unbounded, unbounded);
+create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
+create table mcrparted4 partition of mcrparted for values from (21, unbounded, unbounded) to (30, 20, unbounded);
+create table mcrparted5 partition of mcrparted for values from (30, 21, 20) to (unbounded, unbounded, unbounded);
+
+-- routed to mcrparted0
+insert into mcrparted values (0, 1, 1);
+insert into mcrparted0 values (0, 1, 1);
+
+-- routed to mcparted1
+insert into mcrparted values (9, 1000, 1);
+insert into mcrparted1 values (9, 1000, 1);
+insert into mcrparted values (10, 5, -1);
+insert into mcrparted1 values (10, 5, -1);
+insert into mcrparted values (2, 1, 0);
+insert into mcrparted1 values (2, 1, 0);
+
+-- routed to mcparted2
+insert into mcrparted values (10, 6, 1000);
+insert into mcrparted2 values (10, 6, 1000);
+insert into mcrparted values (10, 1000, 1000);
+insert into mcrparted2 values (10, 1000, 1000);
+
+-- no partition exists, nor does mcrparted3 accept it
+insert into mcrparted values (11, 1, -1);
+insert into mcrparted3 values (11, 1, -1);
+
+-- routed to mcrparted5
+insert into mcrparted values (30, 21, 20);
+insert into mcrparted5 values (30, 21, 20);
+insert into mcrparted4 values (30, 21, 20);	-- error
+
+-- check rows
+select tableoid::regclass::text, * from mcrparted order by 1;
+
+-- cleanup
+drop table mcrparted;
-- 
2.11.0

From 17da90561abdf911e57e2be58d1fce4b1e62fbb3 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Mon, 8 May 2017 10:21:19 +0900
Subject: [PATCH 2/2] Add pg_get_partition_constraintdef

To externally display a partition's implicit constraint.
---
 src/backend/catalog/partition.c            | 29 ++++++++++
 src/backend/utils/adt/ruleutils.c          | 36 ++++++++++++
 src/bin/psql/describe.c                    | 37 +++++++++++--
 src/include/catalog/partition.h            |  1 +
 src/include/catalog/pg_proc.h              |  2 +
 src/test/regress/expected/create_table.out | 89 +++++++++++++++++++++++++-----
 src/test/regress/expected/foreign_data.out |  3 +
 src/test/regress/sql/create_table.sql      | 20 ++++++-
 8 files changed, 196 insertions(+), 21 deletions(-)

diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c
index b05ffd2d90..801263100a 100644
--- a/src/backend/catalog/partition.c
+++ b/src/backend/catalog/partition.c
@@ -978,6 +978,35 @@ RelationGetPartitionQual(Relation rel)
 }
 
 /*
+ * get_partition_qual_relid
+ *
+ * Returns an expression tree describing the passed-in relation's partition
+ * constraint.
+ */
+Expr *
+get_partition_qual_relid(Oid relid)
+{
+	Relation	rel = heap_open(relid, AccessShareLock);
+	Expr	   *result = NULL;
+	List	   *and_args;
+
+	/* Do the work only if this relation is a partition. */
+	if (rel->rd_rel->relispartition)
+	{
+		and_args = generate_partition_qual(rel);
+		if (list_length(and_args) > 1)
+			result = makeBoolExpr(AND_EXPR, and_args, -1);
+		else
+			result = linitial(and_args);
+	}
+
+	/* Keep the lock. */
+	heap_close(rel, NoLock);
+
+	return result;
+}
+
+/*
  * Append OIDs of rel's partitions to the list 'partoids' and for each OID,
  * append pointer rel to the list 'parents'.
  */
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index cbde1fff01..0472cc5ac0 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -24,6 +24,7 @@
 #include "access/sysattr.h"
 #include "catalog/dependency.h"
 #include "catalog/indexing.h"
+#include "catalog/partition.h"
 #include "catalog/pg_aggregate.h"
 #include "catalog/pg_am.h"
 #include "catalog/pg_authid.h"
@@ -1729,6 +1730,41 @@ pg_get_partkeydef_worker(Oid relid, int prettyFlags,
 }
 
 /*
+ * pg_get_partition_constraintdef
+ *
+ * Returns partition constraint expression as a string for the input relation
+ */
+Datum
+pg_get_partition_constraintdef(PG_FUNCTION_ARGS)
+{
+	Oid		relationId = PG_GETARG_OID(0);
+	Expr   *constr_expr;
+	int		prettyFlags;
+	List   *context;
+	char   *consrc;
+	StringInfoData buf;
+
+	constr_expr = get_partition_qual_relid(relationId);
+
+	/* Quick exit if not a partition */
+	if (constr_expr == NULL)
+		PG_RETURN_NULL();
+
+	/*
+	 * Deparse the constraint expression and return in in the form of a CHECK
+	 * constraint.
+	 */
+	prettyFlags = PRETTYFLAG_INDENT;
+	context = deparse_context_for(get_relation_name(relationId), relationId);
+	consrc = deparse_expression_pretty((Node *) constr_expr, context, false,
+									   false, prettyFlags, 0);
+	initStringInfo(&buf);
+	appendStringInfo(&buf, "CHECK (%s)", consrc);
+
+	PG_RETURN_TEXT_P(string_to_text(buf.data));
+}
+
+/*
  * pg_get_constraintdef
  *
  * Returns the definition for the constraint, ie, everything that needs to
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index dbfc7339e5..f7aeaaa26b 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1858,13 +1858,26 @@ describeOneTableDetails(const char *schemaname,
 		PGresult   *result;
 		char	   *parent_name;
 		char	   *partdef;
+		char	   *partconstraintdef = NULL;
 
-		printfPQExpBuffer(&buf,
-			 "SELECT inhparent::pg_catalog.regclass, pg_get_expr(c.relpartbound, inhrelid)"
-			 " FROM pg_catalog.pg_class c"
-			 " JOIN pg_catalog.pg_inherits"
-			 " ON c.oid = inhrelid"
-			 " WHERE c.oid = '%s' AND c.relispartition;", oid);
+		/* If verbose, also request the partition constraint definition */
+		if (verbose)
+			printfPQExpBuffer(&buf,
+				"SELECT inhparent::pg_catalog.regclass,"
+				"		pg_get_expr(c.relpartbound, inhrelid),"
+				"		pg_get_partition_constraintdef(inhrelid)"
+				" FROM pg_catalog.pg_class c"
+				" JOIN pg_catalog.pg_inherits"
+				" ON c.oid = inhrelid"
+				" WHERE c.oid = '%s' AND c.relispartition;", oid);
+		else
+			printfPQExpBuffer(&buf,
+				"SELECT inhparent::pg_catalog.regclass,"
+				"		pg_get_expr(c.relpartbound, inhrelid)"
+				" FROM pg_catalog.pg_class c"
+				" JOIN pg_catalog.pg_inherits"
+				" ON c.oid = inhrelid"
+				" WHERE c.oid = '%s' AND c.relispartition;", oid);
 		result = PSQLexec(buf.data);
 		if (!result)
 			goto error_return;
@@ -1873,9 +1886,21 @@ describeOneTableDetails(const char *schemaname,
 		{
 			parent_name = PQgetvalue(result, 0, 0);
 			partdef = PQgetvalue(result, 0, 1);
+
+			if (PQnfields(result) == 3)
+				partconstraintdef = PQgetvalue(result, 0, 2);
+
 			printfPQExpBuffer(&tmpbuf, _("Partition of: %s %s"), parent_name,
 						  partdef);
 			printTableAddFooter(&cont, tmpbuf.data);
+
+			if (partconstraintdef)
+			{
+				printfPQExpBuffer(&tmpbuf, _("Partition constraint: %s"),
+								  partconstraintdef);
+				printTableAddFooter(&cont, tmpbuf.data);
+			}
+
 			PQclear(result);
 		}
 	}
diff --git a/src/include/catalog/partition.h b/src/include/catalog/partition.h
index 421644ca77..25fb0a0440 100644
--- a/src/include/catalog/partition.h
+++ b/src/include/catalog/partition.h
@@ -80,6 +80,7 @@ extern List *get_qual_from_partbound(Relation rel, Relation parent, Node *bound)
 extern List *map_partition_varattnos(List *expr, int target_varno,
 						Relation partrel, Relation parent);
 extern List *RelationGetPartitionQual(Relation rel);
+extern Expr *get_partition_qual_relid(Oid relid);
 
 /* For tuple routing */
 extern PartitionDispatch *RelationGetPartitionDispatchInfo(Relation rel,
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 82562add43..aa0ce791c0 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -1992,6 +1992,8 @@ DATA(insert OID = 3415 (  pg_get_statisticsextdef	   PGNSP PGUID 12 1 0 0 0 f f
 DESCR("index description");
 DATA(insert OID = 3352 (  pg_get_partkeydef    PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_partkeydef _null_ _null_ _null_ ));
 DESCR("partition key description");
+DATA(insert OID = 3403 (  pg_get_partition_constraintdef    PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_partition_constraintdef _null_ _null_ _null_ ));
+DESCR("partition constraint description");
 DATA(insert OID = 1662 (  pg_get_triggerdef    PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_triggerdef _null_ _null_ _null_ ));
 DESCR("trigger description");
 DATA(insert OID = 1387 (  pg_get_constraintdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_constraintdef _null_ _null_ _null_ ));
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 15d4ce591c..a4df1415df 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -546,6 +546,7 @@ CREATE TABLE part_forced_oids PARTITION OF oids_parted FOR VALUES FROM (1) TO (1
 --------+---------+-----------+----------+---------+---------+--------------+-------------
  a      | integer |           | not null |         | plain   |              | 
 Partition of: oids_parted FOR VALUES FROM (1) TO (10)
+Partition constraint: CHECK (((a >= 1) AND (a < 10)))
 Has OIDs: yes
 
 DROP TABLE oids_parted, part_forced_oids;
@@ -643,29 +644,43 @@ CREATE TABLE part_c PARTITION OF parted (b WITH OPTIONS NOT NULL DEFAULT 0) FOR
 -- create a level-2 partition
 CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
 -- Partition bound in describe output
-\d part_b
-               Table "public.part_b"
- Column |  Type   | Collation | Nullable | Default 
---------+---------+-----------+----------+---------
- a      | text    |           |          | 
- b      | integer |           | not null | 1
+\d+ part_b
+                                   Table "public.part_b"
+ Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a      | text    |           |          |         | extended |              | 
+ b      | integer |           | not null | 1       | plain    |              | 
 Partition of: parted FOR VALUES IN ('b')
+Partition constraint: CHECK (((a IS NOT NULL) AND (a = ANY (ARRAY['b'::text]))))
 Check constraints:
     "check_a" CHECK (length(a) > 0)
     "part_b_b_check" CHECK (b >= 0)
 
 -- Both partition bound and partition key in describe output
-\d part_c
-               Table "public.part_c"
- Column |  Type   | Collation | Nullable | Default 
---------+---------+-----------+----------+---------
- a      | text    |           |          | 
- b      | integer |           | not null | 0
+\d+ part_c
+                                   Table "public.part_c"
+ Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a      | text    |           |          |         | extended |              | 
+ b      | integer |           | not null | 0       | plain    |              | 
 Partition of: parted FOR VALUES IN ('c')
+Partition constraint: CHECK (((a IS NOT NULL) AND (a = ANY (ARRAY['c'::text]))))
 Partition key: RANGE (b)
 Check constraints:
     "check_a" CHECK (length(a) > 0)
-Number of partitions: 1 (Use \d+ to list them.)
+Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10)
+
+-- a level-2 partition's constraint will include the parent's expressions
+\d+ part_c_1_10
+                                Table "public.part_c_1_10"
+ Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a      | text    |           |          |         | extended |              | 
+ b      | integer |           | not null | 0       | plain    |              | 
+Partition of: part_c FOR VALUES FROM (1) TO (10)
+Partition constraint: CHECK (((a IS NOT NULL) AND (a = ANY (ARRAY['c'::text])) AND (b >= 1) AND (b < 10)))
+Check constraints:
+    "check_a" CHECK (length(a) > 0)
 
 -- Show partition count in the parent's describe output
 -- Tempted to include \d+ output listing partitions with bound info but
@@ -682,6 +697,54 @@ Check constraints:
     "check_a" CHECK (length(a) > 0)
 Number of partitions: 3 (Use \d+ to list them.)
 
+-- show that an unbounded range partition has a CHECK (true) constraint
+CREATE TABLE range_parted4 (a int, b int, c int) PARTITION BY RANGE (abs(a), abs(b), c);
+CREATE TABLE unbounded_range_part PARTITION OF range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (UNBOUNDED, UNBOUNDED, UNBOUNDED);
+\d+ unbounded_range_part
+                           Table "public.unbounded_range_part"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a      | integer |           |          |         | plain   |              | 
+ b      | integer |           |          |         | plain   |              | 
+ c      | integer |           | not null |         | plain   |              | 
+Partition of: range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (UNBOUNDED, UNBOUNDED, UNBOUNDED)
+Partition constraint: CHECK (((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND true))
+
+DROP TABLE unbounded_range_part;
+CREATE TABLE range_parted4_1 PARTITION OF range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (1, UNBOUNDED, UNBOUNDED);
+\d+ range_parted4_1
+                              Table "public.range_parted4_1"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a      | integer |           |          |         | plain   |              | 
+ b      | integer |           |          |         | plain   |              | 
+ c      | integer |           | not null |         | plain   |              | 
+Partition of: range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (1, UNBOUNDED, UNBOUNDED)
+Partition constraint: CHECK (((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (abs(a) <= 1)))
+
+CREATE TABLE range_parted4_2 PARTITION OF range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, UNBOUNDED);
+\d+ range_parted4_2
+                              Table "public.range_parted4_2"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a      | integer |           |          |         | plain   |              | 
+ b      | integer |           |          |         | plain   |              | 
+ c      | integer |           | not null |         | plain   |              | 
+Partition of: range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, UNBOUNDED)
+Partition constraint: CHECK (((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND ((abs(a) > 3) OR ((abs(a) = 3) AND (abs(b) > 4)) OR ((abs(a) = 3) AND (abs(b) = 4) AND (c >= 5))) AND ((abs(a) < 6) OR ((abs(a) = 6) AND (abs(b) <= 7)))))
+
+CREATE TABLE range_parted4_3 PARTITION OF range_parted4 FOR VALUES FROM (6, 8, UNBOUNDED) TO (9, UNBOUNDED, UNBOUNDED);
+\d+ range_parted4_3
+                              Table "public.range_parted4_3"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a      | integer |           |          |         | plain   |              | 
+ b      | integer |           |          |         | plain   |              | 
+ c      | integer |           | not null |         | plain   |              | 
+Partition of: range_parted4 FOR VALUES FROM (6, 8, UNBOUNDED) TO (9, UNBOUNDED, UNBOUNDED)
+Partition constraint: CHECK (((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND ((abs(a) > 6) OR ((abs(a) = 6) AND (abs(b) >= 8))) AND (abs(a) <= 9)))
+
+DROP TABLE range_parted4;
 -- cleanup
 DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3;
 -- comments on partitioned tables columns
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index 6a1f22ebeb..8020745d97 100644
--- a/src/test/regress/expected/foreign_data.out
+++ b/src/test/regress/expected/foreign_data.out
@@ -1844,6 +1844,7 @@ Partitions: pt2_1 FOR VALUES IN (1)
  c2     | text    |           |          |         |             | extended |              | 
  c3     | date    |           |          |         |             | plain    |              | 
 Partition of: pt2 FOR VALUES IN (1)
+Partition constraint: CHECK (((c1 IS NOT NULL) AND (c1 = ANY (ARRAY[1]))))
 Server: s0
 FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
 
@@ -1914,6 +1915,7 @@ Partitions: pt2_1 FOR VALUES IN (1)
  c2     | text    |           |          |         |             | extended |              | 
  c3     | date    |           |          |         |             | plain    |              | 
 Partition of: pt2 FOR VALUES IN (1)
+Partition constraint: CHECK (((c1 IS NOT NULL) AND (c1 = ANY (ARRAY[1]))))
 Server: s0
 FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
 
@@ -1941,6 +1943,7 @@ Partitions: pt2_1 FOR VALUES IN (1)
  c2     | text    |           |          |         |             | extended |              | 
  c3     | date    |           | not null |         |             | plain    |              | 
 Partition of: pt2 FOR VALUES IN (1)
+Partition constraint: CHECK (((c1 IS NOT NULL) AND (c1 = ANY (ARRAY[1]))))
 Check constraints:
     "p21chk" CHECK (c2 <> ''::text)
 Server: s0
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index 95035c5947..5cb2bb4813 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -598,10 +598,13 @@ CREATE TABLE part_c PARTITION OF parted (b WITH OPTIONS NOT NULL DEFAULT 0) FOR
 CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
 
 -- Partition bound in describe output
-\d part_b
+\d+ part_b
 
 -- Both partition bound and partition key in describe output
-\d part_c
+\d+ part_c
+
+-- a level-2 partition's constraint will include the parent's expressions
+\d+ part_c_1_10
 
 -- Show partition count in the parent's describe output
 -- Tempted to include \d+ output listing partitions with bound info but
@@ -609,6 +612,19 @@ CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
 -- returned.
 \d parted
 
+-- show that an unbounded range partition has a CHECK (true) constraint
+CREATE TABLE range_parted4 (a int, b int, c int) PARTITION BY RANGE (abs(a), abs(b), c);
+CREATE TABLE unbounded_range_part PARTITION OF range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (UNBOUNDED, UNBOUNDED, UNBOUNDED);
+\d+ unbounded_range_part
+DROP TABLE unbounded_range_part;
+CREATE TABLE range_parted4_1 PARTITION OF range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (1, UNBOUNDED, UNBOUNDED);
+\d+ range_parted4_1
+CREATE TABLE range_parted4_2 PARTITION OF range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, UNBOUNDED);
+\d+ range_parted4_2
+CREATE TABLE range_parted4_3 PARTITION OF range_parted4 FOR VALUES FROM (6, 8, UNBOUNDED) TO (9, UNBOUNDED, UNBOUNDED);
+\d+ range_parted4_3
+DROP TABLE range_parted4;
+
 -- cleanup
 DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3;
 
-- 
2.11.0

From 17ae801b3f3fa5e89ab9f2332a825382281522ad Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Tue, 2 May 2017 11:03:54 +0900
Subject: [PATCH 1/2] Emit "correct" range partition constraint expression

Currently emitted expression does not always describe the constraint
correctly, especially in the case of multi-column range key.

Code surrounding get_qual_for_*() has been rearranged a little to
move common code to a couple of new functions.

Original issue reported by Olaf Gawenda (olaf.gw@googlemail.com)
---
 src/backend/catalog/partition.c       | 733 ++++++++++++++++++++++++----------
 src/include/nodes/pg_list.h           |  14 +
 src/test/regress/expected/inherit.out |  90 +++++
 src/test/regress/expected/insert.out  |  59 +++
 src/test/regress/sql/inherit.sql      |  18 +
 src/test/regress/sql/insert.sql       |  43 ++
 6 files changed, 748 insertions(+), 209 deletions(-)

diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c
index 8641ae16a2..b05ffd2d90 100644
--- a/src/backend/catalog/partition.c
+++ b/src/backend/catalog/partition.c
@@ -118,10 +118,19 @@ static int32 qsort_partition_list_value_cmp(const void *a, const void *b,
 static int32 qsort_partition_rbound_cmp(const void *a, const void *b,
 						   void *arg);
 
-static List *get_qual_for_list(PartitionKey key, PartitionBoundSpec *spec);
-static List *get_qual_for_range(PartitionKey key, PartitionBoundSpec *spec);
 static Oid get_partition_operator(PartitionKey key, int col,
 					   StrategyNumber strategy, bool *need_relabel);
+static Expr* make_partition_op_expr(PartitionKey key, int keynum,
+					   uint16 strategy, Expr *arg1, Expr *arg2);
+static void get_range_key_properties(PartitionKey key, int keynum,
+						 PartitionRangeDatum *ldatum,
+						 PartitionRangeDatum *udatum,
+						 ListCell **partexprs_item,
+						 Expr **keyCol,
+						 Const **lower_val, Const **upper_val,
+						 NullTest **nulltest);
+static List *get_qual_for_list(PartitionKey key, PartitionBoundSpec *spec);
+static List *get_qual_for_range(PartitionKey key, PartitionBoundSpec *spec);
 static List *generate_partition_qual(Relation rel);
 
 static PartitionRangeBound *make_one_range_bound(PartitionKey key, int index,
@@ -1146,6 +1155,123 @@ RelationGetPartitionDispatchInfo(Relation rel, int lockmode,
 /* Module-local functions */
 
 /*
+ * get_partition_operator
+ *
+ * Return oid of the operator of given strategy for a given partition key
+ * column.
+ */
+static Oid
+get_partition_operator(PartitionKey key, int col, StrategyNumber strategy,
+					   bool *need_relabel)
+{
+	Oid			operoid;
+
+	/*
+	 * First check if there exists an operator of the given strategy, with
+	 * this column's type as both its lefttype and righttype, in the
+	 * partitioning operator family specified for the column.
+	 */
+	operoid = get_opfamily_member(key->partopfamily[col],
+								  key->parttypid[col],
+								  key->parttypid[col],
+								  strategy);
+
+	/*
+	 * If one doesn't exist, we must resort to using an operator in the same
+	 * opreator family but with the operator class declared input type.  It is
+	 * OK to do so, because the column's type is known to be binary-coercible
+	 * with the operator class input type (otherwise, the operator class in
+	 * question would not have been accepted as the partitioning operator
+	 * class).  We must however inform the caller to wrap the non-Const
+	 * expression with a RelabelType node to denote the implicit coercion. It
+	 * ensures that the resulting expression structurally matches similarly
+	 * processed expressions within the optimizer.
+	 */
+	if (!OidIsValid(operoid))
+	{
+		operoid = get_opfamily_member(key->partopfamily[col],
+									  key->partopcintype[col],
+									  key->partopcintype[col],
+									  strategy);
+		*need_relabel = true;
+	}
+	else
+		*need_relabel = false;
+
+	if (!OidIsValid(operoid))
+		elog(ERROR, "could not find operator for partitioning");
+
+	return operoid;
+}
+
+/*
+ * make_partition_op_expr
+ *		Returns an Expr for the given partition key column with arg1 and
+ *		arg2 as its leftop and rightop, respectively
+ */
+static Expr*
+make_partition_op_expr(PartitionKey key, int keynum,
+					   uint16 strategy, Expr *arg1, Expr *arg2)
+{
+	Oid		operoid;
+	bool	need_relabel = false;
+	Expr   *result = NULL;
+
+	/* Get the correct btree operator for this partitioning column */
+	operoid = get_partition_operator(key, keynum, strategy, &need_relabel);
+
+	/*
+	 * Chosen operator may be such that the non-Const operand needs to
+	 * be coerced, so apply the same; see the comment in
+	 * get_partition_operator().
+	 */
+	if (!IsA(arg1, Const) &&
+		(need_relabel ||
+		 key->partcollation[keynum] != key->parttypcoll[keynum]))
+		arg1 = (Expr *) makeRelabelType(arg1,
+										key->partopcintype[keynum],
+										-1,
+										key->partcollation[keynum],
+										COERCE_EXPLICIT_CAST);
+
+	/* Generate the actual expression */
+	switch (key->strategy)
+	{
+		case PARTITION_STRATEGY_LIST:
+		{
+			ScalarArrayOpExpr *saopexpr;
+
+			/* Build leftop = ANY (rightop) */
+			saopexpr = makeNode(ScalarArrayOpExpr);
+			saopexpr->opno = operoid;
+			saopexpr->opfuncid = get_opcode(operoid);
+			saopexpr->useOr = true;
+			saopexpr->inputcollid = key->partcollation[0];
+			saopexpr->args = list_make2(arg1, arg2);
+			saopexpr->location = -1;
+
+			result = (Expr *) saopexpr;
+			break;
+		}
+
+		case PARTITION_STRATEGY_RANGE:
+			result = make_opclause(operoid,
+								   BOOLOID,
+								   false,
+								   arg1, arg2,
+								   InvalidOid,
+								   key->partcollation[keynum]);
+			break;
+
+		default:
+			elog(ERROR, "invalid partitioning strategy");
+			break;
+	}
+
+	return result;
+}
+
+/*
  * get_qual_for_list
  *
  * Returns a list of expressions to use as a list partition's constraint.
@@ -1155,14 +1281,12 @@ get_qual_for_list(PartitionKey key, PartitionBoundSpec *spec)
 {
 	List	   *result;
 	ArrayExpr  *arr;
-	ScalarArrayOpExpr *opexpr;
+	Expr	   *opexpr;
 	ListCell   *cell,
 			   *prev,
 			   *next;
 	Expr	   *keyCol;
-	Oid			operoid;
-	bool		need_relabel,
-				list_has_null = false;
+	bool		list_has_null = false;
 	NullTest   *nulltest1 = NULL,
 			   *nulltest2 = NULL;
 
@@ -1233,24 +1357,9 @@ get_qual_for_list(PartitionKey key, PartitionBoundSpec *spec)
 	arr->multidims = false;
 	arr->location = -1;
 
-	/* Get the correct btree equality operator */
-	operoid = get_partition_operator(key, 0, BTEqualStrategyNumber,
-									 &need_relabel);
-	if (need_relabel || key->partcollation[0] != key->parttypcoll[0])
-		keyCol = (Expr *) makeRelabelType(keyCol,
-										  key->partopcintype[0],
-										  -1,
-										  key->partcollation[0],
-										  COERCE_EXPLICIT_CAST);
-
-	/* Build leftop = ANY (rightop) */
-	opexpr = makeNode(ScalarArrayOpExpr);
-	opexpr->opno = operoid;
-	opexpr->opfuncid = get_opcode(operoid);
-	opexpr->useOr = true;
-	opexpr->inputcollid = key->partcollation[0];
-	opexpr->args = list_make2(keyCol, arr);
-	opexpr->location = -1;
+	/* Generate the main expression, i.e., keyCol = ANY (arr) */
+	opexpr = make_partition_op_expr(key, 0, BTEqualStrategyNumber,
+									keyCol, (Expr *) arr);
 
 	if (nulltest1)
 		result = list_make2(nulltest1, opexpr);
@@ -1268,9 +1377,100 @@ get_qual_for_list(PartitionKey key, PartitionBoundSpec *spec)
 }
 
 /*
+ * get_range_key_properties
+ *		Returns range partition key information for a given column
+ *
+ * On return, *lower_val and *upper_val contain either a valid Const
+ * expression or NULL.  *nulltest contains a NullTest expression, or
+ * NULL.
+ */
+static void
+get_range_key_properties(PartitionKey key, int keynum,
+						 PartitionRangeDatum *ldatum,
+						 PartitionRangeDatum *udatum,
+						 ListCell **partexprs_item,
+						 Expr **keyCol,
+						 Const **lower_val, Const **upper_val,
+						 NullTest **nulltest)
+{
+	/* Partition key expression for this column */
+	if (key->partattrs[keynum] != 0)
+	{
+		*keyCol = (Expr *) makeVar(1,
+								  key->partattrs[keynum],
+								  key->parttypid[keynum],
+								  key->parttypmod[keynum],
+								  key->parttypcoll[keynum],
+								  0);
+	}
+	else
+	{
+		*keyCol = copyObject(lfirst(*partexprs_item));
+		*partexprs_item = lnext(*partexprs_item);
+	}
+
+	/*
+	 * A range-partitioned table does not allow partition keys to be null.
+	 * For simple columns, their NOT NULL constraint suffices for the
+	 * enforcement of non-nullability.  But for the expression keys, which
+	 * are still nullable, we must emit a IS NOT NULL expression.
+	 */
+	if (!IsA(*keyCol, Var))
+	{
+		*nulltest = makeNode(NullTest);
+		(*nulltest)->arg = *keyCol;
+		(*nulltest)->nulltesttype = IS_NOT_NULL;
+		(*nulltest)->argisrow = false;
+		(*nulltest)->location = -1;
+	}
+	else
+		*nulltest = NULL;
+
+	if (!ldatum->infinite)
+		*lower_val = (Const *) ldatum->value;
+	else
+		*lower_val = NULL;
+
+	if (!udatum->infinite)
+		*upper_val = (Const *) udatum->value;
+	else
+		*upper_val = NULL;
+}
+
+/*
  * get_qual_for_range
  *
- * Get a list of OpExpr's to use as a range partition's constraint.
+ * Get a list of expressions to use as a range partition's constraint.
+ * If there are multiple expressions, they are to be considered implicitly
+ * ANDed.
+ *
+ * For a multi-column range partition key, say (a, b, c), with (al, bl, cl)
+ * as the lower bound tuple and (au, bu, cu) as the upper bound tuple, we
+ * generate an expression tree of the following form:
+ *
+ *	(a > al OR (a = al AND b > bl) OR (a = al AND b = bl AND c >= cl))
+ *		AND
+ *	(a < au OR (a = au AND b < bu) OR (a = au AND b = bu AND c < cu))
+ *
+ * If b were an expression key instead of a simple column, we also append
+ * (b IS NOT NULL) to the AND's argument list.
+ *
+ * It is often the case that a prefix of lower and upper bound tuples contains
+ * the same values, for example, (al = au), in which case, we will emit an
+ * expression tree of the following form:
+ *
+ *	(a = al)
+ *		AND
+ *	(b > bl OR (b = bl AND c >= cl))
+ *		AND
+ *	(b < bu) OR (b = bu AND c < cu))
+ *
+ * In most common cases with only one partition column, say a, the following
+ * expression tree will be generated: a >= al AND a < au
+ *
+ * If all values of both lower and upper bounds are UNBOUNDED, the partition
+ * does not really have a constraint, except the IS NOT NULL constraint for
+ * any expression keys.  We append a constant-true expression in that case.
  */
 static List *
 get_qual_for_range(PartitionKey key, PartitionBoundSpec *spec)
@@ -1278,239 +1478,354 @@ get_qual_for_range(PartitionKey key, PartitionBoundSpec *spec)
 	List	   *result = NIL;
 	ListCell   *cell1,
 			   *cell2,
-			   *partexprs_item;
-	int			i;
+			   *partexprs_item,
+				partexprs_item_saved;
+	int			i,
+				j,
+				k,
+				l;
+	PartitionRangeDatum *ldatum,
+			   *udatum;
+	Expr	   *keyCol;
+	Const	   *lower_val,
+			   *upper_val;
+	NullTest   *nulltest;
+	List	   *lower_or_arms,
+			   *upper_or_arms;
+	List	   *lower_or_prev_arm_args,
+			   *upper_or_prev_arm_args;
+	int			maxnum_or_arms,
+				num_or_arms;
+	ListCell   *lower_or_start_datum,
+			   *upper_or_start_datum;
+	bool		nulltest_generated[PARTITION_MAX_KEYS];
+
+	lower_or_start_datum = list_head(spec->lowerdatums);
+	upper_or_start_datum = list_head(spec->upperdatums);
+	maxnum_or_arms = key->partnatts;
+	memset(nulltest_generated, 0, sizeof(nulltest_generated));
 
 	/*
-	 * Iterate over columns of the key, emitting an OpExpr for each using the
-	 * corresponding lower and upper datums as constant operands.
+	 * Iterate over the key columns and check if the corresponding lower and
+	 * upper datums are equal using the btree equality operator for the
+	 * column's type.  If equal, we emit single keyCol = common_value
+	 * expression.  Starting from the first column for which the corresponding
+	 * lower and upper bound datums are not equal, we generate OR expressions
+	 * as shown in the function's header comment.
 	 */
 	i = 0;
 	partexprs_item = list_head(key->partexprs);
 	forboth(cell1, spec->lowerdatums, cell2, spec->upperdatums)
 	{
-		PartitionRangeDatum *ldatum = lfirst(cell1),
-				   *udatum = lfirst(cell2);
-		Expr	   *keyCol;
-		Const	   *lower_val = NULL,
-				   *upper_val = NULL;
 		EState	   *estate;
 		MemoryContext oldcxt;
 		Expr	   *test_expr;
 		ExprState  *test_exprstate;
 		Datum		test_result;
 		bool		isNull;
-		bool		need_relabel = false;
-		Oid			operoid;
-		NullTest   *nulltest;
 
-		/* Left operand */
-		if (key->partattrs[i] != 0)
-		{
-			keyCol = (Expr *) makeVar(1,
-									  key->partattrs[i],
-									  key->parttypid[i],
-									  key->parttypmod[i],
-									  key->parttypcoll[i],
-									  0);
-		}
-		else
-		{
-			keyCol = copyObject(lfirst(partexprs_item));
-			partexprs_item = lnext(partexprs_item);
-		}
+		ldatum = lfirst(cell1);
+		udatum = lfirst(cell2);
 
 		/*
-		 * Emit a IS NOT NULL expression for non-Var keys, because whereas
-		 * simple attributes are covered by NOT NULL constraints, expression
-		 * keys are still nullable which is not acceptable in case of range
-		 * partitioning.
+		 * Since get_range_key_properties() modifies partexprs_item, and we
+		 * might need to start over from the previous expression in the
+		 * later part of this functiom, save away the current value.
 		 */
-		if (!IsA(keyCol, Var))
+		if (partexprs_item)
+			partexprs_item_saved = *partexprs_item;
+		get_range_key_properties(key, i, ldatum, udatum,
+								 &partexprs_item,
+								 &keyCol,
+								 &lower_val, &upper_val,
+								 &nulltest);
+
+		if (nulltest && !nulltest_generated[i])
 		{
-			nulltest = makeNode(NullTest);
-			nulltest->arg = keyCol;
-			nulltest->nulltesttype = IS_NOT_NULL;
-			nulltest->argisrow = false;
-			nulltest->location = -1;
 			result = lappend(result, nulltest);
+			nulltest_generated[i] = true;
 		}
 
 		/*
-		 * Stop at this column if either of lower or upper datum is infinite,
-		 * but do emit an OpExpr for the non-infinite datum.
+		 * If either or both of lower_val and upper_val is NULL, they are
+		 * unequal, because being NULL means the column is unbounded in the
+		 * respective direction.
 		 */
-		if (!ldatum->infinite)
-			lower_val = (Const *) ldatum->value;
-		if (!udatum->infinite)
-			upper_val = (Const *) udatum->value;
+		if (!lower_val || !upper_val)
+			break;
+
+		/* Create the test expression */
+		estate = CreateExecutorState();
+		oldcxt = MemoryContextSwitchTo(estate->es_query_cxt);
+		test_expr = make_partition_op_expr(key, i, BTEqualStrategyNumber,
+										   (Expr *) lower_val,
+										   (Expr *) upper_val);
+		fix_opfuncids((Node *) test_expr);
+		test_exprstate = ExecInitExpr(test_expr, NULL);
+		test_result = ExecEvalExprSwitchContext(test_exprstate,
+										  GetPerTupleExprContext(estate),
+												&isNull);
+		MemoryContextSwitchTo(oldcxt);
+		FreeExecutorState(estate);
+
+		/* If not equal, go generate the OR expressions */
+		if (!DatumGetBool(test_result))
+			break;
+
+		/* Equal, so generate keyCol = lower_val expression */
 
 		/*
-		 * If lower_val and upper_val are both finite and happen to be equal,
-		 * emit only (keyCol = lower_val) for this column, because all rows in
-		 * this partition could only ever contain this value (ie, lower_val)
-		 * in the current partitioning column.  We must consider further
-		 * columns because the above condition does not fully constrain the
-		 * rows of this partition.
+		 * This can never be true for the last key column, because such a
+		 * range partition would never be allowed to be defined (it would
+		 * have an empty range otherwise).
 		 */
-		if (lower_val && upper_val)
+		if (i == key->partnatts - 1)
+			elog(ERROR, "invalid range bound specification");
+
+		result = lappend(result,
+						 make_partition_op_expr(key, i, BTEqualStrategyNumber,
+												keyCol, (Expr *) lower_val));
+
+		i++;
+	}
+
+	/* First pair of lower_val and upper_val that are not equal. */
+	lower_or_start_datum = cell1;
+	upper_or_start_datum = cell2;
+
+	/* OR will have as many arms as there are key columns left. */
+	k = l = i;
+	maxnum_or_arms = key->partnatts - i;
+	num_or_arms = 0;
+	lower_or_arms = upper_or_arms = NIL;
+	lower_or_prev_arm_args = upper_or_prev_arm_args = NIL;
+	while (num_or_arms < maxnum_or_arms)
+	{
+		List	*lower_or_arm_args = NIL,
+				*upper_or_arm_args = NIL;
+		Expr	*lower_or_arm,
+				*upper_or_arm;
+
+		j = i;
+		partexprs_item = &partexprs_item_saved;
+		for_both_cell(cell1, lower_or_start_datum, cell2, upper_or_start_datum)
 		{
-			/* Get the correct btree equality operator for the test */
-			operoid = get_partition_operator(key, i, BTEqualStrategyNumber,
-											 &need_relabel);
-
-			/* Create the test expression */
-			estate = CreateExecutorState();
-			oldcxt = MemoryContextSwitchTo(estate->es_query_cxt);
-			test_expr = make_opclause(operoid,
-									  BOOLOID,
-									  false,
-									  (Expr *) lower_val,
-									  (Expr *) upper_val,
-									  InvalidOid,
-									  key->partcollation[i]);
-			fix_opfuncids((Node *) test_expr);
-			test_exprstate = ExecInitExpr(test_expr, NULL);
-			test_result = ExecEvalExprSwitchContext(test_exprstate,
-											  GetPerTupleExprContext(estate),
-													&isNull);
-			MemoryContextSwitchTo(oldcxt);
-			FreeExecutorState(estate);
+			ldatum = lfirst(cell1);
+			udatum = lfirst(cell2);
+			get_range_key_properties(key, j, ldatum, udatum,
+									 &partexprs_item,
+									 &keyCol,
+									 &lower_val, &upper_val,
+									 &nulltest);
+
+			if (nulltest && !nulltest_generated[j])
+			{
+				result = lappend(result, nulltest);
+				nulltest_generated[j] = true;
+			}
 
-			if (DatumGetBool(test_result))
+			if (lower_val)
 			{
-				/* This can never be, but it's better to make sure */
-				if (i == key->partnatts - 1)
-					elog(ERROR, "invalid range bound specification");
-
-				if (need_relabel || key->partcollation[i] != key->parttypcoll[i])
-					keyCol = (Expr *) makeRelabelType(keyCol,
-													  key->partopcintype[i],
-													  -1,
-													  key->partcollation[i],
-													  COERCE_EXPLICIT_CAST);
-				result = lappend(result,
-								 make_opclause(operoid,
-											   BOOLOID,
-											   false,
-											   keyCol,
-											   (Expr *) lower_val,
-											   InvalidOid,
-											   key->partcollation[i]));
-
-				/* Go over to consider the next column. */
-				i++;
-				continue;
+				uint16	strategy;
+
+				/*
+				 * For the non-last columns of this arm, use the equality
+				 * operator.
+				 */
+				if (j - i < num_or_arms)
+					strategy = BTEqualStrategyNumber;
+				else
+					/* Consider that the lower bound is inclusive */
+					strategy = (j == key->partnatts - 1)
+										? BTGreaterEqualStrategyNumber
+										: BTGreaterStrategyNumber;
+
+				lower_or_arm_args = lappend(lower_or_arm_args,
+											make_partition_op_expr(key, j,
+																   strategy,
+																   keyCol,
+														(Expr *) lower_val));
 			}
+
+			if (upper_val)
+			{
+				uint16	strategy;
+
+				/*
+				 * For the non-last columns of this arm, use the equality
+				 * operator.
+				 */
+				if (j - i < num_or_arms)
+					strategy = BTEqualStrategyNumber;
+				else
+					strategy = BTLessStrategyNumber;
+
+				upper_or_arm_args = lappend(upper_or_arm_args,
+											make_partition_op_expr(key, j,
+																   strategy,
+																   keyCol,
+														(Expr *) upper_val));
+
+			}
+
+			/* Did we generate enough of OR's arguments? */
+			++j;
+			if (j - i > num_or_arms)
+				break;
 		}
 
 		/*
-		 * We can say here that lower_val != upper_val.  Emit expressions
-		 * (keyCol >= lower_val) and (keyCol < upper_val), then stop.
+		 * Check if we needn't really add these as separate arms of the
+		 * respective OR expressions.  For example, if this arm didn't add
+		 * an OpExpr for the next column because it is unbounded, then we
+		 * don't really need to add this as the new arm.  Instead we modify
+		 * the previous arm's last OpExpr to consider equality by replacing
+		 * its opno with appropriate operator.
 		 */
-		if (lower_val)
+		if (lower_or_arm_args != NIL)
 		{
-			operoid = get_partition_operator(key, i,
-											 BTGreaterEqualStrategyNumber,
-											 &need_relabel);
-
-			if (need_relabel || key->partcollation[i] != key->parttypcoll[i])
-				keyCol = (Expr *) makeRelabelType(keyCol,
-												  key->partopcintype[i],
-												  -1,
-												  key->partcollation[i],
-												  COERCE_EXPLICIT_CAST);
-			result = lappend(result,
-							 make_opclause(operoid,
-										   BOOLOID,
-										   false,
-										   keyCol,
-										   (Expr *) lower_val,
-										   InvalidOid,
-										   key->partcollation[i]));
-		}
+			bool	consider_cur_arm = true;
 
-		if (upper_val)
-		{
-			operoid = get_partition_operator(key, i,
-											 BTLessStrategyNumber,
-											 &need_relabel);
+			/*
+			 * List length comparison with previous suffices to conclude
+			 * that no new column was introduced in this arm.
+			 */
+			if (lower_or_prev_arm_args != NIL &&
+				list_length(lower_or_arm_args) ==
+				list_length(lower_or_prev_arm_args))
+			{
+				OpExpr   *prev_arm_last,
+						 *cur_arm_last;
+				List	 *op_infos;
+				ListCell *opic;
+				OpBtreeInterpretation *cur_op_intp;
+				Oid		newopno;
 
-			if (need_relabel || key->partcollation[i] != key->parttypcoll[i])
-				keyCol = (Expr *) makeRelabelType(keyCol,
-												  key->partopcintype[i],
-												  -1,
-												  key->partcollation[i],
-												  COERCE_EXPLICIT_CAST);
+				prev_arm_last = (OpExpr *) llast(lower_or_prev_arm_args);
+				cur_arm_last = (OpExpr *) llast(lower_or_arm_args);
 
-			result = lappend(result,
-							 make_opclause(operoid,
-										   BOOLOID,
-										   false,
-										   keyCol,
-										   (Expr *) upper_val,
-										   InvalidOid,
-										   key->partcollation[i]));
+				/*
+				 * Must choose the correct replacement operator, that is,
+				 * figure out the opfamily, lefttype and righttype of the
+				 * current operator, which when fed into get_opfamily_member()
+				 * with the new strategy will give us the intended operator.
+				 */
+				op_infos = get_op_btree_interpretation(cur_arm_last->opno);
+				foreach(opic, op_infos)
+				{
+					OpBtreeInterpretation *intp = lfirst(opic);
+
+					if (intp->opfamily_id == key->partopfamily[k-1])
+					{
+						cur_op_intp = intp;
+						break;
+					}
+				}
+
+				newopno = get_opfamily_member(cur_op_intp->opfamily_id,
+											  cur_op_intp->oplefttype,
+											  cur_op_intp->oprighttype,
+											  BTGreaterEqualStrategyNumber);
+				if (!OidIsValid(newopno))
+					elog(ERROR, "no >= operator found in opfamily %u",
+						 cur_op_intp->opfamily_id);
+				prev_arm_last->opno = newopno;
+				consider_cur_arm = false;
+			}
+
+			lower_or_prev_arm_args = lower_or_arm_args;
+
+			if (consider_cur_arm)
+			{
+				lower_or_arm = list_length(lower_or_arm_args) > 1
+							  ? makeBoolExpr(AND_EXPR, lower_or_arm_args, -1)
+							  : linitial(lower_or_arm_args);
+				lower_or_arms = lappend(lower_or_arms, lower_or_arm);
+				++k;	/* latest column in the lower OR expression */
+			}
 		}
 
-		/*
-		 * We can stop at this column, because we would not have checked the
-		 * next column when routing a given row into this partition.
-		 */
-		break;
-	}
+		if (upper_or_arm_args != NIL)
+		{
+			bool	consider_cur_arm = true;
 
-	return result;
-}
+			/* Check if the new arm didn't add check on a new column */
+			if (upper_or_prev_arm_args != NIL &&
+				list_length(upper_or_arm_args) ==
+				list_length(upper_or_prev_arm_args))
+			{
+				OpExpr   *prev_arm_last,
+						 *cur_arm_last;
+				List	 *op_infos;
+				ListCell *opic;
+				OpBtreeInterpretation *cur_op_intp;
+				Oid		newopno;
+
+				prev_arm_last = (OpExpr *) llast(upper_or_prev_arm_args);
+				cur_arm_last = (OpExpr *) llast(upper_or_arm_args);
+				op_infos = get_op_btree_interpretation(cur_arm_last->opno);
+				foreach(opic, op_infos)
+				{
+					OpBtreeInterpretation *intp = lfirst(opic);
 
-/*
- * get_partition_operator
- *
- * Return oid of the operator of given strategy for a given partition key
- * column.
- */
-static Oid
-get_partition_operator(PartitionKey key, int col, StrategyNumber strategy,
-					   bool *need_relabel)
-{
-	Oid			operoid;
+					if (intp->opfamily_id == key->partopfamily[l-1])
+					{
+						cur_op_intp = intp;
+						break;
+					}
+				}
 
-	/*
-	 * First check if there exists an operator of the given strategy, with
-	 * this column's type as both its lefttype and righttype, in the
-	 * partitioning operator family specified for the column.
-	 */
-	operoid = get_opfamily_member(key->partopfamily[col],
-								  key->parttypid[col],
-								  key->parttypid[col],
-								  strategy);
+				newopno = get_opfamily_member(cur_op_intp->opfamily_id,
+											  cur_op_intp->oplefttype,
+											  cur_op_intp->oprighttype,
+											  BTLessEqualStrategyNumber);
+				if (!OidIsValid(newopno))
+					elog(ERROR, "no <= operator found in opfamily %u",
+						 cur_op_intp->opfamily_id);
+				prev_arm_last->opno = newopno;
+				consider_cur_arm = false;
+			}
+			upper_or_prev_arm_args = upper_or_arm_args;
+
+			if (consider_cur_arm)
+			{
+				upper_or_arm = list_length(upper_or_arm_args) > 1
+							  ? makeBoolExpr(AND_EXPR, upper_or_arm_args, -1)
+							  : linitial(upper_or_arm_args);
+				upper_or_arms = lappend(upper_or_arms, upper_or_arm);
+				++l;	/* latest column in the upper OR expression */
+			}
+		}
+
+		++num_or_arms;
+	}
 
 	/*
-	 * If one doesn't exist, we must resort to using an operator in the same
-	 * opreator family but with the operator class declared input type.  It is
-	 * OK to do so, because the column's type is known to be binary-coercible
-	 * with the operator class input type (otherwise, the operator class in
-	 * question would not have been accepted as the partitioning operator
-	 * class).  We must however inform the caller to wrap the non-Const
-	 * expression with a RelabelType node to denote the implicit coercion. It
-	 * ensures that the resulting expression structurally matches similarly
-	 * processed expressions within the optimizer.
+	 * Generate the OR expressions for each of lower and upper bounds (if
+	 * required), and append to the list of implicitly ANDed list of
+	 * expressions.
+	 *
+	 * If both lower_or_arms and upper_or_arms are empty, we append a
+	 * constant-true expression.  That happens if all of the literal values
+	 * in both the lower and upper bound lists are UNBOUNDED.
 	 */
-	if (!OidIsValid(operoid))
+	if (lower_or_arms == NIL && upper_or_arms == NIL)
+		result = lappend(result, makeBoolConst(true, false));
+	else
 	{
-		operoid = get_opfamily_member(key->partopfamily[col],
-									  key->partopcintype[col],
-									  key->partopcintype[col],
-									  strategy);
-		*need_relabel = true;
+		if (lower_or_arms != NIL)
+			result = lappend(result,
+							 list_length(lower_or_arms) > 1
+								? makeBoolExpr(OR_EXPR, lower_or_arms, -1)
+								: linitial(lower_or_arms));
+		if (upper_or_arms != NIL)
+			result = lappend(result,
+							 list_length(upper_or_arms) > 1
+								? makeBoolExpr(OR_EXPR, upper_or_arms, -1)
+								: linitial(upper_or_arms));
 	}
-	else
-		*need_relabel = false;
 
-	if (!OidIsValid(operoid))
-		elog(ERROR, "could not find operator for partitioning");
-
-	return operoid;
+	return result;
 }
 
 /*
diff --git a/src/include/nodes/pg_list.h b/src/include/nodes/pg_list.h
index 9df7fb30d3..3313b051ce 100644
--- a/src/include/nodes/pg_list.h
+++ b/src/include/nodes/pg_list.h
@@ -183,6 +183,20 @@ list_length(const List *l)
 		 (cell1) = lnext(cell1), (cell2) = lnext(cell2))
 
 /*
+ * for_both_cell -
+ *	  a convenience macro which loops through two lists starting from the
+ *	  specified cells of each. This macro loops through both lists at the same
+ *	  time, stopping when either list runs out of elements.  Depending on the
+ *	  requirements of the call site, it may also be wise to assert that the
+ *	  lengths of the two lists are equal, and initcell1 and initcell2 are at
+ *	  the same position in the respective lists.
+ */
+#define for_both_cell(cell1, initcell1, cell2, initcell2)	\
+	for ((cell1) = (initcell1), (cell2) = (initcell2);		\
+		 (cell1) != NULL && (cell2) != NULL;				\
+		 (cell1) = lnext(cell1), (cell2) = lnext(cell2))
+
+/*
  * forthree -
  *	  the same for three lists
  */
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 6163ed8117..af7090ba0d 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1828,3 +1828,93 @@ explain (costs off) select * from range_list_parted where a >= 30;
 
 drop table list_parted;
 drop table range_list_parted;
+-- check that constraint exclusion is able to cope with the partition
+-- constraint emitted for multi-column range partitioned tables
+create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
+create table mcrparted0 partition of mcrparted for values from (unbounded, unbounded, unbounded) to (1, 1, 1);
+create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to (10, 10, 10);
+create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
+create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20);
+create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (unbounded, unbounded, unbounded);
+explain (costs off) select * from mcrparted where a = 0;	-- scans mcrparted0
+          QUERY PLAN          
+------------------------------
+ Append
+   ->  Seq Scan on mcrparted0
+         Filter: (a = 0)
+(3 rows)
+
+explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5;	-- scans mcrparted1
+                 QUERY PLAN                  
+---------------------------------------------
+ Append
+   ->  Seq Scan on mcrparted1
+         Filter: ((a = 10) AND (abs(b) < 5))
+(3 rows)
+
+explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5;	-- scans mcrparted1, mcrparted2
+                 QUERY PLAN                  
+---------------------------------------------
+ Append
+   ->  Seq Scan on mcrparted1
+         Filter: ((a = 10) AND (abs(b) = 5))
+   ->  Seq Scan on mcrparted2
+         Filter: ((a = 10) AND (abs(b) = 5))
+(5 rows)
+
+explain (costs off) select * from mcrparted where abs(b) = 5;	-- scans all partitions
+          QUERY PLAN          
+------------------------------
+ Append
+   ->  Seq Scan on mcrparted0
+         Filter: (abs(b) = 5)
+   ->  Seq Scan on mcrparted1
+         Filter: (abs(b) = 5)
+   ->  Seq Scan on mcrparted2
+         Filter: (abs(b) = 5)
+   ->  Seq Scan on mcrparted3
+         Filter: (abs(b) = 5)
+   ->  Seq Scan on mcrparted5
+         Filter: (abs(b) = 5)
+(11 rows)
+
+explain (costs off) select * from mcrparted where a > -1;	-- scans all partitions
+             QUERY PLAN              
+-------------------------------------
+ Append
+   ->  Seq Scan on mcrparted0
+         Filter: (a > '-1'::integer)
+   ->  Seq Scan on mcrparted1
+         Filter: (a > '-1'::integer)
+   ->  Seq Scan on mcrparted2
+         Filter: (a > '-1'::integer)
+   ->  Seq Scan on mcrparted3
+         Filter: (a > '-1'::integer)
+   ->  Seq Scan on mcrparted4
+         Filter: (a > '-1'::integer)
+   ->  Seq Scan on mcrparted5
+         Filter: (a > '-1'::integer)
+(13 rows)
+
+explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c > 10;	-- scans mcrparted4
+                        QUERY PLAN                         
+-----------------------------------------------------------
+ Append
+   ->  Seq Scan on mcrparted4
+         Filter: ((c > 10) AND (a = 20) AND (abs(b) = 10))
+(3 rows)
+
+explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5
+               QUERY PLAN                
+-----------------------------------------
+ Append
+   ->  Seq Scan on mcrparted3
+         Filter: ((c > 20) AND (a = 20))
+   ->  Seq Scan on mcrparted4
+         Filter: ((c > 20) AND (a = 20))
+   ->  Seq Scan on mcrparted5
+         Filter: ((c > 20) AND (a = 20))
+(7 rows)
+
+drop table mcrparted;
diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out
index 6f34b1c640..02429a37e3 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -435,3 +435,62 @@ revoke all on key_desc from someone_else;
 revoke all on key_desc_1 from someone_else;
 drop role someone_else;
 drop table key_desc, key_desc_1;
+-- check multi-column range partitioning expression enforces the same
+-- constraint as what tuple-routing would determine it to be
+create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
+create table mcrparted0 partition of mcrparted for values from (unbounded, unbounded, unbounded) to (1, unbounded, unbounded);
+create table mcrparted1 partition of mcrparted for values from (2, 1, unbounded) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 6, unbounded) to (10, unbounded, unbounded);
+create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
+create table mcrparted4 partition of mcrparted for values from (21, unbounded, unbounded) to (30, 20, unbounded);
+create table mcrparted5 partition of mcrparted for values from (30, 21, 20) to (unbounded, unbounded, unbounded);
+-- routed to mcrparted0
+insert into mcrparted values (0, 1, 1);
+insert into mcrparted0 values (0, 1, 1);
+-- routed to mcparted1
+insert into mcrparted values (9, 1000, 1);
+insert into mcrparted1 values (9, 1000, 1);
+insert into mcrparted values (10, 5, -1);
+insert into mcrparted1 values (10, 5, -1);
+insert into mcrparted values (2, 1, 0);
+insert into mcrparted1 values (2, 1, 0);
+-- routed to mcparted2
+insert into mcrparted values (10, 6, 1000);
+insert into mcrparted2 values (10, 6, 1000);
+insert into mcrparted values (10, 1000, 1000);
+insert into mcrparted2 values (10, 1000, 1000);
+-- no partition exists, nor does mcrparted3 accept it
+insert into mcrparted values (11, 1, -1);
+ERROR:  no partition of relation "mcrparted" found for row
+DETAIL:  Partition key of the failing row contains (a, abs(b), c) = (11, 1, -1).
+insert into mcrparted3 values (11, 1, -1);
+ERROR:  new row for relation "mcrparted3" violates partition constraint
+DETAIL:  Failing row contains (11, 1, -1).
+-- routed to mcrparted5
+insert into mcrparted values (30, 21, 20);
+insert into mcrparted5 values (30, 21, 20);
+insert into mcrparted4 values (30, 21, 20);	-- error
+ERROR:  new row for relation "mcrparted4" violates partition constraint
+DETAIL:  Failing row contains (30, 21, 20).
+-- check rows
+select tableoid::regclass::text, * from mcrparted order by 1;
+  tableoid  | a  |  b   |  c   
+------------+----+------+------
+ mcrparted0 |  0 |    1 |    1
+ mcrparted0 |  0 |    1 |    1
+ mcrparted1 |  9 | 1000 |    1
+ mcrparted1 |  9 | 1000 |    1
+ mcrparted1 | 10 |    5 |   -1
+ mcrparted1 | 10 |    5 |   -1
+ mcrparted1 |  2 |    1 |    0
+ mcrparted1 |  2 |    1 |    0
+ mcrparted2 | 10 |    6 | 1000
+ mcrparted2 | 10 |    6 | 1000
+ mcrparted2 | 10 | 1000 | 1000
+ mcrparted2 | 10 | 1000 | 1000
+ mcrparted5 | 30 |   21 |   20
+ mcrparted5 | 30 |   21 |   20
+(14 rows)
+
+-- cleanup
+drop table mcrparted;
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index d43b75c4a7..7f34f43ec0 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -643,3 +643,21 @@ explain (costs off) select * from range_list_parted where a >= 30;
 
 drop table list_parted;
 drop table range_list_parted;
+
+-- check that constraint exclusion is able to cope with the partition
+-- constraint emitted for multi-column range partitioned tables
+create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
+create table mcrparted0 partition of mcrparted for values from (unbounded, unbounded, unbounded) to (1, 1, 1);
+create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to (10, 10, 10);
+create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
+create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20);
+create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (unbounded, unbounded, unbounded);
+explain (costs off) select * from mcrparted where a = 0;	-- scans mcrparted0
+explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5;	-- scans mcrparted1
+explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5;	-- scans mcrparted1, mcrparted2
+explain (costs off) select * from mcrparted where abs(b) = 5;	-- scans all partitions
+explain (costs off) select * from mcrparted where a > -1;	-- scans all partitions
+explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c > 10;	-- scans mcrparted4
+explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5
+drop table mcrparted;
diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql
index 020854c960..db8967bad7 100644
--- a/src/test/regress/sql/insert.sql
+++ b/src/test/regress/sql/insert.sql
@@ -289,3 +289,46 @@ revoke all on key_desc from someone_else;
 revoke all on key_desc_1 from someone_else;
 drop role someone_else;
 drop table key_desc, key_desc_1;
+
+-- check multi-column range partitioning expression enforces the same
+-- constraint as what tuple-routing would determine it to be
+create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
+create table mcrparted0 partition of mcrparted for values from (unbounded, unbounded, unbounded) to (1, unbounded, unbounded);
+create table mcrparted1 partition of mcrparted for values from (2, 1, unbounded) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 6, unbounded) to (10, unbounded, unbounded);
+create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
+create table mcrparted4 partition of mcrparted for values from (21, unbounded, unbounded) to (30, 20, unbounded);
+create table mcrparted5 partition of mcrparted for values from (30, 21, 20) to (unbounded, unbounded, unbounded);
+
+-- routed to mcrparted0
+insert into mcrparted values (0, 1, 1);
+insert into mcrparted0 values (0, 1, 1);
+
+-- routed to mcparted1
+insert into mcrparted values (9, 1000, 1);
+insert into mcrparted1 values (9, 1000, 1);
+insert into mcrparted values (10, 5, -1);
+insert into mcrparted1 values (10, 5, -1);
+insert into mcrparted values (2, 1, 0);
+insert into mcrparted1 values (2, 1, 0);
+
+-- routed to mcparted2
+insert into mcrparted values (10, 6, 1000);
+insert into mcrparted2 values (10, 6, 1000);
+insert into mcrparted values (10, 1000, 1000);
+insert into mcrparted2 values (10, 1000, 1000);
+
+-- no partition exists, nor does mcrparted3 accept it
+insert into mcrparted values (11, 1, -1);
+insert into mcrparted3 values (11, 1, -1);
+
+-- routed to mcrparted5
+insert into mcrparted values (30, 21, 20);
+insert into mcrparted5 values (30, 21, 20);
+insert into mcrparted4 values (30, 21, 20);	-- error
+
+-- check rows
+select tableoid::regclass::text, * from mcrparted order by 1;
+
+-- cleanup
+drop table mcrparted;
-- 
2.11.0

From 17da90561abdf911e57e2be58d1fce4b1e62fbb3 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Mon, 8 May 2017 10:21:19 +0900
Subject: [PATCH 2/2] Add pg_get_partition_constraintdef

To externally display a partition's implicit constraint.
---
 src/backend/catalog/partition.c            | 29 ++++++++++
 src/backend/utils/adt/ruleutils.c          | 36 ++++++++++++
 src/bin/psql/describe.c                    | 37 +++++++++++--
 src/include/catalog/partition.h            |  1 +
 src/include/catalog/pg_proc.h              |  2 +
 src/test/regress/expected/create_table.out | 89 +++++++++++++++++++++++++-----
 src/test/regress/expected/foreign_data.out |  3 +
 src/test/regress/sql/create_table.sql      | 20 ++++++-
 8 files changed, 196 insertions(+), 21 deletions(-)

diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c
index b05ffd2d90..801263100a 100644
--- a/src/backend/catalog/partition.c
+++ b/src/backend/catalog/partition.c
@@ -978,6 +978,35 @@ RelationGetPartitionQual(Relation rel)
 }
 
 /*
+ * get_partition_qual_relid
+ *
+ * Returns an expression tree describing the passed-in relation's partition
+ * constraint.
+ */
+Expr *
+get_partition_qual_relid(Oid relid)
+{
+	Relation	rel = heap_open(relid, AccessShareLock);
+	Expr	   *result = NULL;
+	List	   *and_args;
+
+	/* Do the work only if this relation is a partition. */
+	if (rel->rd_rel->relispartition)
+	{
+		and_args = generate_partition_qual(rel);
+		if (list_length(and_args) > 1)
+			result = makeBoolExpr(AND_EXPR, and_args, -1);
+		else
+			result = linitial(and_args);
+	}
+
+	/* Keep the lock. */
+	heap_close(rel, NoLock);
+
+	return result;
+}
+
+/*
  * Append OIDs of rel's partitions to the list 'partoids' and for each OID,
  * append pointer rel to the list 'parents'.
  */
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index cbde1fff01..0472cc5ac0 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -24,6 +24,7 @@
 #include "access/sysattr.h"
 #include "catalog/dependency.h"
 #include "catalog/indexing.h"
+#include "catalog/partition.h"
 #include "catalog/pg_aggregate.h"
 #include "catalog/pg_am.h"
 #include "catalog/pg_authid.h"
@@ -1729,6 +1730,41 @@ pg_get_partkeydef_worker(Oid relid, int prettyFlags,
 }
 
 /*
+ * pg_get_partition_constraintdef
+ *
+ * Returns partition constraint expression as a string for the input relation
+ */
+Datum
+pg_get_partition_constraintdef(PG_FUNCTION_ARGS)
+{
+	Oid		relationId = PG_GETARG_OID(0);
+	Expr   *constr_expr;
+	int		prettyFlags;
+	List   *context;
+	char   *consrc;
+	StringInfoData buf;
+
+	constr_expr = get_partition_qual_relid(relationId);
+
+	/* Quick exit if not a partition */
+	if (constr_expr == NULL)
+		PG_RETURN_NULL();
+
+	/*
+	 * Deparse the constraint expression and return in in the form of a CHECK
+	 * constraint.
+	 */
+	prettyFlags = PRETTYFLAG_INDENT;
+	context = deparse_context_for(get_relation_name(relationId), relationId);
+	consrc = deparse_expression_pretty((Node *) constr_expr, context, false,
+									   false, prettyFlags, 0);
+	initStringInfo(&buf);
+	appendStringInfo(&buf, "CHECK (%s)", consrc);
+
+	PG_RETURN_TEXT_P(string_to_text(buf.data));
+}
+
+/*
  * pg_get_constraintdef
  *
  * Returns the definition for the constraint, ie, everything that needs to
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index dbfc7339e5..f7aeaaa26b 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1858,13 +1858,26 @@ describeOneTableDetails(const char *schemaname,
 		PGresult   *result;
 		char	   *parent_name;
 		char	   *partdef;
+		char	   *partconstraintdef = NULL;
 
-		printfPQExpBuffer(&buf,
-			 "SELECT inhparent::pg_catalog.regclass, pg_get_expr(c.relpartbound, inhrelid)"
-			 " FROM pg_catalog.pg_class c"
-			 " JOIN pg_catalog.pg_inherits"
-			 " ON c.oid = inhrelid"
-			 " WHERE c.oid = '%s' AND c.relispartition;", oid);
+		/* If verbose, also request the partition constraint definition */
+		if (verbose)
+			printfPQExpBuffer(&buf,
+				"SELECT inhparent::pg_catalog.regclass,"
+				"		pg_get_expr(c.relpartbound, inhrelid),"
+				"		pg_get_partition_constraintdef(inhrelid)"
+				" FROM pg_catalog.pg_class c"
+				" JOIN pg_catalog.pg_inherits"
+				" ON c.oid = inhrelid"
+				" WHERE c.oid = '%s' AND c.relispartition;", oid);
+		else
+			printfPQExpBuffer(&buf,
+				"SELECT inhparent::pg_catalog.regclass,"
+				"		pg_get_expr(c.relpartbound, inhrelid)"
+				" FROM pg_catalog.pg_class c"
+				" JOIN pg_catalog.pg_inherits"
+				" ON c.oid = inhrelid"
+				" WHERE c.oid = '%s' AND c.relispartition;", oid);
 		result = PSQLexec(buf.data);
 		if (!result)
 			goto error_return;
@@ -1873,9 +1886,21 @@ describeOneTableDetails(const char *schemaname,
 		{
 			parent_name = PQgetvalue(result, 0, 0);
 			partdef = PQgetvalue(result, 0, 1);
+
+			if (PQnfields(result) == 3)
+				partconstraintdef = PQgetvalue(result, 0, 2);
+
 			printfPQExpBuffer(&tmpbuf, _("Partition of: %s %s"), parent_name,
 						  partdef);
 			printTableAddFooter(&cont, tmpbuf.data);
+
+			if (partconstraintdef)
+			{
+				printfPQExpBuffer(&tmpbuf, _("Partition constraint: %s"),
+								  partconstraintdef);
+				printTableAddFooter(&cont, tmpbuf.data);
+			}
+
 			PQclear(result);
 		}
 	}
diff --git a/src/include/catalog/partition.h b/src/include/catalog/partition.h
index 421644ca77..25fb0a0440 100644
--- a/src/include/catalog/partition.h
+++ b/src/include/catalog/partition.h
@@ -80,6 +80,7 @@ extern List *get_qual_from_partbound(Relation rel, Relation parent, Node *bound)
 extern List *map_partition_varattnos(List *expr, int target_varno,
 						Relation partrel, Relation parent);
 extern List *RelationGetPartitionQual(Relation rel);
+extern Expr *get_partition_qual_relid(Oid relid);
 
 /* For tuple routing */
 extern PartitionDispatch *RelationGetPartitionDispatchInfo(Relation rel,
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 82562add43..aa0ce791c0 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -1992,6 +1992,8 @@ DATA(insert OID = 3415 (  pg_get_statisticsextdef	   PGNSP PGUID 12 1 0 0 0 f f
 DESCR("index description");
 DATA(insert OID = 3352 (  pg_get_partkeydef    PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_partkeydef _null_ _null_ _null_ ));
 DESCR("partition key description");
+DATA(insert OID = 3403 (  pg_get_partition_constraintdef    PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_partition_constraintdef _null_ _null_ _null_ ));
+DESCR("partition constraint description");
 DATA(insert OID = 1662 (  pg_get_triggerdef    PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_triggerdef _null_ _null_ _null_ ));
 DESCR("trigger description");
 DATA(insert OID = 1387 (  pg_get_constraintdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_constraintdef _null_ _null_ _null_ ));
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 15d4ce591c..a4df1415df 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -546,6 +546,7 @@ CREATE TABLE part_forced_oids PARTITION OF oids_parted FOR VALUES FROM (1) TO (1
 --------+---------+-----------+----------+---------+---------+--------------+-------------
  a      | integer |           | not null |         | plain   |              | 
 Partition of: oids_parted FOR VALUES FROM (1) TO (10)
+Partition constraint: CHECK (((a >= 1) AND (a < 10)))
 Has OIDs: yes
 
 DROP TABLE oids_parted, part_forced_oids;
@@ -643,29 +644,43 @@ CREATE TABLE part_c PARTITION OF parted (b WITH OPTIONS NOT NULL DEFAULT 0) FOR
 -- create a level-2 partition
 CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
 -- Partition bound in describe output
-\d part_b
-               Table "public.part_b"
- Column |  Type   | Collation | Nullable | Default 
---------+---------+-----------+----------+---------
- a      | text    |           |          | 
- b      | integer |           | not null | 1
+\d+ part_b
+                                   Table "public.part_b"
+ Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a      | text    |           |          |         | extended |              | 
+ b      | integer |           | not null | 1       | plain    |              | 
 Partition of: parted FOR VALUES IN ('b')
+Partition constraint: CHECK (((a IS NOT NULL) AND (a = ANY (ARRAY['b'::text]))))
 Check constraints:
     "check_a" CHECK (length(a) > 0)
     "part_b_b_check" CHECK (b >= 0)
 
 -- Both partition bound and partition key in describe output
-\d part_c
-               Table "public.part_c"
- Column |  Type   | Collation | Nullable | Default 
---------+---------+-----------+----------+---------
- a      | text    |           |          | 
- b      | integer |           | not null | 0
+\d+ part_c
+                                   Table "public.part_c"
+ Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a      | text    |           |          |         | extended |              | 
+ b      | integer |           | not null | 0       | plain    |              | 
 Partition of: parted FOR VALUES IN ('c')
+Partition constraint: CHECK (((a IS NOT NULL) AND (a = ANY (ARRAY['c'::text]))))
 Partition key: RANGE (b)
 Check constraints:
     "check_a" CHECK (length(a) > 0)
-Number of partitions: 1 (Use \d+ to list them.)
+Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10)
+
+-- a level-2 partition's constraint will include the parent's expressions
+\d+ part_c_1_10
+                                Table "public.part_c_1_10"
+ Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a      | text    |           |          |         | extended |              | 
+ b      | integer |           | not null | 0       | plain    |              | 
+Partition of: part_c FOR VALUES FROM (1) TO (10)
+Partition constraint: CHECK (((a IS NOT NULL) AND (a = ANY (ARRAY['c'::text])) AND (b >= 1) AND (b < 10)))
+Check constraints:
+    "check_a" CHECK (length(a) > 0)
 
 -- Show partition count in the parent's describe output
 -- Tempted to include \d+ output listing partitions with bound info but
@@ -682,6 +697,54 @@ Check constraints:
     "check_a" CHECK (length(a) > 0)
 Number of partitions: 3 (Use \d+ to list them.)
 
+-- show that an unbounded range partition has a CHECK (true) constraint
+CREATE TABLE range_parted4 (a int, b int, c int) PARTITION BY RANGE (abs(a), abs(b), c);
+CREATE TABLE unbounded_range_part PARTITION OF range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (UNBOUNDED, UNBOUNDED, UNBOUNDED);
+\d+ unbounded_range_part
+                           Table "public.unbounded_range_part"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a      | integer |           |          |         | plain   |              | 
+ b      | integer |           |          |         | plain   |              | 
+ c      | integer |           | not null |         | plain   |              | 
+Partition of: range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (UNBOUNDED, UNBOUNDED, UNBOUNDED)
+Partition constraint: CHECK (((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND true))
+
+DROP TABLE unbounded_range_part;
+CREATE TABLE range_parted4_1 PARTITION OF range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (1, UNBOUNDED, UNBOUNDED);
+\d+ range_parted4_1
+                              Table "public.range_parted4_1"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a      | integer |           |          |         | plain   |              | 
+ b      | integer |           |          |         | plain   |              | 
+ c      | integer |           | not null |         | plain   |              | 
+Partition of: range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (1, UNBOUNDED, UNBOUNDED)
+Partition constraint: CHECK (((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (abs(a) <= 1)))
+
+CREATE TABLE range_parted4_2 PARTITION OF range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, UNBOUNDED);
+\d+ range_parted4_2
+                              Table "public.range_parted4_2"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a      | integer |           |          |         | plain   |              | 
+ b      | integer |           |          |         | plain   |              | 
+ c      | integer |           | not null |         | plain   |              | 
+Partition of: range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, UNBOUNDED)
+Partition constraint: CHECK (((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND ((abs(a) > 3) OR ((abs(a) = 3) AND (abs(b) > 4)) OR ((abs(a) = 3) AND (abs(b) = 4) AND (c >= 5))) AND ((abs(a) < 6) OR ((abs(a) = 6) AND (abs(b) <= 7)))))
+
+CREATE TABLE range_parted4_3 PARTITION OF range_parted4 FOR VALUES FROM (6, 8, UNBOUNDED) TO (9, UNBOUNDED, UNBOUNDED);
+\d+ range_parted4_3
+                              Table "public.range_parted4_3"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a      | integer |           |          |         | plain   |              | 
+ b      | integer |           |          |         | plain   |              | 
+ c      | integer |           | not null |         | plain   |              | 
+Partition of: range_parted4 FOR VALUES FROM (6, 8, UNBOUNDED) TO (9, UNBOUNDED, UNBOUNDED)
+Partition constraint: CHECK (((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND ((abs(a) > 6) OR ((abs(a) = 6) AND (abs(b) >= 8))) AND (abs(a) <= 9)))
+
+DROP TABLE range_parted4;
 -- cleanup
 DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3;
 -- comments on partitioned tables columns
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index 6a1f22ebeb..8020745d97 100644
--- a/src/test/regress/expected/foreign_data.out
+++ b/src/test/regress/expected/foreign_data.out
@@ -1844,6 +1844,7 @@ Partitions: pt2_1 FOR VALUES IN (1)
  c2     | text    |           |          |         |             | extended |              | 
  c3     | date    |           |          |         |             | plain    |              | 
 Partition of: pt2 FOR VALUES IN (1)
+Partition constraint: CHECK (((c1 IS NOT NULL) AND (c1 = ANY (ARRAY[1]))))
 Server: s0
 FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
 
@@ -1914,6 +1915,7 @@ Partitions: pt2_1 FOR VALUES IN (1)
  c2     | text    |           |          |         |             | extended |              | 
  c3     | date    |           |          |         |             | plain    |              | 
 Partition of: pt2 FOR VALUES IN (1)
+Partition constraint: CHECK (((c1 IS NOT NULL) AND (c1 = ANY (ARRAY[1]))))
 Server: s0
 FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
 
@@ -1941,6 +1943,7 @@ Partitions: pt2_1 FOR VALUES IN (1)
  c2     | text    |           |          |         |             | extended |              | 
  c3     | date    |           | not null |         |             | plain    |              | 
 Partition of: pt2 FOR VALUES IN (1)
+Partition constraint: CHECK (((c1 IS NOT NULL) AND (c1 = ANY (ARRAY[1]))))
 Check constraints:
     "p21chk" CHECK (c2 <> ''::text)
 Server: s0
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index 95035c5947..5cb2bb4813 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -598,10 +598,13 @@ CREATE TABLE part_c PARTITION OF parted (b WITH OPTIONS NOT NULL DEFAULT 0) FOR
 CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
 
 -- Partition bound in describe output
-\d part_b
+\d+ part_b
 
 -- Both partition bound and partition key in describe output
-\d part_c
+\d+ part_c
+
+-- a level-2 partition's constraint will include the parent's expressions
+\d+ part_c_1_10
 
 -- Show partition count in the parent's describe output
 -- Tempted to include \d+ output listing partitions with bound info but
@@ -609,6 +612,19 @@ CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
 -- returned.
 \d parted
 
+-- show that an unbounded range partition has a CHECK (true) constraint
+CREATE TABLE range_parted4 (a int, b int, c int) PARTITION BY RANGE (abs(a), abs(b), c);
+CREATE TABLE unbounded_range_part PARTITION OF range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (UNBOUNDED, UNBOUNDED, UNBOUNDED);
+\d+ unbounded_range_part
+DROP TABLE unbounded_range_part;
+CREATE TABLE range_parted4_1 PARTITION OF range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (1, UNBOUNDED, UNBOUNDED);
+\d+ range_parted4_1
+CREATE TABLE range_parted4_2 PARTITION OF range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, UNBOUNDED);
+\d+ range_parted4_2
+CREATE TABLE range_parted4_3 PARTITION OF range_parted4 FOR VALUES FROM (6, 8, UNBOUNDED) TO (9, UNBOUNDED, UNBOUNDED);
+\d+ range_parted4_3
+DROP TABLE range_parted4;
+
 -- cleanup
 DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3;
 
-- 
2.11.0

From 1d5fb913810b0f65ba15e466eb3fc7703184b0f3 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Tue, 2 May 2017 11:03:54 +0900
Subject: [PATCH 1/2] Emit "correct" range partition constraint expression

Currently emitted expression does not always describe the constraint
correctly, especially in the case of multi-column range key.

Code surrounding get_qual_for_*() has been rearranged a little to
move common code to a couple of new functions.

Original issue reported by Olaf Gawenda (olaf.gw@googlemail.com)
---
 src/backend/catalog/partition.c       | 667 +++++++++++++++++++++++-----------
 src/include/nodes/pg_list.h           |  14 +
 src/test/regress/expected/inherit.out |  90 +++++
 src/test/regress/expected/insert.out  |  59 +++
 src/test/regress/sql/inherit.sql      |  18 +
 src/test/regress/sql/insert.sql       |  43 +++
 6 files changed, 670 insertions(+), 221 deletions(-)

diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c
index 8641ae1..b59a573 100644
--- a/src/backend/catalog/partition.c
+++ b/src/backend/catalog/partition.c
@@ -118,10 +118,18 @@ static int32 qsort_partition_list_value_cmp(const void *a, const void *b,
 static int32 qsort_partition_rbound_cmp(const void *a, const void *b,
 						   void *arg);
 
-static List *get_qual_for_list(PartitionKey key, PartitionBoundSpec *spec);
-static List *get_qual_for_range(PartitionKey key, PartitionBoundSpec *spec);
 static Oid get_partition_operator(PartitionKey key, int col,
 					   StrategyNumber strategy, bool *need_relabel);
+static Expr* make_partition_op_expr(PartitionKey key, int keynum,
+					   uint16 strategy, Expr *arg1, Expr *arg2);
+static void get_range_key_properties(PartitionKey key, int keynum,
+						 PartitionRangeDatum *ldatum,
+						 PartitionRangeDatum *udatum,
+						 ListCell **partexprs_item,
+						 Expr **keyCol,
+						 Const **lower_val, Const **upper_val);
+static List *get_qual_for_list(PartitionKey key, PartitionBoundSpec *spec);
+static List *get_qual_for_range(PartitionKey key, PartitionBoundSpec *spec);
 static List *generate_partition_qual(Relation rel);
 
 static PartitionRangeBound *make_one_range_bound(PartitionKey key, int index,
@@ -1146,6 +1154,123 @@ RelationGetPartitionDispatchInfo(Relation rel, int lockmode,
 /* Module-local functions */
 
 /*
+ * get_partition_operator
+ *
+ * Return oid of the operator of given strategy for a given partition key
+ * column.
+ */
+static Oid
+get_partition_operator(PartitionKey key, int col, StrategyNumber strategy,
+					   bool *need_relabel)
+{
+	Oid			operoid;
+
+	/*
+	 * First check if there exists an operator of the given strategy, with
+	 * this column's type as both its lefttype and righttype, in the
+	 * partitioning operator family specified for the column.
+	 */
+	operoid = get_opfamily_member(key->partopfamily[col],
+								  key->parttypid[col],
+								  key->parttypid[col],
+								  strategy);
+
+	/*
+	 * If one doesn't exist, we must resort to using an operator in the same
+	 * opreator family but with the operator class declared input type.  It is
+	 * OK to do so, because the column's type is known to be binary-coercible
+	 * with the operator class input type (otherwise, the operator class in
+	 * question would not have been accepted as the partitioning operator
+	 * class).  We must however inform the caller to wrap the non-Const
+	 * expression with a RelabelType node to denote the implicit coercion. It
+	 * ensures that the resulting expression structurally matches similarly
+	 * processed expressions within the optimizer.
+	 */
+	if (!OidIsValid(operoid))
+	{
+		operoid = get_opfamily_member(key->partopfamily[col],
+									  key->partopcintype[col],
+									  key->partopcintype[col],
+									  strategy);
+		*need_relabel = true;
+	}
+	else
+		*need_relabel = false;
+
+	if (!OidIsValid(operoid))
+		elog(ERROR, "could not find operator for partitioning");
+
+	return operoid;
+}
+
+/*
+ * make_partition_op_expr
+ *		Returns an Expr for the given partition key column with arg1 and
+ *		arg2 as its leftop and rightop, respectively
+ */
+static Expr*
+make_partition_op_expr(PartitionKey key, int keynum,
+					   uint16 strategy, Expr *arg1, Expr *arg2)
+{
+	Oid		operoid;
+	bool	need_relabel = false;
+	Expr   *result = NULL;
+
+	/* Get the correct btree operator for this partitioning column */
+	operoid = get_partition_operator(key, keynum, strategy, &need_relabel);
+
+	/*
+	 * Chosen operator may be such that the non-Const operand needs to
+	 * be coerced, so apply the same; see the comment in
+	 * get_partition_operator().
+	 */
+	if (!IsA(arg1, Const) &&
+		(need_relabel ||
+		 key->partcollation[keynum] != key->parttypcoll[keynum]))
+		arg1 = (Expr *) makeRelabelType(arg1,
+										key->partopcintype[keynum],
+										-1,
+										key->partcollation[keynum],
+										COERCE_EXPLICIT_CAST);
+
+	/* Generate the actual expression */
+	switch (key->strategy)
+	{
+		case PARTITION_STRATEGY_LIST:
+		{
+			ScalarArrayOpExpr *saopexpr;
+
+			/* Build leftop = ANY (rightop) */
+			saopexpr = makeNode(ScalarArrayOpExpr);
+			saopexpr->opno = operoid;
+			saopexpr->opfuncid = get_opcode(operoid);
+			saopexpr->useOr = true;
+			saopexpr->inputcollid = key->partcollation[0];
+			saopexpr->args = list_make2(arg1, arg2);
+			saopexpr->location = -1;
+
+			result = (Expr *) saopexpr;
+			break;
+		}
+
+		case PARTITION_STRATEGY_RANGE:
+			result = make_opclause(operoid,
+								   BOOLOID,
+								   false,
+								   arg1, arg2,
+								   InvalidOid,
+								   key->partcollation[keynum]);
+			break;
+
+		default:
+			elog(ERROR, "invalid partitioning strategy");
+			break;
+	}
+
+	return result;
+}
+
+/*
  * get_qual_for_list
  *
  * Returns a list of expressions to use as a list partition's constraint.
@@ -1155,14 +1280,12 @@ get_qual_for_list(PartitionKey key, PartitionBoundSpec *spec)
 {
 	List	   *result;
 	ArrayExpr  *arr;
-	ScalarArrayOpExpr *opexpr;
+	Expr	   *opexpr;
 	ListCell   *cell,
 			   *prev,
 			   *next;
 	Expr	   *keyCol;
-	Oid			operoid;
-	bool		need_relabel,
-				list_has_null = false;
+	bool		list_has_null = false;
 	NullTest   *nulltest1 = NULL,
 			   *nulltest2 = NULL;
 
@@ -1233,24 +1356,9 @@ get_qual_for_list(PartitionKey key, PartitionBoundSpec *spec)
 	arr->multidims = false;
 	arr->location = -1;
 
-	/* Get the correct btree equality operator */
-	operoid = get_partition_operator(key, 0, BTEqualStrategyNumber,
-									 &need_relabel);
-	if (need_relabel || key->partcollation[0] != key->parttypcoll[0])
-		keyCol = (Expr *) makeRelabelType(keyCol,
-										  key->partopcintype[0],
-										  -1,
-										  key->partcollation[0],
-										  COERCE_EXPLICIT_CAST);
-
-	/* Build leftop = ANY (rightop) */
-	opexpr = makeNode(ScalarArrayOpExpr);
-	opexpr->opno = operoid;
-	opexpr->opfuncid = get_opcode(operoid);
-	opexpr->useOr = true;
-	opexpr->inputcollid = key->partcollation[0];
-	opexpr->args = list_make2(keyCol, arr);
-	opexpr->location = -1;
+	/* Generate the main expression, i.e., keyCol = ANY (arr) */
+	opexpr = make_partition_op_expr(key, 0, BTEqualStrategyNumber,
+									keyCol, (Expr *) arr);
 
 	if (nulltest1)
 		result = list_make2(nulltest1, opexpr);
@@ -1268,9 +1376,90 @@ get_qual_for_list(PartitionKey key, PartitionBoundSpec *spec)
 }
 
 /*
+ * get_range_key_properties
+ *		Returns range partition key information for a given column
+ *
+ * On return, *partexprs_item points to the cell containing the next
+ * expression in the key->partexprs list, or NULL.
+ */
+static void
+get_range_key_properties(PartitionKey key, int keynum,
+						 PartitionRangeDatum *ldatum,
+						 PartitionRangeDatum *udatum,
+						 ListCell **partexprs_item,
+						 Expr **keyCol,
+						 Const **lower_val, Const **upper_val)
+{
+	/* Partition key expression for this column */
+	if (key->partattrs[keynum] != 0)
+	{
+		*keyCol = (Expr *) makeVar(1,
+								  key->partattrs[keynum],
+								  key->parttypid[keynum],
+								  key->parttypmod[keynum],
+								  key->parttypcoll[keynum],
+								  0);
+	}
+	else
+	{
+		*keyCol = copyObject(lfirst(*partexprs_item));
+		*partexprs_item = lnext(*partexprs_item);
+	}
+
+	if (!ldatum->infinite)
+		*lower_val = (Const *) ldatum->value;
+	else
+		*lower_val = NULL;
+
+	if (!udatum->infinite)
+		*upper_val = (Const *) udatum->value;
+	else
+		*upper_val = NULL;
+}
+
+/*
  * get_qual_for_range
  *
- * Get a list of OpExpr's to use as a range partition's constraint.
+ * Get a list of expressions to use as a range partition's constraint.
+ * If there are multiple expressions, they are to be considered implicitly
+ * ANDed.
+ *
+ * For a multi-column range partition key, say (a, b, c), with (al, bl, cl)
+ * as the lower bound tuple and (au, bu, cu) as the upper bound tuple, we
+ * generate an expression tree of the following form:
+ *
+ *	(a > al OR (a = al AND b > bl) OR (a = al AND b = bl AND c >= cl))
+ *		AND
+ *	(a < au OR (a = au AND b < bu) OR (a = au AND b = bu AND c < cu))
+ *
+ * If, say, b were an expression key instead of a simple column, we also
+ * append (b IS NOT NULL) to the AND's argument list.
+ *
+ * It is often the case that a prefix of lower and upper bound tuples contains
+ * the same values, for example, (al = au), in which case, we will emit an
+ * expression tree of the following form:
+ *
+ *	(a = al)
+ *		AND
+ *	(b > bl OR (b = bl AND c >= cl))
+ *		AND
+ *	(b < bu) OR (b = bu AND c < cu))
+ *
+ * If cu happens to be UNBOUNDED, we need not emit any expression for it, so
+ * the last line would be:
+ *
+ *	(b < bu) OR (b = bu), which is simplified to (b <= bu)
+ *
+ * In most common cases with only one partition column, say a, the following
+ * expression tree will be generated: a >= al AND a < au
+ *
+ * If all values of both lower and upper bounds are UNBOUNDED, the partition
+ * does not really have a constraint, except the IS NOT NULL constraint for
+ * any expression keys.
+ *
+ * If we end up with an empty result list, we append return a single-member
+ * list containing a constant-true expression in that case, because callers
+ * expect a non-empty list.
  */
 static List *
 get_qual_for_range(PartitionKey key, PartitionBoundSpec *spec)
@@ -1278,239 +1467,275 @@ get_qual_for_range(PartitionKey key, PartitionBoundSpec *spec)
 	List	   *result = NIL;
 	ListCell   *cell1,
 			   *cell2,
-			   *partexprs_item;
-	int			i;
+			   *partexprs_item,
+				partexprs_item_saved;
+	int			i,
+				j;
+	PartitionRangeDatum *ldatum,
+			   *udatum;
+	Expr	   *keyCol;
+	Const	   *lower_val,
+			   *upper_val;
+	NullTest   *nulltest;
+	List	   *lower_or_arms,
+			   *upper_or_arms;
+	int			num_or_arms,
+				current_or_arm;
+	ListCell   *lower_or_start_datum,
+			   *upper_or_start_datum;
+	bool		need_next_lower_arm,
+				need_next_upper_arm;
+
+	lower_or_start_datum = list_head(spec->lowerdatums);
+	upper_or_start_datum = list_head(spec->upperdatums);
+	num_or_arms = key->partnatts;
 
 	/*
-	 * Iterate over columns of the key, emitting an OpExpr for each using the
-	 * corresponding lower and upper datums as constant operands.
+	 * A range-partitioned table does not allow partition keys to be null.
+	 * For simple columns, their NOT NULL constraint suffices for the
+	 * enforcement of non-nullability.  But for the expression keys, which
+	 * are still nullable, we must emit a IS NOT NULL expression.  Collect
+	 * them in result first.
+	 */
+	partexprs_item = list_head(key->partexprs);
+	for (i = 0; i < key->partnatts; i++)
+	{
+		if (key->partattrs[i] == 0)
+		{
+			Expr   *keyCol;
+
+			if (partexprs_item == NULL)
+				elog(ERROR, "wrong number of partition key expressions");
+			keyCol= lfirst(partexprs_item);
+			partexprs_item = lnext(partexprs_item);
+			Assert(!IsA(keyCol, Var));
+
+			nulltest = makeNode(NullTest);
+			nulltest->arg = keyCol;
+			nulltest->nulltesttype = IS_NOT_NULL;
+			nulltest->argisrow = false;
+			nulltest->location = -1;
+			result = lappend(result, nulltest);
+		}
+	}
+
+	/*
+	 * Iterate over the key columns and check if the corresponding lower and
+	 * upper datums are equal using the btree equality operator for the
+	 * column's type.  If equal, we emit single keyCol = common_value
+	 * expression.  Starting from the first column for which the corresponding
+	 * lower and upper bound datums are not equal, we generate OR expressions
+	 * as shown in the function's header comment.
 	 */
 	i = 0;
 	partexprs_item = list_head(key->partexprs);
 	forboth(cell1, spec->lowerdatums, cell2, spec->upperdatums)
 	{
-		PartitionRangeDatum *ldatum = lfirst(cell1),
-				   *udatum = lfirst(cell2);
-		Expr	   *keyCol;
-		Const	   *lower_val = NULL,
-				   *upper_val = NULL;
 		EState	   *estate;
 		MemoryContext oldcxt;
 		Expr	   *test_expr;
 		ExprState  *test_exprstate;
 		Datum		test_result;
 		bool		isNull;
-		bool		need_relabel = false;
-		Oid			operoid;
-		NullTest   *nulltest;
 
-		/* Left operand */
-		if (key->partattrs[i] != 0)
-		{
-			keyCol = (Expr *) makeVar(1,
-									  key->partattrs[i],
-									  key->parttypid[i],
-									  key->parttypmod[i],
-									  key->parttypcoll[i],
-									  0);
-		}
-		else
-		{
-			keyCol = copyObject(lfirst(partexprs_item));
-			partexprs_item = lnext(partexprs_item);
-		}
+		ldatum = lfirst(cell1);
+		udatum = lfirst(cell2);
 
 		/*
-		 * Emit a IS NOT NULL expression for non-Var keys, because whereas
-		 * simple attributes are covered by NOT NULL constraints, expression
-		 * keys are still nullable which is not acceptable in case of range
-		 * partitioning.
+		 * Since get_range_key_properties() modifies partexprs_item, and we
+		 * might need to start over from the previous expression in the
+		 * later part of this functiom, save away the current value.
 		 */
-		if (!IsA(keyCol, Var))
-		{
-			nulltest = makeNode(NullTest);
-			nulltest->arg = keyCol;
-			nulltest->nulltesttype = IS_NOT_NULL;
-			nulltest->argisrow = false;
-			nulltest->location = -1;
-			result = lappend(result, nulltest);
-		}
+		if (partexprs_item)
+			partexprs_item_saved = *partexprs_item;
+		get_range_key_properties(key, i, ldatum, udatum,
+								 &partexprs_item,
+								 &keyCol,
+								 &lower_val, &upper_val);
 
 		/*
-		 * Stop at this column if either of lower or upper datum is infinite,
-		 * but do emit an OpExpr for the non-infinite datum.
+		 * If either or both of lower_val and upper_val is NULL, they are
+		 * unequal, because being NULL means the column is unbounded in the
+		 * respective direction.
 		 */
-		if (!ldatum->infinite)
-			lower_val = (Const *) ldatum->value;
-		if (!udatum->infinite)
-			upper_val = (Const *) udatum->value;
+		if (!lower_val || !upper_val)
+			break;
+
+		/* Create the test expression */
+		estate = CreateExecutorState();
+		oldcxt = MemoryContextSwitchTo(estate->es_query_cxt);
+		test_expr = make_partition_op_expr(key, i, BTEqualStrategyNumber,
+										   (Expr *) lower_val,
+										   (Expr *) upper_val);
+		fix_opfuncids((Node *) test_expr);
+		test_exprstate = ExecInitExpr(test_expr, NULL);
+		test_result = ExecEvalExprSwitchContext(test_exprstate,
+										  GetPerTupleExprContext(estate),
+												&isNull);
+		MemoryContextSwitchTo(oldcxt);
+		FreeExecutorState(estate);
+
+		/* If not equal, go generate the OR expressions */
+		if (!DatumGetBool(test_result))
+			break;
+
+		/* Equal, so generate keyCol = lower_val expression */
 
 		/*
-		 * If lower_val and upper_val are both finite and happen to be equal,
-		 * emit only (keyCol = lower_val) for this column, because all rows in
-		 * this partition could only ever contain this value (ie, lower_val)
-		 * in the current partitioning column.  We must consider further
-		 * columns because the above condition does not fully constrain the
-		 * rows of this partition.
+		 * This can never be true for the last key column, because such a
+		 * range partition would never be allowed to be defined (it would
+		 * have an empty range otherwise).
 		 */
-		if (lower_val && upper_val)
-		{
-			/* Get the correct btree equality operator for the test */
-			operoid = get_partition_operator(key, i, BTEqualStrategyNumber,
-											 &need_relabel);
-
-			/* Create the test expression */
-			estate = CreateExecutorState();
-			oldcxt = MemoryContextSwitchTo(estate->es_query_cxt);
-			test_expr = make_opclause(operoid,
-									  BOOLOID,
-									  false,
-									  (Expr *) lower_val,
-									  (Expr *) upper_val,
-									  InvalidOid,
-									  key->partcollation[i]);
-			fix_opfuncids((Node *) test_expr);
-			test_exprstate = ExecInitExpr(test_expr, NULL);
-			test_result = ExecEvalExprSwitchContext(test_exprstate,
-											  GetPerTupleExprContext(estate),
-													&isNull);
-			MemoryContextSwitchTo(oldcxt);
-			FreeExecutorState(estate);
+		if (i == key->partnatts - 1)
+			elog(ERROR, "invalid range bound specification");
+
+		result = lappend(result,
+						 make_partition_op_expr(key, i, BTEqualStrategyNumber,
+												keyCol, (Expr *) lower_val));
 
-			if (DatumGetBool(test_result))
+		i++;
+	}
+
+	/* First pair of lower_val and upper_val that are not equal. */
+	lower_or_start_datum = cell1;
+	upper_or_start_datum = cell2;
+
+	/* OR will have as many arms as there are key columns left. */
+	num_or_arms = key->partnatts - i;
+	current_or_arm = 0;
+	lower_or_arms = upper_or_arms = NIL;
+	need_next_lower_arm = need_next_upper_arm = true;
+	while (current_or_arm < num_or_arms)
+	{
+		List	*lower_or_arm_args = NIL,
+				*upper_or_arm_args = NIL;
+
+		j = i;
+
+		/* Don't make partexprs_item point to garbage. */
+		if (key->partexprs)
+			partexprs_item = &partexprs_item_saved;
+
+		for_both_cell(cell1, lower_or_start_datum, cell2, upper_or_start_datum)
+		{
+			PartitionRangeDatum   *ldatum_next = NULL,
+								  *udatum_next = NULL;
+
+			ldatum = lfirst(cell1);
+			if (lnext(cell1))
+				ldatum_next = lfirst(lnext(cell1));
+			udatum = lfirst(cell2);
+			if (lnext(cell2))
+				udatum_next = lfirst(lnext(cell2));
+			get_range_key_properties(key, j, ldatum, udatum,
+									 &partexprs_item,
+									 &keyCol,
+									 &lower_val, &upper_val);
+
+			if (need_next_lower_arm && lower_val)
 			{
-				/* This can never be, but it's better to make sure */
-				if (i == key->partnatts - 1)
-					elog(ERROR, "invalid range bound specification");
-
-				if (need_relabel || key->partcollation[i] != key->parttypcoll[i])
-					keyCol = (Expr *) makeRelabelType(keyCol,
-													  key->partopcintype[i],
-													  -1,
-													  key->partcollation[i],
-													  COERCE_EXPLICIT_CAST);
-				result = lappend(result,
-								 make_opclause(operoid,
-											   BOOLOID,
-											   false,
-											   keyCol,
-											   (Expr *) lower_val,
-											   InvalidOid,
-											   key->partcollation[i]));
-
-				/* Go over to consider the next column. */
-				i++;
-				continue;
+				uint16	strategy;
+
+				/*
+				 * For the non-last columns of this arm, use the EQ operator.
+				 * For the last or the last finite-valued column, use GE.
+				 */
+				if (j - i < current_or_arm)
+					strategy = BTEqualStrategyNumber;
+				else if ((ldatum_next && ldatum_next->infinite) ||
+						 j == key->partnatts - 1)
+					strategy = BTGreaterEqualStrategyNumber;
+				else
+					strategy = BTGreaterStrategyNumber;
+
+				lower_or_arm_args = lappend(lower_or_arm_args,
+											make_partition_op_expr(key, j,
+																   strategy,
+																   keyCol,
+														(Expr *) lower_val));
 			}
-		}
 
-		/*
-		 * We can say here that lower_val != upper_val.  Emit expressions
-		 * (keyCol >= lower_val) and (keyCol < upper_val), then stop.
-		 */
-		if (lower_val)
-		{
-			operoid = get_partition_operator(key, i,
-											 BTGreaterEqualStrategyNumber,
-											 &need_relabel);
-
-			if (need_relabel || key->partcollation[i] != key->parttypcoll[i])
-				keyCol = (Expr *) makeRelabelType(keyCol,
-												  key->partopcintype[i],
-												  -1,
-												  key->partcollation[i],
-												  COERCE_EXPLICIT_CAST);
-			result = lappend(result,
-							 make_opclause(operoid,
-										   BOOLOID,
-										   false,
-										   keyCol,
-										   (Expr *) lower_val,
-										   InvalidOid,
-										   key->partcollation[i]));
-		}
+			if (need_next_upper_arm && upper_val)
+			{
+				uint16	strategy;
 
-		if (upper_val)
-		{
-			operoid = get_partition_operator(key, i,
-											 BTLessStrategyNumber,
-											 &need_relabel);
-
-			if (need_relabel || key->partcollation[i] != key->parttypcoll[i])
-				keyCol = (Expr *) makeRelabelType(keyCol,
-												  key->partopcintype[i],
-												  -1,
-												  key->partcollation[i],
-												  COERCE_EXPLICIT_CAST);
-
-			result = lappend(result,
-							 make_opclause(operoid,
-										   BOOLOID,
-										   false,
-										   keyCol,
-										   (Expr *) upper_val,
-										   InvalidOid,
-										   key->partcollation[i]));
+				/*
+				 * For the non-last columns of this arm, use the EQ operator.
+				 * For the last finite-valued column, use LE.
+				 */
+				if (j - i < current_or_arm)
+					strategy = BTEqualStrategyNumber;
+				else if (udatum_next && udatum_next->infinite)
+					strategy = BTLessEqualStrategyNumber;
+				else
+					strategy = BTLessStrategyNumber;
+
+				upper_or_arm_args = lappend(upper_or_arm_args,
+											make_partition_op_expr(key, j,
+																   strategy,
+																   keyCol,
+														(Expr *) upper_val));
+
+			}
+
+			/*
+			 * Did we generate enough of OR's arguments?  First arm considers
+			 * the first of the remaining columns, second arm considers first
+			 * two of the remaining columns, and so on.
+			 */
+			++j;
+			if (j - i > current_or_arm)
+			{
+				/*
+				 * We need not emit the next arm if the new column that will
+				 * be considered is unbounded.
+				 */
+				need_next_lower_arm = ldatum_next && !ldatum_next->infinite;
+				need_next_upper_arm = udatum_next && !udatum_next->infinite;
+				break;
+			}
 		}
 
-		/*
-		 * We can stop at this column, because we would not have checked the
-		 * next column when routing a given row into this partition.
-		 */
-		break;
-	}
+		if (lower_or_arm_args != NIL)
+			lower_or_arms = lappend(lower_or_arms,
+							 list_length(lower_or_arm_args) > 1
+							  ? makeBoolExpr(AND_EXPR, lower_or_arm_args, -1)
+							  : linitial(lower_or_arm_args));
 
-	return result;
-}
+		if (upper_or_arm_args != NIL)
+			upper_or_arms = lappend(upper_or_arms,
+							 list_length(upper_or_arm_args) > 1
+							  ? makeBoolExpr(AND_EXPR, upper_or_arm_args, -1)
+							  : linitial(upper_or_arm_args));
 
-/*
- * get_partition_operator
- *
- * Return oid of the operator of given strategy for a given partition key
- * column.
- */
-static Oid
-get_partition_operator(PartitionKey key, int col, StrategyNumber strategy,
-					   bool *need_relabel)
-{
-	Oid			operoid;
+		/* If no work to do in the next iteration, break away. */
+		if (!need_next_lower_arm && !need_next_upper_arm)
+			break;
 
-	/*
-	 * First check if there exists an operator of the given strategy, with
-	 * this column's type as both its lefttype and righttype, in the
-	 * partitioning operator family specified for the column.
-	 */
-	operoid = get_opfamily_member(key->partopfamily[col],
-								  key->parttypid[col],
-								  key->parttypid[col],
-								  strategy);
+		++current_or_arm;
+	}
 
 	/*
-	 * If one doesn't exist, we must resort to using an operator in the same
-	 * opreator family but with the operator class declared input type.  It is
-	 * OK to do so, because the column's type is known to be binary-coercible
-	 * with the operator class input type (otherwise, the operator class in
-	 * question would not have been accepted as the partitioning operator
-	 * class).  We must however inform the caller to wrap the non-Const
-	 * expression with a RelabelType node to denote the implicit coercion. It
-	 * ensures that the resulting expression structurally matches similarly
-	 * processed expressions within the optimizer.
+	 * Generate the OR expressions for each of lower and upper bounds (if
+	 * required), and append to the list of implicitly ANDed list of
+	 * expressions.
 	 */
-	if (!OidIsValid(operoid))
-	{
-		operoid = get_opfamily_member(key->partopfamily[col],
-									  key->partopcintype[col],
-									  key->partopcintype[col],
-									  strategy);
-		*need_relabel = true;
-	}
-	else
-		*need_relabel = false;
+	if (lower_or_arms != NIL)
+		result = lappend(result,
+						 list_length(lower_or_arms) > 1
+							? makeBoolExpr(OR_EXPR, lower_or_arms, -1)
+							: linitial(lower_or_arms));
+	if (upper_or_arms != NIL)
+		result = lappend(result,
+						 list_length(upper_or_arms) > 1
+							? makeBoolExpr(OR_EXPR, upper_or_arms, -1)
+							: linitial(upper_or_arms));
+
+	/* As noted above, caller expects the list to be non-empty. */
+	if (result == NULL)
+		result = list_make1(makeBoolConst(true, false));
 
-	if (!OidIsValid(operoid))
-		elog(ERROR, "could not find operator for partitioning");
-
-	return operoid;
+	return result;
 }
 
 /*
diff --git a/src/include/nodes/pg_list.h b/src/include/nodes/pg_list.h
index 9df7fb3..3313b05 100644
--- a/src/include/nodes/pg_list.h
+++ b/src/include/nodes/pg_list.h
@@ -183,6 +183,20 @@ list_length(const List *l)
 		 (cell1) = lnext(cell1), (cell2) = lnext(cell2))
 
 /*
+ * for_both_cell -
+ *	  a convenience macro which loops through two lists starting from the
+ *	  specified cells of each. This macro loops through both lists at the same
+ *	  time, stopping when either list runs out of elements.  Depending on the
+ *	  requirements of the call site, it may also be wise to assert that the
+ *	  lengths of the two lists are equal, and initcell1 and initcell2 are at
+ *	  the same position in the respective lists.
+ */
+#define for_both_cell(cell1, initcell1, cell2, initcell2)	\
+	for ((cell1) = (initcell1), (cell2) = (initcell2);		\
+		 (cell1) != NULL && (cell2) != NULL;				\
+		 (cell1) = lnext(cell1), (cell2) = lnext(cell2))
+
+/*
  * forthree -
  *	  the same for three lists
  */
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 6163ed8..af7090b 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1828,3 +1828,93 @@ explain (costs off) select * from range_list_parted where a >= 30;
 
 drop table list_parted;
 drop table range_list_parted;
+-- check that constraint exclusion is able to cope with the partition
+-- constraint emitted for multi-column range partitioned tables
+create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
+create table mcrparted0 partition of mcrparted for values from (unbounded, unbounded, unbounded) to (1, 1, 1);
+create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to (10, 10, 10);
+create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
+create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20);
+create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (unbounded, unbounded, unbounded);
+explain (costs off) select * from mcrparted where a = 0;	-- scans mcrparted0
+          QUERY PLAN          
+------------------------------
+ Append
+   ->  Seq Scan on mcrparted0
+         Filter: (a = 0)
+(3 rows)
+
+explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5;	-- scans mcrparted1
+                 QUERY PLAN                  
+---------------------------------------------
+ Append
+   ->  Seq Scan on mcrparted1
+         Filter: ((a = 10) AND (abs(b) < 5))
+(3 rows)
+
+explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5;	-- scans mcrparted1, mcrparted2
+                 QUERY PLAN                  
+---------------------------------------------
+ Append
+   ->  Seq Scan on mcrparted1
+         Filter: ((a = 10) AND (abs(b) = 5))
+   ->  Seq Scan on mcrparted2
+         Filter: ((a = 10) AND (abs(b) = 5))
+(5 rows)
+
+explain (costs off) select * from mcrparted where abs(b) = 5;	-- scans all partitions
+          QUERY PLAN          
+------------------------------
+ Append
+   ->  Seq Scan on mcrparted0
+         Filter: (abs(b) = 5)
+   ->  Seq Scan on mcrparted1
+         Filter: (abs(b) = 5)
+   ->  Seq Scan on mcrparted2
+         Filter: (abs(b) = 5)
+   ->  Seq Scan on mcrparted3
+         Filter: (abs(b) = 5)
+   ->  Seq Scan on mcrparted5
+         Filter: (abs(b) = 5)
+(11 rows)
+
+explain (costs off) select * from mcrparted where a > -1;	-- scans all partitions
+             QUERY PLAN              
+-------------------------------------
+ Append
+   ->  Seq Scan on mcrparted0
+         Filter: (a > '-1'::integer)
+   ->  Seq Scan on mcrparted1
+         Filter: (a > '-1'::integer)
+   ->  Seq Scan on mcrparted2
+         Filter: (a > '-1'::integer)
+   ->  Seq Scan on mcrparted3
+         Filter: (a > '-1'::integer)
+   ->  Seq Scan on mcrparted4
+         Filter: (a > '-1'::integer)
+   ->  Seq Scan on mcrparted5
+         Filter: (a > '-1'::integer)
+(13 rows)
+
+explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c > 10;	-- scans mcrparted4
+                        QUERY PLAN                         
+-----------------------------------------------------------
+ Append
+   ->  Seq Scan on mcrparted4
+         Filter: ((c > 10) AND (a = 20) AND (abs(b) = 10))
+(3 rows)
+
+explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5
+               QUERY PLAN                
+-----------------------------------------
+ Append
+   ->  Seq Scan on mcrparted3
+         Filter: ((c > 20) AND (a = 20))
+   ->  Seq Scan on mcrparted4
+         Filter: ((c > 20) AND (a = 20))
+   ->  Seq Scan on mcrparted5
+         Filter: ((c > 20) AND (a = 20))
+(7 rows)
+
+drop table mcrparted;
diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out
index 6f34b1c..02429a3 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -435,3 +435,62 @@ revoke all on key_desc from someone_else;
 revoke all on key_desc_1 from someone_else;
 drop role someone_else;
 drop table key_desc, key_desc_1;
+-- check multi-column range partitioning expression enforces the same
+-- constraint as what tuple-routing would determine it to be
+create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
+create table mcrparted0 partition of mcrparted for values from (unbounded, unbounded, unbounded) to (1, unbounded, unbounded);
+create table mcrparted1 partition of mcrparted for values from (2, 1, unbounded) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 6, unbounded) to (10, unbounded, unbounded);
+create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
+create table mcrparted4 partition of mcrparted for values from (21, unbounded, unbounded) to (30, 20, unbounded);
+create table mcrparted5 partition of mcrparted for values from (30, 21, 20) to (unbounded, unbounded, unbounded);
+-- routed to mcrparted0
+insert into mcrparted values (0, 1, 1);
+insert into mcrparted0 values (0, 1, 1);
+-- routed to mcparted1
+insert into mcrparted values (9, 1000, 1);
+insert into mcrparted1 values (9, 1000, 1);
+insert into mcrparted values (10, 5, -1);
+insert into mcrparted1 values (10, 5, -1);
+insert into mcrparted values (2, 1, 0);
+insert into mcrparted1 values (2, 1, 0);
+-- routed to mcparted2
+insert into mcrparted values (10, 6, 1000);
+insert into mcrparted2 values (10, 6, 1000);
+insert into mcrparted values (10, 1000, 1000);
+insert into mcrparted2 values (10, 1000, 1000);
+-- no partition exists, nor does mcrparted3 accept it
+insert into mcrparted values (11, 1, -1);
+ERROR:  no partition of relation "mcrparted" found for row
+DETAIL:  Partition key of the failing row contains (a, abs(b), c) = (11, 1, -1).
+insert into mcrparted3 values (11, 1, -1);
+ERROR:  new row for relation "mcrparted3" violates partition constraint
+DETAIL:  Failing row contains (11, 1, -1).
+-- routed to mcrparted5
+insert into mcrparted values (30, 21, 20);
+insert into mcrparted5 values (30, 21, 20);
+insert into mcrparted4 values (30, 21, 20);	-- error
+ERROR:  new row for relation "mcrparted4" violates partition constraint
+DETAIL:  Failing row contains (30, 21, 20).
+-- check rows
+select tableoid::regclass::text, * from mcrparted order by 1;
+  tableoid  | a  |  b   |  c   
+------------+----+------+------
+ mcrparted0 |  0 |    1 |    1
+ mcrparted0 |  0 |    1 |    1
+ mcrparted1 |  9 | 1000 |    1
+ mcrparted1 |  9 | 1000 |    1
+ mcrparted1 | 10 |    5 |   -1
+ mcrparted1 | 10 |    5 |   -1
+ mcrparted1 |  2 |    1 |    0
+ mcrparted1 |  2 |    1 |    0
+ mcrparted2 | 10 |    6 | 1000
+ mcrparted2 | 10 |    6 | 1000
+ mcrparted2 | 10 | 1000 | 1000
+ mcrparted2 | 10 | 1000 | 1000
+ mcrparted5 | 30 |   21 |   20
+ mcrparted5 | 30 |   21 |   20
+(14 rows)
+
+-- cleanup
+drop table mcrparted;
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index d43b75c..7f34f43 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -643,3 +643,21 @@ explain (costs off) select * from range_list_parted where a >= 30;
 
 drop table list_parted;
 drop table range_list_parted;
+
+-- check that constraint exclusion is able to cope with the partition
+-- constraint emitted for multi-column range partitioned tables
+create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
+create table mcrparted0 partition of mcrparted for values from (unbounded, unbounded, unbounded) to (1, 1, 1);
+create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to (10, 10, 10);
+create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
+create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20);
+create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (unbounded, unbounded, unbounded);
+explain (costs off) select * from mcrparted where a = 0;	-- scans mcrparted0
+explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5;	-- scans mcrparted1
+explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5;	-- scans mcrparted1, mcrparted2
+explain (costs off) select * from mcrparted where abs(b) = 5;	-- scans all partitions
+explain (costs off) select * from mcrparted where a > -1;	-- scans all partitions
+explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c > 10;	-- scans mcrparted4
+explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5
+drop table mcrparted;
diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql
index 020854c..db8967b 100644
--- a/src/test/regress/sql/insert.sql
+++ b/src/test/regress/sql/insert.sql
@@ -289,3 +289,46 @@ revoke all on key_desc from someone_else;
 revoke all on key_desc_1 from someone_else;
 drop role someone_else;
 drop table key_desc, key_desc_1;
+
+-- check multi-column range partitioning expression enforces the same
+-- constraint as what tuple-routing would determine it to be
+create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
+create table mcrparted0 partition of mcrparted for values from (unbounded, unbounded, unbounded) to (1, unbounded, unbounded);
+create table mcrparted1 partition of mcrparted for values from (2, 1, unbounded) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 6, unbounded) to (10, unbounded, unbounded);
+create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
+create table mcrparted4 partition of mcrparted for values from (21, unbounded, unbounded) to (30, 20, unbounded);
+create table mcrparted5 partition of mcrparted for values from (30, 21, 20) to (unbounded, unbounded, unbounded);
+
+-- routed to mcrparted0
+insert into mcrparted values (0, 1, 1);
+insert into mcrparted0 values (0, 1, 1);
+
+-- routed to mcparted1
+insert into mcrparted values (9, 1000, 1);
+insert into mcrparted1 values (9, 1000, 1);
+insert into mcrparted values (10, 5, -1);
+insert into mcrparted1 values (10, 5, -1);
+insert into mcrparted values (2, 1, 0);
+insert into mcrparted1 values (2, 1, 0);
+
+-- routed to mcparted2
+insert into mcrparted values (10, 6, 1000);
+insert into mcrparted2 values (10, 6, 1000);
+insert into mcrparted values (10, 1000, 1000);
+insert into mcrparted2 values (10, 1000, 1000);
+
+-- no partition exists, nor does mcrparted3 accept it
+insert into mcrparted values (11, 1, -1);
+insert into mcrparted3 values (11, 1, -1);
+
+-- routed to mcrparted5
+insert into mcrparted values (30, 21, 20);
+insert into mcrparted5 values (30, 21, 20);
+insert into mcrparted4 values (30, 21, 20);	-- error
+
+-- check rows
+select tableoid::regclass::text, * from mcrparted order by 1;
+
+-- cleanup
+drop table mcrparted;
-- 
2.3.2 (Apple Git-55)

From 21dceff495b66573a46cd29f08cfa386b3194a1e Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Mon, 8 May 2017 10:21:19 +0900
Subject: [PATCH 2/2] Add pg_get_partition_constraintdef

To externally display a partition's implicit constraint.
---
 src/backend/catalog/partition.c            | 29 +++++++++
 src/backend/utils/adt/ruleutils.c          | 36 +++++++++++
 src/bin/psql/describe.c                    | 37 +++++++++--
 src/include/catalog/partition.h            |  1 +
 src/include/catalog/pg_proc.h              |  2 +
 src/test/regress/expected/create_table.out | 99 ++++++++++++++++++++++++++----
 src/test/regress/expected/foreign_data.out |  3 +
 src/test/regress/sql/create_table.sql      | 22 ++++++-
 8 files changed, 208 insertions(+), 21 deletions(-)

diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c
index b59a573..512e3ab 100644
--- a/src/backend/catalog/partition.c
+++ b/src/backend/catalog/partition.c
@@ -977,6 +977,35 @@ RelationGetPartitionQual(Relation rel)
 }
 
 /*
+ * get_partition_qual_relid
+ *
+ * Returns an expression tree describing the passed-in relation's partition
+ * constraint.
+ */
+Expr *
+get_partition_qual_relid(Oid relid)
+{
+	Relation	rel = heap_open(relid, AccessShareLock);
+	Expr	   *result = NULL;
+	List	   *and_args;
+
+	/* Do the work only if this relation is a partition. */
+	if (rel->rd_rel->relispartition)
+	{
+		and_args = generate_partition_qual(rel);
+		if (list_length(and_args) > 1)
+			result = makeBoolExpr(AND_EXPR, and_args, -1);
+		else
+			result = linitial(and_args);
+	}
+
+	/* Keep the lock. */
+	heap_close(rel, NoLock);
+
+	return result;
+}
+
+/*
  * Append OIDs of rel's partitions to the list 'partoids' and for each OID,
  * append pointer rel to the list 'parents'.
  */
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index cbde1ff..0472cc5 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -24,6 +24,7 @@
 #include "access/sysattr.h"
 #include "catalog/dependency.h"
 #include "catalog/indexing.h"
+#include "catalog/partition.h"
 #include "catalog/pg_aggregate.h"
 #include "catalog/pg_am.h"
 #include "catalog/pg_authid.h"
@@ -1729,6 +1730,41 @@ pg_get_partkeydef_worker(Oid relid, int prettyFlags,
 }
 
 /*
+ * pg_get_partition_constraintdef
+ *
+ * Returns partition constraint expression as a string for the input relation
+ */
+Datum
+pg_get_partition_constraintdef(PG_FUNCTION_ARGS)
+{
+	Oid		relationId = PG_GETARG_OID(0);
+	Expr   *constr_expr;
+	int		prettyFlags;
+	List   *context;
+	char   *consrc;
+	StringInfoData buf;
+
+	constr_expr = get_partition_qual_relid(relationId);
+
+	/* Quick exit if not a partition */
+	if (constr_expr == NULL)
+		PG_RETURN_NULL();
+
+	/*
+	 * Deparse the constraint expression and return in in the form of a CHECK
+	 * constraint.
+	 */
+	prettyFlags = PRETTYFLAG_INDENT;
+	context = deparse_context_for(get_relation_name(relationId), relationId);
+	consrc = deparse_expression_pretty((Node *) constr_expr, context, false,
+									   false, prettyFlags, 0);
+	initStringInfo(&buf);
+	appendStringInfo(&buf, "CHECK (%s)", consrc);
+
+	PG_RETURN_TEXT_P(string_to_text(buf.data));
+}
+
+/*
  * pg_get_constraintdef
  *
  * Returns the definition for the constraint, ie, everything that needs to
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 13395f5..5e56b24 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1858,13 +1858,26 @@ describeOneTableDetails(const char *schemaname,
 		PGresult   *result;
 		char	   *parent_name;
 		char	   *partdef;
+		char	   *partconstraintdef = NULL;
 
-		printfPQExpBuffer(&buf,
-			 "SELECT inhparent::pg_catalog.regclass, pg_get_expr(c.relpartbound, inhrelid)"
-			 " FROM pg_catalog.pg_class c"
-			 " JOIN pg_catalog.pg_inherits"
-			 " ON c.oid = inhrelid"
-			 " WHERE c.oid = '%s' AND c.relispartition;", oid);
+		/* If verbose, also request the partition constraint definition */
+		if (verbose)
+			printfPQExpBuffer(&buf,
+				"SELECT inhparent::pg_catalog.regclass,"
+				"		pg_get_expr(c.relpartbound, inhrelid),"
+				"		pg_get_partition_constraintdef(inhrelid)"
+				" FROM pg_catalog.pg_class c"
+				" JOIN pg_catalog.pg_inherits"
+				" ON c.oid = inhrelid"
+				" WHERE c.oid = '%s' AND c.relispartition;", oid);
+		else
+			printfPQExpBuffer(&buf,
+				"SELECT inhparent::pg_catalog.regclass,"
+				"		pg_get_expr(c.relpartbound, inhrelid)"
+				" FROM pg_catalog.pg_class c"
+				" JOIN pg_catalog.pg_inherits"
+				" ON c.oid = inhrelid"
+				" WHERE c.oid = '%s' AND c.relispartition;", oid);
 		result = PSQLexec(buf.data);
 		if (!result)
 			goto error_return;
@@ -1873,9 +1886,21 @@ describeOneTableDetails(const char *schemaname,
 		{
 			parent_name = PQgetvalue(result, 0, 0);
 			partdef = PQgetvalue(result, 0, 1);
+
+			if (PQnfields(result) == 3)
+				partconstraintdef = PQgetvalue(result, 0, 2);
+
 			printfPQExpBuffer(&tmpbuf, _("Partition of: %s %s"), parent_name,
 						  partdef);
 			printTableAddFooter(&cont, tmpbuf.data);
+
+			if (partconstraintdef)
+			{
+				printfPQExpBuffer(&tmpbuf, _("Partition constraint: %s"),
+								  partconstraintdef);
+				printTableAddFooter(&cont, tmpbuf.data);
+			}
+
 			PQclear(result);
 		}
 	}
diff --git a/src/include/catalog/partition.h b/src/include/catalog/partition.h
index 421644c..25fb0a0 100644
--- a/src/include/catalog/partition.h
+++ b/src/include/catalog/partition.h
@@ -80,6 +80,7 @@ extern List *get_qual_from_partbound(Relation rel, Relation parent, Node *bound)
 extern List *map_partition_varattnos(List *expr, int target_varno,
 						Relation partrel, Relation parent);
 extern List *RelationGetPartitionQual(Relation rel);
+extern Expr *get_partition_qual_relid(Oid relid);
 
 /* For tuple routing */
 extern PartitionDispatch *RelationGetPartitionDispatchInfo(Relation rel,
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 77d8ed5..8accec9 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -1992,6 +1992,8 @@ DATA(insert OID = 3415 (  pg_get_statisticsextdef	   PGNSP PGUID 12 1 0 0 0 f f
 DESCR("index description");
 DATA(insert OID = 3352 (  pg_get_partkeydef    PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_partkeydef _null_ _null_ _null_ ));
 DESCR("partition key description");
+DATA(insert OID = 3403 (  pg_get_partition_constraintdef    PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_partition_constraintdef _null_ _null_ _null_ ));
+DESCR("partition constraint description");
 DATA(insert OID = 1662 (  pg_get_triggerdef    PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_triggerdef _null_ _null_ _null_ ));
 DESCR("trigger description");
 DATA(insert OID = 1387 (  pg_get_constraintdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_constraintdef _null_ _null_ _null_ ));
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 15d4ce5..4a57cf7 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -546,6 +546,7 @@ CREATE TABLE part_forced_oids PARTITION OF oids_parted FOR VALUES FROM (1) TO (1
 --------+---------+-----------+----------+---------+---------+--------------+-------------
  a      | integer |           | not null |         | plain   |              | 
 Partition of: oids_parted FOR VALUES FROM (1) TO (10)
+Partition constraint: CHECK (((a >= 1) AND (a < 10)))
 Has OIDs: yes
 
 DROP TABLE oids_parted, part_forced_oids;
@@ -643,29 +644,43 @@ CREATE TABLE part_c PARTITION OF parted (b WITH OPTIONS NOT NULL DEFAULT 0) FOR
 -- create a level-2 partition
 CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
 -- Partition bound in describe output
-\d part_b
-               Table "public.part_b"
- Column |  Type   | Collation | Nullable | Default 
---------+---------+-----------+----------+---------
- a      | text    |           |          | 
- b      | integer |           | not null | 1
+\d+ part_b
+                                   Table "public.part_b"
+ Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a      | text    |           |          |         | extended |              | 
+ b      | integer |           | not null | 1       | plain    |              | 
 Partition of: parted FOR VALUES IN ('b')
+Partition constraint: CHECK (((a IS NOT NULL) AND (a = ANY (ARRAY['b'::text]))))
 Check constraints:
     "check_a" CHECK (length(a) > 0)
     "part_b_b_check" CHECK (b >= 0)
 
 -- Both partition bound and partition key in describe output
-\d part_c
-               Table "public.part_c"
- Column |  Type   | Collation | Nullable | Default 
---------+---------+-----------+----------+---------
- a      | text    |           |          | 
- b      | integer |           | not null | 0
+\d+ part_c
+                                   Table "public.part_c"
+ Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a      | text    |           |          |         | extended |              | 
+ b      | integer |           | not null | 0       | plain    |              | 
 Partition of: parted FOR VALUES IN ('c')
+Partition constraint: CHECK (((a IS NOT NULL) AND (a = ANY (ARRAY['c'::text]))))
 Partition key: RANGE (b)
 Check constraints:
     "check_a" CHECK (length(a) > 0)
-Number of partitions: 1 (Use \d+ to list them.)
+Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10)
+
+-- a level-2 partition's constraint will include the parent's expressions
+\d+ part_c_1_10
+                                Table "public.part_c_1_10"
+ Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a      | text    |           |          |         | extended |              | 
+ b      | integer |           | not null | 0       | plain    |              | 
+Partition of: part_c FOR VALUES FROM (1) TO (10)
+Partition constraint: CHECK (((a IS NOT NULL) AND (a = ANY (ARRAY['c'::text])) AND (b >= 1) AND (b < 10)))
+Check constraints:
+    "check_a" CHECK (length(a) > 0)
 
 -- Show partition count in the parent's describe output
 -- Tempted to include \d+ output listing partitions with bound info but
@@ -682,6 +697,64 @@ Check constraints:
     "check_a" CHECK (length(a) > 0)
 Number of partitions: 3 (Use \d+ to list them.)
 
+-- show that an unbounded range partition has a CHECK (true) constraint
+CREATE TABLE range_parted4 (a int, b int, c int) PARTITION BY RANGE (abs(a), abs(b), c);
+CREATE TABLE unbounded_range_part PARTITION OF range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (UNBOUNDED, UNBOUNDED, UNBOUNDED);
+\d+ unbounded_range_part
+                           Table "public.unbounded_range_part"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a      | integer |           |          |         | plain   |              | 
+ b      | integer |           |          |         | plain   |              | 
+ c      | integer |           | not null |         | plain   |              | 
+Partition of: range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (UNBOUNDED, UNBOUNDED, UNBOUNDED)
+Partition constraint: CHECK (((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL)))
+
+DROP TABLE unbounded_range_part;
+CREATE TABLE range_parted4_1 PARTITION OF range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (1, UNBOUNDED, UNBOUNDED);
+\d+ range_parted4_1
+                              Table "public.range_parted4_1"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a      | integer |           |          |         | plain   |              | 
+ b      | integer |           |          |         | plain   |              | 
+ c      | integer |           | not null |         | plain   |              | 
+Partition of: range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (1, UNBOUNDED, UNBOUNDED)
+Partition constraint: CHECK (((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (abs(a) <= 1)))
+
+CREATE TABLE range_parted4_2 PARTITION OF range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, UNBOUNDED);
+\d+ range_parted4_2
+                              Table "public.range_parted4_2"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a      | integer |           |          |         | plain   |              | 
+ b      | integer |           |          |         | plain   |              | 
+ c      | integer |           | not null |         | plain   |              | 
+Partition of: range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, UNBOUNDED)
+Partition constraint: CHECK (((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND ((abs(a) > 3) OR ((abs(a) = 3) AND (abs(b) > 4)) OR ((abs(a) = 3) AND (abs(b) = 4) AND (c >= 5))) AND ((abs(a) < 6) OR ((abs(a) = 6) AND (abs(b) <= 7)))))
+
+CREATE TABLE range_parted4_3 PARTITION OF range_parted4 FOR VALUES FROM (6, 8, UNBOUNDED) TO (9, UNBOUNDED, UNBOUNDED);
+\d+ range_parted4_3
+                              Table "public.range_parted4_3"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a      | integer |           |          |         | plain   |              | 
+ b      | integer |           |          |         | plain   |              | 
+ c      | integer |           | not null |         | plain   |              | 
+Partition of: range_parted4 FOR VALUES FROM (6, 8, UNBOUNDED) TO (9, UNBOUNDED, UNBOUNDED)
+Partition constraint: CHECK (((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND ((abs(a) > 6) OR ((abs(a) = 6) AND (abs(b) >= 8))) AND (abs(a) <= 9)))
+
+-- \d does not show the constraint
+\d range_parted4_3
+          Table "public.range_parted4_3"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           |          | 
+ b      | integer |           |          | 
+ c      | integer |           | not null | 
+Partition of: range_parted4 FOR VALUES FROM (6, 8, UNBOUNDED) TO (9, UNBOUNDED, UNBOUNDED)
+
+DROP TABLE range_parted4;
 -- cleanup
 DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3;
 -- comments on partitioned tables columns
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index 6a1f22e..8020745 100644
--- a/src/test/regress/expected/foreign_data.out
+++ b/src/test/regress/expected/foreign_data.out
@@ -1844,6 +1844,7 @@ Partitions: pt2_1 FOR VALUES IN (1)
  c2     | text    |           |          |         |             | extended |              | 
  c3     | date    |           |          |         |             | plain    |              | 
 Partition of: pt2 FOR VALUES IN (1)
+Partition constraint: CHECK (((c1 IS NOT NULL) AND (c1 = ANY (ARRAY[1]))))
 Server: s0
 FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
 
@@ -1914,6 +1915,7 @@ Partitions: pt2_1 FOR VALUES IN (1)
  c2     | text    |           |          |         |             | extended |              | 
  c3     | date    |           |          |         |             | plain    |              | 
 Partition of: pt2 FOR VALUES IN (1)
+Partition constraint: CHECK (((c1 IS NOT NULL) AND (c1 = ANY (ARRAY[1]))))
 Server: s0
 FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
 
@@ -1941,6 +1943,7 @@ Partitions: pt2_1 FOR VALUES IN (1)
  c2     | text    |           |          |         |             | extended |              | 
  c3     | date    |           | not null |         |             | plain    |              | 
 Partition of: pt2 FOR VALUES IN (1)
+Partition constraint: CHECK (((c1 IS NOT NULL) AND (c1 = ANY (ARRAY[1]))))
 Check constraints:
     "p21chk" CHECK (c2 <> ''::text)
 Server: s0
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index 95035c5..bfa04dc 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -598,10 +598,13 @@ CREATE TABLE part_c PARTITION OF parted (b WITH OPTIONS NOT NULL DEFAULT 0) FOR
 CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
 
 -- Partition bound in describe output
-\d part_b
+\d+ part_b
 
 -- Both partition bound and partition key in describe output
-\d part_c
+\d+ part_c
+
+-- a level-2 partition's constraint will include the parent's expressions
+\d+ part_c_1_10
 
 -- Show partition count in the parent's describe output
 -- Tempted to include \d+ output listing partitions with bound info but
@@ -609,6 +612,21 @@ CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
 -- returned.
 \d parted
 
+-- show that an unbounded range partition has a CHECK (true) constraint
+CREATE TABLE range_parted4 (a int, b int, c int) PARTITION BY RANGE (abs(a), abs(b), c);
+CREATE TABLE unbounded_range_part PARTITION OF range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (UNBOUNDED, UNBOUNDED, UNBOUNDED);
+\d+ unbounded_range_part
+DROP TABLE unbounded_range_part;
+CREATE TABLE range_parted4_1 PARTITION OF range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (1, UNBOUNDED, UNBOUNDED);
+\d+ range_parted4_1
+CREATE TABLE range_parted4_2 PARTITION OF range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, UNBOUNDED);
+\d+ range_parted4_2
+CREATE TABLE range_parted4_3 PARTITION OF range_parted4 FOR VALUES FROM (6, 8, UNBOUNDED) TO (9, UNBOUNDED, UNBOUNDED);
+\d+ range_parted4_3
+-- \d does not show the constraint
+\d range_parted4_3
+DROP TABLE range_parted4;
+
 -- cleanup
 DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3;
 
-- 
2.3.2 (Apple Git-55)