Thread: [HACKERS] WIP Patch: Precalculate stable functions, infrastructure v1

Hello everyone again!

This is the continuation of my previous patch on the same topic; here 
there are changes made thanks to Tom Lane comments (see thread here 
[1]). To not send big patch I have split it (that's why version starts 
with the first again) and here I send infrastructure patch which 
includes:
- creation of CachedExpr node
- usual node functions for it
- mutator to replace nonovolatile functions' and operators' expressions 
by appropriate cached expressions.

Any suggestions are welcome!

[1] 

https://www.postgresql.org/message-id/flat/98c77534fa51aa4bf84a5b39931c42ea%40postgrespro.ru#98c77534fa51aa4bf84a5b39931c42ea@postgrespro.ru

-- 
Marina Polyakova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

> and here I send infrastructure patch which includes <...>

Next 2 patches:

Patch 'planning and execution', which includes:
- replacement nonvolatile functions and operators by appropriate cached 
expressions;
- planning and execution cached expressions;
- regression tests.

Patch 'costs', which includes cost changes for cached expressions 
(according to their behaviour).

-- 
Marina Polyakova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hi Marina,

I've noticed that this patch needs a review and decided to take a look.
Here is a short summary:

* Patches apply to the master branch. There are a trailing whitespaces, though.
* All tests pass.
* I see 8-10% performance improvement on full text search queries.
* It seems that there is no obvious performance degradation on regular queries (according to pgbench).

In short, it looks very promising.

--
Best regards,
Aleksander Alekseev

Hello, Aleksander!

> I've noticed that this patch needs a review and decided to take a look.

Thank you very much!

> There are a trailing whitespaces,
>   though.

Oh, sorry, I'll check them.

> I see 8-10% performance improvement on full text search queries.

Glad to hear it =)

> It seems that there is no obvious performance degradation on regular
>   queries (according to pgbench).

Thanks for testing it, I'll try not to forget about it next time =[

> In short, it looks very promising.

And thanks again!

-- 
Marina Polyakova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Hello!

Here's v2 of the patches. Changes from v1:
* Precalculation of DistinctExpr, NullIfExpr, ScalarArrayOpExpr (as 
usual, if their operators are not volatile theirselves, don't return set 
and their arguments are consts or cached expressions too);
* Removed trailing whitespaces.
Also, as I tested, it seems that there is no obvious performance 
degradation too on regular queries (according to pgbench).

Patches are attached. Any suggestions are welcome!

-- 
Marina Polyakova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

> Here's v2 of the patches. Changes from v1:

And here there's v3 of planning and execution: common executor steps for 
all types of cached expression.

-- 
Marina Polyakova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hi,


On 2017-05-18 19:00:09 +0300, Marina Polyakova wrote:
> > Here's v2 of the patches. Changes from v1:
> 
> And here there's v3 of planning and execution: common executor steps for all
> types of cached expression.

I've not followed this thread, but just scanned this quickly because it
affects execExpr* stuff.

> +        case T_CachedExpr:
> +            {
> +                int         adjust_jump;
> +
> +                /*
> +                 * Allocate and fill scratch memory used by all steps of
> +                 * CachedExpr evaluation.
> +                 */
> +                scratch.d.cachedexpr.isExecuted = (bool *) palloc(sizeof(bool));
> +                scratch.d.cachedexpr.resnull = (bool *) palloc(sizeof(bool));
> +                scratch.d.cachedexpr.resvalue = (Datum *) palloc(sizeof(Datum));
> +
> +                *scratch.d.cachedexpr.isExecuted = false;
> +                *scratch.d.cachedexpr.resnull = false;
> +                *scratch.d.cachedexpr.resvalue = (Datum) 0;

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)


> @@ -279,6 +279,7 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
>      TupleTableSlot *innerslot;
>      TupleTableSlot *outerslot;
>      TupleTableSlot *scanslot;
> +    MemoryContext oldContext;    /* for EEOP_CACHEDEXPR_* */

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?

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.


> +        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.
> +                     */

Is this actually a meaningful path?  Shouldn't always have done const
evaluation before adding CachedExpr's?


Greetings,

Andres Freund

