diff --git a/src/backend/executor/execQual.c b/src/backend/executor/execQual.c index 887e5ce8..3498db9 100644 --- a/src/backend/executor/execQual.c +++ b/src/backend/executor/execQual.c @@ -51,6 +51,7 @@ #include "pgstat.h" #include "utils/acl.h" #include "utils/builtins.h" +#include "utils/datum.h" #include "utils/lsyscache.h" #include "utils/memutils.h" #include "utils/typcache.h" @@ -157,6 +158,12 @@ static Datum ExecEvalCoerceToDomain(CoerceToDomainState *cstate, static Datum ExecEvalCoerceToDomainValue(ExprState *exprstate, ExprContext *econtext, bool *isNull, ExprDoneCond *isDone); +static Datum ExecEvalCacheExpr(CacheExprState *cstate, + ExprContext *econtext, + bool *isNull,ExprDoneCond *isDone); +static Datum ExecEvalCacheExprResult(CacheExprState *cstate, + ExprContext *econtext, + bool *isNull,ExprDoneCond *isDone); static Datum ExecEvalFieldSelect(FieldSelectState *fstate, ExprContext *econtext, bool *isNull, ExprDoneCond *isDone); @@ -3754,6 +3761,62 @@ ExecEvalBooleanTest(GenericExprState *bstate, } } +/* ---------------------------------------------------------------- + * ExecEvalCacheExpr + * + * Evaluates a cachable expression for the first time and updates + * xprstate.evalfunc to return cached result next time + * ---------------------------------------------------------------- + */ +static Datum +ExecEvalCacheExpr(CacheExprState *cstate, ExprContext *econtext, + bool *isNull, ExprDoneCond *isDone) +{ + MemoryContext oldcontext; + Datum result; + bool resultTypByVal; + int16 resultTypLen; + Oid resultType; + + result = ExecEvalExpr(cstate->subexpr, econtext, isNull, isDone); + + if (!cstate->cacheEnabled) + return result; /* Cache disabled, pass thru the result */ + + /* Set-returning expressions can't be cached */ + Assert(isDone == NULL || *isDone == ExprSingleResult); + + resultType = exprType((Node *) ((CacheExpr *) cstate->xprstate.expr)->arg); + get_typlenbyval(resultType, &resultTypLen, &resultTypByVal); + + /* This cached datum has to persist for the whole query */ + oldcontext = MemoryContextSwitchTo(econtext->ecxt_per_query_memory); + cstate->result = datumCopy(result, resultTypByVal, resultTypLen); + cstate->isNull = *isNull; + MemoryContextSwitchTo(oldcontext); + + /* Subsequent calls will return the cached result */ + cstate->xprstate.evalfunc = (ExprStateEvalFunc) ExecEvalCacheExprResult; + + return cstate->result; +} + +/* ---------------------------------------------------------------- + * ExecEvalCacheExprResult + * + * Return the already-cached result, computed in ExecEvalCacheExpr + * ---------------------------------------------------------------- + */ +static Datum +ExecEvalCacheExprResult(CacheExprState *cstate, ExprContext *econtext, + bool *isNull, ExprDoneCond *isDone) +{ + if (isDone) + *isDone = ExprSingleResult; + *isNull = cstate->isNull; + return cstate->result; +} + /* * ExecEvalCoerceToDomain * @@ -4858,6 +4921,26 @@ ExecInitExpr(Expr *node, PlanState *parent) state = (ExprState *) gstate; } break; + case T_CacheExpr: + { + CacheExpr *cache = (CacheExpr *) node; + CacheExprState *cstate = makeNode(CacheExprState); + + cstate->xprstate.evalfunc = (ExprStateEvalFunc) ExecEvalCacheExpr; + cstate->subexpr = ExecInitExpr(cache->arg, parent); + /* + * We only enable cache when there's an associated PlanState. + * For other callers we can't guarantee the + * lifetime/invalidation of the cache. + */ + if (parent) + cstate->cacheEnabled = true; + else + cstate->cacheEnabled = false; + + state = (ExprState *) cstate; + } + break; case T_CoerceToDomain: { CoerceToDomain *ctest = (CoerceToDomain *) node; diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index c70a5bd..6f809f2 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -1684,6 +1684,19 @@ _copyBooleanTest(BooleanTest *from) } /* + * _copyCacheExpr + */ +static CacheExpr * +_copyCacheExpr(CacheExpr *from) +{ + CacheExpr *newnode = makeNode(CacheExpr); + + COPY_NODE_FIELD(arg); + + return newnode; +} + +/* * _copyCoerceToDomain */ static CoerceToDomain * @@ -4004,6 +4017,9 @@ copyObject(void *from) case T_BooleanTest: retval = _copyBooleanTest(from); break; + case T_CacheExpr: + retval = _copyCacheExpr(from); + break; case T_CoerceToDomain: retval = _copyCoerceToDomain(from); break; diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index f490a7a..af7ebe7 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -678,6 +678,14 @@ _equalBooleanTest(BooleanTest *a, BooleanTest *b) } static bool +_equalCacheExpr(CacheExpr *a, CacheExpr *b) +{ + COMPARE_NODE_FIELD(arg); + + return true; +} + +static bool _equalCoerceToDomain(CoerceToDomain *a, CoerceToDomain *b) { COMPARE_NODE_FIELD(arg); @@ -2560,6 +2568,9 @@ equal(void *a, void *b) case T_BooleanTest: retval = _equalBooleanTest(a, b); break; + case T_CacheExpr: + retval = _equalCacheExpr(a, b); + break; case T_CoerceToDomain: retval = _equalCoerceToDomain(a, b); break; diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c index 683e751..6d7eaca 100644 --- a/src/backend/nodes/makefuncs.c +++ b/src/backend/nodes/makefuncs.c @@ -471,6 +471,21 @@ makeFuncExpr(Oid funcid, Oid rettype, List *args, } /* + * makeCacheExpr - + * build an expression node for a cachable expression. + */ +CacheExpr * +makeCacheExpr(Expr *arg) +{ + CacheExpr *cacheexpr; + + cacheexpr = makeNode(CacheExpr); + cacheexpr->arg = arg; + + return cacheexpr; +} + +/* * makeDefElem - * build a DefElem node * diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index 0e57f6c..0d38627 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -212,6 +212,9 @@ exprType(Node *expr) case T_BooleanTest: type = BOOLOID; break; + case T_CacheExpr: + type = exprType((Node *) ((CacheExpr *) expr)->arg); + break; case T_CoerceToDomain: type = ((CoerceToDomain *) expr)->resulttype; break; @@ -792,6 +795,9 @@ exprCollation(Node *expr) case T_BooleanTest: coll = InvalidOid; /* result is always boolean */ break; + case T_CacheExpr: + coll = exprCollation((Node *) ((CacheExpr *) expr)->arg); + break; case T_CoerceToDomain: coll = ((CoerceToDomain *) expr)->resultcollid; break; @@ -1263,6 +1269,10 @@ exprLocation(Node *expr) /* just use argument's location */ loc = exprLocation((Node *) ((BooleanTest *) expr)->arg); break; + case T_CacheExpr: + /* original expression location */ + loc = exprLocation((Node *) ((CacheExpr *) expr)->arg); + break; case T_CoerceToDomain: { CoerceToDomain *cexpr = (CoerceToDomain *) expr; @@ -1722,6 +1732,8 @@ expression_tree_walker(Node *node, return walker(((NullTest *) node)->arg, context); case T_BooleanTest: return walker(((BooleanTest *) node)->arg, context); + case T_CacheExpr: + return walker(((CacheExpr *) node)->arg, context); case T_CoerceToDomain: return walker(((CoerceToDomain *) node)->arg, context); case T_TargetEntry: @@ -2374,6 +2386,16 @@ expression_tree_mutator(Node *node, return (Node *) newnode; } break; + case T_CacheExpr: + { + CacheExpr *cache = (CacheExpr *) node; + CacheExpr *newnode; + + FLATCOPY(newnode, cache, CacheExpr); + MUTATE(newnode->arg, cache->arg, Expr *); + return (Node *) newnode; + } + break; case T_CoerceToDomain: { CoerceToDomain *ctest = (CoerceToDomain *) node; @@ -2781,6 +2803,8 @@ bool return walker(((NullTest *) node)->arg, context); case T_BooleanTest: return walker(((BooleanTest *) node)->arg, context); + case T_CacheExpr: + return walker(((CacheExpr *) node)->arg, context); case T_JoinExpr: { JoinExpr *join = (JoinExpr *) node; diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index 31af47f..58dcc26 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -1367,6 +1367,14 @@ _outBooleanTest(StringInfo str, BooleanTest *node) } static void +_outCacheExpr(StringInfo str, CacheExpr *node) +{ + WRITE_NODE_TYPE("CACHEEXPR"); + + WRITE_NODE_FIELD(arg); +} + +static void _outCoerceToDomain(StringInfo str, CoerceToDomain *node) { WRITE_NODE_TYPE("COERCETODOMAIN"); @@ -2918,6 +2926,9 @@ _outNode(StringInfo str, void *obj) case T_BooleanTest: _outBooleanTest(str, obj); break; + case T_CacheExpr: + _outCacheExpr(str, obj); + break; case T_CoerceToDomain: _outCoerceToDomain(str, obj); break; diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index ad02950..a559af0 100644 --- a/src/backend/optimizer/util/clauses.c +++ b/src/backend/optimizer/util/clauses.c @@ -97,43 +97,50 @@ static Relids find_nonnullable_rels_walker(Node *node, bool top_level); static List *find_nonnullable_vars_walker(Node *node, bool top_level); static bool is_strict_saop(ScalarArrayOpExpr *expr, bool falseOK); static bool set_coercionform_dontcare_walker(Node *node, void *context); -static Node *eval_const_expressions_mutator(Node *node, - eval_const_expressions_context *context); +static Node *caching_const_expressions_mutator(Node *node, + eval_const_expressions_context *context); +static Node *const_expressions_mutator(Node *node, + eval_const_expressions_context *context, + bool *cachable); static List *simplify_or_arguments(List *args, eval_const_expressions_context *context, - bool *haveNull, bool *forceTrue); + bool *haveNull, bool *forceTrue, bool *cachable); static List *simplify_and_arguments(List *args, eval_const_expressions_context *context, - bool *haveNull, bool *forceFalse); + bool *haveNull, bool *forceFalse, bool *cachable); static Node *simplify_boolean_equality(Oid opno, List *args); static Expr *simplify_function(Expr *oldexpr, Oid funcid, Oid result_type, int32 result_typmod, Oid result_collid, Oid input_collid, List **args, - bool has_named_args, bool allow_inline, - eval_const_expressions_context *context); + eval_const_expressions_context *context, + bool *cachable); +static List *simplify_copy_function_arguments(List *old_args, Oid result_type, + HeapTuple func_tuple); static List *reorder_function_arguments(List *args, Oid result_type, - HeapTuple func_tuple, - eval_const_expressions_context *context); + HeapTuple func_tuple); static List *add_function_defaults(List *args, Oid result_type, - HeapTuple func_tuple, - eval_const_expressions_context *context); + HeapTuple func_tuple); static List *fetch_function_defaults(HeapTuple func_tuple); static void recheck_cast_function_args(List *args, Oid result_type, HeapTuple func_tuple); static Expr *evaluate_function(Oid funcid, Oid result_type, int32 result_typmod, Oid result_collid, Oid input_collid, List *args, HeapTuple func_tuple, - eval_const_expressions_context *context); + eval_const_expressions_context *context, + bool *cachable); static Expr *inline_function(Oid funcid, Oid result_type, Oid result_collid, Oid input_collid, List *args, HeapTuple func_tuple, - eval_const_expressions_context *context); + eval_const_expressions_context *context, + bool *cachable); static Node *substitute_actual_parameters(Node *expr, int nargs, List *args, int *usecounts); static Node *substitute_actual_parameters_mutator(Node *node, substitute_actual_parameters_context *context); static void sql_inline_error_callback(void *arg); +static inline bool is_cache_useful(Expr *expr); +static Expr *insert_cache(Expr *expr); static Expr *evaluate_expr(Expr *expr, Oid result_type, int32 result_typmod, Oid result_collation); static Query *substitute_actual_srf_parameters(Query *expr, @@ -2028,6 +2035,11 @@ rowtype_field_matches(Oid rowtypeid, int fieldnum, * assumption in the presence of user-defined functions; do we need a * pg_proc flag that prevents discarding the execution of a function?) * + * We also insert CacheExpr nodes above expressions that cannot be + * evaluated at planning time, but are constant at execution time. + * This includes expressions that contain stable function calls and + * Param references. + * * We do understand that certain functions may deliver non-constant * results even with constant inputs, "nextval()" being the classic * example. Functions that are not marked "immutable" in pg_proc @@ -2066,7 +2078,8 @@ eval_const_expressions(PlannerInfo *root, Node *node) context.active_fns = NIL; /* nothing being recursively simplified */ context.case_val = NULL; /* no CASE being examined */ context.estimate = false; /* safe transformations only */ - return eval_const_expressions_mutator(node, &context); + + return caching_const_expressions_mutator(node, &context); } /*-------------------- @@ -2084,12 +2097,14 @@ eval_const_expressions(PlannerInfo *root, Node *node) * value of the Param. * 2. Fold stable, as well as immutable, functions to constants. * 3. Reduce PlaceHolderVar nodes to their contained expressions. + * 4. Strip CacheExpr nodes, as planner only wants to evaluate once. *-------------------- */ Node * estimate_expression_value(PlannerInfo *root, Node *node) { eval_const_expressions_context context; + bool isCachable = true; context.boundParams = root->glob->boundParams; /* bound Params */ /* we do not need to mark the plan as depending on inlined functions */ @@ -2097,13 +2112,43 @@ estimate_expression_value(PlannerInfo *root, Node *node) context.active_fns = NIL; /* nothing being recursively simplified */ context.case_val = NULL; /* no CASE being examined */ context.estimate = true; /* unsafe transformations OK */ - return eval_const_expressions_mutator(node, &context); + + return const_expressions_mutator(node, &context, &isCachable); } +/* + * Calls const_expressions_mutator on the expression tree and automatically + * adds a CacheExpr node if the expression is cachable. + */ static Node * -eval_const_expressions_mutator(Node *node, - eval_const_expressions_context *context) +caching_const_expressions_mutator(Node *node, + eval_const_expressions_context *context) { + bool isCachable = true; + + if (node == NULL) + return NULL; + + node = const_expressions_mutator(node, context, &isCachable); + if (isCachable) + node = (Node *) insert_cache((Expr *) node); + + return node; +} + +/* + * Returns a mutated node tree and determines its cachability. + * + * The caller must make sure that cachable points to a boolean value that's + * initialized to TRUE. + */ +static Node * +const_expressions_mutator(Node *node, + eval_const_expressions_context *context, + bool *cachable) +{ + Assert(*cachable == true); + if (node == NULL) return NULL; switch (nodeTag(node)) @@ -2112,6 +2157,14 @@ eval_const_expressions_mutator(Node *node, { Param *param = (Param *) node; + /* + * Only externally-supplied parameters are stable. Other + * params are used for passing changing values within the + * executor + */ + if (param->paramkind != PARAM_EXTERN) + *cachable = false; + /* Look to see if we've been given a value for this Param */ if (param->paramkind == PARAM_EXTERN && context->boundParams != NULL && @@ -2163,27 +2216,9 @@ eval_const_expressions_mutator(Node *node, case T_FuncExpr: { FuncExpr *expr = (FuncExpr *) node; - List *args; - bool has_named_args; + List *args = expr->args; Expr *simple; FuncExpr *newexpr; - ListCell *lc; - - /* - * Reduce constants in the FuncExpr's arguments, and check to - * see if there are any named args. - */ - args = NIL; - has_named_args = false; - foreach(lc, expr->args) - { - Node *arg = (Node *) lfirst(lc); - - arg = eval_const_expressions_mutator(arg, context); - if (IsA(arg, NamedArgExpr)) - has_named_args = true; - args = lappend(args, arg); - } /* * Code for op/func reduction is pretty bulky, so split it out @@ -2199,9 +2234,9 @@ eval_const_expressions_mutator(Node *node, expr->funccollid, expr->inputcollid, &args, - has_named_args, true, - context); + context, + cachable); if (simple) /* successfully simplified it */ return (Node *) simple; @@ -2225,21 +2260,11 @@ eval_const_expressions_mutator(Node *node, case T_OpExpr: { OpExpr *expr = (OpExpr *) node; - List *args; + List *args = expr->args; Expr *simple; OpExpr *newexpr; /* - * Reduce constants in the OpExpr's arguments. We know args - * is either NIL or a List node, so we can call - * expression_tree_mutator directly rather than recursing to - * self. - */ - args = (List *) expression_tree_mutator((Node *) expr->args, - eval_const_expressions_mutator, - (void *) context); - - /* * Need to get OID of underlying function. Okay to scribble * on input to this extent. */ @@ -2255,7 +2280,8 @@ eval_const_expressions_mutator(Node *node, expr->opcollid, expr->inputcollid, &args, - false, true, context); + true, context, + cachable); if (simple) /* successfully simplified it */ return (Node *) simple; @@ -2292,35 +2318,49 @@ eval_const_expressions_mutator(Node *node, case T_DistinctExpr: { DistinctExpr *expr = (DistinctExpr *) node; - List *args; - ListCell *arg; + List *args = NIL; + ListCell *lc; + Node *arg; bool has_null_input = false; bool all_null_input = true; bool has_nonconst_input = false; Expr *simple; DistinctExpr *newexpr; + bool leftCachable = true; + bool rightCachable = true; /* - * Reduce constants in the DistinctExpr's arguments. We know - * args is either NIL or a List node, so we can call - * expression_tree_mutator directly rather than recursing to - * self. + * Reduce constants in the DistinctExpr's arguments + * + * Note that simplify_function() might call the mutator + * function on arguments for a second time. However, this is + * harmless because it's only called when arguments are + * constant. */ - args = (List *) expression_tree_mutator((Node *) expr->args, - eval_const_expressions_mutator, - (void *) context); + Assert(list_length(expr->args) == 2); + + arg = const_expressions_mutator(linitial(expr->args), + context, + &leftCachable); + args = lappend(args, arg); + + arg = const_expressions_mutator(lsecond(expr->args), + context, + &rightCachable); + args = lappend(args, arg); /* * We must do our own check for NULLs because DistinctExpr has * different results for NULL input than the underlying * operator does. */ - foreach(arg, args) + foreach(lc, args) { - if (IsA(lfirst(arg), Const)) + arg = lfirst(lc); + if (IsA(arg, Const)) { - has_null_input |= ((Const *) lfirst(arg))->constisnull; - all_null_input &= ((Const *) lfirst(arg))->constisnull; + has_null_input |= ((Const *) arg)->constisnull; + all_null_input &= ((Const *) arg)->constisnull; } else has_nonconst_input = true; @@ -2357,7 +2397,9 @@ eval_const_expressions_mutator(Node *node, expr->opcollid, expr->inputcollid, &args, - false, false, context); + false, context, + cachable); + if (simple) /* successfully simplified it */ { /* @@ -2372,6 +2414,32 @@ eval_const_expressions_mutator(Node *node, return (Node *) csimple; } } + else if (!leftCachable || !rightCachable) + { + *cachable = false; + } + else + { + /* + * This expression is only cachable if the equality + * operator is not volatile. + */ + HeapTuple func_tuple = SearchSysCache1(PROCOID, ObjectIdGetDatum(expr->opfuncid)); + Form_pg_proc funcform = (Form_pg_proc) GETSTRUCT(func_tuple); + + if (funcform->provolatile == PROVOLATILE_VOLATILE) + *cachable = false; + + ReleaseSysCache(func_tuple); + } + + if (!(*cachable)) + { + if (leftCachable) + linitial(args) = insert_cache((Expr *) linitial(args)); + if (rightCachable) + lsecond(args) = insert_cache((Expr *) lsecond(args)); + } /* * The expression cannot be simplified any further, so build @@ -2404,7 +2472,8 @@ eval_const_expressions_mutator(Node *node, newargs = simplify_or_arguments(expr->args, context, &haveNull, - &forceTrue); + &forceTrue, + cachable); if (forceTrue) return makeBoolConst(true, false); if (haveNull) @@ -2432,7 +2501,8 @@ eval_const_expressions_mutator(Node *node, newargs = simplify_and_arguments(expr->args, context, &haveNull, - &forceFalse); + &forceFalse, + cachable); if (forceFalse) return makeBoolConst(false, false); if (haveNull) @@ -2456,8 +2526,9 @@ eval_const_expressions_mutator(Node *node, Node *arg; Assert(list_length(expr->args) == 1); - arg = eval_const_expressions_mutator(linitial(expr->args), - context); + arg = const_expressions_mutator(linitial(expr->args), + context, + cachable); /* * Use negate_clause() to see if we can simplify @@ -2481,6 +2552,7 @@ eval_const_expressions_mutator(Node *node, * XXX should we ereport() here instead? Probably this routine * should never be invoked after SubPlan creation. */ + *cachable = false; return node; case T_RelabelType: { @@ -2493,8 +2565,9 @@ eval_const_expressions_mutator(Node *node, RelabelType *relabel = (RelabelType *) node; Node *arg; - arg = eval_const_expressions_mutator((Node *) relabel->arg, - context); + arg = const_expressions_mutator((Node *) relabel->arg, + context, + cachable); /* * If we find stacked RelabelTypes (eg, from foo :: int :: @@ -2528,7 +2601,6 @@ eval_const_expressions_mutator(Node *node, case T_CoerceViaIO: { CoerceViaIO *expr = (CoerceViaIO *) node; - Expr *arg; List *args; Oid outfunc; bool outtypisvarlena; @@ -2540,9 +2612,7 @@ eval_const_expressions_mutator(Node *node, /* * Reduce constants in the CoerceViaIO's argument. */ - arg = (Expr *) eval_const_expressions_mutator((Node *) expr->arg, - context); - args = list_make1(arg); + args = list_make1(expr->arg); /* * CoerceViaIO represents calling the source type's output @@ -2553,7 +2623,7 @@ eval_const_expressions_mutator(Node *node, * Note that the coercion functions are assumed not to care * about input collation, so we just pass InvalidOid for that. */ - getTypeOutputInfo(exprType((Node *) arg), + getTypeOutputInfo(exprType((Node *) expr->arg), &outfunc, &outtypisvarlena); getTypeInputInfo(expr->resulttype, &infunc, &intypioparam); @@ -2564,7 +2634,8 @@ eval_const_expressions_mutator(Node *node, InvalidOid, InvalidOid, &args, - false, true, context); + true, context, + cachable); if (simple) /* successfully simplified output fn */ { /* @@ -2594,7 +2665,8 @@ eval_const_expressions_mutator(Node *node, expr->resultcollid, InvalidOid, &args, - false, true, context); + true, context, + cachable); if (simple) /* successfully simplified input fn */ return (Node *) simple; } @@ -2605,7 +2677,7 @@ eval_const_expressions_mutator(Node *node, * possibly-simplified argument. */ newexpr = makeNode(CoerceViaIO); - newexpr->arg = arg; + newexpr->arg = (Expr *) linitial(args); newexpr->resulttype = expr->resulttype; newexpr->resultcollid = expr->resultcollid; newexpr->coerceformat = expr->coerceformat; @@ -2622,8 +2694,9 @@ eval_const_expressions_mutator(Node *node, * Reduce constants in the ArrayCoerceExpr's argument, then * build a new ArrayCoerceExpr. */ - arg = (Expr *) eval_const_expressions_mutator((Node *) expr->arg, - context); + arg = (Expr *) const_expressions_mutator((Node *) expr->arg, + context, + cachable); newexpr = makeNode(ArrayCoerceExpr); newexpr->arg = arg; @@ -2646,6 +2719,16 @@ eval_const_expressions_mutator(Node *node, newexpr->resulttype, newexpr->resulttypmod, newexpr->resultcollid); + /* + * If the argument is cachable, but conversion isn't, insert a + * CacheExpr above the argument + */ + if (arg && *cachable && (OidIsValid(newexpr->elemfuncid) && + func_volatile(newexpr->elemfuncid) == PROVOLATILE_VOLATILE)) + { + *cachable = false; + newexpr->arg = insert_cache(arg); + } /* Else we must return the partially-simplified node */ return (Node *) newexpr; @@ -2663,8 +2746,9 @@ eval_const_expressions_mutator(Node *node, CollateExpr *collate = (CollateExpr *) node; Node *arg; - arg = eval_const_expressions_mutator((Node *) collate->arg, - context); + arg = const_expressions_mutator((Node *) collate->arg, + context, + cachable); if (arg && IsA(arg, Const)) { @@ -2733,13 +2817,14 @@ eval_const_expressions_mutator(Node *node, Node *save_case_val; Node *newarg; List *newargs; + List *cachable_args = NIL; bool const_true_cond; Node *defresult = NULL; ListCell *arg; /* Simplify the test expression, if any */ - newarg = eval_const_expressions_mutator((Node *) caseexpr->arg, - context); + newarg = caching_const_expressions_mutator((Node *) caseexpr->arg, + context); /* Set up for contained CaseTestExpr nodes */ save_case_val = context->case_val; @@ -2759,12 +2844,15 @@ eval_const_expressions_mutator(Node *node, CaseWhen *oldcasewhen = (CaseWhen *) lfirst(arg); Node *casecond; Node *caseresult; + bool condCachable = true; + bool resultCachable = true; Assert(IsA(oldcasewhen, CaseWhen)); /* Simplify this alternative's test condition */ - casecond = eval_const_expressions_mutator((Node *) oldcasewhen->expr, - context); + casecond = const_expressions_mutator((Node *) oldcasewhen->expr, + context, + &condCachable); /* * If the test condition is constant FALSE (or NULL), then @@ -2783,8 +2871,9 @@ eval_const_expressions_mutator(Node *node, } /* Simplify this alternative's result value */ - caseresult = eval_const_expressions_mutator((Node *) oldcasewhen->result, - context); + caseresult = const_expressions_mutator((Node *) oldcasewhen->result, + context, + &resultCachable); /* If non-constant test condition, emit a new WHEN node */ if (!const_true_cond) @@ -2795,6 +2884,23 @@ eval_const_expressions_mutator(Node *node, newcasewhen->result = (Expr *) caseresult; newcasewhen->location = oldcasewhen->location; newargs = lappend(newargs, newcasewhen); + + if (condCachable) + { + if (is_cache_useful((Expr *) casecond)) + cachable_args = lappend(cachable_args, &newcasewhen->expr); + } + else + *cachable = false; + + if (resultCachable) + { + if (is_cache_useful((Expr *) caseresult)) + cachable_args = lappend(cachable_args, &newcasewhen->result); + } + else + *cachable = false; + continue; } @@ -2809,8 +2915,21 @@ eval_const_expressions_mutator(Node *node, /* Simplify the default result, unless we replaced it above */ if (!const_true_cond) - defresult = eval_const_expressions_mutator((Node *) caseexpr->defresult, - context); + { + bool isCachable = true; + + defresult = const_expressions_mutator((Node *) caseexpr->defresult, + context, + &isCachable); + + if (isCachable) + { + if (is_cache_useful((Expr *) defresult)) + cachable_args = lappend(cachable_args, &defresult); + } + else + *cachable = false; + } context->case_val = save_case_val; @@ -2820,6 +2939,19 @@ eval_const_expressions_mutator(Node *node, */ if (newargs == NIL) return defresult; + + if (!(*cachable)) + { + ListCell *lc; + + foreach(lc, cachable_args) + { + Expr **arg = (Expr **) lfirst(lc); + + *arg = (Expr *) makeCacheExpr(*arg); + } + } + /* Otherwise we need a new CASE node */ newcase = makeNode(CaseExpr); newcase->casetype = caseexpr->casetype; @@ -2840,7 +2972,10 @@ eval_const_expressions_mutator(Node *node, if (context->case_val) return copyObject(context->case_val); else + { + *cachable = false; return copyObject(node); + } } case T_ArrayExpr: { @@ -2850,13 +2985,15 @@ eval_const_expressions_mutator(Node *node, List *newelems; ListCell *element; + *cachable = false; /* XXX cachable! */ + newelems = NIL; foreach(element, arrayexpr->elements) { Node *e; - e = eval_const_expressions_mutator((Node *) lfirst(element), - context); + e = caching_const_expressions_mutator((Node *) lfirst(element), + context); if (!IsA(e, Const)) all_const = false; newelems = lappend(newelems, e); @@ -2883,15 +3020,18 @@ eval_const_expressions_mutator(Node *node, CoalesceExpr *coalesceexpr = (CoalesceExpr *) node; CoalesceExpr *newcoalesce; List *newargs; - ListCell *arg; + List *cachable_args = NIL; + ListCell *lc; newargs = NIL; - foreach(arg, coalesceexpr->args) + foreach(lc, coalesceexpr->args) { - Node *e; + Node *arg = lfirst(lc); + bool isCachable = true; - e = eval_const_expressions_mutator((Node *) lfirst(arg), - context); + arg = const_expressions_mutator((Node *) arg, + context, + &isCachable); /* * We can remove null constants from the list. For a @@ -2901,16 +3041,25 @@ eval_const_expressions_mutator(Node *node, * drop following arguments since they will never be * reached. */ - if (IsA(e, Const)) + if (IsA(arg, Const)) { - if (((Const *) e)->constisnull) + if (((Const *) arg)->constisnull) continue; /* drop null constant */ if (newargs == NIL) - return e; /* first expr */ - newargs = lappend(newargs, e); + return arg; /* first expr */ + newargs = lappend(newargs, arg); break; } - newargs = lappend(newargs, e); + + newargs = lappend(newargs, arg); + + if (isCachable) + { + if (is_cache_useful((Expr *) arg)) + cachable_args = lappend(cachable_args, &llast(newargs)); + } + else + *cachable = false; } /* @@ -2921,6 +3070,15 @@ eval_const_expressions_mutator(Node *node, return (Node *) makeNullConst(coalesceexpr->coalescetype, -1, coalesceexpr->coalescecollid); + if (!(*cachable)) + { + foreach(lc, cachable_args) + { + Expr **arg = (Expr **) lfirst(lc); + + *arg = (Expr *) makeCacheExpr(*arg); + } + } newcoalesce = makeNode(CoalesceExpr); newcoalesce->coalescetype = coalesceexpr->coalescetype; @@ -2942,13 +3100,18 @@ eval_const_expressions_mutator(Node *node, * is still the same as when the FieldSelect was created --- * this can change if someone did ALTER COLUMN TYPE on the * rowtype. + * + * This is never cachable because Var references aren't + * constants. simplify_function() also refuses caching of + * row-returning functions */ FieldSelect *fselect = (FieldSelect *) node; FieldSelect *newfselect; Node *arg; - arg = eval_const_expressions_mutator((Node *) fselect->arg, - context); + arg = const_expressions_mutator((Node *) fselect->arg, + context, + cachable); if (arg && IsA(arg, Var) && ((Var *) arg)->varattno == InvalidAttrNumber) { @@ -2999,8 +3162,9 @@ eval_const_expressions_mutator(Node *node, NullTest *newntest; Node *arg; - arg = eval_const_expressions_mutator((Node *) ntest->arg, - context); + arg = const_expressions_mutator((Node *) ntest->arg, + context, + cachable); if (arg && IsA(arg, RowExpr)) { /* @@ -3083,8 +3247,9 @@ eval_const_expressions_mutator(Node *node, BooleanTest *newbtest; Node *arg; - arg = eval_const_expressions_mutator((Node *) btest->arg, - context); + arg = const_expressions_mutator((Node *) btest->arg, + context, + cachable); if (arg && IsA(arg, Const)) { Const *carg = (Const *) arg; @@ -3130,7 +3295,6 @@ eval_const_expressions_mutator(Node *node, return (Node *) newbtest; } case T_PlaceHolderVar: - /* * In estimation mode, just strip the PlaceHolderVar node * altogether; this amounts to estimating that the contained value @@ -3138,15 +3302,26 @@ eval_const_expressions_mutator(Node *node, * just use the default behavior (ie, simplify the expression but * leave the PlaceHolderVar node intact). */ + *cachable = false; + if (context->estimate) { PlaceHolderVar *phv = (PlaceHolderVar *) node; + bool isCachable = true; /* ignored */ - return eval_const_expressions_mutator((Node *) phv->phexpr, - context); + return const_expressions_mutator((Node *) phv->phexpr, + context, + &isCachable); } break; + case T_Const: + /* Keep *cachable=true */ + break; + case T_CacheExpr: + /* */ default: + /* Everything else is not cachable */ + *cachable = false; break; } @@ -3154,10 +3329,11 @@ eval_const_expressions_mutator(Node *node, * For any node type not handled above, we recurse using * expression_tree_mutator, which will copy the node unchanged but try to * simplify its arguments (if any) using this routine. For example: we - * cannot eliminate an ArrayRef node, but we might be able to simplify - * constant expressions in its subscripts. + * cannot eliminate an ArrayRef node, but we might be able to simplify or + * cache constant expressions in its subscripts. */ - return expression_tree_mutator(node, eval_const_expressions_mutator, + return expression_tree_mutator(node, + caching_const_expressions_mutator, (void *) context); } @@ -3179,13 +3355,23 @@ eval_const_expressions_mutator(Node *node, * The output arguments *haveNull and *forceTrue must be initialized FALSE * by the caller. They will be set TRUE if a null constant or true constant, * respectively, is detected anywhere in the argument list. + * + * We divide elements into two separate lists, one for cachable items and one + * for non-cachable items. Upon returning, the cachable sub-list is turned + * into a new BoolExpr, cached and prepended. This is done in hopes that the + * cachable sub-list is faster to evaluate and short-cicruits the rest of the + * expression. + * + * Input: (cachable OR uncachable OR cachable OR uncachable) + * Output: (CACHE(cachable OR cachable) OR uncachable OR uncachable) */ static List * simplify_or_arguments(List *args, eval_const_expressions_context *context, - bool *haveNull, bool *forceTrue) + bool *haveNull, bool *forceTrue, bool *cachable) { - List *newargs = NIL; + List *nocache_args = NIL; + List *cachable_args = NIL; List *unprocessed_args; /* @@ -3200,6 +3386,7 @@ simplify_or_arguments(List *args, while (unprocessed_args) { Node *arg = (Node *) linitial(unprocessed_args); + bool isCachable = true; unprocessed_args = list_delete_first(unprocessed_args); @@ -3222,7 +3409,7 @@ simplify_or_arguments(List *args, } /* If it's not an OR, simplify it */ - arg = eval_const_expressions_mutator(arg, context); + arg = const_expressions_mutator(arg, context, &isCachable); /* * It is unlikely but not impossible for simplification of a non-OR @@ -3264,10 +3451,40 @@ simplify_or_arguments(List *args, } /* else emit the simplified arg into the result list */ - newargs = lappend(newargs, arg); + if (isCachable) + cachable_args = lappend(cachable_args, arg); + else + nocache_args = lappend(nocache_args, arg); } - return newargs; + if (cachable_args && nocache_args) + { + Expr *arg; + + /* Build a new expression for cachable sub-list */ + if (list_length(cachable_args) == 1) + arg = linitial(cachable_args); + else + arg = makeBoolExpr(OR_EXPR, cachable_args, -1); + + arg = insert_cache(arg); + + /* + * Assume that the cachable expression is cheaper to evaluate, so put + * it first + */ + nocache_args = lcons(arg, nocache_args); + + *cachable = false; + return nocache_args; + } + else if (nocache_args) + { + *cachable = false; + return nocache_args; + } + else + return cachable_args; } /* @@ -3288,13 +3505,23 @@ simplify_or_arguments(List *args, * The output arguments *haveNull and *forceFalse must be initialized FALSE * by the caller. They will be set TRUE if a null constant or false constant, * respectively, is detected anywhere in the argument list. + * + * We divide elements into two separate lists, one for cachable items and one + * for non-cachable items. Upon returning, the cachable sub-list is turned + * into a new BoolExpr, cached and prepended. This is done in hopes that the + * cachable sub-list is faster to evaluate and short-cicruits the rest of the + * expression. + * + * Input: (cachable OR uncachable OR cachable OR uncachable) + * Output: (CACHE(cachable OR cachable) OR uncachable OR uncachable) */ static List * simplify_and_arguments(List *args, eval_const_expressions_context *context, - bool *haveNull, bool *forceFalse) + bool *haveNull, bool *forceFalse, bool *cachable) { - List *newargs = NIL; + List *nocache_args = NIL; + List *cachable_args = NIL; List *unprocessed_args; /* See comments in simplify_or_arguments */ @@ -3302,6 +3529,7 @@ simplify_and_arguments(List *args, while (unprocessed_args) { Node *arg = (Node *) linitial(unprocessed_args); + bool isCachable = true; unprocessed_args = list_delete_first(unprocessed_args); @@ -3324,7 +3552,7 @@ simplify_and_arguments(List *args, } /* If it's not an AND, simplify it */ - arg = eval_const_expressions_mutator(arg, context); + arg = const_expressions_mutator(arg, context, &isCachable); /* * It is unlikely but not impossible for simplification of a non-AND @@ -3366,10 +3594,40 @@ simplify_and_arguments(List *args, } /* else emit the simplified arg into the result list */ - newargs = lappend(newargs, arg); + if (isCachable) + cachable_args = lappend(cachable_args, arg); + else + nocache_args = lappend(nocache_args, arg); } - return newargs; + if (cachable_args && nocache_args) + { + Expr *arg; + + /* Build a new expression for cachable sub-list */ + if (list_length(cachable_args) == 1) + arg = linitial(cachable_args); + else + arg = makeBoolExpr(AND_EXPR, cachable_args, -1); + + arg = insert_cache(arg); + + /* + * Assume that the cachable expression is cheaper to evaluate, so put + * it first + */ + nocache_args = lcons(arg, nocache_args); + + *cachable = false; + return nocache_args; + } + else if (nocache_args) + { + *cachable = false; + return nocache_args; + } + else + return cachable_args; } /* @@ -3444,7 +3702,7 @@ simplify_boolean_equality(Oid opno, List *args) * Inputs are the original expression (can be NULL), function OID, actual * result type OID (which is needed for polymorphic functions), result typmod, * result collation, the input collation to use for the function, the - * pre-simplified argument list, and some flags; also the context data for + * un-simplified argument list, and some flags; also the context data for * eval_const_expressions. In common cases, several of the arguments could be * derived from the original expression. Sending them separately avoids * duplicating NodeTag-specific knowledge, and it's necessary for CoerceViaIO. @@ -3463,14 +3721,17 @@ simplify_boolean_equality(Oid opno, List *args) static Expr * simplify_function(Expr *oldexpr, Oid funcid, Oid result_type, int32 result_typmod, Oid result_collid, - Oid input_collid, List **args, - bool has_named_args, + Oid input_collid, List **old_args, bool allow_inline, - eval_const_expressions_context *context) + eval_const_expressions_context *context, + bool *cachable) { HeapTuple func_tuple; Expr *newexpr; Oid transform; + ListCell *lc; + List *args = NIL; + List *cachable_args = NIL; /* XXX use bitmapset instead? */ /* * We have three strategies for simplification: execute the function to @@ -3484,19 +3745,53 @@ simplify_function(Expr *oldexpr, Oid funcid, if (!HeapTupleIsValid(func_tuple)) elog(ERROR, "cache lookup failed for function %u", funcid); + if (oldexpr && IsA(oldexpr, FuncExpr)) + + /* + * Reorder named arguments and add defaults if needed. Returns a + * copied list, so we can mutate it later. + */ + args = simplify_copy_function_arguments(*old_args, result_type, func_tuple); + else + /* Copy argument list before we start mutating it */ + args = list_copy(*old_args); + + /* Reduce constants in the FuncExpr's arguments */ + foreach(lc, args) + { + Node *arg = (Node *) lfirst(lc); + bool isCachable = true; + + arg = const_expressions_mutator(arg, context, &isCachable); + lfirst(lc) = arg; + + /* + * We're stuck in a catch-22 here. If all arguments and the call + * itself is cachable, we don't want to insert cache nodes for + * arguments. But we don't know that until we walk through all the + * arguments. + * + * So we accumulate cachable arguments in a list of ListCell pointers, + * which we will update later if necessary. + * + * Note: The args list may not be mutated from here on this until we + * handle cachable_args below. + */ + if (isCachable) + { + if (is_cache_useful((Expr *) arg)) + cachable_args = lappend(cachable_args, &lfirst(lc)); + } + else + *cachable = false; /* One bad arg spoils the whole cache */ + } + /* - * While we have the tuple, reorder named arguments and add default - * arguments if needed. + * evaluate_function tells us about the cachability of the function call */ - if (has_named_args) - *args = reorder_function_arguments(*args, result_type, func_tuple, - context); - else if (((Form_pg_proc) GETSTRUCT(func_tuple))->pronargs > list_length(*args)) - *args = add_function_defaults(*args, result_type, func_tuple, context); - newexpr = evaluate_function(funcid, result_type, result_typmod, - result_collid, input_collid, *args, - func_tuple, context); + result_collid, input_collid, args, + func_tuple, context, cachable); /* * Some functions calls can be simplified at plan time based on properties @@ -3533,30 +3828,111 @@ simplify_function(Expr *oldexpr, Oid funcid, PointerGetDatum(oldexpr))); if (!newexpr && allow_inline) + { + /* + * The inlined expression may be cachable regardless of the above, if + * the function's volatility was mis-labeled or if volatile parts are + * removed (possible due to constant folding of conditionals). + * + * inline_function() also takes care of caching all cachable subtrees + */ + bool isCachable = true; + newexpr = inline_function(funcid, result_type, result_collid, - input_collid, *args, - func_tuple, context); + input_collid, args, + func_tuple, context, &isCachable); + + if (newexpr) + *cachable = isCachable; + } ReleaseSysCache(func_tuple); + /* + * If function call can't be cached/inlined, update all cachable arguments + */ + if (!newexpr && !(*cachable)) + { + foreach(lc, cachable_args) + { + Node **arg = (Node **) lfirst(lc); + + *arg = (Node *) makeCacheExpr((Expr *) *arg); + } + } + + /* Argument processing done, give it back to the caller */ + *old_args = args; + return newexpr; } /* + * This function prepares a function's argument list -- converting + * named-notation argument list into positional notation while adding any + * needed default argument expressions. + * + * Always returns a copy of the argument list, the original list is not + * modified. + */ +static List * +simplify_copy_function_arguments(List *old_args, Oid result_type, + HeapTuple func_tuple) +{ + List *args = NIL; + ListCell *lc; + bool has_named_args = false; + int nargs_before; + + /* Do we need to reorder named arguments? */ + foreach(lc, old_args) + { + Node *arg = (Node *) lfirst(lc); + + if (IsA(arg, NamedArgExpr)) + { + has_named_args = true; + break; + } + } + + nargs_before = list_length(old_args); + + /* + * Reorder named arguments and add default arguments if needed. + */ + if (has_named_args) + args = reorder_function_arguments(old_args, result_type, func_tuple); + + else + { + args = list_copy(old_args); + + /* Append missing default arguments to the list */ + if (((Form_pg_proc) GETSTRUCT(func_tuple))->pronargs > list_length(args)) + args = add_function_defaults(args, result_type, func_tuple); + } + + if (list_length(args) != nargs_before) + /* Added defaults may need casts */ + recheck_cast_function_args(args, result_type, func_tuple); + + return args; +} + +/* * reorder_function_arguments: convert named-notation args to positional args * * This function also inserts default argument values as needed, since it's * impossible to form a truly valid positional call without that. */ static List * -reorder_function_arguments(List *args, Oid result_type, HeapTuple func_tuple, - eval_const_expressions_context *context) +reorder_function_arguments(List *args, Oid result_type, HeapTuple func_tuple) { Form_pg_proc funcform = (Form_pg_proc) GETSTRUCT(func_tuple); int pronargs = funcform->pronargs; int nargsprovided = list_length(args); Node *argarray[FUNC_MAX_ARGS]; - Bitmapset *defargnumbers; ListCell *lc; int i; @@ -3590,7 +3966,6 @@ reorder_function_arguments(List *args, Oid result_type, HeapTuple func_tuple, * Fetch default expressions, if needed, and insert into array at proper * locations (they aren't necessarily consecutive or all used) */ - defargnumbers = NULL; if (nargsprovided < pronargs) { List *defaults = fetch_function_defaults(func_tuple); @@ -3599,10 +3974,7 @@ reorder_function_arguments(List *args, Oid result_type, HeapTuple func_tuple, foreach(lc, defaults) { if (argarray[i] == NULL) - { argarray[i] = (Node *) lfirst(lc); - defargnumbers = bms_add_member(defargnumbers, i); - } i++; } } @@ -3615,32 +3987,6 @@ reorder_function_arguments(List *args, Oid result_type, HeapTuple func_tuple, args = lappend(args, argarray[i]); } - /* Recheck argument types and add casts if needed */ - recheck_cast_function_args(args, result_type, func_tuple); - - /* - * Lastly, we have to recursively simplify the defaults we just added (but - * don't recurse on the args passed in, as we already did those). This - * isn't merely an optimization, it's *necessary* since there could be - * functions with named or defaulted arguments down in there. - * - * Note that we do this last in hopes of simplifying any typecasts that - * were added by recheck_cast_function_args --- there shouldn't be any new - * casts added to the explicit arguments, but casts on the defaults are - * possible. - */ - if (defargnumbers != NULL) - { - i = 0; - foreach(lc, args) - { - if (bms_is_member(i, defargnumbers)) - lfirst(lc) = eval_const_expressions_mutator((Node *) lfirst(lc), - context); - i++; - } - } - return args; } @@ -3651,20 +3997,17 @@ reorder_function_arguments(List *args, Oid result_type, HeapTuple func_tuple, * and so we know we just need to add defaults at the end. */ static List * -add_function_defaults(List *args, Oid result_type, HeapTuple func_tuple, - eval_const_expressions_context *context) +add_function_defaults(List *args, Oid result_type, HeapTuple func_tuple) { Form_pg_proc funcform = (Form_pg_proc) GETSTRUCT(func_tuple); - int nargsprovided = list_length(args); List *defaults; int ndelete; - ListCell *lc; /* Get all the default expressions from the pg_proc tuple */ defaults = fetch_function_defaults(func_tuple); /* Delete any unused defaults from the list */ - ndelete = nargsprovided + list_length(defaults) - funcform->pronargs; + ndelete = list_length(args) + list_length(defaults) - funcform->pronargs; if (ndelete < 0) elog(ERROR, "not enough default arguments"); while (ndelete-- > 0) @@ -3673,28 +4016,6 @@ add_function_defaults(List *args, Oid result_type, HeapTuple func_tuple, /* And form the combined argument list */ args = list_concat(args, defaults); - /* Recheck argument types and add casts if needed */ - recheck_cast_function_args(args, result_type, func_tuple); - - /* - * Lastly, we have to recursively simplify the defaults we just added (but - * don't recurse on the args passed in, as we already did those). This - * isn't merely an optimization, it's *necessary* since there could be - * functions with named or defaulted arguments down in there. - * - * Note that we do this last in hopes of simplifying any typecasts that - * were added by recheck_cast_function_args --- there shouldn't be any new - * casts added to the explicit arguments, but casts on the defaults are - * possible. - */ - foreach(lc, args) - { - if (nargsprovided-- > 0) - continue; /* skip original arg positions */ - lfirst(lc) = eval_const_expressions_mutator((Node *) lfirst(lc), - context); - } - return args; } @@ -3784,7 +4105,8 @@ static Expr * evaluate_function(Oid funcid, Oid result_type, int32 result_typmod, Oid result_collid, Oid input_collid, List *args, HeapTuple func_tuple, - eval_const_expressions_context *context) + eval_const_expressions_context *context, + bool *cachable) { Form_pg_proc funcform = (Form_pg_proc) GETSTRUCT(func_tuple); bool has_nonconst_input = false; @@ -3796,7 +4118,10 @@ evaluate_function(Oid funcid, Oid result_type, int32 result_typmod, * Can't simplify if it returns a set. */ if (funcform->proretset) + { + *cachable = false; return NULL; + } /* * Can't simplify if it returns RECORD. The immediate problem is that it @@ -3810,7 +4135,13 @@ evaluate_function(Oid funcid, Oid result_type, int32 result_typmod, * gotchas, seems best to leave the function call unreduced. */ if (funcform->prorettype == RECORDOID) + { + *cachable = false; return NULL; + } + + if (funcform->provolatile == PROVOLATILE_VOLATILE) + *cachable = false; /* * Check for constant inputs and especially constant-NULL inputs. @@ -3907,7 +4238,8 @@ static Expr * inline_function(Oid funcid, Oid result_type, Oid result_collid, Oid input_collid, List *args, HeapTuple func_tuple, - eval_const_expressions_context *context) + eval_const_expressions_context *context, + bool *cachable) { Form_pg_proc funcform = (Form_pg_proc) GETSTRUCT(func_tuple); char *src; @@ -4186,7 +4518,7 @@ inline_function(Oid funcid, Oid result_type, Oid result_collid, * the current function to the context list of active functions. */ context->active_fns = lcons_oid(funcid, context->active_fns); - newexpr = eval_const_expressions_mutator(newexpr, context); + newexpr = const_expressions_mutator(newexpr, context, cachable); context->active_fns = list_delete_first(context->active_fns); error_context_stack = sqlerrcontext.previous; @@ -4266,6 +4598,34 @@ sql_inline_error_callback(void *arg) } /* + * Is it useful to cache this expression? Constants and param references are + * always fast to access so don't insert cache in front of those. + * + * Without inline, we lose almost 10% time in some very simple queries (!) + */ +static inline bool +is_cache_useful(Expr *expr) +{ + if (IsA(expr, Const)) + return false; + if (IsA(expr, Param)) + return false; + return true; +} + +static Expr * +insert_cache(Expr *expr) +{ + /* Don't cache obviously cheap expressions */ + if (!is_cache_useful(expr)) + return expr; + + Assert(!IsA(expr, CacheExpr)); + + return (Expr *) makeCacheExpr(expr); +} + +/* * evaluate_expr: pre-evaluate a constant expression * * We use the executor's routine ExecEvalExpr() to avoid duplication of diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 29df748..b0190bb 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -4612,8 +4612,12 @@ isSimpleNode(Node *node, Node *parentNode, int prettyFlags) /* function-like: name(..) or name[..] */ return true; - /* CASE keywords act as parentheses */ + case T_CacheExpr: + /* hidden from user */ + return true; + case T_CaseExpr: + /* CASE keywords act as parentheses */ return true; case T_FieldSelect: @@ -5734,6 +5738,20 @@ get_rule_expr(Node *node, deparse_context *context, } break; + case T_CacheExpr: + { + CacheExpr *cache = (CacheExpr *) node; + +#ifdef DEBUG_CACHEEXPR + appendStringInfo(buf, "CACHE["); + get_rule_expr((Node *) cache->arg, context, true); + appendStringInfoChar(buf, ']'); +#else + get_rule_expr((Node *) cache->arg, context, true); +#endif + } + break; + case T_CoerceToDomain: { CoerceToDomain *ctest = (CoerceToDomain *) node; diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index 0a89f18..8ff570a 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -673,6 +673,23 @@ typedef struct FuncExprState } FuncExprState; /* ---------------- + * CacheExprState node + * + * Takes care of caching execution-time constants that cannot be constant + * folded at plan-time. + * ---------------- + */ +typedef struct CacheExprState +{ + ExprState xprstate; + ExprState *subexpr; /* state of sub-expression */ + + bool cacheEnabled; + Datum result; /* cached result */ + bool isNull; /* is result NULL? */ +} CacheExprState; + +/* ---------------- * ScalarArrayOpExprState node * * This is a FuncExprState plus some additional data. diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h index c5ed6cb..fe488db 100644 --- a/src/include/nodes/makefuncs.h +++ b/src/include/nodes/makefuncs.h @@ -74,6 +74,7 @@ extern TypeName *makeTypeNameFromOid(Oid typeOid, int32 typmod); extern FuncExpr *makeFuncExpr(Oid funcid, Oid rettype, List *args, Oid funccollid, Oid inputcollid, CoercionForm fformat); +extern CacheExpr *makeCacheExpr(Expr *arg); extern DefElem *makeDefElem(char *name, Node *arg); extern DefElem *makeDefElemExtended(char *nameSpace, char *name, Node *arg, diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index 3a24089..eb2009c 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -172,6 +172,7 @@ typedef enum NodeTag T_JoinExpr, T_FromExpr, T_IntoClause, + T_CacheExpr, /* * TAGS FOR EXPRESSION STATE NODES (execnodes.h) @@ -205,6 +206,7 @@ typedef enum NodeTag T_NullTestState, T_CoerceToDomainState, T_DomainConstraintState, + T_CacheExprState, /* * TAGS FOR PLANNER NODES (relation.h) diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index 28a2b12..d599d23 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -1029,6 +1029,21 @@ typedef struct BooleanTest } BooleanTest; /* + * CacheExpr + * + * CacheExpr is a constant expression to be cached at execution time. The + * eval_const_expressions() function inserts CacheExpr nodes nodes at + * strategic locations when it recognizes constant expressions that cannot be + * constant-folded at plan time, such as expressions with Param references, + * stable function and operator calls with constant arguments, etc + */ +typedef struct CacheExpr +{ + Expr xpr; + Expr *arg; +} CacheExpr; + +/* * CoerceToDomain * * CoerceToDomain represents the operation of coercing a value to a domain diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c index 717ad79..a417715 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -5446,6 +5446,11 @@ exec_simple_check_node(Node *node) return TRUE; case T_Param: + /* + * If we have other kinds of params here, then earlier tests + * should have ruled out this as simple expression + */ + Assert(((Param *) node)->paramkind == PARAM_EXTERN); return TRUE; case T_ArrayRef: @@ -5678,6 +5683,10 @@ exec_simple_check_node(Node *node) return TRUE; } + case T_CacheExpr: + /* Caching is disabled for simple expressions */ + return TRUE; + default: return FALSE; } diff --git a/src/test/regress/expected/cache.out b/src/test/regress/expected/cache.out new file mode 100644 index 0000000..1e4664b --- /dev/null +++ b/src/test/regress/expected/cache.out @@ -0,0 +1,676 @@ +-- +-- Test cachable expressions +-- +-- If the NOTICE outputs of these functions change, you've probably broken +-- something with the CacheExpr optimization +-- +create function stable_true() returns bool STABLE language plpgsql as +$$begin raise notice 'STABLE TRUE'; return true; end$$; +create function volatile_true() returns bool VOLATILE language plpgsql as +$$begin raise notice 'VOLATILE TRUE'; return true; end$$; +create function stable_false() returns bool STABLE language plpgsql as +$$begin raise notice 'STABLE FALSE'; return false; end$$; +create function volatile_false() returns bool VOLATILE language plpgsql as +$$begin raise notice 'VOLATILE FALSE'; return false; end$$; +-- Table with two rows +create table two (i int); +insert into two values (1), (2); +-- Boolean expressions +select stable_false() or volatile_true() or stable_true() as b from two; +NOTICE: STABLE FALSE +NOTICE: STABLE TRUE + b +--- + t + t +(2 rows) + +select stable_true() or volatile_false() or stable_false() as b from two; +NOTICE: STABLE TRUE + b +--- + t + t +(2 rows) + +select stable_false() or volatile_true() as b from two; +NOTICE: STABLE FALSE +NOTICE: VOLATILE TRUE +NOTICE: VOLATILE TRUE + b +--- + t + t +(2 rows) + +select stable_false() or stable_false() or volatile_true() as b from two; +NOTICE: STABLE FALSE +NOTICE: STABLE FALSE +NOTICE: VOLATILE TRUE +NOTICE: VOLATILE TRUE + b +--- + t + t +(2 rows) + +select volatile_true() or volatile_false() or stable_false() as b from two; +NOTICE: STABLE FALSE +NOTICE: VOLATILE TRUE +NOTICE: VOLATILE TRUE + b +--- + t + t +(2 rows) + +select volatile_false() or volatile_true() or stable_false() as b from two; +NOTICE: STABLE FALSE +NOTICE: VOLATILE FALSE +NOTICE: VOLATILE TRUE +NOTICE: VOLATILE FALSE +NOTICE: VOLATILE TRUE + b +--- + t + t +(2 rows) + +select stable_true() and volatile_false() and stable_false() as b from two; +NOTICE: STABLE TRUE +NOTICE: STABLE FALSE + b +--- + f + f +(2 rows) + +select stable_false() and volatile_true() and stable_true() as b from two; +NOTICE: STABLE FALSE + b +--- + f + f +(2 rows) + +select stable_true() and volatile_false() as b from two; +NOTICE: STABLE TRUE +NOTICE: VOLATILE FALSE +NOTICE: VOLATILE FALSE + b +--- + f + f +(2 rows) + +select stable_true() and stable_true() and volatile_false() as b from two; +NOTICE: STABLE TRUE +NOTICE: STABLE TRUE +NOTICE: VOLATILE FALSE +NOTICE: VOLATILE FALSE + b +--- + f + f +(2 rows) + +select volatile_true() and volatile_false() and stable_true() as b from two; +NOTICE: STABLE TRUE +NOTICE: VOLATILE TRUE +NOTICE: VOLATILE FALSE +NOTICE: VOLATILE TRUE +NOTICE: VOLATILE FALSE + b +--- + f + f +(2 rows) + +select volatile_false() and volatile_true() and stable_true() as b from two; +NOTICE: STABLE TRUE +NOTICE: VOLATILE FALSE +NOTICE: VOLATILE FALSE + b +--- + f + f +(2 rows) + +select not stable_true() as b from two; +NOTICE: STABLE TRUE + b +--- + f + f +(2 rows) + +select not volatile_true() as b from two; +NOTICE: VOLATILE TRUE +NOTICE: VOLATILE TRUE + b +--- + f + f +(2 rows) + +-- Bind params +prepare param_test(bool) as select $1 or stable_false() or volatile_true() as b from two; +execute param_test(true); + b +--- + t + t +(2 rows) + +execute param_test(false); +NOTICE: STABLE FALSE +NOTICE: VOLATILE TRUE +NOTICE: VOLATILE TRUE + b +--- + t + t +(2 rows) + +-- Function calls +create function stable(bool) returns bool STABLE language plpgsql as +$$begin raise notice 'STABLE(%)', $1; return $1; end$$; +create function volatile(bool) returns bool VOLATILE language plpgsql as +$$begin raise notice 'VOLATILE(%)', $1; return $1; end$$; +select volatile(volatile_true()) from two; +NOTICE: VOLATILE TRUE +NOTICE: VOLATILE(t) +NOTICE: VOLATILE TRUE +NOTICE: VOLATILE(t) + volatile +---------- + t + t +(2 rows) + +select stable(stable_true()) from two; +NOTICE: STABLE TRUE +NOTICE: STABLE(t) + stable +-------- + t + t +(2 rows) + +select stable(volatile_true()) from two; +NOTICE: VOLATILE TRUE +NOTICE: STABLE(t) +NOTICE: VOLATILE TRUE +NOTICE: STABLE(t) + stable +-------- + t + t +(2 rows) + +select volatile(stable_true()) from two; +NOTICE: STABLE TRUE +NOTICE: VOLATILE(t) +NOTICE: VOLATILE(t) + volatile +---------- + t + t +(2 rows) + +create function stable(bool, bool) returns bool STABLE language plpgsql as +$$begin raise notice 'STABLE(%, %)', $1, $2; return $1; end$$; +create function volatile(bool, bool) returns bool VOLATILE language plpgsql as +$$begin raise notice 'VOLATILE(%, %)', $1, $2; return $1; end$$; +select stable(volatile_true(), volatile_false()) from two; +NOTICE: VOLATILE TRUE +NOTICE: VOLATILE FALSE +NOTICE: STABLE(t, f) +NOTICE: VOLATILE TRUE +NOTICE: VOLATILE FALSE +NOTICE: STABLE(t, f) + stable +-------- + t + t +(2 rows) + +select stable(stable_true(), volatile_false()) from two; +NOTICE: STABLE TRUE +NOTICE: VOLATILE FALSE +NOTICE: STABLE(t, f) +NOTICE: VOLATILE FALSE +NOTICE: STABLE(t, f) + stable +-------- + t + t +(2 rows) + +select stable(stable_true(), stable_false()) from two; +NOTICE: STABLE TRUE +NOTICE: STABLE FALSE +NOTICE: STABLE(t, f) + stable +-------- + t + t +(2 rows) + +select volatile(volatile_true(), volatile_false()) from two; +NOTICE: VOLATILE TRUE +NOTICE: VOLATILE FALSE +NOTICE: VOLATILE(t, f) +NOTICE: VOLATILE TRUE +NOTICE: VOLATILE FALSE +NOTICE: VOLATILE(t, f) + volatile +---------- + t + t +(2 rows) + +select volatile(stable_true(), volatile_false()) from two; +NOTICE: STABLE TRUE +NOTICE: VOLATILE FALSE +NOTICE: VOLATILE(t, f) +NOTICE: VOLATILE FALSE +NOTICE: VOLATILE(t, f) + volatile +---------- + t + t +(2 rows) + +select volatile(stable_true(), stable_false()) from two; +NOTICE: STABLE TRUE +NOTICE: STABLE FALSE +NOTICE: VOLATILE(t, f) +NOTICE: VOLATILE(t, f) + volatile +---------- + t + t +(2 rows) + +-- Default arguments +create function stable_def(a bool = stable_false(), b bool = volatile_true()) +returns bool STABLE language plpgsql as +$$begin raise notice 'STABLE(%, %)', $1, $2; return $1; end$$; +select stable_def() from two; +NOTICE: STABLE FALSE +NOTICE: VOLATILE TRUE +NOTICE: STABLE(f, t) +NOTICE: VOLATILE TRUE +NOTICE: STABLE(f, t) + stable_def +------------ + f + f +(2 rows) + +select stable_def(b := stable_true()) from two; +NOTICE: STABLE FALSE +NOTICE: STABLE TRUE +NOTICE: STABLE(f, t) + stable_def +------------ + f + f +(2 rows) + +select stable_def(volatile_false()) from two; +NOTICE: VOLATILE FALSE +NOTICE: VOLATILE TRUE +NOTICE: STABLE(f, t) +NOTICE: VOLATILE FALSE +NOTICE: VOLATILE TRUE +NOTICE: STABLE(f, t) + stable_def +------------ + f + f +(2 rows) + +-- Operators +create function stable_eq(bool, bool) returns bool STABLE language plpgsql as +$$begin raise notice 'STABLE % == %', $1, $2; return $1 = $2; end$$; +create function volatile_eq(bool, bool) returns bool VOLATILE language plpgsql as +$$begin raise notice 'VOLATILE % =%%= %', $1, $2; return $1 = $2; end$$; +create operator == (procedure = stable_eq, leftarg=bool, rightarg=bool); +create operator =%= (procedure = volatile_eq, leftarg=bool, rightarg=bool); +select volatile_true() == volatile_false() from two; +NOTICE: VOLATILE TRUE +NOTICE: VOLATILE FALSE +NOTICE: STABLE t == f +NOTICE: VOLATILE TRUE +NOTICE: VOLATILE FALSE +NOTICE: STABLE t == f + ?column? +---------- + f + f +(2 rows) + +select stable_true() == volatile_false() from two; +NOTICE: STABLE TRUE +NOTICE: VOLATILE FALSE +NOTICE: STABLE t == f +NOTICE: VOLATILE FALSE +NOTICE: STABLE t == f + ?column? +---------- + f + f +(2 rows) + +select stable_true() == stable_false() from two; +NOTICE: STABLE TRUE +NOTICE: STABLE FALSE +NOTICE: STABLE t == f + ?column? +---------- + f + f +(2 rows) + +select volatile_true() =%= volatile_false() from two; +NOTICE: VOLATILE TRUE +NOTICE: VOLATILE FALSE +NOTICE: VOLATILE t =%= f +NOTICE: VOLATILE TRUE +NOTICE: VOLATILE FALSE +NOTICE: VOLATILE t =%= f + ?column? +---------- + f + f +(2 rows) + +select stable_true() =%= volatile_false() from two; +NOTICE: STABLE TRUE +NOTICE: VOLATILE FALSE +NOTICE: VOLATILE t =%= f +NOTICE: VOLATILE FALSE +NOTICE: VOLATILE t =%= f + ?column? +---------- + f + f +(2 rows) + +select stable_true() =%= stable_false() from two; +NOTICE: STABLE TRUE +NOTICE: STABLE FALSE +NOTICE: VOLATILE t =%= f +NOTICE: VOLATILE t =%= f + ?column? +---------- + f + f +(2 rows) + +select (volatile_true() or stable_true()) == true as b from two; +NOTICE: STABLE TRUE +NOTICE: STABLE t == t +NOTICE: STABLE t == t + b +--- + t + t +(2 rows) + +-- Coalesce +create function stable_null() returns bool STABLE language plpgsql as +$$begin raise notice 'STABLE NULL'; return null; end$$; +create function volatile_null() returns bool VOLATILE language plpgsql as +$$begin raise notice 'VOLATILE NULL'; return null; end$$; +select coalesce(stable_null(), stable_true()) from two; +NOTICE: STABLE NULL +NOTICE: STABLE TRUE + coalesce +---------- + t + t +(2 rows) + +select coalesce(stable_true(), volatile_null()) from two; +NOTICE: STABLE TRUE + coalesce +---------- + t + t +(2 rows) + +select coalesce(volatile_null(), stable_null(), volatile_true()) from two; +NOTICE: VOLATILE NULL +NOTICE: STABLE NULL +NOTICE: VOLATILE TRUE +NOTICE: VOLATILE NULL +NOTICE: VOLATILE TRUE + coalesce +---------- + t + t +(2 rows) + +-- Case/when +select case when stable_true() then 't' else volatile_false() end as b from two; +NOTICE: STABLE TRUE + b +--- + t + t +(2 rows) + +select case when volatile_true() then stable_true() else stable_false() end as b from two; +NOTICE: VOLATILE TRUE +NOTICE: STABLE TRUE +NOTICE: VOLATILE TRUE + b +--- + t + t +(2 rows) + +select case when i=1 then stable_true() else stable_false() end as b from two; +NOTICE: STABLE TRUE +NOTICE: STABLE FALSE + b +--- + t + f +(2 rows) + +select case when i=1 then volatile_true() else volatile_false() end as b from two; +NOTICE: VOLATILE TRUE +NOTICE: VOLATILE FALSE + b +--- + t + f +(2 rows) + +select case when 't' then 't' else volatile_false() end == true as b from two; +NOTICE: STABLE t == t + b +--- + t + t +(2 rows) + +-- Coerce via I/O +select stable_true()::text::bool == true as b from two; +NOTICE: STABLE TRUE +NOTICE: STABLE t == t + b +--- + t + t +(2 rows) + +select volatile_true()::text::bool == true as b from two; +NOTICE: VOLATILE TRUE +NOTICE: STABLE t == t +NOTICE: VOLATILE TRUE +NOTICE: STABLE t == t + b +--- + t + t +(2 rows) + +-- IS DISTINCT FROM +select (stable_true() is not distinct from volatile_false()) as b from two; +NOTICE: STABLE TRUE +NOTICE: VOLATILE FALSE +NOTICE: VOLATILE FALSE + b +--- + f + f +(2 rows) + +select (stable_true() is distinct from stable_false()) == false as b from two; +NOTICE: STABLE TRUE +NOTICE: STABLE FALSE +NOTICE: STABLE t == f + b +--- + f + f +(2 rows) + +select (volatile_true() is distinct from null) as b from two; +NOTICE: VOLATILE TRUE +NOTICE: VOLATILE TRUE + b +--- + t + t +(2 rows) + +-- IS NULL +select volatile_true() is null == false as b from two; +NOTICE: VOLATILE TRUE +NOTICE: STABLE f == f +NOTICE: VOLATILE TRUE +NOTICE: STABLE f == f + b +--- + t + t +(2 rows) + +select stable_null() is not null == true as b from two; +NOTICE: STABLE NULL +NOTICE: STABLE f == t + b +--- + f + f +(2 rows) + +-- Boolean tests +select volatile_false() is true == true as b from two; +NOTICE: VOLATILE FALSE +NOTICE: STABLE f == t +NOTICE: VOLATILE FALSE +NOTICE: STABLE f == t + b +--- + f + f +(2 rows) + +select stable_null() is not unknown == false as b from two; +NOTICE: STABLE NULL +NOTICE: STABLE f == f + b +--- + t + t +(2 rows) + +-- Field select -- not currently cached +create function stable_row(a out int, b out int) STABLE language plpgsql as +$$begin raise notice 'STABLE ROW'; a = 1; b = 2; end$$; +select (stable_row()).a from two; +NOTICE: STABLE ROW +NOTICE: STABLE ROW + a +--- + 1 + 1 +(2 rows) + +-- WHERE clause +begin; +-- stable_true is evaluated twice due to planning estimates +declare stable_where cursor for select * from two where i > stable_true()::int; +NOTICE: STABLE TRUE +fetch all from stable_where; +NOTICE: STABLE TRUE + i +--- + 2 +(1 row) + +declare volatile_where cursor for select * from two where i = volatile_false()::int; +fetch all from volatile_where; +NOTICE: VOLATILE FALSE +NOTICE: VOLATILE FALSE + i +--- +(0 rows) + +rollback; +-- INSERT column default expressions +create table defaults ( + dummy int, + a bool default stable_true(), + b bool default volatile_true() +); +insert into defaults (dummy) values(0), (1); +NOTICE: STABLE TRUE +NOTICE: VOLATILE TRUE +NOTICE: VOLATILE TRUE +-- ALTER COLUMN TYPE USING +alter table defaults alter column a type bool using stable_false(); +NOTICE: STABLE FALSE +alter table defaults alter column a type bool using volatile_false(); +NOTICE: VOLATILE FALSE +NOTICE: VOLATILE FALSE +-- COPY FROM with default expressions +copy defaults (dummy) from stdin; +NOTICE: STABLE TRUE +CONTEXT: COPY defaults, line 1: "2" +NOTICE: VOLATILE TRUE +CONTEXT: COPY defaults, line 1: "2" +NOTICE: VOLATILE TRUE +CONTEXT: COPY defaults, line 2: "3" +-- PL/pgSQL Simple expressions +-- Make sure we don't cache simple expressions -- these expressions are only +-- initialized once per transaction and then executed multiple times +create function stable_max() returns int STABLE language plpgsql as +$$begin return (select max(i) from two); end$$; +create function simple() returns int STABLE language plpgsql as +$$begin return stable_max(); end$$; +begin; +select simple(); + simple +-------- + 2 +(1 row) + +insert into two values(3); +select simple(); + simple +-------- + 3 +(1 row) + +rollback; +-- The end +drop table defaults; +drop table two; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index b47b08b..8b6e563 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -83,7 +83,7 @@ test: select_into select_distinct select_distinct_on select_implicit select_havi # ---------- # Another group of parallel tests # ---------- -test: privileges security_label collate +test: privileges security_label collate cache test: misc # rules cannot run concurrently with any test that creates a view diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 57806b5..bc7e325 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -91,6 +91,7 @@ test: prepared_xacts test: privileges test: security_label test: collate +test: cache test: misc test: rules test: select_views diff --git a/src/test/regress/sql/cache.sql b/src/test/regress/sql/cache.sql new file mode 100644 index 0000000..8cd8908 --- /dev/null +++ b/src/test/regress/sql/cache.sql @@ -0,0 +1,179 @@ +-- +-- Test cachable expressions +-- +-- If the NOTICE outputs of these functions change, you've probably broken +-- something with the CacheExpr optimization +-- + +create function stable_true() returns bool STABLE language plpgsql as +$$begin raise notice 'STABLE TRUE'; return true; end$$; +create function volatile_true() returns bool VOLATILE language plpgsql as +$$begin raise notice 'VOLATILE TRUE'; return true; end$$; +create function stable_false() returns bool STABLE language plpgsql as +$$begin raise notice 'STABLE FALSE'; return false; end$$; +create function volatile_false() returns bool VOLATILE language plpgsql as +$$begin raise notice 'VOLATILE FALSE'; return false; end$$; + +-- Table with two rows +create table two (i int); +insert into two values (1), (2); + +-- Boolean expressions +select stable_false() or volatile_true() or stable_true() as b from two; +select stable_true() or volatile_false() or stable_false() as b from two; +select stable_false() or volatile_true() as b from two; +select stable_false() or stable_false() or volatile_true() as b from two; +select volatile_true() or volatile_false() or stable_false() as b from two; +select volatile_false() or volatile_true() or stable_false() as b from two; + +select stable_true() and volatile_false() and stable_false() as b from two; +select stable_false() and volatile_true() and stable_true() as b from two; +select stable_true() and volatile_false() as b from two; +select stable_true() and stable_true() and volatile_false() as b from two; +select volatile_true() and volatile_false() and stable_true() as b from two; +select volatile_false() and volatile_true() and stable_true() as b from two; + +select not stable_true() as b from two; +select not volatile_true() as b from two; + +-- Bind params +prepare param_test(bool) as select $1 or stable_false() or volatile_true() as b from two; +execute param_test(true); +execute param_test(false); + +-- Function calls +create function stable(bool) returns bool STABLE language plpgsql as +$$begin raise notice 'STABLE(%)', $1; return $1; end$$; +create function volatile(bool) returns bool VOLATILE language plpgsql as +$$begin raise notice 'VOLATILE(%)', $1; return $1; end$$; + +select volatile(volatile_true()) from two; +select stable(stable_true()) from two; +select stable(volatile_true()) from two; +select volatile(stable_true()) from two; + +create function stable(bool, bool) returns bool STABLE language plpgsql as +$$begin raise notice 'STABLE(%, %)', $1, $2; return $1; end$$; +create function volatile(bool, bool) returns bool VOLATILE language plpgsql as +$$begin raise notice 'VOLATILE(%, %)', $1, $2; return $1; end$$; + +select stable(volatile_true(), volatile_false()) from two; +select stable(stable_true(), volatile_false()) from two; +select stable(stable_true(), stable_false()) from two; +select volatile(volatile_true(), volatile_false()) from two; +select volatile(stable_true(), volatile_false()) from two; +select volatile(stable_true(), stable_false()) from two; + +-- Default arguments +create function stable_def(a bool = stable_false(), b bool = volatile_true()) +returns bool STABLE language plpgsql as +$$begin raise notice 'STABLE(%, %)', $1, $2; return $1; end$$; + +select stable_def() from two; +select stable_def(b := stable_true()) from two; +select stable_def(volatile_false()) from two; + +-- Operators +create function stable_eq(bool, bool) returns bool STABLE language plpgsql as +$$begin raise notice 'STABLE % == %', $1, $2; return $1 = $2; end$$; +create function volatile_eq(bool, bool) returns bool VOLATILE language plpgsql as +$$begin raise notice 'VOLATILE % =%%= %', $1, $2; return $1 = $2; end$$; + +create operator == (procedure = stable_eq, leftarg=bool, rightarg=bool); +create operator =%= (procedure = volatile_eq, leftarg=bool, rightarg=bool); + +select volatile_true() == volatile_false() from two; +select stable_true() == volatile_false() from two; +select stable_true() == stable_false() from two; +select volatile_true() =%= volatile_false() from two; +select stable_true() =%= volatile_false() from two; +select stable_true() =%= stable_false() from two; + +select (volatile_true() or stable_true()) == true as b from two; + +-- Coalesce +create function stable_null() returns bool STABLE language plpgsql as +$$begin raise notice 'STABLE NULL'; return null; end$$; +create function volatile_null() returns bool VOLATILE language plpgsql as +$$begin raise notice 'VOLATILE NULL'; return null; end$$; + +select coalesce(stable_null(), stable_true()) from two; +select coalesce(stable_true(), volatile_null()) from two; +select coalesce(volatile_null(), stable_null(), volatile_true()) from two; + +-- Case/when +select case when stable_true() then 't' else volatile_false() end as b from two; +select case when volatile_true() then stable_true() else stable_false() end as b from two; +select case when i=1 then stable_true() else stable_false() end as b from two; +select case when i=1 then volatile_true() else volatile_false() end as b from two; + +select case when 't' then 't' else volatile_false() end == true as b from two; + +-- Coerce via I/O +select stable_true()::text::bool == true as b from two; +select volatile_true()::text::bool == true as b from two; + +-- IS DISTINCT FROM +select (stable_true() is not distinct from volatile_false()) as b from two; +select (stable_true() is distinct from stable_false()) == false as b from two; +select (volatile_true() is distinct from null) as b from two; + +-- IS NULL +select volatile_true() is null == false as b from two; +select stable_null() is not null == true as b from two; + +-- Boolean tests +select volatile_false() is true == true as b from two; +select stable_null() is not unknown == false as b from two; + +-- Field select -- not currently cached +create function stable_row(a out int, b out int) STABLE language plpgsql as +$$begin raise notice 'STABLE ROW'; a = 1; b = 2; end$$; + +select (stable_row()).a from two; + +-- WHERE clause +begin; +-- stable_true is evaluated twice due to planning estimates +declare stable_where cursor for select * from two where i > stable_true()::int; +fetch all from stable_where; +declare volatile_where cursor for select * from two where i = volatile_false()::int; +fetch all from volatile_where; +rollback; + +-- INSERT column default expressions +create table defaults ( + dummy int, + a bool default stable_true(), + b bool default volatile_true() +); +insert into defaults (dummy) values(0), (1); + +-- ALTER COLUMN TYPE USING +alter table defaults alter column a type bool using stable_false(); +alter table defaults alter column a type bool using volatile_false(); + +-- COPY FROM with default expressions +copy defaults (dummy) from stdin; +2 +3 +\. + +-- PL/pgSQL Simple expressions +-- Make sure we don't cache simple expressions -- these expressions are only +-- initialized once per transaction and then executed multiple times +create function stable_max() returns int STABLE language plpgsql as +$$begin return (select max(i) from two); end$$; + +create function simple() returns int STABLE language plpgsql as +$$begin return stable_max(); end$$; + +begin; +select simple(); +insert into two values(3); +select simple(); +rollback; + +-- The end +drop table defaults; +drop table two;