Re: delayed planning of unnamed statements

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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073