On 2017/07/07 4:55, Dean Rasheed wrote:
> On 5 July 2017 at 18:07, Dean Rasheed <dean.a.rash...@gmail.com> wrote:
>> So if we were to go for maximum flexibility and compatibility with
>> Oracle, then perhaps what we would do is more like the original idea
>> of UNBOUNDED ABOVE/BELOW, except call them MINVALUE and MAXVALUE,
>> which conveniently are already unreserved keywords, as well as being
>> much shorter. Plus, we would also relax the constraint about having
>> finite values after MINVALUE/MAXVALUE.
>>
> So I know that I have flip-flopped a few times on this now, but I'm
> now starting to think that this approach, replacing UNBOUNDED with
> MINVALUE and MAXVALUE is the best way to go, along with permitting
> finite values after MINVALUE/MAXVALUE.
Sure.
> This gives the greatest flexibility, it's not too verbose, and it
> makes it easy to define contiguous sets of partitions just by making
> the lower bound of one match the upper bound of another.
>
> With this approach, any partition bounds that Oracle allows are also
> valid in PostgreSQL, not that I would normally give too much weight to
> that, but it is I think quite a nice syntax. Of course, we also
> support things that Oracle doesn't allow, such as MINVALUE and gaps
> between partitions.
Agreed. MINVALUE/MAXVALUE seems like a good way forward.
> Parts of the patch are similar to your UNBOUNDED ABOVE/BELOW patch,
> but there are a number of differences -- most notably, I replaced the
> "infinite" boolean flag on PartitionRangeDatum with a 3-value enum and
> did away with all the DefElem nodes and the associated special string
> constants being copied and compared.
That's better.
> However, this is also an incompatible syntax change, and any attempt
> to support both the old and new syntaxes is likely to be messy, so we
> really need to get consensus on whether this is the right thing to do,
> and whether it *can* be done now for PG10.
+1 to releasing this syntax in PG 10.
The patch looks generally good, although I found and fixed some minor
issues (typos and such). Please find attached the updated patch.
Thanks,
Amit
diff --git a/doc/src/sgml/ref/create_table.sgml
b/doc/src/sgml/ref/create_table.sgml
index b15c19d3d0..06bea2a18a 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> | MINVALUE |
MAXVALUE } [, ...] )
+ TO ( { <replaceable class="PARAMETER">numeric_literal</replaceable> |
<replaceable class="PARAMETER">string_literal</replaceable> | MINVALUE |
MAXVALUE } [, ...] )
<phrase><replaceable class="PARAMETER">index_parameters</replaceable> in
<literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and
<literal>EXCLUDE</literal> constraints are:</phrase>
@@ -269,10 +269,10 @@ FROM ( { <replaceable
class="PARAMETER">numeric_literal</replaceable> | <replace
<para>
Each of the values specified in
the <replaceable class="PARAMETER">partition_bound_spec</> is
- a literal, <literal>NULL</literal>, or <literal>UNBOUNDED</literal>.
- Each literal value must be either a numeric constant that is coercible
- to the corresponding partition key column's type, or a string literal
- that is valid input for that type.
+ a literal, <literal>NULL</literal>, <literal>MINVALUE</literal>, or
+ <literal>MAXVALUE</literal>. Each literal value must be either a
+ numeric constant that is coercible to the corresponding partition key
+ column's type, or a string literal that is valid input for that type.
</para>
<para>
@@ -300,13 +300,47 @@ 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>.
+ The special values <literal>MINVALUE</> and <literal>MAXVALUE</>
+ may be use when creating a range partition to indicate that there
+ is no lower or upper bound on the column's value. For example, a
+ partition defined using <literal>FROM (MINVALUE) TO (10)</> allows
+ any values less than 10, and a partition defined using
+ <literal>FROM (10) TO (MAXVALUE)</> allows any values greater than
+ or equal to 10.
+ </para>
+
+ <para>
+ When creating a range partition involving more than one column, it
+ can also make sense to use <literal>MAXVALUE</> as part of the lower
+ bound, and <literal>MINVALUE</> as part of the upper bound. For
+ example, a partition defined using
+ <literal>FROM (0, MAXVALUE) TO (10, MAXVALUE)</> allows any rows
+ where the first partitioned column is greater than 0 and less than
+ or equal to 10. Similarly, a partition defined using
+ <literal>FROM ('a', MINVALUE) TO ('b', MINVALUE)</>
+ allows only rows where the first partitioned column starts with "a".
+ </para>
+
+ <para>
+ Note that any values after <literal>MINVALUE</> or
+ <literal>MAXVALUE</> in a partition bound are ignored; so the bound
+ <literal>(10, MINVALUE, 0)</> is equivalent to
+ <literal>(10, MINVALUE, 10)</> and <literal>(10, MINVALUE, MINVALUE)</>
+ and <literal>(10, MINVALUE, MAXVALUE)</>.
+ </para>
+
+ <para>
+ Also note that some element types, such as <literal>timestamp</>,
+ have a notion of "infinity", which is just another value that can
+ be stored. This is different from <literal>MINVALUE</> and
+ <literal>MAXVALUE</>, which are not real values that can be stored,
+ but rather they are ways of saying the value is unbounded.
+ <literal>MAXVALUE</> can be thought of as being greater than any
+ other value, including "infinity" and <literal>MINVALUE</> as being
+ less than any other value, including "minus infinity". Thus the range
+ <literal>FROM ('infinity') TO (MAXVALUE)</> is not an empty range; it
+ allows precisely one value to be stored — the timestamp
+ "infinity".
</para>
<para>
diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c
index 43b8924261..e6388032c5 100644
--- a/src/backend/catalog/partition.c
+++ b/src/backend/catalog/partition.c
@@ -67,23 +67,14 @@
* is an upper bound.
*/
-/* Ternary value to represent what's contained in a range bound datum */
-typedef enum RangeDatumContent
-{
- RANGE_DATUM_FINITE = 0, /* actual datum stored elsewhere */
- RANGE_DATUM_NEG_INF, /* negative infinity */
- RANGE_DATUM_POS_INF /* positive infinity */
-} RangeDatumContent;
-
typedef struct PartitionBoundInfoData
{
char strategy; /* list or range bounds? */
int ndatums; /* Length of the datums
following array */
Datum **datums; /* Array of datum-tuples with
key->partnatts
* datums each
*/
- RangeDatumContent **content; /* what's contained in each range bound
- *
datum? (see the above enum); NULL for
- * list
partitioned tables */
+ PartitionRangeDatumKind **kind; /* The kind of each range bound datum;
+ * NULL
for list partitioned tables */
int *indexes; /* Partition indexes; one entry
per member of
* the datums
array (plus one if range
* partitioned
table) */
@@ -110,7 +101,7 @@ typedef struct PartitionRangeBound
{
int index;
Datum *datums; /* range bound datums */
- RangeDatumContent *content; /* what's contained in each datum? */
+ PartitionRangeDatumKind *kind; /* the kind of each datum */
bool lower; /* this is the lower (vs upper)
bound */
} PartitionRangeBound;
@@ -136,10 +127,10 @@ static List *generate_partition_qual(Relation rel);
static PartitionRangeBound *make_one_range_bound(PartitionKey key, int index,
List *datums, bool lower);
static int32 partition_rbound_cmp(PartitionKey key,
- Datum *datums1, RangeDatumContent
*content1, bool lower1,
- PartitionRangeBound *b2);
+ Datum *datums1,
PartitionRangeDatumKind *kind1,
+ bool lower1, PartitionRangeBound *b2);
static int32 partition_rbound_datum_cmp(PartitionKey key,
- Datum *rb_datums,
RangeDatumContent *rb_content,
+ Datum *rb_datums,
PartitionRangeDatumKind *rb_kind,
Datum *tuple_datums);
static int32 partition_bound_cmp(PartitionKey key,
@@ -366,29 +357,25 @@ RelationBuildPartitionDesc(Relation rel)
bool is_distinct = false;
int j;
- /* Is current bound is distinct from the
previous? */
+ /* Is current bound distinct from the previous?
*/
for (j = 0; j < key->partnatts; j++)
{
Datum cmpval;
- if (prev == NULL)
+ if (prev == NULL || cur->kind[j] !=
prev->kind[j])
{
is_distinct = true;
break;
}
/*
- * 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 the bounds are both MINVALUE or
MAXVALUE, stop now
+ * and treat them as equal, since any
values after this
+ * point must be ignored.
*/
- if (cur->content[j] !=
RANGE_DATUM_FINITE ||
- prev->content[j] !=
RANGE_DATUM_FINITE)
- {
- is_distinct = true;
+ if (cur->kind[j] !=
PARTITION_RANGE_DATUM_VALUE)
break;
- }
+
cmpval =
FunctionCall2Coll(&key->partsupfunc[j],
key->partcollation[j],
cur->datums[j],
@@ -513,8 +500,9 @@ RelationBuildPartitionDesc(Relation rel)
case PARTITION_STRATEGY_RANGE:
{
- boundinfo->content = (RangeDatumContent
**) palloc(ndatums *
-
sizeof(RangeDatumContent *));
+ boundinfo->kind =
(PartitionRangeDatumKind **)
+ palloc(ndatums *
+
sizeof(PartitionRangeDatumKind *));
boundinfo->indexes = (int *)
palloc((ndatums + 1) *
sizeof(int));
@@ -524,18 +512,17 @@ RelationBuildPartitionDesc(Relation rel)
boundinfo->datums[i] = (Datum
*) palloc(key->partnatts *
sizeof(Datum));
- boundinfo->content[i] =
(RangeDatumContent *)
+ boundinfo->kind[i] =
(PartitionRangeDatumKind *)
palloc(key->partnatts *
-
sizeof(RangeDatumContent));
+
sizeof(PartitionRangeDatumKind));
for (j = 0; j < key->partnatts;
j++)
{
- if
(rbounds[i]->content[j] == RANGE_DATUM_FINITE)
+ if (rbounds[i]->kind[j]
== PARTITION_RANGE_DATUM_VALUE)
boundinfo->datums[i][j] =
datumCopy(rbounds[i]->datums[j],
key->parttypbyval[j],
key->parttyplen[j]);
- /* Remember, we are
storing the tri-state value. */
-
boundinfo->content[i][j] = rbounds[i]->content[j];
+ boundinfo->kind[i][j] =
rbounds[i]->kind[j];
}
/*
@@ -617,17 +604,14 @@ partition_bounds_equal(PartitionKey key,
for (j = 0; j < key->partnatts; j++)
{
/* For range partitions, the bounds might not be
finite. */
- if (b1->content != NULL)
+ if (b1->kind != NULL)
{
- /*
- * A finite bound always differs from an
infinite bound, and
- * different kinds of infinities differ from
each other.
- */
- if (b1->content[i][j] != b2->content[i][j])
+ /* The different kinds of bound all differ from
each other */
+ if (b1->kind[i][j] != b2->kind[i][j])
return false;
/* Non-finite bounds are equal without further
examination. */
- if (b1->content[i][j] != RANGE_DATUM_FINITE)
+ if (b1->kind[i][j] !=
PARTITION_RANGE_DATUM_VALUE)
continue;
}
@@ -736,7 +720,7 @@ check_new_partition_bound(char *relname, Relation parent,
* First check if the resulting range would be
empty with
* specified lower and upper bounds
*/
- if (partition_rbound_cmp(key, lower->datums,
lower->content, true,
+ if (partition_rbound_cmp(key, lower->datums,
lower->kind, true,
upper) >= 0)
ereport(ERROR,
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
@@ -1399,8 +1383,8 @@ 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.
- * All of these structures are freshly palloc'd.
+ * *upper_val). For MINVALUE/MAXVALUE 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
* the key->partexprs list, or NULL. It may be advanced upon return.
@@ -1432,12 +1416,12 @@ get_range_key_properties(PartitionKey key, int keynum,
}
/* Get appropriate Const nodes for the bounds */
- if (!ldatum->infinite)
+ if (ldatum->kind == PARTITION_RANGE_DATUM_VALUE)
*lower_val = castNode(Const, copyObject(ldatum->value));
else
*lower_val = NULL;
- if (!udatum->infinite)
+ if (udatum->kind == PARTITION_RANGE_DATUM_VALUE)
*upper_val = castNode(Const, copyObject(udatum->value));
else
*upper_val = NULL;
@@ -1471,18 +1455,16 @@ 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 a bound datum is either MINVALUE or MAXVALUE, these expressions are
+ * simplified using the fact that any value is greater than MINVALUE and less
+ * than MAXVALUE. So, for example, if cu = MAXVALUE, c < cu is automatically
+ * true, and we need not emit any expression for it, and the last line becomes
*
* (b < bu) OR (b = bu), which is simplified to (b <= bu)
*
* In most common cases with only one partition column, say a, the following
* expression tree will be generated: a 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.
- *
* 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.
*/
@@ -1668,12 +1650,15 @@ get_qual_for_range(PartitionKey key, PartitionBoundSpec
*spec)
/*
* 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 column of this arm, use GT,
unless this is the
+ * last column of the whole bound check, or the
next bound
+ * datum is MINVALUE, in which case use GE.
*/
if (j - i < current_or_arm)
strategy = BTEqualStrategyNumber;
- else if ((ldatum_next && ldatum_next->infinite)
||
- j == key->partnatts - 1)
+ else if (j == key->partnatts - 1 ||
+ (ldatum_next &&
+ ldatum_next->kind ==
PARTITION_RANGE_DATUM_MINVALUE))
strategy = BTGreaterEqualStrategyNumber;
else
strategy = BTGreaterStrategyNumber;
@@ -1691,11 +1676,13 @@ 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.
+ * For the last column of this arm, use LT,
unless the next
+ * bound datum is MAXVALUE, in which case use
LE.
*/
if (j - i < current_or_arm)
strategy = BTEqualStrategyNumber;
- else if (udatum_next && udatum_next->infinite)
+ else if (udatum_next &&
+ udatum_next->kind ==
PARTITION_RANGE_DATUM_MAXVALUE)
strategy = BTLessEqualStrategyNumber;
else
strategy = BTLessStrategyNumber;
@@ -1716,11 +1703,15 @@ get_qual_for_range(PartitionKey key, PartitionBoundSpec
*spec)
if (j - i > current_or_arm)
{
/*
- * We need not emit the next arm if the new
column that will
- * be considered is unbounded.
+ * We must not emit any more arms if the new
column that will
+ * be considered is unbounded, or this one was.
*/
- need_next_lower_arm = ldatum_next &&
!ldatum_next->infinite;
- need_next_upper_arm = udatum_next &&
!udatum_next->infinite;
+ if (!lower_val || !ldatum_next ||
+ ldatum_next->kind !=
PARTITION_RANGE_DATUM_VALUE)
+ need_next_lower_arm = false;
+ if (!upper_val || !udatum_next ||
+ udatum_next->kind !=
PARTITION_RANGE_DATUM_VALUE)
+ need_next_upper_arm = false;
break;
}
}
@@ -2092,8 +2083,8 @@ make_one_range_bound(PartitionKey key, int index, List
*datums, bool lower)
bound = (PartitionRangeBound *) palloc0(sizeof(PartitionRangeBound));
bound->index = index;
bound->datums = (Datum *) palloc0(key->partnatts * sizeof(Datum));
- bound->content = (RangeDatumContent *) palloc0(key->partnatts *
-
sizeof(RangeDatumContent));
+ bound->kind = (PartitionRangeDatumKind *) palloc0(key->partnatts *
+
sizeof(PartitionRangeDatumKind));
bound->lower = lower;
i = 0;
@@ -2102,12 +2093,9 @@ 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);
+ bound->kind[i] = datum->kind;
- if (bound->content[i] == RANGE_DATUM_FINITE)
+ if (datum->kind == PARTITION_RANGE_DATUM_VALUE)
{
Const *val = castNode(Const, datum->value);
@@ -2130,7 +2118,7 @@ qsort_partition_rbound_cmp(const void *a, const void *b,
void *arg)
PartitionRangeBound *b2 = (*(PartitionRangeBound *const *) b);
PartitionKey key = (PartitionKey) arg;
- return partition_rbound_cmp(key, b1->datums, b1->content, b1->lower,
b2);
+ return partition_rbound_cmp(key, b1->datums, b1->kind, b1->lower, b2);
}
/*
@@ -2148,13 +2136,13 @@ qsort_partition_rbound_cmp(const void *a, const void
*b, void *arg)
*/
static int32
partition_rbound_cmp(PartitionKey key,
- Datum *datums1, RangeDatumContent
*content1, bool lower1,
- PartitionRangeBound *b2)
+ Datum *datums1,
PartitionRangeDatumKind *kind1,
+ bool lower1, PartitionRangeBound *b2)
{
int32 cmpval = 0; /* placate compiler */
int i;
Datum *datums2 = b2->datums;
- RangeDatumContent *content2 = b2->content;
+ PartitionRangeDatumKind *kind2 = b2->kind;
bool lower2 = b2->lower;
for (i = 0; i < key->partnatts; i++)
@@ -2162,28 +2150,16 @@ partition_rbound_cmp(PartitionKey key,
/*
* First, handle cases where the column is unbounded, which
should not
* invoke the comparison procedure, and should not consider any
later
- * columns.
+ * columns. Note that the PartitionRangeDatumKind enum elements
+ * compare the same way as the values they represent.
*/
- if (content1[i] != RANGE_DATUM_FINITE ||
- content2[i] != RANGE_DATUM_FINITE)
- {
- /*
- * If the bound values are equal, fall through and
compare whether
- * they are upper or lower bounds.
- */
- if (content1[i] == content2[i])
- break;
-
- /* Otherwise, one bound is definitely larger than the
other */
- if (content1[i] == RANGE_DATUM_NEG_INF)
- return -1;
- else if (content1[i] == RANGE_DATUM_POS_INF)
- return 1;
- else if (content2[i] == RANGE_DATUM_NEG_INF)
- return 1;
- else if (content2[i] == RANGE_DATUM_POS_INF)
- return -1;
- }
+ if (kind1[i] < kind2[i])
+ return -1;
+ else if (kind1[i] > kind2[i])
+ return 1;
+ else if (kind1[i] != PARTITION_RANGE_DATUM_VALUE)
+ /* No values after a MINVALUE or MAXVALUE should be
considered */
+ break;
cmpval = DatumGetInt32(FunctionCall2Coll(&key->partsupfunc[i],
key->partcollation[i],
@@ -2208,12 +2184,12 @@ partition_rbound_cmp(PartitionKey key,
/*
* partition_rbound_datum_cmp
*
- * Return whether range bound (specified in rb_datums, rb_content, and
- * rb_lower) <=, =, >= partition key of tuple (tuple_datums)
+ * Return whether range bound (specified in rb_datums, rb_kind, and rb_lower)
+ * is <, =, or > partition key of tuple (tuple_datums)
*/
static int32
partition_rbound_datum_cmp(PartitionKey key,
- Datum *rb_datums,
RangeDatumContent *rb_content,
+ Datum *rb_datums,
PartitionRangeDatumKind *rb_kind,
Datum *tuple_datums)
{
int i;
@@ -2221,8 +2197,10 @@ partition_rbound_datum_cmp(PartitionKey key,
for (i = 0; i < key->partnatts; i++)
{
- if (rb_content[i] != RANGE_DATUM_FINITE)
- return rb_content[i] == RANGE_DATUM_NEG_INF ? -1 : 1;
+ if (rb_kind[i] == PARTITION_RANGE_DATUM_MINVALUE)
+ return -1;
+ else if (rb_kind[i] == PARTITION_RANGE_DATUM_MAXVALUE)
+ return 1;
cmpval = DatumGetInt32(FunctionCall2Coll(&key->partsupfunc[i],
key->partcollation[i],
@@ -2238,7 +2216,7 @@ partition_rbound_datum_cmp(PartitionKey key,
/*
* partition_bound_cmp
*
- * Return whether the bound at offset in boundinfo is <=, =, >= the argument
+ * Return whether the bound at offset in boundinfo is <, =, or > the argument
* specified in *probe.
*/
static int32
@@ -2259,7 +2237,7 @@ partition_bound_cmp(PartitionKey key, PartitionBoundInfo
boundinfo,
case PARTITION_STRATEGY_RANGE:
{
- RangeDatumContent *content =
boundinfo->content[offset];
+ PartitionRangeDatumKind *kind =
boundinfo->kind[offset];
if (probe_is_bound)
{
@@ -2271,12 +2249,12 @@ partition_bound_cmp(PartitionKey key,
PartitionBoundInfo boundinfo,
bool lower =
boundinfo->indexes[offset] < 0;
cmpval = partition_rbound_cmp(key,
-
bound_datums, content, lower,
+
bound_datums, kind, lower,
(PartitionRangeBound *) probe);
}
else
cmpval = partition_rbound_datum_cmp(key,
-
bound_datums, content,
+
bound_datums, kind,
(Datum *) probe);
break;
}
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 67ac8145a0..45a04b0b27 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -4458,7 +4458,7 @@ _copyPartitionRangeDatum(const PartitionRangeDatum *from)
{
PartitionRangeDatum *newnode = makeNode(PartitionRangeDatum);
- COPY_SCALAR_FIELD(infinite);
+ COPY_SCALAR_FIELD(kind);
COPY_NODE_FIELD(value);
COPY_LOCATION_FIELD(location);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 91d64b7331..8d92c03633 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -2849,7 +2849,7 @@ _equalPartitionBoundSpec(const PartitionBoundSpec *a,
const PartitionBoundSpec *
static bool
_equalPartitionRangeDatum(const PartitionRangeDatum *a, const
PartitionRangeDatum *b)
{
- COMPARE_SCALAR_FIELD(infinite);
+ COMPARE_SCALAR_FIELD(kind);
COMPARE_NODE_FIELD(value);
COMPARE_LOCATION_FIELD(location);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index b0abe9ec10..124be75acf 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -3573,7 +3573,7 @@ _outPartitionRangeDatum(StringInfo str, const
PartitionRangeDatum *node)
{
WRITE_NODE_TYPE("PARTITIONRANGEDATUM");
- WRITE_BOOL_FIELD(infinite);
+ WRITE_ENUM_FIELD(kind, PartitionRangeDatumKind);
WRITE_NODE_FIELD(value);
WRITE_LOCATION_FIELD(location);
}
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 1380703cbc..76fee4161c 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -2390,7 +2390,7 @@ _readPartitionRangeDatum(void)
{
READ_LOCALS(PartitionRangeDatum);
- READ_BOOL_FIELD(infinite);
+ READ_ENUM_FIELD(kind, PartitionRangeDatumKind);
READ_NODE_FIELD(value);
READ_LOCATION_FIELD(location);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0f3998ff89..4b1ce09c44 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -2696,11 +2696,21 @@ range_datum_list:
;
PartitionRangeDatum:
- UNBOUNDED
+ MINVALUE
{
PartitionRangeDatum *n =
makeNode(PartitionRangeDatum);
- n->infinite = true;
+ n->kind =
PARTITION_RANGE_DATUM_MINVALUE;
+ n->value = NULL;
+ n->location = @1;
+
+ $$ = (Node *) n;
+ }
+ | MAXVALUE
+ {
+ PartitionRangeDatum *n =
makeNode(PartitionRangeDatum);
+
+ n->kind =
PARTITION_RANGE_DATUM_MAXVALUE;
n->value = NULL;
n->location = @1;
@@ -2710,7 +2720,7 @@ PartitionRangeDatum:
{
PartitionRangeDatum *n =
makeNode(PartitionRangeDatum);
- n->infinite = false;
+ n->kind = PARTITION_RANGE_DATUM_VALUE;
n->value = $1;
n->location = @1;
diff --git a/src/backend/parser/parse_utilcmd.c
b/src/backend/parser/parse_utilcmd.c
index ee5f3a3a52..9f37f1b920 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -3365,7 +3365,6 @@ transformPartitionBound(ParseState *pstate, Relation
parent,
*cell2;
int i,
j;
- bool seen_unbounded;
if (spec->strategy != PARTITION_STRATEGY_RANGE)
ereport(ERROR,
@@ -3382,39 +3381,6 @@ transformPartitionBound(ParseState *pstate, Relation
parent,
(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))));
- }
-
/* Transform all the constants */
i = j = 0;
result_spec->lowerdatums = result_spec->upperdatums = NIL;
diff --git a/src/backend/utils/adt/ruleutils.c
b/src/backend/utils/adt/ruleutils.c
index 18d9e27d1e..ba728b98b0 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -8680,8 +8680,10 @@ get_rule_expr(Node *node, deparse_context *context,
castNode(PartitionRangeDatum, lfirst(cell));
appendStringInfoString(buf, sep);
- if (datum->infinite)
-
appendStringInfoString(buf, "UNBOUNDED");
+ if (datum->kind ==
PARTITION_RANGE_DATUM_MINVALUE)
+
appendStringInfoString(buf, "MINVALUE");
+ else if (datum->kind ==
PARTITION_RANGE_DATUM_MAXVALUE)
+
appendStringInfoString(buf, "MAXVALUE");
else
{
Const *val
= castNode(Const, datum->value);
@@ -8698,8 +8700,10 @@ get_rule_expr(Node *node, deparse_context *context,
castNode(PartitionRangeDatum, lfirst(cell));
appendStringInfoString(buf, sep);
- if (datum->infinite)
-
appendStringInfoString(buf, "UNBOUNDED");
+ if (datum->kind ==
PARTITION_RANGE_DATUM_MINVALUE)
+
appendStringInfoString(buf, "MINVALUE");
+ else if (datum->kind ==
PARTITION_RANGE_DATUM_MAXVALUE)
+
appendStringInfoString(buf, "MAXVALUE");
else
{
Const *val
= castNode(Const, datum->value);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 1d96169d34..f731163d0a 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -809,16 +809,24 @@ typedef struct PartitionBoundSpec
} PartitionBoundSpec;
/*
- * PartitionRangeDatum - can be either a value or UNBOUNDED
+ * PartitionRangeDatum - one of the values in a range partition bound
*
- * "value" is an A_Const in raw grammar output, a Const after analysis
+ * This can be MINVALUE, MAXVALUE or a specific bounded value.
*/
+typedef enum PartitionRangeDatumKind
+{
+ PARTITION_RANGE_DATUM_MINVALUE = -1, /* less than any other value */
+ PARTITION_RANGE_DATUM_VALUE = 0, /* specific (bounded) value */
+ PARTITION_RANGE_DATUM_MAXVALUE = 1 /* greater than any other value
*/
+} PartitionRangeDatumKind;
+
typedef struct PartitionRangeDatum
{
NodeTag type;
- bool infinite; /* true if UNBOUNDED */
- Node *value; /* null if UNBOUNDED */
+ PartitionRangeDatumKind kind;
+ Node *value; /* Const (or A_Const in raw
tree), if kind is
+ *
PARTITION_RANGE_DATUM_VALUE, else NULL */
int location; /* token location, or
-1 if unknown */
} PartitionRangeDatum;
diff --git a/src/test/regress/expected/create_table.out
b/src/test/regress/expected/create_table.out
index b6f794e1c2..b301f22ca5 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -512,15 +512,8 @@ 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
(maxvalue);
ERROR: cannot specify NULL in range bound
--- cannot specify finite values after UNBOUNDED has been specified
-CREATE TABLE range_parted_multicol (a int, b int, c int) PARTITION BY RANGE
(a, b, c);
-CREATE TABLE fail_part PARTITION OF range_parted_multicol FOR VALUES FROM (1,
UNBOUNDED, 1) TO (UNBOUNDED, 1, 1);
-ERROR: cannot specify finite value after UNBOUNDED
-LINE 1: ...ge_parted_multicol FOR VALUES FROM (1, UNBOUNDED, 1) TO (UNB...
- ^
-DROP TABLE range_parted_multicol;
-- check if compatible with the specified parent
-- cannot create as partition of a non-partitioned table
CREATE TABLE unparted (
@@ -578,11 +571,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 (minvalue) TO
(1);
+CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (minvalue)
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
(maxvalue);
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,18 +588,18 @@ 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, minvalue)
TO (0, maxvalue);
+CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (0,
minvalue) 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, minvalue)
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,
maxvalue);
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,
minvalue) TO (1, maxvalue);
ERROR: partition "fail_part" would overlap partition "part10"
-- check schema propagation from parent
CREATE TABLE parted (
@@ -708,7 +701,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
(MINVALUE, 0, 0) TO (MAXVALUE, 0, 0);
\d+ unbounded_range_part
Table "public.unbounded_range_part"
Column | Type | Collation | Nullable | Default | Storage | Stats target |
Description
@@ -716,11 +709,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 (MINVALUE, 0, 0) TO (MAXVALUE, 0,
0)
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
(MINVALUE, 0, 0) TO (1, MAXVALUE, 0);
\d+ range_parted4_1
Table "public.range_parted4_1"
Column | Type | Collation | Nullable | Default | Storage | Stats target |
Description
@@ -728,10 +721,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 (MINVALUE, 0, 0) TO (1, MAXVALUE,
0)
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, MAXVALUE);
\d+ range_parted4_2
Table "public.range_parted4_2"
Column | Type | Collation | Nullable | Default | Storage | Stats target |
Description
@@ -739,10 +732,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, MAXVALUE)
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,
MINVALUE) TO (9, MAXVALUE, 0);
\d+ range_parted4_3
Table "public.range_parted4_3"
Column | Type | Collation | Nullable | Default | Storage | Stats target |
Description
@@ -750,7 +743,7 @@ 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, MINVALUE) TO (9, MAXVALUE,
0)
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;
diff --git a/src/test/regress/expected/inherit.out
b/src/test/regress/expected/inherit.out
index 35d182d599..1fa9650ec9 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 (maxvalue) 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 (minvalue, 0,
0) 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
(maxvalue, 0, 0);
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..a2a471c1f6 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 (minvalue) 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 (minvalue, 0,
0) to (1, maxvalue, 0);
+create table mcrparted1 partition of mcrparted for values from (2, 1,
minvalue) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 6,
minvalue) to (10, maxvalue, 0);
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, minvalue,
0) to (30, 20, maxvalue);
+create table mcrparted5 partition of mcrparted for values from (30, 21, 20) to
(maxvalue, 0, 0);
-- 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..1c0ce92763 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -483,12 +483,7 @@ 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);
-
--- 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;
+CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM (null) TO
(maxvalue);
-- check if compatible with the specified parent
@@ -542,10 +537,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 (minvalue) TO
(1);
+CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (minvalue)
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
(maxvalue);
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 +552,18 @@ 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, minvalue)
TO (0, maxvalue);
+CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (0,
minvalue) 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, minvalue)
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,
maxvalue);
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,
minvalue) TO (1, maxvalue);
-- check schema propagation from parent
@@ -626,14 +621,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
(MINVALUE, 0, 0) TO (MAXVALUE, 0, 0);
\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
(MINVALUE, 0, 0) TO (1, MAXVALUE, 0);
\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, MAXVALUE);
\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,
MINVALUE) TO (9, MAXVALUE, 0);
\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..c96580cd81 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 (maxvalue) 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 (minvalue, 0,
0) 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
(maxvalue, 0, 0);
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..28f0c24ed6 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 (minvalue) 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 (minvalue, 0,
0) to (1, maxvalue, 0);
+create table mcrparted1 partition of mcrparted for values from (2, 1,
minvalue) to (10, 5, 10);
+create table mcrparted2 partition of mcrparted for values from (10, 6,
minvalue) to (10, maxvalue, 0);
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, minvalue,
0) to (30, 20, maxvalue);
+create table mcrparted5 partition of mcrparted for values from (30, 21, 20) to
(maxvalue, 0, 0);
-- routed to mcrparted0
insert into mcrparted values (0, 1, 1);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