> 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
-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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.polyakova@postgrespro.ru>
> Date: Mon, 15 May 2017 14:24:36 +0300
> Subject: [PATCH v4 1/3] Precalculate stable functions, infrastructure
>
> Now in Postgresql only immutable functions are precalculated; stable functions
> are calculated for every row so in fact they don't differ from volatile
> functions.
>
> This patch includes:
> - creation of CachedExpr node
> - usual node functions for it
> - mutator to replace nonovolatile functions' and operators' expressions by
> appropriate cached expressions.
> ---
>  src/backend/nodes/copyfuncs.c        |  31 +++++
>  src/backend/nodes/equalfuncs.c       |  31 +++++
>  src/backend/nodes/nodeFuncs.c        | 151 ++++++++++++++++++++
>  src/backend/nodes/outfuncs.c         |  56 ++++++++
>  src/backend/nodes/readfuncs.c        |  48 +++++++
>  src/backend/optimizer/plan/planner.c | 259 +++++++++++++++++++++++++++++++++++
>  src/include/nodes/nodeFuncs.h        |   1 +
>  src/include/nodes/nodes.h            |   1 +
>  src/include/nodes/primnodes.h        |  38 +++++
>  9 files changed, 616 insertions(+)
>
> diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
> index 6ad3844..f9f69a1 100644
> --- a/src/backend/nodes/copyfuncs.c
> +++ b/src/backend/nodes/copyfuncs.c
> @@ -1527,6 +1527,34 @@ _copyNullIfExpr(const NullIfExpr *from)
>      return newnode;
>  }
>
> +static CachedExpr *
> +_copyCachedExpr(const CachedExpr *from)
> +{
> +    CachedExpr *newnode = makeNode(CachedExpr);
> +
> +    COPY_SCALAR_FIELD(subexprtype);
> +    switch(from->subexprtype)
> +    {
> +        case CACHED_FUNCEXPR:
> +            COPY_NODE_FIELD(subexpr.funcexpr);
> +            break;
> +        case CACHED_OPEXPR:
> +            COPY_NODE_FIELD(subexpr.opexpr);
> +            break;
> +        case CACHED_DISTINCTEXPR:
> +            COPY_NODE_FIELD(subexpr.distinctexpr);
> +            break;
> +        case CACHED_NULLIFEXPR:
> +            COPY_NODE_FIELD(subexpr.nullifexpr);
> +            break;
> +        case CACHED_SCALARARRAYOPEXPR:
> +            COPY_NODE_FIELD(subexpr.saopexpr);
> +            break;
> +    }
> +
> +    return newnode;
> +}
> +
>  /*
>   * _copyScalarArrayOpExpr
>   */
> @@ -4867,6 +4895,9 @@ copyObjectImpl(const void *from)
>          case T_NullIfExpr:
>              retval = _copyNullIfExpr(from);
>              break;
> +        case T_CachedExpr:
> +            retval = _copyCachedExpr(from);
> +            break;
>          case T_ScalarArrayOpExpr:
>              retval = _copyScalarArrayOpExpr(from);
>              break;
> diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
> index c9a8c34..8863759 100644
> --- a/src/backend/nodes/equalfuncs.c
> +++ b/src/backend/nodes/equalfuncs.c
> @@ -384,6 +384,34 @@ _equalNullIfExpr(const NullIfExpr *a, const NullIfExpr *b)
>  }
>
>  static bool
> +_equalCachedExpr(const CachedExpr *a, const CachedExpr *b)
> +{
> +    COMPARE_SCALAR_FIELD(subexprtype);
> +
> +    /* the same subexprtype for b because we have already compared it */
> +    switch(a->subexprtype)
> +    {
> +        case CACHED_FUNCEXPR:
> +            COMPARE_NODE_FIELD(subexpr.funcexpr);
> +            break;
> +        case CACHED_OPEXPR:
> +            COMPARE_NODE_FIELD(subexpr.opexpr);
> +            break;
> +        case CACHED_DISTINCTEXPR:
> +            COMPARE_NODE_FIELD(subexpr.distinctexpr);
> +            break;
> +        case CACHED_NULLIFEXPR:
> +            COMPARE_NODE_FIELD(subexpr.nullifexpr);
> +            break;
> +        case CACHED_SCALARARRAYOPEXPR:
> +            COMPARE_NODE_FIELD(subexpr.saopexpr);
> +            break;
> +    }
> +
> +    return true;
> +}
> +
> +static bool
>  _equalScalarArrayOpExpr(const ScalarArrayOpExpr *a, const ScalarArrayOpExpr *b)
>  {
>      COMPARE_SCALAR_FIELD(opno);
> @@ -3031,6 +3059,9 @@ equal(const void *a, const void *b)
>          case T_NullIfExpr:
>              retval = _equalNullIfExpr(a, b);
>              break;
> +        case T_CachedExpr:
> +            retval = _equalCachedExpr(a, b);
> +            break;
>          case T_ScalarArrayOpExpr:
>              retval = _equalScalarArrayOpExpr(a, b);
>              break;
> diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
> index 3e8189c..e3dd576 100644
> --- a/src/backend/nodes/nodeFuncs.c
> +++ b/src/backend/nodes/nodeFuncs.c
> @@ -32,6 +32,7 @@ static bool planstate_walk_subplans(List *plans, bool (*walker) (),
>                                                  void *context);
>  static bool planstate_walk_members(List *plans, PlanState **planstates,
>                         bool (*walker) (), void *context);
> +static const Node *get_const_subexpr(const CachedExpr *cachedexpr);
>
>
>  /*
> @@ -92,6 +93,9 @@ exprType(const Node *expr)
>          case T_NullIfExpr:
>              type = ((const NullIfExpr *) expr)->opresulttype;
>              break;
> +        case T_CachedExpr:
> +            type = exprType(get_const_subexpr((const CachedExpr *) expr));
> +            break;
>          case T_ScalarArrayOpExpr:
>              type = BOOLOID;
>              break;
> @@ -311,6 +315,8 @@ exprTypmod(const Node *expr)
>                  return exprTypmod((Node *) linitial(nexpr->args));
>              }
>              break;
> +        case T_CachedExpr:
> +            return exprTypmod(get_const_subexpr((const CachedExpr *) expr));
>          case T_SubLink:
>              {
>                  const SubLink *sublink = (const SubLink *) expr;
> @@ -573,6 +579,10 @@ exprIsLengthCoercion(const Node *expr, int32 *coercedTypmod)
>          return true;
>      }
>
> +    if (expr && IsA(expr, CachedExpr))
> +        return exprIsLengthCoercion(
> +            get_const_subexpr((const CachedExpr *) expr), coercedTypmod);
> +
>      return false;
>  }
>
> @@ -655,6 +665,10 @@ strip_implicit_coercions(Node *node)
>          if (c->coercionformat == COERCE_IMPLICIT_CAST)
>              return strip_implicit_coercions((Node *) c->arg);
>      }
> +    else if (IsA(node, CachedExpr))
> +    {
> +        return strip_implicit_coercions(get_subexpr((CachedExpr *) node));
> +    }
>      return node;
>  }
>
> @@ -727,6 +741,8 @@ expression_returns_set_walker(Node *node, void *context)
>          return false;
>      if (IsA(node, XmlExpr))
>          return false;
> +    if (IsA(node, CachedExpr))
> +        return false;
>
>      return expression_tree_walker(node, expression_returns_set_walker,
>                                    context);
> @@ -790,6 +806,9 @@ exprCollation(const Node *expr)
>          case T_NullIfExpr:
>              coll = ((const NullIfExpr *) expr)->opcollid;
>              break;
> +        case T_CachedExpr:
> +            coll = exprCollation(get_const_subexpr((const CachedExpr *) expr));
> +            break;
>          case T_ScalarArrayOpExpr:
>              coll = InvalidOid;    /* result is always boolean */
>              break;
> @@ -973,6 +992,10 @@ exprInputCollation(const Node *expr)
>          case T_NullIfExpr:
>              coll = ((const NullIfExpr *) expr)->inputcollid;
>              break;
> +        case T_CachedExpr:
> +            coll = exprInputCollation(
> +                get_const_subexpr((const CachedExpr *) expr));
> +            break;
>          case T_ScalarArrayOpExpr:
>              coll = ((const ScalarArrayOpExpr *) expr)->inputcollid;
>              break;
> @@ -1034,6 +1057,9 @@ exprSetCollation(Node *expr, Oid collation)
>          case T_NullIfExpr:
>              ((NullIfExpr *) expr)->opcollid = collation;
>              break;
> +        case T_CachedExpr:
> +            exprSetCollation(get_subexpr((CachedExpr *) expr), collation);
> +            break;
>          case T_ScalarArrayOpExpr:
>              Assert(!OidIsValid(collation));        /* result is always boolean */
>              break;
> @@ -1168,6 +1194,10 @@ exprSetInputCollation(Node *expr, Oid inputcollation)
>          case T_NullIfExpr:
>              ((NullIfExpr *) expr)->inputcollid = inputcollation;
>              break;
> +        case T_CachedExpr:
> +            exprSetInputCollation(get_subexpr((CachedExpr *) expr),
> +                                  inputcollation);
> +            break;
>          case T_ScalarArrayOpExpr:
>              ((ScalarArrayOpExpr *) expr)->inputcollid = inputcollation;
>              break;
> @@ -1277,6 +1307,9 @@ exprLocation(const Node *expr)
>                                    exprLocation((Node *) opexpr->args));
>              }
>              break;
> +        case T_CachedExpr:
> +            loc = exprLocation(get_const_subexpr((const CachedExpr *) expr));
> +            break;
>          case T_ScalarArrayOpExpr:
>              {
>                  const ScalarArrayOpExpr *saopexpr = (const ScalarArrayOpExpr *) expr;
> @@ -1611,6 +1644,8 @@ fix_opfuncids_walker(Node *node, void *context)
>  {
>      if (node == NULL)
>          return false;
> +    if (IsA(node, CachedExpr))
> +        return fix_opfuncids_walker(get_subexpr((CachedExpr *) node), context);
>      if (IsA(node, OpExpr))
>          set_opfuncid((OpExpr *) node);
>      else if (IsA(node, DistinctExpr))
> @@ -1710,6 +1745,9 @@ check_functions_in_node(Node *node, check_function_callback checker,
>                      return true;
>              }
>              break;
> +        case T_CachedExpr:
> +            return check_functions_in_node(get_subexpr((CachedExpr *) node),
> +                                           checker, context);
>          case T_ScalarArrayOpExpr:
>              {
>                  ScalarArrayOpExpr *expr = (ScalarArrayOpExpr *) node;
> @@ -1980,6 +2018,17 @@ expression_tree_walker(Node *node,
>                      return true;
>              }
>              break;
> +        case T_CachedExpr:
> +            {
> +                /*
> +                 * cachedexpr is processed by my_walker, so its subexpr is
> +                 * processed too and we need to process sub-nodes of subexpr.
> +                 */
> +                if (expression_tree_walker(get_subexpr((CachedExpr *) node),
> +                                           walker, context))
> +                    return true;
> +            }
> +            break;
>          case T_ScalarArrayOpExpr:
>              {
>                  ScalarArrayOpExpr *expr = (ScalarArrayOpExpr *) node;
> @@ -2617,6 +2666,54 @@ expression_tree_mutator(Node *node,
>                  return (Node *) newnode;
>              }
>              break;
> +        case T_CachedExpr:
> +            {
> +                CachedExpr *expr = (CachedExpr *) node;
> +                CachedExpr *newnode;
> +
> +                FLATCOPY(newnode, expr, CachedExpr);
> +
> +                /*
> +                 * expr is already mutated, so its subexpr is already mutated
> +                 * too and we need to mutate sub-nodes of subexpr.
> +                 */
> +                switch(newnode->subexprtype)
> +                {
> +                    case CACHED_FUNCEXPR:
> +                        newnode->subexpr.funcexpr = (FuncExpr *)
> +                            expression_tree_mutator(
> +                                (Node *) expr->subexpr.funcexpr, mutator,
> +                                context);
> +                        break;
> +                    case CACHED_OPEXPR:
> +                        newnode->subexpr.opexpr = (OpExpr *)
> +                            expression_tree_mutator(
> +                                (Node *) expr->subexpr.opexpr, mutator,
> +                                context);
> +                        break;
> +                    case CACHED_DISTINCTEXPR:
> +                        newnode->subexpr.distinctexpr = (DistinctExpr *)
> +                            expression_tree_mutator(
> +                                (Node *) expr->subexpr.distinctexpr, mutator,
> +                                context);
> +                        break;
> +                    case CACHED_NULLIFEXPR:
> +                        newnode->subexpr.nullifexpr = (NullIfExpr *)
> +                            expression_tree_mutator(
> +                                (Node *) expr->subexpr.nullifexpr, mutator,
> +                                context);
> +                        break;
> +                    case CACHED_SCALARARRAYOPEXPR:
> +                        newnode->subexpr.saopexpr = (ScalarArrayOpExpr *)
> +                            expression_tree_mutator(
> +                                (Node *) expr->subexpr.saopexpr, mutator,
> +                                context);
> +                        break;
> +                }
> +
> +                return (Node *) newnode;
> +            }
> +            break;
>          case T_ScalarArrayOpExpr:
>              {
>                  ScalarArrayOpExpr *expr = (ScalarArrayOpExpr *) node;
> @@ -3838,3 +3935,57 @@ planstate_walk_members(List *plans, PlanState **planstates,
>
>      return false;
>  }
> +
> +/*
> + * get_const_subexpr
> + *        Get const subexpression of given const cached expression.
> + */
> +static const Node *
> +get_const_subexpr(const CachedExpr *cachedexpr)
> +{
> +    if (cachedexpr == NULL)
> +        return NULL;
> +
> +    switch (cachedexpr->subexprtype)
> +    {
> +        case CACHED_FUNCEXPR:
> +            return (const Node *) cachedexpr->subexpr.funcexpr;
> +        case CACHED_OPEXPR:
> +            return (const Node *) cachedexpr->subexpr.opexpr;
> +        case CACHED_DISTINCTEXPR:
> +            return (const Node *) cachedexpr->subexpr.distinctexpr;
> +        case CACHED_NULLIFEXPR:
> +            return (const Node *) cachedexpr->subexpr.nullifexpr;
> +        case CACHED_SCALARARRAYOPEXPR:
> +            return (const Node *) cachedexpr->subexpr.saopexpr;
> +    }
> +
> +    return NULL;
> +}
> +
> +/*
> + * get_subexpr
> + *        Get subexpression of given cached expression.
> + */
> +Node *
> +get_subexpr(CachedExpr *cachedexpr)
> +{
> +    if (cachedexpr == NULL)
> +        return NULL;
> +
> +    switch (cachedexpr->subexprtype)
> +    {
> +        case CACHED_FUNCEXPR:
> +            return (Node *) cachedexpr->subexpr.funcexpr;
> +        case CACHED_OPEXPR:
> +            return (Node *) cachedexpr->subexpr.opexpr;
> +        case CACHED_DISTINCTEXPR:
> +            return (Node *) cachedexpr->subexpr.distinctexpr;
> +        case CACHED_NULLIFEXPR:
> +            return (Node *) cachedexpr->subexpr.nullifexpr;
> +        case CACHED_SCALARARRAYOPEXPR:
> +            return (Node *) cachedexpr->subexpr.saopexpr;
> +    }
> +
> +    return NULL;
> +}
> diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
> index 8d9ff63..c0c8363 100644
> --- a/src/backend/nodes/outfuncs.c
> +++ b/src/backend/nodes/outfuncs.c
> @@ -1237,6 +1237,59 @@ _outNullIfExpr(StringInfo str, const NullIfExpr *node)
>  }
>
>  static void
> +_outCachedExpr(StringInfo str, const CachedExpr *node)
> +{
> +    WRITE_NODE_TYPE("CACHEDEXPR");
> +
> +    /* do-it-yourself enum representation; out subexprtype begin... */
> +    appendStringInfoString(str, " :subexprtype ");
> +
> +    switch(node->subexprtype)
> +    {
> +        case CACHED_FUNCEXPR:
> +            {
> +                /* ... out subexprtype end */
> +                outToken(str, "cached_funcexpr");
> +
> +                WRITE_NODE_FIELD(subexpr.funcexpr);
> +            }
> +            break;
> +        case CACHED_OPEXPR:
> +            {
> +                /* ... out subexprtype end */
> +                outToken(str, "cached_opexpr");
> +
> +                WRITE_NODE_FIELD(subexpr.opexpr);
> +            }
> +            break;
> +        case CACHED_DISTINCTEXPR:
> +            {
> +                /* ... out subexprtype end */
> +                outToken(str, "cached_distinctexpr");
> +
> +                WRITE_NODE_FIELD(subexpr.distinctexpr);
> +            }
> +            break;
> +        case CACHED_NULLIFEXPR:
> +            {
> +                /* ... out subexprtype end */
> +                outToken(str, "cached_nullifexpr");
> +
> +                WRITE_NODE_FIELD(subexpr.nullifexpr);
> +            }
> +            break;
> +        case CACHED_SCALARARRAYOPEXPR:
> +            {
> +                /* ... out subexprtype end */
> +                outToken(str, "cached_scalararrayopexpr");
> +
> +                WRITE_NODE_FIELD(subexpr.saopexpr);
> +            }
> +            break;
> +    }
> +}
> +
> +static void
>  _outScalarArrayOpExpr(StringInfo str, const ScalarArrayOpExpr *node)
>  {
>      WRITE_NODE_TYPE("SCALARARRAYOPEXPR");
> @@ -3767,6 +3820,9 @@ outNode(StringInfo str, const void *obj)
>              case T_NullIfExpr:
>                  _outNullIfExpr(str, obj);
>                  break;
> +            case T_CachedExpr:
> +                _outCachedExpr(str, obj);
> +                break;
>              case T_ScalarArrayOpExpr:
>                  _outScalarArrayOpExpr(str, obj);
>                  break;
> diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
> index e24f5d6..acb14f9 100644
> --- a/src/backend/nodes/readfuncs.c
> +++ b/src/backend/nodes/readfuncs.c
> @@ -750,6 +750,52 @@ _readNullIfExpr(void)
>  }
>
>  /*
> + * _readCachedExpr
> + */
> +static CachedExpr *
> +_readCachedExpr(void)
> +{
> +    READ_LOCALS(CachedExpr);
> +
> +    /* do-it-yourself enum representation */
> +    token = pg_strtok(&length); /* skip :subexprtype */
> +    token = pg_strtok(&length); /* get field value */
> +    if (strncmp(token, "cached_funcexpr", 15) == 0)
> +        local_node->subexprtype = CACHED_FUNCEXPR;
> +    else if (strncmp(token, "cached_opexpr", 13) == 0)
> +        local_node->subexprtype = CACHED_OPEXPR;
> +    else if (strncmp(token, "cached_distinctexpr", 19) == 0)
> +        local_node->subexprtype = CACHED_DISTINCTEXPR;
> +    else if (strncmp(token, "cached_nullifexpr", 17) == 0)
> +        local_node->subexprtype = CACHED_NULLIFEXPR;
> +    else if (strncmp(token, "cached_scalararrayopexpr", 24) == 0)
> +        local_node->subexprtype = CACHED_SCALARARRAYOPEXPR;
> +    else
> +        elog(ERROR, "unrecognized subexprtype \"%.*s\"", length, token);
> +
> +    switch (local_node->subexprtype)
> +    {
> +        case CACHED_FUNCEXPR:
> +            READ_NODE_FIELD(subexpr.funcexpr);
> +            break;
> +        case CACHED_OPEXPR:
> +            READ_NODE_FIELD(subexpr.opexpr);
> +            break;
> +        case CACHED_DISTINCTEXPR:
> +            READ_NODE_FIELD(subexpr.distinctexpr);
> +            break;
> +        case CACHED_NULLIFEXPR:
> +            READ_NODE_FIELD(subexpr.nullifexpr);
> +            break;
> +        case CACHED_SCALARARRAYOPEXPR:
> +            READ_NODE_FIELD(subexpr.saopexpr);
> +            break;
> +    }
> +
> +    READ_DONE();
> +}
> +
> +/*
>   * _readScalarArrayOpExpr
>   */
>  static ScalarArrayOpExpr *
> @@ -2462,6 +2508,8 @@ parseNodeString(void)
>          return_value = _readDistinctExpr();
>      else if (MATCH("NULLIFEXPR", 10))
>          return_value = _readNullIfExpr();
> +    else if (MATCH("CACHEDEXPR", 10))
> +        return_value = _readCachedExpr();
>      else if (MATCH("SCALARARRAYOPEXPR", 17))
>          return_value = _readScalarArrayOpExpr();
>      else if (MATCH("BOOLEXPR", 8))
> diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
> index c4a5651..552b73d 100644
> --- a/src/backend/optimizer/plan/planner.c
> +++ b/src/backend/optimizer/plan/planner.c
> @@ -184,6 +184,7 @@ static PathTarget *make_sort_input_target(PlannerInfo *root,
>                         bool *have_postponed_srfs);
>  static void adjust_paths_for_srfs(PlannerInfo *root, RelOptInfo *rel,
>                        List *targets, List *targets_contain_srfs);
> +static Node *replace_cached_expressions_mutator(Node *node);
>
>
>  /*****************************************************************************
> @@ -6086,3 +6087,261 @@ get_partitioned_child_rels(PlannerInfo *root, Index rti)
>
>      return result;
>  }
> +
> +static Node *
> +replace_cached_expressions_mutator(Node *node)
> +{
> +    if (node == NULL)
> +        return NULL;
> +
> +    /* mutate certain types of nodes */
> +    if (IsA(node, RestrictInfo))
> +    {
> +        RestrictInfo *rinfo = (RestrictInfo *) node;
> +
> +        /*
> +         * For an OR clause, recurse into the marked-up tree so that we replace
> +         * cached expressions for contained RestrictInfos too.
> +         */
> +        if (rinfo->orclause)
> +            rinfo->orclause = (Expr *) replace_cached_expressions_mutator(
> +                (Node *) rinfo->orclause);
> +        else
> +            rinfo->clause = (Expr *) replace_cached_expressions_mutator(
> +                (Node *) rinfo->clause);
> +
> +        /* do NOT recurse into children */
> +        return node;
> +    }
> +    else if (IsA(node, FuncExpr))
> +    {
> +        /*
> +         * Function is cached if:
> +         * 1) it doesn't return set,
> +         * 2) it's not volatile itself,
> +         * 3) its arguments are constants or cached expressions too.
> +         */
> +        FuncExpr   *funcexpr;
> +        ListCell   *arg;
> +        bool        has_nonconst_or_noncached_input = false;
> +        bool        func_returns_set;
> +
> +        /* firstly recurse into children */
> +        funcexpr = (FuncExpr *) expression_tree_mutator(node,
> +                                            replace_cached_expressions_mutator,
> +                                            NULL);
> +        func_returns_set = funcexpr->funcretset ||
> +            expression_returns_set((Node *) funcexpr->args);
> +
> +        foreach(arg, funcexpr->args)
> +        {
> +            void       *arg_lfirst = lfirst(arg);
> +            if (!(IsA(arg_lfirst, Const) || IsA(arg_lfirst, CachedExpr)))
> +                has_nonconst_or_noncached_input = true;
> +        }
> +
> +        if (func_returns_set ||
> +            has_nonconst_or_noncached_input ||
> +            contain_volatile_functions((Node *) &funcexpr->xpr))
> +        {
> +            /* return FuncExpr, which will not be cached */
> +            return (Node *) funcexpr;
> +        }
> +        else
> +        {
> +            /* create and return CachedExpr */
> +            CachedExpr *new_node = makeNode(CachedExpr);
> +            new_node->subexprtype = CACHED_FUNCEXPR;
> +            new_node->subexpr.funcexpr = funcexpr;
> +
> +            return (Node *) new_node;
> +        }
> +    }
> +    else if (IsA(node, OpExpr))
> +    {
> +        /*
> +         * Operator is cached if:
> +         * 1) its function doesn't return set,
> +         * 1) its function is not volatile itself,
> +         * 3) its arguments are constants or cached expressions too.
> +         */
> +        OpExpr     *opexpr = (OpExpr *) node;
> +        ListCell   *arg;
> +        bool        has_nonconst_or_noncached_input = false;
> +        bool        op_returns_set;
> +
> +        /* rely on struct equivalence to treat these all alike */
> +        set_opfuncid(opexpr);
> +
> +        /* firstly recurse into children */
> +        opexpr = (OpExpr *) expression_tree_mutator(node,
> +                                            replace_cached_expressions_mutator,
> +                                            NULL);
> +        op_returns_set = opexpr->opretset ||
> +            expression_returns_set((Node *) opexpr->args);
> +
> +        foreach(arg, opexpr->args)
> +        {
> +            void       *arg_lfirst = lfirst(arg);
> +            if (!(IsA(arg_lfirst, Const) || IsA(arg_lfirst, CachedExpr)))
> +                has_nonconst_or_noncached_input = true;
> +        }
> +
> +        if (op_returns_set ||
> +            has_nonconst_or_noncached_input ||
> +            contain_volatile_functions((Node *) &opexpr->xpr))
> +        {
> +            /* return OpExpr, which will not be cached */
> +            return (Node *) opexpr;
> +        }
> +        else
> +        {
> +            /* create and return CachedExpr */
> +            CachedExpr *new_node = makeNode(CachedExpr);
> +            new_node->subexprtype = CACHED_OPEXPR;
> +            new_node->subexpr.opexpr = opexpr;
> +
> +            return (Node *) new_node;
> +        }
> +    }
> +    else if (IsA(node, DistinctExpr))
> +    {
> +        /*
> +         * Operator of DistinctExpr is cached if:
> +         * 1) its function doesn't return set,
> +         * 1) its function is not volatile itself,
> +         * 3) its arguments are constants or cached expressions too.
> +         */
> +        DistinctExpr *distinctexpr = (DistinctExpr *) node;
> +        ListCell   *arg;
> +        bool        has_nonconst_or_noncached_input = false;
> +        bool        op_returns_set;
> +
> +        /* rely on struct equivalence to treat these all alike */
> +        set_opfuncid((OpExpr *) distinctexpr);
> +
> +        /* firstly recurse into children */
> +        distinctexpr = (DistinctExpr *) expression_tree_mutator(node,
> +                                            replace_cached_expressions_mutator,
> +                                            NULL);
> +        op_returns_set = distinctexpr->opretset ||
> +            expression_returns_set((Node *) distinctexpr->args);
> +
> +        foreach(arg, distinctexpr->args)
> +        {
> +            void       *arg_lfirst = lfirst(arg);
> +            if (!(IsA(arg_lfirst, Const) || IsA(arg_lfirst, CachedExpr)))
> +                has_nonconst_or_noncached_input = true;
> +        }
> +
> +        if (op_returns_set ||
> +            has_nonconst_or_noncached_input ||
> +            contain_volatile_functions((Node *) &distinctexpr->xpr))
> +        {
> +            /* return DistinctExpr, which will not be cached */
> +            return (Node *) distinctexpr;
> +        }
> +        else
> +        {
> +            /* create and return CachedExpr */
> +            CachedExpr *new_node = makeNode(CachedExpr);
> +            new_node->subexprtype = CACHED_DISTINCTEXPR;
> +            new_node->subexpr.distinctexpr = distinctexpr;
> +
> +            return (Node *) new_node;
> +        }
> +    }
> +    else if (IsA(node, NullIfExpr))
> +    {
> +        /*
> +         * Operator of NullIfExpr is cached if:
> +         * 1) its function doesn't return set,
> +         * 1) its function is not volatile itself,
> +         * 3) its arguments are constants or cached expressions too.
> +         */
> +        NullIfExpr *nullifexpr = (NullIfExpr *) node;
> +        ListCell   *arg;
> +        bool        has_nonconst_or_noncached_input = false;
> +        bool        op_returns_set;
> +
> +        /* rely on struct equivalence to treat these all alike */
> +        set_opfuncid((OpExpr *) nullifexpr);
> +
> +        /* firstly recurse into children */
> +        nullifexpr = (NullIfExpr *) expression_tree_mutator(node,
> +                                            replace_cached_expressions_mutator,
> +                                            NULL);
> +        op_returns_set = nullifexpr->opretset ||
> +            expression_returns_set((Node *) nullifexpr->args);
> +
> +        foreach(arg, nullifexpr->args)
> +        {
> +            void       *arg_lfirst = lfirst(arg);
> +            if (!(IsA(arg_lfirst, Const) || IsA(arg_lfirst, CachedExpr)))
> +                has_nonconst_or_noncached_input = true;
> +        }
> +
> +        if (op_returns_set ||
> +            has_nonconst_or_noncached_input ||
> +            contain_volatile_functions((Node *) &nullifexpr->xpr))
> +        {
> +            /* return NullIfExpr, which will not be cached */
> +            return (Node *) nullifexpr;
> +        }
> +        else
> +        {
> +            /* create and return CachedExpr */
> +            CachedExpr *new_node = makeNode(CachedExpr);
> +            new_node->subexprtype = CACHED_NULLIFEXPR;
> +            new_node->subexpr.nullifexpr = nullifexpr;
> +
> +            return (Node *) new_node;
> +        }
> +    }
> +    else if (IsA(node, ScalarArrayOpExpr))
> +    {
> +        /*
> +         * Operator of ScalarArrayOpExpr is cached if:
> +         * 1) its function is not volatile itself,
> +         * 2) its arguments are constants or cached expressions too.
> +         * (it returns boolean so we don't need to check if it returns set)
> +         */
> +        ScalarArrayOpExpr *saopexpr = (ScalarArrayOpExpr *) node;
> +        ListCell   *arg;
> +        bool        has_nonconst_or_noncached_input = false;
> +
> +        set_sa_opfuncid(saopexpr);
> +
> +        /* firstly recurse into children */
> +        saopexpr = (ScalarArrayOpExpr *) expression_tree_mutator(node,
> +                                            replace_cached_expressions_mutator,
> +                                            NULL);
> +
> +        foreach(arg, saopexpr->args)
> +        {
> +            void       *arg_lfirst = lfirst(arg);
> +            if (!(IsA(arg_lfirst, Const) || IsA(arg_lfirst, CachedExpr)))
> +                has_nonconst_or_noncached_input = true;
> +        }
> +
> +        if (has_nonconst_or_noncached_input ||
> +            contain_volatile_functions((Node *) &saopexpr->xpr))
> +        {
> +            /* return ScalarArrayOpExpr, which will not be cached */
> +            return (Node *) saopexpr;
> +        }
> +        else
> +        {
> +            /* create and return CachedExpr */
> +            CachedExpr *new_node = makeNode(CachedExpr);
> +            new_node->subexprtype = CACHED_SCALARARRAYOPEXPR;
> +            new_node->subexpr.saopexpr = saopexpr;
> +
> +            return (Node *) new_node;
> +        }
> +    }
> +
> +    /* otherwise recurse into children */
> +    return expression_tree_mutator(node, replace_cached_expressions_mutator,
> +                                   NULL);
> +}
> diff --git a/src/include/nodes/nodeFuncs.h b/src/include/nodes/nodeFuncs.h
> index b6c9b48..0dbfa12 100644
> --- a/src/include/nodes/nodeFuncs.h
> +++ b/src/include/nodes/nodeFuncs.h
> @@ -76,5 +76,6 @@ extern bool raw_expression_tree_walker(Node *node, bool (*walker) (),
>  struct PlanState;
>  extern bool planstate_tree_walker(struct PlanState *planstate, bool (*walker) (),
>                                                void *context);
> +extern Node * get_subexpr(CachedExpr *cachedexpr);
>
>  #endif   /* NODEFUNCS_H */
> diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
> index f59d719..054bc61 100644
> --- a/src/include/nodes/nodes.h
> +++ b/src/include/nodes/nodes.h
> @@ -155,6 +155,7 @@ typedef enum NodeTag
>      T_OpExpr,
>      T_DistinctExpr,
>      T_NullIfExpr,
> +    T_CachedExpr,
>      T_ScalarArrayOpExpr,
>      T_BoolExpr,
>      T_SubLink,
> diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
> index 86ec82e..3f89653 100644
> --- a/src/include/nodes/primnodes.h
> +++ b/src/include/nodes/primnodes.h
> @@ -1498,4 +1498,42 @@ typedef struct OnConflictExpr
>      List       *exclRelTlist;    /* tlist of the EXCLUDED pseudo relation */
>  } OnConflictExpr;
>
> +/*
> + * Discriminator for CachedExpr.
> + *
> + * Identifies the subexpression to be cached in execution (= executed only once
> + * and then used cached value) and which member in the CachedExpr->subexpr union
> + * is valid.
> + */
> +typedef enum CachedSubExprType
> +{
> +    CACHED_FUNCEXPR,            /* cached FuncExpr */
> +    CACHED_OPEXPR,                /* cached OpExpr */
> +    CACHED_DISTINCTEXPR,        /* cached DistinctExpr */
> +    CACHED_NULLIFEXPR,            /* cached NullIfExpr */
> +    CACHED_SCALARARRAYOPEXPR    /* cached ScalarArrayOpExpr */
> +} CachedSubExprType;
> +
> +/*
> + * CachedExpr - expression node for precalculated stable and immutable functions
> + * (= they are calculated once for all output rows, but as many times as
> + * function is mentioned in query), if they don't return a set and their
> + * arguments are constants or recursively precalculated functions. The same for
> + * operators' functions.
> + */
> +typedef struct CachedExpr
> +{
> +    Expr        xpr;
> +    CachedSubExprType subexprtype;  /* expression to be cached */
> +
> +    union SubExpr
> +    {
> +        FuncExpr   *funcexpr;    /* for CACHED_FUNCEXPR */
> +        OpExpr     *opexpr;        /* for CACHED_OPEXPR */
> +        DistinctExpr *distinctexpr; /* for CACHED_DISTINCTEXPR */
> +        NullIfExpr *nullifexpr; /* for CACHED_NULLIFEXPR */
> +        ScalarArrayOpExpr *saopexpr;    /* for CACHED_SCALARARRAYOPEXPR */
> +    } subexpr;
> +} CachedExpr;
> +
>  #endif   /* PRIMNODES_H */
> --
> 1.9.1
>

> From 537d8a2bb085efdfce695f148e614ed4611f9a6e Mon Sep 17 00:00:00 2001
> From: Marina Polyakova <m.polyakova@postgrespro.ru>
> Date: Mon, 15 May 2017 15:31:21 +0300
> Subject: [PATCH v4 2/3] Precalculate stable functions, planning and execution
>
> Now in Postgresql only immutable functions are precalculated; stable functions
> are calculated for every row so in fact they don't differ from volatile
> functions.
>
> This patch includes:
> - replacement nonvolatile functions and operators by appropriate cached
> expressions
> - planning and execution cached expressions
> - regression tests
> ---
>  src/backend/executor/execExpr.c                    |   55 +
>  src/backend/executor/execExprInterp.c              |   51 +
>  src/backend/optimizer/path/allpaths.c              |    9 +-
>  src/backend/optimizer/path/clausesel.c             |   13 +
>  src/backend/optimizer/plan/planagg.c               |    1 +
>  src/backend/optimizer/plan/planner.c               |   28 +
>  src/backend/optimizer/util/clauses.c               |   55 +
>  src/backend/utils/adt/ruleutils.c                  |    5 +
>  src/include/executor/execExpr.h                    |   37 +
>  src/include/optimizer/planner.h                    |    3 +
>  src/include/optimizer/tlist.h                      |    8 +-
>  src/pl/plpgsql/src/pl_exec.c                       |   10 +
>  .../expected/precalculate_stable_functions.out     | 2625 ++++++++++++++++++++
>  src/test/regress/serial_schedule                   |    1 +
>  .../regress/sql/precalculate_stable_functions.sql  |  949 +++++++
>  15 files changed, 3847 insertions(+), 3 deletions(-)
>  create mode 100644 src/test/regress/expected/precalculate_stable_functions.out
>  create mode 100644 src/test/regress/sql/precalculate_stable_functions.sql
>
> diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
> index 5a34a46..dc84975 100644
> --- a/src/backend/executor/execExpr.c
> +++ b/src/backend/executor/execExpr.c
> @@ -865,6 +865,61 @@ ExecInitExprRec(Expr *node, PlanState *parent, ExprState *state,
>                  break;
>              }
>
> +        case T_CachedExpr:
> +            {
> +                CachedExpr *cachedexpr = (CachedExpr *) node;
> +
> +                /*
> +                 * Allocate CachedExprState used by all steps of CachedExpr
> +                 * evaluation.
> +                 */
> +                scratch.d.cachedexpr.state = (CachedExprState *) palloc(
> +                    sizeof(CachedExprState));
> +                scratch.d.cachedexpr.state->isExecuted = false;
> +                scratch.d.cachedexpr.state->resnull = false;
> +                scratch.d.cachedexpr.state->resvalue = (Datum) 0;
> +
> +                switch(cachedexpr->subexprtype)
> +                {
> +                    case CACHED_FUNCEXPR:
> +                        scratch.d.cachedexpr.state->restypid =
> +                            cachedexpr->subexpr.funcexpr->funcresulttype;
> +                        break;
> +                    case CACHED_OPEXPR:
> +                        scratch.d.cachedexpr.state->restypid =
> +                            cachedexpr->subexpr.opexpr->opresulttype;
> +                        break;
> +                    case CACHED_DISTINCTEXPR:
> +                        scratch.d.cachedexpr.state->restypid =
> +                            cachedexpr->subexpr.distinctexpr->opresulttype;
> +                        break;
> +                    case CACHED_NULLIFEXPR:
> +                        scratch.d.cachedexpr.state->restypid =
> +                            cachedexpr->subexpr.nullifexpr->opresulttype;
> +                        break;
> +                    case CACHED_SCALARARRAYOPEXPR:
> +                        scratch.d.cachedexpr.state->restypid = BOOLOID;
> +                        break;
> +                }
> +
> +                /* add EEOP_CACHEDEXPR_IF_CACHED step */
> +                scratch.opcode = EEOP_CACHEDEXPR_IF_CACHED;
> +                ExprEvalPushStep(state, &scratch);
> +
> +                /* add subexpression steps */
> +                ExecInitExprRec((Expr *) get_subexpr(cachedexpr), parent, state,
> +                                resv, resnull);
> +
> +                /* add EEOP_CACHEDEXPR_SUBEXPR_END step */
> +                scratch.opcode = EEOP_CACHEDEXPR_SUBEXPR_END;
> +                ExprEvalPushStep(state, &scratch);
> +
> +                /* adjust jump target */
> +                scratch.d.cachedexpr.state->jumpdone = state->steps_len;
> +
> +                break;
> +            }
> +
>          case T_ScalarArrayOpExpr:
>              {
>                  ScalarArrayOpExpr *opexpr = (ScalarArrayOpExpr *) node;
> diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
> index fed0052..2cb10fd 100644
> --- a/src/backend/executor/execExprInterp.c
> +++ b/src/backend/executor/execExprInterp.c
> @@ -70,6 +70,7 @@
>  #include "pgstat.h"
>  #include "utils/builtins.h"
>  #include "utils/date.h"
> +#include "utils/datum.h"
>  #include "utils/lsyscache.h"
>  #include "utils/timestamp.h"
>  #include "utils/typcache.h"
> @@ -309,6 +310,8 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
>          &&CASE_EEOP_FUNCEXPR_STRICT,
>          &&CASE_EEOP_FUNCEXPR_FUSAGE,
>          &&CASE_EEOP_FUNCEXPR_STRICT_FUSAGE,
> +        &&CASE_EEOP_CACHEDEXPR_IF_CACHED,
> +        &&CASE_EEOP_CACHEDEXPR_SUBEXPR_END,
>          &&CASE_EEOP_BOOL_AND_STEP_FIRST,
>          &&CASE_EEOP_BOOL_AND_STEP,
>          &&CASE_EEOP_BOOL_AND_STEP_LAST,
> @@ -721,6 +724,54 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
>              EEO_NEXT();
>          }
>
> +        EEO_CASE(EEOP_CACHEDEXPR_IF_CACHED)
> +        {
> +            if (op->d.cachedexpr.state->isExecuted)
> +            {
> +                /* use saved result and skip subexpression evaluation */
> +                *op->resnull = op->d.cachedexpr.state->resnull;
> +                if (!(*op->resnull))
> +                    *op->resvalue = op->d.cachedexpr.state->resvalue;
> +
> +                EEO_JUMP(op->d.cachedexpr.state->jumpdone);
> +            }
> +
> +            /* we are ready for subexpression evaluation */
> +            EEO_NEXT();
> +        }
> +
> +        EEO_CASE(EEOP_CACHEDEXPR_SUBEXPR_END)
> +        {
> +            int16        restyplen;
> +            bool        restypbyval;
> +
> +            /* save result */
> +            op->d.cachedexpr.state->resnull = *op->resnull;
> +            if (!(*op->resnull))
> +            {
> +                get_typlenbyval(op->d.cachedexpr.state->restypid, &restyplen,
> +                                &restypbyval);
> +
> +                /*
> +                 * Switch per-query memory context. It is necessary to save the
> +                 * subexpression result value between all tuples if its datum is
> +                 * a pointer.
> +                 */
> +                op->d.cachedexpr.state->oldContext = MemoryContextSwitchTo(
> +                    econtext->ecxt_per_query_memory);
> +
> +                op->d.cachedexpr.state->resvalue = datumCopy(*op->resvalue,
> +                                                             restypbyval,
> +                                                             restyplen);
> +
> +                /* switch memory context back */
> +                MemoryContextSwitchTo(op->d.cachedexpr.state->oldContext);
> +            }
> +            op->d.cachedexpr.state->isExecuted = true;
> +
> +            EEO_NEXT();
> +        }
> +
>          /*
>           * If any of its clauses is FALSE, an AND's result is FALSE regardless
>           * of the states of the rest of the clauses, so we can stop evaluating
> diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
> index b93b4fc..a322255 100644
> --- a/src/backend/optimizer/path/allpaths.c
> +++ b/src/backend/optimizer/path/allpaths.c
> @@ -378,7 +378,11 @@ set_rel_size(PlannerInfo *root, RelOptInfo *rel,
>                  set_subquery_pathlist(root, rel, rti, rte);
>                  break;
>              case RTE_FUNCTION:
> -                set_function_size_estimates(root, rel);
> +                {
> +                    rel->baserestrictinfo = replace_qual_cached_expressions(
> +                        rel->baserestrictinfo);
> +                    set_function_size_estimates(root, rel);
> +                }
>                  break;
>              case RTE_TABLEFUNC:
>                  set_tablefunc_size_estimates(root, rel);
> @@ -517,6 +521,9 @@ set_plain_rel_size(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
>       */
>      check_index_predicates(root, rel);
>
> +    rel->baserestrictinfo = replace_qual_cached_expressions(
> +        rel->baserestrictinfo);
> +
>      /* Mark rel with estimated output rows, width, etc */
>      set_baserel_size_estimates(root, rel);
>  }
> diff --git a/src/backend/optimizer/path/clausesel.c b/src/backend/optimizer/path/clausesel.c
> index 758ddea..fc799f1 100644
> --- a/src/backend/optimizer/path/clausesel.c
> +++ b/src/backend/optimizer/path/clausesel.c
> @@ -15,6 +15,7 @@
>  #include "postgres.h"
>
>  #include "nodes/makefuncs.h"
> +#include "nodes/nodeFuncs.h"
>  #include "optimizer/clauses.h"
>  #include "optimizer/cost.h"
>  #include "optimizer/pathnode.h"
> @@ -825,6 +826,18 @@ clause_selectivity(PlannerInfo *root,
>                                  jointype,
>                                  sjinfo);
>      }
> +    else if (IsA(clause, CachedExpr))
> +    {
> +        /*
> +         * Not sure this case is needed, but it can't hurt.
> +         * Calculate selectivity of subexpression.
> +         */
> +        s1 = clause_selectivity(root,
> +                                get_subexpr((CachedExpr *) clause),
> +                                varRelid,
> +                                jointype,
> +                                sjinfo);
> +    }
>      else
>      {
>          /*
> diff --git a/src/backend/optimizer/plan/planagg.c b/src/backend/optimizer/plan/planagg.c
> index 5565736..7a28764 100644
> --- a/src/backend/optimizer/plan/planagg.c
> +++ b/src/backend/optimizer/plan/planagg.c
> @@ -38,6 +38,7 @@
>  #include "optimizer/pathnode.h"
>  #include "optimizer/paths.h"
>  #include "optimizer/planmain.h"
> +#include "optimizer/planner.h"
>  #include "optimizer/subselect.h"
>  #include "optimizer/tlist.h"
>  #include "parser/parsetree.h"
> diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
> index 552b73d..7c68d6d 100644
> --- a/src/backend/optimizer/plan/planner.c
> +++ b/src/backend/optimizer/plan/planner.c
> @@ -6088,6 +6088,34 @@ get_partitioned_child_rels(PlannerInfo *root, Index rti)
>      return result;
>  }
>
> +/*
> + * replace_pathtarget_cached_expressions
> + *        Replace cached expresisons in a PathTarget tlist.
> + *
> + * As a notational convenience, returns the same PathTarget pointer passed in.
> + */
> +PathTarget *
> +replace_pathtarget_cached_expressions(PathTarget *target)
> +{
> +    target->exprs = (List *) replace_cached_expressions_mutator(
> +        (Node *) target->exprs);
> +
> +    return target;
> +}
> +
> +/*
> + * replace_qual_cached_expressions
> + *        Replace cacehd expressions in a WHERE clause. The input can be either an
> + *        implicitly-ANDed list of boolean expressions, or a list of RestrictInfo
> + *        nodes.
> + */
> +List *
> +replace_qual_cached_expressions(List *quals)
> +{
> +    /* No setup needed for tree walk, so away we go */
> +    return (List *) replace_cached_expressions_mutator((Node *) quals);
> +}
> +
>  static Node *
>  replace_cached_expressions_mutator(Node *node)
>  {
> diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
> index a1dafc8..0c0284a 100644
> --- a/src/backend/optimizer/util/clauses.c
> +++ b/src/backend/optimizer/util/clauses.c
> @@ -2758,6 +2758,61 @@ eval_const_expressions_mutator(Node *node,
>                  newexpr->location = expr->location;
>                  return (Node *) newexpr;
>              }
> +        case T_CachedExpr:
> +            {
> +                CachedExpr *cachedexpr = (CachedExpr *) node;
> +                Node       *new_subexpr = eval_const_expressions_mutator(
> +                    get_subexpr(cachedexpr), context);
> +                CachedExpr *new_cachedexpr;
> +
> +                /*
> +                 * If unsafe transformations are used cached expression should
> +                 * be always simplified.
> +                 */
> +                if (context->estimate)
> +                    Assert(IsA(new_subexpr, Const));
> +
> +                if (IsA(new_subexpr, Const))
> +                {
> +                    /* successfully simplified it */
> +                    return new_subexpr;
> +                }
> +                else
> +                {
> +                    /*
> +                     * The expression cannot be simplified any further, so build
> +                     * and return a replacement CachedExpr node using the
> +                     * possibly-simplified arguments of subexpression.
> +                     */
> +                    new_cachedexpr = makeNode(CachedExpr);
> +                    new_cachedexpr->subexprtype = cachedexpr->subexprtype;
> +                    switch (new_cachedexpr->subexprtype)
> +                    {
> +                        case CACHED_FUNCEXPR:
> +                            new_cachedexpr->subexpr.funcexpr = (FuncExpr *)
> +                                new_subexpr;
> +                            break;
> +                        case CACHED_OPEXPR:
> +                            new_cachedexpr->subexpr.opexpr = (OpExpr *)
> +                                new_subexpr;
> +                            break;
> +                        case CACHED_DISTINCTEXPR:
> +                            new_cachedexpr->subexpr.distinctexpr =
> +                                (DistinctExpr *) new_subexpr;
> +                            break;
> +                        case CACHED_NULLIFEXPR:
> +                            new_cachedexpr->subexpr.nullifexpr = (NullIfExpr *)
> +                                new_subexpr;
> +                            break;
> +                        case CACHED_SCALARARRAYOPEXPR:
> +                            new_cachedexpr->subexpr.saopexpr =
> +                                (ScalarArrayOpExpr *) new_subexpr;
> +                            break;
> +                    }
> +
> +                    return (Node *) new_cachedexpr;
> +                }
> +            }
>          case T_BoolExpr:
>              {
>                  BoolExpr   *expr = (BoolExpr *) node;
> diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
> index 43b1475..838389d 100644
> --- a/src/backend/utils/adt/ruleutils.c
> +++ b/src/backend/utils/adt/ruleutils.c
> @@ -7720,6 +7720,11 @@ get_rule_expr(Node *node, deparse_context *context,
>              }
>              break;
>
> +        case T_CachedExpr:
> +            get_rule_expr(get_subexpr((CachedExpr *) node), context,
> +                          showimplicit);
> +            break;
> +
>          case T_ScalarArrayOpExpr:
>              {
>                  ScalarArrayOpExpr *expr = (ScalarArrayOpExpr *) node;
> diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h
> index 86fdb33..ea37a36 100644
> --- a/src/include/executor/execExpr.h
> +++ b/src/include/executor/execExpr.h
> @@ -86,6 +86,16 @@ typedef enum ExprEvalOp
>      EEOP_FUNCEXPR_STRICT_FUSAGE,
>
>      /*
> +     * Evaluate CachedExpr.  EEOP_CACHEDEXPR_IF_CACHED is used before
> +     * subexpression evaluation (if subexpression was evaluated use cached value
> +     * and jump to next state or get prepared to subexpression evaluation
> +     * otherwise).  EEOP_CACHEDEXPR_SUBEXPR_END is used after subexpression
> +     * evaluation for caching its result.
> +     */
> +    EEOP_CACHEDEXPR_IF_CACHED,
> +    EEOP_CACHEDEXPR_SUBEXPR_END,
> +
> +    /*
>       * Evaluate boolean AND expression, one step per subexpression. FIRST/LAST
>       * subexpressions are special-cased for performance.  Since AND always has
>       * at least two subexpressions, FIRST and LAST never apply to the same
> @@ -298,6 +308,13 @@ typedef struct ExprEvalStep
>              int            nargs;    /* number of arguments */
>          }            func;
>
> +        /* for EEOP_CACHEDEXPR_* */
> +        struct
> +        {
> +            /* steps for evaluation the same CachedExpr have the same state */
> +            struct CachedExprState *state;
> +        }            cachedexpr;
> +
>          /* for EEOP_BOOL_*_STEP */
>          struct
>          {
> @@ -600,6 +617,26 @@ typedef struct ArrayRefState
>  } ArrayRefState;
>
>
> +/*
> + * Non-inline data for EEOP_CACHEDEXPR_* operations (steps for evaluation the
> + * same CachedExpr have the same state).
> + */
> +typedef struct CachedExprState
> +{
> +    bool        isExecuted;
> +    bool        resnull;
> +    Datum        resvalue;
> +    Oid         restypid;        /* for copying resvalue of subexpression */
> +    int            jumpdone;        /* jump here if result determined */
> +
> +    /*
> +     * For switching per-query memory context. It is necessary to save the
> +     * subexpression result between all tuples if its value datum is a pointer.
> +     */
> +    MemoryContext oldContext;
> +} CachedExprState;
> +
> +
>  extern void ExecReadyInterpretedExpr(ExprState *state);
>
>  extern ExprEvalOp ExecEvalStepOp(ExprState *state, ExprEvalStep *op);
> diff --git a/src/include/optimizer/planner.h b/src/include/optimizer/planner.h
> index f3aaa23..bbadcdd 100644
> --- a/src/include/optimizer/planner.h
> +++ b/src/include/optimizer/planner.h
> @@ -59,4 +59,7 @@ extern bool plan_cluster_use_sort(Oid tableOid, Oid indexOid);
>
>  extern List *get_partitioned_child_rels(PlannerInfo *root, Index rti);
>
> +extern PathTarget *replace_pathtarget_cached_expressions(PathTarget *target);
> +extern List *replace_qual_cached_expressions(List *quals);
> +
>  #endif   /* PLANNER_H */
> diff --git a/src/include/optimizer/tlist.h b/src/include/optimizer/tlist.h
> index ccb93d8..7488bd2 100644
> --- a/src/include/optimizer/tlist.h
> +++ b/src/include/optimizer/tlist.h
> @@ -65,8 +65,12 @@ extern void split_pathtarget_at_srfs(PlannerInfo *root,
>                           PathTarget *target, PathTarget *input_target,
>                           List **targets, List **targets_contain_srfs);
>
> -/* Convenience macro to get a PathTarget with valid cost/width fields */
> +/*
> + * Convenience macro to get a PathTarget with valid cost/width fields and
> + * cached expressions.
> + */
>  #define create_pathtarget(root, tlist) \
> -    set_pathtarget_cost_width(root, make_pathtarget_from_tlist(tlist))
> +    set_pathtarget_cost_width(root, replace_pathtarget_cached_expressions( \
> +        make_pathtarget_from_tlist(tlist)))
>
>  #endif   /* TLIST_H */
> diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
> index 7a40c99..2e27052 100644
> --- a/src/pl/plpgsql/src/pl_exec.c
> +++ b/src/pl/plpgsql/src/pl_exec.c
> @@ -6535,6 +6535,16 @@ exec_simple_check_node(Node *node)
>                  return TRUE;
>              }
>
> +        case T_CachedExpr:
> +            {
> +                /*
> +                 * If CachedExpr will not be initialized by ExecInitCachedExpr
> +                 * possibly it will use cached value when it shouldn't (for
> +                 * example, snapshot has changed), so return false.
> +                 */
> +                return FALSE;
> +            }
> +
>          case T_ScalarArrayOpExpr:
>              {
>                  ScalarArrayOpExpr *expr = (ScalarArrayOpExpr *) node;
> diff --git a/src/test/regress/expected/precalculate_stable_functions.out
b/src/test/regress/expected/precalculate_stable_functions.out
> new file mode 100644
> index 0000000..093e6f8
> --- /dev/null
> +++ b/src/test/regress/expected/precalculate_stable_functions.out
> @@ -0,0 +1,2625 @@
> +--
> +-- PRECALCULATE STABLE FUNCTIONS
> +--
> +-- Create types and tables for testing
> +CREATE TYPE my_integer AS (value integer);
> +CREATE TABLE two (i integer);
> +INSERT INTO two VALUES (1), (2);
> +-- Create volatile functions for testing
> +CREATE OR REPLACE FUNCTION public.x_vlt (
> +)
> +RETURNS integer VOLATILE AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 'v';
> +  RETURN 1;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +CREATE OR REPLACE FUNCTION public.equal_integers_vlt (
> +  integer,
> +  integer
> +)
> +RETURNS boolean VOLATILE AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 'equal integers volatile';
> +  RETURN $1 = $2;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +CREATE OR REPLACE FUNCTION public.x_vlt_my_integer (
> +)
> +RETURNS my_integer VOLATILE AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 'v my_integer';
> +  RETURN '(1)'::my_integer;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +CREATE OR REPLACE FUNCTION public.equal_my_integer_vlt (
> +  my_integer,
> +  my_integer
> +)
> +RETURNS boolean VOLATILE AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 'equal my_integer volatile';
> +  RETURN $1.value = $2.value;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +CREATE OR REPLACE FUNCTION public.x_vlt_array_int (
> +)
> +RETURNS int[] VOLATILE AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 'v array_int';
> +  RETURN '{2, 3}'::int[];
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +-- Create stable functions for testing
> +CREATE OR REPLACE FUNCTION public.x_stl (
> +)
> +RETURNS integer STABLE AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 's';
> +  RETURN 1;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +CREATE OR REPLACE FUNCTION public.x_stl2 (
> +     integer
> +)
> +RETURNS integer STABLE AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 's2';
> +  RETURN $1;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +CREATE OR REPLACE FUNCTION public.x_stl2_strict (
> +     integer
> +)
> +RETURNS integer STABLE STRICT AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 's2 strict';
> +  RETURN $1;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +CREATE OR REPLACE FUNCTION public.equal_integers_stl (
> +  integer,
> +  integer
> +)
> +RETURNS boolean STABLE AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 'equal integers stable';
> +  RETURN $1 = $2;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +CREATE OR REPLACE FUNCTION public.x_stl2_boolean (
> +  boolean
> +)
> +RETURNS boolean STABLE AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 's2 boolean';
> +  RETURN $1;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +CREATE OR REPLACE FUNCTION public.equal_booleans_stl_strict (
> +  boolean,
> +  boolean
> +)
> +RETURNS boolean STABLE STRICT AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 'equal booleans stable strict';
> +  RETURN $1 = $2;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +CREATE OR REPLACE FUNCTION public.x_stl_my_integer (
> +)
> +RETURNS my_integer STABLE AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 's my_integer';
> +  RETURN '(1)'::my_integer;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +CREATE OR REPLACE FUNCTION public.equal_my_integer_stl (
> +  my_integer,
> +  my_integer
> +)
> +RETURNS boolean STABLE AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 'equal my_integer stable';
> +  RETURN $1.value = $2.value;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +CREATE OR REPLACE FUNCTION public.x_stl_array_int (
> +)
> +RETURNS int[] STABLE AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 's array_int';
> +  RETURN '{2, 3}'::int[];
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +CREATE OR REPLACE FUNCTION public.stable_max(
> +)
> +RETURNS integer STABLE AS
> +$body$
> +BEGIN
> +  RETURN (SELECT max(i) from two);
> +END
> +$body$
> +LANGUAGE 'plpgsql';
> +CREATE OR REPLACE FUNCTION public.simple(
> +)
> +RETURNS integer STABLE AS
> +$body$
> +BEGIN
> +  RETURN stable_max();
> +END
> +$body$
> +LANGUAGE 'plpgsql';
> +-- Create immutable functions for testing
> +CREATE OR REPLACE FUNCTION public.x_imm2 (
> +     integer
> +)
> +RETURNS integer IMMUTABLE AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 'i2';
> +  RETURN $1;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +CREATE OR REPLACE FUNCTION public.x_imm2_strict (
> +     integer
> +)
> +RETURNS integer IMMUTABLE STRICT AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 'i2 strict';
> +  RETURN $1;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +CREATE OR REPLACE FUNCTION public.equal_integers_imm (
> +  integer,
> +  integer
> +)
> +RETURNS boolean IMMUTABLE AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 'equal integers immutable';
> +  RETURN $1 = $2;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +CREATE OR REPLACE FUNCTION public.equal_my_integer_imm (
> +  my_integer,
> +  my_integer
> +)
> +RETURNS boolean IMMUTABLE AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 'equal my_integer immutable';
> +  RETURN $1.value = $2.value;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +-- Create operators for testing
> +CREATE operator === (
> +  PROCEDURE = equal_integers_vlt,
> +  LEFTARG = integer,
> +  RIGHTARG = integer
> +);
> +CREATE operator ==== (
> +  PROCEDURE = equal_integers_stl,
> +  LEFTARG = integer,
> +  RIGHTARG = integer
> +);
> +CREATE operator ===== (
> +  PROCEDURE = equal_integers_imm,
> +  LEFTARG = integer,
> +  RIGHTARG = integer
> +);
> +CREATE operator ====== (
> +  PROCEDURE = equal_booleans_stl_strict,
> +  LEFTARG = boolean,
> +  RIGHTARG = boolean
> +);
> +CREATE operator ==== (
> +  PROCEDURE = equal_my_integer_stl,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +-- Simple functions testing
> +SELECT x_vlt() FROM generate_series(1, 3) x; -- should not be precalculated
> +NOTICE:  v
> +NOTICE:  v
> +NOTICE:  v
> + x_vlt
> +-------
> +     1
> +     1
> +     1
> +(3 rows)
> +
> +SELECT x_stl() FROM generate_series(1, 3) x;
> +NOTICE:  s
> + x_stl
> +-------
> +     1
> +     1
> +     1
> +(3 rows)
> +
> +-- WHERE clause testing
> +SELECT x_vlt() FROM generate_series(1, 4) x WHERE x_vlt() < x; -- should not be precalculated
> +NOTICE:  v
> +NOTICE:  v
> +NOTICE:  v
> +NOTICE:  v
> +NOTICE:  v
> +NOTICE:  v
> +NOTICE:  v
> + x_vlt
> +-------
> +     1
> +     1
> +     1
> +(3 rows)
> +
> +SELECT x_stl() FROM generate_series(1, 4) x WHERE x_stl() < x;
> +NOTICE:  s
> +NOTICE:  s
> +NOTICE:  s
> + x_stl
> +-------
> +     1
> +     1
> +     1
> +(3 rows)
> +
> +-- Functions with constant arguments and nested functions testing
> +SELECT x_stl2(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
> +NOTICE:  v
> +NOTICE:  s2
> +NOTICE:  v
> +NOTICE:  s2
> +NOTICE:  v
> +NOTICE:  s2
> +NOTICE:  v
> +NOTICE:  s2
> + x_stl2
> +--------
> +      1
> +      1
> +      1
> +      1
> +(4 rows)
> +
> +SELECT x_imm2(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
> +NOTICE:  v
> +NOTICE:  i2
> +NOTICE:  v
> +NOTICE:  i2
> +NOTICE:  v
> +NOTICE:  i2
> +NOTICE:  v
> +NOTICE:  i2
> + x_imm2
> +--------
> +      1
> +      1
> +      1
> +      1
> +(4 rows)
> +
> +SELECT x_stl2(x_stl2(1)) FROM generate_series(1, 4) x;
> +NOTICE:  s2
> +NOTICE:  s2
> + x_stl2
> +--------
> +      1
> +      1
> +      1
> +      1
> +(4 rows)
> +
> +SELECT x_imm2(x_stl2(1)) FROM generate_series(1, 4) x;
> +NOTICE:  s2
> +NOTICE:  i2
> + x_imm2
> +--------
> +      1
> +      1
> +      1
> +      1
> +(4 rows)
> +
> +-- Strict functions testing
> +SELECT x_stl2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
> +NOTICE:  v
> +NOTICE:  s2 strict
> +NOTICE:  v
> +NOTICE:  s2 strict
> +NOTICE:  v
> +NOTICE:  s2 strict
> +NOTICE:  v
> +NOTICE:  s2 strict
> + x_stl2_strict
> +---------------
> +             1
> +             1
> +             1
> +             1
> +(4 rows)
> +
> +SELECT x_imm2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
> +NOTICE:  v
> +NOTICE:  i2 strict
> +NOTICE:  v
> +NOTICE:  i2 strict
> +NOTICE:  v
> +NOTICE:  i2 strict
> +NOTICE:  v
> +NOTICE:  i2 strict
> + x_imm2_strict
> +---------------
> +             1
> +             1
> +             1
> +             1
> +(4 rows)
> +
> +SELECT x_stl2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x;
> +NOTICE:  s2 strict
> +NOTICE:  s2 strict
> + x_stl2_strict
> +---------------
> +             1
> +             1
> +             1
> +             1
> +(4 rows)
> +
> +SELECT x_imm2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x;
> +NOTICE:  s2 strict
> +NOTICE:  i2 strict
> + x_imm2_strict
> +---------------
> +             1
> +             1
> +             1
> +             1
> +(4 rows)
> +
> +-- Strict functions with null arguments testing
> +SELECT x_stl2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x;
> +NOTICE:  s2
> + x_stl2_strict
> +---------------
> +
> +
> +
> +
> +(4 rows)
> +
> +SELECT x_imm2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x;
> +NOTICE:  s2
> + x_imm2_strict
> +---------------
> +
> +
> +
> +
> +(4 rows)
> +
> +-- Operators testing
> +SELECT 1 === 2 FROM generate_series(1, 4) x; -- should not be precalculated
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> + ?column?
> +----------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +SELECT 1 ==== 2 FROM generate_series(1, 4) x;
> +NOTICE:  equal integers stable
> + ?column?
> +----------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +-- Nested and strict operators testing
> +-- (also partly mixed functions and operators testing)
> +SELECT (x_vlt() ==== 2) ====== (x_vlt() ===== 3) FROM generate_series(1, 4) x; -- should not be precalculated
> +NOTICE:  v
> +NOTICE:  equal integers stable
> +NOTICE:  v
> +NOTICE:  equal integers immutable
> +NOTICE:  equal booleans stable strict
> +NOTICE:  v
> +NOTICE:  equal integers stable
> +NOTICE:  v
> +NOTICE:  equal integers immutable
> +NOTICE:  equal booleans stable strict
> +NOTICE:  v
> +NOTICE:  equal integers stable
> +NOTICE:  v
> +NOTICE:  equal integers immutable
> +NOTICE:  equal booleans stable strict
> +NOTICE:  v
> +NOTICE:  equal integers stable
> +NOTICE:  v
> +NOTICE:  equal integers immutable
> +NOTICE:  equal booleans stable strict
> + ?column?
> +----------
> + t
> + t
> + t
> + t
> +(4 rows)
> +
> +SELECT (x_stl() ==== 2) ====== (x_stl() ===== 3) FROM generate_series(1, 4) x;
> +NOTICE:  s
> +NOTICE:  equal integers stable
> +NOTICE:  s
> +NOTICE:  equal integers immutable
> +NOTICE:  equal booleans stable strict
> + ?column?
> +----------
> + t
> + t
> + t
> + t
> +(4 rows)
> +
> +SELECT (1 ==== 2) ====== x_stl2_boolean(NULL) FROM generate_series(1, 4) x;
> +NOTICE:  equal integers stable
> +NOTICE:  s2 boolean
> + ?column?
> +----------
> +
> +
> +
> +
> +(4 rows)
> +
> +-- IS DISTINCT FROM expression testing
> +-- create operator here because we will drop and reuse it several times
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_vlt,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +-- should not be precalculated
> +SELECT '(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> + ?column?
> +----------
> + t
> + t
> + t
> + t
> +(4 rows)
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_stl,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +SELECT '(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer stable
> + ?column?
> +----------
> + t
> + t
> + t
> + t
> +(4 rows)
> +
> +-- IS DISTINCT FROM expressions with null arguments testing
> +SELECT x_stl2_boolean(1 IS DISTINCT FROM x_stl2(NULL))
> +FROM generate_series(1, 4) x;
> +NOTICE:  s2
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + t
> + t
> + t
> + t
> +(4 rows)
> +
> +SELECT x_stl2_boolean(x_stl2(NULL) IS DISTINCT FROM x_stl2(NULL))
> +FROM generate_series(1, 4) x;
> +NOTICE:  s2
> +NOTICE:  s2
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +-- Nested IS DISTINCT FROM expression testing
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_vlt,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +-- should not be precalculated
> +SELECT x_stl2_boolean(
> +  ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) IS DISTINCT FROM
> +  TRUE
> +)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal my_integer volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal my_integer volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal my_integer volatile
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_stl,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +SELECT x_stl2_boolean(
> +  ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) IS DISTINCT FROM
> +  TRUE
> +)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer stable
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +-- NULLIF expressions testing
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_vlt,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +-- should not be precalculated
> +SELECT NULLIF('(1)'::my_integer, '(2)'::my_integer)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> + nullif
> +--------
> + (1)
> + (1)
> + (1)
> + (1)
> +(4 rows)
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_stl,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +SELECT NULLIF('(1)'::my_integer, '(2)'::my_integer)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer stable
> + nullif
> +--------
> + (1)
> + (1)
> + (1)
> + (1)
> +(4 rows)
> +
> +-- NULLIF expressions with null arguments testing
> +SELECT x_stl2(NULLIF(1, x_stl2(NULL))) FROM generate_series(1, 4) x;
> +NOTICE:  s2
> +NOTICE:  s2
> + x_stl2
> +--------
> +      1
> +      1
> +      1
> +      1
> +(4 rows)
> +
> +SELECT x_stl2(NULLIF(x_stl2(NULL), x_stl2(NULL))) FROM generate_series(1, 4) x;
> +NOTICE:  s2
> +NOTICE:  s2
> +NOTICE:  s2
> + x_stl2
> +--------
> +
> +
> +
> +
> +(4 rows)
> +
> +-- Nested NULLIF expression testing
> +-- should not be precalculated
> +SELECT NULLIF(NULLIF(x_vlt_my_integer(), '(2)'::my_integer), '(2)'::my_integer)
> +FROM generate_series(1, 4) x;
> +NOTICE:  v my_integer
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal my_integer stable
> +NOTICE:  v my_integer
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal my_integer stable
> +NOTICE:  v my_integer
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal my_integer stable
> +NOTICE:  v my_integer
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal my_integer stable
> + nullif
> +--------
> + (1)
> + (1)
> + (1)
> + (1)
> +(4 rows)
> +
> +SELECT NULLIF(NULLIF(x_stl_my_integer(), '(2)'::my_integer), '(2)'::my_integer)
> +FROM generate_series(1, 4) x;
> +NOTICE:  s my_integer
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal my_integer stable
> + nullif
> +--------
> + (1)
> + (1)
> + (1)
> + (1)
> +(4 rows)
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_imm,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +-- should not be precalculated
> +SELECT NULLIF(NULLIF(x_vlt_my_integer(), '(2)'::my_integer), '(2)'::my_integer)
> +FROM generate_series(1, 4) x;
> +NOTICE:  v my_integer
> +NOTICE:  equal my_integer immutable
> +NOTICE:  equal my_integer immutable
> +NOTICE:  v my_integer
> +NOTICE:  equal my_integer immutable
> +NOTICE:  equal my_integer immutable
> +NOTICE:  v my_integer
> +NOTICE:  equal my_integer immutable
> +NOTICE:  equal my_integer immutable
> +NOTICE:  v my_integer
> +NOTICE:  equal my_integer immutable
> +NOTICE:  equal my_integer immutable
> + nullif
> +--------
> + (1)
> + (1)
> + (1)
> + (1)
> +(4 rows)
> +
> +SELECT NULLIF(NULLIF(x_stl_my_integer(), '(2)'::my_integer), '(2)'::my_integer)
> +FROM generate_series(1, 4) x;
> +NOTICE:  s my_integer
> +NOTICE:  equal my_integer immutable
> +NOTICE:  equal my_integer immutable
> + nullif
> +--------
> + (1)
> + (1)
> + (1)
> + (1)
> +(4 rows)
> +
> +-- "scalar op ANY/ALL (array)" / "scalar IN (2 or more values)" expressions
> +-- testing
> +SELECT 1 === ANY('{2, 3}') FROM generate_series(1, 4) x; -- should not be precalculated
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> + ?column?
> +----------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +SELECT 1 === ALL('{2, 3}') FROM generate_series(1, 4) x; -- should not be precalculated
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> + ?column?
> +----------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_vlt,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +-- should not be precalculated
> +SELECT '(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> + ?column?
> +----------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +SELECT 1 ==== ANY('{2, 3}') FROM generate_series(1, 4) x;
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> + ?column?
> +----------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +SELECT 1 ==== ALL('{2, 3}') FROM generate_series(1, 4) x;
> +NOTICE:  equal integers stable
> + ?column?
> +----------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_stl,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +SELECT '(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal my_integer stable
> + ?column?
> +----------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +-- "scalar op ANY/ALL (array)" / "scalar IN (2 or more values)" expressions with
> +-- null arguments testing
> +SELECT 1 ==== ANY('{2, NULL}') FROM generate_series(1, 4) x;
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> + ?column?
> +----------
> +
> +
> +
> +
> +(4 rows)
> +
> +SELECT x_stl2_boolean(1 ==== ANY(NULL)) FROM generate_series(1, 4) x;
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> +
> +
> +
> +
> +(4 rows)
> +
> +SELECT NULL ==== ANY('{2, 3}'::int[]) FROM generate_series(1, 4) x;
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> + ?column?
> +----------
> +
> +
> +
> +
> +(4 rows)
> +
> +SELECT NULL ==== ANY('{2, NULL}'::int[]) FROM generate_series(1, 4) x;
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> + ?column?
> +----------
> +
> +
> +
> +
> +(4 rows)
> +
> +SELECT x_stl2_boolean(NULL::int ==== ANY(NULL)) FROM generate_series(1, 4) x;
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> +
> +
> +
> +
> +(4 rows)
> +
> +SELECT 1 ==== ALL('{2, NULL}') FROM generate_series(1, 4) x;
> +NOTICE:  equal integers stable
> + ?column?
> +----------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +SELECT x_stl2_boolean(1 ==== ALL(NULL)) FROM generate_series(1, 4) x;
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> +
> +
> +
> +
> +(4 rows)
> +
> +SELECT NULL ==== ALL('{2, 3}'::int[]) FROM generate_series(1, 4) x;
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> + ?column?
> +----------
> +
> +
> +
> +
> +(4 rows)
> +
> +SELECT NULL ==== ALL('{2, NULL}'::int[]) FROM generate_series(1, 4) x;
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> + ?column?
> +----------
> +
> +
> +
> +
> +(4 rows)
> +
> +SELECT x_stl2_boolean(NULL::int ==== ALL(NULL)) FROM generate_series(1, 4) x;
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> +
> +
> +
> +
> +(4 rows)
> +
> +SELECT x_stl2_boolean(1 IN (2, NULL)) FROM generate_series(1, 4) x;
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> +
> +
> +
> +
> +(4 rows)
> +
> +SELECT x_stl2_boolean(NULL IN (2, 3)) FROM generate_series(1, 4) x;
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> +
> +
> +
> +
> +(4 rows)
> +
> +SELECT x_stl2_boolean(NULL IN (2, NULL)) FROM generate_series(1, 4) x;
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> +
> +
> +
> +
> +(4 rows)
> +
> +-- Nesting "scalar op ANY/ALL (array)" / "scalar IN (2 or more values)"
> +-- expressions testing (also partly mixed functions and "scalar op ANY/ALL
> +-- (array)" / "scalar IN (2 or more values)" expressions testing)
> +-- should not be precalculated
> +SELECT x_stl2_boolean((x_vlt() ==== ANY('{2, 3}')) = ANY('{TRUE}'))
> +FROM generate_series(1, 4) x;
> +NOTICE:  v
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> +NOTICE:  s2 boolean
> +NOTICE:  v
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> +NOTICE:  s2 boolean
> +NOTICE:  v
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> +NOTICE:  s2 boolean
> +NOTICE:  v
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean((x_vlt() ==== ANY('{2, 3}')) = ALL('{TRUE}'))
> +FROM generate_series(1, 4) x;
> +NOTICE:  v
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> +NOTICE:  s2 boolean
> +NOTICE:  v
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> +NOTICE:  s2 boolean
> +NOTICE:  v
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> +NOTICE:  s2 boolean
> +NOTICE:  v
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean((x_vlt() ==== ANY('{2, 3}')) IN (TRUE, FALSE))
> +FROM generate_series(1, 4) x;
> +NOTICE:  v
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> +NOTICE:  s2 boolean
> +NOTICE:  v
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> +NOTICE:  s2 boolean
> +NOTICE:  v
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> +NOTICE:  s2 boolean
> +NOTICE:  v
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + t
> + t
> + t
> + t
> +(4 rows)
> +
> +SELECT x_stl2_boolean((x_stl() ==== ANY('{2, 3}')) = ANY('{TRUE}'))
> +FROM generate_series(1, 4) x;
> +NOTICE:  s
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +SELECT x_stl2_boolean((x_stl() ==== ANY('{2, 3}')) = ALL('{TRUE}'))
> +FROM generate_series(1, 4) x;
> +NOTICE:  s
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +SELECT x_stl2_boolean((x_stl() ==== ANY('{2, 3}')) IN (TRUE, FALSE))
> +FROM generate_series(1, 4) x;
> +NOTICE:  s
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + t
> + t
> + t
> + t
> +(4 rows)
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean((x_vlt() ===== ANY('{2, 3}')) = ANY('{TRUE}'))
> +FROM generate_series(1, 4) x;
> +NOTICE:  v
> +NOTICE:  equal integers immutable
> +NOTICE:  equal integers immutable
> +NOTICE:  s2 boolean
> +NOTICE:  v
> +NOTICE:  equal integers immutable
> +NOTICE:  equal integers immutable
> +NOTICE:  s2 boolean
> +NOTICE:  v
> +NOTICE:  equal integers immutable
> +NOTICE:  equal integers immutable
> +NOTICE:  s2 boolean
> +NOTICE:  v
> +NOTICE:  equal integers immutable
> +NOTICE:  equal integers immutable
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean((x_vlt() ===== ANY('{2, 3}')) = ALL('{TRUE}'))
> +FROM generate_series(1, 4) x;
> +NOTICE:  v
> +NOTICE:  equal integers immutable
> +NOTICE:  equal integers immutable
> +NOTICE:  s2 boolean
> +NOTICE:  v
> +NOTICE:  equal integers immutable
> +NOTICE:  equal integers immutable
> +NOTICE:  s2 boolean
> +NOTICE:  v
> +NOTICE:  equal integers immutable
> +NOTICE:  equal integers immutable
> +NOTICE:  s2 boolean
> +NOTICE:  v
> +NOTICE:  equal integers immutable
> +NOTICE:  equal integers immutable
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean((x_vlt() ===== ANY('{2, 3}')) IN (TRUE, FALSE))
> +FROM generate_series(1, 4) x;
> +NOTICE:  v
> +NOTICE:  equal integers immutable
> +NOTICE:  equal integers immutable
> +NOTICE:  s2 boolean
> +NOTICE:  v
> +NOTICE:  equal integers immutable
> +NOTICE:  equal integers immutable
> +NOTICE:  s2 boolean
> +NOTICE:  v
> +NOTICE:  equal integers immutable
> +NOTICE:  equal integers immutable
> +NOTICE:  s2 boolean
> +NOTICE:  v
> +NOTICE:  equal integers immutable
> +NOTICE:  equal integers immutable
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + t
> + t
> + t
> + t
> +(4 rows)
> +
> +SELECT x_stl2_boolean((x_stl() ===== ANY('{2, 3}')) = ANY('{TRUE}'))
> +FROM generate_series(1, 4) x;
> +NOTICE:  s
> +NOTICE:  equal integers immutable
> +NOTICE:  equal integers immutable
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +SELECT x_stl2_boolean((x_stl() ===== ANY('{2, 3}')) = ALL('{TRUE}'))
> +FROM generate_series(1, 4) x;
> +NOTICE:  s
> +NOTICE:  equal integers immutable
> +NOTICE:  equal integers immutable
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +SELECT x_stl2_boolean((x_stl() ===== ANY('{2, 3}')) IN (TRUE, FALSE))
> +FROM generate_series(1, 4) x;
> +NOTICE:  s
> +NOTICE:  equal integers immutable
> +NOTICE:  equal integers immutable
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + t
> + t
> + t
> + t
> +(4 rows)
> +
> +-- Mixed functions and operators testing
> +-- (most of it was earlier in Nested and strict operators testing)
> +SELECT x_stl2_boolean(1 ==== 2) FROM generate_series(1, 4) x;
> +NOTICE:  equal integers stable
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +-- Mixed functions and IS DISTINCT FROM expressions testing
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_vlt,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +-- should not be precalculated
> +SELECT equal_booleans_stl_strict(
> +  (x_stl_my_integer() IS DISTINCT FROM '(1)'::my_integer),
> +  (x_stl_my_integer() IS DISTINCT FROM '(2)'::my_integer)
> +)
> +FROM generate_series(1, 4) x;
> +NOTICE:  s my_integer
> +NOTICE:  equal my_integer volatile
> +NOTICE:  s my_integer
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal booleans stable strict
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal booleans stable strict
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal booleans stable strict
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal booleans stable strict
> + equal_booleans_stl_strict
> +---------------------------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +SELECT equal_booleans_stl_strict(
> +  (x_stl() IS DISTINCT FROM 1),
> +  (x_stl() IS DISTINCT FROM 2)
> +)
> +FROM generate_series(1, 4) x;
> +NOTICE:  s
> +NOTICE:  s
> +NOTICE:  equal booleans stable strict
> + equal_booleans_stl_strict
> +---------------------------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +-- Mixed functions and NULLIF expressions testing
> +-- should not be precalculated
> +SELECT equal_my_integer_stl(
> +  NULLIF(x_stl_my_integer(), '(1)'::my_integer),
> +  NULLIF(x_stl_my_integer(), '(2)'::my_integer)
> +)
> +FROM generate_series(1, 4) x;
> +NOTICE:  s my_integer
> +NOTICE:  equal my_integer volatile
> +NOTICE:  s my_integer
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer stable
> + equal_my_integer_stl
> +----------------------
> +
> +
> +
> +
> +(4 rows)
> +
> +SELECT equal_integers_stl(NULLIF(x_stl(), 1), NULLIF(x_stl(), 2))
> +FROM generate_series(1, 4) x;
> +NOTICE:  s
> +NOTICE:  s
> +NOTICE:  equal integers stable
> + equal_integers_stl
> +--------------------
> +
> +
> +
> +
> +(4 rows)
> +
> +-- Mixed functions and "scalar op ANY/ALL (array)" / "scalar IN (2 or more
> +-- values)" expressions testing (partly in nesting "scalar op ANY/ALL (array)" /
> +-- "scalar IN (2 or more values)" expressions testing)
> +SELECT 1 ==== ANY(x_vlt_array_int()) FROM generate_series(1, 4) x; -- should not be precalculated
> +NOTICE:  v array_int
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> +NOTICE:  v array_int
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> +NOTICE:  v array_int
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> +NOTICE:  v array_int
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> + ?column?
> +----------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +SELECT 1 ==== ALL(x_vlt_array_int()) FROM generate_series(1, 4) x; -- should not be precalculated
> +NOTICE:  v array_int
> +NOTICE:  equal integers stable
> +NOTICE:  v array_int
> +NOTICE:  equal integers stable
> +NOTICE:  v array_int
> +NOTICE:  equal integers stable
> +NOTICE:  v array_int
> +NOTICE:  equal integers stable
> + ?column?
> +----------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +SELECT 1 ==== ANY(x_stl_array_int()) FROM generate_series(1, 4) x;
> +NOTICE:  s array_int
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> + ?column?
> +----------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +SELECT 1 ==== ALL(x_stl_array_int()) FROM generate_series(1, 4) x;
> +NOTICE:  s array_int
> +NOTICE:  equal integers stable
> + ?column?
> +----------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +-- Mixed operators and IS DISTINCT FROM expressions testing
> +-- should not be precalculated
> +SELECT (
> +  ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) ======
> +  ('(2)'::my_integer IS DISTINCT FROM '(2)'::my_integer)
> +)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal booleans stable strict
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal booleans stable strict
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal booleans stable strict
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal booleans stable strict
> + ?column?
> +----------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean((1 === 2) IS DISTINCT FROM TRUE)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal integers volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal integers volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal integers volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal integers volatile
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + t
> + t
> + t
> + t
> +(4 rows)
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_stl,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +SELECT (
> +  ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) ======
> +  ('(2)'::my_integer IS DISTINCT FROM '(2)'::my_integer)
> +)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal booleans stable strict
> + ?column?
> +----------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +SELECT x_stl2_boolean((1 ==== 2) IS DISTINCT FROM TRUE)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal integers stable
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + t
> + t
> + t
> + t
> +(4 rows)
> +
> +-- Mixed operators and NULLIF expressions testing
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_vlt,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +-- should not be precalculated
> +SELECT (
> +  NULLIF('(1)'::my_integer, '(2)'::my_integer) ====
> +  NULLIF('(2)'::my_integer, '(2)'::my_integer)
> +)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer stable
> + ?column?
> +----------
> +
> +
> +
> +
> +(4 rows)
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean(NULLIF(1 === 2, TRUE)) FROM generate_series(1, 4) x;
> +NOTICE:  equal integers volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal integers volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal integers volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal integers volatile
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_stl,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +SELECT (
> +  NULLIF('(1)'::my_integer, '(2)'::my_integer) ====
> +  NULLIF('(2)'::my_integer, '(2)'::my_integer)
> +)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal my_integer stable
> + ?column?
> +----------
> +
> +
> +
> +
> +(4 rows)
> +
> +SELECT x_stl2_boolean(NULLIF(1 ==== 2, TRUE)) FROM generate_series(1, 4) x;
> +NOTICE:  equal integers stable
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +-- Mixed operators and "scalar op ANY/ALL (array)" / "scalar IN (2 or more
> +-- values)" expressions testing
> +-- should not be precalculated
> +SELECT (1 === ANY('{2, 3}')) ====== (1 === ALL('{2, 3}'))
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  equal booleans stable strict
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  equal booleans stable strict
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  equal booleans stable strict
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  equal booleans stable strict
> + ?column?
> +----------
> + t
> + t
> + t
> + t
> +(4 rows)
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_vlt,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +-- should not be precalculated
> +SELECT ('(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)) ====== TRUE
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal booleans stable strict
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal booleans stable strict
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal booleans stable strict
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal booleans stable strict
> + ?column?
> +----------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean((1 === 2) = ANY('{TRUE}'))
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal integers volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal integers volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal integers volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal integers volatile
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean((1 === 2) = ALL('{TRUE}'))
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal integers volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal integers volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal integers volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal integers volatile
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean((1 === 2) IN (TRUE, FALSE))
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal integers volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal integers volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal integers volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal integers volatile
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + t
> + t
> + t
> + t
> +(4 rows)
> +
> +SELECT (1 ==== ANY('{2, 3}')) ====== (1 ==== ALL('{2, 3}'))
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> +NOTICE:  equal booleans stable strict
> + ?column?
> +----------
> + t
> + t
> + t
> + t
> +(4 rows)
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_stl,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +SELECT ('(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)) ====== TRUE
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal booleans stable strict
> + ?column?
> +----------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +SELECT x_stl2_boolean((1 ==== 2) = ANY('{TRUE}'))
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal integers stable
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +SELECT x_stl2_boolean((1 ==== 2) = ALL('{TRUE}'))
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal integers stable
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +SELECT x_stl2_boolean((1 ==== 2) IN (TRUE, FALSE))
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal integers stable
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + t
> + t
> + t
> + t
> +(4 rows)
> +
> +-- Mixed IS DISTINCT FROM and NULLIF expressions testing
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_vlt,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +-- should not be precalculated
> +SELECT (
> +  NULLIF('(1)'::my_integer, '(2)'::my_integer) IS DISTINCT FROM
> +  NULLIF('(2)'::my_integer, '(2)'::my_integer)
> +)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> + ?column?
> +----------
> + t
> + t
> + t
> + t
> +(4 rows)
> +
> +-- should not be precalculated
> +SELECT NULLIF(
> +  ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer),
> +  ('(2)'::my_integer IS DISTINCT FROM '(2)'::my_integer)
> +)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> + nullif
> +--------
> + t
> + t
> + t
> + t
> +(4 rows)
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_stl,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +SELECT (
> +  NULLIF('(1)'::my_integer, '(2)'::my_integer) IS DISTINCT FROM
> +  NULLIF('(2)'::my_integer, '(2)'::my_integer)
> +)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal my_integer stable
> + ?column?
> +----------
> + t
> + t
> + t
> + t
> +(4 rows)
> +
> +SELECT NULLIF(
> +  ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer),
> +  ('(2)'::my_integer IS DISTINCT FROM '(2)'::my_integer)
> +)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal my_integer stable
> + nullif
> +--------
> + t
> + t
> + t
> + t
> +(4 rows)
> +
> +-- Mixed IS DISTINCT FROM and "scalar op ANY/ALL (array)" / "scalar IN (2 or
> +-- more values)" expressions testing
> +-- should not be precalculated
> +SELECT x_stl2_boolean(
> +  (1 === ANY('{2, 3}')) IS DISTINCT FROM
> +  (1 === ALL('{2, 3}'))
> +)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_vlt,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +-- should not be precalculated
> +SELECT x_stl2_boolean(
> +  ('(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)) IS DISTINCT FROM
> +  TRUE
> +)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + t
> + t
> + t
> + t
> +(4 rows)
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean(
> +  ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) = ANY('{TRUE}')
> +)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal my_integer volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal my_integer volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal my_integer volatile
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + t
> + t
> + t
> + t
> +(4 rows)
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean(
> +  ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) = ALL('{TRUE}')
> +)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal my_integer volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal my_integer volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal my_integer volatile
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + t
> + t
> + t
> + t
> +(4 rows)
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean(
> +  ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) IN (TRUE, FALSE)
> +)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal my_integer volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal my_integer volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal my_integer volatile
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + t
> + t
> + t
> + t
> +(4 rows)
> +
> +SELECT x_stl2_boolean(
> +  (1 ==== ANY('{2, 3}')) IS DISTINCT FROM
> +  (1 ==== ALL('{2, 3}'))
> +)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_stl,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +SELECT x_stl2_boolean(
> +  ('(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)) IS DISTINCT FROM
> +  TRUE
> +)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal my_integer stable
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + t
> + t
> + t
> + t
> +(4 rows)
> +
> +SELECT x_stl2_boolean(
> +  ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) = ANY('{TRUE}')
> +)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer stable
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + t
> + t
> + t
> + t
> +(4 rows)
> +
> +SELECT x_stl2_boolean(
> +  ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) = ALL('{TRUE}')
> +)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer stable
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + t
> + t
> + t
> + t
> +(4 rows)
> +
> +SELECT x_stl2_boolean(
> +  ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) IN (TRUE, FALSE)
> +)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer stable
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + t
> + t
> + t
> + t
> +(4 rows)
> +
> +-- Mixed NULLIF and "scalar op ANY/ALL (array)" / "scalar IN (2 or more values)"
> +-- expressions testing
> +-- should not be precalculated
> +SELECT x_stl2_boolean(NULLIF(1 === ANY('{2, 3}'), 1 === ALL('{2, 3}')))
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> +
> +
> +
> +
> +(4 rows)
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_vlt,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +-- should not be precalculated
> +SELECT x_stl2_boolean(NULLIF(
> +  '(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer),
> +  TRUE
> +))
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  s2 boolean
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +-- should not be precalculated
> +SELECT (
> +  NULLIF('(1)'::my_integer, '(2)'::my_integer) ====
> +  ANY('{(3)}'::my_integer[])
> +)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer stable
> + ?column?
> +----------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +-- should not be precalculated
> +SELECT (
> +  NULLIF('(1)'::my_integer, '(2)'::my_integer) ====
> +  ALL('{(3)}'::my_integer[])
> +)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer stable
> + ?column?
> +----------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +-- should not be precalculated
> +SELECT (
> +  NULLIF('(1)'::my_integer, '(2)'::my_integer) IN
> +  ('(3)'::my_integer, '(2)'::my_integer)
> +)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> +NOTICE:  equal my_integer volatile
> + ?column?
> +----------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +SELECT x_stl2_boolean(NULLIF(1 ==== ANY('{2, 3}'), 1 ==== ALL('{2, 3}')))
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> +
> +
> +
> +
> +(4 rows)
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_stl,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +SELECT x_stl2_boolean(NULLIF(
> +  '(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer),
> +  TRUE
> +))
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal my_integer stable
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +SELECT (
> +  NULLIF('(1)'::my_integer, '(2)'::my_integer) ====
> +  ANY('{(3)}'::my_integer[])
> +)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal my_integer stable
> + ?column?
> +----------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +SELECT (
> +  NULLIF('(1)'::my_integer, '(2)'::my_integer) ====
> +  ALL('{(3)}'::my_integer[])
> +)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal my_integer stable
> + ?column?
> +----------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +SELECT (
> +  NULLIF('(1)'::my_integer, '(2)'::my_integer) IN
> +  ('(3)'::my_integer, '(2)'::my_integer)
> +)
> +FROM generate_series(1, 4) x;
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal my_integer stable
> +NOTICE:  equal my_integer stable
> + ?column?
> +----------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +-- Tracking functions testing
> +SET track_functions TO 'all';
> +SELECT x_vlt() FROM generate_series(1, 3) x; -- should not be precalculated
> +NOTICE:  v
> +NOTICE:  v
> +NOTICE:  v
> + x_vlt
> +-------
> +     1
> +     1
> +     1
> +(3 rows)
> +
> +SELECT x_stl() FROM generate_series(1, 3) x;
> +NOTICE:  s
> + x_stl
> +-------
> +     1
> +     1
> +     1
> +(3 rows)
> +
> +SELECT x_vlt() FROM generate_series(1, 4) x WHERE x_vlt() < x; -- should not be precalculated
> +NOTICE:  v
> +NOTICE:  v
> +NOTICE:  v
> +NOTICE:  v
> +NOTICE:  v
> +NOTICE:  v
> +NOTICE:  v
> + x_vlt
> +-------
> +     1
> +     1
> +     1
> +(3 rows)
> +
> +SELECT x_stl() FROM generate_series(1, 4) x WHERE x_stl() < x;
> +NOTICE:  s
> +NOTICE:  s
> +NOTICE:  s
> + x_stl
> +-------
> +     1
> +     1
> +     1
> +(3 rows)
> +
> +SELECT x_stl2(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
> +NOTICE:  v
> +NOTICE:  s2
> +NOTICE:  v
> +NOTICE:  s2
> +NOTICE:  v
> +NOTICE:  s2
> +NOTICE:  v
> +NOTICE:  s2
> + x_stl2
> +--------
> +      1
> +      1
> +      1
> +      1
> +(4 rows)
> +
> +SELECT x_imm2(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
> +NOTICE:  v
> +NOTICE:  i2
> +NOTICE:  v
> +NOTICE:  i2
> +NOTICE:  v
> +NOTICE:  i2
> +NOTICE:  v
> +NOTICE:  i2
> + x_imm2
> +--------
> +      1
> +      1
> +      1
> +      1
> +(4 rows)
> +
> +SELECT x_stl2(x_stl2(1)) FROM generate_series(1, 4) x;
> +NOTICE:  s2
> +NOTICE:  s2
> + x_stl2
> +--------
> +      1
> +      1
> +      1
> +      1
> +(4 rows)
> +
> +SELECT x_imm2(x_stl2(1)) FROM generate_series(1, 4) x;
> +NOTICE:  s2
> +NOTICE:  i2
> + x_imm2
> +--------
> +      1
> +      1
> +      1
> +      1
> +(4 rows)
> +
> +SELECT x_stl2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
> +NOTICE:  v
> +NOTICE:  s2 strict
> +NOTICE:  v
> +NOTICE:  s2 strict
> +NOTICE:  v
> +NOTICE:  s2 strict
> +NOTICE:  v
> +NOTICE:  s2 strict
> + x_stl2_strict
> +---------------
> +             1
> +             1
> +             1
> +             1
> +(4 rows)
> +
> +SELECT x_imm2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
> +NOTICE:  v
> +NOTICE:  i2 strict
> +NOTICE:  v
> +NOTICE:  i2 strict
> +NOTICE:  v
> +NOTICE:  i2 strict
> +NOTICE:  v
> +NOTICE:  i2 strict
> + x_imm2_strict
> +---------------
> +             1
> +             1
> +             1
> +             1
> +(4 rows)
> +
> +SELECT x_stl2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x;
> +NOTICE:  s2 strict
> +NOTICE:  s2 strict
> + x_stl2_strict
> +---------------
> +             1
> +             1
> +             1
> +             1
> +(4 rows)
> +
> +SELECT x_imm2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x;
> +NOTICE:  s2 strict
> +NOTICE:  i2 strict
> + x_imm2_strict
> +---------------
> +             1
> +             1
> +             1
> +             1
> +(4 rows)
> +
> +SELECT x_stl2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x;
> +NOTICE:  s2
> + x_stl2_strict
> +---------------
> +
> +
> +
> +
> +(4 rows)
> +
> +SELECT x_imm2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x;
> +NOTICE:  s2
> + x_imm2_strict
> +---------------
> +
> +
> +
> +
> +(4 rows)
> +
> +SELECT 1 === 2 FROM generate_series(1, 4) x; -- should not be precalculated
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> +NOTICE:  equal integers volatile
> + ?column?
> +----------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +SELECT 1 ==== 2 FROM generate_series(1, 4) x;
> +NOTICE:  equal integers stable
> + ?column?
> +----------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +SELECT 1 ===== 2 FROM generate_series(1, 4) x;
> +NOTICE:  equal integers immutable
> + ?column?
> +----------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +SELECT (x_vlt() ==== 2) ====== (x_vlt() ===== 3) FROM generate_series(1, 4) x; -- should not be precalculated
> +NOTICE:  v
> +NOTICE:  equal integers stable
> +NOTICE:  v
> +NOTICE:  equal integers immutable
> +NOTICE:  equal booleans stable strict
> +NOTICE:  v
> +NOTICE:  equal integers stable
> +NOTICE:  v
> +NOTICE:  equal integers immutable
> +NOTICE:  equal booleans stable strict
> +NOTICE:  v
> +NOTICE:  equal integers stable
> +NOTICE:  v
> +NOTICE:  equal integers immutable
> +NOTICE:  equal booleans stable strict
> +NOTICE:  v
> +NOTICE:  equal integers stable
> +NOTICE:  v
> +NOTICE:  equal integers immutable
> +NOTICE:  equal booleans stable strict
> + ?column?
> +----------
> + t
> + t
> + t
> + t
> +(4 rows)
> +
> +SELECT (1 ==== 2) ====== (3 ==== 3) FROM generate_series(1, 4) x;
> +NOTICE:  equal integers stable
> +NOTICE:  equal integers stable
> +NOTICE:  equal booleans stable strict
> + ?column?
> +----------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +SELECT x_stl2_boolean(NULL) ====== (3 ==== 3) FROM generate_series(1, 4) x;
> +NOTICE:  s2 boolean
> +NOTICE:  equal integers stable
> + ?column?
> +----------
> +
> +
> +
> +
> +(4 rows)
> +
> +SELECT x_vlt() ==== 2 FROM generate_series(1, 4) x; -- should not be precalculated
> +NOTICE:  v
> +NOTICE:  equal integers stable
> +NOTICE:  v
> +NOTICE:  equal integers stable
> +NOTICE:  v
> +NOTICE:  equal integers stable
> +NOTICE:  v
> +NOTICE:  equal integers stable
> + ?column?
> +----------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +SELECT x_vlt() ===== 2 FROM generate_series(1, 4) x; -- should not be precalculated
> +NOTICE:  v
> +NOTICE:  equal integers immutable
> +NOTICE:  v
> +NOTICE:  equal integers immutable
> +NOTICE:  v
> +NOTICE:  equal integers immutable
> +NOTICE:  v
> +NOTICE:  equal integers immutable
> + ?column?
> +----------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +SELECT x_stl() ==== x_stl() FROM generate_series(1, 4) x;
> +NOTICE:  s
> +NOTICE:  s
> +NOTICE:  equal integers stable
> + ?column?
> +----------
> + t
> + t
> + t
> + t
> +(4 rows)
> +
> +SELECT x_stl2_boolean(1 ==== 2) FROM generate_series(1, 4) x;
> +NOTICE:  equal integers stable
> +NOTICE:  s2 boolean
> + x_stl2_boolean
> +----------------
> + f
> + f
> + f
> + f
> +(4 rows)
> +
> +SET track_functions TO DEFAULT;
> +-- PL/pgSQL Simple expressions
> +-- Make sure precalculated stable functions can't be simple expressions: these
> +-- expressions are only initialized once per transaction and then executed
> +-- multiple times.
> +BEGIN;
> +SELECT simple();
> + simple
> +--------
> +      2
> +(1 row)
> +
> +INSERT INTO two VALUES (3);
> +SELECT simple();
> + simple
> +--------
> +      3
> +(1 row)
> +
> +ROLLBACK;
> +-- Drop tables for testing
> +DROP TABLE two;
> diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
> index 04206c3..f2710b9 100644
> --- a/src/test/regress/serial_schedule
> +++ b/src/test/regress/serial_schedule
> @@ -179,3 +179,4 @@ test: with
>  test: xml
>  test: event_trigger
>  test: stats
> +test: precalculate_stable_functions
> diff --git a/src/test/regress/sql/precalculate_stable_functions.sql
b/src/test/regress/sql/precalculate_stable_functions.sql
> new file mode 100644
> index 0000000..a59791d
> --- /dev/null
> +++ b/src/test/regress/sql/precalculate_stable_functions.sql
> @@ -0,0 +1,949 @@
> +--
> +-- PRECALCULATE STABLE FUNCTIONS
> +--
> +
> +-- Create types and tables for testing
> +
> +CREATE TYPE my_integer AS (value integer);
> +
> +CREATE TABLE two (i integer);
> +INSERT INTO two VALUES (1), (2);
> +
> +-- Create volatile functions for testing
> +
> +CREATE OR REPLACE FUNCTION public.x_vlt (
> +)
> +RETURNS integer VOLATILE AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 'v';
> +  RETURN 1;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +
> +CREATE OR REPLACE FUNCTION public.equal_integers_vlt (
> +  integer,
> +  integer
> +)
> +RETURNS boolean VOLATILE AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 'equal integers volatile';
> +  RETURN $1 = $2;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +
> +CREATE OR REPLACE FUNCTION public.x_vlt_my_integer (
> +)
> +RETURNS my_integer VOLATILE AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 'v my_integer';
> +  RETURN '(1)'::my_integer;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +
> +CREATE OR REPLACE FUNCTION public.equal_my_integer_vlt (
> +  my_integer,
> +  my_integer
> +)
> +RETURNS boolean VOLATILE AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 'equal my_integer volatile';
> +  RETURN $1.value = $2.value;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +
> +CREATE OR REPLACE FUNCTION public.x_vlt_array_int (
> +)
> +RETURNS int[] VOLATILE AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 'v array_int';
> +  RETURN '{2, 3}'::int[];
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +
> +-- Create stable functions for testing
> +
> +CREATE OR REPLACE FUNCTION public.x_stl (
> +)
> +RETURNS integer STABLE AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 's';
> +  RETURN 1;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +
> +CREATE OR REPLACE FUNCTION public.x_stl2 (
> +     integer
> +)
> +RETURNS integer STABLE AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 's2';
> +  RETURN $1;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +
> +CREATE OR REPLACE FUNCTION public.x_stl2_strict (
> +     integer
> +)
> +RETURNS integer STABLE STRICT AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 's2 strict';
> +  RETURN $1;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +
> +CREATE OR REPLACE FUNCTION public.equal_integers_stl (
> +  integer,
> +  integer
> +)
> +RETURNS boolean STABLE AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 'equal integers stable';
> +  RETURN $1 = $2;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +
> +CREATE OR REPLACE FUNCTION public.x_stl2_boolean (
> +  boolean
> +)
> +RETURNS boolean STABLE AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 's2 boolean';
> +  RETURN $1;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +
> +CREATE OR REPLACE FUNCTION public.equal_booleans_stl_strict (
> +  boolean,
> +  boolean
> +)
> +RETURNS boolean STABLE STRICT AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 'equal booleans stable strict';
> +  RETURN $1 = $2;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +
> +CREATE OR REPLACE FUNCTION public.x_stl_my_integer (
> +)
> +RETURNS my_integer STABLE AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 's my_integer';
> +  RETURN '(1)'::my_integer;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +
> +CREATE OR REPLACE FUNCTION public.equal_my_integer_stl (
> +  my_integer,
> +  my_integer
> +)
> +RETURNS boolean STABLE AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 'equal my_integer stable';
> +  RETURN $1.value = $2.value;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +
> +CREATE OR REPLACE FUNCTION public.x_stl_array_int (
> +)
> +RETURNS int[] STABLE AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 's array_int';
> +  RETURN '{2, 3}'::int[];
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +
> +CREATE OR REPLACE FUNCTION public.stable_max(
> +)
> +RETURNS integer STABLE AS
> +$body$
> +BEGIN
> +  RETURN (SELECT max(i) from two);
> +END
> +$body$
> +LANGUAGE 'plpgsql';
> +
> +CREATE OR REPLACE FUNCTION public.simple(
> +)
> +RETURNS integer STABLE AS
> +$body$
> +BEGIN
> +  RETURN stable_max();
> +END
> +$body$
> +LANGUAGE 'plpgsql';
> +
> +-- Create immutable functions for testing
> +
> +CREATE OR REPLACE FUNCTION public.x_imm2 (
> +     integer
> +)
> +RETURNS integer IMMUTABLE AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 'i2';
> +  RETURN $1;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +
> +CREATE OR REPLACE FUNCTION public.x_imm2_strict (
> +     integer
> +)
> +RETURNS integer IMMUTABLE STRICT AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 'i2 strict';
> +  RETURN $1;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +
> +CREATE OR REPLACE FUNCTION public.equal_integers_imm (
> +  integer,
> +  integer
> +)
> +RETURNS boolean IMMUTABLE AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 'equal integers immutable';
> +  RETURN $1 = $2;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +
> +CREATE OR REPLACE FUNCTION public.equal_my_integer_imm (
> +  my_integer,
> +  my_integer
> +)
> +RETURNS boolean IMMUTABLE AS
> +$body$
> +BEGIN
> +  RAISE NOTICE 'equal my_integer immutable';
> +  RETURN $1.value = $2.value;
> +END;
> +$body$
> +LANGUAGE 'plpgsql';
> +
> +-- Create operators for testing
> +
> +CREATE operator === (
> +  PROCEDURE = equal_integers_vlt,
> +  LEFTARG = integer,
> +  RIGHTARG = integer
> +);
> +
> +CREATE operator ==== (
> +  PROCEDURE = equal_integers_stl,
> +  LEFTARG = integer,
> +  RIGHTARG = integer
> +);
> +
> +CREATE operator ===== (
> +  PROCEDURE = equal_integers_imm,
> +  LEFTARG = integer,
> +  RIGHTARG = integer
> +);
> +
> +CREATE operator ====== (
> +  PROCEDURE = equal_booleans_stl_strict,
> +  LEFTARG = boolean,
> +  RIGHTARG = boolean
> +);
> +
> +CREATE operator ==== (
> +  PROCEDURE = equal_my_integer_stl,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +
> +-- Simple functions testing
> +
> +SELECT x_vlt() FROM generate_series(1, 3) x; -- should not be precalculated
> +SELECT x_stl() FROM generate_series(1, 3) x;
> +
> +-- WHERE clause testing
> +
> +SELECT x_vlt() FROM generate_series(1, 4) x WHERE x_vlt() < x; -- should not be precalculated
> +SELECT x_stl() FROM generate_series(1, 4) x WHERE x_stl() < x;
> +
> +-- Functions with constant arguments and nested functions testing
> +
> +SELECT x_stl2(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
> +SELECT x_imm2(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
> +
> +SELECT x_stl2(x_stl2(1)) FROM generate_series(1, 4) x;
> +SELECT x_imm2(x_stl2(1)) FROM generate_series(1, 4) x;
> +
> +-- Strict functions testing
> +
> +SELECT x_stl2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
> +SELECT x_imm2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
> +
> +SELECT x_stl2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x;
> +SELECT x_imm2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x;
> +
> +-- Strict functions with null arguments testing
> +
> +SELECT x_stl2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x;
> +SELECT x_imm2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x;
> +
> +-- Operators testing
> +
> +SELECT 1 === 2 FROM generate_series(1, 4) x; -- should not be precalculated
> +SELECT 1 ==== 2 FROM generate_series(1, 4) x;
> +
> +-- Nested and strict operators testing
> +-- (also partly mixed functions and operators testing)
> +
> +SELECT (x_vlt() ==== 2) ====== (x_vlt() ===== 3) FROM generate_series(1, 4) x; -- should not be precalculated
> +SELECT (x_stl() ==== 2) ====== (x_stl() ===== 3) FROM generate_series(1, 4) x;
> +SELECT (1 ==== 2) ====== x_stl2_boolean(NULL) FROM generate_series(1, 4) x;
> +
> +-- IS DISTINCT FROM expression testing
> +
> +-- create operator here because we will drop and reuse it several times
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_vlt,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +
> +-- should not be precalculated
> +SELECT '(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer
> +FROM generate_series(1, 4) x;
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_stl,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +
> +SELECT '(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer
> +FROM generate_series(1, 4) x;
> +
> +-- IS DISTINCT FROM expressions with null arguments testing
> +
> +SELECT x_stl2_boolean(1 IS DISTINCT FROM x_stl2(NULL))
> +FROM generate_series(1, 4) x;
> +
> +SELECT x_stl2_boolean(x_stl2(NULL) IS DISTINCT FROM x_stl2(NULL))
> +FROM generate_series(1, 4) x;
> +
> +-- Nested IS DISTINCT FROM expression testing
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_vlt,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean(
> +  ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) IS DISTINCT FROM
> +  TRUE
> +)
> +FROM generate_series(1, 4) x;
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_stl,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +
> +SELECT x_stl2_boolean(
> +  ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) IS DISTINCT FROM
> +  TRUE
> +)
> +FROM generate_series(1, 4) x;
> +
> +-- NULLIF expressions testing
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_vlt,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +
> +-- should not be precalculated
> +SELECT NULLIF('(1)'::my_integer, '(2)'::my_integer)
> +FROM generate_series(1, 4) x;
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_stl,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +
> +SELECT NULLIF('(1)'::my_integer, '(2)'::my_integer)
> +FROM generate_series(1, 4) x;
> +
> +-- NULLIF expressions with null arguments testing
> +
> +SELECT x_stl2(NULLIF(1, x_stl2(NULL))) FROM generate_series(1, 4) x;
> +
> +SELECT x_stl2(NULLIF(x_stl2(NULL), x_stl2(NULL))) FROM generate_series(1, 4) x;
> +
> +-- Nested NULLIF expression testing
> +
> +-- should not be precalculated
> +SELECT NULLIF(NULLIF(x_vlt_my_integer(), '(2)'::my_integer), '(2)'::my_integer)
> +FROM generate_series(1, 4) x;
> +
> +SELECT NULLIF(NULLIF(x_stl_my_integer(), '(2)'::my_integer), '(2)'::my_integer)
> +FROM generate_series(1, 4) x;
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_imm,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +
> +-- should not be precalculated
> +SELECT NULLIF(NULLIF(x_vlt_my_integer(), '(2)'::my_integer), '(2)'::my_integer)
> +FROM generate_series(1, 4) x;
> +
> +SELECT NULLIF(NULLIF(x_stl_my_integer(), '(2)'::my_integer), '(2)'::my_integer)
> +FROM generate_series(1, 4) x;
> +
> +-- "scalar op ANY/ALL (array)" / "scalar IN (2 or more values)" expressions
> +-- testing
> +
> +SELECT 1 === ANY('{2, 3}') FROM generate_series(1, 4) x; -- should not be precalculated
> +SELECT 1 === ALL('{2, 3}') FROM generate_series(1, 4) x; -- should not be precalculated
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_vlt,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +
> +-- should not be precalculated
> +SELECT '(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)
> +FROM generate_series(1, 4) x;
> +
> +SELECT 1 ==== ANY('{2, 3}') FROM generate_series(1, 4) x;
> +SELECT 1 ==== ALL('{2, 3}') FROM generate_series(1, 4) x;
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_stl,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +
> +SELECT '(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)
> +FROM generate_series(1, 4) x;
> +
> +-- "scalar op ANY/ALL (array)" / "scalar IN (2 or more values)" expressions with
> +-- null arguments testing
> +
> +SELECT 1 ==== ANY('{2, NULL}') FROM generate_series(1, 4) x;
> +SELECT x_stl2_boolean(1 ==== ANY(NULL)) FROM generate_series(1, 4) x;
> +SELECT NULL ==== ANY('{2, 3}'::int[]) FROM generate_series(1, 4) x;
> +SELECT NULL ==== ANY('{2, NULL}'::int[]) FROM generate_series(1, 4) x;
> +SELECT x_stl2_boolean(NULL::int ==== ANY(NULL)) FROM generate_series(1, 4) x;
> +
> +SELECT 1 ==== ALL('{2, NULL}') FROM generate_series(1, 4) x;
> +SELECT x_stl2_boolean(1 ==== ALL(NULL)) FROM generate_series(1, 4) x;
> +SELECT NULL ==== ALL('{2, 3}'::int[]) FROM generate_series(1, 4) x;
> +SELECT NULL ==== ALL('{2, NULL}'::int[]) FROM generate_series(1, 4) x;
> +SELECT x_stl2_boolean(NULL::int ==== ALL(NULL)) FROM generate_series(1, 4) x;
> +
> +SELECT x_stl2_boolean(1 IN (2, NULL)) FROM generate_series(1, 4) x;
> +SELECT x_stl2_boolean(NULL IN (2, 3)) FROM generate_series(1, 4) x;
> +SELECT x_stl2_boolean(NULL IN (2, NULL)) FROM generate_series(1, 4) x;
> +
> +-- Nesting "scalar op ANY/ALL (array)" / "scalar IN (2 or more values)"
> +-- expressions testing (also partly mixed functions and "scalar op ANY/ALL
> +-- (array)" / "scalar IN (2 or more values)" expressions testing)
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean((x_vlt() ==== ANY('{2, 3}')) = ANY('{TRUE}'))
> +FROM generate_series(1, 4) x;
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean((x_vlt() ==== ANY('{2, 3}')) = ALL('{TRUE}'))
> +FROM generate_series(1, 4) x;
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean((x_vlt() ==== ANY('{2, 3}')) IN (TRUE, FALSE))
> +FROM generate_series(1, 4) x;
> +
> +SELECT x_stl2_boolean((x_stl() ==== ANY('{2, 3}')) = ANY('{TRUE}'))
> +FROM generate_series(1, 4) x;
> +
> +SELECT x_stl2_boolean((x_stl() ==== ANY('{2, 3}')) = ALL('{TRUE}'))
> +FROM generate_series(1, 4) x;
> +
> +SELECT x_stl2_boolean((x_stl() ==== ANY('{2, 3}')) IN (TRUE, FALSE))
> +FROM generate_series(1, 4) x;
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean((x_vlt() ===== ANY('{2, 3}')) = ANY('{TRUE}'))
> +FROM generate_series(1, 4) x;
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean((x_vlt() ===== ANY('{2, 3}')) = ALL('{TRUE}'))
> +FROM generate_series(1, 4) x;
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean((x_vlt() ===== ANY('{2, 3}')) IN (TRUE, FALSE))
> +FROM generate_series(1, 4) x;
> +
> +SELECT x_stl2_boolean((x_stl() ===== ANY('{2, 3}')) = ANY('{TRUE}'))
> +FROM generate_series(1, 4) x;
> +
> +SELECT x_stl2_boolean((x_stl() ===== ANY('{2, 3}')) = ALL('{TRUE}'))
> +FROM generate_series(1, 4) x;
> +
> +SELECT x_stl2_boolean((x_stl() ===== ANY('{2, 3}')) IN (TRUE, FALSE))
> +FROM generate_series(1, 4) x;
> +
> +-- Mixed functions and operators testing
> +-- (most of it was earlier in Nested and strict operators testing)
> +
> +SELECT x_stl2_boolean(1 ==== 2) FROM generate_series(1, 4) x;
> +
> +-- Mixed functions and IS DISTINCT FROM expressions testing
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_vlt,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +
> +-- should not be precalculated
> +SELECT equal_booleans_stl_strict(
> +  (x_stl_my_integer() IS DISTINCT FROM '(1)'::my_integer),
> +  (x_stl_my_integer() IS DISTINCT FROM '(2)'::my_integer)
> +)
> +FROM generate_series(1, 4) x;
> +
> +SELECT equal_booleans_stl_strict(
> +  (x_stl() IS DISTINCT FROM 1),
> +  (x_stl() IS DISTINCT FROM 2)
> +)
> +FROM generate_series(1, 4) x;
> +
> +-- Mixed functions and NULLIF expressions testing
> +
> +-- should not be precalculated
> +SELECT equal_my_integer_stl(
> +  NULLIF(x_stl_my_integer(), '(1)'::my_integer),
> +  NULLIF(x_stl_my_integer(), '(2)'::my_integer)
> +)
> +FROM generate_series(1, 4) x;
> +
> +SELECT equal_integers_stl(NULLIF(x_stl(), 1), NULLIF(x_stl(), 2))
> +FROM generate_series(1, 4) x;
> +
> +-- Mixed functions and "scalar op ANY/ALL (array)" / "scalar IN (2 or more
> +-- values)" expressions testing (partly in nesting "scalar op ANY/ALL (array)" /
> +-- "scalar IN (2 or more values)" expressions testing)
> +
> +SELECT 1 ==== ANY(x_vlt_array_int()) FROM generate_series(1, 4) x; -- should not be precalculated
> +SELECT 1 ==== ALL(x_vlt_array_int()) FROM generate_series(1, 4) x; -- should not be precalculated
> +
> +SELECT 1 ==== ANY(x_stl_array_int()) FROM generate_series(1, 4) x;
> +SELECT 1 ==== ALL(x_stl_array_int()) FROM generate_series(1, 4) x;
> +
> +-- Mixed operators and IS DISTINCT FROM expressions testing
> +
> +-- should not be precalculated
> +SELECT (
> +  ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) ======
> +  ('(2)'::my_integer IS DISTINCT FROM '(2)'::my_integer)
> +)
> +FROM generate_series(1, 4) x;
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean((1 === 2) IS DISTINCT FROM TRUE)
> +FROM generate_series(1, 4) x;
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_stl,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +
> +SELECT (
> +  ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) ======
> +  ('(2)'::my_integer IS DISTINCT FROM '(2)'::my_integer)
> +)
> +FROM generate_series(1, 4) x;
> +
> +SELECT x_stl2_boolean((1 ==== 2) IS DISTINCT FROM TRUE)
> +FROM generate_series(1, 4) x;
> +
> +-- Mixed operators and NULLIF expressions testing
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_vlt,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +
> +-- should not be precalculated
> +SELECT (
> +  NULLIF('(1)'::my_integer, '(2)'::my_integer) ====
> +  NULLIF('(2)'::my_integer, '(2)'::my_integer)
> +)
> +FROM generate_series(1, 4) x;
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean(NULLIF(1 === 2, TRUE)) FROM generate_series(1, 4) x;
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_stl,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +
> +SELECT (
> +  NULLIF('(1)'::my_integer, '(2)'::my_integer) ====
> +  NULLIF('(2)'::my_integer, '(2)'::my_integer)
> +)
> +FROM generate_series(1, 4) x;
> +
> +SELECT x_stl2_boolean(NULLIF(1 ==== 2, TRUE)) FROM generate_series(1, 4) x;
> +
> +-- Mixed operators and "scalar op ANY/ALL (array)" / "scalar IN (2 or more
> +-- values)" expressions testing
> +
> +-- should not be precalculated
> +SELECT (1 === ANY('{2, 3}')) ====== (1 === ALL('{2, 3}'))
> +FROM generate_series(1, 4) x;
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_vlt,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +
> +-- should not be precalculated
> +SELECT ('(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)) ====== TRUE
> +FROM generate_series(1, 4) x;
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean((1 === 2) = ANY('{TRUE}'))
> +FROM generate_series(1, 4) x;
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean((1 === 2) = ALL('{TRUE}'))
> +FROM generate_series(1, 4) x;
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean((1 === 2) IN (TRUE, FALSE))
> +FROM generate_series(1, 4) x;
> +
> +SELECT (1 ==== ANY('{2, 3}')) ====== (1 ==== ALL('{2, 3}'))
> +FROM generate_series(1, 4) x;
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_stl,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +
> +SELECT ('(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)) ====== TRUE
> +FROM generate_series(1, 4) x;
> +
> +SELECT x_stl2_boolean((1 ==== 2) = ANY('{TRUE}'))
> +FROM generate_series(1, 4) x;
> +
> +SELECT x_stl2_boolean((1 ==== 2) = ALL('{TRUE}'))
> +FROM generate_series(1, 4) x;
> +
> +SELECT x_stl2_boolean((1 ==== 2) IN (TRUE, FALSE))
> +FROM generate_series(1, 4) x;
> +
> +-- Mixed IS DISTINCT FROM and NULLIF expressions testing
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_vlt,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +
> +-- should not be precalculated
> +SELECT (
> +  NULLIF('(1)'::my_integer, '(2)'::my_integer) IS DISTINCT FROM
> +  NULLIF('(2)'::my_integer, '(2)'::my_integer)
> +)
> +FROM generate_series(1, 4) x;
> +
> +-- should not be precalculated
> +SELECT NULLIF(
> +  ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer),
> +  ('(2)'::my_integer IS DISTINCT FROM '(2)'::my_integer)
> +)
> +FROM generate_series(1, 4) x;
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_stl,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +
> +SELECT (
> +  NULLIF('(1)'::my_integer, '(2)'::my_integer) IS DISTINCT FROM
> +  NULLIF('(2)'::my_integer, '(2)'::my_integer)
> +)
> +FROM generate_series(1, 4) x;
> +
> +SELECT NULLIF(
> +  ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer),
> +  ('(2)'::my_integer IS DISTINCT FROM '(2)'::my_integer)
> +)
> +FROM generate_series(1, 4) x;
> +
> +-- Mixed IS DISTINCT FROM and "scalar op ANY/ALL (array)" / "scalar IN (2 or
> +-- more values)" expressions testing
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean(
> +  (1 === ANY('{2, 3}')) IS DISTINCT FROM
> +  (1 === ALL('{2, 3}'))
> +)
> +FROM generate_series(1, 4) x;
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_vlt,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean(
> +  ('(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)) IS DISTINCT FROM
> +  TRUE
> +)
> +FROM generate_series(1, 4) x;
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean(
> +  ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) = ANY('{TRUE}')
> +)
> +FROM generate_series(1, 4) x;
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean(
> +  ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) = ALL('{TRUE}')
> +)
> +FROM generate_series(1, 4) x;
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean(
> +  ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) IN (TRUE, FALSE)
> +)
> +FROM generate_series(1, 4) x;
> +
> +SELECT x_stl2_boolean(
> +  (1 ==== ANY('{2, 3}')) IS DISTINCT FROM
> +  (1 ==== ALL('{2, 3}'))
> +)
> +FROM generate_series(1, 4) x;
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_stl,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +
> +SELECT x_stl2_boolean(
> +  ('(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer)) IS DISTINCT FROM
> +  TRUE
> +)
> +FROM generate_series(1, 4) x;
> +
> +SELECT x_stl2_boolean(
> +  ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) = ANY('{TRUE}')
> +)
> +FROM generate_series(1, 4) x;
> +
> +SELECT x_stl2_boolean(
> +  ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) = ALL('{TRUE}')
> +)
> +FROM generate_series(1, 4) x;
> +
> +SELECT x_stl2_boolean(
> +  ('(1)'::my_integer IS DISTINCT FROM '(2)'::my_integer) IN (TRUE, FALSE)
> +)
> +FROM generate_series(1, 4) x;
> +
> +-- Mixed NULLIF and "scalar op ANY/ALL (array)" / "scalar IN (2 or more values)"
> +-- expressions testing
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean(NULLIF(1 === ANY('{2, 3}'), 1 === ALL('{2, 3}')))
> +FROM generate_series(1, 4) x;
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_vlt,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +
> +-- should not be precalculated
> +SELECT x_stl2_boolean(NULLIF(
> +  '(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer),
> +  TRUE
> +))
> +FROM generate_series(1, 4) x;
> +
> +-- should not be precalculated
> +SELECT (
> +  NULLIF('(1)'::my_integer, '(2)'::my_integer) ====
> +  ANY('{(3)}'::my_integer[])
> +)
> +FROM generate_series(1, 4) x;
> +
> +-- should not be precalculated
> +SELECT (
> +  NULLIF('(1)'::my_integer, '(2)'::my_integer) ====
> +  ALL('{(3)}'::my_integer[])
> +)
> +FROM generate_series(1, 4) x;
> +
> +-- should not be precalculated
> +SELECT (
> +  NULLIF('(1)'::my_integer, '(2)'::my_integer) IN
> +  ('(3)'::my_integer, '(2)'::my_integer)
> +)
> +FROM generate_series(1, 4) x;
> +
> +SELECT x_stl2_boolean(NULLIF(1 ==== ANY('{2, 3}'), 1 ==== ALL('{2, 3}')))
> +FROM generate_series(1, 4) x;
> +
> +DROP OPERATOR = (my_integer, my_integer);
> +CREATE OPERATOR = (
> +  PROCEDURE = equal_my_integer_stl,
> +  LEFTARG = my_integer,
> +  RIGHTARG = my_integer
> +);
> +
> +SELECT x_stl2_boolean(NULLIF(
> +  '(1)'::my_integer IN ('(2)'::my_integer, '(3)'::my_integer),
> +  TRUE
> +))
> +FROM generate_series(1, 4) x;
> +
> +SELECT (
> +  NULLIF('(1)'::my_integer, '(2)'::my_integer) ====
> +  ANY('{(3)}'::my_integer[])
> +)
> +FROM generate_series(1, 4) x;
> +
> +SELECT (
> +  NULLIF('(1)'::my_integer, '(2)'::my_integer) ====
> +  ALL('{(3)}'::my_integer[])
> +)
> +FROM generate_series(1, 4) x;
> +
> +SELECT (
> +  NULLIF('(1)'::my_integer, '(2)'::my_integer) IN
> +  ('(3)'::my_integer, '(2)'::my_integer)
> +)
> +FROM generate_series(1, 4) x;
> +
> +-- Tracking functions testing
> +
> +SET track_functions TO 'all';
> +
> +SELECT x_vlt() FROM generate_series(1, 3) x; -- should not be precalculated
> +SELECT x_stl() FROM generate_series(1, 3) x;
> +
> +SELECT x_vlt() FROM generate_series(1, 4) x WHERE x_vlt() < x; -- should not be precalculated
> +SELECT x_stl() FROM generate_series(1, 4) x WHERE x_stl() < x;
> +
> +SELECT x_stl2(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
> +SELECT x_imm2(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
> +
> +SELECT x_stl2(x_stl2(1)) FROM generate_series(1, 4) x;
> +SELECT x_imm2(x_stl2(1)) FROM generate_series(1, 4) x;
> +
> +SELECT x_stl2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
> +SELECT x_imm2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
> +
> +SELECT x_stl2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x;
> +SELECT x_imm2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x;
> +
> +SELECT x_stl2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x;
> +SELECT x_imm2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x;
> +
> +SELECT 1 === 2 FROM generate_series(1, 4) x; -- should not be precalculated
> +SELECT 1 ==== 2 FROM generate_series(1, 4) x;
> +SELECT 1 ===== 2 FROM generate_series(1, 4) x;
> +
> +SELECT (x_vlt() ==== 2) ====== (x_vlt() ===== 3) FROM generate_series(1, 4) x; -- should not be precalculated
> +SELECT (1 ==== 2) ====== (3 ==== 3) FROM generate_series(1, 4) x;
> +SELECT x_stl2_boolean(NULL) ====== (3 ==== 3) FROM generate_series(1, 4) x;
> +
> +SELECT x_vlt() ==== 2 FROM generate_series(1, 4) x; -- should not be precalculated
> +SELECT x_vlt() ===== 2 FROM generate_series(1, 4) x; -- should not be precalculated
> +
> +SELECT x_stl() ==== x_stl() FROM generate_series(1, 4) x;
> +SELECT x_stl2_boolean(1 ==== 2) FROM generate_series(1, 4) x;
> +
> +SET track_functions TO DEFAULT;
> +
> +-- PL/pgSQL Simple expressions
> +-- Make sure precalculated stable functions can't be simple expressions: these
> +-- expressions are only initialized once per transaction and then executed
> +-- multiple times.
> +
> +BEGIN;
> +SELECT simple();
> +INSERT INTO two VALUES (3);
> +SELECT simple();
> +ROLLBACK;
> +
> +-- Drop tables for testing
> +
> +DROP TABLE two;
> --
> 1.9.1
>

> From 2382fa68414f6bbed42ff66c7abbc3c9b200d244 Mon Sep 17 00:00:00 2001
> From: Marina Polyakova <m.polyakova@postgrespro.ru>
> Date: Mon, 15 May 2017 16:05:38 +0300
> Subject: [PATCH v4 3/3] Precalculate stable functions, costs
>
> Now in Postgresql only immutable functions are precalculated; stable functions
> are calculated for every row so in fact they don't differ from volatile
> functions.
>
> This patch includes:
> - cost changes for cached expressions (according to their behaviour)
> ---
>  src/backend/optimizer/path/costsize.c | 89 ++++++++++++++++++++++++++---------
>  1 file changed, 67 insertions(+), 22 deletions(-)
>
> diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
> index 52643d0..505772a 100644
> --- a/src/backend/optimizer/path/costsize.c
> +++ b/src/backend/optimizer/path/costsize.c
> @@ -140,6 +140,7 @@ static MergeScanSelCache *cached_scansel(PlannerInfo *root,
>                 PathKey *pathkey);
>  static void cost_rescan(PlannerInfo *root, Path *path,
>              Cost *rescan_startup_cost, Cost *rescan_total_cost);
> +static double cost_eval_cacheable_expr_per_tuple(Node *node);
>  static bool cost_qual_eval_walker(Node *node, cost_qual_eval_context *context);
>  static void get_restriction_qual_cost(PlannerInfo *root, RelOptInfo *baserel,
>                            ParamPathInfo *param_info,
> @@ -3464,6 +3465,59 @@ cost_qual_eval_node(QualCost *cost, Node *qual, PlannerInfo *root)
>      *cost = context.total;
>  }
>
> +/*
> + * cost_eval_cacheable_expr_per_tuple
> + *        Evaluate per tuple cost for expressions that can be cacheable.
> + *
> + * This function was created to not duplicate code for some expression and
> + * cached some expression.
> + */
> +static double
> +cost_eval_cacheable_expr_per_tuple(Node *node)
> +{
> +    double result;
> +
> +    /*
> +     * For each operator or function node in the given tree, we charge the
> +     * estimated execution cost given by pg_proc.procost (remember to multiply
> +     * this by cpu_operator_cost).
> +     */
> +    if (IsA(node, FuncExpr))
> +    {
> +        result = get_func_cost(((FuncExpr *) node)->funcid) * cpu_operator_cost;
> +    }
> +    else if (IsA(node, OpExpr) ||
> +             IsA(node, DistinctExpr) ||
> +             IsA(node, NullIfExpr))
> +    {
> +        OpExpr     *opexpr = (OpExpr *) node;
> +
> +        /* rely on struct equivalence to treat these all alike */
> +        set_opfuncid(opexpr);
> +
> +        result = get_func_cost(opexpr->opfuncid) * cpu_operator_cost;
> +    }
> +    else if (IsA(node, ScalarArrayOpExpr))
> +    {
> +        /*
> +         * Estimate that the operator will be applied to about half of the
> +         * array elements before the answer is determined.
> +         */
> +        ScalarArrayOpExpr *saop = (ScalarArrayOpExpr *) node;
> +        Node       *arraynode = (Node *) lsecond(saop->args);
> +
> +        set_sa_opfuncid(saop);
> +        result = get_func_cost(saop->opfuncid) * cpu_operator_cost *
> +            estimate_array_length(arraynode) * 0.5;
> +    }
> +    else
> +    {
> +        elog(ERROR, "non cacheable expression node type: %d", (int) nodeTag(node));
> +    }
> +
> +    return result;
> +}
> +
>  static bool
>  cost_qual_eval_walker(Node *node, cost_qual_eval_context *context)
>  {
> @@ -3537,32 +3591,23 @@ cost_qual_eval_walker(Node *node, cost_qual_eval_context *context)
>       * moreover, since our rowcount estimates for functions tend to be pretty
>       * phony, the results would also be pretty phony.
>       */
> -    if (IsA(node, FuncExpr))
> +    if (IsA(node, FuncExpr) ||
> +        IsA(node, OpExpr) ||
> +        IsA(node, DistinctExpr) ||
> +        IsA(node, NullIfExpr) ||
> +        IsA(node, ScalarArrayOpExpr))
>      {
> -        context->total.per_tuple +=
> -            get_func_cost(((FuncExpr *) node)->funcid) * cpu_operator_cost;
> +        context->total.per_tuple += cost_eval_cacheable_expr_per_tuple(node);
>      }
> -    else if (IsA(node, OpExpr) ||
> -             IsA(node, DistinctExpr) ||
> -             IsA(node, NullIfExpr))
> -    {
> -        /* rely on struct equivalence to treat these all alike */
> -        set_opfuncid((OpExpr *) node);
> -        context->total.per_tuple +=
> -            get_func_cost(((OpExpr *) node)->opfuncid) * cpu_operator_cost;
> -    }
> -    else if (IsA(node, ScalarArrayOpExpr))
> -    {
> +    else if (IsA(node, CachedExpr))
> +    {
>          /*
> -         * Estimate that the operator will be applied to about half of the
> -         * array elements before the answer is determined.
> +         * Calculate subexpression cost per tuple as usual and add it to startup
> +         * cost (because subexpression will be executed only once for all
> +         * tuples).
>           */
> -        ScalarArrayOpExpr *saop = (ScalarArrayOpExpr *) node;
> -        Node       *arraynode = (Node *) lsecond(saop->args);
> -
> -        set_sa_opfuncid(saop);
> -        context->total.per_tuple += get_func_cost(saop->opfuncid) *
> -            cpu_operator_cost * estimate_array_length(arraynode) * 0.5;
> +        context->total.startup += cost_eval_cacheable_expr_per_tuple(
> +            get_subexpr((CachedExpr *) node));
>      }
>      else if (IsA(node, Aggref) ||
>               IsA(node, WindowFunc))
> --
> 1.9.1
>


--
Best regards,
Aleksander Alekseev

> Hi Marina,

Hello again!

> 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.

Thank you very much as usual!

> Is there anything that you would like to change before we call it more
> or less final?

I would like to add some primitive nodes for precalculation if their 
behaviour allows to do it and their arguments/inputs are constant or 
precalculated too; and regression tests for it, of course. Also I would 
like to add some notes about precalculation of stable functions in 
documentation, for example, here [1] and here [2].

> Also I would advice to add your branch to our internal buildfarm just 
> to
> make sure everything is OK on exotic platforms like Windows ;)

Thanks! Done)

[1] https://www.postgresql.org/docs/10/static/xfunc-volatility.html
[2] https://www.postgresql.org/docs/10/static/sql-createfunction.html
-- 
Marina Polyakova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Hello, hackers!

Here I have made the 5th version of the patches. I have added the 
precalculation of all primitive nodes that don't return set, are not 
volatile themselves and their arguments are constant or precalculated 
expressions too. There're regression tests for all of them and little 
notes in the documentation. Like for the previous patches it seems that 
there is no obvious performance degradation too on regular queries 
(according to pgbench).

About functionality: precalculation doesn't work for parameters nodes in 
plan. So it doesn't work, for example, in the generic plans of prepared 
statements. There'll be an another patch for them.

About code:
* Infrastructure patch changes: no enum and union for all 25 
precalculated node types. Instead of them there is a new CacheableExpr 
node which only contains a NodeTag.
* There're some changes for CoerceToDomain, which constraints now are 
checked not only in the executor but in the planner too.

Patches are attached. Any suggestions are welcome!

-- 
Marina Polyakova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Jul 18, 2017 at 9:16 AM, Marina Polyakova
<m.polyakova@postgrespro.ru> wrote:
> Here I have made the 5th version of the patches. I have added the
> precalculation of all primitive nodes that don't return set, are not
> volatile themselves and their arguments are constant or precalculated
> expressions too. There're regression tests for all of them and little notes
> in the documentation. Like for the previous patches it seems that there is
> no obvious performance degradation too on regular queries (according to
> pgbench).

pgbench probably isn't a very good test for this sort of thing - it
only issues very short-running queries where the cost of evaluating
expressions is a relatively small part of the total cost.  Even if
things get worse, I'm not sure if you'd see it.  I'm not sure exactly
how you could construct a test case that could be harmed by this patch
- I guess you'd want to initialize lots of CacheExprs but never make
use of the caching usefully?

It could also be useful to test things like TPC-H to see if you get an
improvement.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

>> Like for the previous patches it seems that there is
>> no obvious performance degradation too on regular queries (according 
>> to
>> pgbench).
> 
> pgbench probably isn't a very good test for this sort of thing - it
> only issues very short-running queries where the cost of evaluating
> expressions is a relatively small part of the total cost.  Even if
> things get worse, I'm not sure if you'd see it.

If there's a mistake, for example, more than 1 try to replace cached 
expressions in the whole query tree, results of "in buffer test" or 
"mostly cache test" can different a little..

> I'm not sure exactly
> how you could construct a test case that could be harmed by this patch
> - I guess you'd want to initialize lots of CacheExprs but never make
> use of the caching usefully?

As I mentioned in the first letter about this feature it will be useful 
for such text search queries [1]:

SELECT COUNT(*) FROM messages WHERE body_tsvector @@ 
to_tsquery('postgres');

And I'm not sure that it is logical to precalculate stable and immutable 
functions themselves, but not to precalculate expressions that behave 
like stable/immutable functions; precalculate some types of operators 
and not to precalculate others (ScalarArrayOpExpr, RowCompareExpr). My 
patch solves the problem that not all nodes are simplified in 
eval_const_expressions_mutator (for example, ScalarArrayOpExpr) and 
consts of other types now behave more like ordinary consts (for example, 
composite types, coerce expressions, ConvertRowtypeExpr).

> It could also be useful to test things like TPC-H to see if you get an
> improvement.

I saw the examples of queries in TPC-H tests. If I'm not wrong they are 
not the target tests for this functionality (nothing will be 
precalculated). But it's a good idea to check that there's no a 
performance degradation on them too.

[1] 
https://www.postgresql.org/message-id/ba261b9fc25dea4069d8ba9a8fcadf35%40postgrespro.ru

-- 
Marina Polyakova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

The following review has been posted through the commitfest application:
make installcheck-world:  not tested
Implements feature:       not tested
Spec compliant:           not tested
Documentation:            not tested

Hi Marina,

I'm sorry to inform you that the v5 path set become a little outdated:

```
$ git apply v5-0002-Precalculate-stable-functions-planning-and-execut.patch
error: patch failed: src/pl/plpgsql/src/pl_exec.c:6471
error: src/pl/plpgsql/src/pl_exec.c: patch does not apply
```

If it's not too much trouble could you please fix the conflicts with the current master branch?

The new status of this patch is: Waiting on Author

Hello, hackers!

First of all, happy New Year!

Secondly, here there's a sixth version of the patch for the 
precalculation of stable or immutable functions, stable or immutable 
operators and other nonvolatile expressions.

The basic idea: the expression is precalculated (= calculated once for 
all output rows, but as many times as the expression is mentioned in the 
query) if:
1) it doesn't return a set,
2) it's not volatile itself,
3) its arguments are also constants or precalculated expressions.

