Re: delayed planning of unnamed statements - Mailing list pgsql-patches
| From | Bruce Momjian |
|---|---|
| Subject | Re: delayed planning of unnamed statements |
| Date | |
| Msg-id | 200406110116.i5B1GjZ22179@candle.pha.pa.us Whole thread Raw |
| In response to | delayed planning of unnamed statements (Oliver Jowett <oliver@opencloud.com>) |
| List | pgsql-patches |
Applied by Tom. Thanks. --------------------------------------------------------------------------- Oliver Jowett wrote: > 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); > [ text/x-python is unsupported, treating like TEXT/PLAIN ] > #!/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() > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
pgsql-patches by date: