delayed planning of unnamed statements - Mailing list pgsql-patches
| From | Oliver Jowett |
|---|---|
| Subject | delayed planning of unnamed statements |
| Date | |
| Msg-id | 40B20EC1.9050702@opencloud.com Whole thread Raw |
| Responses |
Re: delayed planning of unnamed statements
|
| List | pgsql-patches |
Per discussion on -hackers (http://archives.postgresql.org/pgsql-hackers/2004-05/msg00990.php), here is a first cut at delaying query planning for unnamed statements until we have concrete parameters (i.e. at Bind). This passes 'make check' (not particularly informative since that doesn't exercise the extended query protocol path). It also passes the JDBC driver's regression tests when using a modified driver that uses the extended query protocol and parameterized queries. Also attached is a python script that talks the V3 protocol directly and does some simple tests of various bind/rebind cases with named and unnamed statements (beware, that code is very ad-hoc!). With these changes the JDBC driver gets performance similar to the unparameterized case when using the unnamed statement: > FE=> Parse(stmt=null,query="SELECT count(*) FROM test_big WHERE value = 2",oids={}) > FE=> Bind(stmt=null,portal=null) > FE=> Describe(portal=null) > FE=> Execute(portal=null,limit=0) > FE=> Sync > <=BE ParseComplete [null] > <=BE BindComplete [null] > <=BE RowDescription(1) > <=BE DataRow > <=BE CommandStatus(SELECT) > <=BE ReadyForQuery(I) > <<<<< END > Elapsed: 54ms > Result: 1 > FE=> Parse(stmt=S_1,query="SELECT count(*) FROM test_big WHERE value = $1",oids={23}) > FE=> Bind(stmt=S_1,portal=null,$1=<2>) > FE=> Describe(portal=null) > FE=> Execute(portal=null,limit=0) > FE=> Sync > <=BE ParseComplete [S_1] > <=BE BindComplete [null] > <=BE RowDescription(1) > <=BE DataRow > <=BE CommandStatus(SELECT) > <=BE ReadyForQuery(I) > <<<<< END > Elapsed: 1484ms > Result: 1 > FE=> Parse(stmt=null,query="SELECT count(*) FROM test_big WHERE value = $1",oids={23}) > FE=> Bind(stmt=null,portal=null,$1=<2>) > FE=> Describe(portal=null) > FE=> Execute(portal=null,limit=0) > FE=> Sync > <=BE ParseComplete [null] > <=BE BindComplete [null] > <=BE RowDescription(1) > <=BE DataRow > <=BE CommandStatus(SELECT) > <=BE ReadyForQuery(I) > <<<<< END > Elapsed: 65ms > Result: 1 -O Index: doc/src/sgml/protocol.sgml =================================================================== RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/protocol.sgml,v retrieving revision 1.51 diff -u -c -r1.51 protocol.sgml *** doc/src/sgml/protocol.sgml 21 Mar 2004 22:29:10 -0000 1.51 --- doc/src/sgml/protocol.sgml 24 May 2004 14:42:10 -0000 *************** *** 663,668 **** --- 663,688 ---- </para> <para> + Query planning of named prepared-statement objects occurs when the Parse + message is received. If a query will be repeatedly executed with + different parameters, it may be beneficial to send a single Parse message + containing a parameterized query, followed by multiple Bind + and Execute messages. This will avoid replanning the query on each + execution. + </para> + + <note> + <para> + Query plans generated from a parameterized query may be less + efficient than query plans generated from an equivalent query with actual + parameter values substituted. The query planner cannot make decisions + based on actual parameter values (for example, index selectivity) when + planning a parameterized query assigned to a named prepared-statement + object. + </para> + </note> + + <para> Once a prepared statement exists, it can be readied for execution using a Bind message. The Bind message gives the name of the source prepared statement (empty string denotes the unnamed prepared statement), the name *************** *** 674,679 **** --- 694,720 ---- by the query; the format can be specified overall, or per-column. The response is either BindComplete or ErrorResponse. </para> + + <para> + Query planning of the unnamed prepared-statement object occurs when the + first Bind message after a Parse message is received. The planner will + consider the actual values of any parameters provided in the Bind message + when planning the query. A Parse followed by Bind of the unnamed + prepared-statement object will produce the same query plan as for the + equivalent unparameterized query. + </para> + + <note> + <para> + When a second or subsequent Bind referencing the unnamed prepared- + statement object is received without an intervening Parse, the query is + not replanned. The parameter values used in the first Bind message may + produce a query plan that is only efficient for a subset of possible + parameter values. To force replanning of the query on each execution, send + a Parse message to replace the unnamed prepared-statement object before + each Bind. + </para> + </note> <note> <para> Index: src/backend/commands/explain.c =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/backend/commands/explain.c,v retrieving revision 1.120 diff -u -c -r1.120 explain.c *** src/backend/commands/explain.c 1 Apr 2004 21:28:44 -0000 1.120 --- src/backend/commands/explain.c 24 May 2004 14:42:10 -0000 *************** *** 176,182 **** } /* plan the query */ ! plan = planner(query, isCursor, cursorOptions); /* Create a QueryDesc requesting no output */ queryDesc = CreateQueryDesc(query, plan, None_Receiver, NULL, --- 176,182 ---- } /* plan the query */ ! plan = planner(query, isCursor, cursorOptions, NULL); /* Create a QueryDesc requesting no output */ queryDesc = CreateQueryDesc(query, plan, None_Receiver, NULL, Index: src/backend/commands/portalcmds.c =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/backend/commands/portalcmds.c,v retrieving revision 1.26 diff -u -c -r1.26 portalcmds.c *** src/backend/commands/portalcmds.c 21 Mar 2004 22:29:10 -0000 1.26 --- src/backend/commands/portalcmds.c 24 May 2004 14:42:10 -0000 *************** *** 84,90 **** errmsg("DECLARE CURSOR ... FOR UPDATE is not supported"), errdetail("Cursors must be READ ONLY."))); ! plan = planner(query, true, stmt->options); /* * Create a portal and copy the query and plan into its memory --- 84,90 ---- errmsg("DECLARE CURSOR ... FOR UPDATE is not supported"), errdetail("Cursors must be READ ONLY."))); ! plan = planner(query, true, stmt->options, NULL); /* * Create a portal and copy the query and plan into its memory Index: src/backend/commands/prepare.c =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/backend/commands/prepare.c,v retrieving revision 1.26 diff -u -c -r1.26 prepare.c *** src/backend/commands/prepare.c 22 Apr 2004 02:58:20 -0000 1.26 --- src/backend/commands/prepare.c 24 May 2004 14:42:10 -0000 *************** *** 91,97 **** query_list = QueryRewrite(stmt->query); /* Generate plans for queries. Snapshot is already set. */ ! plan_list = pg_plan_queries(query_list, false); /* Save the results. */ StorePreparedStatement(stmt->name, --- 91,97 ---- query_list = QueryRewrite(stmt->query); /* Generate plans for queries. Snapshot is already set. */ ! plan_list = pg_plan_queries(query_list, false, NULL); /* Save the results. */ StorePreparedStatement(stmt->name, Index: src/backend/executor/functions.c =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/backend/executor/functions.c,v retrieving revision 1.80 diff -u -c -r1.80 functions.c *** src/backend/executor/functions.c 2 Apr 2004 23:14:08 -0000 1.80 --- src/backend/executor/functions.c 24 May 2004 14:42:10 -0000 *************** *** 100,106 **** Plan *planTree; execution_state *newes; ! planTree = pg_plan_query(queryTree); newes = (execution_state *) palloc(sizeof(execution_state)); if (preves) --- 100,106 ---- Plan *planTree; execution_state *newes; ! planTree = pg_plan_query(queryTree, NULL); newes = (execution_state *) palloc(sizeof(execution_state)); if (preves) Index: src/backend/executor/spi.c =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/backend/executor/spi.c,v retrieving revision 1.113 diff -u -c -r1.113 spi.c *** src/backend/executor/spi.c 1 Apr 2004 21:28:44 -0000 1.113 --- src/backend/executor/spi.c 24 May 2004 14:42:10 -0000 *************** *** 1130,1136 **** QueryDesc *qdesc; DestReceiver *dest; ! planTree = pg_plan_query(queryTree); plan_list = lappend(plan_list, planTree); dest = CreateDestReceiver(queryTree->canSetTag ? SPI : None, NULL); --- 1130,1136 ---- QueryDesc *qdesc; DestReceiver *dest; ! planTree = pg_plan_query(queryTree, NULL); plan_list = lappend(plan_list, planTree); dest = CreateDestReceiver(queryTree->canSetTag ? SPI : None, NULL); Index: src/backend/optimizer/path/clausesel.c =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/backend/optimizer/path/clausesel.c,v retrieving revision 1.65 diff -u -c -r1.65 clausesel.c *** src/backend/optimizer/path/clausesel.c 10 May 2004 22:44:45 -0000 1.65 --- src/backend/optimizer/path/clausesel.c 24 May 2004 14:42:11 -0000 *************** *** 489,496 **** } else if (IsA(clause, Param)) { ! /* XXX any way to do better? */ ! s1 = 1.0; } else if (IsA(clause, Const)) { --- 489,504 ---- } else if (IsA(clause, Param)) { ! /* Try to collapse to Const. */ ! Node *collapsed_clause = collapse_parameters_to_const(clause); ! if (IsA(collapsed_clause, Const)) { ! /* bool constant is pretty easy... */ ! s1 = ((bool) ((Const *) collapsed_clause)->constvalue) ? 1.0 : 0.0; ! } else { ! /* Can't collapse to Const. */ ! /* XXX any way to do better? */ ! s1 = 1.0; ! } } else if (IsA(clause, Const)) { Index: src/backend/optimizer/path/indxpath.c =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/backend/optimizer/path/indxpath.c,v retrieving revision 1.158 diff -u -c -r1.158 indxpath.c *** src/backend/optimizer/path/indxpath.c 27 Mar 2004 00:24:28 -0000 1.158 --- src/backend/optimizer/path/indxpath.c 24 May 2004 14:42:11 -0000 *************** *** 1068,1073 **** --- 1068,1076 ---- rightop = get_rightop(predicate); if (rightop == NULL) return false; /* not a binary opclause */ + + rightop = collapse_parameters_to_const(rightop); + leftop = collapse_parameters_to_const(leftop); if (IsA(rightop, Const)) { pred_var = leftop; *************** *** 1091,1096 **** --- 1094,1102 ---- rightop = get_rightop((Expr *) clause); if (rightop == NULL) return false; /* not a binary opclause */ + + rightop = collapse_parameters_to_const(rightop); + leftop = collapse_parameters_to_const(leftop); if (IsA(rightop, Const)) { clause_var = leftop; *************** *** 1873,1878 **** --- 1879,1885 ---- expr_op = ((OpExpr *) clause)->opno; /* again, required for all current special ops: */ + rightop = collapse_parameters_to_const(rightop); if (!IsA(rightop, Const) || ((Const *) rightop)->constisnull) return false; *************** *** 2056,2062 **** Node *leftop = get_leftop(clause); Node *rightop = get_rightop(clause); Oid expr_op = ((OpExpr *) clause)->opno; ! Const *patt = (Const *) rightop; Const *prefix = NULL; Const *rest = NULL; Pattern_Prefix_Status pstatus; --- 2063,2069 ---- Node *leftop = get_leftop(clause); Node *rightop = get_rightop(clause); Oid expr_op = ((OpExpr *) clause)->opno; ! Const *patt = (Const *) collapse_parameters_to_const(rightop); Const *prefix = NULL; Const *rest = NULL; Pattern_Prefix_Status pstatus; Index: src/backend/optimizer/plan/createplan.c =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/backend/optimizer/plan/createplan.c,v retrieving revision 1.169 diff -u -c -r1.169 createplan.c *** src/backend/optimizer/plan/createplan.c 25 Apr 2004 18:23:56 -0000 1.169 --- src/backend/optimizer/plan/createplan.c 24 May 2004 14:42:11 -0000 *************** *** 2330,2335 **** --- 2330,2337 ---- * level, but if we are building a subquery then it's important to * report correct info to the outer planner. */ + if (limitOffset) + limitOffset = collapse_parameters_to_const(limitOffset); if (limitOffset && IsA(limitOffset, Const)) { Const *limito = (Const *) limitOffset; *************** *** 2348,2353 **** --- 2350,2357 ---- plan->plan_rows = 1; } } + if (limitCount) + limitCount = collapse_parameters_to_const(limitCount); if (limitCount && IsA(limitCount, Const)) { Const *limitc = (Const *) limitCount; Index: src/backend/optimizer/plan/planner.c =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/backend/optimizer/plan/planner.c,v retrieving revision 1.169 diff -u -c -r1.169 planner.c *** src/backend/optimizer/plan/planner.c 11 May 2004 02:21:37 -0000 1.169 --- src/backend/optimizer/plan/planner.c 24 May 2004 14:42:11 -0000 *************** *** 71,82 **** * *****************************************************************************/ Plan * ! planner(Query *parse, bool isCursor, int cursorOptions) { double tuple_fraction; Plan *result_plan; Index save_PlannerQueryLevel; List *save_PlannerParamList; /* * The planner can be called recursively (an example is when --- 71,83 ---- * *****************************************************************************/ Plan * ! planner(Query *parse, bool isCursor, int cursorOptions, ParamListInfo boundParams) { double tuple_fraction; Plan *result_plan; Index save_PlannerQueryLevel; List *save_PlannerParamList; + ParamListInfo save_PlannerBoundParamList; /* * The planner can be called recursively (an example is when *************** *** 93,102 **** --- 94,105 ---- */ save_PlannerQueryLevel = PlannerQueryLevel; save_PlannerParamList = PlannerParamList; + save_PlannerBoundParamList = PlannerBoundParamList; /* Initialize state for handling outer-level references and params */ PlannerQueryLevel = 0; /* will be 1 in top-level subquery_planner */ PlannerParamList = NIL; + PlannerBoundParamList = boundParams; /* Determine what fraction of the plan is likely to be scanned */ if (isCursor) *************** *** 139,144 **** --- 142,148 ---- /* restore state for outer planner, if any */ PlannerQueryLevel = save_PlannerQueryLevel; PlannerParamList = save_PlannerParamList; + PlannerBoundParamList = save_PlannerBoundParamList; return result_plan; } *************** *** 401,407 **** /* * Simplify constant expressions. */ ! expr = eval_const_expressions(expr); /* Expand SubLinks to SubPlans */ if (parse->hasSubLinks) --- 405,411 ---- /* * Simplify constant expressions. */ ! expr = eval_const_expressions(expr, NULL); /* Expand SubLinks to SubPlans */ if (parse->hasSubLinks) *************** *** 762,770 **** */ double limit_fraction = 0.0; ! if (IsA(parse->limitCount, Const)) { ! Const *limitc = (Const *) parse->limitCount; int32 count = DatumGetInt32(limitc->constvalue); /* --- 766,775 ---- */ double limit_fraction = 0.0; ! Node *limitCount = collapse_parameters_to_const(parse->limitCount); ! if (IsA(limitCount, Const)) { ! Const *limitc = (Const *) limitCount; int32 count = DatumGetInt32(limitc->constvalue); /* *************** *** 778,788 **** /* We must also consider the OFFSET, if present */ if (parse->limitOffset != NULL) { ! if (IsA(parse->limitOffset, Const)) { int32 offset; ! limitc = (Const *) parse->limitOffset; offset = DatumGetInt32(limitc->constvalue); if (!limitc->constisnull && offset > 0) limit_fraction += (double) offset; --- 783,794 ---- /* We must also consider the OFFSET, if present */ if (parse->limitOffset != NULL) { ! Node *limitOffset = collapse_parameters_to_const(parse->limitCount); ! if (IsA(limitOffset, Const)) { int32 offset; ! limitc = (Const *) limitOffset; offset = DatumGetInt32(limitc->constvalue); if (!limitc->constisnull && offset > 0) limit_fraction += (double) offset; Index: src/backend/optimizer/prep/prepunion.c =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/backend/optimizer/prep/prepunion.c,v retrieving revision 1.110 diff -u -c -r1.110 prepunion.c *** src/backend/optimizer/prep/prepunion.c 11 May 2004 22:43:55 -0000 1.110 --- src/backend/optimizer/prep/prepunion.c 24 May 2004 14:42:11 -0000 *************** *** 430,435 **** --- 430,436 ---- TargetEntry *inputtle = (TargetEntry *) lfirst(input_tlist); TargetEntry *reftle = (TargetEntry *) lfirst(refnames_tlist); int32 colTypmod; + Node *collapsed_expr; Assert(inputtle->resdom->resno == resno); Assert(reftle->resdom->resno == resno); *************** *** 449,456 **** * subquery-scan plans; we don't want phony constants appearing in * the output tlists of upper-level nodes! */ ! if (hack_constants && inputtle->expr && IsA(inputtle->expr, Const)) ! expr = (Node *) inputtle->expr; else expr = (Node *) makeVar(0, inputtle->resdom->resno, --- 450,459 ---- * subquery-scan plans; we don't want phony constants appearing in * the output tlists of upper-level nodes! */ ! if (hack_constants && inputtle->expr && ! NULL != (collapsed_expr = collapse_parameters_to_const((Node *) inputtle->expr)) && ! IsA(collapsed_expr, Const)) ! expr = collapsed_expr; else expr = (Node *) makeVar(0, inputtle->resdom->resno, Index: src/backend/optimizer/util/clauses.c =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/backend/optimizer/util/clauses.c,v retrieving revision 1.170 diff -u -c -r1.170 clauses.c *** src/backend/optimizer/util/clauses.c 10 May 2004 22:44:45 -0000 1.170 --- src/backend/optimizer/util/clauses.c 24 May 2004 14:42:12 -0000 *************** *** 28,37 **** --- 28,39 ---- #include "optimizer/clauses.h" #include "optimizer/cost.h" #include "optimizer/planmain.h" + #include "optimizer/planner.h" #include "optimizer/var.h" #include "parser/analyze.h" #include "parser/parse_clause.h" #include "parser/parse_expr.h" + #include "parser/parse_type.h" #include "tcop/tcopprot.h" #include "utils/acl.h" #include "utils/builtins.h" *************** *** 41,48 **** --- 43,61 ---- #include "utils/syscache.h" + ParamListInfo PlannerBoundParamList = NULL; /* to keep track of currently-bound parameter values */ + + + typedef struct + { + List *active_fns; + ParamListInfo params; + int nparams; + } eval_const_expressions_context; + typedef struct { + int nargs; List *args; int *usecounts; *************** *** 57,73 **** static bool contain_volatile_functions_walker(Node *node, void *context); static bool contain_nonstrict_functions_walker(Node *node, void *context); static bool set_coercionform_dontcare_walker(Node *node, void *context); ! static Node *eval_const_expressions_mutator(Node *node, List *active_fns); static List *simplify_or_arguments(List *args, bool *haveNull, bool *forceTrue); static List *simplify_and_arguments(List *args, bool *haveNull, bool *forceFalse); static Expr *simplify_function(Oid funcid, Oid result_type, List *args, ! bool allow_inline, List *active_fns); static Expr *evaluate_function(Oid funcid, Oid result_type, List *args, HeapTuple func_tuple); static Expr *inline_function(Oid funcid, Oid result_type, List *args, ! HeapTuple func_tuple, List *active_fns); static Node *substitute_actual_parameters(Node *expr, int nargs, List *args, int *usecounts); static Node *substitute_actual_parameters_mutator(Node *node, --- 70,86 ---- static bool contain_volatile_functions_walker(Node *node, void *context); static bool contain_nonstrict_functions_walker(Node *node, void *context); static bool set_coercionform_dontcare_walker(Node *node, void *context); ! static Node *eval_const_expressions_mutator(Node *node, eval_const_expressions_context *context); static List *simplify_or_arguments(List *args, bool *haveNull, bool *forceTrue); static List *simplify_and_arguments(List *args, bool *haveNull, bool *forceFalse); static Expr *simplify_function(Oid funcid, Oid result_type, List *args, ! bool allow_inline, eval_const_expressions_context *active_fns); static Expr *evaluate_function(Oid funcid, Oid result_type, List *args, HeapTuple func_tuple); static Expr *inline_function(Oid funcid, Oid result_type, List *args, ! HeapTuple func_tuple, eval_const_expressions_context *active_fns); static Node *substitute_actual_parameters(Node *expr, int nargs, List *args, int *usecounts); static Node *substitute_actual_parameters_mutator(Node *node, *************** *** 1062,1078 **** *-------------------- */ Node * ! eval_const_expressions(Node *node) { /* ! * The context for the mutator is a list of SQL functions being ! * recursively simplified, so we start with an empty list. */ ! return eval_const_expressions_mutator(node, NIL); } static Node * ! eval_const_expressions_mutator(Node *node, List *active_fns) { if (node == NULL) return NULL; --- 1075,1128 ---- *-------------------- */ Node * ! eval_const_expressions(Node *node, ParamListInfo params) { /* ! * The context for the mutator is the parameter list plus ! * a list of SQL functions being recursively simplified. ! * The function list is initially empty. */ ! int i; ! eval_const_expressions_context context; ! context.active_fns = NIL; ! context.params = params; ! ! if (params != NULL) { ! /* Count and check parameters. */ ! for (i = 0; params[i].kind != PARAM_INVALID; ++i) { ! if (params[i].id != (i + 1)) ! elog(ERROR, "param id mismatch: expected %d but was %d", i+1, params[i].id); ! } ! ! context.nparams = i; ! } else { ! context.nparams = 0; ! } ! ! return eval_const_expressions_mutator(node, &context); ! } ! ! /* ! * Helper for selectivity functions: apply a Param to Const replacement, ! * using parameters from PlannerBoundParamList, and return the new tree. Might ! * not copy the tree if it's obvious no change will happen. ! */ ! Node * ! collapse_parameters_to_const(Node *node) ! { ! /* Already a leaf? */ ! if (IsA(node, Const) || IsA(node, Var)) ! return node; ! ! /* No parameters? */ ! if (PlannerBoundParamList == NULL || PlannerBoundParamList[0].kind == PARAM_INVALID) ! return node; ! ! return eval_const_expressions(node, PlannerBoundParamList); } static Node * ! eval_const_expressions_mutator(Node *node, eval_const_expressions_context *context) { if (node == NULL) return NULL; *************** *** 1090,1103 **** */ args = (List *) expression_tree_mutator((Node *) expr->args, eval_const_expressions_mutator, ! (void *) active_fns); /* * Code for op/func reduction is pretty bulky, so split it out as * a separate function. */ simple = simplify_function(expr->funcid, expr->funcresulttype, args, ! true, active_fns); if (simple) /* successfully simplified it */ return (Node *) simple; --- 1140,1153 ---- */ args = (List *) expression_tree_mutator((Node *) expr->args, eval_const_expressions_mutator, ! (void *) context); /* * Code for op/func reduction is pretty bulky, so split it out as * a separate function. */ simple = simplify_function(expr->funcid, expr->funcresulttype, args, ! true, context); if (simple) /* successfully simplified it */ return (Node *) simple; *************** *** 1128,1134 **** */ args = (List *) expression_tree_mutator((Node *) expr->args, eval_const_expressions_mutator, ! (void *) active_fns); /* * Need to get OID of underlying function. Okay to scribble on --- 1178,1184 ---- */ 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 *************** *** 1141,1147 **** * a separate function. */ simple = simplify_function(expr->opfuncid, expr->opresulttype, args, ! true, active_fns); if (simple) /* successfully simplified it */ return (Node *) simple; --- 1191,1197 ---- * a separate function. */ simple = simplify_function(expr->opfuncid, expr->opresulttype, args, ! true, context); if (simple) /* successfully simplified it */ return (Node *) simple; *************** *** 1176,1182 **** */ args = (List *) expression_tree_mutator((Node *) expr->args, eval_const_expressions_mutator, ! (void *) active_fns); /* * We must do our own check for NULLs because DistinctExpr has --- 1226,1232 ---- */ args = (List *) expression_tree_mutator((Node *) expr->args, eval_const_expressions_mutator, ! (void *) context); /* * We must do our own check for NULLs because DistinctExpr has *************** *** 1220,1226 **** * as a separate function. */ simple = simplify_function(expr->opfuncid, expr->opresulttype, ! args, false, active_fns); if (simple) /* successfully simplified it */ { /* --- 1270,1276 ---- * as a separate function. */ simple = simplify_function(expr->opfuncid, expr->opresulttype, ! args, false, context); if (simple) /* successfully simplified it */ { /* *************** *** 1261,1267 **** */ args = (List *) expression_tree_mutator((Node *) expr->args, eval_const_expressions_mutator, ! (void *) active_fns); switch (expr->boolop) { --- 1311,1317 ---- */ args = (List *) expression_tree_mutator((Node *) expr->args, eval_const_expressions_mutator, ! (void *) context); switch (expr->boolop) { *************** *** 1354,1360 **** Node *arg; arg = eval_const_expressions_mutator((Node *) relabel->arg, ! active_fns); /* * If we find stacked RelabelTypes (eg, from foo :: int :: oid) we --- 1404,1410 ---- Node *arg; arg = eval_const_expressions_mutator((Node *) relabel->arg, ! context); /* * If we find stacked RelabelTypes (eg, from foo :: int :: oid) we *************** *** 1418,1424 **** /* Simplify the test expression, if any */ newarg = eval_const_expressions_mutator((Node *) caseexpr->arg, ! active_fns); /* Simplify the WHEN clauses */ FastListInit(&newargs); --- 1468,1474 ---- /* Simplify the test expression, if any */ newarg = eval_const_expressions_mutator((Node *) caseexpr->arg, ! context); /* Simplify the WHEN clauses */ FastListInit(&newargs); *************** *** 1428,1434 **** CaseWhen *casewhen = (CaseWhen *) expression_tree_mutator((Node *) lfirst(arg), eval_const_expressions_mutator, ! (void *) active_fns); Assert(IsA(casewhen, CaseWhen)); if (casewhen->expr == NULL || --- 1478,1484 ---- CaseWhen *casewhen = (CaseWhen *) expression_tree_mutator((Node *) lfirst(arg), eval_const_expressions_mutator, ! (void *) context); Assert(IsA(casewhen, CaseWhen)); if (casewhen->expr == NULL || *************** *** 1458,1464 **** /* Simplify the default result */ defresult = eval_const_expressions_mutator((Node *) caseexpr->defresult, ! active_fns); /* * If no non-FALSE alternatives, CASE reduces to the default --- 1508,1514 ---- /* Simplify the default result */ defresult = eval_const_expressions_mutator((Node *) caseexpr->defresult, ! context); /* * If no non-FALSE alternatives, CASE reduces to the default *************** *** 1488,1494 **** Node *e; e = eval_const_expressions_mutator((Node *) lfirst(element), ! active_fns); if (!IsA(e, Const)) all_const = false; FastAppend(&newelems, e); --- 1538,1544 ---- Node *e; e = eval_const_expressions_mutator((Node *) lfirst(element), ! context); if (!IsA(e, Const)) all_const = false; FastAppend(&newelems, e); *************** *** 1519,1525 **** Node *e; e = eval_const_expressions_mutator((Node *) lfirst(arg), ! active_fns); /* * We can remove null constants from the list. For a non-null --- 1569,1575 ---- Node *e; e = eval_const_expressions_mutator((Node *) lfirst(arg), ! context); /* * We can remove null constants from the list. For a non-null *************** *** 1555,1561 **** Node *arg; arg = eval_const_expressions_mutator((Node *) fselect->arg, ! active_fns); if (arg && IsA(arg, Var) && ((Var *) arg)->varattno == InvalidAttrNumber) { --- 1605,1611 ---- Node *arg; arg = eval_const_expressions_mutator((Node *) fselect->arg, ! context); if (arg && IsA(arg, Var) && ((Var *) arg)->varattno == InvalidAttrNumber) { *************** *** 1580,1585 **** --- 1630,1665 ---- newfselect->resulttypmod = fselect->resulttypmod; return (Node *) newfselect; } + if (IsA(node, Param)) + { + /* + * Attempt to substitute concrete parameter values in, if we have them. + */ + + Param *param = (Param *) node; + + if (param->paramkind == PARAM_NUM && context->params != NULL) { + /* + * Return a Constant in place of this Param. + */ + + Type type; + Const *replacement; + + if (param->paramid <= 0 || param->paramid > context->nparams) + elog(ERROR, "invalid paramid: %d", param->paramid); + + type = typeidType(param->paramtype); + replacement = makeConst(param->paramtype, + typeLen(type), + context->params[param->paramid - 1].value, + context->params[param->paramid - 1].isnull, + typeByVal(type)); + + ReleaseSysCache(type); + return (Node *) replacement; + } + } /* * For any node type not handled above, we recurse using *************** *** 1589,1595 **** * simplify constant expressions in its subscripts. */ return expression_tree_mutator(node, eval_const_expressions_mutator, ! (void *) active_fns); } /* --- 1669,1675 ---- * simplify constant expressions in its subscripts. */ return expression_tree_mutator(node, eval_const_expressions_mutator, ! (void *) context); } /* *************** *** 1727,1733 **** */ static Expr * simplify_function(Oid funcid, Oid result_type, List *args, ! bool allow_inline, List *active_fns) { HeapTuple func_tuple; Expr *newexpr; --- 1807,1813 ---- */ static Expr * simplify_function(Oid funcid, Oid result_type, List *args, ! bool allow_inline, eval_const_expressions_context *context) { HeapTuple func_tuple; Expr *newexpr; *************** *** 1750,1756 **** if (!newexpr && allow_inline) newexpr = inline_function(funcid, result_type, args, ! func_tuple, active_fns); ReleaseSysCache(func_tuple); --- 1830,1836 ---- if (!newexpr && allow_inline) newexpr = inline_function(funcid, result_type, args, ! func_tuple, context); ReleaseSysCache(func_tuple); *************** *** 1854,1860 **** */ static Expr * inline_function(Oid funcid, Oid result_type, List *args, ! HeapTuple func_tuple, List *active_fns) { Form_pg_proc funcform = (Form_pg_proc) GETSTRUCT(func_tuple); bool polymorphic = false; --- 1934,1940 ---- */ static Expr * inline_function(Oid funcid, Oid result_type, List *args, ! HeapTuple func_tuple, eval_const_expressions_context *context) { Form_pg_proc funcform = (Form_pg_proc) GETSTRUCT(func_tuple); bool polymorphic = false; *************** *** 1884,1890 **** return NULL; /* Check for recursive function, and give up trying to expand if so */ ! if (oidMember(funcid, active_fns)) return NULL; /* Check permission to call function (fail later, if not) */ --- 1964,1970 ---- return NULL; /* Check for recursive function, and give up trying to expand if so */ ! if (oidMember(funcid, context->active_fns)) return NULL; /* Check permission to call function (fail later, if not) */ *************** *** 2077,2084 **** * Recursively try to simplify the modified expression. Here we must * add the current function to the context list of active functions. */ ! newexpr = eval_const_expressions_mutator(newexpr, ! lconso(funcid, active_fns)); error_context_stack = sqlerrcontext.previous; --- 2157,2164 ---- * Recursively try to simplify the modified expression. Here we must * add the current function to the context list of active functions. */ ! context->active_fns = lconso(funcid, context->active_fns); ! newexpr = eval_const_expressions_mutator(newexpr, context); error_context_stack = sqlerrcontext.previous; Index: src/backend/tcop/postgres.c =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/backend/tcop/postgres.c,v retrieving revision 1.414 diff -u -c -r1.414 postgres.c *** src/backend/tcop/postgres.c 23 May 2004 03:50:45 -0000 1.414 --- src/backend/tcop/postgres.c 24 May 2004 14:42:12 -0000 *************** *** 631,637 **** /* Generate a plan for a single already-rewritten query. */ Plan * ! pg_plan_query(Query *querytree) { Plan *plan; --- 631,637 ---- /* Generate a plan for a single already-rewritten query. */ Plan * ! pg_plan_query(Query *querytree, ParamListInfo params) { Plan *plan; *************** *** 643,649 **** ResetUsage(); /* call the optimizer */ ! plan = planner(querytree, false, 0); if (log_planner_stats) ShowUsage("PLANNER STATISTICS"); --- 643,649 ---- ResetUsage(); /* call the optimizer */ ! plan = planner(querytree, false, 0, params); if (log_planner_stats) ShowUsage("PLANNER STATISTICS"); *************** *** 688,694 **** * statements in the rewriter's output.) */ List * ! pg_plan_queries(List *querytrees, bool needSnapshot) { List *plan_list = NIL; List *query_list; --- 688,694 ---- * statements in the rewriter's output.) */ List * ! pg_plan_queries(List *querytrees, bool needSnapshot, ParamListInfo params) { List *plan_list = NIL; List *query_list; *************** *** 710,716 **** SetQuerySnapshot(); needSnapshot = false; } ! plan = pg_plan_query(query); } plan_list = lappend(plan_list, plan); --- 710,716 ---- SetQuerySnapshot(); needSnapshot = false; } ! plan = pg_plan_query(query, params); } plan_list = lappend(plan_list, plan); *************** *** 868,874 **** querytree_list = pg_analyze_and_rewrite(parsetree, NULL, 0); ! plantree_list = pg_plan_queries(querytree_list, true); /* If we got a cancel signal in analysis or planning, quit */ CHECK_FOR_INTERRUPTS(); --- 868,874 ---- querytree_list = pg_analyze_and_rewrite(parsetree, NULL, 0); ! plantree_list = pg_plan_queries(querytree_list, true, NULL); /* If we got a cancel signal in analysis or planning, quit */ CHECK_FOR_INTERRUPTS(); *************** *** 1206,1212 **** querytree_list = pg_rewrite_queries(querytree_list); ! plantree_list = pg_plan_queries(querytree_list, true); } else { --- 1206,1217 ---- querytree_list = pg_rewrite_queries(querytree_list); ! /* If this is the unnamed statement and we have parameters, defer query planning until Bind. */ ! if (!is_named && numParams > 0) { ! plantree_list = NIL; ! } else { ! plantree_list = pg_plan_queries(querytree_list, true, NULL); ! } } else { *************** *** 1357,1368 **** else portal = CreatePortal(portal_name, false, false); ! PortalDefineQuery(portal, ! pstmt->query_string, ! pstmt->commandTag, ! pstmt->query_list, ! pstmt->plan_list, ! pstmt->context); /* * Fetch parameters, if any, and store in the portal's memory context. --- 1362,1368 ---- else portal = CreatePortal(portal_name, false, false); ! /* Defer portal query definition until we're sure planning is done. */ /* * Fetch parameters, if any, and store in the portal's memory context. *************** *** 1517,1524 **** pq_getmsgend(input_message); /* ! * Start portal execution. */ PortalStart(portal, params); /* --- 1517,1542 ---- pq_getmsgend(input_message); /* ! * If this is the unnamed statement, we may not have planned the ! * query yet. In that case, do the planning (in the query's ! * memory context) now that we have concrete parameter values. ! */ ! if (pstmt->plan_list == NIL && pstmt->query_list != NIL) { ! MemoryContext oldContext = MemoryContextSwitchTo(pstmt->context); ! pstmt->plan_list = pg_plan_queries(pstmt->query_list, true, params); ! MemoryContextSwitchTo(oldContext); ! } ! ! /* ! * Define portal and start execution. */ + PortalDefineQuery(portal, + pstmt->query_string, + pstmt->commandTag, + pstmt->query_list, + pstmt->plan_list, + pstmt->context); + PortalStart(portal, params); /* Index: src/backend/utils/adt/selfuncs.c =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/selfuncs.c,v retrieving revision 1.158 diff -u -c -r1.158 selfuncs.c *** src/backend/utils/adt/selfuncs.c 27 Feb 2004 21:44:34 -0000 1.158 --- src/backend/utils/adt/selfuncs.c 24 May 2004 14:42:12 -0000 *************** *** 242,247 **** --- 242,249 ---- &vardata, &other, &varonleft)) PG_RETURN_FLOAT8(DEFAULT_EQ_SEL); + other = collapse_parameters_to_const(other); + /* * If the something is a NULL constant, assume operator is strict and * return zero, ie, operator will never return TRUE. *************** *** 711,716 **** --- 713,720 ---- &vardata, &other, &varonleft)) PG_RETURN_FLOAT8(DEFAULT_INEQ_SEL); + other = collapse_parameters_to_const(other); + /* * Can't do anything useful if the something is not a constant, * either. *************** *** 787,792 **** --- 791,798 ---- &vardata, &other, &varonleft)) PG_RETURN_FLOAT8(DEFAULT_INEQ_SEL); + other = collapse_parameters_to_const(other); + /* * Can't do anything useful if the something is not a constant, * either. *************** *** 870,876 **** if (!get_restriction_variable(root, args, varRelid, &vardata, &other, &varonleft)) return DEFAULT_MATCH_SEL; ! if (!varonleft || !IsA(other, Const)) { ReleaseVariableStats(vardata); return DEFAULT_MATCH_SEL; --- 876,891 ---- if (!get_restriction_variable(root, args, varRelid, &vardata, &other, &varonleft)) return DEFAULT_MATCH_SEL; ! ! if (!varonleft) ! { ! ReleaseVariableStats(vardata); ! return DEFAULT_MATCH_SEL; ! } ! ! other = collapse_parameters_to_const(other); ! ! if (!IsA(other, Const)) { ReleaseVariableStats(vardata); return DEFAULT_MATCH_SEL; Index: src/backend/utils/cache/relcache.c =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/cache/relcache.c,v retrieving revision 1.202 diff -u -c -r1.202 relcache.c *** src/backend/utils/cache/relcache.c 8 May 2004 19:09:25 -0000 1.202 --- src/backend/utils/cache/relcache.c 24 May 2004 14:42:13 -0000 *************** *** 2680,2686 **** */ result = (List *) flatten_andors((Node *) result); ! result = (List *) eval_const_expressions((Node *) result); /* * Also mark any coercion format fields as "don't care", so that the --- 2680,2686 ---- */ result = (List *) flatten_andors((Node *) result); ! result = (List *) eval_const_expressions((Node *) result, NULL); /* * Also mark any coercion format fields as "don't care", so that the *************** *** 2754,2760 **** */ result = (List *) canonicalize_qual((Expr *) result); ! result = (List *) eval_const_expressions((Node *) result); /* * Also mark any coercion format fields as "don't care", so that the --- 2754,2760 ---- */ result = (List *) canonicalize_qual((Expr *) result); ! result = (List *) eval_const_expressions((Node *) result, NULL); /* * Also mark any coercion format fields as "don't care", so that the Index: src/include/optimizer/clauses.h =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/include/optimizer/clauses.h,v retrieving revision 1.73 diff -u -c -r1.73 clauses.h *** src/include/optimizer/clauses.h 14 Mar 2004 23:41:27 -0000 1.73 --- src/include/optimizer/clauses.h 24 May 2004 14:42:13 -0000 *************** *** 15,22 **** #define CLAUSES_H #include "nodes/relation.h" ! #define is_opclause(clause) ((clause) != NULL && IsA(clause, OpExpr)) #define is_funcclause(clause) ((clause) != NULL && IsA(clause, FuncExpr)) --- 15,23 ---- #define CLAUSES_H #include "nodes/relation.h" + #include "nodes/params.h" ! extern ParamListInfo PlannerBoundParamList; /* to keep track of externally-specified parameter values */ #define is_opclause(clause) ((clause) != NULL && IsA(clause, OpExpr)) #define is_funcclause(clause) ((clause) != NULL && IsA(clause, FuncExpr)) *************** *** 65,71 **** extern void set_coercionform_dontcare(Node *node); ! extern Node *eval_const_expressions(Node *node); extern bool expression_tree_walker(Node *node, bool (*walker) (), void *context); --- 66,74 ---- extern void set_coercionform_dontcare(Node *node); ! extern Node *eval_const_expressions(Node *node, ParamListInfo params); ! ! extern Node *collapse_parameters_to_const(Node *node); extern bool expression_tree_walker(Node *node, bool (*walker) (), void *context); Index: src/include/optimizer/planner.h =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/include/optimizer/planner.h,v retrieving revision 1.28 diff -u -c -r1.28 planner.h *** src/include/optimizer/planner.h 29 Nov 2003 22:41:07 -0000 1.28 --- src/include/optimizer/planner.h 24 May 2004 14:42:13 -0000 *************** *** 16,24 **** #include "nodes/parsenodes.h" #include "nodes/plannodes.h" ! ! extern Plan *planner(Query *parse, bool isCursor, int cursorOptions); extern Plan *subquery_planner(Query *parse, double tuple_fraction); #endif /* PLANNER_H */ --- 16,24 ---- #include "nodes/parsenodes.h" #include "nodes/plannodes.h" + #include "nodes/params.h" ! extern Plan *planner(Query *parse, bool isCursor, int cursorOptions, ParamListInfo extParams); extern Plan *subquery_planner(Query *parse, double tuple_fraction); #endif /* PLANNER_H */ Index: src/include/tcop/tcopprot.h =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/include/tcop/tcopprot.h,v retrieving revision 1.65 diff -u -c -r1.65 tcopprot.h *** src/include/tcop/tcopprot.h 11 Apr 2004 00:54:45 -0000 1.65 --- src/include/tcop/tcopprot.h 24 May 2004 14:42:13 -0000 *************** *** 24,29 **** --- 24,30 ---- #include "executor/execdesc.h" #include "tcop/dest.h" #include "utils/guc.h" + #include "nodes/params.h" extern DLLIMPORT sigjmp_buf Warn_restart; *************** *** 57,64 **** extern List *pg_analyze_and_rewrite(Node *parsetree, Oid *paramTypes, int numParams); extern List *pg_rewrite_queries(List *querytree_list); ! extern Plan *pg_plan_query(Query *querytree); ! extern List *pg_plan_queries(List *querytrees, bool needSnapshot); extern bool assign_max_stack_depth(int newval, bool doit, GucSource source); --- 58,65 ---- extern List *pg_analyze_and_rewrite(Node *parsetree, Oid *paramTypes, int numParams); extern List *pg_rewrite_queries(List *querytree_list); ! extern Plan *pg_plan_query(Query *querytree, ParamListInfo params); ! extern List *pg_plan_queries(List *querytrees, bool needSnapshot, ParamListInfo params); extern bool assign_max_stack_depth(int newval, bool doit, GucSource source); #!/usr/bin/env python import sys, socket, struct # V3 primitives def send_startup(conn, db, user): paramValues = 'user\0%s\0database\0%s\0\0' % (user, db) msgLen = 8 + len(paramValues) msg = struct.pack('>II', msgLen, 196608) + paramValues conn.send(msg) def send(conn, type, data): msg = type + struct.pack('>i', len(data)+4) + data conn.send(msg) def send_parse(conn, statement, query, oids): sys.stdout.write('<= Parse(%s,%s,%s)\n' % (repr(statement), repr(query), repr(oids))) msg = statement + '\0' msg += query + '\0' msg += struct.pack('>H', len(oids)) for oid in oids: msg += struct.pack('>I', oid) send(conn, 'P', msg) def send_bind(conn, portal, statement, params): sys.stdout.write('<= Bind(%s,%s,%s)\n' % (repr(portal), repr(statement), repr(params))) msg = portal + '\0' msg += statement + '\0' msg += struct.pack('>H', 0) # param format count msg += struct.pack('>H', len(params)) # param count for param in params: if param is None: msg += struct.pack('>i', -1) # NULL else: msg += struct.pack('>i', len(param) + 1) # param data length msg += param + '\0' msg += struct.pack('>H', 0) # result format count send(conn, 'B', msg) def send_execute(conn, portal, count): sys.stdout.write('<= Execute(%s,%d)\n' % (repr(portal), count)) msg = portal + '\0' msg += struct.pack('>i', 0) # max rows send(conn, 'E', msg) def send_sync(conn): sys.stdout.write('<= Sync\n') send(conn, 'S', '') # # Connection setup. # def connect(host, port, db, user): conn = socket.socket(socket.AF_INET, socket.SOCK_STREAM, 0) conn.connect( (host, port) ) send_startup(conn, db, user) type = conn.recv(1) if type != 'R': raise RuntimeError('bad startup type: ' + type) length, = struct.unpack('>i', conn.recv(4)) data = conn.recv(length-4) res, = struct.unpack('>i', data[:4]) if res != 0: raise RuntimeError("auth was needed but we don't do it") process_results(conn) return conn # # Normal connection processing # def make_log(type): return lambda conn, data: sys.stdout.write(' => ' + type + '\n') def make_server_response(type): def server_response(conn, data, type=type): sys.stdout.write(' => ' + type + '\n') i = 0 while i != -1 and data[i] != '\0': type = data[i] end = data.find('\0', i+1) if end == -1: value = data[i+1:] i = -1 else: value = data[i+1:end] i = end + 1 sys.stdout.write(' => ' + type + ': ' + value + '\n') return server_response def command_complete(conn, data): sys.stdout.write(' => CommandComplete: ' + data + '\n') def data_row(conn, data): sys.stdout.write(' => DataRow ') count, = struct.unpack('>H', data[:2]) o = 2 for i in xrange(count): length, = struct.unpack('>i', data[o:o+4]) o += 4 if length == -1: sys.stdout.write('NULL,') else: sys.stdout.write(data[o:o+length] + ',') o += length sys.stdout.write('\n') handlers = { 'K': make_log('BackendKeyData'), '2': make_log('BindComplete'), '3': make_log('CloseComplete'), 'C': command_complete, 'D': data_row, 'I': make_log('EmptyQuery'), 'E': make_server_response('ErrorResponse'), 'N': make_server_response('NoticeResponse'), 'S': make_log('ParameterStatus'), '1': make_log('ParseComplete'), 'Z': make_log('ReadyForQuery') } def process_results(conn): seen_sync = False type = None while type != 'Z': type = conn.recv(1) if not type: raise RuntimeError('EOF seen') length, = struct.unpack('>i', conn.recv(4)) data = conn.recv(length-4) if not handlers.has_key(type): raise RuntimeError('Unhandled message type ' + type) handlers[type](conn,data) statement_number = 1 def combos(conn, query, oids, params, params_2): global statement_number send_parse(conn, '', query, oids) send_parse(conn, '', query, oids) send_bind(conn, '', '', params) send_execute(conn, '', 0) send_sync(conn) process_results(conn) send_bind(conn, '', '', params_2) send_execute(conn, '', 0) send_sync(conn) process_results(conn) stmt = 's_%d' % statement_number statement_number += 1 send_parse(conn, stmt, query, oids) send_bind(conn, '', stmt, params) send_execute(conn, '', 0) send_sync(conn) process_results(conn) send_bind(conn, '', stmt, params_2) send_execute(conn, '', 0) send_sync(conn) process_results(conn) def tests(conn): # Empty query combos(conn=conn, query='', oids=(), params=(), params_2=()) # Simple SELECT combos(conn=conn, query='SELECT 1', oids=(), params=(), params_2=()) # Simple parameterized SELECT combos(conn=conn, query='SELECT $1', oids=(23,), params=('42',), params_2=('24',)) # Parameterized SELECT that calls a function that can be preevaluated combos(conn=conn, query='SELECT abs($1)', oids=(23,), params=('42',), params_2=('-24',)) # Parameterized SELECT that calls a function that can't be preevaluated combos(conn=conn, query='SELECT abs($1 + random())', oids=(23,), params=('42',), params_2=('-24',)) if __name__ == '__main__': host, port, db, user = sys.argv[1:] conn = connect(host, int(port), db, user) tests(conn) send(conn, 'X', '') conn.close()
pgsql-patches by date: