Index: doc/src/sgml/ref/execute.sgml
===================================================================
RCS file: /var/lib/cvs/pgsql-server/doc/src/sgml/ref/execute.sgml,v
retrieving revision 1.1
diff -c -r1.1 execute.sgml
*** doc/src/sgml/ref/execute.sgml 27 Aug 2002 04:55:07 -0000 1.1
--- doc/src/sgml/ref/execute.sgml 12 Jan 2003 00:17:19 -0000
***************
*** 21,27 ****
2002-08-12
! EXECUTE plan_name [ (parameter [, ...] ) ]
--- 21,27 ----
2002-08-12
! EXECUTE plan_name [ (parameter [, ...] ) ] [ INTO [ TEMPORARY | TEMP ] table ]
***************
*** 46,57 ****
parameter
! The actual value of a parameter to the prepared query.
! This must be an expression yielding a value of a type
! compatible with
! the data-type specified for this parameter position in the
! PREPARE statement that created the prepared
! query.
--- 46,67 ----
parameter
! The actual value of a parameter to the prepared query. This
! must be an expression yielding a value of a type compatible
! with the data-type specified for this parameter position in
! the PREPARE statement that created the
! prepared query.
!
!
!
!
! table
!
!
! The name of the table in which to store the results of
! executing the query (if it is a SELECT). If
! no table is specified, the results are returned to the client
! (as normal).
***************
*** 83,88 ****
--- 93,105 ----
error is raised. Note that (unlike functions) prepared queries are
not overloaded based on the type or number of their parameters: the
name of a prepared query must be unique within a database session.
+
+
+
+ Like SELECT INTO, EXECUTE can
+ be used to store the results of executing the query in a table by
+ specifying an INTO clause. For more information on this behavior,
+ consult the reference for .
Index: doc/src/sgml/ref/explain.sgml
===================================================================
RCS file: /var/lib/cvs/pgsql-server/doc/src/sgml/ref/explain.sgml,v
retrieving revision 1.21
diff -c -r1.21 explain.sgml
*** doc/src/sgml/ref/explain.sgml 15 Nov 2002 03:11:18 -0000 1.21
--- doc/src/sgml/ref/explain.sgml 12 Jan 2003 00:21:30 -0000
***************
*** 22,28 ****
1999-07-20
! EXPLAIN [ ANALYZE ] [ VERBOSE ] query
--- 22,30 ----
1999-07-20
! EXPLAIN [ ANALYZE ] [ VERBOSE ] query
!
! EXPLAIN EXECUTE [ ANALYZE ] [ VERBOSE ] name [ ( parameter, ... ) ]
***************
*** 59,64 ****
--- 61,84 ----
+
+ name
+
+
+ The name of a prepared query, created previously using
+ PREPARE.
+
+
+
+
+ parameter
+
+
+ The parameters required by the prepared query, if any. Note that
+ these should only be specified if ANALYZE is also specified.
+
+
+
***************
*** 114,119 ****
--- 134,152 ----
+ There are two variants of the EXPLAIN
+ command. The first takes a literal query, which is parsed, rewritten,
+ and planned as normal. The second variant, EXPLAIN
+ EXECUTE, takes the name of a prepared query. In this
+ case, the previously generated prepared plan is loaded. If the
+ prepared query takes parameters and ANALYZE is specified, you also
+ need to specify the parameters to the prepared query (this is
+ necessary because ANALYZE actually executes the query). If the
+ ANALYZE option is not used, no parameters should be specified.
+
+
+
The most critical part of the display is the estimated query execution
cost, which is the planner's guess at how long it will take to run the
query (measured in units of disk page fetches). Actually two numbers
***************
*** 127,138 ****
costs to estimate which plan is really the cheapest.
The ANALYZE option causes the query to be actually executed, not only
planned. The total elapsed time expended within each plan node (in
milliseconds) and total number of rows it actually returned are added to
the display. This is useful for seeing whether the planner's estimates
! are close to reality.
--- 160,183 ----
costs to estimate which plan is really the cheapest.
+
+
+ In order to allow the PostgreSQL query
+ planner to make a reasonably informed decision when optimizing
+ queries, the ANALYZE statement should be used
+ to record statistics about the distribution of data within that
+ table. If you have not done this, the estimated costs and the
+ resulting query plan displayed by EXPLAIN are
+ unlikely to conform to the real properties of the query.
+
+
+
The ANALYZE option causes the query to be actually executed, not only
planned. The total elapsed time expended within each plan node (in
milliseconds) and total number of rows it actually returned are added to
the display. This is useful for seeing whether the planner's estimates
! are close to the real performance of the query.
***************
*** 231,239 ****
Note that the specific numbers shown, and even the selected query
strategy, may vary between PostgreSQL
! releases due to planner improvements.
--- 276,329 ----
+ Here is an example of using the ANALYZE option to verify whether
+ the information produced by EXPLAIN conforms
+ with real-world performance.
+
+
+ EXPLAIN ANALYZE SELECT sum(bar) FROM test WHERE id > 100 AND id < 200 GROUP BY foo;
+
+ QUERY PLAN
+ ----------------------------------------------------------------------------------------------------------------------
+ HashAggregate (cost=4.04..4.04 rows=1 width=8) (actual time=0.64..0.66 rows=7 loops=1)
+ -> Index Scan using test_pkey on test (cost=0.00..3.79 rows=50 width=8) (actual time=0.05..0.38 rows=99 loops=1)
+ Index Cond: ((id > 100) AND (id < 200))
+ Total runtime: 3.43 msec
+ (4 rows)
+
+
+
+
+
+
+ Here is an example of using the EXECUTE option to display the query
+ plan for a prepared query.
+
+
+ PREPARE query(int, int) AS SELECT sum(bar) FROM test WHERE id > $1
+ AND id < $2 GROUP BY foo;
+
+ EXPLAIN EXECUTE ANALYZE query(100, 200);
+
+ QUERY PLAN
+ -------------------------------------------------------------------------------------------------------------------------
+ HashAggregate (cost=39.53..39.53 rows=1 width=8) (actual time=0.66..0.67 rows=7 loops=1)
+ -> Index Scan using test_pkey on test (cost=0.00..32.97 rows=1311 width=8) (actual time=0.05..0.39 rows=99 loops=1)
+ Index Cond: ((id > $1) AND (id < $2))
+ Total runtime: 0.85 msec
+ (4 rows)
+
+
+
+
+
Note that the specific numbers shown, and even the selected query
strategy, may vary between PostgreSQL
! releases due to planner improvements. In addition, the algorithm
! used by ANALYZE to generate statistics is not
! completely deterministic; therefore, it is possible (although not
! common) for cost estimations to change between runs of
! ANALYZE.
Index: doc/src/sgml/ref/prepare.sgml
===================================================================
RCS file: /var/lib/cvs/pgsql-server/doc/src/sgml/ref/prepare.sgml,v
retrieving revision 1.1
diff -c -r1.1 prepare.sgml
*** doc/src/sgml/ref/prepare.sgml 27 Aug 2002 04:55:07 -0000 1.1
--- doc/src/sgml/ref/prepare.sgml 11 Jan 2003 19:54:27 -0000
***************
*** 156,162 ****
constant values in a query to make guesses about the likely
result of executing the query. Since this data is unavailable when
planning prepared queries with parameters, the chosen plan may be
! sub-optimal.
--- 156,164 ----
constant values in a query to make guesses about the likely
result of executing the query. Since this data is unavailable when
planning prepared queries with parameters, the chosen plan may be
! sub-optimal. In order to examine the query plan
! PostgreSQL has chosen for a prepared
! query, use EXPLAIN EXECUTE.
Index: src/backend/commands/explain.c
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/backend/commands/explain.c,v
retrieving revision 1.99
diff -c -r1.99 explain.c
*** src/backend/commands/explain.c 15 Dec 2002 16:17:38 -0000 1.99
--- src/backend/commands/explain.c 11 Jan 2003 22:56:40 -0000
***************
*** 119,128 ****
{
Plan *plan;
QueryDesc *queryDesc;
- ExplainState *es;
- StringInfo str;
- double totaltime = 0;
- struct timeval starttime;
/* planner will not cope with utility statements */
if (query->commandType == CMD_UTILITY)
--- 119,124 ----
***************
*** 134,139 ****
--- 130,138 ----
return;
}
+ /* We don't support DECLARE CURSOR here */
+ Assert(!query->isPortal);
+
/* plan the query */
plan = planner(query);
***************
*** 141,156 ****
if (plan == NULL)
return;
- /* We don't support DECLARE CURSOR here */
- Assert(!query->isPortal);
-
- gettimeofday(&starttime, NULL);
-
/* Create a QueryDesc requesting no output */
queryDesc = CreateQueryDesc(query, plan, None, NULL, NULL,
stmt->analyze);
! /* call ExecutorStart to prepare the plan for execution */
ExecutorStart(queryDesc);
/* Execute the plan for statistics if asked for */
--- 140,171 ----
if (plan == NULL)
return;
/* Create a QueryDesc requesting no output */
queryDesc = CreateQueryDesc(query, plan, None, NULL, NULL,
stmt->analyze);
! ExplainOnePlan(plan, queryDesc, stmt, tstate);
! }
!
! /*
! * ExplainOnePlan -
! * print out the execution plan for one query that has already been
! * planned. This is not static because it is also used in the
! * implementation of EXPLAIN EXECUTE (in prepare.c)
! */
! void
! ExplainOnePlan(Plan *plan, QueryDesc *queryDesc,
! ExplainStmt *stmt, TupOutputState *tstate)
! {
! ExplainState *es;
! StringInfo str;
! double totaltime = 0;
! struct timeval starttime;
!
! if (stmt->analyze)
! gettimeofday(&starttime, NULL);
!
! /* Create an executor for the plan */
ExecutorStart(queryDesc);
/* Execute the plan for statistics if asked for */
***************
*** 160,166 ****
ExecutorRun(queryDesc, ForwardScanDirection, 0L);
/* We can't clean up 'till we're done printing the stats... */
-
totaltime += elapsed_time(&starttime);
}
--- 175,180 ----
***************
*** 169,175 ****
es->printCost = true; /* default */
es->printNodes = stmt->verbose;
es->printAnalyze = stmt->analyze;
! es->rtable = query->rtable;
if (es->printNodes)
{
--- 183,189 ----
es->printCost = true; /* default */
es->printNodes = stmt->verbose;
es->printAnalyze = stmt->analyze;
! es->rtable = queryDesc->parsetree->rtable;
if (es->printNodes)
{
***************
*** 203,216 ****
* Close down the query and free resources. Include time for this
* in the total runtime.
*/
! gettimeofday(&starttime, NULL);
ExecutorEnd(queryDesc);
FreeQueryDesc(queryDesc);
CommandCounterIncrement();
! totaltime += elapsed_time(&starttime);
if (es->printCost)
{
--- 217,232 ----
* Close down the query and free resources. Include time for this
* in the total runtime.
*/
! if (stmt->analyze)
! gettimeofday(&starttime, NULL);
ExecutorEnd(queryDesc);
FreeQueryDesc(queryDesc);
CommandCounterIncrement();
! if (stmt->analyze)
! totaltime += elapsed_time(&starttime);
if (es->printCost)
{
Index: src/backend/commands/prepare.c
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/backend/commands/prepare.c,v
retrieving revision 1.12
diff -c -r1.12 prepare.c
*** src/backend/commands/prepare.c 15 Dec 2002 21:01:34 -0000 1.12
--- src/backend/commands/prepare.c 11 Jan 2003 23:11:26 -0000
***************
*** 12,17 ****
--- 12,19 ----
*/
#include "postgres.h"
+ #include "catalog/pg_type.h"
+ #include "commands/explain.h"
#include "commands/prepare.h"
#include "executor/executor.h"
#include "utils/guc.h"
***************
*** 47,55 ****
static void InitQueryHashTable(void);
static void StoreQuery(const char *stmt_name, List *query_list,
! List *plan_list, List *argtype_list);
static QueryHashEntry *FetchQuery(const char *plan_name);
!
/*
* Implements the 'PREPARE' utility statement.
--- 49,58 ----
static void InitQueryHashTable(void);
static void StoreQuery(const char *stmt_name, List *query_list,
! List *plan_list, List *argtype_list);
static QueryHashEntry *FetchQuery(const char *plan_name);
! static ParamListInfo EvaluateParams(EState *estate,
! List *params, List *argtypes);
/*
* Implements the 'PREPARE' utility statement.
***************
*** 94,100 ****
*query_list,
*plan_list;
ParamListInfo paramLI = NULL;
! EState *estate;
/* Look it up in the hash table */
entry = FetchQuery(stmt->name);
--- 97,103 ----
*query_list,
*plan_list;
ParamListInfo paramLI = NULL;
! EState *estate = NULL;
/* Look it up in the hash table */
entry = FetchQuery(stmt->name);
***************
*** 104,147 ****
Assert(length(query_list) == length(plan_list));
- /*
- * Need an EState to evaluate parameters; must not delete it till end
- * of query, in case parameters are pass-by-reference.
- */
- estate = CreateExecutorState();
/* Evaluate parameters, if any */
if (entry->argtype_list != NIL)
{
! int nargs = length(entry->argtype_list);
! int i = 0;
! List *exprstates;
!
! /* Parser should have caught this error, but check */
! if (nargs != length(stmt->params))
! elog(ERROR, "ExecuteQuery: wrong number of arguments");
!
! exprstates = (List *) ExecPrepareExpr((Expr *) stmt->params, estate);
!
! paramLI = (ParamListInfo)
! palloc0((nargs + 1) * sizeof(ParamListInfoData));
!
! foreach(l, exprstates)
! {
! ExprState *n = lfirst(l);
! bool isNull;
!
! paramLI[i].value = ExecEvalExprSwitchContext(n,
! GetPerTupleExprContext(estate),
! &isNull,
! NULL);
! paramLI[i].kind = PARAM_NUM;
! paramLI[i].id = i + 1;
! paramLI[i].isnull = isNull;
!
! i++;
! }
! paramLI[i].kind = PARAM_INVALID;
}
/* Execute each query */
--- 107,122 ----
Assert(length(query_list) == length(plan_list));
/* Evaluate parameters, if any */
if (entry->argtype_list != NIL)
{
! /*
! * Need an EState to evaluate parameters; must not delete it
! * till end of query, in case parameters are pass-by-reference.
! */
! estate = CreateExecutorState();
! paramLI = EvaluateParams(estate, stmt->params, entry->argtype_list);
}
/* Execute each query */
***************
*** 196,207 ****
CommandCounterIncrement();
}
! FreeExecutorState(estate);
/* No need to pfree other memory, MemoryContext will be reset */
}
/*
* Initialize query hash table upon first use.
*/
static void
--- 171,232 ----
CommandCounterIncrement();
}
! if (estate)
! FreeExecutorState(estate);
/* No need to pfree other memory, MemoryContext will be reset */
}
/*
+ * Evaluates a list of parameters, using the given executor state. It
+ * requires a list of the parameter values themselves, and a list of
+ * their types. It returns a filled-in ParamListInfo -- this can later
+ * be passed to CreateQueryDesc(), which allows the executor to make use
+ * of the parameters during query execution.
+ */
+ static ParamListInfo
+ EvaluateParams(EState *estate, List *params, List *argtypes)
+ {
+ ParamListInfo paramLI;
+ List *exprstates;
+ List *l;
+ int i = 0;
+ int nargs;
+
+ nargs = length(argtypes);
+
+ /* Parser should have caught this error, but check anyway */
+ if (length(params) != nargs)
+ elog(ERROR, "EvaluateParams: wrong number of arguments");
+
+ exprstates = (List *) ExecPrepareExpr((Expr *) params, estate);
+
+ paramLI = (ParamListInfo)
+ palloc0((nargs + 1) * sizeof(ParamListInfoData));
+
+ foreach(l, exprstates)
+ {
+ ExprState *n = lfirst(l);
+ bool isNull;
+
+ paramLI[i].value = ExecEvalExprSwitchContext(n,
+ GetPerTupleExprContext(estate),
+ &isNull,
+ NULL);
+ paramLI[i].kind = PARAM_NUM;
+ paramLI[i].id = i + 1;
+ paramLI[i].isnull = isNull;
+
+ i++;
+ }
+
+ paramLI[i].kind = PARAM_INVALID;
+
+ return paramLI;
+ }
+
+
+ /*
* Initialize query hash table upon first use.
*/
static void
***************
*** 229,236 ****
* to the hash entry, so the caller can dispose of their copy.
*/
static void
! StoreQuery(const char *stmt_name, List *query_list, List *plan_list,
! List *argtype_list)
{
QueryHashEntry *entry;
MemoryContext oldcxt,
--- 254,261 ----
* to the hash entry, so the caller can dispose of their copy.
*/
static void
! StoreQuery(const char *stmt_name, List *query_list,
! List *plan_list, List *argtype_list)
{
QueryHashEntry *entry;
MemoryContext oldcxt,
***************
*** 242,249 ****
if (!prepared_queries)
InitQueryHashTable();
! /* Check for pre-existing entry of same name */
! /* See notes in FetchQuery */
MemSet(key, 0, sizeof(key));
strncpy(key, stmt_name, sizeof(key));
--- 267,274 ----
if (!prepared_queries)
InitQueryHashTable();
! /* Check for pre-existing entry of same name: see notes in
! * FetchQuery */
MemSet(key, 0, sizeof(key));
strncpy(key, stmt_name, sizeof(key));
***************
*** 278,284 ****
HASH_ENTER,
&found);
! /* Shouldn't get a failure, nor duplicate entry */
if (!entry || found)
elog(ERROR, "Unable to store prepared statement \"%s\"!",
stmt_name);
--- 303,309 ----
HASH_ENTER,
&found);
! /* Shouldn't get a failure, nor a duplicate entry */
if (!entry || found)
elog(ERROR, "Unable to store prepared statement \"%s\"!",
stmt_name);
***************
*** 293,299 ****
}
/*
! * Lookup an existing query in the hash table.
*/
static QueryHashEntry *
FetchQuery(const char *plan_name)
--- 318,325 ----
}
/*
! * Lookup an existing query in the hash table. If the query does not
! * actually exist, an elog(ERROR) is thrown.
*/
static QueryHashEntry *
FetchQuery(const char *plan_name)
***************
*** 353,397 ****
void
DeallocateQuery(DeallocateStmt *stmt)
{
- char key[HASH_KEY_LEN];
QueryHashEntry *entry;
! /*
! * If the hash table hasn't been initialized, it can't be storing
! * anything, therefore it couldn't possibly store our plan.
! */
! if (!prepared_queries)
! elog(ERROR, "Prepared statement with name \"%s\" does not exist",
! stmt->name);
!
! /*
! * We can't just use the statement name as supplied by the user: the
! * hash package is picky enough that it needs to be NULL-padded out to
! * the appropriate length to work correctly.
! */
! MemSet(key, 0, sizeof(key));
! strncpy(key, stmt->name, sizeof(key));
!
! /*
! * First lookup the entry, so we can release all the subsidiary memory
! * it has allocated (when it's removed, hash_search() will return a
! * dangling pointer, so it needs to be done prior to HASH_REMOVE).
! * This requires an extra hash-table lookup, but DEALLOCATE isn't
! * exactly a performance bottleneck.
! */
! entry = (QueryHashEntry *) hash_search(prepared_queries,
! key,
! HASH_FIND,
! NULL);
!
! if (!entry)
! elog(ERROR, "Prepared statement with name \"%s\" does not exist",
! stmt->name);
/* Flush the context holding the subsidiary data */
Assert(MemoryContextIsValid(entry->context));
MemoryContextDelete(entry->context);
/* Now we can remove the hash table entry */
! hash_search(prepared_queries, key, HASH_REMOVE, NULL);
}
--- 379,471 ----
void
DeallocateQuery(DeallocateStmt *stmt)
{
QueryHashEntry *entry;
! entry = FetchQuery(stmt->name);
/* Flush the context holding the subsidiary data */
Assert(MemoryContextIsValid(entry->context));
MemoryContextDelete(entry->context);
/* Now we can remove the hash table entry */
! hash_search(prepared_queries, entry->key, HASH_REMOVE, NULL);
! }
!
! /*
! * Implements the EXPLAIN EXECUTE utility statement.
! */
! void
! ExplainPreparedQuery(ExplainStmt *stmt, CommandDest outputDest)
! {
! QueryHashEntry *entry;
! List *l;
! List *query_list;
! List *plan_list;
! TupleDesc tupdesc;
! TupOutputState *tstate;
! ParamListInfo paramLI = NULL;
! EState *estate = NULL;
!
! entry = FetchQuery(stmt->executeName);
!
! query_list = entry->query_list;
! plan_list = entry->plan_list;
!
! tupdesc = CreateTemplateTupleDesc(1, false);
! TupleDescInitEntry(tupdesc, (AttrNumber) 1, "QUERY PLAN",
! TEXTOID, -1, 0, false);
!
! /* Prepare for projection of tuples */
! tstate = begin_tup_output_tupdesc(outputDest, tupdesc);
!
! /* Evaluate parameters, if any */
! if (stmt->executeParams)
! {
! /* If params are specified, so must be ANALYZE */
! if (!stmt->analyze)
! elog(ERROR, "Parameters can only be specified with ANALYZE");
!
! /*
! * Need an EState to evaluate parameters; must not delete it
! * till end of query, in case parameters are pass-by-reference.
! */
! estate = CreateExecutorState();
! paramLI = EvaluateParams(estate, stmt->executeParams,
! entry->argtype_list);
! }
! else
! {
! /*
! * If params were not specified, check that the prepared query
! * wasn't expecting any. If ANALYZE was not specified, we won't
! * actually be executing the query, so it doesn't matter.
! */
! if (stmt->analyze && entry->argtype_list != NIL)
! elog(ERROR, "Prepared query \"%s\" must be called with %d parameters",
! stmt->executeName, length(entry->argtype_list));
! }
!
! foreach(l, query_list)
! {
! Query *query = (Query *) lfirst(l);
! Plan *plan = (Plan *) lfirst(plan_list);
! QueryDesc *queryDesc;
!
! /* Create a QueryDesc requesting no output */
! queryDesc = CreateQueryDesc(query, plan, None, NULL, paramLI,
! stmt->analyze);
!
! ExplainOnePlan(plan, queryDesc, stmt, tstate);
!
! /* put a blank line between plans */
! if (lnext(l) != NIL)
! do_text_output_oneline(tstate, "");
!
! plan_list = lnext(plan_list);
! }
!
! end_tup_output(tstate);
!
! if (estate)
! FreeExecutorState(estate);
}
Index: src/backend/parser/analyze.c
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/backend/parser/analyze.c,v
retrieving revision 1.259
diff -c -r1.259 analyze.c
*** src/backend/parser/analyze.c 2 Jan 2003 19:29:22 -0000 1.259
--- src/backend/parser/analyze.c 11 Jan 2003 23:02:35 -0000
***************
*** 97,102 ****
--- 97,104 ----
static Query *transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt);
static Query *transformPrepareStmt(ParseState *pstate, PrepareStmt *stmt);
static Query *transformExecuteStmt(ParseState *pstate, ExecuteStmt *stmt);
+ static Query *transformExplainStmt(ParseState *pstate, ExplainStmt *stmt,
+ List **extras_before, List **extras_after);
static Query *transformCreateStmt(ParseState *pstate, CreateStmt *stmt,
List **extras_before, List **extras_after);
static Query *transformAlterTableStmt(ParseState *pstate, AlterTableStmt *stmt,
***************
*** 119,126 ****
static bool relationHasPrimaryKey(Oid relationOid);
static void release_pstate_resources(ParseState *pstate);
static FromExpr *makeFromExpr(List *fromlist, Node *quals);
!
!
/*
* parse_analyze -
--- 121,128 ----
static bool relationHasPrimaryKey(Oid relationOid);
static void release_pstate_resources(ParseState *pstate);
static FromExpr *makeFromExpr(List *fromlist, Node *quals);
! static void transformExecuteParams(ParseState *pstate, List *params,
! List *paramtypes);
/*
* parse_analyze -
***************
*** 262,276 ****
break;
case T_ExplainStmt:
! {
! ExplainStmt *n = (ExplainStmt *) parseTree;
!
! result = makeNode(Query);
! result->commandType = CMD_UTILITY;
! n->query = transformStmt(pstate, (Node *) n->query,
! extras_before, extras_after);
! result->utilityStmt = (Node *) parseTree;
! }
break;
case T_AlterTableStmt:
--- 264,271 ----
break;
case T_ExplainStmt:
! result = transformExplainStmt(pstate, (ExplainStmt *) parseTree,
! extras_before, extras_after);
break;
case T_AlterTableStmt:
***************
*** 2366,2425 ****
static Query *
transformExecuteStmt(ParseState *pstate, ExecuteStmt *stmt)
{
! Query *result = makeNode(Query);
List *paramtypes;
result->commandType = CMD_UTILITY;
result->utilityStmt = (Node *) stmt;
! paramtypes = FetchQueryParams(stmt->name);
! if (stmt->params || paramtypes)
{
! int nparams = length(stmt->params);
! int nexpected = length(paramtypes);
! List *l;
! int i = 1;
! if (nparams != nexpected)
! elog(ERROR, "Wrong number of parameters, expected %d but got %d",
! nexpected, nparams);
! foreach(l, stmt->params)
! {
! Node *expr = lfirst(l);
! Oid expected_type_id,
! given_type_id;
!
! expr = transformExpr(pstate, expr);
!
! /* Cannot contain subselects or aggregates */
! if (contain_subplans(expr))
! elog(ERROR, "Cannot use subselects in EXECUTE parameters");
! if (contain_agg_clause(expr))
! elog(ERROR, "Cannot use aggregates in EXECUTE parameters");
!
! given_type_id = exprType(expr);
! expected_type_id = (Oid) lfirsti(paramtypes);
!
! expr = coerce_to_target_type(expr, given_type_id,
! expected_type_id, -1,
! COERCION_ASSIGNMENT,
! COERCE_IMPLICIT_CAST);
!
! if (expr == NULL)
! elog(ERROR, "Parameter $%d of type %s cannot be coerced into the expected type %s"
! "\n\tYou will need to rewrite or cast the expression",
! i,
! format_type_be(given_type_id),
! format_type_be(expected_type_id));
! lfirst(l) = expr;
! paramtypes = lnext(paramtypes);
! i++;
! }
}
return result;
}
--- 2361,2455 ----
static Query *
transformExecuteStmt(ParseState *pstate, ExecuteStmt *stmt)
{
! Query *result;
List *paramtypes;
+ paramtypes = FetchQueryParams(stmt->name);
+
+ if (stmt->params || paramtypes)
+ {
+ if (length(stmt->params) != length(paramtypes))
+ elog(ERROR, "Wrong number of parameters, expected %d but got %d",
+ length(paramtypes), length(stmt->params));
+
+ transformExecuteParams(pstate, stmt->params, paramtypes);
+ }
+
+ result = makeNode(Query);
result->commandType = CMD_UTILITY;
result->utilityStmt = (Node *) stmt;
! return result;
! }
! static void
! transformExecuteParams(ParseState *pstate, List *params, List *paramtypes)
! {
! List *l;
! int i = 1;
!
! foreach(l, params)
{
! Node *expr = lfirst(l);
! Oid expected_type_id,
! given_type_id;
! expr = transformExpr(pstate, expr);
! /* Cannot contain subselects or aggregates */
! if (contain_subplans(expr))
! elog(ERROR, "Cannot use subselects in EXECUTE parameters");
! if (contain_agg_clause(expr))
! elog(ERROR, "Cannot use aggregates in EXECUTE parameters");
! given_type_id = exprType(expr);
! expected_type_id = (Oid) lfirsti(paramtypes);
! expr = coerce_to_target_type(expr, given_type_id,
! expected_type_id, -1,
! COERCION_ASSIGNMENT,
! COERCE_IMPLICIT_CAST);
!
! if (expr == NULL)
! elog(ERROR, "Parameter $%d of type %s cannot be coerced into the expected type %s"
! "\n\tYou will need to rewrite or cast the expression",
! i,
! format_type_be(given_type_id),
! format_type_be(expected_type_id));
!
! lfirst(l) = expr;
!
! paramtypes = lnext(paramtypes);
! i++;
}
+ }
+
+ static Query *
+ transformExplainStmt(ParseState *pstate, ExplainStmt *stmt,
+ List **extras_before, List **extras_after)
+ {
+ Query *result;
+
+ if (stmt->query)
+ stmt->query = transformStmt(pstate, (Node *) stmt->query,
+ extras_before, extras_after);
+
+ if (stmt->executeParams)
+ {
+ List *paramtypes;
+
+ paramtypes = FetchQueryParams(stmt->executeName);
+
+ if (length(paramtypes) != length(stmt->executeParams))
+ elog(ERROR, "Wrong number of parameters, expected %d but got %d",
+ length(paramtypes), length(stmt->executeParams));
+
+ transformExecuteParams(pstate, stmt->executeParams, paramtypes);
+ }
+
+ result = makeNode(Query);
+ result->commandType = CMD_UTILITY;
+ result->utilityStmt = (Node *) stmt;
return result;
}
Index: src/backend/parser/gram.y
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/backend/parser/gram.y,v
retrieving revision 2.395
diff -c -r2.395 gram.y
*** src/backend/parser/gram.y 10 Jan 2003 22:03:27 -0000 2.395
--- src/backend/parser/gram.y 11 Jan 2003 21:42:10 -0000
***************
*** 240,246 ****
%type opt_interval
%type overlay_placing substr_from substr_for
! %type opt_instead opt_cursor
%type index_opt_unique opt_verbose opt_full
%type opt_freeze opt_default opt_recheck
%type opt_binary opt_oids copy_delimiter
--- 240,246 ----
%type opt_interval
%type overlay_placing substr_from substr_for
! %type opt_instead opt_cursor opt_analyze
%type index_opt_unique opt_verbose opt_full
%type opt_freeze opt_default opt_recheck
%type opt_binary opt_oids copy_delimiter
***************
*** 3958,3986 ****
/*****************************************************************************
*
* QUERY:
! * EXPLAIN query
! * EXPLAIN ANALYZE query
*
*****************************************************************************/
ExplainStmt:
! EXPLAIN opt_verbose OptimizableStmt
{
ExplainStmt *n = makeNode(ExplainStmt);
! n->verbose = $2;
! n->analyze = FALSE;
! n->query = (Query*)$3;
$$ = (Node *)n;
}
! | EXPLAIN analyze_keyword opt_verbose OptimizableStmt
{
ExplainStmt *n = makeNode(ExplainStmt);
! n->verbose = $3;
! n->analyze = TRUE;
! n->query = (Query*)$4;
$$ = (Node *)n;
}
;
/*****************************************************************************
*
--- 3958,3995 ----
/*****************************************************************************
*
* QUERY:
! * EXPLAIN [ANALYZE] query
! * EXPLAIN EXECUTE [ANALYZE] name
*
*****************************************************************************/
ExplainStmt:
! EXPLAIN opt_analyze opt_verbose OptimizableStmt
{
ExplainStmt *n = makeNode(ExplainStmt);
! n->analyze = $2;
! n->verbose = $3;
! n->query = (Query *)$4;
! n->executeName = NULL;
! n->executeParams = NULL;
$$ = (Node *)n;
}
! | EXPLAIN EXECUTE opt_analyze opt_verbose name execute_param_clause
{
ExplainStmt *n = makeNode(ExplainStmt);
! n->analyze = $3;
! n->verbose = $4;
! n->query = NULL;
! n->executeName = $5;
! n->executeParams = $6;
$$ = (Node *)n;
}
;
+
+ opt_analyze:
+ analyze_keyword { $$ = TRUE; }
+ | /* EMPTY */ { $$ = FALSE; }
+ ;
/*****************************************************************************
*
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/backend/tcop/utility.c,v
retrieving revision 1.189
diff -c -r1.189 utility.c
*** src/backend/tcop/utility.c 10 Jan 2003 22:03:28 -0000 1.189
--- src/backend/tcop/utility.c 11 Jan 2003 19:35:58 -0000
***************
*** 825,831 ****
break;
case T_ExplainStmt:
! ExplainQuery((ExplainStmt *) parsetree, dest);
break;
case T_VariableSetStmt:
--- 825,842 ----
break;
case T_ExplainStmt:
! {
! ExplainStmt *n = (ExplainStmt *) parsetree;
!
! /* One or the other must be specified, not both */
! Assert((n->query || n->executeName) &&
! !(n->query && n->executeName));
!
! if (n->query)
! ExplainQuery(n, dest);
! else if (n->executeName)
! ExplainPreparedQuery(n, dest);
! }
break;
case T_VariableSetStmt:
Index: src/include/commands/explain.h
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/include/commands/explain.h,v
retrieving revision 1.17
diff -c -r1.17 explain.h
*** src/include/commands/explain.h 20 Jun 2002 20:29:49 -0000 1.17
--- src/include/commands/explain.h 11 Jan 2003 22:56:27 -0000
***************
*** 13,21 ****
--- 13,26 ----
#ifndef EXPLAIN_H
#define EXPLAIN_H
+ #include "executor/executor.h"
#include "nodes/parsenodes.h"
+ #include "nodes/plannodes.h"
#include "tcop/dest.h"
extern void ExplainQuery(ExplainStmt *stmt, CommandDest dest);
+
+ extern void ExplainOnePlan(Plan *plan, QueryDesc *queryDesc,
+ ExplainStmt *stmt, TupOutputState *tstate);
#endif /* EXPLAIN_H */
Index: src/include/commands/prepare.h
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/include/commands/prepare.h,v
retrieving revision 1.2
diff -c -r1.2 prepare.h
*** src/include/commands/prepare.h 4 Sep 2002 20:31:42 -0000 1.2
--- src/include/commands/prepare.h 11 Jan 2003 22:55:57 -0000
***************
*** 17,29 ****
#include "nodes/parsenodes.h"
#include "tcop/dest.h"
-
extern void PrepareQuery(PrepareStmt *stmt);
-
extern void ExecuteQuery(ExecuteStmt *stmt, CommandDest outputDest);
-
extern void DeallocateQuery(DeallocateStmt *stmt);
-
extern List *FetchQueryParams(const char *plan_name);
#endif /* PREPARE_H */
--- 17,26 ----
#include "nodes/parsenodes.h"
#include "tcop/dest.h"
extern void PrepareQuery(PrepareStmt *stmt);
extern void ExecuteQuery(ExecuteStmt *stmt, CommandDest outputDest);
extern void DeallocateQuery(DeallocateStmt *stmt);
extern List *FetchQueryParams(const char *plan_name);
+ extern void ExplainPreparedQuery(ExplainStmt *stmt, CommandDest dest);
#endif /* PREPARE_H */
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/include/nodes/parsenodes.h,v
retrieving revision 1.225
diff -c -r1.225 parsenodes.h
*** src/include/nodes/parsenodes.h 6 Jan 2003 00:31:45 -0000 1.225
--- src/include/nodes/parsenodes.h 11 Jan 2003 21:51:28 -0000
***************
*** 1467,1473 ****
typedef struct ExplainStmt
{
NodeTag type;
! Query *query; /* the query */
bool verbose; /* print plan info */
bool analyze; /* get statistics by executing plan */
} ExplainStmt;
--- 1467,1475 ----
typedef struct ExplainStmt
{
NodeTag type;
! Query *query; /* the query, or NULL */
! char *executeName; /* the name of the prepared plan, or NULL */
! List *executeParams; /* Values to assign to parameters, or NIL */
bool verbose; /* print plan info */
bool analyze; /* get statistics by executing plan */
} ExplainStmt;