Hi Marina, I still don't see anything particularly wrong with your patch. It applies, passes all test, it is well test-covered and even documented. Also I've run `make installcheck` under Valgrind and didn't find any memory-related errors.
Is there anything that you would like to change before we call it more or less final? Also I would advice to add your branch to our internal buildfarm just to make sure everything is OK on exotic platforms like Windows ;) On Mon, May 22, 2017 at 06:32:17PM +0300, Marina Polyakova wrote: > > Hi, > > Hello! > > > I've not followed this thread, but just scanned this quickly because it > > affects execExpr* stuff. > > Thank you very much for your comments! Thanks to them I have made v4 of the > patches (as in the previous one, only planning and execution part is > changed). > > > Looks like having something like struct CachedExprState would be better, > > than these separate allocations? That also allows to aleviate some size > > concerns when adding new fields (see below) > > > I'd rather not have this on function scope - a) the stack pressure in > > ExecInterpExpr is quite noticeable in profiles already b) this is going > > to trigger warnings because of unused vars, because the compiler doesn't > > understand that EEOP_CACHEDEXPR_IF_CACHED always follows > > EEOP_CACHEDEXPR_SUBEXPR_END. > > > > How about instead storing oldcontext in the expression itself? > > Thanks, in new version I did all of it in this way. > > > I'm also not sure how acceptable it is to just assume it's ok to leave > > stuff in per_query_memory, in some cases that could prove to be > > problematic. > > I agree with you and in new version context is changed only for copying > datum of result value (if it's a pointer, its data should be allocated in > per_query_memory, or we will lost it for next tuples). > > > Is this actually a meaningful path? Shouldn't always have done const > > evaluation before adding CachedExpr's? > > eval_const_expressions_mutator is used several times, and one of them in > functions for selectivity evaluation (set_baserel_size_estimates -> > clauselist_selectivity -> clause_selectivity -> restriction_selectivity -> > ... -> get_restriction_variable -> estimate_expression_value -> > eval_const_expressions_mutator). In set_baserel_size_estimates function > right after selectivity evaluation there's costs evaluation and cached > expressions should be replaced before costs. I'm not sure that it is a good > idea to insert cached expressions replacement in set_baserel_size_estimates, > because in comments to it it's said "The rel's targetlist and restrictinfo > list must have been constructed already, and rel->tuples must be set." and > its file costsize.c is entitled as "Routines to compute (and set) relation > sizes and path costs". So I have inserted cached expressions replacement > just before it (but I'm not sure that I have seen all places where it should > be inserted). What do you think about all of this? > > -- > Marina Polyakova > Postgres Professional: http://www.postgrespro.com > The Russian Postgres Company > From 02262b9f3a3215d3884b6ac188bafa6517ac543d Mon Sep 17 00:00:00 2001 > From: Marina Polyakova <m.polyak...@postgrespro.ru> > Date: Mon, 15 May 2017 14:24:36 +0300 > Subject: [PATCH v4 1/3] Precalculate stable functions, infrastructure > > Now in Postgresql only immutable functions are precalculated; stable functions > are calculated for every row so in fact they don't differ from volatile > functions. > > This patch includes: > - creation of CachedExpr node > - usual node functions for it > - mutator to replace nonovolatile functions' and operators' expressions by > appropriate cached expressions. > --- > src/backend/nodes/copyfuncs.c | 31 +++++ > src/backend/nodes/equalfuncs.c | 31 +++++ > src/backend/nodes/nodeFuncs.c | 151 ++++++++++++++++++++ > src/backend/nodes/outfuncs.c | 56 ++++++++ > src/backend/nodes/readfuncs.c | 48 +++++++ > src/backend/optimizer/plan/planner.c | 259 > +++++++++++++++++++++++++++++++++++ > src/include/nodes/nodeFuncs.h | 1 + > src/include/nodes/nodes.h | 1 + > src/include/nodes/primnodes.h | 38 +++++ > 9 files changed, 616 insertions(+) > > diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c > index 6ad3844..f9f69a1 100644 > --- a/src/backend/nodes/copyfuncs.c > +++ b/src/backend/nodes/copyfuncs.c > @@ -1527,6 +1527,34 @@ _copyNullIfExpr(const NullIfExpr *from) > return newnode; > } > > +static CachedExpr * > +_copyCachedExpr(const CachedExpr *from) > +{ > + CachedExpr *newnode = makeNode(CachedExpr); > + > + COPY_SCALAR_FIELD(subexprtype); > + switch(from->subexprtype) > + { > + case CACHED_FUNCEXPR: > + COPY_NODE_FIELD(subexpr.funcexpr); > + break; > + case CACHED_OPEXPR: > + COPY_NODE_FIELD(subexpr.opexpr); > + break; > + case CACHED_DISTINCTEXPR: > + COPY_NODE_FIELD(subexpr.distinctexpr); > + break; > + case CACHED_NULLIFEXPR: > + COPY_NODE_FIELD(subexpr.nullifexpr); > + break; > + case CACHED_SCALARARRAYOPEXPR: > + COPY_NODE_FIELD(subexpr.saopexpr); > + break; > + } > + > + return newnode; > +} > + > /* > * _copyScalarArrayOpExpr > */ > @@ -4867,6 +4895,9 @@ copyObjectImpl(const void *from) > case T_NullIfExpr: > retval = _copyNullIfExpr(from); > break; > + case T_CachedExpr: > + retval = _copyCachedExpr(from); > + break; > case T_ScalarArrayOpExpr: > retval = _copyScalarArrayOpExpr(from); > break; > diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c > index c9a8c34..8863759 100644 > --- a/src/backend/nodes/equalfuncs.c > +++ b/src/backend/nodes/equalfuncs.c > @@ -384,6 +384,34 @@ _equalNullIfExpr(const NullIfExpr *a, const NullIfExpr > *b) > } > > static bool > +_equalCachedExpr(const CachedExpr *a, const CachedExpr *b) > +{ > + COMPARE_SCALAR_FIELD(subexprtype); > + > + /* the same subexprtype for b because we have already compared it */ > + switch(a->subexprtype) > + { > + case CACHED_FUNCEXPR: > + COMPARE_NODE_FIELD(subexpr.funcexpr); > + break; > + case CACHED_OPEXPR: > + COMPARE_NODE_FIELD(subexpr.opexpr); > + break; > + case CACHED_DISTINCTEXPR: > + COMPARE_NODE_FIELD(subexpr.distinctexpr); > + break; > + case CACHED_NULLIFEXPR: > + COMPARE_NODE_FIELD(subexpr.nullifexpr); > + break; > + case CACHED_SCALARARRAYOPEXPR: > + COMPARE_NODE_FIELD(subexpr.saopexpr); > + break; > + } > + > + return true; > +} > + > +static bool > _equalScalarArrayOpExpr(const ScalarArrayOpExpr *a, const ScalarArrayOpExpr > *b) > { > COMPARE_SCALAR_FIELD(opno); > @@ -3031,6 +3059,9 @@ equal(const void *a, const void *b) > case T_NullIfExpr: > retval = _equalNullIfExpr(a, b); > break; > + case T_CachedExpr: > + retval = _equalCachedExpr(a, b); > + break; > case T_ScalarArrayOpExpr: > retval = _equalScalarArrayOpExpr(a, b); > break; > diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c > index 3e8189c..e3dd576 100644 > --- a/src/backend/nodes/nodeFuncs.c > +++ b/src/backend/nodes/nodeFuncs.c > @@ -32,6 +32,7 @@ static bool planstate_walk_subplans(List *plans, bool > (*walker) (), > > void *context); > static bool planstate_walk_members(List *plans, PlanState **planstates, > bool (*walker) (), void *context); > +static const Node *get_const_subexpr(const CachedExpr *cachedexpr); > > > /* > @@ -92,6 +93,9 @@ exprType(const Node *expr) > case T_NullIfExpr: > type = ((const NullIfExpr *) expr)->opresulttype; > break; > + case T_CachedExpr: > + type = exprType(get_const_subexpr((const CachedExpr *) > expr)); > + break; > case T_ScalarArrayOpExpr: > type = BOOLOID; > break; > @@ -311,6 +315,8 @@ exprTypmod(const Node *expr) > return exprTypmod((Node *) > linitial(nexpr->args)); > } > break; > + case T_CachedExpr: > + return exprTypmod(get_const_subexpr((const CachedExpr > *) expr)); > case T_SubLink: > { > const SubLink *sublink = (const SubLink *) expr; > @@ -573,6 +579,10 @@ exprIsLengthCoercion(const Node *expr, int32 > *coercedTypmod) > return true; > } > > + if (expr && IsA(expr, CachedExpr)) > + return exprIsLengthCoercion( > + get_const_subexpr((const CachedExpr *) expr), > coercedTypmod); > + > return false; > } > > @@ -655,6 +665,10 @@ strip_implicit_coercions(Node *node) > if (c->coercionformat == COERCE_IMPLICIT_CAST) > return strip_implicit_coercions((Node *) c->arg); > } > + else if (IsA(node, CachedExpr)) > + { > + return strip_implicit_coercions(get_subexpr((CachedExpr *) > node)); > + } > return node; > } > > @@ -727,6 +741,8 @@ expression_returns_set_walker(Node *node, void *context) > return false; > if (IsA(node, XmlExpr)) > return false; > + if (IsA(node, CachedExpr)) > + return false; > > return expression_tree_walker(node, expression_returns_set_walker, > context); > @@ -790,6 +806,9 @@ exprCollation(const Node *expr) > case T_NullIfExpr: > coll = ((const NullIfExpr *) expr)->opcollid; > break; > + case T_CachedExpr: > + coll = exprCollation(get_const_subexpr((const > CachedExpr *) expr)); > + break; > case T_ScalarArrayOpExpr: > coll = InvalidOid; /* result is always boolean */ > break; > @@ -973,6 +992,10 @@ exprInputCollation(const Node *expr) > case T_NullIfExpr: > coll = ((const NullIfExpr *) expr)->inputcollid; > break; > + case T_CachedExpr: > + coll = exprInputCollation( > + get_const_subexpr((const CachedExpr *) expr)); > + break; > case T_ScalarArrayOpExpr: > coll = ((const ScalarArrayOpExpr *) expr)->inputcollid; > break; > @@ -1034,6 +1057,9 @@ exprSetCollation(Node *expr, Oid collation) > case T_NullIfExpr: > ((NullIfExpr *) expr)->opcollid = collation; > break; > + case T_CachedExpr: > + exprSetCollation(get_subexpr((CachedExpr *) expr), > collation); > + break; > case T_ScalarArrayOpExpr: > Assert(!OidIsValid(collation)); /* result is > always boolean */ > break; > @@ -1168,6 +1194,10 @@ exprSetInputCollation(Node *expr, Oid inputcollation) > case T_NullIfExpr: > ((NullIfExpr *) expr)->inputcollid = inputcollation; > break; > + case T_CachedExpr: > + exprSetInputCollation(get_subexpr((CachedExpr *) expr), > + > inputcollation); > + break; > case T_ScalarArrayOpExpr: > ((ScalarArrayOpExpr *) expr)->inputcollid = > inputcollation; > break; > @@ -1277,6 +1307,9 @@ exprLocation(const Node *expr) > > exprLocation((Node *) opexpr->args)); > } > break; > + case T_CachedExpr: > + loc = exprLocation(get_const_subexpr((const CachedExpr > *) expr)); > + break; > case T_ScalarArrayOpExpr: > { > const ScalarArrayOpExpr *saopexpr = (const > ScalarArrayOpExpr *) expr; > @@ -1611,6 +1644,8 @@ fix_opfuncids_walker(Node *node, void *context) > { > if (node == NULL) > return false; > + if (IsA(node, CachedExpr)) > + return fix_opfuncids_walker(get_subexpr((CachedExpr *) node), > context); > if (IsA(node, OpExpr)) > set_opfuncid((OpExpr *) node); > else if (IsA(node, DistinctExpr)) > @@ -1710,6 +1745,9 @@ check_functions_in_node(Node *node, > check_function_callback checker, > return true; > } > break; > + case T_CachedExpr: > + return check_functions_in_node(get_subexpr((CachedExpr > *) node), > + > checker, context); > case T_ScalarArrayOpExpr: > { > ScalarArrayOpExpr *expr = (ScalarArrayOpExpr *) > node; > @@ -1980,6 +2018,17 @@ expression_tree_walker(Node *node, > return true; > } > break; > + case T_CachedExpr: > + { > + /* > + * cachedexpr is processed by my_walker, so its > subexpr is > + * processed too and we need to process > sub-nodes of subexpr. > + */ > + if > (expression_tree_walker(get_subexpr((CachedExpr *) node), > + > walker, context)) > + return true; > + } > + break; > case T_ScalarArrayOpExpr: > { > ScalarArrayOpExpr *expr = (ScalarArrayOpExpr *) > node; > @@ -2617,6 +2666,54 @@ expression_tree_mutator(Node *node, > return (Node *) newnode; > } > break; > + case T_CachedExpr: > + { > + CachedExpr *expr = (CachedExpr *) node; > + CachedExpr *newnode; > + > + FLATCOPY(newnode, expr, CachedExpr); > + > + /* > + * expr is already mutated, so its subexpr is > already mutated > + * too and we need to mutate sub-nodes of > subexpr. > + */ > + switch(newnode->subexprtype) > + { > + case CACHED_FUNCEXPR: > + newnode->subexpr.funcexpr = > (FuncExpr *) > + expression_tree_mutator( > + (Node *) > expr->subexpr.funcexpr, mutator, > + context); > + break; > + case CACHED_OPEXPR: > + newnode->subexpr.opexpr = > (OpExpr *) > + expression_tree_mutator( > + (Node *) > expr->subexpr.opexpr, mutator, > + context); > + break; > + case CACHED_DISTINCTEXPR: > + newnode->subexpr.distinctexpr = > (DistinctExpr *) > + expression_tree_mutator( > + (Node *) > expr->subexpr.distinctexpr, mutator, > + context); > + break; > + case CACHED_NULLIFEXPR: > + newnode->subexpr.nullifexpr = > (NullIfExpr *) > + expression_tree_mutator( > + (Node *) > expr->subexpr.nullifexpr, mutator, > + context); > + break; > + case CACHED_SCALARARRAYOPEXPR: > + newnode->subexpr.saopexpr = > (ScalarArrayOpExpr *) > + expression_tree_mutator( > + (Node *) > expr->subexpr.saopexpr, mutator, > + context); > + break; > + } > + > + return (Node *) newnode; > + } > + break; > case T_ScalarArrayOpExpr: > { > ScalarArrayOpExpr *expr = (ScalarArrayOpExpr *) > node; > @@ -3838,3 +3935,57 @@ planstate_walk_members(List *plans, PlanState > **planstates, > > return false; > } > + > +/* > + * get_const_subexpr > + * Get const subexpression of given const cached expression. > + */ > +static const Node * > +get_const_subexpr(const CachedExpr *cachedexpr) > +{ > + if (cachedexpr == NULL) > + return NULL; > + > + switch (cachedexpr->subexprtype) > + { > + case CACHED_FUNCEXPR: > + return (const Node *) cachedexpr->subexpr.funcexpr; > + case CACHED_OPEXPR: > + return (const Node *) cachedexpr->subexpr.opexpr; > + case CACHED_DISTINCTEXPR: > + return (const Node *) cachedexpr->subexpr.distinctexpr; > + case CACHED_NULLIFEXPR: > + return (const Node *) cachedexpr->subexpr.nullifexpr; > + case CACHED_SCALARARRAYOPEXPR: > + return (const Node *) cachedexpr->subexpr.saopexpr; > + } > + > + return NULL; > +} > + > +/* > + * get_subexpr > + * Get subexpression of given cached expression. > + */ > +Node * > +get_subexpr(CachedExpr *cachedexpr) > +{ > + if (cachedexpr == NULL) > + return NULL; > + > + switch (cachedexpr->subexprtype) > + { > + case CACHED_FUNCEXPR: > + return (Node *) cachedexpr->subexpr.funcexpr; > + case CACHED_OPEXPR: > + return (Node *) cachedexpr->subexpr.opexpr; > + case CACHED_DISTINCTEXPR: > + return (Node *) cachedexpr->subexpr.distinctexpr; > + case CACHED_NULLIFEXPR: > + return (Node *) cachedexpr->subexpr.nullifexpr; > + case CACHED_SCALARARRAYOPEXPR: > + return (Node *) cachedexpr->subexpr.saopexpr; > + } > + > + return NULL; > +} > diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c > index 8d9ff63..c0c8363 100644 > --- a/src/backend/nodes/outfuncs.c > +++ b/src/backend/nodes/outfuncs.c > @@ -1237,6 +1237,59 @@ _outNullIfExpr(StringInfo str, const NullIfExpr *node) > } > > static void > +_outCachedExpr(StringInfo str, const CachedExpr *node) > +{ > + WRITE_NODE_TYPE("CACHEDEXPR"); > + > + /* do-it-yourself enum representation; out subexprtype begin... */ > + appendStringInfoString(str, " :subexprtype "); > + > + switch(node->subexprtype) > + { > + case CACHED_FUNCEXPR: > + { > + /* ... out subexprtype end */ > + outToken(str, "cached_funcexpr"); > + > + WRITE_NODE_FIELD(subexpr.funcexpr); > + } > + break; > + case CACHED_OPEXPR: > + { > + /* ... out subexprtype end */ > + outToken(str, "cached_opexpr"); > + > + WRITE_NODE_FIELD(subexpr.opexpr); > + } > + break; > + case CACHED_DISTINCTEXPR: > + { > + /* ... out subexprtype end */ > + outToken(str, "cached_distinctexpr"); > + > + WRITE_NODE_FIELD(subexpr.distinctexpr); > + } > + break; > + case CACHED_NULLIFEXPR: > + { > + /* ... out subexprtype end */ > + outToken(str, "cached_nullifexpr"); > + > + WRITE_NODE_FIELD(subexpr.nullifexpr); > + } > + break; > + case CACHED_SCALARARRAYOPEXPR: > + { > + /* ... out subexprtype end */ > + outToken(str, "cached_scalararrayopexpr"); > + > + WRITE_NODE_FIELD(subexpr.saopexpr); > + } > + break; > + } > +} > + > +static void > _outScalarArrayOpExpr(StringInfo str, const ScalarArrayOpExpr *node) > { > WRITE_NODE_TYPE("SCALARARRAYOPEXPR"); > @@ -3767,6 +3820,9 @@ outNode(StringInfo str, const void *obj) > case T_NullIfExpr: > _outNullIfExpr(str, obj); > break; > + case T_CachedExpr: > + _outCachedExpr(str, obj); > + break; > case T_ScalarArrayOpExpr: > _outScalarArrayOpExpr(str, obj); > break; > diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c > index e24f5d6..acb14f9 100644 > --- a/src/backend/nodes/readfuncs.c > +++ b/src/backend/nodes/readfuncs.c > @@ -750,6 +750,52 @@ _readNullIfExpr(void) > } > > /* > + * _readCachedExpr > + */ > +static CachedExpr * > +_readCachedExpr(void) > +{ > + READ_LOCALS(CachedExpr); > + > + /* do-it-yourself enum representation */ > + token = pg_strtok(&length); /* skip :subexprtype */ > + token = pg_strtok(&length); /* get field value */ > + if (strncmp(token, "cached_funcexpr", 15) == 0) > + local_node->subexprtype = CACHED_FUNCEXPR; > + else if (strncmp(token, "cached_opexpr", 13) == 0) > + local_node->subexprtype = CACHED_OPEXPR; > + else if (strncmp(token, "cached_distinctexpr", 19) == 0) > + local_node->subexprtype = CACHED_DISTINCTEXPR; > + else if (strncmp(token, "cached_nullifexpr", 17) == 0) > + local_node->subexprtype = CACHED_NULLIFEXPR; > + else if (strncmp(token, "cached_scalararrayopexpr", 24) == 0) > + local_node->subexprtype = CACHED_SCALARARRAYOPEXPR; > + else > + elog(ERROR, "unrecognized subexprtype \"%.*s\"", length, token); > + > + switch (local_node->subexprtype) > + { > + case CACHED_FUNCEXPR: > + READ_NODE_FIELD(subexpr.funcexpr); > + break; > + case CACHED_OPEXPR: > + READ_NODE_FIELD(subexpr.opexpr); > + break; > + case CACHED_DISTINCTEXPR: > + READ_NODE_FIELD(subexpr.distinctexpr); > + break; > + case CACHED_NULLIFEXPR: > + READ_NODE_FIELD(subexpr.nullifexpr); > + break; > + case CACHED_SCALARARRAYOPEXPR: > + READ_NODE_FIELD(subexpr.saopexpr); > + break; > + } > + > + READ_DONE(); > +} > + > +/* > * _readScalarArrayOpExpr > */ > static ScalarArrayOpExpr * > @@ -2462,6 +2508,8 @@ parseNodeString(void) > return_value = _readDistinctExpr(); > else if (MATCH("NULLIFEXPR", 10)) > return_value = _readNullIfExpr(); > + else if (MATCH("CACHEDEXPR", 10)) > + return_value = _readCachedExpr(); > else if (MATCH("SCALARARRAYOPEXPR", 17)) > return_value = _readScalarArrayOpExpr(); > else if (MATCH("BOOLEXPR", 8)) > diff --git a/src/backend/optimizer/plan/planner.c > b/src/backend/optimizer/plan/planner.c > index c4a5651..552b73d 100644 > --- a/src/backend/optimizer/plan/planner.c > +++ b/src/backend/optimizer/plan/planner.c > @@ -184,6 +184,7 @@ static PathTarget *make_sort_input_target(PlannerInfo > *root, > bool *have_postponed_srfs); > static void adjust_paths_for_srfs(PlannerInfo *root, RelOptInfo *rel, > List *targets, List > *targets_contain_srfs); > +static Node *replace_cached_expressions_mutator(Node *node); > > > > /***************************************************************************** > @@ -6086,3 +6087,261 @@ get_partitioned_child_rels(PlannerInfo *root, Index > rti) > > return result; > } > + > +static Node * > +replace_cached_expressions_mutator(Node *node) > +{ > + if (node == NULL) > + return NULL; > + > + /* mutate certain types of nodes */ > + if (IsA(node, RestrictInfo)) > + { > + RestrictInfo *rinfo = (RestrictInfo *) node; > + > + /* > + * For an OR clause, recurse into the marked-up tree so that we > replace > + * cached expressions for contained RestrictInfos too. > + */ > + if (rinfo->orclause) > + rinfo->orclause = (Expr *) > replace_cached_expressions_mutator( > + (Node *) rinfo->orclause); > + else > + rinfo->clause = (Expr *) > replace_cached_expressions_mutator( > + (Node *) rinfo->clause); > + > + /* do NOT recurse into children */ > + return node; > + } > + else if (IsA(node, FuncExpr)) > + { > + /* > + * Function is cached if: > + * 1) it doesn't return set, > + * 2) it's not volatile itself, > + * 3) its arguments are constants or cached expressions too. > + */ > + FuncExpr *funcexpr; > + ListCell *arg; > + bool has_nonconst_or_noncached_input = false; > + bool func_returns_set; > + > + /* firstly recurse into children */ > + funcexpr = (FuncExpr *) expression_tree_mutator(node, > + > replace_cached_expressions_mutator, > + > NULL); > + func_returns_set = funcexpr->funcretset || > + expression_returns_set((Node *) funcexpr->args); > + > + foreach(arg, funcexpr->args) > + { > + void *arg_lfirst = lfirst(arg); > + if (!(IsA(arg_lfirst, Const) || IsA(arg_lfirst, > CachedExpr))) > + has_nonconst_or_noncached_input = true; > + } > + > + if (func_returns_set || > + has_nonconst_or_noncached_input || > + contain_volatile_functions((Node *) &funcexpr->xpr)) > + { > + /* return FuncExpr, which will not be cached */ > + return (Node *) funcexpr; > + } > + else > + { > + /* create and return CachedExpr */ > + CachedExpr *new_node = makeNode(CachedExpr); > + new_node->subexprtype = CACHED_FUNCEXPR; > + new_node->subexpr.funcexpr = funcexpr; > + > + return (Node *) new_node; > + } > + } > + else if (IsA(node, OpExpr)) > + { > + /* > + * Operator is cached if: > + * 1) its function doesn't return set, > + * 1) its function is not volatile itself, > + * 3) its arguments are constants or cached expressions too. > + */ > + OpExpr *opexpr = (OpExpr *) node; > + ListCell *arg; > + bool has_nonconst_or_noncached_input = false; > + bool op_returns_set; > + > + /* rely on struct equivalence to treat these all alike */ > + set_opfuncid(opexpr); > + > + /* firstly recurse into children */ > + opexpr = (OpExpr *) expression_tree_mutator(node, > + > replace_cached_expressions_mutator, > + > NULL); > + op_returns_set = opexpr->opretset || > + expression_returns_set((Node *) opexpr->args); > + > + foreach(arg, opexpr->args) > + { > + void *arg_lfirst = lfirst(arg); > + if (!(IsA(arg_lfirst, Const) || IsA(arg_lfirst, > CachedExpr))) > + has_nonconst_or_noncached_input = true; > + } > + > + if (op_returns_set || > + has_nonconst_or_noncached_input || > + contain_volatile_functions((Node *) &opexpr->xpr)) > + { > + /* return OpExpr, which will not be cached */ > + return (Node *) opexpr; > + } > + else > + { > + /* create and return CachedExpr */ > + CachedExpr *new_node = makeNode(CachedExpr); > + new_node->subexprtype = CACHED_OPEXPR; > + new_node->subexpr.opexpr = opexpr; > + > + return (Node *) new_node; > + } > + } > + else if (IsA(node, DistinctExpr)) > + { > + /* > + * Operator of DistinctExpr is cached if: > + * 1) its function doesn't return set, > + * 1) its function is not volatile itself, > + * 3) its arguments are constants or cached expressions too. > + */ > + DistinctExpr *distinctexpr = (DistinctExpr *) node; > + ListCell *arg; > + bool has_nonconst_or_noncached_input = false; > + bool op_returns_set; > + > + /* rely on struct equivalence to treat these all alike */ > + set_opfuncid((OpExpr *) distinctexpr); > + > + /* firstly recurse into children */ > + distinctexpr = (DistinctExpr *) expression_tree_mutator(node, > + > replace_cached_expressions_mutator, > + > NULL); > + op_returns_set = distinctexpr->opretset || > + expression_returns_set((Node *) distinctexpr->args); > + > + foreach(arg, distinctexpr->args) > + { > + void *arg_lfirst = lfirst(arg); > + if (!(IsA(arg_lfirst, Const) || IsA(arg_lfirst, > CachedExpr))) > + has_nonconst_or_noncached_input = true; > + } > + > + if (op_returns_set || > + has_nonconst_or_noncached_input || > + contain_volatile_functions((Node *) &distinctexpr->xpr)) > + { > + /* return DistinctExpr, which will not be cached */ > + return (Node *) distinctexpr; > + } > + else > + { > + /* create and return CachedExpr */ > + CachedExpr *new_node = makeNode(CachedExpr); > + new_node->subexprtype = CACHED_DISTINCTEXPR; > + new_node->subexpr.distinctexpr = distinctexpr; > + > + return (Node *) new_node; > + } > + } > + else if (IsA(node, NullIfExpr)) > + { > + /* > + * Operator of NullIfExpr is cached if: > + * 1) its function doesn't return set, > + * 1) its function is not volatile itself, > + * 3) its arguments are constants or cached expressions too. > + */ > + NullIfExpr *nullifexpr = (NullIfExpr *) node; > + ListCell *arg; > + bool has_nonconst_or_noncached_input = false; > + bool op_returns_set; > + > + /* rely on struct equivalence to treat these all alike */ > + set_opfuncid((OpExpr *) nullifexpr); > + > + /* firstly recurse into children */ > + nullifexpr = (NullIfExpr *) expression_tree_mutator(node, > + > replace_cached_expressions_mutator, > + > NULL); > + op_returns_set = nullifexpr->opretset || > + expression_returns_set((Node *) nullifexpr->args); > + > + foreach(arg, nullifexpr->args) > + { > + void *arg_lfirst = lfirst(arg); > + if (!(IsA(arg_lfirst, Const) || IsA(arg_lfirst, > CachedExpr))) > + has_nonconst_or_noncached_input = true; > + } > + > + if (op_returns_set || > + has_nonconst_or_noncached_input || > + contain_volatile_functions((Node *) &nullifexpr->xpr)) > + { > + /* return NullIfExpr, which will not be cached */ > + return (Node *) nullifexpr; > + } > + else > + { > + /* create and return CachedExpr */ > + CachedExpr *new_node = makeNode(CachedExpr); > + new_node->subexprtype = CACHED_NULLIFEXPR; > + new_node->subexpr.nullifexpr = nullifexpr; > + > + return (Node *) new_node; > + } > + } > + else if (IsA(node, ScalarArrayOpExpr)) > + { > + /* > + * Operator of ScalarArrayOpExpr is cached if: > + * 1) its function is not volatile itself, > + * 2) its arguments are constants or cached expressions too. > + * (it returns boolean so we don't need to check if it returns > set) > + */ > + ScalarArrayOpExpr *saopexpr = (ScalarArrayOpExpr *) node; > + ListCell *arg; > + bool has_nonconst_or_noncached_input = false; > + > + set_sa_opfuncid(saopexpr); > + > + /* firstly recurse into children */ > + saopexpr = (ScalarArrayOpExpr *) expression_tree_mutator(node, > + > replace_cached_expressions_mutator, > + > NULL); > + > + foreach(arg, saopexpr->args) > + { > + void *arg_lfirst = lfirst(arg); > + if (!(IsA(arg_lfirst, Const) || IsA(arg_lfirst, > CachedExpr))) > + has_nonconst_or_noncached_input = true; > + } > + > + if (has_nonconst_or_noncached_input || > + contain_volatile_functions((Node *) &saopexpr->xpr)) > + { > + /* return ScalarArrayOpExpr, which will not be cached */ > + return (Node *) saopexpr; > + } > + else > + { > + /* create and return CachedExpr */ > + CachedExpr *new_node = makeNode(CachedExpr); > + new_node->subexprtype = CACHED_SCALARARRAYOPEXPR; > + new_node->subexpr.saopexpr = saopexpr; > + > + return (Node *) new_node; > + } > + } > + > + /* otherwise recurse into children */ > + return expression_tree_mutator(node, replace_cached_expressions_mutator, > + NULL); > +} > diff --git a/src/include/nodes/nodeFuncs.h b/src/include/nodes/nodeFuncs.h > index b6c9b48..0dbfa12 100644 > --- a/src/include/nodes/nodeFuncs.h > +++ b/src/include/nodes/nodeFuncs.h > @@ -76,5 +76,6 @@ extern bool raw_expression_tree_walker(Node *node, bool > (*walker) (), > struct PlanState; > extern bool planstate_tree_walker(struct PlanState *planstate, bool > (*walker) (), > > void *context); > +extern Node * get_subexpr(CachedExpr *cachedexpr); > > #endif /* NODEFUNCS_H */ > diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h > index f59d719..054bc61 100644 > --- a/src/include/nodes/nodes.h > +++ b/src/include/nodes/nodes.h > @@ -155,6 +155,7 @@ typedef enum NodeTag > T_OpExpr, > T_DistinctExpr, > T_NullIfExpr, > + T_CachedExpr, > T_ScalarArrayOpExpr, > T_BoolExpr, > T_SubLink, > diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h > index 86ec82e..3f89653 100644 > --- a/src/include/nodes/primnodes.h > +++ b/src/include/nodes/primnodes.h > @@ -1498,4 +1498,42 @@ typedef struct OnConflictExpr > List *exclRelTlist; /* tlist of the EXCLUDED pseudo > relation */ > } OnConflictExpr; > > +/* > + * Discriminator for CachedExpr. > + * > + * Identifies the subexpression to be cached in execution (= executed only > once > + * and then used cached value) and which member in the CachedExpr->subexpr > union > + * is valid. > + */ > +typedef enum CachedSubExprType > +{ > + CACHED_FUNCEXPR, /* cached FuncExpr */ > + CACHED_OPEXPR, /* cached OpExpr */ > + CACHED_DISTINCTEXPR, /* cached DistinctExpr */ > + CACHED_NULLIFEXPR, /* cached NullIfExpr */ > + CACHED_SCALARARRAYOPEXPR /* cached ScalarArrayOpExpr */ > +} CachedSubExprType; > + > +/* > + * CachedExpr - expression node for precalculated stable and immutable > functions > + * (= they are calculated once for all output rows, but as many times as > + * function is mentioned in query), if they don't return a set and their > + * arguments are constants or recursively precalculated functions. The same > for > + * operators' functions. > + */ > +typedef struct CachedExpr > +{ > + Expr xpr; > + CachedSubExprType subexprtype; /* expression to be cached */ > + > + union SubExpr > + { > + FuncExpr *funcexpr; /* for CACHED_FUNCEXPR */ > + OpExpr *opexpr; /* for CACHED_OPEXPR */ > + DistinctExpr *distinctexpr; /* for CACHED_DISTINCTEXPR */ > + NullIfExpr *nullifexpr; /* for CACHED_NULLIFEXPR */ > + ScalarArrayOpExpr *saopexpr; /* for CACHED_SCALARARRAYOPEXPR > */ > + } subexpr; > +} CachedExpr; > + > #endif /* PRIMNODES_H */ > -- > 1.9.1 > > From 537d8a2bb085efdfce695f148e614ed4611f9a6e Mon Sep 17 00:00:00 2001 > From: Marina Polyakova <m.polyak...@postgrespro.ru> > Date: Mon, 15 May 2017 15:31:21 +0300 > Subject: [PATCH v4 2/3] Precalculate stable functions, planning and execution > > Now in Postgresql only immutable functions are precalculated; stable functions > are calculated for every row so in fact they don't differ from volatile > functions. > > This patch includes: > - replacement nonvolatile functions and operators by appropriate cached > expressions > - planning and execution cached expressions > - regression tests > --- > src/backend/executor/execExpr.c | 55 + > src/backend/executor/execExprInterp.c | 51 + > src/backend/optimizer/path/allpaths.c | 9 +- > src/backend/optimizer/path/clausesel.c | 13 + > src/backend/optimizer/plan/planagg.c | 1 + > src/backend/optimizer/plan/planner.c | 28 + > src/backend/optimizer/util/clauses.c | 55 + > src/backend/utils/adt/ruleutils.c | 5 + > src/include/executor/execExpr.h | 37 + > src/include/optimizer/planner.h | 3 + > src/include/optimizer/tlist.h | 8 +- > src/pl/plpgsql/src/pl_exec.c | 10 + > .../expected/precalculate_stable_functions.out | 2625 > ++++++++++++++++++++ > src/test/regress/serial_schedule | 1 + > .../regress/sql/precalculate_stable_functions.sql | 949 +++++++ > 15 files changed, 3847 insertions(+), 3 deletions(-) > create mode 100644 > src/test/regress/expected/precalculate_stable_functions.out > create mode 100644 src/test/regress/sql/precalculate_stable_functions.sql > > diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c > index 5a34a46..dc84975 100644 > --- a/src/backend/executor/execExpr.c > +++ b/src/backend/executor/execExpr.c > @@ -865,6 +865,61 @@ ExecInitExprRec(Expr *node, PlanState *parent, ExprState > *state, > break; > } > > + case T_CachedExpr: > + { > + CachedExpr *cachedexpr = (CachedExpr *) node; > + > + /* > + * Allocate CachedExprState used by all steps > of CachedExpr > + * evaluation. > + */ > + scratch.d.cachedexpr.state = (CachedExprState > *) palloc( > + sizeof(CachedExprState)); > + scratch.d.cachedexpr.state->isExecuted = false; > + scratch.d.cachedexpr.state->resnull = false; > + scratch.d.cachedexpr.state->resvalue = (Datum) > 0; > + > + switch(cachedexpr->subexprtype) > + { > + case CACHED_FUNCEXPR: > + > scratch.d.cachedexpr.state->restypid = > + > cachedexpr->subexpr.funcexpr->funcresulttype; > + break; > + case CACHED_OPEXPR: > + > scratch.d.cachedexpr.state->restypid = > + > cachedexpr->subexpr.opexpr->opresulttype; > + break; > + case CACHED_DISTINCTEXPR: > + > scratch.d.cachedexpr.state->restypid = > + > cachedexpr->subexpr.distinctexpr->opresulttype; > + break; > + case CACHED_NULLIFEXPR: > + > scratch.d.cachedexpr.state->restypid = > + > cachedexpr->subexpr.nullifexpr->opresulttype; > + break; > + case CACHED_SCALARARRAYOPEXPR: > + > scratch.d.cachedexpr.state->restypid = BOOLOID; > + break; > + } > + > + /* add EEOP_CACHEDEXPR_IF_CACHED step */ > + scratch.opcode = EEOP_CACHEDEXPR_IF_CACHED; > + ExprEvalPushStep(state, &scratch); > + > + /* add subexpression steps */ > + ExecInitExprRec((Expr *) > get_subexpr(cachedexpr), parent, state, > + resv, resnull); > + > + /* add EEOP_CACHEDEXPR_SUBEXPR_END step */ > + scratch.opcode = EEOP_CACHEDEXPR_SUBEXPR_END; > + ExprEvalPushStep(state, &scratch); > + > + /* adjust jump target */ > + scratch.d.cachedexpr.state->jumpdone = > state->steps_len; > + > + break; > + } > + > case T_ScalarArrayOpExpr: > { > ScalarArrayOpExpr *opexpr = (ScalarArrayOpExpr > *) node; > diff --git a/src/backend/executor/execExprInterp.c > b/src/backend/executor/execExprInterp.c > index fed0052..2cb10fd 100644 > --- a/src/backend/executor/execExprInterp.c > +++ b/src/backend/executor/execExprInterp.c > @@ -70,6 +70,7 @@ > #include "pgstat.h" > #include "utils/builtins.h" > #include "utils/date.h" > +#include "utils/datum.h" > #include "utils/lsyscache.h" > #include "utils/timestamp.h" > #include "utils/typcache.h" > @@ -309,6 +310,8 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, > bool *isnull) > &&CASE_EEOP_FUNCEXPR_STRICT, > &&CASE_EEOP_FUNCEXPR_FUSAGE, > &&CASE_EEOP_FUNCEXPR_STRICT_FUSAGE, > + &&CASE_EEOP_CACHEDEXPR_IF_CACHED, > + &&CASE_EEOP_CACHEDEXPR_SUBEXPR_END, > &&CASE_EEOP_BOOL_AND_STEP_FIRST, > &&CASE_EEOP_BOOL_AND_STEP, > &&CASE_EEOP_BOOL_AND_STEP_LAST, > @@ -721,6 +724,54 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, > bool *isnull) > EEO_NEXT(); > } > > + EEO_CASE(EEOP_CACHEDEXPR_IF_CACHED) > + { > + if (op->d.cachedexpr.state->isExecuted) > + { > + /* use saved result and skip subexpression > evaluation */ > + *op->resnull = op->d.cachedexpr.state->resnull; > + if (!(*op->resnull)) > + *op->resvalue = > op->d.cachedexpr.state->resvalue; > + > + EEO_JUMP(op->d.cachedexpr.state->jumpdone); > + } > + > + /* we are ready for subexpression evaluation */ > + EEO_NEXT(); > + } > + > + EEO_CASE(EEOP_CACHEDEXPR_SUBEXPR_END) > + { > + int16 restyplen; > + bool restypbyval; > + > + /* save result */ > + op->d.cachedexpr.state->resnull = *op->resnull; > + if (!(*op->resnull)) > + { > + > get_typlenbyval(op->d.cachedexpr.state->restypid, &restyplen, > + &restypbyval); > + > + /* > + * Switch per-query memory context. It is > necessary to save the > + * subexpression result value between all > tuples if its datum is > + * a pointer. > + */ > + op->d.cachedexpr.state->oldContext = > MemoryContextSwitchTo( > + econtext->ecxt_per_query_memory); > + > + op->d.cachedexpr.state->resvalue = > datumCopy(*op->resvalue, > + > restypbyval, > + > restyplen); > + > + /* switch memory context back */ > + > MemoryContextSwitchTo(op->d.cachedexpr.state->oldContext); > + } > + op->d.cachedexpr.state->isExecuted = true; > + > + EEO_NEXT(); > + } > + > /* > * If any of its clauses is FALSE, an AND's result is FALSE > regardless > * of the states of the rest of the clauses, so we can stop > evaluating > diff --git a/src/backend/optimizer/path/allpaths.c > b/src/backend/optimizer/path/allpaths.c > index b93b4fc..a322255 100644 > --- a/src/backend/optimizer/path/allpaths.c > +++ b/src/backend/optimizer/path/allpaths.c > @@ -378,7 +378,11 @@ set_rel_size(PlannerInfo *root, RelOptInfo *rel, > set_subquery_pathlist(root, rel, rti, rte); > break; > case RTE_FUNCTION: > - set_function_size_estimates(root, rel); > + { > + rel->baserestrictinfo = > replace_qual_cached_expressions( > + rel->baserestrictinfo); > + set_function_size_estimates(root, rel); > + } > break; > case RTE_TABLEFUNC: > set_tablefunc_size_estimates(root, rel); > @@ -517,6 +521,9 @@ set_plain_rel_size(PlannerInfo *root, RelOptInfo *rel, > RangeTblEntry *rte) > */ > check_index_predicates(root, rel); > > + rel->baserestrictinfo = replace_qual_cached_expressions( > + rel->baserestrictinfo); > + > /* Mark rel with estimated output rows, width, etc */ > set_baserel_size_estimates(root, rel); > } > diff --git a/src/backend/optimizer/path/clausesel.c > b/src/backend/optimizer/path/clausesel.c > index 758ddea..fc799f1 100644 > --- a/src/backend/optimizer/path/clausesel.c > +++ b/src/backend/optimizer/path/clausesel.c > @@ -15,6 +15,7 @@ > #include "postgres.h" > > #include "nodes/makefuncs.h" > +#include "nodes/nodeFuncs.h" > #include "optimizer/clauses.h" > #include "optimizer/cost.h" > #include "optimizer/pathnode.h" > @@ -825,6 +826,18 @@ clause_selectivity(PlannerInfo *root, > jointype, > sjinfo); > } > + else if (IsA(clause, CachedExpr)) > + { > + /* > + * Not sure this case is needed, but it can't hurt. > + * Calculate selectivity of subexpression. > + */ > + s1 = clause_selectivity(root, > + > get_subexpr((CachedExpr *) clause), > + varRelid, > + jointype, > + sjinfo); > + } > else > { > /* > diff --git a/src/backend/optimizer/plan/planagg.c > b/src/backend/optimizer/plan/planagg.c > index 5565736..7a28764 100644 > --- a/src/backend/optimizer/plan/planagg.c > +++ b/src/backend/optimizer/plan/planagg.c > @@ -38,6 +38,7 @@ > #include "optimizer/pathnode.h" > #include "optimizer/paths.h" > #include "optimizer/planmain.h" > +#include "optimizer/planner.h" > #include "optimizer/subselect.h" > #include "optimizer/tlist.h" > #include "parser/parsetree.h" > diff --git a/src/backend/optimizer/plan/planner.c > b/src/backend/optimizer/plan/planner.c > index 552b73d..7c68d6d 100644 > --- a/src/backend/optimizer/plan/planner.c > +++ b/src/backend/optimizer/plan/planner.c > @@ -6088,6 +6088,34 @@ get_partitioned_child_rels(PlannerInfo *root, Index > rti) > return result; > } > > +/* > + * replace_pathtarget_cached_expressions > + * Replace cached expresisons in a PathTarget tlist. > + * > + * As a notational convenience, returns the same PathTarget pointer passed > in. > + */ > +PathTarget * > +replace_pathtarget_cached_expressions(PathTarget *target) > +{ > + target->exprs = (List *) replace_cached_expressions_mutator( > + (Node *) target->exprs); > + > + return target; > +} > + > +/* > + * replace_qual_cached_expressions > + * Replace cacehd expressions in a WHERE clause. The input can be > either an > + * implicitly-ANDed list of boolean expressions, or a list of > RestrictInfo > + * nodes. > + */ > +List * > +replace_qual_cached_expressions(List *quals) > +{ > + /* No setup needed for tree walk, so away we go */ > + return (List *) replace_cached_expressions_mutator((Node *) quals); > +} > + > static Node * > replace_cached_expressions_mutator(Node *node) > { > diff --git a/src/backend/optimizer/util/clauses.c > b/src/backend/optimizer/util/clauses.c > index a1dafc8..0c0284a 100644 > --- a/src/backend/optimizer/util/clauses.c > +++ b/src/backend/optimizer/util/clauses.c > @@ -2758,6 +2758,61 @@ eval_const_expressions_mutator(Node *node, > newexpr->location = expr->location; > return (Node *) newexpr; > } > + case T_CachedExpr: > + { > + CachedExpr *cachedexpr = (CachedExpr *) node; > + Node *new_subexpr = > eval_const_expressions_mutator( > + get_subexpr(cachedexpr), context); > + CachedExpr *new_cachedexpr; > + > + /* > + * If unsafe transformations are used cached > expression should > + * be always simplified. > + */ > + if (context->estimate) > + Assert(IsA(new_subexpr, Const)); > + > + if (IsA(new_subexpr, Const)) > + { > + /* successfully simplified it */ > + return new_subexpr; > + } > + else > + { > + /* > + * The expression cannot be simplified > any further, so build > + * and return a replacement CachedExpr > node using the > + * possibly-simplified arguments of > subexpression. > + */ > + new_cachedexpr = makeNode(CachedExpr); > + new_cachedexpr->subexprtype = > cachedexpr->subexprtype; > + switch (new_cachedexpr->subexprtype) > + { > + case CACHED_FUNCEXPR: > + > new_cachedexpr->subexpr.funcexpr = (FuncExpr *) > + new_subexpr; > + break; > + case CACHED_OPEXPR: > + > new_cachedexpr->subexpr.opexpr = (OpExpr *) > + new_subexpr; > + break; > + case CACHED_DISTINCTEXPR: > + > new_cachedexpr->subexpr.distinctexpr = > + (DistinctExpr > *) new_subexpr; > + break; > + case CACHED_NULLIFEXPR: > + > new_cachedexpr->subexpr.nullifexpr = (NullIfExpr *) > + new_subexpr; > + break; > + case CACHED_SCALARARRAYOPEXPR: > + > new_cachedexpr->subexpr.saopexpr = > + > (ScalarArrayOpExpr *) new_subexpr; > + break; > + } > + > + return (Node *) new_cachedexpr; > + } > + } > case T_BoolExpr: > { > BoolExpr *expr = (BoolExpr *) node; > diff --git a/src/backend/utils/adt/ruleutils.c > b/src/backend/utils/adt/ruleutils.c > index 43b1475..838389d 100644 > --- a/src/backend/utils/adt/ruleutils.c > +++ b/src/backend/utils/adt/ruleutils.c > @@ -7720,6 +7720,11 @@ get_rule_expr(Node *node, deparse_context *context, > } > break; > > + case T_CachedExpr: > + get_rule_expr(get_subexpr((CachedExpr *) node), context, > + showimplicit); > + break; > + > case T_ScalarArrayOpExpr: > { > ScalarArrayOpExpr *expr = (ScalarArrayOpExpr *) > node; > diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h > index 86fdb33..ea37a36 100644 > --- a/src/include/executor/execExpr.h > +++ b/src/include/executor/execExpr.h > @@ -86,6 +86,16 @@ typedef enum ExprEvalOp > EEOP_FUNCEXPR_STRICT_FUSAGE, > > /* > + * Evaluate CachedExpr. EEOP_CACHEDEXPR_IF_CACHED is used before > + * subexpression evaluation (if subexpression was evaluated use cached > value > + * and jump to next state or get prepared to subexpression evaluation > + * otherwise). EEOP_CACHEDEXPR_SUBEXPR_END is used after subexpression > + * evaluation for caching its result. > + */ > + EEOP_CACHEDEXPR_IF_CACHED, > + EEOP_CACHEDEXPR_SUBEXPR_END, > + > + /* > * Evaluate boolean AND expression, one step per subexpression. > FIRST/LAST > * subexpressions are special-cased for performance. Since AND always > has > * at least two subexpressions, FIRST and LAST never apply to the same > @@ -298,6 +308,13 @@ typedef struct ExprEvalStep > int nargs; /* number of arguments > */ > } func; > > + /* for EEOP_CACHEDEXPR_* */ > + struct > + { > + /* steps for evaluation the same CachedExpr have the > same state */ > + struct CachedExprState *state; > + } cachedexpr; > + > /* for EEOP_BOOL_*_STEP */ > struct > { > @@ -600,6 +617,26 @@ typedef struct ArrayRefState > } ArrayRefState; > > > +/* > + * Non-inline data for EEOP_CACHEDEXPR_* operations (steps for evaluation the > + * same CachedExpr have the same state). > + */ > +typedef struct CachedExprState > +{ > + bool isExecuted; > + bool resnull; > + Datum resvalue; > + Oid restypid; /* for copying resvalue of > subexpression */ > + int jumpdone; /* jump here if result > determined */ > + > + /* > + * For switching per-query memory context. It is necessary to save the > + * subexpression result between all tuples if its value datum is a > pointer. > + */ > + MemoryContext oldContext; > +} CachedExprState; > + > + > extern void ExecReadyInterpretedExpr(ExprState *state); > > extern ExprEvalOp ExecEvalStepOp(ExprState *state, ExprEvalStep *op); > diff --git a/src/include/optimizer/planner.h b/src/include/optimizer/planner.h > index f3aaa23..bbadcdd 100644 > --- a/src/include/optimizer/planner.h > +++ b/src/include/optimizer/planner.h > @@ -59,4 +59,7 @@ extern bool plan_cluster_use_sort(Oid tableOid, Oid > indexOid); > > extern List *get_partitioned_child_rels(PlannerInfo *root, Index rti); > > +extern PathTarget *replace_pathtarget_cached_expressions(PathTarget *target); > +extern List *replace_qual_cached_expressions(List *quals); > + > #endif /* PLANNER_H */ > diff --git a/src/include/optimizer/tlist.h b/src/include/optimizer/tlist.h > index ccb93d8..7488bd2 100644 > --- a/src/include/optimizer/tlist.h > +++ b/src/include/optimizer/tlist.h > @@ -65,8 +65,12 @@ extern void split_pathtarget_at_srfs(PlannerInfo *root, > PathTarget *target, PathTarget > *input_target, > List **targets, List > **targets_contain_srfs); > > -/* Convenience macro to get a PathTarget with valid cost/width fields */ > +/* > + * Convenience macro to get a PathTarget with valid cost/width fields and > + * cached expressions. > + */ > #define create_pathtarget(root, tlist) \ > - set_pathtarget_cost_width(root, make_pathtarget_from_tlist(tlist)) > + set_pathtarget_cost_width(root, replace_pathtarget_cached_expressions( \ > + make_pathtarget_from_tlist(tlist))) > > #endif /* TLIST_H */ > diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c > index 7a40c99..2e27052 100644 > --- a/src/pl/plpgsql/src/pl_exec.c > +++ b/src/pl/plpgsql/src/pl_exec.c > @@ -6535,6 +6535,16 @@ exec_simple_check_node(Node *node) > return TRUE; > } > > + case T_CachedExpr: > + { > + /* > + * If CachedExpr will not be initialized by > ExecInitCachedExpr > + * possibly it will use cached value when it > shouldn't (for > + * example, snapshot has changed), so return > false. > + */ > + return FALSE; > + } > + > case T_ScalarArrayOpExpr: > { > ScalarArrayOpExpr *expr = (ScalarArrayOpExpr *) > node; > diff --git a/src/test/regress/expected/precalculate_stable_functions.out > b/src/test/regress/expected/precalculate_stable_functions.out > new file mode 100644 > index 0000000..093e6f8 > --- /dev/null > +++ b/src/test/regress/expected/precalculate_stable_functions.out > @@ -0,0 +1,2625 @@ > +-- > +-- PRECALCULATE STABLE FUNCTIONS > +-- > +-- Create types and tables for testing > +CREATE TYPE my_integer AS (value integer); > +CREATE TABLE two (i integer); > +INSERT INTO two VALUES (1), (2); > +-- Create volatile functions for testing > +CREATE OR REPLACE FUNCTION public.x_vlt ( > +) > +RETURNS integer VOLATILE AS > +$body$ > +BEGIN > + RAISE NOTICE 'v'; > + RETURN 1; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > +CREATE OR REPLACE FUNCTION public.equal_integers_vlt ( > + integer, > + integer > +) > +RETURNS boolean VOLATILE AS > +$body$ > +BEGIN > + RAISE NOTICE 'equal integers volatile'; > + RETURN $1 = $2; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > +CREATE OR REPLACE FUNCTION public.x_vlt_my_integer ( > +) > +RETURNS my_integer VOLATILE AS > +$body$ > +BEGIN > + RAISE NOTICE 'v my_integer'; > + RETURN '(1)'::my_integer; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > +CREATE OR REPLACE FUNCTION public.equal_my_integer_vlt ( > + my_integer, > + my_integer > +) > +RETURNS boolean VOLATILE AS > +$body$ > +BEGIN > + RAISE NOTICE 'equal my_integer volatile'; > + RETURN $1.value = $2.value; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > +CREATE OR REPLACE FUNCTION public.x_vlt_array_int ( > +) > +RETURNS int[] VOLATILE AS > +$body$ > +BEGIN > + RAISE NOTICE 'v array_int'; > + RETURN '{2, 3}'::int[]; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > +-- Create stable functions for testing > +CREATE OR REPLACE FUNCTION public.x_stl ( > +) > +RETURNS integer STABLE AS > +$body$ > +BEGIN > + RAISE NOTICE 's'; > + RETURN 1; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > +CREATE OR REPLACE FUNCTION public.x_stl2 ( > + integer > +) > +RETURNS integer STABLE AS > +$body$ > +BEGIN > + RAISE NOTICE 's2'; > + RETURN $1; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > +CREATE OR REPLACE FUNCTION public.x_stl2_strict ( > + integer > +) > +RETURNS integer STABLE STRICT AS > +$body$ > +BEGIN > + RAISE NOTICE 's2 strict'; > + RETURN $1; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > +CREATE OR REPLACE FUNCTION public.equal_integers_stl ( > + integer, > + integer > +) > +RETURNS boolean STABLE AS > +$body$ > +BEGIN > + RAISE NOTICE 'equal integers stable'; > + RETURN $1 = $2; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > +CREATE OR REPLACE FUNCTION public.x_stl2_boolean ( > + boolean > +) > +RETURNS boolean STABLE AS > +$body$ > +BEGIN > + RAISE NOTICE 's2 boolean'; > + RETURN $1; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > +CREATE OR REPLACE FUNCTION public.equal_booleans_stl_strict ( > + boolean, > + boolean > +) > +RETURNS boolean STABLE STRICT AS > +$body$ > +BEGIN > + RAISE NOTICE 'equal booleans stable strict'; > + RETURN $1 = $2; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > +CREATE OR REPLACE FUNCTION public.x_stl_my_integer ( > +) > +RETURNS my_integer STABLE AS > +$body$ > +BEGIN > + RAISE NOTICE 's my_integer'; > + RETURN '(1)'::my_integer; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > +CREATE OR REPLACE FUNCTION public.equal_my_integer_stl ( > + my_integer, > + my_integer > +) > +RETURNS boolean STABLE AS > +$body$ > +BEGIN > + RAISE NOTICE 'equal my_integer stable'; > + RETURN $1.value = $2.value; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > +CREATE OR REPLACE FUNCTION public.x_stl_array_int ( > +) > +RETURNS int[] STABLE AS > +$body$ > +BEGIN > + RAISE NOTICE 's array_int'; > + RETURN '{2, 3}'::int[]; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > +CREATE OR REPLACE FUNCTION public.stable_max( > +) > +RETURNS integer STABLE AS > +$body$ > +BEGIN > + RETURN (SELECT max(i) from two); > +END > +$body$ > +LANGUAGE 'plpgsql'; > +CREATE OR REPLACE FUNCTION public.simple( > +) > +RETURNS integer STABLE AS > +$body$ > +BEGIN > + RETURN stable_max(); > +END > +$body$ > +LANGUAGE 'plpgsql'; > +-- Create immutable functions for testing > +CREATE OR REPLACE FUNCTION public.x_imm2 ( > + integer > +) > +RETURNS integer IMMUTABLE AS > +$body$ > +BEGIN > + RAISE NOTICE 'i2'; > + RETURN $1; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > +CREATE OR REPLACE FUNCTION public.x_imm2_strict ( > + integer > +) > +RETURNS integer IMMUTABLE STRICT AS > +$body$ > +BEGIN > + RAISE NOTICE 'i2 strict'; > + RETURN $1; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > +CREATE OR REPLACE FUNCTION public.equal_integers_imm ( > + integer, > + integer > +) > +RETURNS boolean IMMUTABLE AS > +$body$ > +BEGIN > + RAISE NOTICE 'equal integers immutable'; > + RETURN $1 = $2; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > +CREATE OR REPLACE FUNCTION public.equal_my_integer_imm ( > + my_integer, > + my_integer > +) > +RETURNS boolean IMMUTABLE AS > +$body$ > +BEGIN > + RAISE NOTICE 'equal my_integer immutable'; > + RETURN $1.value = $2.value; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > +-- Create operators for testing > +CREATE operator === ( > + PROCEDURE = equal_integers_vlt, > + LEFTARG = integer, > + RIGHTARG = integer > +); > +CREATE operator ==== ( > + PROCEDURE = equal_integers_stl, > + LEFTARG = integer, > + RIGHTARG = integer > +); > +CREATE operator ===== ( > + PROCEDURE = equal_integers_imm, > + LEFTARG = integer, > + RIGHTARG = integer > +); > +CREATE operator ====== ( > + PROCEDURE = equal_booleans_stl_strict, > + LEFTARG = boolean, > + RIGHTARG = boolean > +); > +CREATE operator ==== ( > + PROCEDURE = equal_my_integer_stl, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > +-- Simple functions testing > +SELECT x_vlt() FROM generate_series(1, 3) x; -- should not be precalculated > +NOTICE: v > +NOTICE: v > +NOTICE: v > + x_vlt > +------- > + 1 > + 1 > + 1 > +(3 rows) > + > +SELECT x_stl() FROM generate_series(1, 3) x; > +NOTICE: s > + x_stl > +------- > + 1 > + 1 > + 1 > +(3 rows) > + > +-- WHERE clause testing > +SELECT x_vlt() FROM generate_series(1, 4) x WHERE x_vlt() < x; -- should not > be precalculated > +NOTICE: v > +NOTICE: v > +NOTICE: v > +NOTICE: v > +NOTICE: v > +NOTICE: v > +NOTICE: v > + x_vlt > +------- > + 1 > + 1 > + 1 > +(3 rows) > + > +SELECT x_stl() FROM generate_series(1, 4) x WHERE x_stl() < x; > +NOTICE: s > +NOTICE: s > +NOTICE: s > + x_stl > +------- > + 1 > + 1 > + 1 > +(3 rows) > + > +-- Functions with constant arguments and nested functions testing > +SELECT x_stl2(x_vlt()) FROM generate_series(1, 4) x; -- should not be > precalculated > +NOTICE: v > +NOTICE: s2 > +NOTICE: v > +NOTICE: s2 > +NOTICE: v > +NOTICE: s2 > +NOTICE: v > +NOTICE: s2 > + x_stl2 > +-------- > + 1 > + 1 > + 1 > + 1 > +(4 rows) > + > +SELECT x_imm2(x_vlt()) FROM generate_series(1, 4) x; -- should not be > precalculated > +NOTICE: v > +NOTICE: i2 > +NOTICE: v > +NOTICE: i2 > +NOTICE: v > +NOTICE: i2 > +NOTICE: v > +NOTICE: i2 > + x_imm2 > +-------- > + 1 > + 1 > + 1 > + 1 > +(4 rows) > + > +SELECT x_stl2(x_stl2(1)) FROM generate_series(1, 4) x; > +NOTICE: s2 > +NOTICE: s2 > + x_stl2 > +-------- > + 1 > + 1 > + 1 > + 1 > +(4 rows) > + > +SELECT x_imm2(x_stl2(1)) FROM generate_series(1, 4) x; > +NOTICE: s2 > +NOTICE: i2 > + x_imm2 > +-------- > + 1 > + 1 > + 1 > + 1 > +(4 rows) > + > +-- Strict functions testing > +SELECT x_stl2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be > precalculated > +NOTICE: v > +NOTICE: s2 strict > +NOTICE: v > +NOTICE: s2 strict > +NOTICE: v > +NOTICE: s2 strict > +NOTICE: v > +NOTICE: s2 strict > + x_stl2_strict > +--------------- > + 1 > + 1 > + 1 > + 1 > +(4 rows) > + > +SELECT x_imm2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be > precalculated > +NOTICE: v > +NOTICE: i2 strict > +NOTICE: v > +NOTICE: i2 strict > +NOTICE: v > +NOTICE: i2 strict > +NOTICE: v > +NOTICE: i2 strict > + x_imm2_strict > +--------------- > + 1 > + 1 > + 1 > + 1 > +(4 rows) > + > +SELECT x_stl2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x; > +NOTICE: s2 strict > +NOTICE: s2 strict > + x_stl2_strict > +--------------- > + 1 > + 1 > + 1 > + 1 > +(4 rows) > + > +SELECT x_imm2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x; > +NOTICE: s2 strict > +NOTICE: i2 strict > + x_imm2_strict > +--------------- > + 1 > + 1 > + 1 > + 1 > +(4 rows) > + > +-- Strict functions with null arguments testing > +SELECT x_stl2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x; > +NOTICE: s2 > + x_stl2_strict > +--------------- > + > + > + > + > +(4 rows) > + > +SELECT x_imm2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x; > +NOTICE: s2 > + x_imm2_strict > +--------------- > + > + > + > + > +(4 rows) > + > +-- Operators testing > +SELECT 1 === 2 FROM generate_series(1, 4) x; -- should not be precalculated > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > + ?column? > +---------- > + f > + f > + f > + f > +(4 rows) > + > +SELECT 1 ==== 2 FROM generate_series(1, 4) x; > +NOTICE: equal integers stable > + ?column? > +---------- > + f > + f > + f > + f > +(4 rows) > + > +-- Nested and strict operators testing > +-- (also partly mixed functions and operators testing) > +SELECT (x_vlt() ==== 2) ====== (x_vlt() ===== 3) FROM generate_series(1, 4) > x; -- should not be precalculated > +NOTICE: v > +NOTICE: equal integers stable > +NOTICE: v > +NOTICE: equal integers immutable > +NOTICE: equal booleans stable strict > +NOTICE: v > +NOTICE: equal integers stable > +NOTICE: v > +NOTICE: equal integers immutable > +NOTICE: equal booleans stable strict > +NOTICE: v > +NOTICE: equal integers stable > +NOTICE: v > +NOTICE: equal integers immutable > +NOTICE: equal booleans stable strict > +NOTICE: v > +NOTICE: equal integers stable > +NOTICE: v > +NOTICE: equal integers immutable > +NOTICE: equal booleans stable strict > + ?column? > +---------- > + t > + t > + t > + t > +(4 rows) > + > +SELECT (x_stl() ==== 2) ====== (x_stl() ===== 3) FROM generate_series(1, 4) > x; > +NOTICE: s > +NOTICE: equal integers stable > +NOTICE: s > +NOTICE: equal integers immutable > +NOTICE: equal booleans stable strict > + ?column? > +---------- > + t > + t > + t > + t > +(4 rows) > + > +SELECT (1 ==== 2) ====== x_stl2_boolean(NULL) FROM generate_series(1, 4) x; > +NOTICE: equal integers stable > +NOTICE: s2 boolean > + ?column? > +---------- > + > + > + > + > +(4 rows) > + > +-- IS DISTINCT FROM expression testing > +-- create operator here because we will drop and reuse it several times > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_vlt, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > +-- should not be precalculated > +SELECT '(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > + ?column? > +---------- > + t > + t > + t > + t > +(4 rows) > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_stl, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > +SELECT '(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer stable > + ?column? > +---------- > + t > + t > + t > + t > +(4 rows) > + > +-- IS DISTINCT FROM expressions with null arguments testing > +SELECT x_stl2_boolean(1 IS DISTINCT FROM x_stl2(NULL)) > +FROM generate_series(1, 4) x; > +NOTICE: s2 > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + t > + t > + t > + t > +(4 rows) > + > +SELECT x_stl2_boolean(x_stl2(NULL) IS DISTINCT FROM x_stl2(NULL)) > +FROM generate_series(1, 4) x; > +NOTICE: s2 > +NOTICE: s2 > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + f > + f > + f > + f > +(4 rows) > + > +-- Nested IS DISTINCT FROM expression testing > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_vlt, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > +-- should not be precalculated > +SELECT x_stl2_boolean( > + ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) IS DISTINCT FROM > + TRUE > +) > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer volatile > +NOTICE: s2 boolean > +NOTICE: equal my_integer volatile > +NOTICE: s2 boolean > +NOTICE: equal my_integer volatile > +NOTICE: s2 boolean > +NOTICE: equal my_integer volatile > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + f > + f > + f > + f > +(4 rows) > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_stl, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > +SELECT x_stl2_boolean( > + ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) IS DISTINCT FROM > + TRUE > +) > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer stable > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + f > + f > + f > + f > +(4 rows) > + > +-- NULLIF expressions testing > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_vlt, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > +-- should not be precalculated > +SELECT NULLIF('(1)'::my_integer, '(2)'::my_integer) > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > + nullif > +-------- > + (1) > + (1) > + (1) > + (1) > +(4 rows) > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_stl, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > +SELECT NULLIF('(1)'::my_integer, '(2)'::my_integer) > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer stable > + nullif > +-------- > + (1) > + (1) > + (1) > + (1) > +(4 rows) > + > +-- NULLIF expressions with null arguments testing > +SELECT x_stl2(NULLIF(1, x_stl2(NULL))) FROM generate_series(1, 4) x; > +NOTICE: s2 > +NOTICE: s2 > + x_stl2 > +-------- > + 1 > + 1 > + 1 > + 1 > +(4 rows) > + > +SELECT x_stl2(NULLIF(x_stl2(NULL), x_stl2(NULL))) FROM generate_series(1, 4) > x; > +NOTICE: s2 > +NOTICE: s2 > +NOTICE: s2 > + x_stl2 > +-------- > + > + > + > + > +(4 rows) > + > +-- Nested NULLIF expression testing > +-- should not be precalculated > +SELECT NULLIF(NULLIF(x_vlt_my_integer(), '(2)'::my_integer), > '(2)'::my_integer) > +FROM generate_series(1, 4) x; > +NOTICE: v my_integer > +NOTICE: equal my_integer stable > +NOTICE: equal my_integer stable > +NOTICE: v my_integer > +NOTICE: equal my_integer stable > +NOTICE: equal my_integer stable > +NOTICE: v my_integer > +NOTICE: equal my_integer stable > +NOTICE: equal my_integer stable > +NOTICE: v my_integer > +NOTICE: equal my_integer stable > +NOTICE: equal my_integer stable > + nullif > +-------- > + (1) > + (1) > + (1) > + (1) > +(4 rows) > + > +SELECT NULLIF(NULLIF(x_stl_my_integer(), '(2)'::my_integer), > '(2)'::my_integer) > +FROM generate_series(1, 4) x; > +NOTICE: s my_integer > +NOTICE: equal my_integer stable > +NOTICE: equal my_integer stable > + nullif > +-------- > + (1) > + (1) > + (1) > + (1) > +(4 rows) > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_imm, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > +-- should not be precalculated > +SELECT NULLIF(NULLIF(x_vlt_my_integer(), '(2)'::my_integer), > '(2)'::my_integer) > +FROM generate_series(1, 4) x; > +NOTICE: v my_integer > +NOTICE: equal my_integer immutable > +NOTICE: equal my_integer immutable > +NOTICE: v my_integer > +NOTICE: equal my_integer immutable > +NOTICE: equal my_integer immutable > +NOTICE: v my_integer > +NOTICE: equal my_integer immutable > +NOTICE: equal my_integer immutable > +NOTICE: v my_integer > +NOTICE: equal my_integer immutable > +NOTICE: equal my_integer immutable > + nullif > +-------- > + (1) > + (1) > + (1) > + (1) > +(4 rows) > + > +SELECT NULLIF(NULLIF(x_stl_my_integer(), '(2)'::my_integer), > '(2)'::my_integer) > +FROM generate_series(1, 4) x; > +NOTICE: s my_integer > +NOTICE: equal my_integer immutable > +NOTICE: equal my_integer immutable > + nullif > +-------- > + (1) > + (1) > + (1) > + (1) > +(4 rows) > + > +-- "scalar op ANY/ALL (array)" / "scalar IN (2 or more values)" expressions > +-- testing > +SELECT 1 === ANY('{2, 3}') FROM generate_series(1, 4) x; -- should not be > precalculated > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > + ?column? > +---------- > + f > + f > + f > + f > +(4 rows) > + > +SELECT 1 === ALL('{2, 3}') FROM generate_series(1, 4) x; -- should not be > precalculated > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > + ?column? > +---------- > + f > + f > + f > + f > +(4 rows) > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_vlt, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > +-- should not be precalculated > +SELECT '(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer) > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > + ?column? > +---------- > + f > + f > + f > + f > +(4 rows) > + > +SELECT 1 ==== ANY('{2, 3}') FROM generate_series(1, 4) x; > +NOTICE: equal integers stable > +NOTICE: equal integers stable > + ?column? > +---------- > + f > + f > + f > + f > +(4 rows) > + > +SELECT 1 ==== ALL('{2, 3}') FROM generate_series(1, 4) x; > +NOTICE: equal integers stable > + ?column? > +---------- > + f > + f > + f > + f > +(4 rows) > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_stl, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > +SELECT '(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer) > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer stable > +NOTICE: equal my_integer stable > + ?column? > +---------- > + f > + f > + f > + f > +(4 rows) > + > +-- "scalar op ANY/ALL (array)" / "scalar IN (2 or more values)" expressions > with > +-- null arguments testing > +SELECT 1 ==== ANY('{2, NULL}') FROM generate_series(1, 4) x; > +NOTICE: equal integers stable > +NOTICE: equal integers stable > + ?column? > +---------- > + > + > + > + > +(4 rows) > + > +SELECT x_stl2_boolean(1 ==== ANY(NULL)) FROM generate_series(1, 4) x; > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + > + > + > + > +(4 rows) > + > +SELECT NULL ==== ANY('{2, 3}'::int[]) FROM generate_series(1, 4) x; > +NOTICE: equal integers stable > +NOTICE: equal integers stable > + ?column? > +---------- > + > + > + > + > +(4 rows) > + > +SELECT NULL ==== ANY('{2, NULL}'::int[]) FROM generate_series(1, 4) x; > +NOTICE: equal integers stable > +NOTICE: equal integers stable > + ?column? > +---------- > + > + > + > + > +(4 rows) > + > +SELECT x_stl2_boolean(NULL::int ==== ANY(NULL)) FROM generate_series(1, 4) x; > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + > + > + > + > +(4 rows) > + > +SELECT 1 ==== ALL('{2, NULL}') FROM generate_series(1, 4) x; > +NOTICE: equal integers stable > + ?column? > +---------- > + f > + f > + f > + f > +(4 rows) > + > +SELECT x_stl2_boolean(1 ==== ALL(NULL)) FROM generate_series(1, 4) x; > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + > + > + > + > +(4 rows) > + > +SELECT NULL ==== ALL('{2, 3}'::int[]) FROM generate_series(1, 4) x; > +NOTICE: equal integers stable > +NOTICE: equal integers stable > + ?column? > +---------- > + > + > + > + > +(4 rows) > + > +SELECT NULL ==== ALL('{2, NULL}'::int[]) FROM generate_series(1, 4) x; > +NOTICE: equal integers stable > +NOTICE: equal integers stable > + ?column? > +---------- > + > + > + > + > +(4 rows) > + > +SELECT x_stl2_boolean(NULL::int ==== ALL(NULL)) FROM generate_series(1, 4) x; > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + > + > + > + > +(4 rows) > + > +SELECT x_stl2_boolean(1 IN (2, NULL)) FROM generate_series(1, 4) x; > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + > + > + > + > +(4 rows) > + > +SELECT x_stl2_boolean(NULL IN (2, 3)) FROM generate_series(1, 4) x; > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + > + > + > + > +(4 rows) > + > +SELECT x_stl2_boolean(NULL IN (2, NULL)) FROM generate_series(1, 4) x; > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + > + > + > + > +(4 rows) > + > +-- Nesting "scalar op ANY/ALL (array)" / "scalar IN (2 or more values)" > +-- expressions testing (also partly mixed functions and "scalar op ANY/ALL > +-- (array)" / "scalar IN (2 or more values)" expressions testing) > +-- should not be precalculated > +SELECT x_stl2_boolean((x_vlt() ==== ANY('{2, 3}')) = ANY('{TRUE}')) > +FROM generate_series(1, 4) x; > +NOTICE: v > +NOTICE: equal integers stable > +NOTICE: equal integers stable > +NOTICE: s2 boolean > +NOTICE: v > +NOTICE: equal integers stable > +NOTICE: equal integers stable > +NOTICE: s2 boolean > +NOTICE: v > +NOTICE: equal integers stable > +NOTICE: equal integers stable > +NOTICE: s2 boolean > +NOTICE: v > +NOTICE: equal integers stable > +NOTICE: equal integers stable > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + f > + f > + f > + f > +(4 rows) > + > +-- should not be precalculated > +SELECT x_stl2_boolean((x_vlt() ==== ANY('{2, 3}')) = ALL('{TRUE}')) > +FROM generate_series(1, 4) x; > +NOTICE: v > +NOTICE: equal integers stable > +NOTICE: equal integers stable > +NOTICE: s2 boolean > +NOTICE: v > +NOTICE: equal integers stable > +NOTICE: equal integers stable > +NOTICE: s2 boolean > +NOTICE: v > +NOTICE: equal integers stable > +NOTICE: equal integers stable > +NOTICE: s2 boolean > +NOTICE: v > +NOTICE: equal integers stable > +NOTICE: equal integers stable > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + f > + f > + f > + f > +(4 rows) > + > +-- should not be precalculated > +SELECT x_stl2_boolean((x_vlt() ==== ANY('{2, 3}')) IN (TRUE, FALSE)) > +FROM generate_series(1, 4) x; > +NOTICE: v > +NOTICE: equal integers stable > +NOTICE: equal integers stable > +NOTICE: s2 boolean > +NOTICE: v > +NOTICE: equal integers stable > +NOTICE: equal integers stable > +NOTICE: s2 boolean > +NOTICE: v > +NOTICE: equal integers stable > +NOTICE: equal integers stable > +NOTICE: s2 boolean > +NOTICE: v > +NOTICE: equal integers stable > +NOTICE: equal integers stable > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + t > + t > + t > + t > +(4 rows) > + > +SELECT x_stl2_boolean((x_stl() ==== ANY('{2, 3}')) = ANY('{TRUE}')) > +FROM generate_series(1, 4) x; > +NOTICE: s > +NOTICE: equal integers stable > +NOTICE: equal integers stable > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + f > + f > + f > + f > +(4 rows) > + > +SELECT x_stl2_boolean((x_stl() ==== ANY('{2, 3}')) = ALL('{TRUE}')) > +FROM generate_series(1, 4) x; > +NOTICE: s > +NOTICE: equal integers stable > +NOTICE: equal integers stable > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + f > + f > + f > + f > +(4 rows) > + > +SELECT x_stl2_boolean((x_stl() ==== ANY('{2, 3}')) IN (TRUE, FALSE)) > +FROM generate_series(1, 4) x; > +NOTICE: s > +NOTICE: equal integers stable > +NOTICE: equal integers stable > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + t > + t > + t > + t > +(4 rows) > + > +-- should not be precalculated > +SELECT x_stl2_boolean((x_vlt() ===== ANY('{2, 3}')) = ANY('{TRUE}')) > +FROM generate_series(1, 4) x; > +NOTICE: v > +NOTICE: equal integers immutable > +NOTICE: equal integers immutable > +NOTICE: s2 boolean > +NOTICE: v > +NOTICE: equal integers immutable > +NOTICE: equal integers immutable > +NOTICE: s2 boolean > +NOTICE: v > +NOTICE: equal integers immutable > +NOTICE: equal integers immutable > +NOTICE: s2 boolean > +NOTICE: v > +NOTICE: equal integers immutable > +NOTICE: equal integers immutable > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + f > + f > + f > + f > +(4 rows) > + > +-- should not be precalculated > +SELECT x_stl2_boolean((x_vlt() ===== ANY('{2, 3}')) = ALL('{TRUE}')) > +FROM generate_series(1, 4) x; > +NOTICE: v > +NOTICE: equal integers immutable > +NOTICE: equal integers immutable > +NOTICE: s2 boolean > +NOTICE: v > +NOTICE: equal integers immutable > +NOTICE: equal integers immutable > +NOTICE: s2 boolean > +NOTICE: v > +NOTICE: equal integers immutable > +NOTICE: equal integers immutable > +NOTICE: s2 boolean > +NOTICE: v > +NOTICE: equal integers immutable > +NOTICE: equal integers immutable > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + f > + f > + f > + f > +(4 rows) > + > +-- should not be precalculated > +SELECT x_stl2_boolean((x_vlt() ===== ANY('{2, 3}')) IN (TRUE, FALSE)) > +FROM generate_series(1, 4) x; > +NOTICE: v > +NOTICE: equal integers immutable > +NOTICE: equal integers immutable > +NOTICE: s2 boolean > +NOTICE: v > +NOTICE: equal integers immutable > +NOTICE: equal integers immutable > +NOTICE: s2 boolean > +NOTICE: v > +NOTICE: equal integers immutable > +NOTICE: equal integers immutable > +NOTICE: s2 boolean > +NOTICE: v > +NOTICE: equal integers immutable > +NOTICE: equal integers immutable > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + t > + t > + t > + t > +(4 rows) > + > +SELECT x_stl2_boolean((x_stl() ===== ANY('{2, 3}')) = ANY('{TRUE}')) > +FROM generate_series(1, 4) x; > +NOTICE: s > +NOTICE: equal integers immutable > +NOTICE: equal integers immutable > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + f > + f > + f > + f > +(4 rows) > + > +SELECT x_stl2_boolean((x_stl() ===== ANY('{2, 3}')) = ALL('{TRUE}')) > +FROM generate_series(1, 4) x; > +NOTICE: s > +NOTICE: equal integers immutable > +NOTICE: equal integers immutable > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + f > + f > + f > + f > +(4 rows) > + > +SELECT x_stl2_boolean((x_stl() ===== ANY('{2, 3}')) IN (TRUE, FALSE)) > +FROM generate_series(1, 4) x; > +NOTICE: s > +NOTICE: equal integers immutable > +NOTICE: equal integers immutable > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + t > + t > + t > + t > +(4 rows) > + > +-- Mixed functions and operators testing > +-- (most of it was earlier in Nested and strict operators testing) > +SELECT x_stl2_boolean(1 ==== 2) FROM generate_series(1, 4) x; > +NOTICE: equal integers stable > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + f > + f > + f > + f > +(4 rows) > + > +-- Mixed functions and IS DISTINCT FROM expressions testing > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_vlt, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > +-- should not be precalculated > +SELECT equal_booleans_stl_strict( > + (x_stl_my_integer() IS DISTINCT FROM '(1)'::my_integer), > + (x_stl_my_integer() IS DISTINCT FROM '(2)'::my_integer) > +) > +FROM generate_series(1, 4) x; > +NOTICE: s my_integer > +NOTICE: equal my_integer volatile > +NOTICE: s my_integer > +NOTICE: equal my_integer volatile > +NOTICE: equal booleans stable strict > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal booleans stable strict > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal booleans stable strict > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal booleans stable strict > + equal_booleans_stl_strict > +--------------------------- > + f > + f > + f > + f > +(4 rows) > + > +SELECT equal_booleans_stl_strict( > + (x_stl() IS DISTINCT FROM 1), > + (x_stl() IS DISTINCT FROM 2) > +) > +FROM generate_series(1, 4) x; > +NOTICE: s > +NOTICE: s > +NOTICE: equal booleans stable strict > + equal_booleans_stl_strict > +--------------------------- > + f > + f > + f > + f > +(4 rows) > + > +-- Mixed functions and NULLIF expressions testing > +-- should not be precalculated > +SELECT equal_my_integer_stl( > + NULLIF(x_stl_my_integer(), '(1)'::my_integer), > + NULLIF(x_stl_my_integer(), '(2)'::my_integer) > +) > +FROM generate_series(1, 4) x; > +NOTICE: s my_integer > +NOTICE: equal my_integer volatile > +NOTICE: s my_integer > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer stable > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer stable > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer stable > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer stable > + equal_my_integer_stl > +---------------------- > + > + > + > + > +(4 rows) > + > +SELECT equal_integers_stl(NULLIF(x_stl(), 1), NULLIF(x_stl(), 2)) > +FROM generate_series(1, 4) x; > +NOTICE: s > +NOTICE: s > +NOTICE: equal integers stable > + equal_integers_stl > +-------------------- > + > + > + > + > +(4 rows) > + > +-- Mixed functions and "scalar op ANY/ALL (array)" / "scalar IN (2 or more > +-- values)" expressions testing (partly in nesting "scalar op ANY/ALL > (array)" / > +-- "scalar IN (2 or more values)" expressions testing) > +SELECT 1 ==== ANY(x_vlt_array_int()) FROM generate_series(1, 4) x; -- should > not be precalculated > +NOTICE: v array_int > +NOTICE: equal integers stable > +NOTICE: equal integers stable > +NOTICE: v array_int > +NOTICE: equal integers stable > +NOTICE: equal integers stable > +NOTICE: v array_int > +NOTICE: equal integers stable > +NOTICE: equal integers stable > +NOTICE: v array_int > +NOTICE: equal integers stable > +NOTICE: equal integers stable > + ?column? > +---------- > + f > + f > + f > + f > +(4 rows) > + > +SELECT 1 ==== ALL(x_vlt_array_int()) FROM generate_series(1, 4) x; -- should > not be precalculated > +NOTICE: v array_int > +NOTICE: equal integers stable > +NOTICE: v array_int > +NOTICE: equal integers stable > +NOTICE: v array_int > +NOTICE: equal integers stable > +NOTICE: v array_int > +NOTICE: equal integers stable > + ?column? > +---------- > + f > + f > + f > + f > +(4 rows) > + > +SELECT 1 ==== ANY(x_stl_array_int()) FROM generate_series(1, 4) x; > +NOTICE: s array_int > +NOTICE: equal integers stable > +NOTICE: equal integers stable > + ?column? > +---------- > + f > + f > + f > + f > +(4 rows) > + > +SELECT 1 ==== ALL(x_stl_array_int()) FROM generate_series(1, 4) x; > +NOTICE: s array_int > +NOTICE: equal integers stable > + ?column? > +---------- > + f > + f > + f > + f > +(4 rows) > + > +-- Mixed operators and IS DISTINCT FROM expressions testing > +-- should not be precalculated > +SELECT ( > + ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) ====== > + ('(2)'::my_integer IS DISTINCT FROM '(2)'::my_integer) > +) > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal booleans stable strict > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal booleans stable strict > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal booleans stable strict > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal booleans stable strict > + ?column? > +---------- > + f > + f > + f > + f > +(4 rows) > + > +-- should not be precalculated > +SELECT x_stl2_boolean((1 === 2) IS DISTINCT FROM TRUE) > +FROM generate_series(1, 4) x; > +NOTICE: equal integers volatile > +NOTICE: s2 boolean > +NOTICE: equal integers volatile > +NOTICE: s2 boolean > +NOTICE: equal integers volatile > +NOTICE: s2 boolean > +NOTICE: equal integers volatile > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + t > + t > + t > + t > +(4 rows) > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_stl, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > +SELECT ( > + ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) ====== > + ('(2)'::my_integer IS DISTINCT FROM '(2)'::my_integer) > +) > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer stable > +NOTICE: equal my_integer stable > +NOTICE: equal booleans stable strict > + ?column? > +---------- > + f > + f > + f > + f > +(4 rows) > + > +SELECT x_stl2_boolean((1 ==== 2) IS DISTINCT FROM TRUE) > +FROM generate_series(1, 4) x; > +NOTICE: equal integers stable > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + t > + t > + t > + t > +(4 rows) > + > +-- Mixed operators and NULLIF expressions testing > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_vlt, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > +-- should not be precalculated > +SELECT ( > + NULLIF('(1)'::my_integer, '(2)'::my_integer) ==== > + NULLIF('(2)'::my_integer, '(2)'::my_integer) > +) > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer stable > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer stable > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer stable > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer stable > + ?column? > +---------- > + > + > + > + > +(4 rows) > + > +-- should not be precalculated > +SELECT x_stl2_boolean(NULLIF(1 === 2, TRUE)) FROM generate_series(1, 4) x; > +NOTICE: equal integers volatile > +NOTICE: s2 boolean > +NOTICE: equal integers volatile > +NOTICE: s2 boolean > +NOTICE: equal integers volatile > +NOTICE: s2 boolean > +NOTICE: equal integers volatile > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + f > + f > + f > + f > +(4 rows) > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_stl, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > +SELECT ( > + NULLIF('(1)'::my_integer, '(2)'::my_integer) ==== > + NULLIF('(2)'::my_integer, '(2)'::my_integer) > +) > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer stable > +NOTICE: equal my_integer stable > +NOTICE: equal my_integer stable > + ?column? > +---------- > + > + > + > + > +(4 rows) > + > +SELECT x_stl2_boolean(NULLIF(1 ==== 2, TRUE)) FROM generate_series(1, 4) x; > +NOTICE: equal integers stable > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + f > + f > + f > + f > +(4 rows) > + > +-- Mixed operators and "scalar op ANY/ALL (array)" / "scalar IN (2 or more > +-- values)" expressions testing > +-- should not be precalculated > +SELECT (1 === ANY('{2, 3}')) ====== (1 === ALL('{2, 3}')) > +FROM generate_series(1, 4) x; > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: equal booleans stable strict > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: equal booleans stable strict > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: equal booleans stable strict > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: equal booleans stable strict > + ?column? > +---------- > + t > + t > + t > + t > +(4 rows) > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_vlt, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > +-- should not be precalculated > +SELECT ('(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)) ====== > TRUE > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal booleans stable strict > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal booleans stable strict > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal booleans stable strict > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal booleans stable strict > + ?column? > +---------- > + f > + f > + f > + f > +(4 rows) > + > +-- should not be precalculated > +SELECT x_stl2_boolean((1 === 2) = ANY('{TRUE}')) > +FROM generate_series(1, 4) x; > +NOTICE: equal integers volatile > +NOTICE: s2 boolean > +NOTICE: equal integers volatile > +NOTICE: s2 boolean > +NOTICE: equal integers volatile > +NOTICE: s2 boolean > +NOTICE: equal integers volatile > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + f > + f > + f > + f > +(4 rows) > + > +-- should not be precalculated > +SELECT x_stl2_boolean((1 === 2) = ALL('{TRUE}')) > +FROM generate_series(1, 4) x; > +NOTICE: equal integers volatile > +NOTICE: s2 boolean > +NOTICE: equal integers volatile > +NOTICE: s2 boolean > +NOTICE: equal integers volatile > +NOTICE: s2 boolean > +NOTICE: equal integers volatile > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + f > + f > + f > + f > +(4 rows) > + > +-- should not be precalculated > +SELECT x_stl2_boolean((1 === 2) IN (TRUE, FALSE)) > +FROM generate_series(1, 4) x; > +NOTICE: equal integers volatile > +NOTICE: s2 boolean > +NOTICE: equal integers volatile > +NOTICE: s2 boolean > +NOTICE: equal integers volatile > +NOTICE: s2 boolean > +NOTICE: equal integers volatile > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + t > + t > + t > + t > +(4 rows) > + > +SELECT (1 ==== ANY('{2, 3}')) ====== (1 ==== ALL('{2, 3}')) > +FROM generate_series(1, 4) x; > +NOTICE: equal integers stable > +NOTICE: equal integers stable > +NOTICE: equal integers stable > +NOTICE: equal booleans stable strict > + ?column? > +---------- > + t > + t > + t > + t > +(4 rows) > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_stl, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > +SELECT ('(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)) ====== > TRUE > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer stable > +NOTICE: equal my_integer stable > +NOTICE: equal booleans stable strict > + ?column? > +---------- > + f > + f > + f > + f > +(4 rows) > + > +SELECT x_stl2_boolean((1 ==== 2) = ANY('{TRUE}')) > +FROM generate_series(1, 4) x; > +NOTICE: equal integers stable > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + f > + f > + f > + f > +(4 rows) > + > +SELECT x_stl2_boolean((1 ==== 2) = ALL('{TRUE}')) > +FROM generate_series(1, 4) x; > +NOTICE: equal integers stable > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + f > + f > + f > + f > +(4 rows) > + > +SELECT x_stl2_boolean((1 ==== 2) IN (TRUE, FALSE)) > +FROM generate_series(1, 4) x; > +NOTICE: equal integers stable > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + t > + t > + t > + t > +(4 rows) > + > +-- Mixed IS DISTINCT FROM and NULLIF expressions testing > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_vlt, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > +-- should not be precalculated > +SELECT ( > + NULLIF('(1)'::my_integer, '(2)'::my_integer) IS DISTINCT FROM > + NULLIF('(2)'::my_integer, '(2)'::my_integer) > +) > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > + ?column? > +---------- > + t > + t > + t > + t > +(4 rows) > + > +-- should not be precalculated > +SELECT NULLIF( > + ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer), > + ('(2)'::my_integer IS DISTINCT FROM '(2)'::my_integer) > +) > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > + nullif > +-------- > + t > + t > + t > + t > +(4 rows) > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_stl, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > +SELECT ( > + NULLIF('(1)'::my_integer, '(2)'::my_integer) IS DISTINCT FROM > + NULLIF('(2)'::my_integer, '(2)'::my_integer) > +) > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer stable > +NOTICE: equal my_integer stable > + ?column? > +---------- > + t > + t > + t > + t > +(4 rows) > + > +SELECT NULLIF( > + ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer), > + ('(2)'::my_integer IS DISTINCT FROM '(2)'::my_integer) > +) > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer stable > +NOTICE: equal my_integer stable > + nullif > +-------- > + t > + t > + t > + t > +(4 rows) > + > +-- Mixed IS DISTINCT FROM and "scalar op ANY/ALL (array)" / "scalar IN (2 or > +-- more values)" expressions testing > +-- should not be precalculated > +SELECT x_stl2_boolean( > + (1 === ANY('{2, 3}')) IS DISTINCT FROM > + (1 === ALL('{2, 3}')) > +) > +FROM generate_series(1, 4) x; > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: s2 boolean > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: s2 boolean > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: s2 boolean > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + f > + f > + f > + f > +(4 rows) > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_vlt, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > +-- should not be precalculated > +SELECT x_stl2_boolean( > + ('(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)) IS DISTINCT > FROM > + TRUE > +) > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: s2 boolean > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: s2 boolean > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: s2 boolean > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + t > + t > + t > + t > +(4 rows) > + > +-- should not be precalculated > +SELECT x_stl2_boolean( > + ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) = ANY('{TRUE}') > +) > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer volatile > +NOTICE: s2 boolean > +NOTICE: equal my_integer volatile > +NOTICE: s2 boolean > +NOTICE: equal my_integer volatile > +NOTICE: s2 boolean > +NOTICE: equal my_integer volatile > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + t > + t > + t > + t > +(4 rows) > + > +-- should not be precalculated > +SELECT x_stl2_boolean( > + ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) = ALL('{TRUE}') > +) > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer volatile > +NOTICE: s2 boolean > +NOTICE: equal my_integer volatile > +NOTICE: s2 boolean > +NOTICE: equal my_integer volatile > +NOTICE: s2 boolean > +NOTICE: equal my_integer volatile > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + t > + t > + t > + t > +(4 rows) > + > +-- should not be precalculated > +SELECT x_stl2_boolean( > + ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) IN (TRUE, FALSE) > +) > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer volatile > +NOTICE: s2 boolean > +NOTICE: equal my_integer volatile > +NOTICE: s2 boolean > +NOTICE: equal my_integer volatile > +NOTICE: s2 boolean > +NOTICE: equal my_integer volatile > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + t > + t > + t > + t > +(4 rows) > + > +SELECT x_stl2_boolean( > + (1 ==== ANY('{2, 3}')) IS DISTINCT FROM > + (1 ==== ALL('{2, 3}')) > +) > +FROM generate_series(1, 4) x; > +NOTICE: equal integers stable > +NOTICE: equal integers stable > +NOTICE: equal integers stable > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + f > + f > + f > + f > +(4 rows) > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_stl, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > +SELECT x_stl2_boolean( > + ('(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)) IS DISTINCT > FROM > + TRUE > +) > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer stable > +NOTICE: equal my_integer stable > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + t > + t > + t > + t > +(4 rows) > + > +SELECT x_stl2_boolean( > + ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) = ANY('{TRUE}') > +) > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer stable > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + t > + t > + t > + t > +(4 rows) > + > +SELECT x_stl2_boolean( > + ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) = ALL('{TRUE}') > +) > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer stable > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + t > + t > + t > + t > +(4 rows) > + > +SELECT x_stl2_boolean( > + ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) IN (TRUE, FALSE) > +) > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer stable > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + t > + t > + t > + t > +(4 rows) > + > +-- Mixed NULLIF and "scalar op ANY/ALL (array)" / "scalar IN (2 or more > values)" > +-- expressions testing > +-- should not be precalculated > +SELECT x_stl2_boolean(NULLIF(1 === ANY('{2, 3}'), 1 === ALL('{2, 3}'))) > +FROM generate_series(1, 4) x; > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: s2 boolean > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: s2 boolean > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: s2 boolean > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + > + > + > + > +(4 rows) > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_vlt, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > +-- should not be precalculated > +SELECT x_stl2_boolean(NULLIF( > + '(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer), > + TRUE > +)) > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: s2 boolean > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: s2 boolean > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: s2 boolean > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + f > + f > + f > + f > +(4 rows) > + > +-- should not be precalculated > +SELECT ( > + NULLIF('(1)'::my_integer, '(2)'::my_integer) ==== > + ANY('{(3)}'::my_integer[]) > +) > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer stable > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer stable > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer stable > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer stable > + ?column? > +---------- > + f > + f > + f > + f > +(4 rows) > + > +-- should not be precalculated > +SELECT ( > + NULLIF('(1)'::my_integer, '(2)'::my_integer) ==== > + ALL('{(3)}'::my_integer[]) > +) > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer stable > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer stable > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer stable > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer stable > + ?column? > +---------- > + f > + f > + f > + f > +(4 rows) > + > +-- should not be precalculated > +SELECT ( > + NULLIF('(1)'::my_integer, '(2)'::my_integer) IN > + ('(3)'::my_integer, '(2)'::my_integer) > +) > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > +NOTICE: equal my_integer volatile > + ?column? > +---------- > + f > + f > + f > + f > +(4 rows) > + > +SELECT x_stl2_boolean(NULLIF(1 ==== ANY('{2, 3}'), 1 ==== ALL('{2, 3}'))) > +FROM generate_series(1, 4) x; > +NOTICE: equal integers stable > +NOTICE: equal integers stable > +NOTICE: equal integers stable > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + > + > + > + > +(4 rows) > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_stl, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > +SELECT x_stl2_boolean(NULLIF( > + '(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer), > + TRUE > +)) > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer stable > +NOTICE: equal my_integer stable > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + f > + f > + f > + f > +(4 rows) > + > +SELECT ( > + NULLIF('(1)'::my_integer, '(2)'::my_integer) ==== > + ANY('{(3)}'::my_integer[]) > +) > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer stable > +NOTICE: equal my_integer stable > + ?column? > +---------- > + f > + f > + f > + f > +(4 rows) > + > +SELECT ( > + NULLIF('(1)'::my_integer, '(2)'::my_integer) ==== > + ALL('{(3)}'::my_integer[]) > +) > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer stable > +NOTICE: equal my_integer stable > + ?column? > +---------- > + f > + f > + f > + f > +(4 rows) > + > +SELECT ( > + NULLIF('(1)'::my_integer, '(2)'::my_integer) IN > + ('(3)'::my_integer, '(2)'::my_integer) > +) > +FROM generate_series(1, 4) x; > +NOTICE: equal my_integer stable > +NOTICE: equal my_integer stable > +NOTICE: equal my_integer stable > + ?column? > +---------- > + f > + f > + f > + f > +(4 rows) > + > +-- Tracking functions testing > +SET track_functions TO 'all'; > +SELECT x_vlt() FROM generate_series(1, 3) x; -- should not be precalculated > +NOTICE: v > +NOTICE: v > +NOTICE: v > + x_vlt > +------- > + 1 > + 1 > + 1 > +(3 rows) > + > +SELECT x_stl() FROM generate_series(1, 3) x; > +NOTICE: s > + x_stl > +------- > + 1 > + 1 > + 1 > +(3 rows) > + > +SELECT x_vlt() FROM generate_series(1, 4) x WHERE x_vlt() < x; -- should not > be precalculated > +NOTICE: v > +NOTICE: v > +NOTICE: v > +NOTICE: v > +NOTICE: v > +NOTICE: v > +NOTICE: v > + x_vlt > +------- > + 1 > + 1 > + 1 > +(3 rows) > + > +SELECT x_stl() FROM generate_series(1, 4) x WHERE x_stl() < x; > +NOTICE: s > +NOTICE: s > +NOTICE: s > + x_stl > +------- > + 1 > + 1 > + 1 > +(3 rows) > + > +SELECT x_stl2(x_vlt()) FROM generate_series(1, 4) x; -- should not be > precalculated > +NOTICE: v > +NOTICE: s2 > +NOTICE: v > +NOTICE: s2 > +NOTICE: v > +NOTICE: s2 > +NOTICE: v > +NOTICE: s2 > + x_stl2 > +-------- > + 1 > + 1 > + 1 > + 1 > +(4 rows) > + > +SELECT x_imm2(x_vlt()) FROM generate_series(1, 4) x; -- should not be > precalculated > +NOTICE: v > +NOTICE: i2 > +NOTICE: v > +NOTICE: i2 > +NOTICE: v > +NOTICE: i2 > +NOTICE: v > +NOTICE: i2 > + x_imm2 > +-------- > + 1 > + 1 > + 1 > + 1 > +(4 rows) > + > +SELECT x_stl2(x_stl2(1)) FROM generate_series(1, 4) x; > +NOTICE: s2 > +NOTICE: s2 > + x_stl2 > +-------- > + 1 > + 1 > + 1 > + 1 > +(4 rows) > + > +SELECT x_imm2(x_stl2(1)) FROM generate_series(1, 4) x; > +NOTICE: s2 > +NOTICE: i2 > + x_imm2 > +-------- > + 1 > + 1 > + 1 > + 1 > +(4 rows) > + > +SELECT x_stl2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be > precalculated > +NOTICE: v > +NOTICE: s2 strict > +NOTICE: v > +NOTICE: s2 strict > +NOTICE: v > +NOTICE: s2 strict > +NOTICE: v > +NOTICE: s2 strict > + x_stl2_strict > +--------------- > + 1 > + 1 > + 1 > + 1 > +(4 rows) > + > +SELECT x_imm2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be > precalculated > +NOTICE: v > +NOTICE: i2 strict > +NOTICE: v > +NOTICE: i2 strict > +NOTICE: v > +NOTICE: i2 strict > +NOTICE: v > +NOTICE: i2 strict > + x_imm2_strict > +--------------- > + 1 > + 1 > + 1 > + 1 > +(4 rows) > + > +SELECT x_stl2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x; > +NOTICE: s2 strict > +NOTICE: s2 strict > + x_stl2_strict > +--------------- > + 1 > + 1 > + 1 > + 1 > +(4 rows) > + > +SELECT x_imm2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x; > +NOTICE: s2 strict > +NOTICE: i2 strict > + x_imm2_strict > +--------------- > + 1 > + 1 > + 1 > + 1 > +(4 rows) > + > +SELECT x_stl2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x; > +NOTICE: s2 > + x_stl2_strict > +--------------- > + > + > + > + > +(4 rows) > + > +SELECT x_imm2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x; > +NOTICE: s2 > + x_imm2_strict > +--------------- > + > + > + > + > +(4 rows) > + > +SELECT 1 === 2 FROM generate_series(1, 4) x; -- should not be precalculated > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > +NOTICE: equal integers volatile > + ?column? > +---------- > + f > + f > + f > + f > +(4 rows) > + > +SELECT 1 ==== 2 FROM generate_series(1, 4) x; > +NOTICE: equal integers stable > + ?column? > +---------- > + f > + f > + f > + f > +(4 rows) > + > +SELECT 1 ===== 2 FROM generate_series(1, 4) x; > +NOTICE: equal integers immutable > + ?column? > +---------- > + f > + f > + f > + f > +(4 rows) > + > +SELECT (x_vlt() ==== 2) ====== (x_vlt() ===== 3) FROM generate_series(1, 4) > x; -- should not be precalculated > +NOTICE: v > +NOTICE: equal integers stable > +NOTICE: v > +NOTICE: equal integers immutable > +NOTICE: equal booleans stable strict > +NOTICE: v > +NOTICE: equal integers stable > +NOTICE: v > +NOTICE: equal integers immutable > +NOTICE: equal booleans stable strict > +NOTICE: v > +NOTICE: equal integers stable > +NOTICE: v > +NOTICE: equal integers immutable > +NOTICE: equal booleans stable strict > +NOTICE: v > +NOTICE: equal integers stable > +NOTICE: v > +NOTICE: equal integers immutable > +NOTICE: equal booleans stable strict > + ?column? > +---------- > + t > + t > + t > + t > +(4 rows) > + > +SELECT (1 ==== 2) ====== (3 ==== 3) FROM generate_series(1, 4) x; > +NOTICE: equal integers stable > +NOTICE: equal integers stable > +NOTICE: equal booleans stable strict > + ?column? > +---------- > + f > + f > + f > + f > +(4 rows) > + > +SELECT x_stl2_boolean(NULL) ====== (3 ==== 3) FROM generate_series(1, 4) x; > +NOTICE: s2 boolean > +NOTICE: equal integers stable > + ?column? > +---------- > + > + > + > + > +(4 rows) > + > +SELECT x_vlt() ==== 2 FROM generate_series(1, 4) x; -- should not be > precalculated > +NOTICE: v > +NOTICE: equal integers stable > +NOTICE: v > +NOTICE: equal integers stable > +NOTICE: v > +NOTICE: equal integers stable > +NOTICE: v > +NOTICE: equal integers stable > + ?column? > +---------- > + f > + f > + f > + f > +(4 rows) > + > +SELECT x_vlt() ===== 2 FROM generate_series(1, 4) x; -- should not be > precalculated > +NOTICE: v > +NOTICE: equal integers immutable > +NOTICE: v > +NOTICE: equal integers immutable > +NOTICE: v > +NOTICE: equal integers immutable > +NOTICE: v > +NOTICE: equal integers immutable > + ?column? > +---------- > + f > + f > + f > + f > +(4 rows) > + > +SELECT x_stl() ==== x_stl() FROM generate_series(1, 4) x; > +NOTICE: s > +NOTICE: s > +NOTICE: equal integers stable > + ?column? > +---------- > + t > + t > + t > + t > +(4 rows) > + > +SELECT x_stl2_boolean(1 ==== 2) FROM generate_series(1, 4) x; > +NOTICE: equal integers stable > +NOTICE: s2 boolean > + x_stl2_boolean > +---------------- > + f > + f > + f > + f > +(4 rows) > + > +SET track_functions TO DEFAULT; > +-- PL/pgSQL Simple expressions > +-- Make sure precalculated stable functions can't be simple expressions: > these > +-- expressions are only initialized once per transaction and then executed > +-- multiple times. > +BEGIN; > +SELECT simple(); > + simple > +-------- > + 2 > +(1 row) > + > +INSERT INTO two VALUES (3); > +SELECT simple(); > + simple > +-------- > + 3 > +(1 row) > + > +ROLLBACK; > +-- Drop tables for testing > +DROP TABLE two; > diff --git a/src/test/regress/serial_schedule > b/src/test/regress/serial_schedule > index 04206c3..f2710b9 100644 > --- a/src/test/regress/serial_schedule > +++ b/src/test/regress/serial_schedule > @@ -179,3 +179,4 @@ test: with > test: xml > test: event_trigger > test: stats > +test: precalculate_stable_functions > diff --git a/src/test/regress/sql/precalculate_stable_functions.sql > b/src/test/regress/sql/precalculate_stable_functions.sql > new file mode 100644 > index 0000000..a59791d > --- /dev/null > +++ b/src/test/regress/sql/precalculate_stable_functions.sql > @@ -0,0 +1,949 @@ > +-- > +-- PRECALCULATE STABLE FUNCTIONS > +-- > + > +-- Create types and tables for testing > + > +CREATE TYPE my_integer AS (value integer); > + > +CREATE TABLE two (i integer); > +INSERT INTO two VALUES (1), (2); > + > +-- Create volatile functions for testing > + > +CREATE OR REPLACE FUNCTION public.x_vlt ( > +) > +RETURNS integer VOLATILE AS > +$body$ > +BEGIN > + RAISE NOTICE 'v'; > + RETURN 1; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > + > +CREATE OR REPLACE FUNCTION public.equal_integers_vlt ( > + integer, > + integer > +) > +RETURNS boolean VOLATILE AS > +$body$ > +BEGIN > + RAISE NOTICE 'equal integers volatile'; > + RETURN $1 = $2; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > + > +CREATE OR REPLACE FUNCTION public.x_vlt_my_integer ( > +) > +RETURNS my_integer VOLATILE AS > +$body$ > +BEGIN > + RAISE NOTICE 'v my_integer'; > + RETURN '(1)'::my_integer; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > + > +CREATE OR REPLACE FUNCTION public.equal_my_integer_vlt ( > + my_integer, > + my_integer > +) > +RETURNS boolean VOLATILE AS > +$body$ > +BEGIN > + RAISE NOTICE 'equal my_integer volatile'; > + RETURN $1.value = $2.value; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > + > +CREATE OR REPLACE FUNCTION public.x_vlt_array_int ( > +) > +RETURNS int[] VOLATILE AS > +$body$ > +BEGIN > + RAISE NOTICE 'v array_int'; > + RETURN '{2, 3}'::int[]; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > + > +-- Create stable functions for testing > + > +CREATE OR REPLACE FUNCTION public.x_stl ( > +) > +RETURNS integer STABLE AS > +$body$ > +BEGIN > + RAISE NOTICE 's'; > + RETURN 1; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > + > +CREATE OR REPLACE FUNCTION public.x_stl2 ( > + integer > +) > +RETURNS integer STABLE AS > +$body$ > +BEGIN > + RAISE NOTICE 's2'; > + RETURN $1; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > + > +CREATE OR REPLACE FUNCTION public.x_stl2_strict ( > + integer > +) > +RETURNS integer STABLE STRICT AS > +$body$ > +BEGIN > + RAISE NOTICE 's2 strict'; > + RETURN $1; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > + > +CREATE OR REPLACE FUNCTION public.equal_integers_stl ( > + integer, > + integer > +) > +RETURNS boolean STABLE AS > +$body$ > +BEGIN > + RAISE NOTICE 'equal integers stable'; > + RETURN $1 = $2; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > + > +CREATE OR REPLACE FUNCTION public.x_stl2_boolean ( > + boolean > +) > +RETURNS boolean STABLE AS > +$body$ > +BEGIN > + RAISE NOTICE 's2 boolean'; > + RETURN $1; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > + > +CREATE OR REPLACE FUNCTION public.equal_booleans_stl_strict ( > + boolean, > + boolean > +) > +RETURNS boolean STABLE STRICT AS > +$body$ > +BEGIN > + RAISE NOTICE 'equal booleans stable strict'; > + RETURN $1 = $2; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > + > +CREATE OR REPLACE FUNCTION public.x_stl_my_integer ( > +) > +RETURNS my_integer STABLE AS > +$body$ > +BEGIN > + RAISE NOTICE 's my_integer'; > + RETURN '(1)'::my_integer; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > + > +CREATE OR REPLACE FUNCTION public.equal_my_integer_stl ( > + my_integer, > + my_integer > +) > +RETURNS boolean STABLE AS > +$body$ > +BEGIN > + RAISE NOTICE 'equal my_integer stable'; > + RETURN $1.value = $2.value; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > + > +CREATE OR REPLACE FUNCTION public.x_stl_array_int ( > +) > +RETURNS int[] STABLE AS > +$body$ > +BEGIN > + RAISE NOTICE 's array_int'; > + RETURN '{2, 3}'::int[]; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > + > +CREATE OR REPLACE FUNCTION public.stable_max( > +) > +RETURNS integer STABLE AS > +$body$ > +BEGIN > + RETURN (SELECT max(i) from two); > +END > +$body$ > +LANGUAGE 'plpgsql'; > + > +CREATE OR REPLACE FUNCTION public.simple( > +) > +RETURNS integer STABLE AS > +$body$ > +BEGIN > + RETURN stable_max(); > +END > +$body$ > +LANGUAGE 'plpgsql'; > + > +-- Create immutable functions for testing > + > +CREATE OR REPLACE FUNCTION public.x_imm2 ( > + integer > +) > +RETURNS integer IMMUTABLE AS > +$body$ > +BEGIN > + RAISE NOTICE 'i2'; > + RETURN $1; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > + > +CREATE OR REPLACE FUNCTION public.x_imm2_strict ( > + integer > +) > +RETURNS integer IMMUTABLE STRICT AS > +$body$ > +BEGIN > + RAISE NOTICE 'i2 strict'; > + RETURN $1; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > + > +CREATE OR REPLACE FUNCTION public.equal_integers_imm ( > + integer, > + integer > +) > +RETURNS boolean IMMUTABLE AS > +$body$ > +BEGIN > + RAISE NOTICE 'equal integers immutable'; > + RETURN $1 = $2; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > + > +CREATE OR REPLACE FUNCTION public.equal_my_integer_imm ( > + my_integer, > + my_integer > +) > +RETURNS boolean IMMUTABLE AS > +$body$ > +BEGIN > + RAISE NOTICE 'equal my_integer immutable'; > + RETURN $1.value = $2.value; > +END; > +$body$ > +LANGUAGE 'plpgsql'; > + > +-- Create operators for testing > + > +CREATE operator === ( > + PROCEDURE = equal_integers_vlt, > + LEFTARG = integer, > + RIGHTARG = integer > +); > + > +CREATE operator ==== ( > + PROCEDURE = equal_integers_stl, > + LEFTARG = integer, > + RIGHTARG = integer > +); > + > +CREATE operator ===== ( > + PROCEDURE = equal_integers_imm, > + LEFTARG = integer, > + RIGHTARG = integer > +); > + > +CREATE operator ====== ( > + PROCEDURE = equal_booleans_stl_strict, > + LEFTARG = boolean, > + RIGHTARG = boolean > +); > + > +CREATE operator ==== ( > + PROCEDURE = equal_my_integer_stl, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > + > +-- Simple functions testing > + > +SELECT x_vlt() FROM generate_series(1, 3) x; -- should not be precalculated > +SELECT x_stl() FROM generate_series(1, 3) x; > + > +-- WHERE clause testing > + > +SELECT x_vlt() FROM generate_series(1, 4) x WHERE x_vlt() < x; -- should not > be precalculated > +SELECT x_stl() FROM generate_series(1, 4) x WHERE x_stl() < x; > + > +-- Functions with constant arguments and nested functions testing > + > +SELECT x_stl2(x_vlt()) FROM generate_series(1, 4) x; -- should not be > precalculated > +SELECT x_imm2(x_vlt()) FROM generate_series(1, 4) x; -- should not be > precalculated > + > +SELECT x_stl2(x_stl2(1)) FROM generate_series(1, 4) x; > +SELECT x_imm2(x_stl2(1)) FROM generate_series(1, 4) x; > + > +-- Strict functions testing > + > +SELECT x_stl2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be > precalculated > +SELECT x_imm2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be > precalculated > + > +SELECT x_stl2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x; > +SELECT x_imm2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x; > + > +-- Strict functions with null arguments testing > + > +SELECT x_stl2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x; > +SELECT x_imm2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x; > + > +-- Operators testing > + > +SELECT 1 === 2 FROM generate_series(1, 4) x; -- should not be precalculated > +SELECT 1 ==== 2 FROM generate_series(1, 4) x; > + > +-- Nested and strict operators testing > +-- (also partly mixed functions and operators testing) > + > +SELECT (x_vlt() ==== 2) ====== (x_vlt() ===== 3) FROM generate_series(1, 4) > x; -- should not be precalculated > +SELECT (x_stl() ==== 2) ====== (x_stl() ===== 3) FROM generate_series(1, 4) > x; > +SELECT (1 ==== 2) ====== x_stl2_boolean(NULL) FROM generate_series(1, 4) x; > + > +-- IS DISTINCT FROM expression testing > + > +-- create operator here because we will drop and reuse it several times > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_vlt, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > + > +-- should not be precalculated > +SELECT '(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer > +FROM generate_series(1, 4) x; > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_stl, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > + > +SELECT '(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer > +FROM generate_series(1, 4) x; > + > +-- IS DISTINCT FROM expressions with null arguments testing > + > +SELECT x_stl2_boolean(1 IS DISTINCT FROM x_stl2(NULL)) > +FROM generate_series(1, 4) x; > + > +SELECT x_stl2_boolean(x_stl2(NULL) IS DISTINCT FROM x_stl2(NULL)) > +FROM generate_series(1, 4) x; > + > +-- Nested IS DISTINCT FROM expression testing > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_vlt, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > + > +-- should not be precalculated > +SELECT x_stl2_boolean( > + ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) IS DISTINCT FROM > + TRUE > +) > +FROM generate_series(1, 4) x; > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_stl, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > + > +SELECT x_stl2_boolean( > + ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) IS DISTINCT FROM > + TRUE > +) > +FROM generate_series(1, 4) x; > + > +-- NULLIF expressions testing > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_vlt, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > + > +-- should not be precalculated > +SELECT NULLIF('(1)'::my_integer, '(2)'::my_integer) > +FROM generate_series(1, 4) x; > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_stl, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > + > +SELECT NULLIF('(1)'::my_integer, '(2)'::my_integer) > +FROM generate_series(1, 4) x; > + > +-- NULLIF expressions with null arguments testing > + > +SELECT x_stl2(NULLIF(1, x_stl2(NULL))) FROM generate_series(1, 4) x; > + > +SELECT x_stl2(NULLIF(x_stl2(NULL), x_stl2(NULL))) FROM generate_series(1, 4) > x; > + > +-- Nested NULLIF expression testing > + > +-- should not be precalculated > +SELECT NULLIF(NULLIF(x_vlt_my_integer(), '(2)'::my_integer), > '(2)'::my_integer) > +FROM generate_series(1, 4) x; > + > +SELECT NULLIF(NULLIF(x_stl_my_integer(), '(2)'::my_integer), > '(2)'::my_integer) > +FROM generate_series(1, 4) x; > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_imm, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > + > +-- should not be precalculated > +SELECT NULLIF(NULLIF(x_vlt_my_integer(), '(2)'::my_integer), > '(2)'::my_integer) > +FROM generate_series(1, 4) x; > + > +SELECT NULLIF(NULLIF(x_stl_my_integer(), '(2)'::my_integer), > '(2)'::my_integer) > +FROM generate_series(1, 4) x; > + > +-- "scalar op ANY/ALL (array)" / "scalar IN (2 or more values)" expressions > +-- testing > + > +SELECT 1 === ANY('{2, 3}') FROM generate_series(1, 4) x; -- should not be > precalculated > +SELECT 1 === ALL('{2, 3}') FROM generate_series(1, 4) x; -- should not be > precalculated > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_vlt, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > + > +-- should not be precalculated > +SELECT '(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer) > +FROM generate_series(1, 4) x; > + > +SELECT 1 ==== ANY('{2, 3}') FROM generate_series(1, 4) x; > +SELECT 1 ==== ALL('{2, 3}') FROM generate_series(1, 4) x; > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_stl, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > + > +SELECT '(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer) > +FROM generate_series(1, 4) x; > + > +-- "scalar op ANY/ALL (array)" / "scalar IN (2 or more values)" expressions > with > +-- null arguments testing > + > +SELECT 1 ==== ANY('{2, NULL}') FROM generate_series(1, 4) x; > +SELECT x_stl2_boolean(1 ==== ANY(NULL)) FROM generate_series(1, 4) x; > +SELECT NULL ==== ANY('{2, 3}'::int[]) FROM generate_series(1, 4) x; > +SELECT NULL ==== ANY('{2, NULL}'::int[]) FROM generate_series(1, 4) x; > +SELECT x_stl2_boolean(NULL::int ==== ANY(NULL)) FROM generate_series(1, 4) x; > + > +SELECT 1 ==== ALL('{2, NULL}') FROM generate_series(1, 4) x; > +SELECT x_stl2_boolean(1 ==== ALL(NULL)) FROM generate_series(1, 4) x; > +SELECT NULL ==== ALL('{2, 3}'::int[]) FROM generate_series(1, 4) x; > +SELECT NULL ==== ALL('{2, NULL}'::int[]) FROM generate_series(1, 4) x; > +SELECT x_stl2_boolean(NULL::int ==== ALL(NULL)) FROM generate_series(1, 4) x; > + > +SELECT x_stl2_boolean(1 IN (2, NULL)) FROM generate_series(1, 4) x; > +SELECT x_stl2_boolean(NULL IN (2, 3)) FROM generate_series(1, 4) x; > +SELECT x_stl2_boolean(NULL IN (2, NULL)) FROM generate_series(1, 4) x; > + > +-- Nesting "scalar op ANY/ALL (array)" / "scalar IN (2 or more values)" > +-- expressions testing (also partly mixed functions and "scalar op ANY/ALL > +-- (array)" / "scalar IN (2 or more values)" expressions testing) > + > +-- should not be precalculated > +SELECT x_stl2_boolean((x_vlt() ==== ANY('{2, 3}')) = ANY('{TRUE}')) > +FROM generate_series(1, 4) x; > + > +-- should not be precalculated > +SELECT x_stl2_boolean((x_vlt() ==== ANY('{2, 3}')) = ALL('{TRUE}')) > +FROM generate_series(1, 4) x; > + > +-- should not be precalculated > +SELECT x_stl2_boolean((x_vlt() ==== ANY('{2, 3}')) IN (TRUE, FALSE)) > +FROM generate_series(1, 4) x; > + > +SELECT x_stl2_boolean((x_stl() ==== ANY('{2, 3}')) = ANY('{TRUE}')) > +FROM generate_series(1, 4) x; > + > +SELECT x_stl2_boolean((x_stl() ==== ANY('{2, 3}')) = ALL('{TRUE}')) > +FROM generate_series(1, 4) x; > + > +SELECT x_stl2_boolean((x_stl() ==== ANY('{2, 3}')) IN (TRUE, FALSE)) > +FROM generate_series(1, 4) x; > + > +-- should not be precalculated > +SELECT x_stl2_boolean((x_vlt() ===== ANY('{2, 3}')) = ANY('{TRUE}')) > +FROM generate_series(1, 4) x; > + > +-- should not be precalculated > +SELECT x_stl2_boolean((x_vlt() ===== ANY('{2, 3}')) = ALL('{TRUE}')) > +FROM generate_series(1, 4) x; > + > +-- should not be precalculated > +SELECT x_stl2_boolean((x_vlt() ===== ANY('{2, 3}')) IN (TRUE, FALSE)) > +FROM generate_series(1, 4) x; > + > +SELECT x_stl2_boolean((x_stl() ===== ANY('{2, 3}')) = ANY('{TRUE}')) > +FROM generate_series(1, 4) x; > + > +SELECT x_stl2_boolean((x_stl() ===== ANY('{2, 3}')) = ALL('{TRUE}')) > +FROM generate_series(1, 4) x; > + > +SELECT x_stl2_boolean((x_stl() ===== ANY('{2, 3}')) IN (TRUE, FALSE)) > +FROM generate_series(1, 4) x; > + > +-- Mixed functions and operators testing > +-- (most of it was earlier in Nested and strict operators testing) > + > +SELECT x_stl2_boolean(1 ==== 2) FROM generate_series(1, 4) x; > + > +-- Mixed functions and IS DISTINCT FROM expressions testing > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_vlt, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > + > +-- should not be precalculated > +SELECT equal_booleans_stl_strict( > + (x_stl_my_integer() IS DISTINCT FROM '(1)'::my_integer), > + (x_stl_my_integer() IS DISTINCT FROM '(2)'::my_integer) > +) > +FROM generate_series(1, 4) x; > + > +SELECT equal_booleans_stl_strict( > + (x_stl() IS DISTINCT FROM 1), > + (x_stl() IS DISTINCT FROM 2) > +) > +FROM generate_series(1, 4) x; > + > +-- Mixed functions and NULLIF expressions testing > + > +-- should not be precalculated > +SELECT equal_my_integer_stl( > + NULLIF(x_stl_my_integer(), '(1)'::my_integer), > + NULLIF(x_stl_my_integer(), '(2)'::my_integer) > +) > +FROM generate_series(1, 4) x; > + > +SELECT equal_integers_stl(NULLIF(x_stl(), 1), NULLIF(x_stl(), 2)) > +FROM generate_series(1, 4) x; > + > +-- Mixed functions and "scalar op ANY/ALL (array)" / "scalar IN (2 or more > +-- values)" expressions testing (partly in nesting "scalar op ANY/ALL > (array)" / > +-- "scalar IN (2 or more values)" expressions testing) > + > +SELECT 1 ==== ANY(x_vlt_array_int()) FROM generate_series(1, 4) x; -- should > not be precalculated > +SELECT 1 ==== ALL(x_vlt_array_int()) FROM generate_series(1, 4) x; -- should > not be precalculated > + > +SELECT 1 ==== ANY(x_stl_array_int()) FROM generate_series(1, 4) x; > +SELECT 1 ==== ALL(x_stl_array_int()) FROM generate_series(1, 4) x; > + > +-- Mixed operators and IS DISTINCT FROM expressions testing > + > +-- should not be precalculated > +SELECT ( > + ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) ====== > + ('(2)'::my_integer IS DISTINCT FROM '(2)'::my_integer) > +) > +FROM generate_series(1, 4) x; > + > +-- should not be precalculated > +SELECT x_stl2_boolean((1 === 2) IS DISTINCT FROM TRUE) > +FROM generate_series(1, 4) x; > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_stl, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > + > +SELECT ( > + ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) ====== > + ('(2)'::my_integer IS DISTINCT FROM '(2)'::my_integer) > +) > +FROM generate_series(1, 4) x; > + > +SELECT x_stl2_boolean((1 ==== 2) IS DISTINCT FROM TRUE) > +FROM generate_series(1, 4) x; > + > +-- Mixed operators and NULLIF expressions testing > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_vlt, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > + > +-- should not be precalculated > +SELECT ( > + NULLIF('(1)'::my_integer, '(2)'::my_integer) ==== > + NULLIF('(2)'::my_integer, '(2)'::my_integer) > +) > +FROM generate_series(1, 4) x; > + > +-- should not be precalculated > +SELECT x_stl2_boolean(NULLIF(1 === 2, TRUE)) FROM generate_series(1, 4) x; > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_stl, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > + > +SELECT ( > + NULLIF('(1)'::my_integer, '(2)'::my_integer) ==== > + NULLIF('(2)'::my_integer, '(2)'::my_integer) > +) > +FROM generate_series(1, 4) x; > + > +SELECT x_stl2_boolean(NULLIF(1 ==== 2, TRUE)) FROM generate_series(1, 4) x; > + > +-- Mixed operators and "scalar op ANY/ALL (array)" / "scalar IN (2 or more > +-- values)" expressions testing > + > +-- should not be precalculated > +SELECT (1 === ANY('{2, 3}')) ====== (1 === ALL('{2, 3}')) > +FROM generate_series(1, 4) x; > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_vlt, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > + > +-- should not be precalculated > +SELECT ('(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)) ====== > TRUE > +FROM generate_series(1, 4) x; > + > +-- should not be precalculated > +SELECT x_stl2_boolean((1 === 2) = ANY('{TRUE}')) > +FROM generate_series(1, 4) x; > + > +-- should not be precalculated > +SELECT x_stl2_boolean((1 === 2) = ALL('{TRUE}')) > +FROM generate_series(1, 4) x; > + > +-- should not be precalculated > +SELECT x_stl2_boolean((1 === 2) IN (TRUE, FALSE)) > +FROM generate_series(1, 4) x; > + > +SELECT (1 ==== ANY('{2, 3}')) ====== (1 ==== ALL('{2, 3}')) > +FROM generate_series(1, 4) x; > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_stl, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > + > +SELECT ('(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)) ====== > TRUE > +FROM generate_series(1, 4) x; > + > +SELECT x_stl2_boolean((1 ==== 2) = ANY('{TRUE}')) > +FROM generate_series(1, 4) x; > + > +SELECT x_stl2_boolean((1 ==== 2) = ALL('{TRUE}')) > +FROM generate_series(1, 4) x; > + > +SELECT x_stl2_boolean((1 ==== 2) IN (TRUE, FALSE)) > +FROM generate_series(1, 4) x; > + > +-- Mixed IS DISTINCT FROM and NULLIF expressions testing > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_vlt, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > + > +-- should not be precalculated > +SELECT ( > + NULLIF('(1)'::my_integer, '(2)'::my_integer) IS DISTINCT FROM > + NULLIF('(2)'::my_integer, '(2)'::my_integer) > +) > +FROM generate_series(1, 4) x; > + > +-- should not be precalculated > +SELECT NULLIF( > + ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer), > + ('(2)'::my_integer IS DISTINCT FROM '(2)'::my_integer) > +) > +FROM generate_series(1, 4) x; > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_stl, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > + > +SELECT ( > + NULLIF('(1)'::my_integer, '(2)'::my_integer) IS DISTINCT FROM > + NULLIF('(2)'::my_integer, '(2)'::my_integer) > +) > +FROM generate_series(1, 4) x; > + > +SELECT NULLIF( > + ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer), > + ('(2)'::my_integer IS DISTINCT FROM '(2)'::my_integer) > +) > +FROM generate_series(1, 4) x; > + > +-- Mixed IS DISTINCT FROM and "scalar op ANY/ALL (array)" / "scalar IN (2 or > +-- more values)" expressions testing > + > +-- should not be precalculated > +SELECT x_stl2_boolean( > + (1 === ANY('{2, 3}')) IS DISTINCT FROM > + (1 === ALL('{2, 3}')) > +) > +FROM generate_series(1, 4) x; > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_vlt, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > + > +-- should not be precalculated > +SELECT x_stl2_boolean( > + ('(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)) IS DISTINCT > FROM > + TRUE > +) > +FROM generate_series(1, 4) x; > + > +-- should not be precalculated > +SELECT x_stl2_boolean( > + ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) = ANY('{TRUE}') > +) > +FROM generate_series(1, 4) x; > + > +-- should not be precalculated > +SELECT x_stl2_boolean( > + ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) = ALL('{TRUE}') > +) > +FROM generate_series(1, 4) x; > + > +-- should not be precalculated > +SELECT x_stl2_boolean( > + ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) IN (TRUE, FALSE) > +) > +FROM generate_series(1, 4) x; > + > +SELECT x_stl2_boolean( > + (1 ==== ANY('{2, 3}')) IS DISTINCT FROM > + (1 ==== ALL('{2, 3}')) > +) > +FROM generate_series(1, 4) x; > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_stl, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > + > +SELECT x_stl2_boolean( > + ('(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)) IS DISTINCT > FROM > + TRUE > +) > +FROM generate_series(1, 4) x; > + > +SELECT x_stl2_boolean( > + ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) = ANY('{TRUE}') > +) > +FROM generate_series(1, 4) x; > + > +SELECT x_stl2_boolean( > + ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) = ALL('{TRUE}') > +) > +FROM generate_series(1, 4) x; > + > +SELECT x_stl2_boolean( > + ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) IN (TRUE, FALSE) > +) > +FROM generate_series(1, 4) x; > + > +-- Mixed NULLIF and "scalar op ANY/ALL (array)" / "scalar IN (2 or more > values)" > +-- expressions testing > + > +-- should not be precalculated > +SELECT x_stl2_boolean(NULLIF(1 === ANY('{2, 3}'), 1 === ALL('{2, 3}'))) > +FROM generate_series(1, 4) x; > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_vlt, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > + > +-- should not be precalculated > +SELECT x_stl2_boolean(NULLIF( > + '(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer), > + TRUE > +)) > +FROM generate_series(1, 4) x; > + > +-- should not be precalculated > +SELECT ( > + NULLIF('(1)'::my_integer, '(2)'::my_integer) ==== > + ANY('{(3)}'::my_integer[]) > +) > +FROM generate_series(1, 4) x; > + > +-- should not be precalculated > +SELECT ( > + NULLIF('(1)'::my_integer, '(2)'::my_integer) ==== > + ALL('{(3)}'::my_integer[]) > +) > +FROM generate_series(1, 4) x; > + > +-- should not be precalculated > +SELECT ( > + NULLIF('(1)'::my_integer, '(2)'::my_integer) IN > + ('(3)'::my_integer, '(2)'::my_integer) > +) > +FROM generate_series(1, 4) x; > + > +SELECT x_stl2_boolean(NULLIF(1 ==== ANY('{2, 3}'), 1 ==== ALL('{2, 3}'))) > +FROM generate_series(1, 4) x; > + > +DROP OPERATOR = (my_integer, my_integer); > +CREATE OPERATOR = ( > + PROCEDURE = equal_my_integer_stl, > + LEFTARG = my_integer, > + RIGHTARG = my_integer > +); > + > +SELECT x_stl2_boolean(NULLIF( > + '(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer), > + TRUE > +)) > +FROM generate_series(1, 4) x; > + > +SELECT ( > + NULLIF('(1)'::my_integer, '(2)'::my_integer) ==== > + ANY('{(3)}'::my_integer[]) > +) > +FROM generate_series(1, 4) x; > + > +SELECT ( > + NULLIF('(1)'::my_integer, '(2)'::my_integer) ==== > + ALL('{(3)}'::my_integer[]) > +) > +FROM generate_series(1, 4) x; > + > +SELECT ( > + NULLIF('(1)'::my_integer, '(2)'::my_integer) IN > + ('(3)'::my_integer, '(2)'::my_integer) > +) > +FROM generate_series(1, 4) x; > + > +-- Tracking functions testing > + > +SET track_functions TO 'all'; > + > +SELECT x_vlt() FROM generate_series(1, 3) x; -- should not be precalculated > +SELECT x_stl() FROM generate_series(1, 3) x; > + > +SELECT x_vlt() FROM generate_series(1, 4) x WHERE x_vlt() < x; -- should not > be precalculated > +SELECT x_stl() FROM generate_series(1, 4) x WHERE x_stl() < x; > + > +SELECT x_stl2(x_vlt()) FROM generate_series(1, 4) x; -- should not be > precalculated > +SELECT x_imm2(x_vlt()) FROM generate_series(1, 4) x; -- should not be > precalculated > + > +SELECT x_stl2(x_stl2(1)) FROM generate_series(1, 4) x; > +SELECT x_imm2(x_stl2(1)) FROM generate_series(1, 4) x; > + > +SELECT x_stl2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be > precalculated > +SELECT x_imm2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be > precalculated > + > +SELECT x_stl2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x; > +SELECT x_imm2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x; > + > +SELECT x_stl2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x; > +SELECT x_imm2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x; > + > +SELECT 1 === 2 FROM generate_series(1, 4) x; -- should not be precalculated > +SELECT 1 ==== 2 FROM generate_series(1, 4) x; > +SELECT 1 ===== 2 FROM generate_series(1, 4) x; > + > +SELECT (x_vlt() ==== 2) ====== (x_vlt() ===== 3) FROM generate_series(1, 4) > x; -- should not be precalculated > +SELECT (1 ==== 2) ====== (3 ==== 3) FROM generate_series(1, 4) x; > +SELECT x_stl2_boolean(NULL) ====== (3 ==== 3) FROM generate_series(1, 4) x; > + > +SELECT x_vlt() ==== 2 FROM generate_series(1, 4) x; -- should not be > precalculated > +SELECT x_vlt() ===== 2 FROM generate_series(1, 4) x; -- should not be > precalculated > + > +SELECT x_stl() ==== x_stl() FROM generate_series(1, 4) x; > +SELECT x_stl2_boolean(1 ==== 2) FROM generate_series(1, 4) x; > + > +SET track_functions TO DEFAULT; > + > +-- PL/pgSQL Simple expressions > +-- Make sure precalculated stable functions can't be simple expressions: > these > +-- expressions are only initialized once per transaction and then executed > +-- multiple times. > + > +BEGIN; > +SELECT simple(); > +INSERT INTO two VALUES (3); > +SELECT simple(); > +ROLLBACK; > + > +-- Drop tables for testing > + > +DROP TABLE two; > -- > 1.9.1 > > From 2382fa68414f6bbed42ff66c7abbc3c9b200d244 Mon Sep 17 00:00:00 2001 > From: Marina Polyakova <m.polyak...@postgrespro.ru> > Date: Mon, 15 May 2017 16:05:38 +0300 > Subject: [PATCH v4 3/3] Precalculate stable functions, costs > > Now in Postgresql only immutable functions are precalculated; stable functions > are calculated for every row so in fact they don't differ from volatile > functions. > > This patch includes: > - cost changes for cached expressions (according to their behaviour) > --- > src/backend/optimizer/path/costsize.c | 89 > ++++++++++++++++++++++++++--------- > 1 file changed, 67 insertions(+), 22 deletions(-) > > diff --git a/src/backend/optimizer/path/costsize.c > b/src/backend/optimizer/path/costsize.c > index 52643d0..505772a 100644 > --- a/src/backend/optimizer/path/costsize.c > +++ b/src/backend/optimizer/path/costsize.c > @@ -140,6 +140,7 @@ static MergeScanSelCache *cached_scansel(PlannerInfo > *root, > PathKey *pathkey); > static void cost_rescan(PlannerInfo *root, Path *path, > Cost *rescan_startup_cost, Cost *rescan_total_cost); > +static double cost_eval_cacheable_expr_per_tuple(Node *node); > static bool cost_qual_eval_walker(Node *node, cost_qual_eval_context > *context); > static void get_restriction_qual_cost(PlannerInfo *root, RelOptInfo *baserel, > ParamPathInfo *param_info, > @@ -3464,6 +3465,59 @@ cost_qual_eval_node(QualCost *cost, Node *qual, > PlannerInfo *root) > *cost = context.total; > } > > +/* > + * cost_eval_cacheable_expr_per_tuple > + * Evaluate per tuple cost for expressions that can be cacheable. > + * > + * This function was created to not duplicate code for some expression and > + * cached some expression. > + */ > +static double > +cost_eval_cacheable_expr_per_tuple(Node *node) > +{ > + double result; > + > + /* > + * For each operator or function node in the given tree, we charge the > + * estimated execution cost given by pg_proc.procost (remember to > multiply > + * this by cpu_operator_cost). > + */ > + if (IsA(node, FuncExpr)) > + { > + result = get_func_cost(((FuncExpr *) node)->funcid) * > cpu_operator_cost; > + } > + else if (IsA(node, OpExpr) || > + IsA(node, DistinctExpr) || > + IsA(node, NullIfExpr)) > + { > + OpExpr *opexpr = (OpExpr *) node; > + > + /* rely on struct equivalence to treat these all alike */ > + set_opfuncid(opexpr); > + > + result = get_func_cost(opexpr->opfuncid) * cpu_operator_cost; > + } > + else if (IsA(node, ScalarArrayOpExpr)) > + { > + /* > + * Estimate that the operator will be applied to about half of > the > + * array elements before the answer is determined. > + */ > + ScalarArrayOpExpr *saop = (ScalarArrayOpExpr *) node; > + Node *arraynode = (Node *) lsecond(saop->args); > + > + set_sa_opfuncid(saop); > + result = get_func_cost(saop->opfuncid) * cpu_operator_cost * > + estimate_array_length(arraynode) * 0.5; > + } > + else > + { > + elog(ERROR, "non cacheable expression node type: %d", (int) > nodeTag(node)); > + } > + > + return result; > +} > + > static bool > cost_qual_eval_walker(Node *node, cost_qual_eval_context *context) > { > @@ -3537,32 +3591,23 @@ cost_qual_eval_walker(Node *node, > cost_qual_eval_context *context) > * moreover, since our rowcount estimates for functions tend to be > pretty > * phony, the results would also be pretty phony. > */ > - if (IsA(node, FuncExpr)) > + if (IsA(node, FuncExpr) || > + IsA(node, OpExpr) || > + IsA(node, DistinctExpr) || > + IsA(node, NullIfExpr) || > + IsA(node, ScalarArrayOpExpr)) > { > - context->total.per_tuple += > - get_func_cost(((FuncExpr *) node)->funcid) * > cpu_operator_cost; > + context->total.per_tuple += > cost_eval_cacheable_expr_per_tuple(node); > } > - else if (IsA(node, OpExpr) || > - IsA(node, DistinctExpr) || > - IsA(node, NullIfExpr)) > - { > - /* rely on struct equivalence to treat these all alike */ > - set_opfuncid((OpExpr *) node); > - context->total.per_tuple += > - get_func_cost(((OpExpr *) node)->opfuncid) * > cpu_operator_cost; > - } > - else if (IsA(node, ScalarArrayOpExpr)) > - { > + else if (IsA(node, CachedExpr)) > + { > /* > - * Estimate that the operator will be applied to about half of > the > - * array elements before the answer is determined. > + * Calculate subexpression cost per tuple as usual and add it > to startup > + * cost (because subexpression will be executed only once for > all > + * tuples). > */ > - ScalarArrayOpExpr *saop = (ScalarArrayOpExpr *) node; > - Node *arraynode = (Node *) lsecond(saop->args); > - > - set_sa_opfuncid(saop); > - context->total.per_tuple += get_func_cost(saop->opfuncid) * > - cpu_operator_cost * estimate_array_length(arraynode) * > 0.5; > + context->total.startup += cost_eval_cacheable_expr_per_tuple( > + get_subexpr((CachedExpr *) node)); > } > else if (IsA(node, Aggref) || > IsA(node, WindowFunc)) > -- > 1.9.1 > -- Best regards, Aleksander Alekseev
signature.asc
Description: PGP signature