Differences from the previous version:
* rebased, including changes for ArrayCoerce expressions;
* support for prepared statements (including tests, but only for 
immutable functions);
* fix the caching of SQLValueFunctions (all of them are stable even 
date/time functions);
* added the expected output for the tests in case the xml functions are 
not supported;
* the tests are also performed in make check, not just in make 
check-world;
* code cleanup.

Like for the previous patches it seems that there is no obvious 
performance degradation too on regular queries (according to pgbench).

> pgbench probably isn't a very good test for this sort of thing - it
> only issues very short-running queries where the cost of evaluating
> expressions is a relatively small part of the total cost.  Even if
> things get worse, I'm not sure if you'd see it.  I'm not sure exactly
> how you could construct a test case that could be harmed by this patch
> - I guess you'd want to initialize lots of CacheExprs but never make
> use of the caching usefully?
> 
> It could also be useful to test things like TPC-H to see if you get an
> improvement.
I'm sorry, the TPC-H comparative tests will be later..

Patch is attached. Any suggestions are welcome!

-- 
Marina Polyakova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

> Thanks for your patch, looks quite interesting!

Glad to hear it :)

>> To not send big patch I have split it (that's why version starts
> with the
>> first again) and here I send infrastructure patch which includes:
> 
> Yeah, but it's still 18k lines :)

