0002-Range-partition-bound-specification-syntax-overhaul.patch

From 6cf7bc6a620517b4d1e61f75ebf3675d5106c94d Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Wed, 5 Jul 2017 13:01:21 +0900
Subject: [PATCH 2/2] Range partition bound specification syntax overhaul

---
 doc/src/sgml/ref/create_table.sgml         |  14 +--
 src/backend/catalog/partition.c            |  69 ++++++++-----
 src/backend/parser/parse_utilcmd.c         | 160 ++++++++++++++++++++---------
 src/backend/utils/adt/ruleutils.c          |  35 +++++--
 src/test/regress/expected/create_table.out |  77 ++++++++------
 src/test/regress/expected/inherit.out      |   4 +-
 src/test/regress/expected/insert.out       |  10 +-
 src/test/regress/sql/create_table.sql      |  49 +++++----
 src/test/regress/sql/inherit.sql           |   4 +-
 src/test/regress/sql/insert.sql            |  10 +-
 10 files changed, 280 insertions(+), 152 deletions(-)

diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index b15c19d3d0..4eefe24115 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -300,13 +300,13 @@ FROM ( { <replaceable class="PARAMETER">numeric_literal</replaceable> | <replace
      </para>
 
      <para>
-      Writing <literal>UNBOUNDED</literal> in <literal>FROM</literal>
-      signifies <literal>-infinity</literal> as the lower bound of the
-      corresponding column, whereas when written in <literal>TO</literal>,
-      it signifies <literal>+infinity</literal> as the upper bound.
-      All items following an <literal>UNBOUNDED</literal> item within
-      a <literal>FROM</literal> or <literal>TO</literal> list must also
-      be <literal>UNBOUNDED</literal>.
+      <literal>UNBOUNDED</literal> can be specified only for the first column
+      of the partition key and it must not be followed by values for the
+      remaining columns.  Specifying <literal>UNBOUNDED</literal> in
+      <literal>FROM</literal> signifies <literal>-infinity</literal> as the
+      lower bound of the corresponding column, whereas when specified in
+      <literal>TO</literal>, it signifies <literal>+infinity</literal> as the
+      upper bound.
      </para>
 
      <para>
diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c
index 96760a0f05..4a788719e5 100644
--- a/src/backend/catalog/partition.c
+++ b/src/backend/catalog/partition.c
@@ -377,15 +377,13 @@ RelationBuildPartitionDesc(Relation rel)
 					}
 
 					/*
-					 * If either of them has infinite element, we can't equate
-					 * them.  Even when both are infinite, they'd have
-					 * opposite signs, because only one of cur and prev is a
-					 * lower bound).
+					 * If either of them has infinite element, we can't invoke
+					 * the comparison procedure.
 					 */
 					if (cur->content[j] != RANGE_DATUM_FINITE ||
 						prev->content[j] != RANGE_DATUM_FINITE)
 					{
-						is_distinct = true;
+						is_distinct = (cur->content[j] != prev->content[j]);
 						break;
 					}
 					cmpval = FunctionCall2Coll(&key->partsupfunc[j],
@@ -1468,17 +1466,14 @@ get_range_key_properties(PartitionKey key, int keynum,
  *		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:
+ * If cu happens to be -infinity (if a user didn't specify cu, it's treated
+ * as such), the last line effectively becomes:
  *
- *	(b < bu) OR (b = bu), which is simplified to (b <= bu)
+ *	(b < bu) OR (b = bu AND FALSE) which is simplied to (b < bu)
  *
  * In most common cases with only one partition column, say a, the following
- * expression tree will be generated: a IS NOT NULL AND 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
- * partition keys.
+ * expression tree will be generated: a IS NOT NULL AND a >= al AND a < au,
+ * provided both al and au are finite values.
  *
  * If we end up with an empty result list, we return a single-member list
  * containing a constant TRUE, because callers expect a non-empty list.
@@ -1688,12 +1683,10 @@ get_qual_for_range(PartitionKey key, PartitionBoundSpec *spec)
 
 				/*
 				 * For the non-last columns of this arm, use the EQ operator.
-				 * For the last finite-valued column, use LE.
+				 * and LE for the last column.
 				 */
 				if (j - i < current_or_arm)
 					strategy = BTEqualStrategyNumber;
-				else if (udatum_next && udatum_next->infinite)
-					strategy = BTLessEqualStrategyNumber;
 				else
 					strategy = BTLessStrategyNumber;
 
@@ -2099,17 +2092,26 @@ make_one_range_bound(PartitionKey key, int index, List *datums, bool lower)
 		PartitionRangeDatum *datum = castNode(PartitionRangeDatum, lfirst(lc));
 
 		/* What's contained in this range datum? */
-		bound->content[i] = !datum->infinite
-			? RANGE_DATUM_FINITE
-			: (lower ? RANGE_DATUM_NEG_INF
-			   : RANGE_DATUM_POS_INF);
-
-		if (bound->content[i] == RANGE_DATUM_FINITE)
+		if (datum->infinite)
+		{
+			/*
+			 * Infinite datums for partitioning column other than the first
+			 * column are to be interpreted as negative infinite.
+			 */
+			if (i == 0)
+				bound->content[i] = lower ? RANGE_DATUM_NEG_INF
+										  : RANGE_DATUM_POS_INF;
+			else
+				bound->content[i] = RANGE_DATUM_NEG_INF;
+		}
+		else
 		{
 			Const	   *val = castNode(Const, datum->value);
 
 			if (val->constisnull)
 				elog(ERROR, "invalid range bound datum");
+
+			bound->content[i] = RANGE_DATUM_FINITE;
 			bound->datums[i] = val->constvalue;
 		}
 
@@ -2151,17 +2153,28 @@ partition_rbound_cmp(PartitionKey key,
 	{
 		/*
 		 * First, handle cases involving infinity, which don't require
-		 * invoking the comparison proc.
+		 * invoking the comparison proc.  Infinite datums in only the first
+		 * column are significant as a user-specified boundary condition.
+		 * Its presence in non-first columns is an internal implementation
+		 * detail and both content1[i] and content2[i] would contain
+		 * RANGE_DATUM_NEG_INF in that case.  We tie-break by considering
+		 * the lower bound as the greater of the two.
 		 */
 		if (content1[i] != RANGE_DATUM_FINITE &&
 			content2[i] != RANGE_DATUM_FINITE)
+			return i == 0 ? (content1[i] < content2[i] ? -1 : 1)
+						  : (lower1 ? 1 : -1);
 
 			/*
-			 * Both are infinity, so they are equal unless one is negative
-			 * infinity and other positive (or vice versa)
-			 */
-			return content1[i] == content2[i] ? 0
-				: (content1[i] < content2[i] ? -1 : 1);
+			 * Both are infinity.  If both have the same sign, we know that
+			 * they come from the upper bound and the lower bound of two
+			 * contiguous partitions, respectively.  We tie-break in this
+			 * case by considering the lower bound as the greater one of the
+			 * two.  If they have different signs, then the answer is obvious.
+			 *
+			return content1[i] == content2[i]
+						? (lower1 ? 1 : -1)
+						: (content1[i] < content2[i] ? -1 : 1);*/
 		else if (content1[i] != RANGE_DATUM_FINITE)
 			return content1[i] == RANGE_DATUM_NEG_INF ? -1 : 1;
 		else if (content2[i] != RANGE_DATUM_FINITE)
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index ee5f3a3a52..34390661cd 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -3361,8 +3361,7 @@ transformPartitionBound(ParseState *pstate, Relation parent,
 	}
 	else if (strategy == PARTITION_STRATEGY_RANGE)
 	{
-		ListCell   *cell1,
-				   *cell2;
+		ListCell   *cell;
 		int			i,
 					j;
 		bool		seen_unbounded;
@@ -3373,61 +3372,45 @@ transformPartitionBound(ParseState *pstate, Relation parent,
 					 errmsg("invalid bound specification for a range partition"),
 					 parser_errposition(pstate, exprLocation((Node *) spec))));
 
-		if (list_length(spec->lowerdatums) != partnatts)
+		if (list_length(spec->lowerdatums) > partnatts)
 			ereport(ERROR,
 					(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
-					 errmsg("FROM must specify exactly one value per partitioning column")));
-		if (list_length(spec->upperdatums) != partnatts)
+					 errmsg("FROM cannot contain more values than the number of partitioning columns"),
+					 errdetail_plural("\"%s\" has %d partitioning column.",
+									  "\"%s\" has %d partitioning columns.",
+									  partnatts,
+									  RelationGetRelationName(parent),
+									  partnatts)));
+		if (list_length(spec->upperdatums) > partnatts)
 			ereport(ERROR,
 					(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
-					 errmsg("TO must specify exactly one value per partitioning column")));
-
-		/*
-		 * Check that no finite value follows an UNBOUNDED item in either of
-		 * lower and upper bound lists.
-		 */
-		seen_unbounded = false;
-		foreach(cell1, spec->lowerdatums)
-		{
-			PartitionRangeDatum *ldatum = castNode(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)
-		{
-			PartitionRangeDatum *rdatum = castNode(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))));
-		}
+					 errmsg("TO cannot contain more values than the number of partitioning columns"),
+					 errdetail_plural("\"%s\" has %d partitioning column.",
+									  "\"%s\" has %d partitioning columns.",
+									  partnatts,
+									  RelationGetRelationName(parent),
+									  partnatts)));
 
 		/* Transform all the constants */
