Thanks for the review and sorry it took me a while to reply.
On 2019/01/02 22:58, Peter Eisentraut wrote:
> On 26/11/2018 05:58, Amit Langote wrote:
>> On 2018/11/09 14:38, Amit Langote wrote:
>>> Rebased due to change in addRangeTableEntryForRelation's API.
>>
>> Rebased again due to changes in psql's describe output for partitioned
>> tables.
>
> Review:
>
> Is "partition bound" the right term? For list partitioning, it's not
> really a bound. Maybe it's OK.
Hmm, maybe "partition bound value"? Just want to stress that the
expression specifies "bounding" value of a partition.
> Keep the ordering of EXPR_KIND_PARTITION_BOUND in the various switch
> statements consistent.
OK, fixed.
> I don't see any treatment of non-immutable expressions. There is a test
> case with a non-immutable cast that was removed, but that's all. There
> was considerable discussion earlier in the thread on whether
> non-immutable expressions should be allowed. I'm not sure what the
> resolution was, but in any case there should be documentation and tests
> of the outcome.
The partition bound expression is evaluated only once, that is, when the
partition creation command is executed, and what gets stored in the
catalog is a Const expression that contains the value that was computed,
not the original non-immutable expression. So, we don't need to do
anything special in terms of code to handle users possibly specifying a
non-immutable expression as partition bound. Tom said something in that
thread which seems to support such a position:
https://www.postgresql.org/message-id/22534.1523374457%40sss.pgh.pa.us
Part of the test case that was removed is the error that used to be emitted:
CREATE TABLE moneyp (
a money
) PARTITION BY LIST (a);
CREATE TABLE moneyp_10 PARTITION OF moneyp FOR VALUES IN (10);
-ERROR: specified value cannot be cast to type money for column "a"
-LINE 1: ...EATE TABLE moneyp_10 PARTITION OF moneyp FOR VALUES IN (10);
- ^
-DETAIL: The cast requires a non-immutable conversion.
-HINT: Try putting the literal value in single quotes.
-CREATE TABLE moneyp_10 PARTITION OF moneyp FOR VALUES IN ('10');
which is no longer emitted, because the patched
transformPartitionBoundValue evaluates the expression, instead of emitting
error because the expression hasn't become a Const even after applying
eval_const_expressions().
Do we need to mention any aspect of how this now works in the user-facing
documentation?
> The collation handling might need another look. The following is
> allowed without any diagnostic:
>
> CREATE TABLE t2 (
> a text COLLATE "POSIX"
> ) PARTITION BY RANGE (a);
> CREATE TABLE t2a PARTITION OF t2 FOR VALUES FROM ('foo' COLLATE "C") TO
> ('xyz');
>
> I think the correct behavior would be that an explicit collation in the
> partition bound expression is an error.
I tend to agree with that. What happens is the partition bound expression
is assigned the collation of the parent's partition key:
+ partcollation = get_partition_col_collation(key, 0);
+ value = transformPartitionBoundValue(pstate, expr,
+ colname, coltype, coltypmod,
+ partcollation);
But that's essentially ignoring the collation specified by the user for
the partition bound value without providing any information about that to
the user. I've fixed that by explicitly checking if the collate clause in
the partition bound value expression contains the same collation as the
parent partition key collation and give an error otherwise.
Updated patch attached.
Thanks,
Amit
>From 4c14559e2d8412c9ec4990e078cdc017a78ca69f Mon Sep 17 00:00:00 2001
From: Kyotaro Horiguchi <horiguchi.kyot...@lab.ntt.co.jp>
Date: Fri, 6 Jul 2018 14:05:22 +0900
Subject: [PATCH v8] Allow generalized expression syntax for partition bounds
Authors: Kyotaro Horiguchi, Tom Lane, Amit Langote
---
doc/src/sgml/ref/alter_table.sgml | 6 +-
doc/src/sgml/ref/create_table.sgml | 16 +--
src/backend/commands/tablecmds.c | 9 ++
src/backend/optimizer/util/clauses.c | 4 +-
src/backend/parser/gram.y | 60 +-------
src/backend/parser/parse_agg.c | 10 ++
src/backend/parser/parse_expr.c | 5 +
src/backend/parser/parse_func.c | 3 +
src/backend/parser/parse_utilcmd.c | 211 +++++++++++++++++++----------
src/include/optimizer/clauses.h | 2 +
src/include/parser/parse_node.h | 1 +
src/include/utils/partcache.h | 6 +
src/test/regress/expected/create_table.out | 69 +++++++---
src/test/regress/sql/create_table.sql | 31 ++++-
14 files changed, 265 insertions(+), 168 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml
b/doc/src/sgml/ref/alter_table.sgml
index 0d1feaf828..bd037f2e39 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -86,9 +86,9 @@ ALTER TABLE [ IF EXISTS ] <replaceable
class="parameter">name</replaceable>
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable>
is:</phrase>
-IN ( { <replaceable class="parameter">numeric_literal</replaceable> |
<replaceable class="parameter">string_literal</replaceable> | TRUE | FALSE |
NULL } [, ...] ) |
-FROM ( { <replaceable class="parameter">numeric_literal</replaceable> |
<replaceable class="parameter">string_literal</replaceable> | TRUE | FALSE |
MINVALUE | MAXVALUE } [, ...] )
- TO ( { <replaceable class="parameter">numeric_literal</replaceable> |
<replaceable class="parameter">string_literal</replaceable> | TRUE | FALSE |
MINVALUE | MAXVALUE } [, ...] ) |
+IN ( <replaceable class="parameter">partition_bound_value</replaceable> [,
...] ) |
+FROM ( { <replaceable class="parameter">partition_bound_value</replaceable> |
MINVALUE | MAXVALUE } [, ...] )
+ TO ( { <replaceable class="parameter">partition_bound_value</replaceable> |
MINVALUE | MAXVALUE } [, ...] ) |
WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>,
REMAINDER <replaceable class="parameter">numeric_literal</replaceable> )
<phrase>and <replaceable class="parameter">column_constraint</replaceable>
is:</phrase>
diff --git a/doc/src/sgml/ref/create_table.sgml
b/doc/src/sgml/ref/create_table.sgml
index 857515ec8f..e8c0601602 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -87,9 +87,9 @@ class="parameter">referential_action</replaceable> ] [ ON
UPDATE <replaceable cl
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable>
is:</phrase>
-IN ( { <replaceable class="parameter">numeric_literal</replaceable> |
<replaceable class="parameter">string_literal</replaceable> | TRUE | FALSE |
NULL } [, ...] ) |
-FROM ( { <replaceable class="parameter">numeric_literal</replaceable> |
<replaceable class="parameter">string_literal</replaceable> | TRUE | FALSE |
MINVALUE | MAXVALUE } [, ...] )
- TO ( { <replaceable class="parameter">numeric_literal</replaceable> |
<replaceable class="parameter">string_literal</replaceable> | TRUE | FALSE |
MINVALUE | MAXVALUE } [, ...] ) |
+IN ( <replaceable class="parameter">partition_bound_value</replaceable> [,
...] ) |
+FROM ( { <replaceable class="parameter">partition_bound_value</replaceable> |
MINVALUE | MAXVALUE } [, ...] )
+ TO ( { <replaceable class="parameter">partition_bound_value</replaceable> |
MINVALUE | MAXVALUE } [, ...] ) |
WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>,
REMAINDER <replaceable class="parameter">numeric_literal</replaceable> )
<phrase><replaceable class="parameter">index_parameters</replaceable> in
<literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and
<literal>EXCLUDE</literal> constraints are:</phrase>
@@ -413,12 +413,10 @@ WITH ( MODULUS <replaceable
class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
- Each of the values specified in
- the <replaceable class="parameter">partition_bound_spec</replaceable> is
- 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.
+ <replaceable class="parameter">partition_bound_value</replaceable> is
+ any variable-free expression (subqueries, window functions, aggregate
+ functions, and set-returning functions are not allowed). Its data type
+ must match the data type of the corresponding partition key column.
</para>
<para>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index d2781cbf19..a1acdd14c2 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -827,6 +827,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
defaultPartOid;
Relation parent,
defaultRel = NULL;
+ RangeTblEntry *rte;
/* Already have strong enough lock on the parent */
parent = heap_open(parentId, NoLock);
@@ -869,6 +870,14 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
pstate = make_parsestate(NULL);
pstate->p_sourcetext = queryString;
+ /*
+ * Add an RTE containing this relation, so that transformExpr
called
+ * on partition bound expressions is able to report errors
using a
+ * proper context.
+ */
+ rte = addRangeTableEntryForRelation(pstate, rel,
AccessShareLock,
+
NULL, false, false);
+ addRTEtoQuery(pstate, rte, false, true, true);
bound = transformPartitionBound(pstate, parent,
stmt->partbound);
/*
diff --git a/src/backend/optimizer/util/clauses.c
b/src/backend/optimizer/util/clauses.c
index f0ef1029d1..94b8fa0c3d 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -150,8 +150,6 @@ static Node *substitute_actual_parameters(Node *expr, int
nargs, List *args,
static Node *substitute_actual_parameters_mutator(Node *node,
substitute_actual_parameters_context *context);
static void sql_inline_error_callback(void *arg);
-static Expr *evaluate_expr(Expr *expr, Oid result_type, int32 result_typmod,
- Oid result_collation);
static Query *substitute_actual_srf_parameters(Query *expr,
int nargs,
List *args);
static Node *substitute_actual_srf_parameters_mutator(Node *node,
@@ -5045,7 +5043,7 @@ sql_inline_error_callback(void *arg)
* We use the executor's routine ExecEvalExpr() to avoid duplication of
* code and ensure we get the same result as the executor would get.
*/
-static Expr *
+Expr *
evaluate_expr(Expr *expr, Oid result_type, int32 result_typmod,
Oid result_collation)
{
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c086235b25..497e29047e 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -581,8 +581,7 @@ static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
%type <partelem> part_elem
%type <list> part_params
%type <partboundspec> PartitionBoundSpec
-%type <node> partbound_datum PartitionRangeDatum
-%type <list> hash_partbound partbound_datum_list range_datum_list
+%type <list> hash_partbound
%type <defelt> hash_partbound_elem
/*
@@ -2731,7 +2730,7 @@ PartitionBoundSpec:
}
/* a LIST partition */
- | FOR VALUES IN_P '(' partbound_datum_list ')'
+ | FOR VALUES IN_P '(' expr_list ')'
{
PartitionBoundSpec *n =
makeNode(PartitionBoundSpec);
@@ -2744,7 +2743,7 @@ PartitionBoundSpec:
}
/* a RANGE partition */
- | FOR VALUES FROM '(' range_datum_list ')' TO '('
range_datum_list ')'
+ | FOR VALUES FROM '(' expr_list ')' TO '(' expr_list ')'
{
PartitionBoundSpec *n =
makeNode(PartitionBoundSpec);
@@ -2787,59 +2786,6 @@ hash_partbound:
}
;
-partbound_datum:
- Sconst { $$ = makeStringConst($1, @1);
}
- | NumericOnly { $$ = makeAConst($1, @1); }
- | TRUE_P { $$ =
makeStringConst(pstrdup("true"), @1); }
- | FALSE_P { $$ =
makeStringConst(pstrdup("false"), @1); }
- | NULL_P { $$ = makeNullAConst(@1); }
- ;
-
-partbound_datum_list:
- partbound_datum
{ $$ = list_make1($1); }
- | partbound_datum_list ',' partbound_datum
-
{ $$ = lappend($1, $3); }
- ;
-
-range_datum_list:
- PartitionRangeDatum
{ $$ = list_make1($1); }
- | range_datum_list ',' PartitionRangeDatum
-
{ $$ = lappend($1, $3); }
- ;
-
-PartitionRangeDatum:
- MINVALUE
- {
- PartitionRangeDatum *n =
makeNode(PartitionRangeDatum);
-
- 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;
-
- $$ = (Node *) n;
- }
- | partbound_datum
- {
- PartitionRangeDatum *n =
makeNode(PartitionRangeDatum);
-
- n->kind = PARTITION_RANGE_DATUM_VALUE;
- n->value = $1;
- n->location = @1;
-
- $$ = (Node *) n;
- }
- ;
-
/*****************************************************************************
*
* ALTER TYPE
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index bd6201e50a..153c8641e9 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -507,6 +507,13 @@ check_agglevels_and_constraints(ParseState *pstate, Node
*expr)
err = _("grouping operations are not allowed in
trigger WHEN conditions");
break;
+ case EXPR_KIND_PARTITION_BOUND:
+ if (isAgg)
+ err = _("aggregate functions are not allowed in
partition bound");
+ else
+ err = _("grouping operations are not allowed in
partition bound");
+
+ break;
case EXPR_KIND_PARTITION_EXPRESSION:
if (isAgg)
err = _("aggregate functions are not allowed in
partition key expressions");
@@ -896,6 +903,9 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc
*wfunc,
case EXPR_KIND_TRIGGER_WHEN:
err = _("window functions are not allowed in trigger
WHEN conditions");
break;
+ case EXPR_KIND_PARTITION_BOUND:
+ err = _("window functions are not allowed in partition
bound");
+ break;
case EXPR_KIND_PARTITION_EXPRESSION:
err = _("window functions are not allowed in partition
key expressions");
break;
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index bff237094a..397b945825 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -1843,6 +1843,9 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
case EXPR_KIND_TRIGGER_WHEN:
err = _("cannot use subquery in trigger WHEN
condition");
break;
+ case EXPR_KIND_PARTITION_BOUND:
+ err = _("cannot use subquery in partition bound");
+ break;
case EXPR_KIND_PARTITION_EXPRESSION:
err = _("cannot use subquery in partition key
expression");
break;
@@ -3471,6 +3474,8 @@ ParseExprKindName(ParseExprKind exprKind)
return "EXECUTE";
case EXPR_KIND_TRIGGER_WHEN:
return "WHEN";
+ case EXPR_KIND_PARTITION_BOUND:
+ return "partition bound";
case EXPR_KIND_PARTITION_EXPRESSION:
return "PARTITION BY";
case EXPR_KIND_CALL_ARGUMENT:
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index 4661fc4f62..e997bb1871 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -2364,6 +2364,9 @@ check_srf_call_placement(ParseState *pstate, Node
*last_srf, int location)
case EXPR_KIND_TRIGGER_WHEN:
err = _("set-returning functions are not allowed in
trigger WHEN conditions");
break;
+ case EXPR_KIND_PARTITION_BOUND:
+ err = _("set-returning functions are not allowed in
partition bound");
+ break;
case EXPR_KIND_PARTITION_EXPRESSION:
err = _("set-returning functions are not allowed in
partition key expressions");
break;
diff --git a/src/backend/parser/parse_utilcmd.c
b/src/backend/parser/parse_utilcmd.c
index bebee9d540..8bbbfa03b6 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -49,7 +49,9 @@
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "optimizer/clauses.h"
#include "optimizer/planner.h"
+#include "optimizer/var.h"
#include "parser/analyze.h"
#include "parser/parse_clause.h"
#include "parser/parse_coerce.h"
@@ -138,9 +140,12 @@ static void transformConstraintAttrs(CreateStmtContext
*cxt,
static void transformColumnType(CreateStmtContext *cxt, ColumnDef *column);
static void setSchemaName(char *context_schema, char **stmt_schema_name);
static void transformPartitionCmd(CreateStmtContext *cxt, PartitionCmd *cmd);
+static List *transformPartitionRangeBounds(ParseState *pstate, List *blist,
+ Relation parent);
static void validateInfiniteBounds(ParseState *pstate, List *blist);
-static Const *transformPartitionBoundValue(ParseState *pstate, A_Const *con,
- const char *colName,
Oid colType, int32 colTypmod);
+static Const *transformPartitionBoundValue(ParseState *pstate, Node *con,
+ const char *colName,
Oid colType, int32 colTypmod,
+ Oid partCollation);
/*
@@ -3624,6 +3629,7 @@ transformPartitionBound(ParseState *pstate, Relation
parent,
char *colname;
Oid coltype;
int32 coltypmod;
+ Oid partcollation;
if (spec->strategy != PARTITION_STRATEGY_LIST)
ereport(ERROR,
@@ -3643,17 +3649,19 @@ transformPartitionBound(ParseState *pstate, Relation
parent,
/* Need its type data too */
coltype = get_partition_col_typid(key, 0);
coltypmod = get_partition_col_typmod(key, 0);
+ partcollation = get_partition_col_collation(key, 0);
result_spec->listdatums = NIL;
foreach(cell, spec->listdatums)
{
- A_Const *con = castNode(A_Const, lfirst(cell));
+ Node *expr = (Node *) lfirst (cell);
Const *value;
ListCell *cell2;
bool duplicate;
- value = transformPartitionBoundValue(pstate, con,
-
colname, coltype, coltypmod);
+ value = transformPartitionBoundValue(pstate, expr,
+
colname, coltype, coltypmod,
+
partcollation);
/* Don't add to the result if the value is a duplicate
*/
duplicate = false;
@@ -3676,11 +3684,6 @@ transformPartitionBound(ParseState *pstate, Relation
parent,
}
else if (strategy == PARTITION_STRATEGY_RANGE)
{
- ListCell *cell1,
- *cell2;
- int i,
- j;
-
if (spec->strategy != PARTITION_STRATEGY_RANGE)
ereport(ERROR,
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
@@ -3697,23 +3700,78 @@ transformPartitionBound(ParseState *pstate, Relation
parent,
errmsg("TO must specify exactly one
value per partitioning column")));
/*
- * Once we see MINVALUE or MAXVALUE for one column, the
remaining
- * columns must be the same.
+ * Convert raw parser nodes into PartitionRangeDatum nodes and
perform
+ * any necessary validation.
*/
- validateInfiniteBounds(pstate, spec->lowerdatums);
- validateInfiniteBounds(pstate, spec->upperdatums);
+ result_spec->lowerdatums =
+ transformPartitionRangeBounds(pstate,
spec->lowerdatums,
+
parent);
+ result_spec->upperdatums =
+ transformPartitionRangeBounds(pstate,
spec->upperdatums,
+
parent);
+ }
+ else
+ elog(ERROR, "unexpected partition strategy: %d", (int)
strategy);
- /* Transform all the constants */
- i = j = 0;
- result_spec->lowerdatums = result_spec->upperdatums = NIL;
- forboth(cell1, spec->lowerdatums, cell2, spec->upperdatums)
+ return result_spec;
+}
+
+/*
+ * transformPartitionRangeBounds
+ * This converts the expressions for range partition bounds from
the raw
+ * grammar representation to PartitionRangeDatum structs
+ */
+static List *
+transformPartitionRangeBounds(ParseState *pstate, List *blist,
+ Relation parent)
+{
+ List *result = NIL;
+ PartitionKey key = RelationGetPartitionKey(parent);
+ List *partexprs = get_partition_exprs(key);
+ ListCell *lc;
+ int i,
+ j;
+
+ i = j = 0;
+ foreach(lc, blist)
+ {
+ Node *expr = lfirst(lc);
+ PartitionRangeDatum *prd = NULL;
+
+ /*
+ * Infinite range bounds -- "minvalue" and "maxvalue" -- get
passed
+ * in as ColumnRefs.
+ */
+ if (IsA(expr, ColumnRef))
+ {
+ ColumnRef *cref = (ColumnRef *) expr;
+ char *cname = NULL;
+
+ if (list_length(cref->fields) == 1 &&
+ IsA(linitial(cref->fields), String))
+ cname = strVal(linitial(cref->fields));
+
+ Assert(cname != NULL);
+ if (strcmp("minvalue", cname) == 0)
+ {
+ prd = makeNode(PartitionRangeDatum);
+ prd->kind = PARTITION_RANGE_DATUM_MINVALUE;
+ prd->value = NULL;
+ }
+ else if (strcmp("maxvalue", cname) == 0)
+ {
+ prd = makeNode(PartitionRangeDatum);
+ prd->kind = PARTITION_RANGE_DATUM_MAXVALUE;
+ prd->value = NULL;
+ }
+ }
+
+ if (prd == NULL)
{
- PartitionRangeDatum *ldatum = (PartitionRangeDatum *)
lfirst(cell1);
- PartitionRangeDatum *rdatum = (PartitionRangeDatum *)
lfirst(cell2);
char *colname;
Oid coltype;
int32 coltypmod;
- A_Const *con;
+ Oid partcollation;
Const *value;
/* Get the column's name in case we need to output an
error */
@@ -3728,50 +3786,38 @@ transformPartitionBound(ParseState *pstate, Relation
parent,
false, false);
++j;
}
+
/* Need its type data too */
coltype = get_partition_col_typid(key, i);
coltypmod = get_partition_col_typmod(key, i);
+ partcollation = get_partition_col_collation(key, i);
- if (ldatum->value)
- {
- con = castNode(A_Const, ldatum->value);
- value = transformPartitionBoundValue(pstate,
con,
-
colname,
-
coltype, coltypmod);
- if (value->constisnull)
- ereport(ERROR,
-
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
- errmsg("cannot specify
NULL in range bound")));
- ldatum = copyObject(ldatum); /* don't
scribble on input */
- ldatum->value = (Node *) value;
- }
-
- if (rdatum->value)
- {
- con = castNode(A_Const, rdatum->value);
- value = transformPartitionBoundValue(pstate,
con,
-
colname,
-
coltype, coltypmod);
- if (value->constisnull)
- ereport(ERROR,
-
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
- errmsg("cannot specify
NULL in range bound")));
- rdatum = copyObject(rdatum); /* don't
scribble on input */
- rdatum->value = (Node *) value;
- }
-
- result_spec->lowerdatums =
lappend(result_spec->lowerdatums,
-
ldatum);
- result_spec->upperdatums =
lappend(result_spec->upperdatums,
-
rdatum);
-
+ value = transformPartitionBoundValue(pstate, expr,
+
colname,
+
coltype, coltypmod,
+
partcollation);
+ if (value->constisnull)
+ ereport(ERROR,
+
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("cannot specify NULL in
range bound")));
+ prd = makeNode(PartitionRangeDatum);
+ prd->kind = PARTITION_RANGE_DATUM_VALUE;
+ prd->value = (Node *) value;
++i;
}
- }
- else
- elog(ERROR, "unexpected partition strategy: %d", (int)
strategy);
- return result_spec;
+ prd->location = exprLocation(expr);
+
+ result = lappend(result, prd);
+ }
+
+ /*
+ * Once we see MINVALUE or MAXVALUE for one column, the remaining
+ * columns must be the same.
+ */
+ validateInfiniteBounds(pstate, result);
+
+ return result;
}
/*
@@ -3820,13 +3866,31 @@ validateInfiniteBounds(ParseState *pstate, List *blist)
* Transform one constant in a partition bound spec
*/
static Const *
-transformPartitionBoundValue(ParseState *pstate, A_Const *con,
- const char *colName,
Oid colType, int32 colTypmod)
+transformPartitionBoundValue(ParseState *pstate, Node *val,
+ const char *colName,
Oid colType, int32 colTypmod,
+ Oid partCollation)
{
Node *value;
+ Oid exprCollOid;
- /* Make it into a Const */
- value = (Node *) make_const(pstate, &con->val, con->location);
+ /* Transform raw parsetree */
+ value = transformExpr(pstate, val, EXPR_KIND_PARTITION_BOUND);
+
+ /*
+ * Check that the input expression's collation is compatible with one
+ * specified for the parent's partition key (partcollation). Don't
+ * throw an error if it's the default collation which we'll replace with
+ * the parent's collation anyway.
+ */
+ exprCollOid = exprCollation(value);
+ if (OidIsValid(exprCollOid) &&
+ exprCollOid != DEFAULT_COLLATION_OID &&
+ exprCollOid != partCollation)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("collation of partition bound value for
column \"%s\" does not match partition key collation \"%s\"",
+ colName,
get_collation_name(partCollation)),
+ parser_errposition(pstate,
exprLocation(val))));
/* Coerce to correct type */
value = coerce_to_target_type(pstate,
@@ -3842,21 +3906,26 @@ transformPartitionBoundValue(ParseState *pstate,
A_Const *con,
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("specified value cannot be cast to type
%s for column \"%s\"",
format_type_be(colType),
colName),
- parser_errposition(pstate, con->location)));
+ parser_errposition(pstate,
exprLocation(val))));
/* Simplify the expression, in case we had a coercion */
if (!IsA(value, Const))
value = (Node *) expression_planner((Expr *) value);
- /* Fail if we don't have a constant (i.e., non-immutable coercion) */
- if (!IsA(value, Const))
+ /* Make sure the expression does not refer to any vars. */
+ if (contain_var_clause(value))
ereport(ERROR,
- (errcode(ERRCODE_DATATYPE_MISMATCH),
- errmsg("specified value cannot be cast to type
%s for column \"%s\"",
- format_type_be(colType),
colName),
- errdetail("The cast requires a non-immutable
conversion."),
- errhint("Try putting the literal value in
single quotes."),
- parser_errposition(pstate, con->location)));
+ (errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
+ errmsg("cannot use column references in
partition bound expression")));
+
+ /*
+ * If it is still not a Const, evaluate the expression assigning the
+ * partition key's collation to the resulting Const expression.
+ */
+ value = (Node *) evaluate_expr((Expr *) value, colType, colTypmod,
+
partCollation);
+ if (!IsA(value, Const))
+ elog(ERROR, "could not evaluate partition bound expression");
return (Const *) value;
}
diff --git a/src/include/optimizer/clauses.h b/src/include/optimizer/clauses.h
index 5c8580e478..1bf34c30f2 100644
--- a/src/include/optimizer/clauses.h
+++ b/src/include/optimizer/clauses.h
@@ -88,4 +88,6 @@ extern Query *inline_set_returning_function(PlannerInfo *root,
extern List *expand_function_arguments(List *args, Oid result_type,
HeapTuple func_tuple);
+extern Expr *evaluate_expr(Expr *expr, Oid result_type, int32 result_typmod,
+ Oid result_collation);
#endif /* CLAUSES_H */
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index f4e1cdd85b..22854b5e28 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -69,6 +69,7 @@ typedef enum ParseExprKind
EXPR_KIND_TRIGGER_WHEN, /* WHEN condition in CREATE TRIGGER */
EXPR_KIND_POLICY, /* USING or WITH CHECK expr in
policy */
EXPR_KIND_PARTITION_EXPRESSION, /* PARTITION BY expression */
+ EXPR_KIND_PARTITION_BOUND, /* partition bound value */
EXPR_KIND_CALL_ARGUMENT /* procedure argument in CALL */
} ParseExprKind;
diff --git a/src/include/utils/partcache.h b/src/include/utils/partcache.h
index d058a1d194..7c2f973f68 100644
--- a/src/include/utils/partcache.h
+++ b/src/include/utils/partcache.h
@@ -93,4 +93,10 @@ get_partition_col_typmod(PartitionKey key, int col)
return key->parttypmod[col];
}
+static inline Oid
+get_partition_col_collation(PartitionKey key, int col)
+{
+ return key->partcollation[col];
+}
+
#endif /* PARTCACHE_H */
diff --git a/src/test/regress/expected/create_table.out
b/src/test/regress/expected/create_table.out
index 7e52c27e3f..ff3489c6a1 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -460,19 +460,40 @@ DROP TABLE partitioned, partitioned2;
CREATE TABLE list_parted (
a int
) PARTITION BY LIST (a);
--- syntax allows only string literal, numeric literal and null to be
--- specified for a partition bound value
CREATE TABLE part_1 PARTITION OF list_parted FOR VALUES IN ('1');
CREATE TABLE part_2 PARTITION OF list_parted FOR VALUES IN (2);
+CREATE TABLE part_3 PARTITION OF list_parted FOR VALUES IN ((2+1));
CREATE TABLE part_null PARTITION OF list_parted FOR VALUES IN (null);
-CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES IN (int '1');
-ERROR: syntax error at or near "int"
-LINE 1: ... fail_part PARTITION OF list_parted FOR VALUES IN (int '1');
- ^
-CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES IN ('1'::int);
-ERROR: syntax error at or near "::"
-LINE 1: ...fail_part PARTITION OF list_parted FOR VALUES IN ('1'::int);
- ^
+\d+ list_parted
+ Partitioned table "public.list_parted"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target |
Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | | | plain | |
+Partition key: LIST (a)
+Partitions: part_1 FOR VALUES IN (1),
+ part_2 FOR VALUES IN (2),
+ part_3 FOR VALUES IN (3),
+ part_null FOR VALUES IN (NULL)
+
+-- forbidden expressions for partition bound
+CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN
(somename);
+ERROR: column "somename" does not exist
+LINE 1: ...expr_fail PARTITION OF list_parted FOR VALUES IN (somename);
+ ^
+CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (a);
+ERROR: cannot use column references in partition bound expression
+CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN
(sum(a));
+ERROR: aggregate functions are not allowed in partition bound
+LINE 1: ...s_expr_fail PARTITION OF list_parted FOR VALUES IN (sum(a));
+ ^
+CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN
((select 1));
+ERROR: cannot use subquery in partition bound
+LINE 1: ...expr_fail PARTITION OF list_parted FOR VALUES IN ((select 1)...
+ ^
+CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN
(generate_series(4, 6));
+ERROR: set-returning functions are not allowed in partition bound
+LINE 1: ...expr_fail PARTITION OF list_parted FOR VALUES IN (generate_s...
+ ^
-- syntax does not allow empty list of values for list partitions
CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES IN ();
ERROR: syntax error at or near ")"
@@ -501,19 +522,15 @@ ERROR: specified value cannot be cast to type boolean
for column "a"
LINE 1: ...REATE TABLE bools_true PARTITION OF bools FOR VALUES IN (1);
^
DROP TABLE bools;
--- specified literal can be cast, but cast isn't immutable
+-- specified literal can be cast, and the cast may be immutable
CREATE TABLE moneyp (
a money
) PARTITION BY LIST (a);
CREATE TABLE moneyp_10 PARTITION OF moneyp FOR VALUES IN (10);
-ERROR: specified value cannot be cast to type money for column "a"
-LINE 1: ...EATE TABLE moneyp_10 PARTITION OF moneyp FOR VALUES IN (10);
- ^
-DETAIL: The cast requires a non-immutable conversion.
-HINT: Try putting the literal value in single quotes.
-CREATE TABLE moneyp_10 PARTITION OF moneyp FOR VALUES IN ('10');
+CREATE TABLE moneyp_11 PARTITION OF moneyp FOR VALUES IN ('11');
+CREATE TABLE moneyp_12 PARTITION OF moneyp FOR VALUES IN (to_char(12,
'99')::int);
DROP TABLE moneyp;
--- immutable cast should work, though
+-- cast is immutable
CREATE TABLE bigintp (
a bigint
) PARTITION BY LIST (a);
@@ -774,6 +791,22 @@ create table parted_collate_must_match1 partition of
parted_collate_must_match
create table parted_collate_must_match2 partition of parted_collate_must_match
(b collate "POSIX") for values from ('m') to ('z');
drop table parted_collate_must_match;
+-- check for collation mismatch between partition key and partition bound
values
+create table test_part_coll_posix (a text) partition by range (a collate
"POSIX");
+create table test_part_coll_c partition of test_part_coll_posix for values
from ('a' collate "C") to ('m');
+ERROR: collation of partition bound value for column "a" does not match
partition key collation "POSIX"
+LINE 1: ...artition of test_part_coll_posix for values from ('a' collat...
+ ^
+create table test_part_coll_ok partition of test_part_coll_posix for values
from ('a' collate "POSIX") to ('m');
+drop table test_part_coll_posix;
+create table test_part_coll_default (a text) partition by range (a);
+create table test_part_coll_c partition of test_part_coll_default for values
from ('a' collate "C") to ('m');
+ERROR: collation of partition bound value for column "a" does not match
partition key collation "default"
+LINE 1: ...tition of test_part_coll_default for values from ('a' collat...
+ ^
+create table test_part_coll_ok partition of test_part_coll_default for values
from ('a') to ('m');
+create table test_part_coll_ok_too partition of test_part_coll_default for
values from ('m' collate "default") to ('z');
+drop table test_part_coll_default;
-- Partition bound in describe output
\d+ part_b
Table "public.part_b"
diff --git a/src/test/regress/sql/create_table.sql
b/src/test/regress/sql/create_table.sql
index a2cae9663c..64a21244a6 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -436,13 +436,18 @@ DROP TABLE partitioned, partitioned2;
CREATE TABLE list_parted (
a int
) PARTITION BY LIST (a);
--- syntax allows only string literal, numeric literal and null to be
--- specified for a partition bound value
CREATE TABLE part_1 PARTITION OF list_parted FOR VALUES IN ('1');
CREATE TABLE part_2 PARTITION OF list_parted FOR VALUES IN (2);
+CREATE TABLE part_3 PARTITION OF list_parted FOR VALUES IN ((2+1));
CREATE TABLE part_null PARTITION OF list_parted FOR VALUES IN (null);
-CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES IN (int '1');
-CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES IN ('1'::int);
+\d+ list_parted
+
+-- forbidden expressions for partition bound
+CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN
(somename);
+CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (a);
+CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN
(sum(a));
+CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN
((select 1));
+CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN
(generate_series(4, 6));
-- syntax does not allow empty list of values for list partitions
CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES IN ();
@@ -462,15 +467,16 @@ CREATE TABLE bools (
CREATE TABLE bools_true PARTITION OF bools FOR VALUES IN (1);
DROP TABLE bools;
--- specified literal can be cast, but cast isn't immutable
+-- specified literal can be cast, and the cast may be immutable
CREATE TABLE moneyp (
a money
) PARTITION BY LIST (a);
CREATE TABLE moneyp_10 PARTITION OF moneyp FOR VALUES IN (10);
-CREATE TABLE moneyp_10 PARTITION OF moneyp FOR VALUES IN ('10');
+CREATE TABLE moneyp_11 PARTITION OF moneyp FOR VALUES IN ('11');
+CREATE TABLE moneyp_12 PARTITION OF moneyp FOR VALUES IN (to_char(12,
'99')::int);
DROP TABLE moneyp;
--- immutable cast should work, though
+-- cast is immutable
CREATE TABLE bigintp (
a bigint
) PARTITION BY LIST (a);
@@ -686,6 +692,17 @@ create table parted_collate_must_match2 partition of
parted_collate_must_match
(b collate "POSIX") for values from ('m') to ('z');
drop table parted_collate_must_match;
+-- check for collation mismatch between partition key and partition bound
values
+create table test_part_coll_posix (a text) partition by range (a collate
"POSIX");
+create table test_part_coll_c partition of test_part_coll_posix for values
from ('a' collate "C") to ('m');
+create table test_part_coll_ok partition of test_part_coll_posix for values
from ('a' collate "POSIX") to ('m');
+drop table test_part_coll_posix;
+create table test_part_coll_default (a text) partition by range (a);
+create table test_part_coll_c partition of test_part_coll_default for values
from ('a' collate "C") to ('m');
+create table test_part_coll_ok partition of test_part_coll_default for values
from ('a') to ('m');
+create table test_part_coll_ok_too partition of test_part_coll_default for
values from ('m' collate "default") to ('z');
+drop table test_part_coll_default;
+
-- Partition bound in describe output
\d+ part_b
--
2.11.0