Here 13k lines - 2 sets of expected results for regression tests..)

> After the first quick glance I have
> a few
> small questions.
> 
> If I call a stable function from a query and subquery, looks like it's
> cached:
> 
> ```
> =# select stable_with_int(1) from (select stable_with_int(1) from x)
> q;
> NOTICE:  00000: stable with int
> LOCATION:  exec_stmt_raise, pl_exec.c:3353
>  stable_with_int
> -----------------
>                1
>                1
>                1
>                1
> (4 rows)
> ```
> 
> But the same from CTE works different, is it supposed to be like that?
> 
> ```
> =# with data as (select stable_with_int(1) from x) select
> stable_with_int(1) from data;
> NOTICE:  00000: stable with int
> LOCATION:  exec_stmt_raise, pl_exec.c:3353
> NOTICE:  00000: stable with int
> LOCATION:  exec_stmt_raise, pl_exec.c:3353
>  stable_with_int
> -----------------
>                1
>                1
>                1
>                1
> (4 rows)
> ```

The function is always cached, but in the first example the plan is 
simplified so you only get one call of the function in the entire plan. 
(In the function subquery_planner, CTE are processed separately by 
calling the function SS_process_ctes. Subqueries are simplified a little 
later by calling the function pull_up_subqueries; in our case the 
function pull_up_simple_subquery is used.)

