Thanks for the comments.
On 2019/01/18 16:48, Peter Eisentraut wrote:
>> How about the following note in the documentation:
>>
>> + Although volatile expressions such as
>> + <literal><function>CURRENT_TIMESTAMP</function></literal> can be used
>> + for this, be careful when using them, because
>> + <productname>PostgreSQL</productname> will evaluate them only once
>> + when adding the partition.
>
> I don't think we have to phrase it in this warning way. Just say that
> volatile expressions are evaluated at the time of the DDL statement.
OK, then just this:
+ Even volatile expressions such as
+ <literal><function>CURRENT_TIMESTAMP</function></literal> can be used.
>> Sorry but I'm not sure how or what I would test about this. Maybe, just
>> add a test in create_table.sql/alter_table.sql that shows that using
>> volatile expression doesn't cause an error?
>
> Possibilities: Create a range partition with current_timestamp as the
> upper bound and then in a separate transaction insert current_timestamp
> and have it appear in the default partition. Or create list partition
> with session_user as one partition's value and then insert session_user
> and have it appear in that table. Or something like those.
OK, added a test that uses current_timestamp.
>> So, should the "name" type's collation should simply be discarded in favor
>> of "POSIX" that's being used for partitioning?
>
> In that specific case, yes, I think so.
>
>>> What we don't want is someone writing an explicit COLLATE clause. I
>>> think we just need to check that there is no top-level COLLATE clause.
>>> This would then sort of match the logic parse_collate.c for combining
>>> collations.
>>
>> Maybe I'm missing something, but isn't it OK to allow the COLLATE clause
>> as long as it specifies the matching collation as the parent?
>
> Yes, that should be OK.
Alright, I've included a test that uses cast expression in partition bound.
Updated patch attached.
Thanks,
Amit
>From f68a0c72c68dd096182be751b238621b3a8c6741 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 v9] 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 | 18 +--
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 | 214 +++++++++++++++++++----------
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 | 89 +++++++++---
src/test/regress/sql/create_table.sql | 51 ++++++-
14 files changed, 310 insertions(+), 168 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml
b/doc/src/sgml/ref/alter_table.sgml
index 0d1feaf828..0aa0f093f2 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_expr</replaceable> [, ...]
) |
+FROM ( { <replaceable class="parameter">partition_bound_expr</replaceable> |
MINVALUE | MAXVALUE } [, ...] )
+ TO ( { <replaceable class="parameter">partition_bound_expr</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..4230f6947a 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_expr</replaceable> [, ...]
) |
+FROM ( { <replaceable class="parameter">partition_bound_expr</replaceable> |
MINVALUE | MAXVALUE } [, ...] )
+ TO ( { <replaceable class="parameter">partition_bound_expr</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,12 @@ 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_expr</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.
+ Even volatile expressions such as
+ <literal><function>CURRENT_TIMESTAMP</function></literal> can be used.
</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..4fc4b9fd2e 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,34 @@ 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;
- /* 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.
+ */
+ if (IsA(value, CollateExpr))
+ {
+ Oid 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 +3909,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..6886e8e08b 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,29 @@ 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 that specifying incompatible collations for partition bound
+-- expressions fails promptly
+create table test_part_coll_posix (a text) partition by range (a collate
"POSIX");
+-- fail
+create table test_part_coll partition of test_part_coll_posix for values from
('a' collate "C") to ('g');
+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...
+ ^
+-- ok
+create table test_part_coll partition of test_part_coll_posix for values from
('a' collate "POSIX") to ('g');
+-- ok
+create table test_part_coll2 partition of test_part_coll_posix for values from
('g') to ('m');
+-- using a cast expression uses the target type's default collation
+-- fail
+create table test_part_coll_cast partition of test_part_coll_posix for values
from (name 'm' collate "C") to ('s');
+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 (name 'm' c...
+ ^
+-- ok
+create table test_part_coll_cast partition of test_part_coll_posix for values
from (name 'm' collate "POSIX") to ('s');
+-- ok; partition collation silently overrides the default collation of type
'name'
+create table test_part_coll_cast2 partition of test_part_coll_posix for values
from (name 's') to ('z');
+drop table test_part_coll_posix;
-- Partition bound in describe output
\d+ part_b
Table "public.part_b"
@@ -963,3 +1003,16 @@ CONTEXT: SQL statement "create table tab_part_create_1
partition of tab_part_cr
PL/pgSQL function func_part_create() line 3 at EXECUTE
drop table tab_part_create;
drop function func_part_create();
+-- test using a volatile expression as partition bound
+create table volatile_partbound_test (partkey timestamp) partition by range
(partkey);
+create table volatile_partbound_test1 partition of volatile_partbound_test for
values from (minvalue) to (current_timestamp);
+create table volatile_partbound_test2 partition of volatile_partbound_test for
values from (current_timestamp) to (maxvalue);
+-- this should go into the partition volatile_partbound_test2
+insert into volatile_partbound_test values (current_timestamp);
+select tableoid::regclass from volatile_partbound_test;
+ tableoid
+--------------------------
+ volatile_partbound_test2
+(1 row)
+
+drop table volatile_partbound_test;
diff --git a/src/test/regress/sql/create_table.sql
b/src/test/regress/sql/create_table.sql
index a2cae9663c..0b66056ffd 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,28 @@ 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 that specifying incompatible collations for partition bound
+-- expressions fails promptly
+
+create table test_part_coll_posix (a text) partition by range (a collate
"POSIX");
+-- fail
+create table test_part_coll partition of test_part_coll_posix for values from
('a' collate "C") to ('g');
+-- ok
+create table test_part_coll partition of test_part_coll_posix for values from
('a' collate "POSIX") to ('g');
+-- ok
+create table test_part_coll2 partition of test_part_coll_posix for values from
('g') to ('m');
+
+-- using a cast expression uses the target type's default collation
+
+-- fail
+create table test_part_coll_cast partition of test_part_coll_posix for values
from (name 'm' collate "C") to ('s');
+-- ok
+create table test_part_coll_cast partition of test_part_coll_posix for values
from (name 'm' collate "POSIX") to ('s');
+-- ok; partition collation silently overrides the default collation of type
'name'
+create table test_part_coll_cast2 partition of test_part_coll_posix for values
from (name 's') to ('z');
+
+drop table test_part_coll_posix;
+
-- Partition bound in describe output
\d+ part_b
@@ -776,3 +804,12 @@ create trigger trig_part_create before insert on
tab_part_create
insert into tab_part_create values (1);
drop table tab_part_create;
drop function func_part_create();
+
+-- test using a volatile expression as partition bound
+create table volatile_partbound_test (partkey timestamp) partition by range
(partkey);
+create table volatile_partbound_test1 partition of volatile_partbound_test for
values from (minvalue) to (current_timestamp);
+create table volatile_partbound_test2 partition of volatile_partbound_test for
values from (current_timestamp) to (maxvalue);
+-- this should go into the partition volatile_partbound_test2
+insert into volatile_partbound_test values (current_timestamp);
+select tableoid::regclass from volatile_partbound_test;
+drop table volatile_partbound_test;
--
2.11.0
