On 2017/06/23 17:00, Dean Rasheed wrote:
> On 23 June 2017 at 08:01, Ashutosh Bapat
> <ashutosh.ba...@enterprisedb.com> wrote:
>> The way we have designed our syntax, we don't have a way to tell that
>> p3 comes after p2 and they have no gap between those. But I don't
>> think that's your question. What you are struggling with is a way to
>> specify a lower bound (10, +infinity) so that anything with i1 > 10
>> would go to partition 3.
>
> I think actually there is a fundamental problem here, which arises
> because UNBOUNDED has 2 different meanings depending on context, and
> thus it is not possible in general to specify the start of one range
> to be equal to the end of the previous range, as is necessary to get
> contiguous non-overlapping ranges.
Okay, I thought about this a bit more and I think I realize that this
arbitrary-sounding restriction of allowing only -infinity in FROM and
+infinity in TO limits the usefulness of the feature to specify infinite
bounds at all.
> Note that this isn't just a problem for floating point datatypes
> either, it also applies to other types such as strings. For example,
> given a partition over (text, int) types defined with the following
> values:
>
> FROM ('a', UNBOUNDED) TO ('b', UNBOUNDED)
>
> which is equivalent to
>
> FROM ('a', -INFINITY) TO ('b', +INFINITY)
>
> where should the next range start?
>
> Even if you were to find a way to specify "the next string after 'b'",
> it wouldn't exactly be pretty. The problem is that the above partition
> corresponds to "all the strings starting with 'a', plus the string
> 'b', which is pretty ugly. A neater way to define the pair of ranges
> in this case would be:
>
> FROM ('a', -INFINITY) TO ('b', -INFINITY)
> FROM ('b', -INFINITY) TO ('c', -INFINITY)
>
> since then all strings starting with 'a' would fall into the first
> partition and all the strings starting with 'b' would fall into the
> second one.
I agree that a valid use case like the one above is awkward to express
currently.
> Currently, when there are 2 partition columns, the partition
> constraint is defined as
>
> (a is not null) and (b is not null)
> and
> (a > al or (a = al and b >= bl))
> and
> (a < au or (a = au and b < bu))
>
> if the upper bound bu were allowed to be -INFINITY (something that
> should probably be forbidden unless the previous column's upper bound
> were finite), then this would simplify to
>
> (a is not null) and (b is not null)
> and
> (a > al or (a = al and b >= bl))
> and
> (a < au)
>
> and in the example above, where al is -INFINITY, it would further simplify to
>
> (a is not null) and (b is not null)
> and
> (a >= al)
> and
> (a < au)
>
> There would also be a similar simplification possible if the lower
> bound of a partition column were allowed to be +INFINITY.
Yep.
> So, I think that having UNBOUNDED represent both -INFINITY and
> +INFINITY depending on context is a design flaw, and that we need to
> allow both -INFINITY and +INFINITY as upper and lower bounds (provided
> they are preceded by a column with a finite bound). I think that, in
> general, that's the only way to allow contiguous non-overlapping
> partitions to be defined on multiple columns.
Alright, I spent some time implementing a patch to allow specifying
-infinity and +infinity in arbitrary ways. Of course, it prevents
nonsensical inputs with appropriate error messages.
When implementing the same, I initially thought that the only grammar
modification required is to allow specifying a sign before the unbounded
keyword, but thought it sounded strange to call the actual bound values
-unbounded and +unbounded. While the keyword "unbounded" describes the
property of being unbounded, actual values are really -infinity and
+infinity. So, I decided to instead modify the grammar to accept
-infinity and +infinity in the FROM and TO lists. The sign is optional
and in its absence, infinity in FROM means -infinity and vice versa. This
decision may be seen as controversial, now that we are actually in beta,
if we decide to go with this patch at all.
Some adjustments were required in the logic in partition.c that depended
on the old assumption that all infinite values in the lower bound meant
-infinity and vice versa. That includes get_qual_for_range() being able
to simplify the partition constraint as Dean mentioned in his email.
When testing the patch, I realized that the current code in
check_new_partition_bound() that checks for range partition overlap had a
latent bug that resulted in false positives for the new cases that the new
less restrictive syntax allowed. I spent some time simplifying that code
while also fixing the aforementioned bug. It's implemented in the
attached patch 0001.
0002 is the patch that implements the new syntax.
It's possible that this won't be considered a PG 10 open item but a new
feature and so PG 11 material, as Ashutosh also wondered.
Thanks,
Amit
From 3487d63069a2ba7cb205ded31be235bcf08fc0fa Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Thu, 29 Jun 2017 16:39:07 +0900
Subject: [PATCH 1/2] Simplify code that checks range partition overlap
While making the logic a sightly easier to reason about, a latent
bug in the logic was fixed in this simplification process, whereby
false positives would occur (partitions that don't actually overlap
would be concluded to overlap).
---
src/backend/catalog/partition.c | 64 ++++++++++--------------------
src/test/regress/expected/create_table.out | 2 +-
2 files changed, 23 insertions(+), 43 deletions(-)
diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c
index f8c55b1fe7..457b2fef66 100644
--- a/src/backend/catalog/partition.c
+++ b/src/backend/catalog/partition.c
@@ -753,68 +753,48 @@ check_new_partition_bound(char *relname, Relation parent,
boundinfo->strategy ==
PARTITION_STRATEGY_RANGE);
/*
- * Firstly, find the greatest range
bound that is less
- * than or equal to the new lower bound.
+ * Find the greatest range bound among
those of the
+ * existing partitions that is less
than or equal to the
+ * new lower bound.
*/
off1 = partition_bound_bsearch(key,
boundinfo, lower, true,
&equal);
/*
- * off1 == -1 means that all existing
bounds are greater
- * than the new lower bound. In that
case and the case
- * where no partition is defined
between the bounds at
- * off1 and off1 + 1, we have a "gap"
in the range that
- * could be occupied by the new
partition. We confirm if
- * so by checking whether the new upper
bound is confined
- * within the gap.
+ * If there is a "gap" in the range
immediately on the
+ * right of the returned bound (one at
off1, which the new
+ * lower bound is greater than or equal
to), then the
+ * new partition could occupy the same.
Only if its upper
+ * bound is also confined within the
gap.
*/
- if (!equal && boundinfo->indexes[off1 +
1] < 0)
+ if (boundinfo->indexes[off1 + 1] < 0)
{
+ equal = false;
off2 =
partition_bound_bsearch(key, boundinfo, upper,
true, &equal);
-
/*
- * If the new upper bound is
returned to be equal to
- * the bound at off2, the
latter must be the upper
- * bound of some partition with
which the new
- * partition clearly overlaps.
- *
- * Also, if bound at off2 is
not same as the one
- * returned for the new lower
bound (IOW, off1 !=
- * off2), then the new
partition overlaps at least one
- * partition.
+ * If the new upper bound turns
out to be crossing
+ * over the available gap, then
the new partition
+ * overlaps with the
partition(s) on the right.
+ * However, because a
partition's upper bound can be
+ * equal to the lower bound of
the partition
+ * immediately on the the
right, discount that case.
*/
- if (equal || off1 != off2)
+ if (off2 > off1 + 1 || ((off2
== off1 + 1) && !equal))
{
overlap = true;
/*
- * The bound at off2
could be the lower bound of
- * the partition with
which the new partition
- * overlaps. In that
case, use the upper bound
- * (that is, the bound
at off2 + 1) to get the
- * index of that
partition.
+ * Although the new
upper bound might have crossed
+ * over multiple
partitions on the right, we only
+ * ever report the
immediately adjacent one.
*/
- if
(boundinfo->indexes[off2] < 0)
- with =
boundinfo->indexes[off2 + 1];
- else
- with =
boundinfo->indexes[off2];
+ with =
boundinfo->indexes[off1 + 2];
}
}
else
{
- /*
- * Equal has been set to true
and there is no "gap"
- * between the bound at off1
and that at off1 + 1, so
- * the new partition will
overlap some partition. In
- * the former case, the new
lower bound is found to be
- * equal to the bound at off1,
which could only ever
- * be true if the latter is the
lower bound of some
- * partition. It's clear in
such a case that the new
- * partition overlaps that
partition, whose index we
- * get using its upper bound
(that is, using the bound
- * at off1 + 1).
- */
+ /* There is no gap. */
overlap = true;
with = boundinfo->indexes[off1
+ 1];
}
diff --git a/src/test/regress/expected/create_table.out
b/src/test/regress/expected/create_table.out
index fb8745be04..b6f794e1c2 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -589,7 +589,7 @@ CREATE TABLE part3 PARTITION OF range_parted2 FOR VALUES
FROM (30) TO (40);
CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (10) TO (30);
ERROR: partition "fail_part" would overlap partition "part2"
CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (10) TO (50);
-ERROR: partition "fail_part" would overlap partition "part3"
+ERROR: partition "fail_part" would overlap partition "part2"
-- now check for multi-column range partition key
CREATE TABLE range_parted3 (
a int,
--
2.11.0
From 52574b20e53e5da94ffe256ae30b0bb19503659a Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Thu, 29 Jun 2017 13:50:27 +0900
Subject: [PATCH 2/2] Relax some rules about unbounded range partition bounds
Currently, unbounded means - or + infinity depending on whether
it appears in FROM or TO, respectively. That rule limits the
usefulness of being able to specify unboundedness for range partition
columns at all. For example, in case of multi-column partition key,
one cannot specify the same bound value as both a partition's upper
bound and the next partition's lower bound to establish them as
contiguous non-overlapping partitions, if there is unbounded value
in the suffix of the bound tuple, because such a specification
would be considered to be overlapping with the current rule.
Adjust syntax to allow an explicit sign to be specified for unbounded
values. Also, replace the 'unbounded' keyword with 'infinity', because
that's what seems to make sense with a sign.
Adjust the logic in get_qual_for_range() to consider the possibility
that an infinity in a lower bound could really be +infinity and one
in an upper bound could be -infinity.
Add tests for the new syntax, tuple-routing, and partition constraint
checking for the new use cases allowed by the relaxed syntax.
---
doc/src/sgml/ref/create_table.sgml | 16 +++---
src/backend/catalog/partition.c | 74 +++++++++++++++++++---------
src/backend/parser/gram.y | 36 ++++++++------
src/backend/parser/parse_utilcmd.c | 42 +++++++++++-----
src/backend/utils/adt/ruleutils.c | 22 ++++++++-
src/include/nodes/parsenodes.h | 8 +--
src/include/parser/kwlist.h | 1 +
src/test/regress/expected/create_table.out | 79 +++++++++++++++++++++---------
src/test/regress/expected/inherit.out | 6 +--
src/test/regress/expected/insert.out | 61 ++++++++++++++++++++---
src/test/regress/sql/create_table.sql | 50 +++++++++++++------
src/test/regress/sql/inherit.sql | 6 +--
src/test/regress/sql/insert.sql | 34 ++++++++++---
13 files changed, 315 insertions(+), 120 deletions(-)
diff --git a/doc/src/sgml/ref/create_table.sgml
b/doc/src/sgml/ref/create_table.sgml
index b15c19d3d0..d146291d9a 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -87,8 +87,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ]
TABLE [ IF NOT EXI
<phrase>and <replaceable class="PARAMETER">partition_bound_spec</replaceable>
is:</phrase>
IN ( { <replaceable class="PARAMETER">numeric_literal</replaceable> |
<replaceable class="PARAMETER">string_literal</replaceable> | NULL } [, ...] ) |
-FROM ( { <replaceable class="PARAMETER">numeric_literal</replaceable> |
<replaceable class="PARAMETER">string_literal</replaceable> | UNBOUNDED } [,
...] )
- TO ( { <replaceable class="PARAMETER">numeric_literal</replaceable> |
<replaceable class="PARAMETER">string_literal</replaceable> | UNBOUNDED } [,
...] )
+FROM ( { <replaceable class="PARAMETER">numeric_literal</replaceable> |
<replaceable class="PARAMETER">string_literal</replaceable> | [ + | - ]
infinity } [, ...] )
+ TO ( { <replaceable class="PARAMETER">numeric_literal</replaceable> |
<replaceable class="PARAMETER">string_literal</replaceable> | [ + | - ]
infinity } [, ...] )
<phrase><replaceable class="PARAMETER">index_parameters</replaceable> in
<literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and
<literal>EXCLUDE</literal> constraints are:</phrase>
@@ -300,13 +300,11 @@ 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>.
+ In the absence of an explicit sign, infinity specified in the
+ <literal>FROM</literal> list signifies -infinity, whereas +infinity
+ if specified in the <literal>TO</literal>. Note that one cannot
+ specify a finite value after specifying (+/-) infinity in either
+ the <literal>FROM</literal> or the <literal>TO</literal> list.
</para>
<para>
diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c
index 457b2fef66..0e2b60e5a8 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],
@@ -1392,7 +1390,7 @@ get_qual_for_list(PartitionKey key, PartitionBoundSpec
*spec)
*
* Constructs an Expr for the key column (returned in *keyCol) and Consts
* for the lower and upper range limits (returned in *lower_val and
- * *upper_val). For UNBOUNDED limits, NULL is returned instead of a Const.
+ * *upper_val). For infinite limits, NULL is returned instead of a Const.
* All of these structures are freshly palloc'd.
*
* *partexprs_item points to the cell containing the next expression in
@@ -1464,17 +1462,21 @@ 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 cl happens to be -infinity, we need not emit any expression for it, so
+ * the AND sub-expression corresponding to the lower bound would be:
*
- * (b < bu) OR (b = bu), which is simplified to (b <= bu)
+ * (b > bl) OR (b = bl), which is simplified to (b >= bl)
*
- * 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
+ * But if cl were to be +infinity (because the range partition bound syntax
+ * allows that), no row with b == bl would qualify for this partition, because
+ * c >= +infinity will never be true. So, the expression would simply be:
+ * b > bl. Similarly, if cu happens to be -infinity, the AND sub-expression
+ * for the upper bound would be b < bu, because no row with b = bu would
+ * qualify for this partition.
*
- * 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.
+ * 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,
+ * 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.
@@ -1658,15 +1660,24 @@ get_qual_for_range(PartitionKey key, PartitionBoundSpec
*spec)
if (need_next_lower_arm && lower_val)
{
uint16 strategy;
+ DefElem *inf;
+ bool next_is_neg_inf = false;
+
+ if (ldatum_next && ldatum_next->infinite)
+ {
+ inf = castNode(DefElem,
ldatum_next->value);
+ if (strcmp(inf->defname,
"negative_infinity") == 0)
+ next_is_neg_inf = true;
+ }
/*
* For the non-last columns of this arm, use
the EQ operator.
- * For the last or the last finite-valued
column, use GE.
+ * For the last or the last finite-valued
column (provided the
+ * next infinite column is actually -infinity),
use GE.
*/
if (j - i < current_or_arm)
strategy = BTEqualStrategyNumber;
- else if ((ldatum_next && ldatum_next->infinite)
||
- j == key->partnatts - 1)
+ else if (next_is_neg_inf || j == key->partnatts
- 1)
strategy = BTGreaterEqualStrategyNumber;
else
strategy = BTGreaterStrategyNumber;
@@ -1681,14 +1692,24 @@ get_qual_for_range(PartitionKey key, PartitionBoundSpec
*spec)
if (need_next_upper_arm && upper_val)
{
uint16 strategy;
+ DefElem *inf;
+ bool next_is_pos_inf = false;
+
+ if (udatum_next && udatum_next->infinite)
+ {
+ inf = castNode(DefElem,
udatum_next->value);
+ if (strcmp(inf->defname,
"positive_infinity") == 0)
+ next_is_pos_inf = true;
+ }
/*
* For the non-last columns of this arm, use
the EQ operator.
- * For the last finite-valued column, use LE.
+ * For the last finite-valued column (provided
the next
+ * infinite column is actually +infinity), use
LE.
*/
if (j - i < current_or_arm)
strategy = BTEqualStrategyNumber;
- else if (udatum_next && udatum_next->infinite)
+ else if (next_is_pos_inf)
strategy = BTLessEqualStrategyNumber;
else
strategy = BTLessStrategyNumber;
@@ -2093,12 +2114,19 @@ make_one_range_bound(PartitionKey key, int index, List
*datums, bool lower)
foreach(lc, datums)
{
PartitionRangeDatum *datum = castNode(PartitionRangeDatum,
lfirst(lc));
+ DefElem *inf;
/* 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 (!datum->infinite)
+ bound->content[i] = RANGE_DATUM_FINITE;
+ else
+ {
+ inf = castNode(DefElem, datum->value);
+ if (strcmp(inf->defname, "negative_infinity") == 0)
+ bound->content[i] = RANGE_DATUM_NEG_INF;
+ else
+ bound->content[i] = RANGE_DATUM_POS_INF;
+ }
if (bound->content[i] == RANGE_DATUM_FINITE)
{
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0f3998ff89..111f35ce8b 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -634,8 +634,8 @@ static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
HANDLER HAVING HEADER_P HOLD HOUR_P
IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P
- INCLUDING INCREMENT INDEX INDEXES INHERIT INHERITS INITIALLY INLINE_P
- INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER
+ INCLUDING INCREMENT INDEX INDEXES INFINITY INHERIT INHERITS INITIALLY
+ INLINE_P INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER
INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION
JOIN
@@ -2681,6 +2681,23 @@ partbound_datum:
Sconst { $$ = makeStringConst($1, @1);
}
| NumericOnly { $$ = makeAConst($1, @1); }
| NULL_P { $$ = makeNullAConst(@1); }
+ | '-' INFINITY
+ {
+ $$ = (Node *)
makeDefElem("negative_infinity", NULL, @1);
+ }
+ | '+' INFINITY
+ {
+ $$ = (Node *)
makeDefElem("positive_infinity", NULL, @1);
+ }
+ /*
+ * If a user skips the sign, whether it's the negative
or the
+ * positive infinity is determined during the parse
analysis
+ * by seeing which of FROM and TO lists the bound is in.
+ */
+ | INFINITY
+ {
+ $$ = (Node *) makeDefElem("infinity",
NULL, @1);
+ }
;
partbound_datum_list:
@@ -2696,21 +2713,11 @@ range_datum_list:
;
PartitionRangeDatum:
- UNBOUNDED
- {
- PartitionRangeDatum *n =
makeNode(PartitionRangeDatum);
-
- n->infinite = true;
- n->value = NULL;
- n->location = @1;
-
- $$ = (Node *) n;
- }
- | partbound_datum
+ partbound_datum
{
PartitionRangeDatum *n =
makeNode(PartitionRangeDatum);
- n->infinite = false;
+ n->infinite = IsA($1, DefElem);
n->value = $1;
n->location = @1;
@@ -14714,6 +14721,7 @@ unreserved_keyword:
| INCREMENT
| INDEX
| INDEXES
+ | INFINITY
| INHERIT
| INHERITS
| INLINE_P
diff --git a/src/backend/parser/parse_utilcmd.c
b/src/backend/parser/parse_utilcmd.c
index ee5f3a3a52..2734677855 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -3365,7 +3365,7 @@ transformPartitionBound(ParseState *pstate, Relation
parent,
*cell2;
int i,
j;
- bool seen_unbounded;
+ bool seen_infinity;
if (spec->strategy != PARTITION_STRATEGY_RANGE)
ereport(ERROR,
@@ -3383,35 +3383,51 @@ transformPartitionBound(ParseState *pstate, Relation
parent,
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.
+ * Check that no finite value follows an infinity value in
either of
+ * lower and upper bound lists. While at it, convert the
"infinite"
+ * elements in the lowerdatums list into "negative_infinity"
and those
+ * in the upperdatums list into "positive_infinity".
*/
- seen_unbounded = false;
+ seen_infinity = false;
foreach(cell1, spec->lowerdatums)
{
PartitionRangeDatum *ldatum =
castNode(PartitionRangeDatum,
lfirst(cell1));
if (ldatum->infinite)
- seen_unbounded = true;
- else if (seen_unbounded)
+ {
+ DefElem *inf;
+
+ seen_infinity = true;
+ inf = castNode(DefElem, ldatum->value);
+ if (strcmp(inf->defname, "infinity") == 0)
+ inf->defname =
pstrdup("negative_infinity");
+ }
+ else if (seen_infinity)
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
- errmsg("cannot specify finite
value after UNBOUNDED"),
+ errmsg("cannot specify finite
value after infinity"),
parser_errposition(pstate,
exprLocation((Node *) ldatum))));
}
- seen_unbounded = false;
+ seen_infinity = false;
foreach(cell1, spec->upperdatums)
{
PartitionRangeDatum *rdatum =
castNode(PartitionRangeDatum,
lfirst(cell1));
if (rdatum->infinite)
- seen_unbounded = true;
- else if (seen_unbounded)
+ {
+ DefElem *inf;
+
+ seen_infinity = true;
+ inf = castNode(DefElem, rdatum->value);
+ if (strcmp(inf->defname, "infinity") == 0)
+ inf->defname =
pstrdup("positive_infinity");
+ }
+ else if (seen_infinity)
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
- errmsg("cannot specify finite
value after UNBOUNDED"),
+ errmsg("cannot specify finite
value after infinity"),
parser_errposition(pstate,
exprLocation((Node *) rdatum))));
}
@@ -3444,7 +3460,7 @@ transformPartitionBound(ParseState *pstate, Relation
parent,
coltype = get_partition_col_typid(key, i);
coltypmod = get_partition_col_typmod(key, i);
- if (ldatum->value)
+ if (ldatum->value && !IsA(ldatum->value, DefElem))
{
con = castNode(A_Const, ldatum->value);
value = transformPartitionBoundValue(pstate,
con,
@@ -3458,7 +3474,7 @@ transformPartitionBound(ParseState *pstate, Relation
parent,
ldatum->value = (Node *) value;
}
- if (rdatum->value)
+ if (rdatum->value && !IsA(rdatum->value, DefElem))
{
con = castNode(A_Const, rdatum->value);
value = transformPartitionBoundValue(pstate,
con,
diff --git a/src/backend/utils/adt/ruleutils.c
b/src/backend/utils/adt/ruleutils.c
index 18d9e27d1e..e093e503b3 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -8681,7 +8681,16 @@ get_rule_expr(Node *node, deparse_context *context,
appendStringInfoString(buf, sep);
if (datum->infinite)
-
appendStringInfoString(buf, "UNBOUNDED");
+ {
+ DefElem
*inf;
+
+ inf =
castNode(DefElem, datum->value);
+ if
(strcmp(inf->defname,
+
"negative_infinity") == 0)
+
appendStringInfoString(buf, "-infinity");
+ else
+
appendStringInfoString(buf, "+infinity");
+ }
else
{
Const *val
= castNode(Const, datum->value);
@@ -8699,7 +8708,16 @@ get_rule_expr(Node *node, deparse_context *context,
appendStringInfoString(buf, sep);
if (datum->infinite)
-
appendStringInfoString(buf, "UNBOUNDED");
+ {
+ DefElem
*inf;
+
+ inf =
castNode(DefElem, datum->value);
+ if
(strcmp(inf->defname,
+
"negative_infinity") == 0)
+
appendStringInfoString(buf, "-infinity");
+ else
+
appendStringInfoString(buf, "+infinity");
+ }
else
{
Const *val
= castNode(Const, datum->value);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 1d96169d34..5fea805717 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -811,14 +811,16 @@ typedef struct PartitionBoundSpec
/*
* PartitionRangeDatum - can be either a value or UNBOUNDED
*
- * "value" is an A_Const in raw grammar output, a Const after analysis
+ * "value" is an A_Const in raw grammar output, a Const after analysis, if
+ * it represents a finite value. For (-/+) infinity, it contains a suitably
+ * decorated DefElem instead, both before and after analysis.
*/
typedef struct PartitionRangeDatum
{
NodeTag type;
- bool infinite; /* true if UNBOUNDED */
- Node *value; /* null if UNBOUNDED */
+ bool infinite; /* true if (-/+) infinity */
+ Node *value;
int location; /* token location, or
-1 if unknown */
} PartitionRangeDatum;
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index f50e45e886..dff0af69bd 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -198,6 +198,7 @@ PG_KEYWORD("including", INCLUDING, UNRESERVED_KEYWORD)
PG_KEYWORD("increment", INCREMENT, UNRESERVED_KEYWORD)
PG_KEYWORD("index", INDEX, UNRESERVED_KEYWORD)
PG_KEYWORD("indexes", INDEXES, UNRESERVED_KEYWORD)
+PG_KEYWORD("infinity", INFINITY, UNRESERVED_KEYWORD)
PG_KEYWORD("inherit", INHERIT, UNRESERVED_KEYWORD)
PG_KEYWORD("inherits", INHERITS, UNRESERVED_KEYWORD)
PG_KEYWORD("initially", INITIALLY, RESERVED_KEYWORD)
diff --git a/src/test/regress/expected/create_table.out
b/src/test/regress/expected/create_table.out
index b6f794e1c2..ab5665b0c8 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -512,13 +512,13 @@ ERROR: FROM must specify exactly one value per
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
-- 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 range_parted FOR VALUES FROM (null) TO
(infinity);
ERROR: cannot specify NULL in range bound
--- cannot specify finite values after UNBOUNDED has been specified
+-- cannot specify finite values after infinity 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...
+CREATE TABLE fail_part PARTITION OF range_parted_multicol FOR VALUES FROM (1,
infinity, 1) TO (infinity, 1, 1);
+ERROR: cannot specify finite value after infinity
+LINE 1: ...nge_parted_multicol FOR VALUES FROM (1, infinity, 1) TO (inf...
^
DROP TABLE range_parted_multicol;
-- check if compatible with the specified parent
@@ -578,11 +578,11 @@ ERROR: cannot create range partition with empty range
-- note that the range '[1, 1)' has no elements
CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (1) TO (1);
ERROR: cannot create range partition with empty range
-CREATE TABLE part0 PARTITION OF range_parted2 FOR VALUES FROM (unbounded) TO
(1);
-CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (unbounded)
TO (2);
+CREATE TABLE part0 PARTITION OF range_parted2 FOR VALUES FROM (infinity) TO
(1);
+CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (infinity)
TO (2);
ERROR: partition "fail_part" would overlap partition "part0"
CREATE TABLE part1 PARTITION OF range_parted2 FOR VALUES FROM (1) TO (10);
-CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (9) TO
(unbounded);
+CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (9) TO
(infinity);
ERROR: partition "fail_part" would overlap partition "part1"
CREATE TABLE part2 PARTITION OF range_parted2 FOR VALUES FROM (20) TO (30);
CREATE TABLE part3 PARTITION OF range_parted2 FOR VALUES FROM (30) TO (40);
@@ -595,19 +595,54 @@ 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 (0, infinity)
TO (0, infinity);
+CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (0,
infinity) TO (0, 1);
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 (1, infinity)
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 (1,
infinity);
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);
+CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (1,
infinity) TO (1, infinity);
ERROR: partition "fail_part" would overlap partition "part10"
+-- check that range partition bound syntax allows -/+ to be specified for
+-- infinity values in arbitrary ways.
+create table multicol_range (a text, b numeric) partition by range (a, b);
+create table multicol_range_1 partition of multicol_range for values from
('a', infinity) to ('b', -infinity);
+create table multicol_range_2 partition of multicol_range for values from
('b', infinity) to ('c', -infinity);
+-- Accepts 'd', but not strings lexically greater than 'd'
+create table multicol_range_3 partition of multicol_range for values from
('c', infinity) to ('d', infinity);
+\d+ multicol_range_3
+ Table "public.multicol_range_3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target |
Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a | text | | | | extended | |
+ b | numeric | | | | main | |
+Partition of: multicol_range FOR VALUES FROM ('c', -infinity) TO ('d',
+infinity)
+Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a >=
'c'::text) AND (a <= 'd'::text))
+
+-- Does not accept 'd'; only strings lexically greater than 'd'
+create table multicol_range_4 partition of multicol_range for values from
('d', +infinity) to (infinity, infinity);
+\d+ multicol_range_4
+ Table "public.multicol_range_4"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target |
Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a | text | | | | extended | |
+ b | numeric | | | | main | |
+Partition of: multicol_range FOR VALUES FROM ('d', +infinity) TO (+infinity,
+infinity)
+Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a > 'd'::text))
+
+-- Some combinations would make the resulting range effectivly empty, which
+-- are promptly rejected
+create table multicol_range_err partition of multicol_range for values from
('e', +infinity) to (-infinity, infinity);
+ERROR: cannot create range partition with empty range
+create table multicol_range_err partition of multicol_range for values from
('e', infinity) to ('e', -infinity);
+ERROR: cannot create range partition with empty range
+create table multicol_range_err partition of multicol_range for values from
(+infinity, infinity) to (-infinity, infinity);
+ERROR: cannot create range partition with empty range
-- check schema propagation from parent
CREATE TABLE parted (
a text,
@@ -708,7 +743,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
(infinity, infinity, infinity) TO (infinity, infinity, infinity);
\d+ unbounded_range_part
Table "public.unbounded_range_part"
Column | Type | Collation | Nullable | Default | Storage | Stats target |
Description
@@ -716,11 +751,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 (-infinity, -infinity, -infinity)
TO (+infinity, +infinity, +infinity)
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
(infinity, infinity, infinity) TO (1, infinity, infinity);
\d+ range_parted4_1
Table "public.range_parted4_1"
Column | Type | Collation | Nullable | Default | Storage | Stats target |
Description
@@ -728,10 +763,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 of: range_parted4 FOR VALUES FROM (-infinity, -infinity, -infinity)
TO (1, +infinity, +infinity)
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, infinity);
\d+ range_parted4_2
Table "public.range_parted4_2"
Column | Type | Collation | Nullable | Default | Storage | Stats target |
Description
@@ -739,10 +774,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 of: range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, +infinity)
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, 8,
infinity) TO (9, infinity, infinity);
\d+ range_parted4_3
Table "public.range_parted4_3"
Column | Type | Collation | Nullable | Default | Storage | Stats target |
Description
@@ -750,12 +785,12 @@ 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 of: range_parted4 FOR VALUES FROM (6, 8, -infinity) TO (9,
+infinity, +infinity)
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))
DROP TABLE range_parted4;
-- cleanup
-DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2,
range_parted3;
+DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2,
range_parted3, multicol_range;
-- comments on partitioned tables columns
CREATE TABLE parted_col_comment (a int, b text) PARTITION BY LIST (a);
COMMENT ON TABLE parted_col_comment IS 'Am partitioned table';
diff --git a/src/test/regress/expected/inherit.out
b/src/test/regress/expected/inherit.out
index 35d182d599..924a24f97b 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1718,7 +1718,7 @@ create table part_10_20_cd partition of part_10_20 for
values in ('cd');
create table part_21_30 partition of range_list_parted for values from (21) to
(30) partition by list (b);
create table part_21_30_ab partition of part_21_30 for values in ('ab');
create table part_21_30_cd partition of part_21_30 for values in ('cd');
-create table part_40_inf partition of range_list_parted for values from (40)
to (unbounded) partition by list (b);
+create table part_40_inf partition of range_list_parted for values from (40)
to (+infinity) partition by list (b);
create table part_40_inf_ab partition of part_40_inf for values in ('ab');
create table part_40_inf_cd partition of part_40_inf for values in ('cd');
create table part_40_inf_null partition of part_40_inf for values in (null);
@@ -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 (-infinity,
-infinity, -infinity) 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
(+infinity, +infinity, +infinity);
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..6047c5b0a6 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -288,7 +288,7 @@ select tableoid::regclass, * from list_parted;
-- some more tests to exercise tuple-routing with multi-level partitioning
create table part_gg partition of list_parted for values in ('gg') partition
by range (b);
-create table part_gg1 partition of part_gg for values from (unbounded) to (1);
+create table part_gg1 partition of part_gg for values from (infinity) to (1);
create table part_gg2 partition of part_gg for values from (1) to (10)
partition by range (b);
create table part_gg2_1 partition of part_gg2 for values from (1) to (5);
create table part_gg2_2 partition of part_gg2 for values from (5) to (10);
@@ -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 (infinity,
infinity, infinity) to (1, infinity, infinity);
+create table mcrparted1 partition of mcrparted for values from (2, 1,
infinity) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 6,
infinity) to (10, infinity, infinity);
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, infinity,
infinity) to (30, 20, infinity);
+create table mcrparted5 partition of mcrparted for values from (30, 21, 20) to
(infinity, infinity, infinity);
-- routed to mcrparted0
insert into mcrparted values (0, 1, 1);
insert into mcrparted0 values (0, 1, 1);
@@ -508,3 +508,52 @@ ERROR: new row for relation "brtrigpartcon1" violates
partition constraint
DETAIL: Failing row contains (2, hi there).
drop table brtrigpartcon;
drop function brtrigpartcon1trigf();
+-- check that tuple-routing works and partition constraint are enforced
+-- properly when infinity values are specified with arbitrary signs in
+-- FROM and TO of range partition bounds.
+create table blogs (a text, b numeric) partition by range (a, b);
+create table blogs_a partition of blogs for values from ('a', infinity) to
('b', -infinity);
+create table blogs_b partition of blogs for values from ('b', infinity) to
('c', -infinity);
+-- Note that this one admits just the letter 'd'
+create table blogs_c_and_d partition of blogs for values from ('c', infinity)
to ('d', infinity);
+-- This one allows strings lexically greater than letter 'd'
+create table blogs_d_to_z partition of blogs for values from ('d', +infinity)
to (infinity, infinity);
+\d+ blogs
+ Table "public.blogs"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target |
Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a | text | | | | extended | |
+ b | numeric | | | | main | |
+Partition key: RANGE (a, b)
+Partitions: blogs_a FOR VALUES FROM ('a', -infinity) TO ('b', -infinity),
+ blogs_b FOR VALUES FROM ('b', -infinity) TO ('c', -infinity),
+ blogs_c_and_d FOR VALUES FROM ('c', -infinity) TO ('d', +infinity),
+ blogs_d_to_z FOR VALUES FROM ('d', +infinity) TO (+infinity,
+infinity)
+
+insert into blogs values ('and there it was', 0), ('because thats why', -1),
('come on', 12345), ('d', 132), ('drag', 908.0), ('zappa the legend', 1);
+select tableoid::regclass::text, * from blogs order by 1;
+ tableoid | a | b
+---------------+-------------------+-------
+ blogs_a | and there it was | 0
+ blogs_b | because thats why | -1
+ blogs_c_and_d | come on | 12345
+ blogs_c_and_d | d | 132
+ blogs_d_to_z | drag | 908.0
+ blogs_d_to_z | zappa the legend | 1
+(6 rows)
+
+-- indmissible
+insert into blogs_a values ('b', 1);
+ERROR: new row for relation "blogs_a" violates partition constraint
+DETAIL: Failing row contains (b, 1).
+insert into blogs_b values ('c', 1);
+ERROR: new row for relation "blogs_b" violates partition constraint
+DETAIL: Failing row contains (c, 1).
+insert into blogs_c_and_d values ('dr', 1);
+ERROR: new row for relation "blogs_c_and_d" violates partition constraint
+DETAIL: Failing row contains (dr, 1).
+insert into blogs_d_to_z values ('d', 1);
+ERROR: new row for relation "blogs_d_to_z" violates partition constraint
+DETAIL: Failing row contains (d, 1).
+-- cleanup
+drop table blogs;
diff --git a/src/test/regress/sql/create_table.sql
b/src/test/regress/sql/create_table.sql
index cb7aa5bbc6..f0bdc7116c 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -483,11 +483,11 @@ 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);
-- 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 range_parted FOR VALUES FROM (null) TO
(infinity);
--- cannot specify finite values after UNBOUNDED has been specified
+-- cannot specify finite values after infinity 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);
+CREATE TABLE fail_part PARTITION OF range_parted_multicol FOR VALUES FROM (1,
infinity, 1) TO (infinity, 1, 1);
DROP TABLE range_parted_multicol;
-- check if compatible with the specified parent
@@ -542,10 +542,10 @@ CREATE TABLE fail_part PARTITION OF range_parted2 FOR
VALUES FROM (1) TO (0);
-- note that the range '[1, 1)' has no elements
CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (1) TO (1);
-CREATE TABLE part0 PARTITION OF range_parted2 FOR VALUES FROM (unbounded) TO
(1);
-CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (unbounded)
TO (2);
+CREATE TABLE part0 PARTITION OF range_parted2 FOR VALUES FROM (infinity) TO
(1);
+CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (infinity)
TO (2);
CREATE TABLE part1 PARTITION OF range_parted2 FOR VALUES FROM (1) TO (10);
-CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (9) TO
(unbounded);
+CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (9) TO
(infinity);
CREATE TABLE part2 PARTITION OF range_parted2 FOR VALUES FROM (20) TO (30);
CREATE TABLE part3 PARTITION OF range_parted2 FOR VALUES FROM (30) TO (40);
CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (10) TO (30);
@@ -557,18 +557,36 @@ 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 (0, infinity)
TO (0, infinity);
+CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (0,
infinity) TO (0, 1);
-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 (1, infinity)
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 (1,
infinity);
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);
+CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (1,
infinity) TO (1, infinity);
+
+-- check that range partition bound syntax allows -/+ to be specified for
+-- infinity values in arbitrary ways.
+create table multicol_range (a text, b numeric) partition by range (a, b);
+create table multicol_range_1 partition of multicol_range for values from
('a', infinity) to ('b', -infinity);
+create table multicol_range_2 partition of multicol_range for values from
('b', infinity) to ('c', -infinity);
+-- Accepts 'd', but not strings lexically greater than 'd'
+create table multicol_range_3 partition of multicol_range for values from
('c', infinity) to ('d', infinity);
+\d+ multicol_range_3
+-- Does not accept 'd'; only strings lexically greater than 'd'
+create table multicol_range_4 partition of multicol_range for values from
('d', +infinity) to (infinity, infinity);
+\d+ multicol_range_4
+
+-- Some combinations would make the resulting range effectivly empty, which
+-- are promptly rejected
+create table multicol_range_err partition of multicol_range for values from
('e', +infinity) to (-infinity, infinity);
+create table multicol_range_err partition of multicol_range for values from
('e', infinity) to ('e', -infinity);
+create table multicol_range_err partition of multicol_range for values from
(+infinity, infinity) to (-infinity, infinity);
-- check schema propagation from parent
@@ -626,19 +644,19 @@ 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
(infinity, infinity, infinity) TO (infinity, infinity, infinity);
\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
(infinity, infinity, infinity) TO (1, infinity, infinity);
\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, infinity);
\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, 8,
infinity) TO (9, infinity, infinity);
\d+ range_parted4_3
DROP TABLE range_parted4;
-- cleanup
-DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2,
range_parted3;
+DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2,
range_parted3, multicol_range;
-- comments on partitioned tables columns
CREATE TABLE parted_col_comment (a int, b text) PARTITION BY LIST (a);
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 70fe971d51..663ecc8190 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -623,7 +623,7 @@ create table part_10_20_cd partition of part_10_20 for
values in ('cd');
create table part_21_30 partition of range_list_parted for values from (21) to
(30) partition by list (b);
create table part_21_30_ab partition of part_21_30 for values in ('ab');
create table part_21_30_cd partition of part_21_30 for values in ('cd');
-create table part_40_inf partition of range_list_parted for values from (40)
to (unbounded) partition by list (b);
+create table part_40_inf partition of range_list_parted for values from (40)
to (+infinity) partition by list (b);
create table part_40_inf_ab partition of part_40_inf for values in ('ab');
create table part_40_inf_cd partition of part_40_inf for values in ('cd');
create table part_40_inf_null partition of part_40_inf for values in (null);
@@ -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 (-infinity,
-infinity, -infinity) 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
(+infinity, +infinity, +infinity);
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..dd3baab93a 100644
--- a/src/test/regress/sql/insert.sql
+++ b/src/test/regress/sql/insert.sql
@@ -169,7 +169,7 @@ select tableoid::regclass, * from list_parted;
-- some more tests to exercise tuple-routing with multi-level partitioning
create table part_gg partition of list_parted for values in ('gg') partition
by range (b);
-create table part_gg1 partition of part_gg for values from (unbounded) to (1);
+create table part_gg1 partition of part_gg for values from (infinity) to (1);
create table part_gg2 partition of part_gg for values from (1) to (10)
partition by range (b);
create table part_gg2_1 partition of part_gg2 for values from (1) to (5);
create table part_gg2_2 partition of part_gg2 for values from (5) to (10);
@@ -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 (infinity,
infinity, infinity) to (1, infinity, infinity);
+create table mcrparted1 partition of mcrparted for values from (2, 1,
infinity) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 6,
infinity) to (10, infinity, infinity);
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, infinity,
infinity) to (30, 20, infinity);
+create table mcrparted5 partition of mcrparted for values from (30, 21, 20) to
(infinity, infinity, infinity);
-- routed to mcrparted0
insert into mcrparted values (0, 1, 1);
@@ -342,3 +342,25 @@ insert into brtrigpartcon values (1, 'hi there');
insert into brtrigpartcon1 values (1, 'hi there');
drop table brtrigpartcon;
drop function brtrigpartcon1trigf();
+
+-- check that tuple-routing works and partition constraint are enforced
+-- properly when infinity values are specified with arbitrary signs in
+-- FROM and TO of range partition bounds.
+create table blogs (a text, b numeric) partition by range (a, b);
+create table blogs_a partition of blogs for values from ('a', infinity) to
('b', -infinity);
+create table blogs_b partition of blogs for values from ('b', infinity) to
('c', -infinity);
+-- Note that this one admits just the letter 'd'
+create table blogs_c_and_d partition of blogs for values from ('c', infinity)
to ('d', infinity);
+-- This one allows strings lexically greater than letter 'd'
+create table blogs_d_to_z partition of blogs for values from ('d', +infinity)
to (infinity, infinity);
+\d+ blogs
+insert into blogs values ('and there it was', 0), ('because thats why', -1),
('come on', 12345), ('d', 132), ('drag', 908.0), ('zappa the legend', 1);
+select tableoid::regclass::text, * from blogs order by 1;
+-- indmissible
+insert into blogs_a values ('b', 1);
+insert into blogs_b values ('c', 1);
+insert into blogs_c_and_d values ('dr', 1);
+insert into blogs_d_to_z values ('d', 1);
+
+-- cleanup
+drop table blogs;
--
2.11.0
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