> Also I see this pattern quite some time, maybe it makes sense to move
> it to a function?
> 
> ```
> + /* create and return CachedExpr */
> + CachedExpr *new_node = makeNode(CachedExpr);
> + new_node->subexpr = (CacheableExpr *) current_node;
> +
> + context->root->hasCachedExpr = true;
> +
> + return (Node *) new_node;
> ```

Thanks, I agree with you and I'll change it accordingly.

-- 
Marina Polyakova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Hello, hackers!

Here there's a seventh version of the patch for the precalculation of 
stable or immutable functions, stable or immutable operators and other 
nonvolatile expressions. It is rebased on the top of master and has some 
code cleanup [1]. See attached patch.

About TPC-H tests using dbt3 (see the attached small archieve):
* they were made based on commit 
255f14183ac7bc6a83a5bb00d67d5ac7e8b645f1;
* they take some time so I have used only scales 1 and 4;
* 3 attempts are used to eliminate some noise; so you can see the 
minimum, median and maximum execution time for each query on the graphs.
About their results:
* as expected, cached expressions were not used in queries;
* it seems that there is no obvious degradation of performance.

Any suggestions are welcome!

[1] Fixed:

>> Also I see this pattern quite some time, maybe it makes sense to move
>> it to a function?
>> 
>> ```
>> + /* create and return CachedExpr */
>> + CachedExpr *new_node = makeNode(CachedExpr);
>> + new_node->subexpr = (CacheableExpr *) current_node;
>> +
>> + context->root->hasCachedExpr = true;
>> +
>> + return (Node *) new_node;
>> ```
> 
> Thanks, I agree with you and I'll change it accordingly.