+		result_spec->lowerdatums = NIL;
 		i = j = 0;
-		result_spec->lowerdatums = result_spec->upperdatums = NIL;
-		forboth(cell1, spec->lowerdatums, cell2, spec->upperdatums)
+		seen_unbounded = false;
+		foreach(cell, spec->lowerdatums)
 		{
-			PartitionRangeDatum *ldatum = (PartitionRangeDatum *) lfirst(cell1);
-			PartitionRangeDatum *rdatum = (PartitionRangeDatum *) lfirst(cell2);
+			PartitionRangeDatum *ldatum = castNode(PartitionRangeDatum,
+												   lfirst(cell));
 			char	   *colname;
 			Oid			coltype;
 			int32		coltypmod;
 			A_Const    *con;
 			Const	   *value;
 
+			if (seen_unbounded)
+				ereport(ERROR,
+						(errcode(ERRCODE_DATATYPE_MISMATCH),
+						 errmsg("cannot specify a value after UNBOUNDED"),
+						 parser_errposition(pstate, exprLocation((Node *) ldatum))));
+
 			/* Get the column's name in case we need to output an error */
 			if (key->partattrs[i] != 0)
 				colname = get_relid_attribute_name(RelationGetRelid(parent),
@@ -3457,6 +3440,72 @@ transformPartitionBound(ParseState *pstate, Relation parent,
 				ldatum = copyObject(ldatum);	/* don't scribble on input */
 				ldatum->value = (Node *) value;
 			}
+			else if (i > 0)
+			{
+				ereport(ERROR,
+						(errcode(ERRCODE_DATATYPE_MISMATCH),
+						 errmsg("cannot specify UNBOUNDED for columns other than the first column"),
+						 parser_errposition(pstate, exprLocation((Node *) ldatum))));
+			}
+			else
+				seen_unbounded = true;
+
+			result_spec->lowerdatums = lappend(result_spec->lowerdatums,
+											   ldatum);
+			++i;
+		}
+
+		/*
+		 * Fill in infinite datums for the remaining columns.  Note that
+		 * infinite datums for non-first columns are always interpreted as
+		 * negative infinity by the interal partitioning code.
+		 */
+		for (j = i; j < partnatts; j++)
+		{
+			PartitionRangeDatum *datum = makeNode(PartitionRangeDatum);
+
+			datum->infinite = true;
+			datum->value = NULL;
+			datum->location = -1;
+			result_spec->lowerdatums = lappend(result_spec->lowerdatums,
+											   datum);
+		}
+		Assert(list_length(result_spec->lowerdatums) == partnatts);
+
+		result_spec->upperdatums = NIL;
+		i = j = 0;
+		seen_unbounded = false;
+		foreach(cell, spec->upperdatums)
+		{
+			PartitionRangeDatum *rdatum = castNode(PartitionRangeDatum,
+												   lfirst(cell));
+			char	   *colname;
+			Oid			coltype;
+			int32		coltypmod;
+			A_Const    *con;
+			Const	   *value;
+
+			if (seen_unbounded)
+				ereport(ERROR,
+						(errcode(ERRCODE_DATATYPE_MISMATCH),
+						 errmsg("cannot specify a value after UNBOUNDED"),
+						 parser_errposition(pstate, exprLocation((Node *) rdatum))));
+
+			/* Get the column's name in case we need to output an error */
+			if (key->partattrs[i] != 0)
+				colname = get_relid_attribute_name(RelationGetRelid(parent),
+												   key->partattrs[i]);
+			else
+			{
+				colname = deparse_expression((Node *) list_nth(partexprs, j),
+											 deparse_context_for(RelationGetRelationName(parent),
+																 RelationGetRelid(parent)),
+											 false, false);
+				++j;
+			}
+			/* Need its type data too */
+			coltype = get_partition_col_typid(key, i);
+			coltypmod = get_partition_col_typmod(key, i);
 
 			if (rdatum->value)
 			{
@@ -3471,14 +3520,33 @@ transformPartitionBound(ParseState *pstate, Relation parent,
 				rdatum = copyObject(rdatum);	/* don't scribble on input */
 				rdatum->value = (Node *) value;
 			}
+			else if (i > 0)
+			{
+				ereport(ERROR,
+						(errcode(ERRCODE_DATATYPE_MISMATCH),
+						 errmsg("cannot specify UNBOUNDED for columns other than the first column"),
+						 parser_errposition(pstate, exprLocation((Node *) rdatum))));
+			}
+			else
+				seen_unbounded = true;
 
-			result_spec->lowerdatums = lappend(result_spec->lowerdatums,
-											   ldatum);
 			result_spec->upperdatums = lappend(result_spec->upperdatums,
 											   rdatum);
-
 			++i;
 		}
+
+		/* See the comment above. */
+		for (j = i; j < partnatts; j++)
+		{
+			PartitionRangeDatum *datum = makeNode(PartitionRangeDatum);
+
+			datum->infinite = true;
+			datum->value = NULL;
+			datum->location = -1;
+			result_spec->upperdatums = lappend(result_spec->upperdatums,
+											   datum);
+		}
+		Assert(list_length(result_spec->upperdatums) == partnatts);
 	}
 	else
 		elog(ERROR, "unexpected partition strategy: %d", (int) strategy);
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 18d9e27d1e..25be1bad52 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -8646,6 +8646,7 @@ get_rule_expr(Node *node, deparse_context *context,
 				PartitionBoundSpec *spec = (PartitionBoundSpec *) node;
 				ListCell   *cell;
 				char	   *sep;
+				int			i;
 
 				switch (spec->strategy)
 				{
@@ -8674,39 +8675,57 @@ get_rule_expr(Node *node, deparse_context *context,
 
 						appendStringInfoString(buf, "FOR VALUES FROM (");
 						sep = "";
+						i = 0;
 						foreach(cell, spec->lowerdatums)
 						{
 							PartitionRangeDatum *datum =
 							castNode(PartitionRangeDatum, lfirst(cell));
 
-							appendStringInfoString(buf, sep);
-							if (datum->infinite)
+							/*
+							 * We display UNBOUNDED only if it's known that
+							 * it was user-entered infinite datum, which is
+							 * allowed only for the first paritioning column.
+							 * Any other infinite datums have been internally
+							 * added by the system which we don't want to
+							 * display in the deparsed output.
+							 */
+							if (datum->infinite && i == 0)
+							{
 								appendStringInfoString(buf, "UNBOUNDED");
-							else
+								break;
+							}
+							else if (!datum->infinite)
 							{
 								Const	   *val = castNode(Const, datum->value);
 
+								appendStringInfoString(buf, sep);
 								get_const_expr(val, context, -1);
+								sep = ", ";
 							}
-							sep = ", ";
+							i++;
 						}
 						appendStringInfoString(buf, ") TO (");
 						sep = "";
+						i = 0;
 						foreach(cell, spec->upperdatums)
 						{
 							PartitionRangeDatum *datum =
 							castNode(PartitionRangeDatum, lfirst(cell));
 
-							appendStringInfoString(buf, sep);
-							if (datum->infinite)
+							if (datum->infinite && i == 0)
+							{
 								appendStringInfoString(buf, "UNBOUNDED");
-							else
+								break;		/* See the comment above. */
+							}
+							else if (!datum->infinite)
 							{
 								Const	   *val = castNode(Const, datum->value);
 
+								appendStringInfoString(buf, sep);
 								get_const_expr(val, context, -1);
+								sep = ", ";
 							}
-							sep = ", ";
+							i++;
 						}
 						appendStringInfoString(buf, ")");
 						break;
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index b6f794e1c2..beba88541e 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -505,22 +505,44 @@ CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES IN ('a');
 ERROR:  invalid bound specification for a range partition
 LINE 1: ...BLE fail_part PARTITION OF range_parted FOR VALUES IN ('a');
                                                               ^
--- each of start and end bounds must have same number of values as the
+-- each of start and end bounds cannot contain more values than the
 -- length of the partition key
 CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM ('a', 1) TO ('z');
-ERROR:  FROM must specify exactly one value per partitioning column
+ERROR:  FROM cannot contain more values than the number of partitioning columns
+DETAIL:  "range_parted" has 1 partitioning column.
 CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM ('a') TO ('z', 1);
-ERROR:  TO must specify exactly one value per partitioning column
+ERROR:  TO cannot contain more values than the number of partitioning columns
+DETAIL:  "range_parted" has 1 partitioning column.
+CREATE TABLE multicol_range_parted (
+	a int,
+	b int,
+	c int
+) partition by range (a, b, c);
+CREATE TABLE fail_part PARTITION OF multicol_range_parted FOR VALUES FROM (1) TO (1, 10, 9, 10, 23);
+ERROR:  TO cannot contain more values than the number of partitioning columns
+DETAIL:  "multicol_range_parted" has 3 partitioning columns.
+-- although they are allowed to contain fewer
+CREATE TABLE multicol_range_parted1 PARTITION OF multicol_range_parted FOR VALUES FROM (1) TO (1, 10);
+CREATE TABLE multicol_range_parted2 PARTITION OF multicol_range_parted FOR VALUES FROM (1, 10) TO (1, 10, 1);
 -- cannot specify null values in range bounds
-CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM (null) TO (unbounded);
+CREATE TABLE fail_part PARTITION OF multicol_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...
+-- cannot specify UNBOUNDED for non-first columns
+CREATE TABLE fail_part PARTITION OF multicol_range_parted FOR VALUES FROM (1, UNBOUNDED, 1) TO (UNBOUNDED, 1, 1);
+ERROR:  cannot specify UNBOUNDED for columns other than the first column
+LINE 1: ...TION OF multicol_range_parted FOR VALUES FROM (1, UNBOUNDED,...
+                                                             ^
+CREATE TABLE multicol_range_parted0 PARTITION OF multicol_range_parted FOR VALUES FROM (UNBOUNDED) TO (1);
+-- cannot specify any values after unbounded
+CREATE TABLE fail_part PARTITION OF multicol_range_parted FOR VALUES FROM (UNBOUNDED, 1) TO (1);
+ERROR:  cannot specify a value after UNBOUNDED
+LINE 1: ...multicol_range_parted FOR VALUES FROM (UNBOUNDED, 1) TO (1);
+                                                             ^
+CREATE TABLE fail_part PARTITION OF multicol_range_parted FOR VALUES FROM (UNBOUNDED, UNBOUNDED) TO (1);
+ERROR:  cannot specify a value after UNBOUNDED
+LINE 1: ...multicol_range_parted FOR VALUES FROM (UNBOUNDED, UNBOUNDED)...
                                                              ^
-DROP TABLE range_parted_multicol;
+DROP TABLE multicol_range_parted;
 -- check if compatible with the specified parent
 -- cannot create as partition of a non-partitioned table
 CREATE TABLE unparted (
@@ -595,19 +617,14 @@ CREATE TABLE range_parted3 (
 	a int,
 	b int
 ) PARTITION BY RANGE (a, (b+1));
-CREATE TABLE part00 PARTITION OF range_parted3 FOR VALUES FROM (0, unbounded) TO (0, unbounded);
-CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (0, unbounded) TO (0, 1);
+CREATE TABLE part00 PARTITION OF range_parted3 FOR VALUES FROM (unbounded) TO (0);
+CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (-1) TO (0);
 ERROR:  partition "fail_part" would overlap partition "part00"
-CREATE TABLE part10 PARTITION OF range_parted3 FOR VALUES FROM (1, unbounded) TO (1, 1);
+CREATE TABLE part10 PARTITION OF range_parted3 FOR VALUES FROM (0) TO (1, 1);
 CREATE TABLE part11 PARTITION OF range_parted3 FOR VALUES FROM (1, 1) TO (1, 10);
-CREATE TABLE part12 PARTITION OF range_parted3 FOR VALUES FROM (1, 10) TO (1, unbounded);
+CREATE TABLE part12 PARTITION OF range_parted3 FOR VALUES FROM (1, 10) TO (unbounded);
 CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (1, 10) TO (1, 20);
 ERROR:  partition "fail_part" would overlap partition "part12"
--- cannot create a partition that says column b is allowed to range
--- from -infinity to +infinity, while there exist partitions that have
--- more specific ranges
-CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (1, unbounded) TO (1, unbounded);
-ERROR:  partition "fail_part" would overlap partition "part10"
 -- check schema propagation from parent
 CREATE TABLE parted (
 	a text,
@@ -708,7 +725,7 @@ Number of partitions: 3 (Use \d+ to list them.)
 
 -- check that we get the expected partition constraints
 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);
+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 | Storage | Stats target | Description 
@@ -716,11 +733,11 @@ CREATE TABLE unbounded_range_part PARTITION OF range_parted4 FOR VALUES FROM (UN
  a      | integer |           |          |         | plain   |              | 
  b      | integer |           |          |         | plain   |              | 
  c      | integer |           |          |         | plain   |              | 
-Partition of: range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (UNBOUNDED, UNBOUNDED, UNBOUNDED)
+Partition of: range_parted4 FOR VALUES FROM (UNBOUNDED) TO (UNBOUNDED)
 Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c 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);
+CREATE TABLE range_parted4_1 PARTITION OF range_parted4 FOR VALUES FROM (UNBOUNDED) TO (1);
 \d+ range_parted4_1
                               Table "public.range_parted4_1"
  Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
@@ -728,10 +745,10 @@ CREATE TABLE range_parted4_1 PARTITION OF range_parted4 FOR VALUES FROM (UNBOUND
  a      | integer |           |          |         | plain   |              | 
  b      | integer |           |          |         | plain   |              | 
  c      | integer |           |          |         | plain   |              | 
-Partition of: range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (1, UNBOUNDED, UNBOUNDED)
-Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL) AND (abs(a) <= 1))
+Partition of: range_parted4 FOR VALUES FROM (UNBOUNDED) TO (1)
+Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c 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);
+CREATE TABLE range_parted4_2 PARTITION OF range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7);
 \d+ range_parted4_2
                               Table "public.range_parted4_2"
  Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
@@ -739,10 +756,10 @@ CREATE TABLE range_parted4_2 PARTITION OF range_parted4 FOR VALUES FROM (3, 4, 5
  a      | integer |           |          |         | plain   |              | 
  b      | integer |           |          |         | plain   |              | 
  c      | integer |           |          |         | plain   |              | 
-Partition of: range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, UNBOUNDED)
-Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c 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))))
+Partition of: range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7)
+Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c 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);
+CREATE TABLE range_parted4_3 PARTITION OF range_parted4 FOR VALUES FROM (6, 7) TO (9);
 \d+ range_parted4_3
                               Table "public.range_parted4_3"
  Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
