Hi Dean,
On 2017/07/04 16:49, Dean Rasheed wrote:
> On 3 July 2017 at 10:32, Amit Langote <langote_amit...@lab.ntt.co.jp> wrote:
>> On 2017/07/03 17:36, Dean Rasheed wrote:
>>> The bigger question is do we want this for PG10? If so, time is
>>> getting tight. My feeling is that we do, because otherwise we'd be
>>> changing the syntax in PG11 of a feature only just released in PG10,
>>> and I think the current syntax is flawed, so it would be better not to
>>> have it in any public release. I'd feel better hearing from the
>>> original committer though.
>>
>> The way I have extended the syntax in the posted patch, ABOVE/BELOW (or
>> whatever we decide instead) are optional. UNBOUNDED without the
>> ABOVE/BELOW specifications implicitly means UNBOUNDED ABOVE if in FROM and
>> vice versa, which seems to me like sensible default behavior and what's
>> already present in PG 10.
>>
>> Do you think ABOVE/BELOW shouldn't really be optional?
>>
>
> Hmm, I'm not so sure about that.
>
> The more I think about this, the more I think that the current design
> is broken, and that introducing UNBOUNDED ABOVE/BELOW is just a
> sticking plaster to cover that up. Yes, it allows nicer multi-column
> ranges to be defined, as demonstrated upthread. But, it also allows
> some pretty counterintuitive things like making the lower bound
> exclusive and the upper bound inclusive.
Yes, I kind of got that impression from the example, but wasn't able to
reach the same conclusion as yours that it stems from the underlying
design issues; I thought we'd just have to document them as caveats, but
that doesn't really sound nice. Thanks for pointing that out.
> I think that's actually the real problem with the current design. If I
> have a single-column partition like
>
> (col) FROM (x) TO (y)
>
> it's pretty clear that's a simple range, inclusive at the lower end
> and exclusive at the upper end:
>
> (x) <= (col) < (y)
>
> If I now make that a 2-column partition, but leave the second column
> unbounded:
>
> (col1,col2) FROM (x,UNBOUNDED) TO (y,UNBOUNDED)
>
> my initial expectation would have been for that to mean the same
> thing, i.e.,
>
> (x) <= (col1) < (y)
>
> but that only happens if "UNBOUNDED" means negative infinity in both
> places. That then starts to give the sort of desirable properties
> you'd expect, like using the same expression for the lower bound of
> one partition as the upper bound of another makes the two partitions
> contiguous.
>
> But of course, that's not exactly a pretty design either, because then
> you'd be saying that UNBOUNDED means positive infinity if it's the
> upper bound of the first column, and negative infinity if it's the
> lower bound of the first column or either bound of any other column.
Initially, I didn't understand the part where you said FROM (x, UNBOUNDED)
TO (y, UNBOUNDED) would mean the same thing as (x) <= (col1) < (y),
because row comparison logic that underlying multi-column range partition
key comparisons appears to me to contradict the same. But, maybe it's
thinking about the implementation details like this that's clouding my
judgement about the correctness or the intuitiveness of the current design.
> Another aspect of the current design I don't like is that you have to
> keep repeating UNBOUNDED [ABOVE/BELOW], for each of the rest of the
> columns in the bound, and anything else is an error. That's a pretty
> verbose way of saying "the rest of the columns are unbounded".
>
> So the more I think about this, the more I think that a cleaner design
> would be as follows:
>
> 1). Don't allow UNBOUNDED, except in the first column, where it can
> keep it's current meaning.
>
> 2). Allow the partition bounds to have fewer columns than the
> partition definition, and have that mean the same as it would have
> meant if you were partitioning by that many columns. So, for
> example, if you were partitioning by (col1,col2), you'd be allowed
> to define a partition like so:
>
> FROM (x) TO (y)
>
> and it would mean
>
> x <= col1 < y
>
> Or you'd be able to define a partition like
>
> FROM (x1,x2) TO (y)
>
> which would mean
>
> (col1 > x1) OR (col1 = x1 AND col2 >= x2) AND col1 < y
>
> 3). Don't allow any value after UNBOUNDED (i.e., only specify
> UNBOUNDED once in a partition bound).
I assume we don't need the ability of specifying ABOVE/BELOW in this design.
In retrospect, that sounds like something that was implemented in the
earlier versions of the patch, whereby there was no ability to specify
UNBOUNDED on a per-column basis. So the syntax was:
FROM { (x [, ...]) | UNBOUNDED } TO { (y [, ...]) | UNBOUNDED }
But, it was pointed out to me [1] that that doesn't address the use case,
for example, where part1 goes up to (10, 10) and part2 goes from (10, 10)
up to (10, unbounded).
The new design will limit the usage of unbounded range partitions at the
tail ends.
> This design has a few neat properties:
>
> - Lower bounds are always inclusive and upper bounds are always
> exclusive.
>
> - If the expression for the lower bound of one partition is the same
> as the expression for the upper bound of another, the 2 partitions
> are contiguous, making it easy to define a covering set of
> partitions.
>
> - It's much easier to understand what a bound of "(x)" means than
> "(x,UNBOUNDED [ABOVE/BELOW])"
>
> - It's much less verbose, and there's no needless repetition.
They all sound good to me.
> Of course, it's pretty late in the day to be proposing this kind of
> redesign, but I fear that if we don't tackle it now, it will just be
> harder to deal with in the future.
>
> Actually, a quick, simple hacky implementation might be to just fill
> in any omitted values in a partition bound with negative infinity
> internally, and when printing a bound, omit any values after an
> infinite value. But really, I think we'd want to tidy up the
> implementation, and I think a number of things would actually get much
> simpler. For example, get_qual_for_range() could simply stop when it
> reached the end of the list of values for the bound, and it wouldn't
> need to worry about an unbounded value following a bounded one.
>
> Thoughts?
I cooked up a patch for the "hacky" implementation for now, just as you
described in the above paragraph. Will you be willing to give it a look?
I will also think about the non-hacky way of implementing this.
0001 is your patch to tidy up check_new_partition_bound() (must be
applied before 0002)
0002 is the patch to implement the range partition syntax redesign that
you outlined above
Thanks again.
Regards,
Amit
[1]
https://www.postgresql.org/message-id/CA%2BTgmoYJcUTcN7vVgg54GHtffH11JJWYZnfF4KiRxjV-iaACQg%40mail.gmail.com
From b8c832f8ca80291b953379c6195f5c90a5d05c91 Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Mon, 3 Jul 2017 10:52:45 +0900
Subject: [PATCH 1/2] Dean's patch to simply range partition overlap check
---
src/backend/catalog/partition.c | 90 ++++++++++++------------------
src/test/regress/expected/create_table.out | 2 +-
2 files changed, 38 insertions(+), 54 deletions(-)
diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c
index 7da2058f15..96760a0f05 100644
--- a/src/backend/catalog/partition.c
+++ b/src/backend/catalog/partition.c
@@ -745,78 +745,62 @@ check_new_partition_bound(char *relname, Relation parent,
if (partdesc->nparts > 0)
{
PartitionBoundInfo boundinfo =
partdesc->boundinfo;
- int off1,
- off2;
- bool equal = false;
+ int offset;
+ bool equal;
Assert(boundinfo && boundinfo->ndatums
> 0 &&
boundinfo->strategy ==
PARTITION_STRATEGY_RANGE);
/*
- * Firstly, find the greatest range
bound that is less
- * than or equal to the new lower bound.
+ * Test whether the new lower bound
(which is treated
+ * inclusively as part of the new
partition) lies inside an
+ * existing partition, or in a gap.
+ *
+ * If it's in a gap, the next index
value will be -1 (the
+ * lower bound of the next partition).
This is also true
+ * if there is no next partition, since
the index array is
+ * initialised with an extra -1 at the
end.
+ *
+ * Note that this also allows for the
possibility that the
+ * new lower bound equals an existing
upper bound.
*/
- off1 = partition_bound_bsearch(key,
boundinfo, lower, true,
-
&equal);
+ offset = 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 (!equal && boundinfo->indexes[off1 +
1] < 0)
+ if (boundinfo->indexes[offset + 1] < 0)
{
- 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.
+ * Check that the new partition
will fit in the gap.
+ * For it to fit, the new upper
bound must be less than
+ * or equal to the lower bound
of the next partition,
+ * if there is one.
*/
- if (equal || off1 != off2)
+ if (offset + 1 <
boundinfo->ndatums)
{
- overlap = true;
+ int32 cmpval;
- /*
- * 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.
- */
- if
(boundinfo->indexes[off2] < 0)
- with =
boundinfo->indexes[off2 + 1];
- else
- with =
boundinfo->indexes[off2];
+ cmpval =
partition_bound_cmp(key, boundinfo,
+
offset + 1, upper,
+
true);
+ if (cmpval < 0)
+ {
+ /*
+ * The new
partition overlaps with the existing
+ * partition
between offset + 1 and offset + 2.
+ */
+ overlap = true;
+ with =
boundinfo->indexes[offset + 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).
+ * The new partition overlaps
with the existing
+ * partition between offset and
offset + 1.
*/
overlap = true;
- with = boundinfo->indexes[off1
+ 1];
+ with =
boundinfo->indexes[offset + 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 6cf7bc6a620517b4d1e61f75ebf3675d5106c94d Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