-- 
Marina Polyakova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

The following review has been posted through the commitfest application:
make installcheck-world:  tested, passed
Implements feature:       tested, passed
Spec compliant:           tested, passed
Documentation:            tested, passed

I can confirm this code works. However, since this is quite a large patch, I believe we better have a second reviewer
ora very attentive committer.  

The new status of this patch is: Ready for Committer

Aleksander Alekseev <a.alekseev@postgrespro.ru> writes:
> I can confirm this code works. However, since this is quite a large patch, I believe we better have a second reviewer
ora very attentive committer.  
> The new status of this patch is: Ready for Committer

This is indeed quite a large patch, but it seems to me it could become
smaller.  After a bit of review:

1. I do not like what you've done with ParamListInfo.  The changes around
that are invasive, accounting for a noticeable part of the patch bulk,
and I don't think they're well designed.  Having to cast back and forth
between ParamListInfo and ParamListInfoCommon and so on is ugly and prone
to cause (or hide) errors.  And I don't really understand why
ParamListInfoPrecalculationData exists at all.  Couldn't you have gotten
the same result with far fewer notational changes by defining another
PARAM_FLAG bit in the existing pflags field?  (Or alternatively, maybe
the real need here is for another ParamKind value for Param nodes?)

I also dislike this approach because it effectively throws away the
support for "virtual" param arrays that I added in commit 6719b238e:
ParamListInfoPrecalculationData has no support for dynamically determined
parameter properties, which is surely something that somebody will need.
(It's just luck that the patch doesn't break plpgsql today.)  I realize
that that's a recent commit and the code I'm complaining about predates
it, but we need to adjust this so that it fits in with the new approach.
See comment block at lines 25ff in params.h.