@@ -750,8 +767,8 @@ CREATE TABLE range_parted4_3 PARTITION OF range_parted4 FOR VALUES FROM (6, 8, U
  a      | integer |           |          |         | plain   |              | 
  b      | integer |           |          |         | plain   |              | 
  c      | integer |           |          |         | plain   |              | 
-Partition of: range_parted4 FOR VALUES FROM (6, 8, UNBOUNDED) TO (9, UNBOUNDED, UNBOUNDED)
-Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL) AND ((abs(a) > 6) OR ((abs(a) = 6) AND (abs(b) >= 8))) AND (abs(a) <= 9))
+Partition of: range_parted4 FOR VALUES FROM (6, 7) TO (9)
+Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL) AND ((abs(a) > 6) OR ((abs(a) = 6) AND (abs(b) >= 7))) AND (abs(a) < 9))
 
 DROP TABLE range_parted4;
 -- cleanup
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 35d182d599..cd9072ec09 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1831,12 +1831,12 @@ 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 mcrparted0 partition of mcrparted for values from (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);
+create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (unbounded);
 explain (costs off) select * from mcrparted where a = 0;	-- scans mcrparted0
           QUERY PLAN          
 ------------------------------
diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out
index d1153f410b..0424a75738 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -439,12 +439,12 @@ 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 mcrparted0 partition of mcrparted for values from (unbounded) to (1);
+create table mcrparted1 partition of mcrparted for values from (2, 1) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 6) to (11);
 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);
