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
From cf446cbfc8625701f9e3f32d1870b47de869802a Mon Sep 17 00:00:00 2001
From: Marina Polyakova <m.polyak...@postgrespro.ru>
Date: Thu, 4 May 2017 19:36:05 +0300
Subject: [PATCH 3/3] Precalculate stable functions, costs v1
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 | 58 ++++++++++++++++++++++++++++++++---
1 file changed, 53 insertions(+), 5 deletions(-)
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 52643d0..34707fa 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,44 @@ 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))
+ {
+ 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
+ {
+ 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,13 +3576,22 @@ 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))
{
- 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, CachedExpr))
+ {
+ /*
+ * Calculate subexpression cost per tuple as usual and add it to startup
+ * cost (because subexpression will be executed only once for all
+ * tuples).
+ */
+ context->total.startup += cost_eval_cacheable_expr_per_tuple(
+ get_subexpr((CachedExpr *) node));
}
- else if (IsA(node, OpExpr) ||
- IsA(node, DistinctExpr) ||
+ else if (IsA(node, DistinctExpr) ||
IsA(node, NullIfExpr))
{
/* rely on struct equivalence to treat these all alike */
--
1.9.1
From 508f8b959ff9d1ab78dfc79ab4657b4c10a11690 Mon Sep 17 00:00:00 2001
From: Marina Polyakova <m.polyak...@postgrespro.ru>
Date: Thu, 4 May 2017 19:09:51 +0300
Subject: [PATCH 2/3] Precalculate stable functions, planning and execution v1
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 | 70 ++
src/backend/executor/execExprInterp.c | 191 +++++
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 | 43 ++
src/backend/utils/adt/ruleutils.c | 5 +
src/include/executor/execExpr.h | 38 +-
src/include/optimizer/planner.h | 3 +
src/include/optimizer/tlist.h | 7 +-
src/pl/plpgsql/src/pl_exec.c | 10 +
.../expected/precalculate_stable_functions.out | 827 +++++++++++++++++++++
src/test/regress/serial_schedule | 1 +
.../regress/sql/precalculate_stable_functions.sql | 282 +++++++
15 files changed, 1518 insertions(+), 10 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..c004f4c 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -72,6 +72,8 @@ static bool isAssignmentIndirectionExpr(Expr *expr);
static void ExecInitCoerceToDomain(ExprEvalStep *scratch, CoerceToDomain *ctest,
PlanState *parent, ExprState *state,
Datum *resv, bool *resnull);
+static void ExecInitCachedExpr(ExprEvalStep *scratch, CachedExpr *cachedexpr,
+ PlanState *parent, ExprState *state);
/*
@@ -865,6 +867,14 @@ ExecInitExprRec(Expr *node, PlanState *parent, ExprState *state,
break;
}
+ case T_CachedExpr:
+ {
+ ExecInitCachedExpr(&scratch, (CachedExpr *) node, parent,
+ state);
+ ExprEvalPushStep(state, &scratch);
+ break;
+ }
+
case T_ScalarArrayOpExpr:
{
ScalarArrayOpExpr *opexpr = (ScalarArrayOpExpr *) node;
@@ -2675,3 +2685,63 @@ ExecInitCoerceToDomain(ExprEvalStep *scratch, CoerceToDomain *ctest,
}
}
}
+
+/*
+ * Prepare evaluation of an CachedExpr expression.
+ */
+static void
+ExecInitCachedExpr(ExprEvalStep *scratch, CachedExpr *cachedexpr,
+ PlanState *parent, ExprState *state)
+{
+ FuncData *data = palloc0(sizeof(FuncData));
+
+ /* initialize subexpression as usual */
+ switch (cachedexpr->subexprtype)
+ {
+ case CACHED_FUNCEXPR:
+ {
+ FuncExpr *func = cachedexpr->subexpr.funcexpr;
+
+ ExecInitFunc(scratch, (Expr *) func,
+ func->args, func->funcid, func->inputcollid,
+ parent, state);
+ }
+ break;
+ case CACHED_OPEXPR:
+ {
+ OpExpr *op = cachedexpr->subexpr.opexpr;
+
+ ExecInitFunc(scratch, (Expr *) op,
+ op->args, op->opfuncid, op->inputcollid,
+ parent, state);
+ }
+ break;
+ }
+
+ /* copy data from scratch */
+ *data = scratch->d.func;
+
+ /* initialize scratch as cached expression */
+ switch (scratch->opcode)
+ {
+ case EEOP_FUNCEXPR:
+ scratch->opcode = EEOP_CACHED_FUNCEXPR;
+ break;
+ case EEOP_FUNCEXPR_STRICT:
+ scratch->opcode = EEOP_CACHED_FUNCEXPR_STRICT;
+ break;
+ case EEOP_FUNCEXPR_FUSAGE:
+ scratch->opcode = EEOP_CACHED_FUNCEXPR_FUSAGE;
+ break;
+ case EEOP_FUNCEXPR_STRICT_FUSAGE:
+ scratch->opcode = EEOP_CACHED_FUNCEXPR_STRICT_FUSAGE;
+ break;
+ default:
+ elog(ERROR, "unknown opcode for caching expression");
+ break;
+ }
+ scratch->d.cachedexpr.subexprdata = data;
+ scratch->d.cachedexpr.isExecuted = false;
+ scratch->d.cachedexpr.resnull = false;
+ scratch->d.cachedexpr.resvalue = (Datum) 0;
+}
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index fed0052..8c5989e 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -279,6 +279,7 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
TupleTableSlot *innerslot;
TupleTableSlot *outerslot;
TupleTableSlot *scanslot;
+ MemoryContext oldContext; /* for EEOP_CACHED_* */
/*
* This array has to be in the same order as enum ExprEvalOp.
@@ -309,6 +310,10 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
&&CASE_EEOP_FUNCEXPR_STRICT,
&&CASE_EEOP_FUNCEXPR_FUSAGE,
&&CASE_EEOP_FUNCEXPR_STRICT_FUSAGE,
+ &&CASE_EEOP_CACHED_FUNCEXPR,
+ &&CASE_EEOP_CACHED_FUNCEXPR_STRICT,
+ &&CASE_EEOP_CACHED_FUNCEXPR_FUSAGE,
+ &&CASE_EEOP_CACHED_FUNCEXPR_STRICT_FUSAGE,
&&CASE_EEOP_BOOL_AND_STEP_FIRST,
&&CASE_EEOP_BOOL_AND_STEP,
&&CASE_EEOP_BOOL_AND_STEP_LAST,
@@ -721,6 +726,192 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
EEO_NEXT();
}
+ EEO_CASE(EEOP_CACHED_FUNCEXPR)
+ {
+ FunctionCallInfo fcinfo = op->d.cachedexpr.subexprdata->fcinfo_data;
+
+ if (op->d.cachedexpr.isExecuted)
+ {
+ /* use saved result */
+ fcinfo->isnull = op->d.cachedexpr.resnull;
+ *op->resvalue = op->d.cachedexpr.resvalue;
+ *op->resnull = fcinfo->isnull;
+
+ goto cached_funcexpr;
+ }
+
+ /*
+ * If function is cacheable then switch per-query memory context.
+ * It is necessary to save result between all tuples.
+ */
+ oldContext = MemoryContextSwitchTo(econtext->ecxt_per_query_memory);
+
+ /* execute function as usual */
+ fcinfo->isnull = false;
+ *op->resvalue = (op->d.cachedexpr.subexprdata->fn_addr) (fcinfo);
+ *op->resnull = fcinfo->isnull;
+
+ /* save result and switch memory context back */
+ op->d.cachedexpr.resnull = fcinfo->isnull;
+ op->d.cachedexpr.resvalue = *op->resvalue;
+ op->d.cachedexpr.isExecuted = true;
+ MemoryContextSwitchTo(oldContext);
+
+ cached_funcexpr:
+ EEO_NEXT();
+ }
+
+ EEO_CASE(EEOP_CACHED_FUNCEXPR_STRICT)
+ {
+ FunctionCallInfo fcinfo = op->d.cachedexpr.subexprdata->fcinfo_data;
+ bool *argnull = fcinfo->argnull;
+ int argno;
+
+ if (op->d.cachedexpr.isExecuted)
+ {
+ /* use saved result */
+ fcinfo->isnull = op->d.cachedexpr.resnull;
+ if (!fcinfo->isnull)
+ *op->resvalue = op->d.cachedexpr.resvalue;
+ *op->resnull = fcinfo->isnull;
+
+ goto cached_funcexpr_strict;
+ }
+
+ /* strict function, so check for NULL args */
+ for (argno = 0; argno < op->d.func.nargs; argno++)
+ {
+ if (argnull[argno])
+ {
+ *op->resnull = true;
+
+ op->d.cachedexpr.resnull = *op->resnull;
+ op->d.cachedexpr.isExecuted = true;
+
+ goto cached_strictfail;
+ }
+ }
+
+ /*
+ * If function is cacheable then switch per-query memory context.
+ * It is necessary to save result between all tuples.
+ */
+ oldContext = MemoryContextSwitchTo(econtext->ecxt_per_query_memory);
+
+ /* execute function as usual */
+ fcinfo->isnull = false;
+ *op->resvalue = (op->d.cachedexpr.subexprdata->fn_addr) (fcinfo);
+ *op->resnull = fcinfo->isnull;
+
+ /* save result and switch memory context back */
+ op->d.cachedexpr.resnull = fcinfo->isnull;
+ op->d.cachedexpr.resvalue = *op->resvalue;
+ op->d.cachedexpr.isExecuted = true;
+ MemoryContextSwitchTo(oldContext);
+
+ cached_funcexpr_strict:
+ cached_strictfail:
+ EEO_NEXT();
+ }
+
+ EEO_CASE(EEOP_CACHED_FUNCEXPR_FUSAGE)
+ {
+ FunctionCallInfo fcinfo = op->d.cachedexpr.subexprdata->fcinfo_data;
+ PgStat_FunctionCallUsage fcusage;
+
+ if (op->d.cachedexpr.isExecuted)
+ {
+ /* use saved result */
+ fcinfo->isnull = op->d.cachedexpr.resnull;
+ *op->resvalue = op->d.cachedexpr.resvalue;
+ *op->resnull = fcinfo->isnull;
+
+ goto cached_funcexpr_fusage;
+ }
+
+ pgstat_init_function_usage(fcinfo, &fcusage);
+
+ /*
+ * If function is cacheable then switch per-query memory context.
+ * It is necessary to save result between all tuples.
+ */
+ oldContext = MemoryContextSwitchTo(econtext->ecxt_per_query_memory);
+
+ /* execute function as usual */
+ fcinfo->isnull = false;
+ *op->resvalue = (op->d.cachedexpr.subexprdata->fn_addr) (fcinfo);
+ *op->resnull = fcinfo->isnull;
+
+ /* save result and switch memory context back */
+ op->d.cachedexpr.resnull = fcinfo->isnull;
+ op->d.cachedexpr.resvalue = *op->resvalue;
+ op->d.cachedexpr.isExecuted = true;
+ MemoryContextSwitchTo(oldContext);
+
+ pgstat_end_function_usage(&fcusage, true);
+
+ cached_funcexpr_fusage:
+ EEO_NEXT();
+ }
+
+ EEO_CASE(EEOP_CACHED_FUNCEXPR_STRICT_FUSAGE)
+ {
+ FunctionCallInfo fcinfo = op->d.cachedexpr.subexprdata->fcinfo_data;
+ PgStat_FunctionCallUsage fcusage;
+ bool *argnull = fcinfo->argnull;
+ int argno;
+
+ if (op->d.cachedexpr.isExecuted)
+ {
+ /* use saved result */
+ fcinfo->isnull = op->d.cachedexpr.resnull;
+ if (!fcinfo->isnull)
+ *op->resvalue = op->d.cachedexpr.resvalue;
+ *op->resnull = fcinfo->isnull;
+
+ goto cached_funcexpr_strict_fusage;
+ }
+
+ /* strict function, so check for NULL args */
+ for (argno = 0; argno < op->d.func.nargs; argno++)
+ {
+ if (argnull[argno])
+ {
+ *op->resnull = true;
+
+ op->d.cachedexpr.resnull = *op->resnull;
+ op->d.cachedexpr.isExecuted = true;
+
+ goto cached_strictfail_fusage;
+ }
+ }
+
+ pgstat_init_function_usage(fcinfo, &fcusage);
+
+ /*
+ * If function is cacheable then switch per-query memory context.
+ * It is necessary to save result between all tuples.
+ */
+ oldContext = MemoryContextSwitchTo(econtext->ecxt_per_query_memory);
+
+ /* execute function as usual */
+ fcinfo->isnull = false;
+ *op->resvalue = (op->d.cachedexpr.subexprdata->fn_addr) (fcinfo);
+ *op->resnull = fcinfo->isnull;
+
+ /* save result and switch memory context back */
+ op->d.cachedexpr.resnull = fcinfo->isnull;
+ op->d.cachedexpr.resvalue = *op->resvalue;
+ op->d.cachedexpr.isExecuted = true;
+ MemoryContextSwitchTo(oldContext);
+
+ pgstat_end_function_usage(&fcusage, true);
+
+ cached_funcexpr_strict_fusage:
+ cached_strictfail_fusage:
+ 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 4dd8cbb..985e1b4 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..adf8dac 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -2758,6 +2758,49 @@ 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;
+ }
+
+ 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 cbde1ff..cc33655 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -7690,6 +7690,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..c0c4207 100644
--- a/src/include/executor/execExpr.h
+++ b/src/include/executor/execExpr.h
@@ -85,6 +85,12 @@ typedef enum ExprEvalOp
EEOP_FUNCEXPR_FUSAGE,
EEOP_FUNCEXPR_STRICT_FUSAGE,
+ /* evaluate CachedExpr */
+ EEOP_CACHED_FUNCEXPR,
+ EEOP_CACHED_FUNCEXPR_STRICT,
+ EEOP_CACHED_FUNCEXPR_FUSAGE,
+ EEOP_CACHED_FUNCEXPR_STRICT_FUSAGE,
+
/*
* Evaluate boolean AND expression, one step per subexpression. FIRST/LAST
* subexpressions are special-cased for performance. Since AND always has
@@ -217,6 +223,20 @@ typedef enum ExprEvalOp
} ExprEvalOp;
+/*
+ * Inline data of ExprEvalStep for operations
+ * EEOP_FUNCEXPR_* / NULLIF / DISTINCT / CACHED_FUNCEXPR_*
+ */
+typedef struct FuncData
+{
+ FmgrInfo *finfo; /* function's lookup data */
+ FunctionCallInfo fcinfo_data; /* arguments etc */
+ /* faster to access without additional indirection: */
+ PGFunction fn_addr; /* actual call address */
+ int nargs; /* number of arguments */
+} FuncData;
+
+
typedef struct ExprEvalStep
{
/*
@@ -289,14 +309,18 @@ typedef struct ExprEvalStep
} constval;
/* for EEOP_FUNCEXPR_* / NULLIF / DISTINCT */
+ FuncData func;
+
+ /* for EEOP_CACHED_FUNCEXPR_* */
struct
- {
- FmgrInfo *finfo; /* function's lookup data */
- FunctionCallInfo fcinfo_data; /* arguments etc */
- /* faster to access without additional indirection: */
- PGFunction fn_addr; /* actual call address */
- int nargs; /* number of arguments */
- } func;
+ {
+ /* cached ExprEvalOp data */
+ FuncData *subexprdata;
+
+ bool isExecuted;
+ bool resnull;
+ Datum resvalue;
+ } cachedexpr;
/* for EEOP_BOOL_*_STEP */
struct
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..0b893d0 100644
--- a/src/include/optimizer/tlist.h
+++ b/src/include/optimizer/tlist.h
@@ -65,8 +65,11 @@ 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..cfef1d2
--- /dev/null
+++ b/src/test/regress/expected/precalculate_stable_functions.out
@@ -0,0 +1,827 @@
+--
+-- PRECALCULATE STABLE FUNCTIONS
+--
+-- Create tables for testing
+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 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.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 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);
+-- 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)
+
+SELECT 1 ===== 2 FROM generate_series(1, 4) x;
+NOTICE: equal integers immutable
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+-- Nested and strict 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 (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)
+
+-- Mixed functions and operators testing
+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)
+
+-- 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..c86c382
--- /dev/null
+++ b/src/test/regress/sql/precalculate_stable_functions.sql
@@ -0,0 +1,282 @@
+--
+-- PRECALCULATE STABLE FUNCTIONS
+--
+
+-- Create tables for testing
+
+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 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.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 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);
+
+-- 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;
+SELECT 1 ===== 2 FROM generate_series(1, 4) x;
+
+-- Nested and strict operators testing
+
+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;
+
+-- Mixed functions and operators testing
+
+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;
+
+-- 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