2. I don't follow the need for the also-rather-invasive changes to domain
constraint data structures.  I do see that the patch attempts to make
CoerceToDomain nodes cacheable, which is flat wrong and has to be ripped
out.  You *cannot* assume that the planner has access to the same domain
constraints that will apply at runtime.

I've occasionally thought that we should hook domain constraint changes
into the plan invalidation mechanism, which would make it possible for
the planner to assume that the constraints seen at planning time will
apply at execution.  Whereupon we could have the planner insert domain
constraint expressions into the plan rather than leaving those to be
collected at query startup by execExpr.c, and then do things like
constant-folding and cacheing CoerceToDomain nodes.  But that would be
a rather large and very domain-specific change, and so it would be fit
material for a different patch IMO.  I recommend that for now you just
treat CoerceToDomain as an uncacheable expression type and rip all the
domain-related changes out of this patch.

3. I think you should also try hard to get rid of the need for
PlannedStmt.hasCachedExpr.  AFAICS there's only one place that is
using that flag, which is exec_simple_check_plan, and I have to
think there are better ways we could deal with that.  In particular,
I don't understand why you haven't simply set up plpgsql parameter
references to be noncacheable.  Or maybe what we'd better do is
disable CacheExpr insertions into potentially-simple plans in the
first place.  As you have it here, it's possible for recompilation
of an expression to result in a change in whether it should be deemed
simple or not, which will break things (cf commit 00418c612).

4. I don't like the way that you've inserted
"replace_qual_cached_expressions" and
"replace_pathtarget_cached_expressions" calls into seemingly random places
in the planner.  Why isn't that being done uniformly during expression
preprocessing?  There's no apparent structure to where you've put these
calls, and so they seem really vulnerable to errors of omission.  Also,
if this were done in expression preprocessing, there'd be a chance of
combining it with some existing pass over expression trees instead of
having to do a separate (and expensive) expression tree mutation.
I can't help suspecting that eval_const_expressions could take this on
as an additional responsibility with a lot less than a thousand new lines
of code.

5. BTW, cost_eval_cacheable_expr seems like useless restructuring as well.
Why aren't you just recursively applying the regular costing function?


If you did all of the above it would result in a pretty significant
reduction of the number of places touched by the patch, which would make
it easier to see what's going on.  Then we could start to discuss, for
instance, what does the "isConstParam" flag actually *mean* and why
is it different from PARAM_FLAG_CONST?  And what in the world is
CheckBoundParams about?  The internal documentation in this patch
isn't quite nonexistent, but it's well short of being in a
committable state IMO.

            regards, tom lane

[ I'm sending this comment separately because I think it's an issue
Andres might take an interest in. ]

Marina Polyakova <m.polyakova@postgrespro.ru> writes:
> [ v7-0001-Precalculate-stable-and-immutable-functions.patch ]

Another thing that's bothering me is that the execution semantics
you're proposing for CachedExpr seem rather inflexible.  AFAICS, once a
CachedExpr has run once, it will hang on to the result value and keep
returning that for the entire lifespan of the compiled expression.
We already noted that that breaks plpgsql's "simple expression"
logic, and it seems inevitable to me that it will be an issue for
other places as well.  I think it'd be a better design if we had some
provision for resetting the cached values, short of recompiling the
expression from scratch.

One way that occurs to me to do this is to replace the simple boolean
isExecuted flags with a generation counter, and add a master generation
counter to ExprState.  The rule for executing CachedExpr would be "if my
generation counter is different from the ExprState's counter, then
evaluate the subexpression and copy the ExprState's counter into mine".
Then the procedure for forcing recalculation of cached values is just to
increment the ExprState's counter.  There are other ways one could imagine
doing this --- for instance, I initially thought of keeping the master
counter in the ExprContext being used to run the expression.  But you need
some way to remember what counter value was used last with a particular
expression, so probably keeping it in ExprState is better.

Or we could just brute-force it by providing a function that runs through
a compiled expression step list and resets the isExecuted flag for each
EEOP_CACHEDEXPR_IF_CACHED step it finds.  A slightly less brute-force
way is to link those steps together in a list, so that the function
doesn't have to visit irrelevant steps.  If the reset function were seldom
used then the extra cycles for this wouldn't be very expensive.  But I'm
not sure it will be seldom used --- it seems like plpgsql simple
expressions will be doing this every time --- so I think the counter
approach might be a better idea.

I'm curious to know whether Andres has some other ideas, or whether he
feels this is all a big wart on the compiled-expression concept.  I don't
think there are any existing cases where we keep any meaningful state
across executions of a compiled-expression data structure; maybe that's
a bad idea in itself.

            regards, tom lane

Thank you so much for your comments!! I'll answer a bit later because 
now I'm trying to find a test for int128 on Solaris 10.. [1]

On 17-01-2018 1:05, Tom Lane wrote:
> [ I'm sending this comment separately because I think it's an issue
> Andres might take an interest in. ]
> 
> Marina Polyakova <m.polyakova@postgrespro.ru> writes:
>> [ v7-0001-Precalculate-stable-and-immutable-functions.patch ]
> 
> Another thing that's bothering me is that the execution semantics
> you're proposing for CachedExpr seem rather inflexible.  AFAICS, once a
> CachedExpr has run once, it will hang on to the result value and keep
> returning that for the entire lifespan of the compiled expression.
> We already noted that that breaks plpgsql's "simple expression"
> logic, and it seems inevitable to me that it will be an issue for
> other places as well.  I think it'd be a better design if we had some
> provision for resetting the cached values, short of recompiling the
> expression from scratch.
> 
> One way that occurs to me to do this is to replace the simple boolean
> isExecuted flags with a generation counter, and add a master generation
> counter to ExprState.  The rule for executing CachedExpr would be "if 
> my
> generation counter is different from the ExprState's counter, then
> evaluate the subexpression and copy the ExprState's counter into mine".
> Then the procedure for forcing recalculation of cached values is just 
> to
> increment the ExprState's counter.  There are other ways one could 
> imagine
> doing this --- for instance, I initially thought of keeping the master
> counter in the ExprContext being used to run the expression.  But you 
> need
> some way to remember what counter value was used last with a particular
> expression, so probably keeping it in ExprState is better.
> 
> Or we could just brute-force it by providing a function that runs 
> through
> a compiled expression step list and resets the isExecuted flag for each
> EEOP_CACHEDEXPR_IF_CACHED step it finds.  A slightly less brute-force
> way is to link those steps together in a list, so that the function
> doesn't have to visit irrelevant steps.  If the reset function were 
> seldom
> used then the extra cycles for this wouldn't be very expensive.  But 
> I'm
> not sure it will be seldom used --- it seems like plpgsql simple
> expressions will be doing this every time --- so I think the counter
> approach might be a better idea.
> 
> I'm curious to know whether Andres has some other ideas, or whether he
> feels this is all a big wart on the compiled-expression concept.  I 
> don't
> think there are any existing cases where we keep any meaningful state
> across executions of a compiled-expression data structure; maybe that's
> a bad idea in itself.
> 
>             regards, tom lane

[1] 
https://www.postgresql.org/message-id/18209.1516059711%40sss.pgh.pa.us

-- 
Marina Polyakova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

As I said, thank you so much for your comments!!

On 17-01-2018 0:30, Tom Lane wrote:
> ...
> This is indeed quite a large patch, but it seems to me it could become
> smaller. After a bit of review:
> 
> 1. I do not like what you've done with ParamListInfo. The changes 
> around
> that are invasive, accounting for a noticeable part of the patch bulk,
> and I don't think they're well designed. Having to cast back and forth
> between ParamListInfo and ParamListInfoCommon and so on is ugly and 
> prone
> to cause (or hide) errors. And I don't really understand why
> ParamListInfoPrecalculationData exists at all. Couldn't you have gotten
> the same result with far fewer notational changes by defining another
> PARAM_FLAG bit in the existing pflags field? (Or alternatively, maybe
> the real need here is for another ParamKind value for Param nodes?)
> 
> I also dislike this approach because it effectively throws away the
> support for "virtual" param arrays that I added in commit 6719b238e:
> ParamListInfoPrecalculationData has no support for dynamically 
> determined
> parameter properties, which is surely something that somebody will 
> need.
> (It's just luck that the patch doesn't break plpgsql today.) I realize
> that that's a recent commit and the code I'm complaining about predates
> it, but we need to adjust this so that it fits in with the new 
> approach.
> See comment block at lines 25ff in params.h.

I'll try to use ParamListInfoData for generic plans (= to get cached 
expressions for params of prepared statements where possible) without 
changing its infrastructure.

> 2. I don't follow the need for the also-rather-invasive changes to 
> domain
> constraint data structures. I do see that the patch attempts to make
> CoerceToDomain nodes cacheable, which is flat wrong and has to be 
> ripped
> out. You *cannot* assume that the planner has access to the same domain
> constraints that will apply at runtime.

I'm sorry, I did not know about this :-[

> I've occasionally thought that we should hook domain constraint changes
> into the plan invalidation mechanism, which would make it possible for
> the planner to assume that the constraints seen at planning time will
> apply at execution. Whereupon we could have the planner insert domain
> constraint expressions into the plan rather than leaving those to be
> collected at query startup by execExpr.c, and then do things like
> constant-folding and cacheing CoerceToDomain nodes. But that would be
> a rather large and very domain-specific change, and so it would be fit
> material for a different patch IMO. I recommend that for now you just
> treat CoerceToDomain as an uncacheable expression type and rip all the
> domain-related changes out of this patch.

I'll fix this.

> 3. I think you should also try hard to get rid of the need for
> PlannedStmt.hasCachedExpr. AFAICS there's only one place that is
> using that flag, which is exec_simple_check_plan, and I have to
> think there are better ways we could deal with that. In particular,
> I don't understand why you haven't simply set up plpgsql parameter
> references to be noncacheable. Or maybe what we'd better do is
> disable CacheExpr insertions into potentially-simple plans in the
> first place. As you have it here, it's possible for recompilation
> of an expression to result in a change in whether it should be deemed
> simple or not, which will break things (cf commit 00418c612).

I'm sorry, I'll fix the use of parameters in this case. And I'll think 
how to get rid of the need for PlannedStmt.hasCachedExpr when there're 
possible cached expressions without parameters.

> 4. I don't like the way that you've inserted
> "replace_qual_cached_expressions" and
> "replace_pathtarget_cached_expressions" calls into seemingly random 
> places
> in the planner. Why isn't that being done uniformly during expression
> preprocessing? There's no apparent structure to where you've put these
> calls, and so they seem really vulnerable to errors of omission.

I'll fix this.

> Also,
> if this were done in expression preprocessing, there'd be a chance of
> combining it with some existing pass over expression trees instead of
> having to do a separate (and expensive) expression tree mutation.
> I can't help suspecting that eval_const_expressions could take this on
> as an additional responsibility with a lot less than a thousand new 
> lines
> of code.

 From quick look I see no contradictions so I'll try to implement it.

> 5. BTW, cost_eval_cacheable_expr seems like useless restructuring as 
> well.
> Why aren't you just recursively applying the regular costing function?

Such a stupid mistake :( I'll fix this.

> If you did all of the above it would result in a pretty significant
> reduction of the number of places touched by the patch, which would 
> make
> it easier to see what's going on. Then we could start to discuss, for
> instance, what does the "isConstParam" flag actually *mean* and why
> is it different from PARAM_FLAG_CONST?

AFAIU they do not differ, and as I said above I'll try not to change the 
infrastructure of ParamListInfoData.

> And what in the world is
> CheckBoundParams about? The internal documentation in this patch
> isn't quite nonexistent, but it's well short of being in a
> committable state IMO.

I'll try to improve it, for CheckBoundParams (if I understood you 
correctly) and others.

-- 
Marina Polyakova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On 17-01-2018 1:05, Tom Lane wrote:
> [ I'm sending this comment separately because I think it's an issue
> Andres might take an interest in. ]
> 
> Marina Polyakova <m.polyakova@postgrespro.ru> writes:
>> [ v7-0001-Precalculate-stable-and-immutable-functions.patch ]
> 
> Another thing that's bothering me is that the execution semantics
> you're proposing for CachedExpr seem rather inflexible.  AFAICS, once a
> CachedExpr has run once, it will hang on to the result value and keep
> returning that for the entire lifespan of the compiled expression.
> We already noted that that breaks plpgsql's "simple expression"
> logic, and it seems inevitable to me that it will be an issue for
> other places as well.  I think it'd be a better design if we had some
> provision for resetting the cached values, short of recompiling the
> expression from scratch.
> 
> One way that occurs to me to do this is to replace the simple boolean
> isExecuted flags with a generation counter, and add a master generation
> counter to ExprState.  The rule for executing CachedExpr would be "if 
> my
> generation counter is different from the ExprState's counter, then
> evaluate the subexpression and copy the ExprState's counter into mine".
> Then the procedure for forcing recalculation of cached values is just 
> to
> increment the ExprState's counter.  There are other ways one could 
> imagine
> doing this --- for instance, I initially thought of keeping the master
> counter in the ExprContext being used to run the expression.  But you 
> need
> some way to remember what counter value was used last with a particular
> expression, so probably keeping it in ExprState is better.
> 
> Or we could just brute-force it by providing a function that runs 
> through
> a compiled expression step list and resets the isExecuted flag for each
> EEOP_CACHEDEXPR_IF_CACHED step it finds.  A slightly less brute-force
> way is to link those steps together in a list, so that the function
> doesn't have to visit irrelevant steps.  If the reset function were 
> seldom
> used then the extra cycles for this wouldn't be very expensive.  But 
> I'm
> not sure it will be seldom used --- it seems like plpgsql simple
> expressions will be doing this every time --- so I think the counter
> approach might be a better idea.

Thank you very much! I'll try to implement something from this.

> I'm curious to know whether Andres has some other ideas, or whether he
> feels this is all a big wart on the compiled-expression concept.  I 
> don't
> think there are any existing cases where we keep any meaningful state
> across executions of a compiled-expression data structure; maybe that's
> a bad idea in itself.