+create table mcrparted4 partition of mcrparted for values from (21) to (30, 20);
+create table mcrparted5 partition of mcrparted for values from (30, 21, 20) to (unbounded);
 -- routed to mcrparted0
 insert into mcrparted values (0, 1, 1);
 insert into mcrparted0 values (0, 1, 1);
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index cb7aa5bbc6..30ea2f0834 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -477,18 +477,34 @@ CREATE TABLE range_parted (
 
 -- trying to specify list for range partitioned table
 CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES IN ('a');
--- each of start and end bounds must have same number of values as the
+
+-- each of start and end bounds cannot contain more values than the
 -- length of the partition key
 CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM ('a', 1) TO ('z');
 CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM ('a') TO ('z', 1);
 
+CREATE TABLE multicol_range_parted (
+	a int,
+	b int,
+	c int
+) partition by range (a, b, c);
+CREATE TABLE fail_part PARTITION OF multicol_range_parted FOR VALUES FROM (1) TO (1, 10, 9, 10, 23);
+-- although they are allowed to contain fewer
+CREATE TABLE multicol_range_parted1 PARTITION OF multicol_range_parted FOR VALUES FROM (1) TO (1, 10);
+CREATE TABLE multicol_range_parted2 PARTITION OF multicol_range_parted FOR VALUES FROM (1, 10) TO (1, 10, 1);
+
 -- cannot specify null values in range bounds
-CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM (null) TO (unbounded);
+CREATE TABLE fail_part PARTITION OF multicol_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;
+-- cannot specify UNBOUNDED for non-first columns
+CREATE TABLE fail_part PARTITION OF multicol_range_parted FOR VALUES FROM (1, UNBOUNDED, 1) TO (UNBOUNDED, 1, 1);
+CREATE TABLE multicol_range_parted0 PARTITION OF multicol_range_parted FOR VALUES FROM (UNBOUNDED) TO (1);
+
+-- cannot specify any values after unbounded
+CREATE TABLE fail_part PARTITION OF multicol_range_parted FOR VALUES FROM (UNBOUNDED, 1) TO (1);
+CREATE TABLE fail_part PARTITION OF multicol_range_parted FOR VALUES FROM (UNBOUNDED, UNBOUNDED) TO (1);
+
+DROP TABLE multicol_range_parted;
 
 -- check if compatible with the specified parent
 
@@ -557,19 +573,14 @@ CREATE TABLE range_parted3 (
 	b int
 ) PARTITION BY RANGE (a, (b+1));
 
-CREATE TABLE part00 PARTITION OF range_parted3 FOR VALUES FROM (0, unbounded) TO (0, unbounded);
-CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (0, unbounded) TO (0, 1);
+CREATE TABLE part00 PARTITION OF range_parted3 FOR VALUES FROM (unbounded) TO (0);
+CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (-1) TO (0);
 
-CREATE TABLE part10 PARTITION OF range_parted3 FOR VALUES FROM (1, unbounded) TO (1, 1);
+CREATE TABLE part10 PARTITION OF range_parted3 FOR VALUES FROM (0) TO (1, 1);
 CREATE TABLE part11 PARTITION OF range_parted3 FOR VALUES FROM (1, 1) TO (1, 10);
-CREATE TABLE part12 PARTITION OF range_parted3 FOR VALUES FROM (1, 10) TO (1, unbounded);
+CREATE TABLE part12 PARTITION OF range_parted3 FOR VALUES FROM (1, 10) TO (unbounded);
 CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (1, 10) TO (1, 20);
 
--- cannot create a partition that says column b is allowed to range
--- from -infinity to +infinity, while there exist partitions that have
--- more specific ranges
-CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (1, unbounded) TO (1, unbounded);
-
 -- check schema propagation from parent
 
 CREATE TABLE parted (
@@ -626,14 +637,14 @@ CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
 
 -- check that we get the expected partition constraints
 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);
+CREATE TABLE unbounded_range_part PARTITION OF range_parted4 FOR VALUES FROM (UNBOUNDED) TO (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);
+CREATE TABLE range_parted4_1 PARTITION OF range_parted4 FOR VALUES FROM (UNBOUNDED) TO (1);
 \d+ range_parted4_1
-CREATE TABLE range_parted4_2 PARTITION OF range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, UNBOUNDED);
+CREATE TABLE range_parted4_2 PARTITION OF range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7);
 \d+ range_parted4_2
-CREATE TABLE range_parted4_3 PARTITION OF range_parted4 FOR VALUES FROM (6, 8, UNBOUNDED) TO (9, UNBOUNDED, UNBOUNDED);
+CREATE TABLE range_parted4_3 PARTITION OF range_parted4 FOR VALUES FROM (6, 7) TO (9);
 \d+ range_parted4_3
 DROP TABLE range_parted4;
 
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 70fe971d51..5e86dc3f31 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -647,12 +647,12 @@ 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 mcrparted0 partition of mcrparted for values from (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);
+create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (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
diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql
index 83c3ad8f53..c974aa9ed4 100644
--- a/src/test/regress/sql/insert.sql
+++ b/src/test/regress/sql/insert.sql
@@ -293,12 +293,12 @@ 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 mcrparted0 partition of mcrparted for values from (unbounded) to (1);
+create table mcrparted1 partition of mcrparted for values from (2, 1) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 6) to (11);
 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);
+create table mcrparted4 partition of mcrparted for values from (21) to (30, 20);
+create table mcrparted5 partition of mcrparted for values from (30, 21, 20) to (unbounded);
 
 -- routed to mcrparted0
 insert into mcrparted values (0, 1, 1);
-- 
2.11.0