Re: WIP Patch: Precalculate stable functions, infrastructure v1

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(dot)polyakova(at)postgrespro(dot)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(dot)polyakova(at)postgrespro(dot)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(dot)polyakova(at)postgrespro(dot)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