-- 
Marina Polyakova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Hi,


On 2018-01-16 17:05:01 -0500, Tom Lane wrote:
> [ I'm sending this comment separately because I think it's an issue
> Andres might take an interest in. ]

Thanks for that. I indeed am interested. Sorry for the late response,
was very deep into the JIT patch.


> Marina Polyakova <m.polyakova@postgrespro.ru> writes:
> > [ v7-0001-Precalculate-stable-and-immutable-functions.patch ]
> 
> Another thing that's bothering me is that the execution semantics
> you're proposing for CachedExpr seem rather inflexible.  AFAICS, once a
> CachedExpr has run once, it will hang on to the result value and keep
> returning that for the entire lifespan of the compiled expression.
> We already noted that that breaks plpgsql's "simple expression"
> logic, and it seems inevitable to me that it will be an issue for
> other places as well.  I think it'd be a better design if we had some
> provision for resetting the cached values, short of recompiling the
> expression from scratch.

Hm. Yes, that makes me uncomfortable as well.


> One way that occurs to me to do this is to replace the simple boolean
> isExecuted flags with a generation counter, and add a master generation
> counter to ExprState.  The rule for executing CachedExpr would be "if my
> generation counter is different from the ExprState's counter, then
> evaluate the subexpression and copy the ExprState's counter into mine".
> Then the procedure for forcing recalculation of cached values is just to
> increment the ExprState's counter.  There are other ways one could imagine
> doing this --- for instance, I initially thought of keeping the master
> counter in the ExprContext being used to run the expression.  But you need
> some way to remember what counter value was used last with a particular
> expression, so probably keeping it in ExprState is better.

I'm not a big fan of this solution. We seem to be inventing more and
more places we keep state, rather than the contrary.


> Or we could just brute-force it by providing a function that runs through
> a compiled expression step list and resets the isExecuted flag for each
> EEOP_CACHEDEXPR_IF_CACHED step it finds.  A slightly less brute-force
> way is to link those steps together in a list, so that the function
> doesn't have to visit irrelevant steps.  If the reset function were seldom
> used then the extra cycles for this wouldn't be very expensive.  But I'm
> not sure it will be seldom used --- it seems like plpgsql simple
> expressions will be doing this every time --- so I think the counter
> approach might be a better idea.

Hm, that sounds like it'd not be cheap.


> I'm curious to know whether Andres has some other ideas, or whether he
> feels this is all a big wart on the compiled-expression concept.

I don't have too many "artistic" concerns from the compiled expression
POV. The biggest issue I see is that it'll make it a bit harder to
separate out the expression compilation phase from the expression
instantiation phase - something I think we definitely want.


> I don't think there are any existing cases where we keep any
> meaningful state across executions of a compiled-expression data
> structure; maybe that's a bad idea in itself.

To me, who has *not* followed the thread in detail, it sounds like the
relevant data shouldn't be stored inside the expression itself.  For
one, we do not want to have to visit every single simple expression and
reset them, for another it architecturally doesn't seem the right place
to me.  Storing all cached values in an EState or ExprContext (the
latter referring to the former) somewhat alike the values for Param's
sounds a lot more reasonable to me.

Besides that it seems to make it a lot easier to reset the values, it
also seems like it makes it a lot cleaner to cache stable functions
across multiple expressions in different places in a query? ISTM having
expression steps to actually compute the expression value in every
referencing expression is quite the waste.

This all reminds me a lot of the infrastructure for Params...

Greetings,

Andres Freund

Andres Freund <andres@anarazel.de> writes:
> On 2018-01-16 17:05:01 -0500, Tom Lane wrote:
>> I'm curious to know whether Andres has some other ideas, or whether he
>> feels this is all a big wart on the compiled-expression concept.

> I don't have too many "artistic" concerns from the compiled expression
> POV. The biggest issue I see is that it'll make it a bit harder to
> separate out the expression compilation phase from the expression
> instantiation phase - something I think we definitely want.

Hmm, there's no such distinction now, so could you explain what you
have in mind there?

>> I don't think there are any existing cases where we keep any
>> meaningful state across executions of a compiled-expression data
>> structure; maybe that's a bad idea in itself.

> Storing all cached values in an EState or ExprContext (the
> latter referring to the former) somewhat alike the values for Param's
> sounds a lot more reasonable to me.
> ...
> This all reminds me a lot of the infrastructure for Params...

Yeah, one thing I was thinking about in connection with this is the
stuff associated with propagating changes in outer-reference Params
(the extParam/allParam/chgParam mess).  I wonder if we could find
a way to unify that with this feature.

Keeping the stored value of a CachedExpr in a Param slot is an
interesting idea indeed.

            regards, tom lane

On 2018-01-24 15:10:56 -0500, Tom Lane wrote:
> Andres Freund <andres@anarazel.de> writes:
> > On 2018-01-16 17:05:01 -0500, Tom Lane wrote:
> >> I'm curious to know whether Andres has some other ideas, or whether he
> >> feels this is all a big wart on the compiled-expression concept.
>
> > I don't have too many "artistic" concerns from the compiled expression
> > POV. The biggest issue I see is that it'll make it a bit harder to
> > separate out the expression compilation phase from the expression
> > instantiation phase - something I think we definitely want.
>
> Hmm, there's no such distinction now, so could you explain what you
> have in mind there?

There's a few related concerns I have:
- For OLTP workloads using prepared statements, we often spend the
  majority of the time doing ExecInitExpr() and related tasks like
  computing tupledescs.
- For OLTP workloads the low allocation density for things hanging off
  ExprState's and PlanState nodes is a significant concern. The number
  of allocations cause overhead, the overhead wastes memory and lowers
  cache hit ratios.
- For JIT we currently end up encoding specific pointer values into the
  generated code. As these obviously prevent reuse of the generated
  function, this noticeably reduces the applicability of JITing to fewer
  usecases. JITing is actually quite beneficial for a lot of OLTP
  workloads too, but it's too expensive to do every query.

To address these, I think we may want to split the the division of labor
a bit. Expression instantiation (i.e. ExecReadyExpr()) should happen at
executor startup, but in a lot of cases "compiling" the steps itself
should happen at plan time. Obviously that means the steps themselves
can't contain plain pointers, as the per-execution memory will be
located in different places.  So I think what we should have is that
expression initialization just computes the size of required memory for
all steps and puts *offsets* into that in the steps. After that
expression instantiation either leaves them alone and evaluation uses
relative pointers (cheap-ish e.g. on x86 due to lea), or just turn the
relative pointers into absolute ones.
That means that all the memory for all steps of an ExprState would be
allocated in one chunk, reducing allocation overhead and increasing
cache hit ratios considerably.

I've experimented a bit with a rough rough hack of the above (purely at
execution time), and it doesn't seem too hard.


> Keeping the stored value of a CachedExpr in a Param slot is an
> interesting idea indeed.

We keep coming back to this, IIRC we had a pretty similar discussion
around redesigning caseValue_datum/isNull domainValue_datum/isNull to be
less ugly. There also was
https://www.postgresql.org/message-id/20171116182208.kcvf75nfaldv36uh@alap3.anarazel.de
where we discussed using something similar to PARAM_EXEC Param nodes to
allow inlining of volatile functions.

ISTM, there might be some value to consider all of them in the design of
the new mechanism.

Greetings,

Andres Freund

Hello!

On 24-01-2018 23:36, Andres Freund wrote:
> On 2018-01-24 15:10:56 -0500, Tom Lane wrote:
> ...
>> Keeping the stored value of a CachedExpr in a Param slot is an
>> interesting idea indeed.
> 
> We keep coming back to this, IIRC we had a pretty similar discussion
> around redesigning caseValue_datum/isNull domainValue_datum/isNull to 
> be
> less ugly. There also was
> https://www.postgresql.org/message-id/20171116182208.kcvf75nfaldv36uh@alap3.anarazel.de
> where we discussed using something similar to PARAM_EXEC Param nodes to
> allow inlining of volatile functions.
> 
> ISTM, there might be some value to consider all of them in the design 
> of
> the new mechanism.

Thank you both very much for this discussion and for the link on that 
thread! Now I'm working on the patch, thanks to Tom Lane's comments 
earlier [1], and I'll try to implement something of this..

[1] 
https://www.postgresql.org/message-id/403e0ae329c6868b3f3467eac92cc04d%40postgrespro.ru

-- 
Marina Polyakova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Hello, hackers!

This is the 8-th version of the patch for the precalculation of stable 
or immutable functions, stable or immutable operators and other 
nonvolatile expressions. This is a try to fix the most problems (I'm 
sorry, it took some time..) that Tom Lane and Andres Freund mentioned in 
[1], [2] and [3]. It is based on the top of master, and on my computer 
make check-world passes. And I'll continue work on it.

Any suggestions are welcome!

[1] 
https://www.postgresql.org/message-id/27837.1516138246%40sss.pgh.pa.us
[2] 
https://www.postgresql.org/message-id/29643.1516140301%40sss.pgh.pa.us
[3] 
https://www.postgresql.org/message-id/20180124203616.3gx4vm45hpoijpw3%40alap3.anarazel.de

> On 17-01-2018 0:30, Tom Lane wrote:
> ...
> This is indeed quite a large patch, but it seems to me it could become
> smaller. After a bit of review:
> 
> 1. I do not like what you've done with ParamListInfo. The changes 
> around
> that are invasive, accounting for a noticeable part of the patch bulk,
> and I don't think they're well designed. Having to cast back and forth
> between ParamListInfo and ParamListInfoCommon and so on is ugly and 
> prone
> to cause (or hide) errors. And I don't really understand why
> ParamListInfoPrecalculationData exists at all. Couldn't you have gotten
> the same result with far fewer notational changes by defining another
> PARAM_FLAG bit in the existing pflags field? (Or alternatively, maybe
> the real need here is for another ParamKind value for Param nodes?)
> 
> I also dislike this approach because it effectively throws away the
> support for "virtual" param arrays that I added in commit 6719b238e:
> ParamListInfoPrecalculationData has no support for dynamically 
> determined
> parameter properties, which is surely something that somebody will 
> need.
> (It's just luck that the patch doesn't break plpgsql today.) I realize
> that that's a recent commit and the code I'm complaining about predates
> it, but we need to adjust this so that it fits in with the new 
> approach.
> See comment block at lines 25ff in params.h.

Changed, now only the new flag PARAM_FLAG_PRECALCULATED

> 2. I don't follow the need for the also-rather-invasive changes to 
> domain
> constraint data structures.  I do see that the patch attempts to make
> CoerceToDomain nodes cacheable, which is flat wrong and has to be 
> ripped
> out.  You *cannot* assume that the planner has access to the same 
> domain
> constraints that will apply at runtime.

Removed

> 4. I don't like the way that you've inserted
> "replace_qual_cached_expressions" and
> "replace_pathtarget_cached_expressions" calls into seemingly random 
> places
> in the planner.  Why isn't that being done uniformly during expression
> preprocessing?  There's no apparent structure to where you've put these
> calls, and so they seem really vulnerable to errors of omission.  Also,
> if this were done in expression preprocessing, there'd be a chance of
> combining it with some existing pass over expression trees instead of
> having to do a separate (and expensive) expression tree mutation.
> I can't help suspecting that eval_const_expressions could take this on
> as an additional responsibility with a lot less than a thousand new 
> lines
> of code.

eval_const_expressions is changed accordingly and, thank you, now 
there're fewer omissions)

> 5. BTW, cost_eval_cacheable_expr seems like useless restructuring as 
> well.
> Why aren't you just recursively applying the regular costing function?

Fixed

> And what in the world is
> CheckBoundParams about?  The internal documentation in this patch
> isn't quite nonexistent, but it's well short of being in a
> committable state IMO.

This is a try to improve it..

> 3. I think you should also try hard to get rid of the need for
> PlannedStmt.hasCachedExpr.  AFAICS there's only one place that is
> using that flag, which is exec_simple_check_plan, and I have to
> think there are better ways we could deal with that.  In particular,
> I don't understand why you haven't simply set up plpgsql parameter
> references to be noncacheable.  Or maybe what we'd better do is
> disable CacheExpr insertions into potentially-simple plans in the
> first place.  As you have it here, it's possible for recompilation
> of an expression to result in a change in whether it should be deemed
> simple or not, which will break things (cf commit 00418c612).

<...>

> Another thing that's bothering me is that the execution semantics
> you're proposing for CachedExpr seem rather inflexible.  AFAICS, once a
> CachedExpr has run once, it will hang on to the result value and keep
> returning that for the entire lifespan of the compiled expression.
> We already noted that that breaks plpgsql's "simple expression"
> logic, and it seems inevitable to me that it will be an issue for
> other places as well.  I think it'd be a better design if we had some
> provision for resetting the cached values, short of recompiling the
> expression from scratch.
> 
> One way that occurs to me to do this is to replace the simple boolean
> isExecuted flags with a generation counter, and add a master generation
> counter to ExprState.  The rule for executing CachedExpr would be "if 
> my
> generation counter is different from the ExprState's counter, then
> evaluate the subexpression and copy the ExprState's counter into mine".
> Then the procedure for forcing recalculation of cached values is just 
> to
> increment the ExprState's counter.  There are other ways one could 
> imagine
> doing this --- for instance, I initially thought of keeping the master
> counter in the ExprContext being used to run the expression.  But you 
> need
> some way to remember what counter value was used last with a particular
> expression, so probably keeping it in ExprState is better.

I did something like that..

>> Keeping the stored value of a CachedExpr in a Param slot is an
>> interesting idea indeed.
> 
> We keep coming back to this, IIRC we had a pretty similar discussion
> around redesigning caseValue_datum/isNull domainValue_datum/isNull to 
> be
> less ugly. There also was
> https://www.postgresql.org/message-id/20171116182208.kcvf75nfaldv36uh@alap3.anarazel.de
> where we discussed using something similar to PARAM_EXEC Param nodes to
> allow inlining of volatile functions.
> 
> ISTM, there might be some value to consider all of them in the design 
> of
> the new mechanism.

I'm sorry, the other parts have occupied all the time, and I'll work on 
it..

-- 
Marina Polyakova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On Thu, Feb 1, 2018 at 6:01 PM, Marina Polyakova
<m.polyakova@postgrespro.ru> wrote:
> This is the 8-th version of the patch for the precalculation of stable or
> immutable functions, stable or immutable operators and other nonvolatile
> expressions. This is a try to fix the most problems (I'm sorry, it took some
> time..) that Tom Lane and Andres Freund mentioned in [1], [2] and [3]. It is
> based on the top of master, and on my computer make check-world passes. And
> I'll continue work on it.

Hi Marina,

FYI I saw a repeatable crash in the contrib regression tests when
running make check-world with this patch applied.

test hstore_plperl            ... FAILED (test process exited with exit code 2)
test hstore_plperlu           ... FAILED (test process exited with exit code 2)
test create_transform         ... FAILED

I'm not sure why it passes for you but fails here, but we can see from
the backtrace[1] that ExecInitExprRec is receiving a null node pointer
on this Ubuntu Trusty GCC 4.8 amd64 system.

[1] https://travis-ci.org/postgresql-cfbot/postgresql/builds/337255374

-- 
Thomas Munro
http://www.enterprisedb.com

Hello!

Thank you for reporting! I'll try to get it on our buildfarm..

On 05-02-2018 0:10, Thomas Munro wrote:
> On Thu, Feb 1, 2018 at 6:01 PM, Marina Polyakova
> <m.polyakova@postgrespro.ru> wrote:
>> This is the 8-th version of the patch for the precalculation of stable 
>> or
>> immutable functions, stable or immutable operators and other 
>> nonvolatile
>> expressions. This is a try to fix the most problems (I'm sorry, it 
>> took some
>> time..) that Tom Lane and Andres Freund mentioned in [1], [2] and [3]. 
>> It is
>> based on the top of master, and on my computer make check-world 
>> passes. And
>> I'll continue work on it.
> 
> Hi Marina,
> 
> FYI I saw a repeatable crash in the contrib regression tests when
> running make check-world with this patch applied.
> 
> test hstore_plperl            ... FAILED (test process exited with exit 
> code 2)
> test hstore_plperlu           ... FAILED (test process exited with exit 
> code 2)
> test create_transform         ... FAILED
> 
> I'm not sure why it passes for you but fails here, but we can see from
> the backtrace[1] that ExecInitExprRec is receiving a null node pointer
> on this Ubuntu Trusty GCC 4.8 amd64 system.
> 
> [1] https://travis-ci.org/postgresql-cfbot/postgresql/builds/337255374

-- 
Marina Polyakova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Hi,

On 2018-02-01 08:01:48 +0300, Marina Polyakova wrote:
> > ISTM, there might be some value to consider all of them in the design of
> > the new mechanism.
>
> I'm sorry, the other parts have occupied all the time, and I'll work on it..

That has, as far as I can see, not happened. And the patch has been
reported as failing by Thomas a while ago.  So I'm inclined to mark this
as returned with feedback for this CF?

- Andres

Hello!

I fixed the failure that Thomas pointed out to me, and I'm finishing 
work on it, but it took me a while to study this part of the executor..

On 02-03-2018 0:11, Andres Freund wrote:
> Hi,
> 
> On 2018-02-01 08:01:48 +0300, Marina Polyakova wrote:
>> > ISTM, there might be some value to consider all of them in the design of
>> > the new mechanism.
>> 
>> I'm sorry, the other parts have occupied all the time, and I'll work 
>> on it..
> 
> That has, as far as I can see, not happened. And the patch has been
> reported as failing by Thomas a while ago.  So I'm inclined to mark 
> this
> as returned with feedback for this CF?
> 
> - Andres

-- 
Marina Polyakova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Hi,

On 2018-03-02 11:22:01 +0300, Marina Polyakova wrote:
> I fixed the failure that Thomas pointed out to me, and I'm finishing work on
> it, but it took me a while to study this part of the executor..

I unfortunately think that makes this too late for v11, and we should
mark this as returned with feedback.

Greetings,

Andres Freund

Ok!

On 02-03-2018 22:56, Andres Freund wrote:
> Hi,
> 
> On 2018-03-02 11:22:01 +0300, Marina Polyakova wrote:
>> I fixed the failure that Thomas pointed out to me, and I'm finishing 
>> work on
>> it, but it took me a while to study this part of the executor..
> 
> I unfortunately think that makes this too late for v11, and we should
> mark this as returned with feedback.
> 
> Greetings,
> 
> Andres Freund

-- 
Marina Polyakova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On 3/3/18 2:42 AM, Marina Polyakova wrote:
> Ok!
> 
> On 02-03-2018 22:56, Andres Freund wrote:
>> Hi,
>>
>> On 2018-03-02 11:22:01 +0300, Marina Polyakova wrote:
>>> I fixed the failure that Thomas pointed out to me, and I'm finishing
>>> work on
>>> it, but it took me a while to study this part of the executor..
>>
>> I unfortunately think that makes this too late for v11, and we should
>> mark this as returned with feedback.

Marked as Returned with Feedback.

Regards,
-- 
-David
david@pgmasters.net

Hello, hackers!

Here there's a 9-th version of the patches for the precalculation of 
stable or immutable functions, stable or immutable operators and other 
nonvolatile expressions. This is a try to execute cached expressions as 
PARAM_EXEC, thanks to the comments of Tom Lane and Andres Freund [1].

v9-0001-Move-the-FreeExecutorState-call-in-the-StoreAttrD.patch
- a patch in which the call of FreeExecutorState() is used only after 
copying the result from the executor memory context.

v9-0002-Compile-check-constraints-for-domains-into-separa.patch
- a patch for compiling CoerceToDomain nodes into separate ExprStates so 
they can be used with lists of their own cached expressions that are 
compiled as PARAM_EXEC.

v9-0003-Precalculate-stable-immutable-expressions-infrast.patch
- a patch that simply adds new nodes / new fields to existing nodes and 
accordingly adds / changes the main functions of the nodes for all of 
them.

v9-0004-Precalculate-stable-immutable-expressions-executo.patch
- a patch for compiling cached expressions as PARAM_EXEC.

v9-0005-Precalculate-stable-and-immutable-functions-plann.patch
- the main patch that adds the CachedExpr nodes in 
eval_const_expressions() and processes the cached expressions as 
ordinary expressions in other parts of the code.

v9-0006-Precalculate-stable-immutable-expressions-prepare.patch
- a patch for supporting cached expressions in prepared statements.

As usual any suggestions are welcome!

[1] On 24-01-2018 22:20, Andres Freund wrote:
> To me, who has *not* followed the thread in detail, it sounds like the
> relevant data shouldn't be stored inside the expression itself. For
> one, we do not want to have to visit every single simple expression and
> reset them, for another it architecturally doesn't seem the right place
> to me. Storing all cached values in an EState or ExprContext (the
> latter referring to the former) somewhat alike the values for Param's
> sounds a lot more reasonable to me.

> Besides that it seems to make it a lot easier to reset the values, it
> also seems like it makes it a lot cleaner to cache stable functions
> across multiple expressions in different places in a query? ISTM having
> expression steps to actually compute the expression value in every
> referencing expression is quite the waste.

The problem is that with the function expression_planner some 
expressions are planned at compile time.. You can also use the function 
ExecInitExpr without the parent PlanState node => without a pointer to 
the corresponding EState.

-- 
Marina Polyakova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Hi Marina,

On Thu, May 24, 2018 at 04:00:33PM +0300, Marina Polyakova wrote:
> Here there's a 9-th version of the patches for the precalculation of stable
> or immutable functions, stable or immutable operators and other nonvolatile
> expressions. This is a try to execute cached expressions as PARAM_EXEC,
> thanks to the comments of Tom Lane and Andres Freund [1].

Please note that v9-0004 fails to apply, so a rebase is needed.  This
patch is moved to next CF, waiting on author.
--
Michael

> On Tue, Oct 2, 2018 at 4:22 AM Michael Paquier <michael@paquier.xyz> wrote:
>
> On Thu, May 24, 2018 at 04:00:33PM +0300, Marina Polyakova wrote:
> > Here there's a 9-th version of the patches for the precalculation of stable
> > or immutable functions, stable or immutable operators and other nonvolatile
> > expressions. This is a try to execute cached expressions as PARAM_EXEC,
> > thanks to the comments of Tom Lane and Andres Freund [1].
>
> Please note that v9-0004 fails to apply, so a rebase is needed.  This
> patch is moved to next CF, waiting on author.

Unfortunately, patch still has some conflicts, could you please post an updated
version?

On 2018-11-29 18:00:15 +0100, Dmitry Dolgov wrote:
> > On Tue, Oct 2, 2018 at 4:22 AM Michael Paquier <michael@paquier.xyz> wrote:
> >
> > On Thu, May 24, 2018 at 04:00:33PM +0300, Marina Polyakova wrote:
> > > Here there's a 9-th version of the patches for the precalculation of stable
> > > or immutable functions, stable or immutable operators and other nonvolatile
> > > expressions. This is a try to execute cached expressions as PARAM_EXEC,
> > > thanks to the comments of Tom Lane and Andres Freund [1].
> >
> > Please note that v9-0004 fails to apply, so a rebase is needed.  This
> > patch is moved to next CF, waiting on author.
> 
> Unfortunately, patch still has some conflicts, could you please post an updated
> version?

As nothing has happened since, I'm marking this as returned with
feedback.

Greetings,

Andres Freund