prepareable statements
The attached patch implements per-backend prepareable statements.
The syntax is:
PREPARE name_of_stmt(param_types) FROM <some query>;
EXECUTE name_of_stmt [INTO relation] [USING args];
DEALLOCATE [PREPARE] name_of_stmt;
I don't really like the 'FROM' keyword in PREPARE (I was planning to
use 'AS'), but that's what SQL92 specifies.
The PREPARE keyword in DEALLOCATE is ignored, for SQL92 compliance.
You can specify EXECUTE ... INTO, using the same syntax as SELECT
INTO, to store the result set from the EXECUTE in a relation.
The syntax is largely SQL92 compliant, but not totally. I'm not sure how
the SQL spec expects parameters to be set up in PREPARE, but I doubt
it's the same way I used. And the SQL92 spec for EXECUTE is functionally
similar, but uses a different syntax (EXECUTE ... USING INTO <rel>, I
think). If someone can decipher the spec on these two points and
can suggest what the proper syntax should be, let me know.
Parameters are fully supported -- for example:
PREPARE q1(text) FROM SELECT * FROM pg_class WHERE relname = $1;
EXECUTE q1 USING 'abc';
For simple queries such as the preceding one, using PREPARE followed
by EXECUTE is about 10% faster than continuosly using SELECT (when
executing 100,000 statements). When executing more complex statements
(such as the monstrous 12 table join used by the JDBC driver for
getting some meta-data), the performance improvement is more drastic
(IIRC it was about 100x in that case, when executing 75 statements).
I've included some regression tests for the work -- when/if the
patch is applied I'll write the documentation.
The patch stores queries in a hash table in TopMemoryContext. I
considered replacing the hash table with a linked list and
searching through that linearly, but I decided it wasn't worth
the bother (since the # of prepared statements is likely to be
very small, I would expect a linked list to outperform a hash
table in the common case). If you feel strongly one way or another,
let me know.
Also, I'm not entirely sure my approach to memory management is
correct. Each entry in the hash table stores its data in its
own MemoryContext, which is deleted when the statement is
DEALLOCATE'd. When actually running the prepared statement
through the executor, CurrentMemoryContext is used. Let me know
if there's a better way to do this.
This patch is based on Karel Zak's qCache patch for 7.0, but it's
completely new code (it's also a lot simpler, and doesn't bother
with caching plans in shared memory, as discussed on -hackers).
Cheers,
Neil
--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC
Attachments:
prep_stmt.patchtext/plain; charset=us-asciiDownload
Index: src/backend/commands/Makefile
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/commands/Makefile,v
retrieving revision 1.28
diff -c -r1.28 Makefile
*** src/backend/commands/Makefile 15 Apr 2002 05:22:03 -0000 1.28
--- src/backend/commands/Makefile 28 Jun 2002 17:20:06 -0000
***************
*** 13,20 ****
include $(top_builddir)/src/Makefile.global
OBJS = aggregatecmds.o analyze.o async.o cluster.o comment.o copy.o \
! dbcommands.o define.o explain.o functioncmds.o \
! indexcmds.o lockcmds.o operatorcmds.o portalcmds.o proclang.o \
schemacmds.o sequence.o tablecmds.o trigger.o typecmds.o user.o \
vacuum.o vacuumlazy.o variable.o view.o
--- 13,20 ----
include $(top_builddir)/src/Makefile.global
OBJS = aggregatecmds.o analyze.o async.o cluster.o comment.o copy.o \
! dbcommands.o define.o explain.o functioncmds.o indexcmds.o \
! lockcmds.o operatorcmds.o portalcmds.o prepare.o proclang.o \
schemacmds.o sequence.o tablecmds.o trigger.o typecmds.o user.o \
vacuum.o vacuumlazy.o variable.o view.o
Index: src/backend/commands/prepare.c
===================================================================
RCS file: src/backend/commands/prepare.c
diff -N src/backend/commands/prepare.c
*** /dev/null 1 Jan 1970 00:00:00 -0000
--- src/backend/commands/prepare.c 28 Jun 2002 17:20:06 -0000
***************
*** 0 ****
--- 1,387 ----
+ /*-------------------------------------------------------------------------
+ *
+ * prepare.c
+ * Prepareable SQL statements via PREPARE, EXECUTE and DEALLOCATE
+ *
+ * Copyright (c) 2002, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * $Header$
+ *
+ *-------------------------------------------------------------------------
+ */
+
+ #include "postgres.h"
+
+ #include "commands/prepare.h"
+ #include "executor/executor.h"
+ #include "utils/guc.h"
+ #include "optimizer/planner.h"
+ #include "optimizer/planmain.h"
+ #include "rewrite/rewriteHandler.h"
+ #include "tcop/pquery.h"
+ #include "tcop/tcopprot.h"
+ #include "tcop/utility.h"
+ #include "utils/hsearch.h"
+ #include "utils/memutils.h"
+
+ #define HASH_KEY_LEN NAMEDATALEN
+
+ typedef struct
+ {
+ char key[HASH_KEY_LEN];
+ QueryData *data;
+ } HashEntry;
+
+ /*
+ * The hash table in which prepared queries are stored. This is
+ * per-backend: query plans are not shared between backends.
+ * The keys for this hash table are the arguments to PREPARE
+ * and EXECUTE ("plan names"); the values are QueryData structs.
+ */
+ static HTAB *prepared_queries = NULL;
+
+ static void StoreQuery(const char *plan_name, QueryData *query_data);
+ static void InitHashTable(void);
+ static void RunQuery(QueryDesc *qdesc, EState *state);
+
+ void
+ PrepareQuery(PrepareStmt *stmt)
+ {
+ QueryData query_data;
+ List *plan_list = NIL;
+ List *query_list,
+ *query_list_item;
+
+ if (!stmt->name)
+ elog(ERROR, "No statement name given.");
+
+ if (stmt->query->commandType == CMD_UTILITY)
+ elog(ERROR, "Utility statements cannot be prepared.");
+
+ /* Rewrite the query. The result could be 0, 1, or many queries. */
+ query_list = QueryRewrite(stmt->query);
+
+ foreach(query_list_item, query_list)
+ {
+ Plan *plan;
+ Query *query = (Query *) lfirst(query_list_item);
+
+ /* We can't generate plans for utility statements. */
+ if (query->commandType == CMD_UTILITY)
+ continue;
+
+ /* Call the query planner to generate a plan. */
+ plan = planner(query);
+
+ plan_list = lappend(plan_list, plan);
+ }
+
+ query_data.plan_list = plan_list;
+ query_data.query_list = query_list;
+ query_data.nargs = stmt->nargs;
+ query_data.argtypes = stmt->argtoids;
+
+ StoreQuery(stmt->name, &query_data);
+ }
+
+ /*
+ * Store all the data pertaining to the query in the hash table using
+ * the specified key. A copy of the data is made before storage, so the
+ * caller can dispose of their copy.
+ */
+ static void
+ StoreQuery(const char *stmt_name, QueryData *query_data)
+ {
+ bool found;
+ HashEntry *entry;
+ QueryData *data;
+ MemoryContext oldcxt,
+ entrycxt;
+ char key[HASH_KEY_LEN];
+
+ MemSet(key, 0, sizeof(key));
+ strncpy(key, stmt_name, sizeof(key));
+
+ /* Initialize the hash table, if necessary */
+ if (!prepared_queries)
+ InitHashTable();
+
+ hash_search(prepared_queries, key, HASH_FIND, &found);
+
+ if (found)
+ elog(ERROR, "Prepared statement with name \"%s\" already exists.", stmt_name);
+
+ entrycxt = AllocSetContextCreate(TopMemoryContext,
+ stmt_name,
+ 1024,
+ 1024,
+ ALLOCSET_DEFAULT_MAXSIZE);
+
+ oldcxt = MemoryContextSwitchTo(entrycxt);
+
+ /*
+ * Create the hash table entry. We need to copy the data so that
+ * it is stored in the correct memory context.
+ */
+ data = (QueryData *) palloc(sizeof(QueryData));
+
+ data->context = entrycxt;
+ data->nargs = query_data->nargs;
+ data->plan_list = (List *) copyObject(query_data->plan_list);
+ data->query_list = (List *) copyObject(query_data->query_list);
+
+ if (data->nargs)
+ {
+ int mem_size = sizeof(Oid) * data->nargs;
+ data->argtypes = (Oid *) palloc(mem_size);
+ memcpy(data->argtypes, query_data->argtypes, mem_size);
+ }
+ else
+ data->argtypes = NULL;
+
+ /* Add entry to hash table */
+ MemoryContextSwitchTo(TopMemoryContext);
+
+ entry = (HashEntry *) hash_search(prepared_queries,
+ key,
+ HASH_ENTER,
+ NULL);
+
+ if (!entry)
+ elog(ERROR, "Unable to store prepared statement \"%s\"!", stmt_name);
+
+ data->key = entry->key;
+ entry->data = data;
+
+ MemoryContextSwitchTo(oldcxt);
+ }
+
+ static void
+ InitHashTable(void)
+ {
+ HASHCTL hash_ctl;
+
+ MemSet(&hash_ctl, 0, sizeof(hash_ctl));
+
+ hash_ctl.keysize = HASH_KEY_LEN;
+ hash_ctl.entrysize = sizeof(HashEntry);
+
+ prepared_queries = hash_create("Prepared Queries",
+ 8,
+ &hash_ctl,
+ HASH_ELEM);
+
+ if (!prepared_queries)
+ elog(ERROR, "InitHashTable(): unable to create hash table.");
+ }
+
+ /*
+ * Implements the 'EXECUTE' utility statement.
+ */
+ void
+ ExecuteQuery(ExecuteStmt *stmt, CommandDest outputDest)
+ {
+ ParamListInfo paramLI = NULL;
+ QueryData *qdata;
+ List *l,
+ *query_list,
+ *plan_list;
+
+ qdata = FetchQuery(stmt->name);
+
+ if (qdata->nargs)
+ {
+ List *l;
+ bool isNull;
+ int i = 0;
+
+ ExprContext *econtext = MakeExprContext(NULL, CurrentMemoryContext);
+
+ paramLI = (ParamListInfo) palloc(sizeof(ParamListInfoData) * qdata->nargs);
+
+ foreach (l, stmt->params)
+ {
+ Node *n = lfirst(l);
+ fix_opids(n);
+
+ paramLI[i].value = ExecEvalExprSwitchContext(n, econtext, &isNull, NULL);
+ paramLI[i].kind = PARAM_NUM;
+ paramLI[i].id = i + 1;
+ paramLI[i].isnull = isNull;
+
+ i++;
+ }
+
+ pfree(econtext);
+ }
+
+ query_list = qdata->query_list;
+ plan_list = qdata->plan_list;
+
+ Assert(length(query_list) == length(plan_list));
+
+ foreach(l, query_list)
+ {
+ CommandDest dest;
+ bool is_last_query;
+ Query *query = lfirst(l);
+ Plan *plan = lfirst(plan_list);
+ plan_list = lnext(plan_list);
+
+ is_last_query = (plan_list == NIL);
+
+ /*
+ * We only send the output to the final destination for the final
+ * query we execute.
+ */
+ dest = (is_last_query ? outputDest : None);
+
+ if (query->commandType == CMD_UTILITY)
+ ProcessUtility(query->utilityStmt, dest, NULL);
+ else
+ {
+ if (Show_executor_stats)
+ ResetUsage();
+
+ QueryDesc *qdesc = CreateQueryDesc(query, plan, dest, NULL);
+ EState *state = CreateExecutorState();
+
+ state->es_param_list_info = qdata->nargs ? paramLI : NULL;
+
+ if (stmt->into)
+ {
+ if (qdesc->operation != CMD_SELECT)
+ elog(ERROR, "INTO clause specified for non-SELECT query.");
+
+ query->into = stmt->into;
+ qdesc->dest = None;
+ }
+
+ RunQuery(qdesc, state);
+
+ if (Show_executor_stats)
+ ShowUsage("EXECUTOR STATISTICS");
+ }
+
+ /*
+ * If we're processing multiple queries, we need to increment
+ * the command counter between them. For the last query,
+ * there's no need to do this, it's done automatically.
+ */
+ if (! is_last_query)
+ CommandCounterIncrement();
+ }
+
+ if (paramLI)
+ pfree(paramLI);
+ pfree(qdata);
+ }
+
+ /*
+ * Fetch all data on the given prepared statement from the hash table in
+ * which it is stored.
+ */
+ QueryData *
+ FetchQuery(const char *plan_name)
+ {
+ QueryData *result;
+ HashEntry *entry;
+
+ /* See notes in DeallocateQuery() */
+ char key[HASH_KEY_LEN];
+
+ MemSet(key, 0, HASH_KEY_LEN);
+ strncpy(key, plan_name, HASH_KEY_LEN);
+
+ /*
+ * 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", plan_name);
+
+ entry = (HashEntry *) hash_search(prepared_queries,
+ key,
+ HASH_FIND,
+ NULL);
+
+ if (!entry)
+ elog(ERROR, "Prepared statement with name \"%s\" does not exist", plan_name);
+
+ result = palloc(sizeof(QueryData));
+
+ result->key = entry->data->key;
+ result->nargs = entry->data->nargs;
+ result->plan_list = (List *) copyObject(entry->data->plan_list);
+ result->query_list = (List *) copyObject(entry->data->query_list);
+ result->context = entry->data->context;
+
+ if (result->nargs)
+ {
+ int mem_size = sizeof(Oid) * result->nargs;
+ result->argtypes = (Oid *) palloc(mem_size);
+ memcpy(result->argtypes, entry->data->argtypes, mem_size);
+ }
+ else
+ result->argtypes = NULL;
+
+ return result;
+ }
+
+ /*
+ * Actually execute a prepared query. We can't use any of the existing
+ * routines in tcop/postgres.c because we need to manipulate part of the
+ * Executor (e.g. to inform it of parameters to execute) -- in any case,
+ * not much code is duplicated.
+ */
+ static void
+ RunQuery(QueryDesc *qdesc, EState *state)
+ {
+ TupleDesc tupdesc;
+
+ tupdesc = ExecutorStart(qdesc, state);
+
+ BeginCommand(NULL, qdesc->dest);
+
+ ExecutorRun(qdesc, state, state->es_direction, 0L);
+
+ ExecutorEnd(qdesc, state);
+ }
+
+ /*
+ * Implements the 'DEALLOCATE' utility statement: deletes the
+ * specified plan from storage.
+ */
+ void
+ DeallocateQuery(DeallocateStmt *stmt)
+ {
+ /*
+ * 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.
+ */
+ char key[HASH_KEY_LEN];
+ HashEntry *entry;
+
+ MemSet(key, 0, HASH_KEY_LEN);
+ strncpy(key, stmt->name, HASH_KEY_LEN);
+
+ /*
+ * First lookup the entry, so we can release some of the 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 = hash_search(prepared_queries, key, HASH_FIND, NULL);
+
+ if (!entry)
+ elog(ERROR, "No plan found with name \"%s\"", stmt->name);
+
+ if (entry->data && MemoryContextIsValid(entry->data->context))
+ MemoryContextDelete(entry->data->context);
+
+ /* Okay, now we can remove the hash table entry */
+ hash_search(prepared_queries, key, HASH_REMOVE, NULL);
+ }
Index: src/backend/parser/analyze.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/parser/analyze.c,v
retrieving revision 1.237
diff -c -r1.237 analyze.c
*** src/backend/parser/analyze.c 20 Jun 2002 20:29:31 -0000 1.237
--- src/backend/parser/analyze.c 28 Jun 2002 17:20:06 -0000
***************
*** 20,25 ****
--- 20,26 ----
#include "catalog/namespace.h"
#include "catalog/pg_index.h"
#include "catalog/pg_type.h"
+ #include "commands/prepare.h"
#include "nodes/makefuncs.h"
#include "parser/analyze.h"
#include "parser/gramparse.h"
***************
*** 43,49 ****
#include "mb/pg_wchar.h"
#endif
-
/* State shared by transformCreateSchemaStmt and its subroutines */
typedef struct
{
--- 44,49 ----
***************
*** 94,99 ****
--- 94,101 ----
static Query *transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt);
static Node *transformSetOperationTree(ParseState *pstate, SelectStmt *stmt);
static Query *transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt);
+ static Query *transformPrepareStmt(ParseState *pstate, PrepareStmt *stmt);
+ static Query *transformExecuteStmt(ParseState *pstate, ExecuteStmt *stmt);
static Query *transformCreateStmt(ParseState *pstate, CreateStmt *stmt,
List **extras_before, List **extras_after);
static Query *transformAlterTableStmt(ParseState *pstate, AlterTableStmt *stmt,
***************
*** 280,285 ****
--- 282,295 ----
extras_before, extras_after);
break;
+ case T_PrepareStmt:
+ result = transformPrepareStmt(pstate, (PrepareStmt *) parseTree);
+ break;
+
+ case T_ExecuteStmt:
+ result = transformExecuteStmt(pstate, (ExecuteStmt *) parseTree);
+ break;
+
/*
* Optimizable statements
*/
***************
*** 2651,2656 ****
--- 2661,2784 ----
qry->utilityStmt = (Node *) stmt;
return qry;
+ }
+
+ static Query *
+ transformPrepareStmt(ParseState *pstate, PrepareStmt *stmt)
+ {
+ Query *result = makeNode(Query);
+ List *extras_before = NIL,
+ *extras_after = NIL;
+ Oid *argtoids = NULL;
+
+ stmt->nargs = length(stmt->argtypes);
+
+ if (stmt->nargs)
+ {
+ List *l;
+ int i = 0;
+
+ argtoids = palloc(sizeof(*argtoids) * stmt->nargs);
+
+ foreach (l, stmt->argtypes)
+ {
+ TypeName *tn = lfirst(l);
+ Oid toid = typenameTypeId(tn);
+
+ if (!OidIsValid(toid))
+ {
+ elog(ERROR, "Argument $%d has invalid type \"%s\"",
+ i + 1, TypeNameToString(tn));
+ }
+
+ argtoids[i++] = toid;
+ }
+ }
+
+ /*
+ * We need to adjust the number of parameters expected by the
+ * rest of the system, so that $1, ... $n are parsed properly.
+ * This is somewhat of a hack; however, the existing interfaces
+ * only allow parameters to be specified when working with a
+ * raw query string (parser(), pg_parse_query(), etc.), which
+ * can't be used here.
+ */
+ parser_param_init(argtoids, stmt->nargs);
+
+ stmt->argtoids = argtoids;
+
+ stmt->query = transformStmt(pstate, (Node *) stmt->query,
+ &extras_before, &extras_after);
+
+ result->commandType = CMD_UTILITY;
+ result->utilityStmt = (Node *) stmt;
+
+ return result;
+ }
+
+ static Query *
+ transformExecuteStmt(ParseState *pstate, ExecuteStmt *stmt)
+ {
+ Query *result = makeNode(Query);
+ QueryData *qdata;
+
+ result->commandType = CMD_UTILITY;
+ result->utilityStmt = (Node *) stmt;
+
+ qdata = FetchQuery(stmt->name);
+
+ if (!qdata)
+ elog(ERROR, "No such prepared statement: %s", stmt->name);
+
+ if (stmt->params || qdata->nargs)
+ {
+ List *l;
+ Oid expected_type_id,
+ given_type_id;
+ int i = 0;
+ int nparams = length(stmt->params);
+
+ if (nparams != qdata->nargs)
+ {
+ elog(ERROR, "Wrong number of parameters, expected %d but got %d",
+ qdata->nargs, nparams);
+ }
+
+ foreach (l, stmt->params)
+ {
+ Node *expr = lfirst(l);
+
+ expr = transformExpr(pstate, expr);
+
+ given_type_id = exprType(expr);
+ expected_type_id = qdata->argtypes[i];
+
+ if (given_type_id != expected_type_id)
+ {
+ expr = CoerceTargetExpr(pstate,
+ expr,
+ given_type_id,
+ expected_type_id,
+ -1,
+ false);
+
+ if (!expr)
+ {
+ elog(ERROR, "Parameter $%d of type \"%s\" cannot be "
+ "coerced into the expected type (\"%s\").\n\t"
+ "You will need to rewrite or cast the expression.",
+ i + 1,
+ typeidToString(given_type_id),
+ typeidToString(expected_type_id));
+ }
+ }
+
+ lfirst(l) = expr;
+ i++;
+ }
+ }
+
+ return result;
}
/* exported so planner can check again after rewriting, query pullup, etc */
Index: src/backend/parser/gram.y
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.334
diff -c -r2.334 gram.y
*** src/backend/parser/gram.y 22 Jun 2002 02:04:45 -0000 2.334
--- src/backend/parser/gram.y 28 Jun 2002 17:20:06 -0000
***************
*** 152,158 ****
SelectStmt, TransactionStmt, TruncateStmt,
UnlistenStmt, UpdateStmt, VacuumStmt,
VariableResetStmt, VariableSetStmt, VariableShowStmt,
! ViewStmt, CheckPointStmt
%type <node> select_no_parens, select_with_parens, select_clause,
simple_select
--- 152,159 ----
SelectStmt, TransactionStmt, TruncateStmt,
UnlistenStmt, UpdateStmt, VacuumStmt,
VariableResetStmt, VariableSetStmt, VariableShowStmt,
! ViewStmt, CheckPointStmt, DeallocateStmt, ExecuteStmt,
! PrepareStmt
%type <node> select_no_parens, select_with_parens, select_clause,
simple_select
***************
*** 217,223 ****
target_list, update_target_list, insert_column_list,
insert_target_list, def_list, opt_indirection,
group_clause, TriggerFuncArgs, select_limit,
! opt_select_limit
%type <range> into_clause, OptTempTableName
--- 218,225 ----
target_list, update_target_list, insert_column_list,
insert_target_list, def_list, opt_indirection,
group_clause, TriggerFuncArgs, select_limit,
! opt_select_limit, execute_using, prepare_type_clause,
! prepare_type_list
%type <range> into_clause, OptTempTableName
***************
*** 333,339 ****
CREATEUSER, CROSS, CURRENT_DATE, CURRENT_TIME,
CURRENT_TIMESTAMP, CURRENT_USER, CURSOR, CYCLE,
! DATABASE, DAY_P, DEC, DECIMAL, DECLARE, DEFAULT,
DEFERRABLE, DEFERRED, DEFINER, DELETE_P, DELIMITER, DELIMITERS,
DESC, DISTINCT, DO, DOMAIN_P, DOUBLE, DROP,
--- 335,341 ----
CREATEUSER, CROSS, CURRENT_DATE, CURRENT_TIME,
CURRENT_TIMESTAMP, CURRENT_USER, CURSOR, CYCLE,
! DATABASE, DAY_P, DEALLOCATE, DEC, DECIMAL, DECLARE, DEFAULT,
DEFERRABLE, DEFERRED, DEFINER, DELETE_P, DELIMITER, DELIMITERS,
DESC, DISTINCT, DO, DOMAIN_P, DOUBLE, DROP,
***************
*** 369,375 ****
ORDER, OUT_P, OUTER_P, OVERLAPS, OVERLAY, OWNER,
PARTIAL, PASSWORD, PATH_P, PENDANT, PLACING, POSITION,
! PRECISION, PRIMARY, PRIOR, PRIVILEGES, PROCEDURE,
PROCEDURAL,
READ, REAL, REFERENCES, REINDEX, RELATIVE, RENAME, REPLACE,
--- 371,377 ----
ORDER, OUT_P, OUTER_P, OVERLAPS, OVERLAY, OWNER,
PARTIAL, PASSWORD, PATH_P, PENDANT, PLACING, POSITION,
! PRECISION, PREPARE, PRIMARY, PRIOR, PRIVILEGES, PROCEDURE,
PROCEDURAL,
READ, REAL, REFERENCES, REINDEX, RELATIVE, RENAME, REPLACE,
***************
*** 492,503 ****
--- 494,507 ----
| DropSchemaStmt
| TruncateStmt
| CommentStmt
+ | DeallocateStmt
| DropGroupStmt
| DropPLangStmt
| DropAssertStmt
| DropTrigStmt
| DropRuleStmt
| DropUserStmt
+ | ExecuteStmt
| ExplainStmt
| FetchStmt
| GrantStmt
***************
*** 526,531 ****
--- 530,536 ----
| VariableResetStmt
| ConstraintsSetStmt
| CheckPointStmt
+ | PrepareStmt
| /*EMPTY*/
{ $$ = (Node *)NULL; }
;
***************
*** 3803,3808 ****
--- 3808,3882 ----
}
;
+ /*****************************************************************************
+ *
+ * QUERY:
+ * PREPARE <plan_name> FROM <query>
+ *
+ *****************************************************************************/
+
+ PrepareStmt: PREPARE name prepare_type_clause FROM OptimizableStmt
+ {
+ PrepareStmt *n = makeNode(PrepareStmt);
+ n->name = $2;
+ n->argtypes = $3;
+ n->query = (Query *) $5;
+ $$ = (Node *) n;
+ }
+ ;
+
+ prepare_type_clause:
+ '(' prepare_type_list ')' { $$ = $2; }
+ | /* EMPTY */ { $$ = NIL; }
+ ;
+
+ prepare_type_list:
+ Typename { $$ = makeList1($1); }
+ | prepare_type_list ',' Typename
+ { $$ = lappend($1, $3); }
+ | /* EMPTY */ { $$ = NIL; }
+ ;
+
+ /*****************************************************************************
+ *
+ * QUERY:
+ * EXECUTE <plan_name> [INTO ...] [USING expr, ...]
+ *
+ *****************************************************************************/
+
+ ExecuteStmt: EXECUTE name into_clause execute_using
+ {
+ ExecuteStmt *n = makeNode(ExecuteStmt);
+ n->name = $2;
+ n->into = $3;
+ n->params = $4;
+ $$ = (Node *) n;
+ }
+ ;
+
+ execute_using: USING expr_list { $$ = $2; }
+ | /* EMPTY */ { $$ = NULL; }
+ ;
+
+ /*****************************************************************************
+ *
+ * QUERY:
+ * DEALLOCATE <plan_name>
+ *
+ *****************************************************************************/
+
+ DeallocateStmt: DEALLOCATE opt_prepare name
+ {
+ DeallocateStmt *n = makeNode(DeallocateStmt);
+ n->name = $3;
+ $$ = (Node *) n;
+ }
+ ;
+
+ /* Ignored, for SQL92 spec compliance */
+ opt_prepare: PREPARE {}
+ | /* EMPTY */ {}
+ ;
/*****************************************************************************
* *
***************
*** 7176,7181 ****
--- 7250,7266 ----
* Keep enough information around to fill out the type of param nodes
* used in postquel functions
*/
+ parser_param_init(typev, nargs);
+ }
+
+ /*
+ * Do the initialization required for processing parameters. This
+ * is also called by transformPrepareStmt() to update the parser's
+ * view of the parameters in a prepared statement.
+ */
+ void
+ parser_param_init(Oid *typev, int nargs)
+ {
param_type_info = typev;
pfunc_num_args = nargs;
}
Index: src/backend/parser/keywords.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/parser/keywords.c,v
retrieving revision 1.117
diff -c -r1.117 keywords.c
*** src/backend/parser/keywords.c 22 Jun 2002 02:04:45 -0000 1.117
--- src/backend/parser/keywords.c 28 Jun 2002 17:20:06 -0000
***************
*** 91,96 ****
--- 91,97 ----
{"cycle", CYCLE},
{"database", DATABASE},
{"day", DAY_P},
+ {"deallocate", DEALLOCATE},
{"dec", DEC},
{"decimal", DECIMAL},
{"declare", DECLARE},
***************
*** 225,230 ****
--- 226,232 ----
{"placing", PLACING},
{"position", POSITION},
{"precision", PRECISION},
+ {"prepare", PREPARE},
{"primary", PRIMARY},
{"prior", PRIOR},
{"privileges", PRIVILEGES},
Index: src/backend/parser/parse_type.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/parser/parse_type.c,v
retrieving revision 1.43
diff -c -r1.43 parse_type.c
*** src/backend/parser/parse_type.c 20 Jun 2002 20:29:33 -0000 1.43
--- src/backend/parser/parse_type.c 28 Jun 2002 17:20:06 -0000
***************
*** 277,282 ****
--- 277,295 ----
return (Type) tup;
}
+ /* given a Type structure, return the name of the type in string form */
+ char *
+ typeidToString(Oid id)
+ {
+ Type type = typeidType(id);
+
+ char *name = typeTypeName(type);
+
+ ReleaseSysCache((HeapTuple) type);
+
+ return name;
+ }
+
/* given type (as type struct), return the type OID */
Oid
typeTypeId(Type tp)
Index: src/backend/tcop/postgres.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/tcop/postgres.c,v
retrieving revision 1.268
diff -c -r1.268 postgres.c
*** src/backend/tcop/postgres.c 20 Jun 2002 20:29:36 -0000 1.268
--- src/backend/tcop/postgres.c 28 Jun 2002 17:20:06 -0000
***************
*** 2426,2431 ****
--- 2426,2443 ----
tag = "REINDEX";
break;
+ case T_ExecuteStmt:
+ tag = "EXECUTE";
+ break;
+
+ case T_PrepareStmt:
+ tag = "PREPARE";
+ break;
+
+ case T_DeallocateStmt:
+ tag = "DEALLOCATE";
+ break;
+
default:
elog(LOG, "CreateCommandTag: unknown parse node type %d",
nodeTag(parsetree));
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/tcop/utility.c,v
retrieving revision 1.159
diff -c -r1.159 utility.c
*** src/backend/tcop/utility.c 20 Jun 2002 20:29:36 -0000 1.159
--- src/backend/tcop/utility.c 28 Jun 2002 17:20:06 -0000
***************
*** 29,34 ****
--- 29,35 ----
#include "commands/explain.h"
#include "commands/lockcmds.h"
#include "commands/portalcmds.h"
+ #include "commands/prepare.h"
#include "commands/proclang.h"
#include "commands/schemacmds.h"
#include "commands/sequence.h"
***************
*** 351,356 ****
--- 352,375 ----
SetQuerySnapshot();
DoCopy(stmt);
+ }
+ break;
+
+ case T_PrepareStmt:
+ {
+ PrepareQuery((PrepareStmt *) parsetree);
+ }
+ break;
+
+ case T_ExecuteStmt:
+ {
+ ExecuteQuery((ExecuteStmt *) parsetree, dest);
+ }
+ break;
+
+ case T_DeallocateStmt:
+ {
+ DeallocateQuery((DeallocateStmt *) parsetree);
}
break;
Index: src/include/commands/prepare.h
===================================================================
RCS file: src/include/commands/prepare.h
diff -N src/include/commands/prepare.h
*** /dev/null 1 Jan 1970 00:00:00 -0000
--- src/include/commands/prepare.h 28 Jun 2002 17:20:06 -0000
***************
*** 0 ****
--- 1,26 ----
+ #ifndef _PREPARE_H
+ #define _PREPARE_H
+
+ #include "nodes/parsenodes.h"
+ #include "tcop/dest.h"
+
+ /* All the data we need to execute a stored query */
+ typedef struct
+ {
+ char *key;
+ List *plan_list;
+ List *query_list;
+ int nargs;
+ Oid *argtypes;
+ MemoryContext context;
+ } QueryData;
+
+ extern void PrepareQuery(PrepareStmt *stmt);
+
+ extern void ExecuteQuery(ExecuteStmt *stmt, CommandDest outputDest);
+
+ extern void DeallocateQuery(DeallocateStmt *stmt);
+
+ extern QueryData *FetchQuery(const char *plan_name);
+
+ #endif
Index: src/include/nodes/nodes.h
===================================================================
RCS file: /var/lib/cvs/pgsql/src/include/nodes/nodes.h,v
retrieving revision 1.109
diff -c -r1.109 nodes.h
*** src/include/nodes/nodes.h 20 Jun 2002 20:29:51 -0000 1.109
--- src/include/nodes/nodes.h 28 Jun 2002 17:20:06 -0000
***************
*** 198,203 ****
--- 198,206 ----
T_CreateSchemaStmt,
T_AlterDatabaseSetStmt,
T_AlterUserSetStmt,
+ T_DeallocateStmt,
+ T_ExecuteStmt,
+ T_PrepareStmt,
T_A_Expr = 700,
T_ColumnRef,
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /var/lib/cvs/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.182
diff -c -r1.182 parsenodes.h
*** src/include/nodes/parsenodes.h 20 Jun 2002 20:29:51 -0000 1.182
--- src/include/nodes/parsenodes.h 28 Jun 2002 17:20:06 -0000
***************
*** 1494,1497 ****
--- 1494,1536 ----
bool all;
} ReindexStmt;
+ /* ----------------------
+ * PREPARE Statement
+ * ----------------------
+ */
+ typedef struct PrepareStmt
+ {
+ NodeTag type;
+ char *name; /* Name of plan, arbitrary */
+ int nargs; /* # of parameters */
+ List *argtypes; /* Types of parameters (TypeNames) */
+ Oid *argtoids; /* The OIDs of the param types */
+ Query *query; /* The query itself */
+ } PrepareStmt;
+
+
+ /* ----------------------
+ * EXECUTE Statement
+ * ----------------------
+ */
+
+ typedef struct ExecuteStmt
+ {
+ NodeTag type;
+ char *name; /* The name of the plan to execute */
+ RangeVar *into; /* The relation to store the results in */
+ List *params; /* Values of parameters */
+ } ExecuteStmt;
+
+
+ /* ----------------------
+ * DEALLOCATE Statement
+ * ----------------------
+ */
+ typedef struct DeallocateStmt
+ {
+ NodeTag type;
+ char *name; /* The name of the plan to remove */
+ } DeallocateStmt;
+
#endif /* PARSENODES_H */
Index: src/include/parser/gramparse.h
===================================================================
RCS file: /var/lib/cvs/pgsql/src/include/parser/gramparse.h,v
retrieving revision 1.23
diff -c -r1.23 gramparse.h
*** src/include/parser/gramparse.h 20 Jun 2002 20:29:51 -0000 1.23
--- src/include/parser/gramparse.h 28 Jun 2002 17:20:06 -0000
***************
*** 29,34 ****
--- 29,35 ----
/* from gram.y */
extern void parser_init(Oid *typev, int nargs);
+ extern void parser_param_init(Oid *typev, int nargs);
extern Oid param_type(int t);
extern int yyparse(void);
extern List *SystemFuncName(char *name);
Index: src/include/parser/parse_type.h
===================================================================
RCS file: /var/lib/cvs/pgsql/src/include/parser/parse_type.h,v
retrieving revision 1.23
diff -c -r1.23 parse_type.h
*** src/include/parser/parse_type.h 20 Jun 2002 20:29:52 -0000 1.23
--- src/include/parser/parse_type.h 28 Jun 2002 17:20:06 -0000
***************
*** 27,32 ****
--- 27,33 ----
extern bool typeidIsValid(Oid id);
extern Type typeidType(Oid id);
+ extern char *typeidToString(Oid id);
extern Oid typeTypeId(Type tp);
extern int16 typeLen(Type t);
Index: src/test/regress/parallel_schedule
===================================================================
RCS file: /var/lib/cvs/pgsql/src/test/regress/parallel_schedule,v
retrieving revision 1.10
diff -c -r1.10 parallel_schedule
*** src/test/regress/parallel_schedule 20 Jun 2002 17:09:42 -0000 1.10
--- src/test/regress/parallel_schedule 28 Jun 2002 17:20:07 -0000
***************
*** 74,77 ****
# The sixth group of parallel test
# ----------
# "plpgsql" cannot run concurrently with "rules"
! test: limit plpgsql temp domain rangefuncs
--- 74,77 ----
# The sixth group of parallel test
# ----------
# "plpgsql" cannot run concurrently with "rules"
! test: limit plpgsql temp domain rangefuncs prepare
Index: src/test/regress/serial_schedule
===================================================================
RCS file: /var/lib/cvs/pgsql/src/test/regress/serial_schedule,v
retrieving revision 1.10
diff -c -r1.10 serial_schedule
*** src/test/regress/serial_schedule 20 Jun 2002 17:09:42 -0000 1.10
--- src/test/regress/serial_schedule 28 Jun 2002 17:20:07 -0000
***************
*** 83,85 ****
--- 83,86 ----
test: temp
test: domain
test: rangefuncs
+ test: prepare
Index: src/test/regress/expected/prepare.out
===================================================================
RCS file: src/test/regress/expected/prepare.out
diff -N src/test/regress/expected/prepare.out
*** /dev/null 1 Jan 1970 00:00:00 -0000
--- src/test/regress/expected/prepare.out 28 Jun 2002 17:20:07 -0000
***************
*** 0 ****
--- 1,107 ----
+ -- Regression tests for prepareable statements
+ PREPARE q1 FROM SELECT 1;
+ EXECUTE q1;
+ ?column?
+ ----------
+ 1
+ (1 row)
+
+ -- should fail
+ PREPARE q1 FROM SELECT 2;
+ ERROR: Prepared statement with name "q1" already exists.
+ -- should succeed
+ DEALLOCATE q1;
+ PREPARE q1 FROM SELECT 2;
+ EXECUTE q1;
+ ?column?
+ ----------
+ 2
+ (1 row)
+
+ -- sql92 syntax
+ DEALLOCATE PREPARE q1;
+ -- parametized queries
+ PREPARE q2(text) FROM
+ SELECT datname, datdba, datistemplate, datallowconn
+ FROM pg_database WHERE datname = $1;
+ EXECUTE q2 USING 'regression';
+ datname | datdba | datistemplate | datallowconn
+ ------------+--------+---------------+--------------
+ regression | 1 | f | t
+ (1 row)
+
+ PREPARE q3(text, int, float, boolean, oid, smallint) FROM
+ SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR
+ ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int);
+ EXECUTE q3 USING 'AAAAxx', 5::smallint, 10.5::float, false, 500::oid, 4::bigint;
+ unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
+ ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
+ 4502 | 412 | 0 | 2 | 2 | 2 | 2 | 502 | 502 | 4502 | 4502 | 4 | 5 | ERAAAA | WPAAAA | AAAAxx
+ 102 | 612 | 0 | 2 | 2 | 2 | 2 | 102 | 102 | 102 | 102 | 4 | 5 | YDAAAA | OXAAAA | AAAAxx
+ 7602 | 1040 | 0 | 2 | 2 | 2 | 2 | 602 | 1602 | 2602 | 7602 | 4 | 5 | KGAAAA | AOBAAA | AAAAxx
+ 902 | 1104 | 0 | 2 | 2 | 2 | 2 | 902 | 902 | 902 | 902 | 4 | 5 | SIAAAA | MQBAAA | AAAAxx
+ 4902 | 1600 | 0 | 2 | 2 | 2 | 2 | 902 | 902 | 4902 | 4902 | 4 | 5 | OGAAAA | OJCAAA | AAAAxx
+ 9502 | 1812 | 0 | 2 | 2 | 2 | 2 | 502 | 1502 | 4502 | 9502 | 4 | 5 | MBAAAA | SRCAAA | AAAAxx
+ 4702 | 2520 | 0 | 2 | 2 | 2 | 2 | 702 | 702 | 4702 | 4702 | 4 | 5 | WYAAAA | YSDAAA | AAAAxx
+ 1002 | 2580 | 0 | 2 | 2 | 2 | 2 | 2 | 1002 | 1002 | 1002 | 4 | 5 | OMAAAA | GVDAAA | AAAAxx
+ 2 | 2716 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | MAEAAA | AAAAxx
+ 802 | 2908 | 0 | 2 | 2 | 2 | 2 | 802 | 802 | 802 | 802 | 4 | 5 | WEAAAA | WHEAAA | AAAAxx
+ 6402 | 3808 | 0 | 2 | 2 | 2 | 2 | 402 | 402 | 1402 | 6402 | 4 | 5 | GMAAAA | MQFAAA | AAAAxx
+ 8602 | 5440 | 0 | 2 | 2 | 2 | 2 | 602 | 602 | 3602 | 8602 | 4 | 5 | WSAAAA | GBIAAA | AAAAxx
+ 8402 | 5708 | 0 | 2 | 2 | 2 | 2 | 402 | 402 | 3402 | 8402 | 4 | 5 | ELAAAA | OLIAAA | AAAAxx
+ 2102 | 6184 | 0 | 2 | 2 | 2 | 2 | 102 | 102 | 2102 | 2102 | 4 | 5 | WCAAAA | WDJAAA | AAAAxx
+ 4202 | 6628 | 0 | 2 | 2 | 2 | 2 | 202 | 202 | 4202 | 4202 | 4 | 5 | QFAAAA | YUJAAA | AAAAxx
+ 2902 | 6816 | 0 | 2 | 2 | 2 | 2 | 902 | 902 | 2902 | 2902 | 4 | 5 | QHAAAA | ECKAAA | AAAAxx
+ 2302 | 7112 | 0 | 2 | 2 | 2 | 2 | 302 | 302 | 2302 | 2302 | 4 | 5 | OKAAAA | ONKAAA | AAAAxx
+ 3202 | 7128 | 0 | 2 | 2 | 2 | 2 | 202 | 1202 | 3202 | 3202 | 4 | 5 | ETAAAA | EOKAAA | AAAAxx
+ 7802 | 7508 | 0 | 2 | 2 | 2 | 2 | 802 | 1802 | 2802 | 7802 | 4 | 5 | COAAAA | UCLAAA | AAAAxx
+ 4102 | 7676 | 0 | 2 | 2 | 2 | 2 | 102 | 102 | 4102 | 4102 | 4 | 5 | UBAAAA | GJLAAA | AAAAxx
+ 8302 | 7800 | 0 | 2 | 2 | 2 | 2 | 302 | 302 | 3302 | 8302 | 4 | 5 | IHAAAA | AOLAAA | AAAAxx
+ 1702 | 7940 | 0 | 2 | 2 | 2 | 2 | 702 | 1702 | 1702 | 1702 | 4 | 5 | MNAAAA | KTLAAA | AAAAxx
+ 2202 | 8028 | 0 | 2 | 2 | 2 | 2 | 202 | 202 | 2202 | 2202 | 4 | 5 | SGAAAA | UWLAAA | AAAAxx
+ 1602 | 8148 | 0 | 2 | 2 | 2 | 2 | 602 | 1602 | 1602 | 1602 | 4 | 5 | QJAAAA | KBMAAA | AAAAxx
+ 5602 | 8796 | 0 | 2 | 2 | 2 | 2 | 602 | 1602 | 602 | 5602 | 4 | 5 | MHAAAA | IANAAA | AAAAxx
+ 6002 | 8932 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 1002 | 6002 | 4 | 5 | WWAAAA | OFNAAA | AAAAxx
+ 3902 | 9224 | 0 | 2 | 2 | 2 | 2 | 902 | 1902 | 3902 | 3902 | 4 | 5 | CUAAAA | UQNAAA | AAAAxx
+ 9602 | 9972 | 0 | 2 | 2 | 2 | 2 | 602 | 1602 | 4602 | 9602 | 4 | 5 | IFAAAA | OTOAAA | AAAAxx
+ 8002 | 9980 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 3002 | 8002 | 4 | 5 | UVAAAA | WTOAAA | AAAAxx
+ (29 rows)
+
+ -- too few params
+ EXECUTE q3 USING 'bool';
+ ERROR: Wrong number of parameters, expected 6 but got 1
+ -- too many params
+ EXECUTE q3 USING 'bytea', 5::smallint, 10.5::float, false, 500::oid, 4::bigint, true;
+ ERROR: Wrong number of parameters, expected 6 but got 7
+ -- wrong param types
+ EXECUTE q3 USING 5::smallint, 10.5::float, false, 500::oid, 4::bigint, 'bytea';
+ ERROR: Parameter $2 of type "float8" cannot be coerced into the expected type ("int4").
+ You will need to rewrite or cast the expression.
+ -- invalid type
+ PREPARE q4(nonexistenttype) FROM SELECT $1;
+ ERROR: Type "nonexistenttype" does not exist
+ -- execute into
+ PREPARE q5(int, text) FROM
+ SELECT * FROM tenk1 WHERE unique1 = $1 OR stringu1 = $2;
+ EXECUTE q5 INTO TEMPORARY q5_prep_results USING 200, 'DTAAAA';
+ SELECT * FROM q5_prep_results;
+ unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
+ ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
+ 2525 | 64 | 1 | 1 | 5 | 5 | 25 | 525 | 525 | 2525 | 2525 | 50 | 51 | DTAAAA | MCAAAA | AAAAxx
+ 7257 | 1895 | 1 | 1 | 7 | 17 | 57 | 257 | 1257 | 2257 | 7257 | 114 | 115 | DTAAAA | XUCAAA | VVVVxx
+ 9961 | 2058 | 1 | 1 | 1 | 1 | 61 | 961 | 1961 | 4961 | 9961 | 122 | 123 | DTAAAA | EBDAAA | OOOOxx
+ 3877 | 4060 | 1 | 1 | 7 | 17 | 77 | 877 | 1877 | 3877 | 3877 | 154 | 155 | DTAAAA | EAGAAA | AAAAxx
+ 4553 | 4113 | 1 | 1 | 3 | 13 | 53 | 553 | 553 | 4553 | 4553 | 106 | 107 | DTAAAA | FCGAAA | HHHHxx
+ 7933 | 4514 | 1 | 1 | 3 | 13 | 33 | 933 | 1933 | 2933 | 7933 | 66 | 67 | DTAAAA | QRGAAA | OOOOxx
+ 6581 | 4686 | 1 | 1 | 1 | 1 | 81 | 581 | 581 | 1581 | 6581 | 162 | 163 | DTAAAA | GYGAAA | OOOOxx
+ 8609 | 5918 | 1 | 1 | 9 | 9 | 9 | 609 | 609 | 3609 | 8609 | 18 | 19 | DTAAAA | QTIAAA | OOOOxx
+ 5229 | 6407 | 1 | 1 | 9 | 9 | 29 | 229 | 1229 | 229 | 5229 | 58 | 59 | DTAAAA | LMJAAA | VVVVxx
+ 1173 | 6699 | 1 | 1 | 3 | 13 | 73 | 173 | 1173 | 1173 | 1173 | 146 | 147 | DTAAAA | RXJAAA | VVVVxx
+ 3201 | 7309 | 1 | 1 | 1 | 1 | 1 | 201 | 1201 | 3201 | 3201 | 2 | 3 | DTAAAA | DVKAAA | HHHHxx
+ 1849 | 8143 | 1 | 1 | 9 | 9 | 49 | 849 | 1849 | 1849 | 1849 | 98 | 99 | DTAAAA | FBMAAA | VVVVxx
+ 9285 | 8469 | 1 | 1 | 5 | 5 | 85 | 285 | 1285 | 4285 | 9285 | 170 | 171 | DTAAAA | TNMAAA | HHHHxx
+ 497 | 9092 | 1 | 1 | 7 | 17 | 97 | 497 | 497 | 497 | 497 | 194 | 195 | DTAAAA | SLNAAA | AAAAxx
+ 200 | 9441 | 0 | 0 | 0 | 0 | 0 | 200 | 200 | 200 | 200 | 0 | 1 | SHAAAA | DZNAAA | HHHHxx
+ 5905 | 9537 | 1 | 1 | 5 | 5 | 5 | 905 | 1905 | 905 | 5905 | 10 | 11 | DTAAAA | VCOAAA | HHHHxx
+ (16 rows)
+
Index: src/test/regress/sql/prepare.sql
===================================================================
RCS file: src/test/regress/sql/prepare.sql
diff -N src/test/regress/sql/prepare.sql
*** /dev/null 1 Jan 1970 00:00:00 -0000
--- src/test/regress/sql/prepare.sql 28 Jun 2002 17:20:07 -0000
***************
*** 0 ****
--- 1,45 ----
+ -- Regression tests for prepareable statements
+
+ PREPARE q1 FROM SELECT 1;
+ EXECUTE q1;
+
+ -- should fail
+ PREPARE q1 FROM SELECT 2;
+
+ -- should succeed
+ DEALLOCATE q1;
+ PREPARE q1 FROM SELECT 2;
+ EXECUTE q1;
+
+ -- sql92 syntax
+ DEALLOCATE PREPARE q1;
+
+ -- parametized queries
+ PREPARE q2(text) FROM
+ SELECT datname, datdba, datistemplate, datallowconn
+ FROM pg_database WHERE datname = $1;
+ EXECUTE q2 USING 'regression';
+
+ PREPARE q3(text, int, float, boolean, oid, smallint) FROM
+ SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR
+ ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int);
+
+ EXECUTE q3 USING 'AAAAxx', 5::smallint, 10.5::float, false, 500::oid, 4::bigint;
+
+ -- too few params
+ EXECUTE q3 USING 'bool';
+
+ -- too many params
+ EXECUTE q3 USING 'bytea', 5::smallint, 10.5::float, false, 500::oid, 4::bigint, true;
+
+ -- wrong param types
+ EXECUTE q3 USING 5::smallint, 10.5::float, false, 500::oid, 4::bigint, 'bytea';
+
+ -- invalid type
+ PREPARE q4(nonexistenttype) FROM SELECT $1;
+
+ -- execute into
+ PREPARE q5(int, text) FROM
+ SELECT * FROM tenk1 WHERE unique1 = $1 OR stringu1 = $2;
+ EXECUTE q5 INTO TEMPORARY q5_prep_results USING 200, 'DTAAAA';
+ SELECT * FROM q5_prep_results;
The syntax is largely SQL92 compliant, but not totally. I'm not sure how
the SQL spec expects parameters to be set up in PREPARE, but I doubt
it's the same way I used. And the SQL92 spec for EXECUTE is functionally
similar, but uses a different syntax (EXECUTE ... USING INTO <rel>, I
think). If someone can decipher the spec on these two points and
can suggest what the proper syntax should be, let me know.
I'll have a read of the spec for you to see if I can decode something out of
it! I think it's pretty essential we have full standard compliance on this
one!
Chris
On Fri, Jun 28, 2002 at 01:41:54PM -0400, Neil Conway wrote:
The attached patch implements per-backend prepareable statements.
Can someone comment on when this will be reviewed and/or applied?
Cheers,
Neil
--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC
nconway@klamath.dyndns.org (Neil Conway) writes:
On Fri, Jun 28, 2002 at 01:41:54PM -0400, Neil Conway wrote:
The attached patch implements per-backend prepareable statements.
Can someone comment on when this will be reviewed and/or applied?
It's on my to-look-at list, but I'm deathly behind on reviewing patches.
I guess the good news is that lots of great stuff is coming in from a
lot of fairly new contributors. The bad news is that we're getting way
behind on reviewing it. I think I've spent all my reviewing time this
month just on stuff from Rod Taylor...
regards, tom lane
Tom Lane wrote:
nconway@klamath.dyndns.org (Neil Conway) writes:
On Fri, Jun 28, 2002 at 01:41:54PM -0400, Neil Conway wrote:
The attached patch implements per-backend prepareable statements.
Can someone comment on when this will be reviewed and/or applied?
It's on my to-look-at list, but I'm deathly behind on reviewing patches.
I guess the good news is that lots of great stuff is coming in from a
lot of fairly new contributors. The bad news is that we're getting way
behind on reviewing it. I think I've spent all my reviewing time this
month just on stuff from Rod Taylor...
Yes, we are backed up. I am applying stuff that Tom doesn't claim after
a few days, but even then Tom will go back and review them. Not sure
what we can do except to say everything will be in before 7.3 beta, and
we regret that a few items can't get in sooner.
The good news is that it is only a few patches that are held up. The
others are getting applied in a timely manner.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026nconway@klamath.dyndns.org (Neil Conway) writes:
The attached patch implements per-backend prepareable statements.
Finally some feedback:
The syntax is:
PREPARE name_of_stmt(param_types) FROM <some query>;
EXECUTE name_of_stmt [INTO relation] [USING args];
DEALLOCATE [PREPARE] name_of_stmt;
I don't really like the 'FROM' keyword in PREPARE (I was planning to
use 'AS'), but that's what SQL92 specifies.
Actually not. SQL92 defines this command as
<prepare statement> ::=
PREPARE <SQL statement name> FROM <SQL statement variable>
<SQL statement variable> ::= <simple value specification>
where
<simple value specification> ::=
<parameter name>
| <embedded variable name>
(the normal <literal> case for <simple value specification> is
disallowed). So what they are really truly defining here is an
embedded-SQL operation in which the statement-to-prepare comes from
some kind of string variable in the client program. (SQL99 makes this
even clearer by moving PREPARE into Part 5, Host Language Bindings.)
AFAICT, the syntax we are setting up with actual SQL following the
PREPARE keyword is *not* valid SQL92 nor SQL99. It would be a good
idea to look and see whether any other DBMSes implement syntax that
is directly comparable to the feature we want. (Oracle manuals handy,
anyone?)
Assuming we do not find any comparable syntax to steal, my inclination
would be to go back to your original syntax and use "AS" as the
delimiter. That way we're not creating problems for ourselves if we
ever want to implement the truly spec-compliant syntax (in ecpg, say).
The syntax is largely SQL92 compliant, but not totally. I'm not sure how
the SQL spec expects parameters to be set up in PREPARE, but I doubt
it's the same way I used.
I can't see any hint of specifying parameter types in SQL's PREPARE at
all. So we're on our own there, unless we can take some guidance
from other systems.
And the SQL92 spec for EXECUTE is functionally
similar, but uses a different syntax (EXECUTE ... USING INTO <rel>, I
think).
It's not really similar at all. Again, the assumed context is an
embedded SQL program, and the real targets of INTO are supposed to be
host-program variable names. (plpgsql's use of SELECT INTO is a lot
more similar to the spec than our main grammar's use of it.)
While I won't strongly object to implementing EXECUTE INTO as you've
shown it, I think a good case could be made for leaving it out, on the
grounds that our form of SELECT INTO is a mistake and a compatibility
problem, and we shouldn't propagate it further. Any opinions out there?
In general, this is only vaguely similar to what SQL92 contemplates,
and you're probably better off not getting too close to their syntax...
Moving on to coding issues of varying significance:
The patch stores queries in a hash table in TopMemoryContext.
Fine with me. No reason to change to a linked list. (But see note below.)
Also, I'm not entirely sure my approach to memory management is
correct. Each entry in the hash table stores its data in its
own MemoryContext, which is deleted when the statement is
DEALLOCATE'd. When actually running the prepared statement
through the executor, CurrentMemoryContext is used. Let me know
if there's a better way to do this.
I think it's all right. On entry to ExecuteQuery, current context
should be TransactionCommandContext, which is a perfectly fine place
for constructing the querytree-to-execute. You do need to copy the
querytree as you're doing because of our lamentable tendency to scribble
on querytrees in the executor.
* In PrepareQuery: plan_list must be same len as query list (indeed you
have an Assert for that later); this code will blow it if a UTILITY_CMD
is produced by the rewriter. (Can happen: consider a NOTIFY produced
by a rule.) Insert a NULL into the plan list to keep the lists in step.
* In StoreQuery, the MemoryContextSwitchTo(TopMemoryContext) should be
unnecessary. The hashtable code stuffs its stuff into its own context.
You aren't actually storing anything into TopMemoryContext, only into
children thereof.
* DeallocateQuery is not prepared for uninitialized hashtable.
* RunQuery should NOT do BeginCommand; that was done by postgres.c.
* Sending output only for last query is wrong; this makes incorrect
assumptions about what the rewriter will produce. AFAIK there is no
good reason you should not execute all queries with the passed-in dest;
that's what postgres.c does.
* Is it really appropriate to be doing Show_executor_stats stuff here?
I think only postgres.c should do that.
* This is certainly not legal C:
+ if (Show_executor_stats)
+ ResetUsage();
+
+ QueryDesc *qdesc = CreateQueryDesc(query, plan, dest, NULL);
+ EState *state = CreateExecutorState();You must be using a C++ compiler.
* The couple of pfrees at the bottom of ExecuteQuery are kinda silly
considering how much else got allocated and not freed there.
* transformPrepareStmt is not doing the right thing with extras_before
and extras_after. Since you only allow an OptimizableStmt in the
syntax, probably these will always remain NIL, but I'd suggest throwing
in a test and elog.
* What if the stored query is replaced between the time that
transformExecuteStmt runs and the time the EXECUTE stmt is actually
executed? All your careful checking of the parameters could be totally
wrong --- and ExecuteQuery contains absolutely no defenses against a
mismatch. One answer is to store the expected parameter typelist
(array) in the ExecuteStmt node during transformExecuteStmt, and then
verify that this matches after you look up the statement in
ExecuteQuery.
* transformExecuteStmt must disallow subselects and aggregate functions
in the parameter expressions, since you aren't prepared to generate
query plans for them. Compare the processing of default or
check-constraint expressions. BTW, you might as well do the fix_opids
call at transform time not runtime, too.
* In gram.y: put the added keywords in the appropriate keyword-list
production (hopefully the unreserved one).
* Syntax for prepare_type_list is not good; it allows
( , int )
Probably best to push the () case into prepare_type_clause.
* typeidToString is bogus. Use format_type_be instead.
* Why does QueryData contain a context field?
* prepare.h should contain a standard header comment.
* You missed copyfuncs/equalfuncs support for the three added node types.
regards, tom lane
On Sat, Jul 20, 2002 at 10:00:01PM -0400, Tom Lane wrote:
AFAICT, the syntax we are setting up with actual SQL following the
PREPARE keyword is *not* valid SQL92 nor SQL99. It would be a good
idea to look and see whether any other DBMSes implement syntax that
is directly comparable to the feature we want. (Oracle manuals handy,
anyone?)
I couldn't find anything on the subject in the Oracle docs -- they have
PREPARE for use in embedded SQL, but I couldn't see a reference to
PREPARE for usage in regular SQL. Does anyone else know of an Oracle
equivalent?
Assuming we do not find any comparable syntax to steal, my inclination
would be to go back to your original syntax and use "AS" as the
delimiter. That way we're not creating problems for ourselves if we
ever want to implement the truly spec-compliant syntax (in ecpg, say).
Ok, sounds good to me.
* This is certainly not legal C:
+ if (Show_executor_stats) + ResetUsage(); + + QueryDesc *qdesc = CreateQueryDesc(query, plan, dest, NULL); + EState *state = CreateExecutorState();You must be using a C++ compiler.
Well, it's legal C99 I believe. I'm using gcc 3.1 with the default
CFLAGS, not a C++ compiler -- I guess it's a GNU extension... In any
case, I've fixed this.
* What if the stored query is replaced between the time that
transformExecuteStmt runs and the time the EXECUTE stmt is actually
executed?
Good point ... perhaps the easiest solution would be to remove
DEALLOCATE. Since the backend's prepared statements are flushed when the
backend dies, there is little need for deleting prepared statements
earlier than that. Users who need to prevent name clashes for
plan names can easily achieve that without using DEALLOCATE.
Regarding the syntax for EXECUTE, it occurs to me that it could be made
to be more similar to the PREPARE syntax -- i.e.
PREPARE foo(text, int) AS ...;
EXECUTE foo('a', 1);
(rather than EXECUTE USING -- the effect being that prepared statements
now look more like function calls on a syntactical level, which I think
is okay.)
Cheers,
Neil
--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC
Neil Conway wrote:
On Sat, Jul 20, 2002 at 10:00:01PM -0400, Tom Lane wrote:
AFAICT, the syntax we are setting up with actual SQL following the
PREPARE keyword is *not* valid SQL92 nor SQL99. It would be a good
idea to look and see whether any other DBMSes implement syntax that
is directly comparable to the feature we want. (Oracle manuals handy,
anyone?)I couldn't find anything on the subject in the Oracle docs -- they have
PREPARE for use in embedded SQL, but I couldn't see a reference to
PREPARE for usage in regular SQL. Does anyone else know of an Oracle
equivalent?
Oracle doesn't have this functionality exposed at the SQL level. In
Oracle the implementation is at the protocol level (i.e. sqlnet).
Therefore the SQL syntax is the same when using prepared statements or
when not using them. The client implementation of the sqlnet protocol
decides to use prepared statements or not. As of Oracle 8, I think
pretty much all of the Oracle clients use prepared statements for all
the sql statements. The sqlnet protocol exposes 'open', 'prepare',
'describe', 'bind', 'fetch' and 'close'. None of these are exposed out
into the SQL syntax.
thanks,
--Barry
nconway@klamath.dyndns.org (Neil Conway) writes:
Regarding the syntax for EXECUTE, it occurs to me that it could be made
to be more similar to the PREPARE syntax -- i.e.
PREPARE foo(text, int) AS ...;
EXECUTE foo('a', 1);
(rather than EXECUTE USING -- the effect being that prepared statements
now look more like function calls on a syntactical level, which I think
is okay.)
Hmm, maybe *too* much like a function call. Is there any risk of a
conflict with syntax that we might want to use to invoke stored
procedures? If not, this is fine with me.
regards, tom lane
On Tue, 2002-07-23 at 11:34, Tom Lane wrote:
nconway@klamath.dyndns.org (Neil Conway) writes:
Regarding the syntax for EXECUTE, it occurs to me that it could be made
to be more similar to the PREPARE syntax -- i.e.PREPARE foo(text, int) AS ...;
EXECUTE foo('a', 1);
(rather than EXECUTE USING -- the effect being that prepared statements
now look more like function calls on a syntactical level, which I think
is okay.)Hmm, maybe *too* much like a function call. Is there any risk of a
conflict with syntax that we might want to use to invoke stored
procedures? If not, this is fine with me.
Stored procedures would use PERFORM would they not?
I like the function syntax. It looks and acts like a temporary 'sql'
function.
On Sat, Jul 20, 2002 at 10:00:01PM -0400, Tom Lane wrote:
* In gram.y: put the added keywords in the appropriate keyword-list
production (hopefully the unreserved one).
I think the patch already does this, doesn't it? If not, what else
needs to be modified?
* Syntax for prepare_type_list is not good; it allows
( , int )
Erm, I don't see that it does. The syntax is:
prep_type_list: Typename { $$ = makeList1($1); }
| prep_type_list ',' Typename
{ $$ = lappend($1, $3); }
;
(i.e. there's no ' /* EMPTY */ ' case)
* Why does QueryData contain a context field?
Because the context in which the query data is stored needs to be
remembered so that it can be deleted by DeallocateQuery(). If
DEALLOCATE goes away, this should also be removed.
I've attached a revised patch, which includes most of Tom's suggestions,
with the exception of the three mentioned above. The syntax is now:
PREPARE q1(int, float, text) AS ...;
EXECUTE q1(5, 10.0, 'foo');
DEALLOCATE q1;
I'll post an updated patch to -patches tomorrow that gets rid of
DEALLOCATE. I also need to check if there is a need for executor_stats.
Finally, should the syntax for EXECUTE INTO be:
EXECUTE q1(...) INTO foo;
or
EXECUTE INTO foo q1(...);
The current patch uses the former, which I personally prefer, but
I'm not adamant about it.
Cheers,
Neil
--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC
Attachments:
new_qcache-14.patchtext/plain; charset=us-asciiDownload
Index: src/backend/commands/Makefile
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/commands/Makefile,v
retrieving revision 1.29
diff -c -r1.29 Makefile
*** src/backend/commands/Makefile 11 Jul 2002 07:39:27 -0000 1.29
--- src/backend/commands/Makefile 23 Jul 2002 16:25:53 -0000
***************
*** 13,21 ****
include $(top_builddir)/src/Makefile.global
OBJS = aggregatecmds.o analyze.o async.o cluster.o comment.o \
! conversioncmds.o copy.o \
! dbcommands.o define.o explain.o functioncmds.o \
! indexcmds.o lockcmds.o operatorcmds.o portalcmds.o proclang.o \
schemacmds.o sequence.o tablecmds.o trigger.o typecmds.o user.o \
vacuum.o vacuumlazy.o variable.o view.o
--- 13,21 ----
include $(top_builddir)/src/Makefile.global
OBJS = aggregatecmds.o analyze.o async.o cluster.o comment.o \
! conversioncmds.o copy.o dbcommands.o define.o \
! explain.o functioncmds.o indexcmds.o lockcmds.o \
! operatorcmds.o portalcmds.o prepare.o proclang.o \
schemacmds.o sequence.o tablecmds.o trigger.o typecmds.o user.o \
vacuum.o vacuumlazy.o variable.o view.o
Index: src/backend/commands/prepare.c
===================================================================
RCS file: src/backend/commands/prepare.c
diff -N src/backend/commands/prepare.c
*** /dev/null 1 Jan 1970 00:00:00 -0000
--- src/backend/commands/prepare.c 23 Jul 2002 16:25:53 -0000
***************
*** 0 ****
--- 1,392 ----
+ /*-------------------------------------------------------------------------
+ *
+ * prepare.c
+ * Prepareable SQL statements via PREPARE, EXECUTE and DEALLOCATE
+ *
+ * Copyright (c) 2002, PostgreSQL Global Development Group
+ *
+ * TODO:
+ * - in PREPARE, don't search the hash table twice
+ * - improve logic in EXECUTE
+ * - support EXECUTE IMMEDIATE, per SQL92
+ * - support DESCRIBE INPUT & DESCRIBE OUTPUT, per SQL92
+ * - IMHO, this is a complete waste of time
+ * - memory management: ensure that this is done properly, are
+ * we using the Context scheme suggested by Karel? (and if not,
+ * should we?)
+ *
+ * IDENTIFICATION
+ * $Header$
+ *
+ *-------------------------------------------------------------------------
+ */
+
+ #include "postgres.h"
+
+ #include "commands/prepare.h"
+ #include "executor/executor.h"
+ #include "utils/guc.h"
+ #include "optimizer/planner.h"
+ #include "rewrite/rewriteHandler.h"
+ #include "tcop/pquery.h"
+ #include "tcop/tcopprot.h"
+ #include "tcop/utility.h"
+ #include "utils/hsearch.h"
+ #include "utils/memutils.h"
+
+ #define HASH_KEY_LEN NAMEDATALEN
+
+ typedef struct
+ {
+ char key[HASH_KEY_LEN];
+ QueryData *data;
+ } HashEntry;
+
+ /*
+ * The hash table in which prepared queries are stored. This is
+ * per-backend: query plans are not shared between backends.
+ * The keys for this hash table are the arguments to PREPARE
+ * and EXECUTE ("plan names"); the values are QueryData structs.
+ */
+ static HTAB *prepared_queries = NULL;
+
+ static void StoreQuery(const char *plan_name, QueryData *query_data);
+ static void InitHashTable(void);
+ static void RunQuery(QueryDesc *qdesc, EState *state);
+
+ void
+ PrepareQuery(PrepareStmt *stmt)
+ {
+ QueryData query_data;
+ List *plan_list = NIL;
+ List *query_list,
+ *query_list_item;
+
+ if (!stmt->name)
+ elog(ERROR, "No statement name given.");
+
+ if (stmt->query->commandType == CMD_UTILITY)
+ elog(ERROR, "Utility statements cannot be prepared.");
+
+ /* Rewrite the query. The result could be 0, 1, or many queries. */
+ query_list = QueryRewrite(stmt->query);
+
+ foreach(query_list_item, query_list)
+ {
+ Plan *plan;
+ Query *query = (Query *) lfirst(query_list_item);
+
+ /* We can't generate plans for utility statements. */
+ if (query->commandType == CMD_UTILITY)
+ plan = NULL;
+ else
+ {
+ /* Call the query planner to generate a plan. */
+ plan = planner(query);
+ }
+
+ plan_list = lappend(plan_list, plan);
+ }
+
+ query_data.plan_list = plan_list;
+ query_data.query_list = query_list;
+ query_data.nargs = stmt->nargs;
+ query_data.argtypes = stmt->argtoids;
+
+ StoreQuery(stmt->name, &query_data);
+ }
+
+ /*
+ * Store all the data pertaining to the query in the hash table using
+ * the specified key. A copy of the data is made before storage, so the
+ * caller can dispose of their copy.
+ */
+ static void
+ StoreQuery(const char *stmt_name, QueryData *query_data)
+ {
+ bool found;
+ HashEntry *entry;
+ QueryData *data;
+ MemoryContext oldcxt,
+ entrycxt;
+ char key[HASH_KEY_LEN];
+
+ MemSet(key, 0, sizeof(key));
+ strncpy(key, stmt_name, sizeof(key));
+
+ /* Initialize the hash table, if necessary */
+ if (!prepared_queries)
+ InitHashTable();
+
+ hash_search(prepared_queries, key, HASH_FIND, &found);
+
+ if (found)
+ elog(ERROR, "Prepared statement with name \"%s\" already exists.", stmt_name);
+
+ entrycxt = AllocSetContextCreate(TopMemoryContext,
+ stmt_name,
+ 1024,
+ 1024,
+ ALLOCSET_DEFAULT_MAXSIZE);
+
+ oldcxt = MemoryContextSwitchTo(entrycxt);
+
+ /*
+ * Create the hash table entry. We need to copy the data so that
+ * it is stored in the correct memory context.
+ */
+ data = (QueryData *) palloc(sizeof(QueryData));
+
+ data->context = entrycxt;
+ data->nargs = query_data->nargs;
+ data->plan_list = (List *) copyObject(query_data->plan_list);
+ data->query_list = (List *) copyObject(query_data->query_list);
+
+ if (data->nargs)
+ {
+ int mem_size = sizeof(Oid) * data->nargs;
+ data->argtypes = (Oid *) palloc(mem_size);
+ memcpy(data->argtypes, query_data->argtypes, mem_size);
+ }
+ else
+ data->argtypes = NULL;
+
+ /* Add entry to hash table */
+ entry = (HashEntry *) hash_search(prepared_queries,
+ key,
+ HASH_ENTER,
+ NULL);
+
+ if (!entry)
+ elog(ERROR, "Unable to store prepared statement \"%s\"!", stmt_name);
+
+ data->key = entry->key;
+ entry->data = data;
+
+ MemoryContextSwitchTo(oldcxt);
+ }
+
+ static void
+ InitHashTable(void)
+ {
+ HASHCTL hash_ctl;
+
+ MemSet(&hash_ctl, 0, sizeof(hash_ctl));
+
+ hash_ctl.keysize = HASH_KEY_LEN;
+ hash_ctl.entrysize = sizeof(HashEntry);
+
+ prepared_queries = hash_create("Prepared Queries",
+ 8,
+ &hash_ctl,
+ HASH_ELEM);
+
+ if (!prepared_queries)
+ elog(ERROR, "InitHashTable(): unable to create hash table.");
+ }
+
+ /*
+ * Implements the 'EXECUTE' utility statement.
+ */
+ void
+ ExecuteQuery(ExecuteStmt *stmt, CommandDest outputDest)
+ {
+ ParamListInfo paramLI = NULL;
+ QueryData *qdata;
+ List *l,
+ *query_list,
+ *plan_list;
+
+ qdata = FetchQuery(stmt->name);
+
+ if (qdata->nargs)
+ {
+ List *l;
+ bool isNull;
+ int i = 0;
+
+ ExprContext *econtext = MakeExprContext(NULL, CurrentMemoryContext);
+
+ paramLI = (ParamListInfo) palloc(sizeof(ParamListInfoData) * qdata->nargs);
+
+ foreach (l, stmt->params)
+ {
+ Node *n = lfirst(l);
+
+ paramLI[i].value = ExecEvalExprSwitchContext(n, econtext, &isNull, NULL);
+ paramLI[i].kind = PARAM_NUM;
+ paramLI[i].id = i + 1;
+ paramLI[i].isnull = isNull;
+
+ i++;
+ }
+ }
+
+ query_list = qdata->query_list;
+ plan_list = qdata->plan_list;
+
+ Assert(length(query_list) == length(plan_list));
+
+ foreach(l, query_list)
+ {
+ bool is_last_query;
+ Query *query = lfirst(l);
+ Plan *plan = lfirst(plan_list);
+ plan_list = lnext(plan_list);
+
+ is_last_query = (plan_list == NIL);
+
+ if (query->commandType == CMD_UTILITY)
+ ProcessUtility(query->utilityStmt, outputDest, NULL);
+ else
+ {
+ QueryDesc *qdesc;
+ EState *state;
+
+ if (Show_executor_stats)
+ ResetUsage();
+
+ qdesc = CreateQueryDesc(query, plan, outputDest, NULL);
+ state = CreateExecutorState();
+
+ state->es_param_list_info = qdata->nargs ? paramLI : NULL;
+
+ if (stmt->into)
+ {
+ if (qdesc->operation != CMD_SELECT)
+ elog(ERROR, "INTO clause specified for non-SELECT query.");
+
+ query->into = stmt->into;
+ qdesc->dest = None;
+ }
+
+ RunQuery(qdesc, state);
+
+ if (Show_executor_stats)
+ ShowUsage("EXECUTOR STATISTICS");
+ }
+
+ /*
+ * If we're processing multiple queries, we need to increment
+ * the command counter between them. For the last query,
+ * there's no need to do this, it's done automatically.
+ */
+ if (! is_last_query)
+ CommandCounterIncrement();
+ }
+
+ /* No need to pfree memory, MemoryContext will be reset */
+ }
+
+ /*
+ * Fetch all data on the given prepared statement from the hash table in
+ * which it is stored.
+ */
+ QueryData *
+ FetchQuery(const char *plan_name)
+ {
+ QueryData *result;
+ HashEntry *entry;
+
+ /* See notes in DeallocateQuery() */
+ char key[HASH_KEY_LEN];
+
+ MemSet(key, 0, sizeof(key));
+ strncpy(key, plan_name, sizeof(key));
+
+ /*
+ * 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", plan_name);
+
+ entry = (HashEntry *) hash_search(prepared_queries,
+ key,
+ HASH_FIND,
+ NULL);
+
+ if (!entry)
+ elog(ERROR, "Prepared statement with name \"%s\" does not exist", plan_name);
+
+ result = palloc(sizeof(QueryData));
+
+ result->key = entry->data->key;
+ result->nargs = entry->data->nargs;
+ result->plan_list = (List *) copyObject(entry->data->plan_list);
+ result->query_list = (List *) copyObject(entry->data->query_list);
+ result->context = entry->data->context;
+
+ if (result->nargs)
+ {
+ int mem_size = sizeof(Oid) * result->nargs;
+ result->argtypes = (Oid *) palloc(mem_size);
+ memcpy(result->argtypes, entry->data->argtypes, mem_size);
+ }
+ else
+ result->argtypes = NULL;
+
+ return result;
+ }
+
+ /*
+ * Actually execute a prepared query. We can't use any of the existing
+ * routines in tcop/postgres.c because we need to manipulate part of the
+ * Executor (e.g. to inform it of parameters to execute) -- in any case,
+ * not much code is duplicated.
+ */
+ static void
+ RunQuery(QueryDesc *qdesc, EState *state)
+ {
+ TupleDesc tupdesc;
+
+ tupdesc = ExecutorStart(qdesc, state);
+
+ ExecutorRun(qdesc, state, state->es_direction, 0L);
+
+ ExecutorEnd(qdesc, state);
+ }
+
+ /*
+ * Implements the 'DEALLOCATE' utility statement: deletes the
+ * specified plan from storage.
+ */
+ void
+ DeallocateQuery(DeallocateStmt *stmt)
+ {
+ char key[HASH_KEY_LEN];
+ HashEntry *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 some of the 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 = hash_search(prepared_queries, key, HASH_FIND, NULL);
+
+ if (!entry)
+ elog(ERROR, "No plan found with name \"%s\"", stmt->name);
+
+ if (entry->data && MemoryContextIsValid(entry->data->context))
+ MemoryContextDelete(entry->data->context);
+
+ /* Okay, now we can remove the hash table entry */
+ hash_search(prepared_queries, key, HASH_REMOVE, NULL);
+ }
Index: src/backend/nodes/copyfuncs.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/nodes/copyfuncs.c,v
retrieving revision 1.196
diff -c -r1.196 copyfuncs.c
*** src/backend/nodes/copyfuncs.c 18 Jul 2002 17:14:19 -0000 1.196
--- src/backend/nodes/copyfuncs.c 23 Jul 2002 16:25:53 -0000
***************
*** 2569,2574 ****
--- 2569,2610 ----
return newnode;
}
+ static ExecuteStmt *
+ _copyExecuteStmt(ExecuteStmt *from)
+ {
+ ExecuteStmt *newnode = makeNode(ExecuteStmt);
+
+ newnode->name = pstrdup(from->name);
+ Node_Copy(from, newnode, into);
+ Node_Copy(from, newnode, params);
+
+ return newnode;
+ }
+
+ static PrepareStmt *
+ _copyPrepareStmt(PrepareStmt *from)
+ {
+ PrepareStmt *newnode = makeNode(PrepareStmt);
+
+ newnode->name = pstrdup(from->name);
+ newnode->nargs = from->nargs;
+ Node_Copy(from, newnode, argtypes);
+ Node_Copy(from, newnode, argtoids);
+ Node_Copy(from, newnode, query);
+
+ return newnode;
+ }
+
+ static DeallocateStmt *
+ _copyDeallocateStmt(DeallocateStmt *from)
+ {
+ DeallocateStmt *newnode = makeNode(DeallocateStmt);
+
+ newnode->name = pstrdup(from->name);
+
+ return newnode;
+ }
+
/* ****************************************************************
* pg_list.h copy functions
***************
*** 2975,2980 ****
--- 3011,3025 ----
break;
case T_CreateSchemaStmt:
retval = _copyCreateSchemaStmt(from);
+ break;
+ case T_ExecuteStmt:
+ retval = _copyExecuteStmt(from);
+ break;
+ case T_PrepareStmt:
+ retval = _copyPrepareStmt(from);
+ break;
+ case T_DeallocateStmt:
+ retval = _copyDeallocateStmt(from);
break;
case T_A_Expr:
Index: src/backend/nodes/equalfuncs.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/nodes/equalfuncs.c,v
retrieving revision 1.143
diff -c -r1.143 equalfuncs.c
*** src/backend/nodes/equalfuncs.c 18 Jul 2002 17:14:19 -0000 1.143
--- src/backend/nodes/equalfuncs.c 23 Jul 2002 16:25:53 -0000
***************
*** 1401,1406 ****
--- 1401,1445 ----
}
static bool
+ _equalExecuteStmt(ExecuteStmt *a, ExecuteStmt *b)
+ {
+ if (!equalstr(a->name, b->name))
+ return false;
+ if (!equal(a->into, b->into))
+ return false;
+ if (!equal(a->params, b->params))
+ return false;
+
+ return true;
+ }
+
+ static bool
+ _equalPrepareStmt(PrepareStmt *a, PrepareStmt *b)
+ {
+ if (!equalstr(a->name, b->name))
+ return false;
+ if (a->nargs != b->nargs)
+ return false;
+ if (!equal(a->argtypes, b->argtypes))
+ return false;
+ if (!equal(a->argtoids, b->argtoids))
+ return false;
+ if (!equal(a->query, b->query))
+ return false;
+
+ return true;
+ }
+
+ static bool
+ _equalDeallocateStmt(DeallocateStmt *a, DeallocateStmt *b)
+ {
+ if (!equalstr(a->name, b->name))
+ return false;
+
+ return true;
+ }
+
+ static bool
_equalAExpr(A_Expr *a, A_Expr *b)
{
if (a->oper != b->oper)
***************
*** 2139,2144 ****
--- 2178,2192 ----
break;
case T_CreateSchemaStmt:
retval = _equalCreateSchemaStmt(a, b);
+ break;
+ case T_ExecuteStmt:
+ retval = _equalExecuteStmt(a, b);
+ break;
+ case T_PrepareStmt:
+ retval = _equalPrepareStmt(a, b);
+ break;
+ case T_DeallocateStmt:
+ retval = _equalDeallocateStmt(a, b);
break;
case T_A_Expr:
Index: src/backend/parser/analyze.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/parser/analyze.c,v
retrieving revision 1.239
diff -c -r1.239 analyze.c
*** src/backend/parser/analyze.c 16 Jul 2002 22:12:19 -0000 1.239
--- src/backend/parser/analyze.c 23 Jul 2002 16:25:53 -0000
***************
*** 20,26 ****
--- 20,29 ----
#include "catalog/namespace.h"
#include "catalog/pg_index.h"
#include "catalog/pg_type.h"
+ #include "commands/prepare.h"
#include "nodes/makefuncs.h"
+ #include "optimizer/clauses.h"
+ #include "optimizer/planmain.h"
#include "parser/analyze.h"
#include "parser/gramparse.h"
#include "parser/parsetree.h"
***************
*** 43,49 ****
#include "mb/pg_wchar.h"
#endif
-
/* State shared by transformCreateSchemaStmt and its subroutines */
typedef struct
{
--- 46,51 ----
***************
*** 94,99 ****
--- 96,103 ----
static Query *transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt);
static Node *transformSetOperationTree(ParseState *pstate, SelectStmt *stmt);
static Query *transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt);
+ static Query *transformPrepareStmt(ParseState *pstate, PrepareStmt *stmt);
+ static Query *transformExecuteStmt(ParseState *pstate, ExecuteStmt *stmt);
static Query *transformCreateStmt(ParseState *pstate, CreateStmt *stmt,
List **extras_before, List **extras_after);
static Query *transformAlterTableStmt(ParseState *pstate, AlterTableStmt *stmt,
***************
*** 280,285 ****
--- 284,297 ----
extras_before, extras_after);
break;
+ case T_PrepareStmt:
+ result = transformPrepareStmt(pstate, (PrepareStmt *) parseTree);
+ break;
+
+ case T_ExecuteStmt:
+ result = transformExecuteStmt(pstate, (ExecuteStmt *) parseTree);
+ break;
+
/*
* Optimizable statements
*/
***************
*** 2477,2482 ****
--- 2489,2622 ----
qry->utilityStmt = (Node *) stmt;
return qry;
+ }
+
+ static Query *
+ transformPrepareStmt(ParseState *pstate, PrepareStmt *stmt)
+ {
+ Query *result = makeNode(Query);
+ List *extras_before = NIL,
+ *extras_after = NIL;
+ Oid *argtoids = NULL;
+
+ stmt->nargs = length(stmt->argtypes);
+
+ if (stmt->nargs)
+ {
+ List *l;
+ int i = 0;
+
+ argtoids = palloc(sizeof(*argtoids) * stmt->nargs);
+
+ foreach (l, stmt->argtypes)
+ {
+ TypeName *tn = lfirst(l);
+ Oid toid = typenameTypeId(tn);
+
+ if (!OidIsValid(toid))
+ {
+ elog(ERROR, "Argument $%d has invalid type \"%s\"",
+ i + 1, TypeNameToString(tn));
+ }
+
+ argtoids[i++] = toid;
+ }
+ }
+
+ /*
+ * We need to adjust the number of parameters expected by the
+ * rest of the system, so that $1, ... $n are parsed properly.
+ * This is somewhat of a hack; however, the existing interfaces
+ * only allow parameters to be specified when working with a
+ * raw query string (parser(), pg_parse_query(), etc.), which
+ * can't be used here.
+ */
+ parser_param_init(argtoids, stmt->nargs);
+
+ stmt->argtoids = argtoids;
+
+ stmt->query = transformStmt(pstate, (Node *) stmt->query,
+ &extras_before, &extras_after);
+
+ if (extras_before || extras_after)
+ elog(ERROR, "transformPrepareStmt: internal error");
+
+ result->commandType = CMD_UTILITY;
+ result->utilityStmt = (Node *) stmt;
+
+ return result;
+ }
+
+ static Query *
+ transformExecuteStmt(ParseState *pstate, ExecuteStmt *stmt)
+ {
+ Query *result = makeNode(Query);
+ QueryData *qdata;
+
+ result->commandType = CMD_UTILITY;
+ result->utilityStmt = (Node *) stmt;
+
+ qdata = FetchQuery(stmt->name);
+
+ if (!qdata)
+ elog(ERROR, "No such prepared statement: %s", stmt->name);
+
+ if (stmt->params || qdata->nargs)
+ {
+ List *l;
+ Oid expected_type_id,
+ given_type_id;
+ int i = 0;
+ int nparams = length(stmt->params);
+
+ if (nparams != qdata->nargs)
+ {
+ elog(ERROR, "Wrong number of parameters, expected %d but got %d",
+ qdata->nargs, nparams);
+ }
+
+ foreach (l, stmt->params)
+ {
+ Node *expr = lfirst(l);
+
+ expr = transformExpr(pstate, expr);
+
+ /* Cannot contain subselects or aggregates */
+ if (contain_subplans(expr))
+ elog(ERROR, "Cannot use subselect in EXECUTE parameters.");
+ if (contain_agg_clause(expr))
+ elog(ERROR, "Cannot use aggregates in EXECUTE parameters.");
+
+ given_type_id = exprType(expr);
+ expected_type_id = qdata->argtypes[i];
+
+ if (given_type_id != expected_type_id)
+ {
+ expr = CoerceTargetExpr(pstate,
+ expr,
+ given_type_id,
+ expected_type_id,
+ -1,
+ false);
+
+ if (!expr)
+ {
+ elog(ERROR, "Parameter $%d of type \"%s\" cannot be "
+ "coerced into the expected type (\"%s\").\n\t"
+ "You will need to rewrite or cast the expression.",
+ i + 1,
+ format_type_be(given_type_id),
+ format_type_be(expected_type_id));
+ }
+ }
+
+ fix_opids(expr);
+ lfirst(l) = expr;
+ i++;
+ }
+ }
+
+ return result;
}
/* exported so planner can check again after rewriting, query pullup, etc */
Index: src/backend/parser/gram.y
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.348
diff -c -r2.348 gram.y
*** src/backend/parser/gram.y 20 Jul 2002 05:58:34 -0000 2.348
--- src/backend/parser/gram.y 23 Jul 2002 16:25:53 -0000
***************
*** 151,157 ****
SelectStmt, TransactionStmt, TruncateStmt,
UnlistenStmt, UpdateStmt, VacuumStmt,
VariableResetStmt, VariableSetStmt, VariableShowStmt,
! ViewStmt, CheckPointStmt, CreateConversionStmt
%type <node> select_no_parens, select_with_parens, select_clause,
simple_select
--- 151,158 ----
SelectStmt, TransactionStmt, TruncateStmt,
UnlistenStmt, UpdateStmt, VacuumStmt,
VariableResetStmt, VariableSetStmt, VariableShowStmt,
! ViewStmt, CheckPointStmt, CreateConversionStmt,
! DeallocateStmt, PrepareStmt, ExecuteStmt
%type <node> select_no_parens, select_with_parens, select_clause,
simple_select
***************
*** 218,224 ****
target_list, update_target_list, insert_column_list,
insert_target_list, def_list, opt_indirection,
group_clause, TriggerFuncArgs, select_limit,
! opt_select_limit
%type <range> into_clause, OptTempTableName
--- 219,226 ----
target_list, update_target_list, insert_column_list,
insert_target_list, def_list, opt_indirection,
group_clause, TriggerFuncArgs, select_limit,
! opt_select_limit, prep_type_clause, prep_type_list,
! execute_param_clause, execute_param_list
%type <range> into_clause, OptTempTableName
***************
*** 332,338 ****
CREATEUSER, CROSS, CURRENT_DATE, CURRENT_TIME,
CURRENT_TIMESTAMP, CURRENT_USER, CURSOR, CYCLE,
! DATABASE, DAY_P, DEC, DECIMAL, DECLARE, DEFAULT,
DEFERRABLE, DEFERRED, DEFINER, DELETE_P, DELIMITER, DELIMITERS,
DESC, DISTINCT, DO, DOMAIN_P, DOUBLE, DROP,
--- 334,340 ----
CREATEUSER, CROSS, CURRENT_DATE, CURRENT_TIME,
CURRENT_TIMESTAMP, CURRENT_USER, CURSOR, CYCLE,
! DATABASE, DAY_P, DEALLOCATE, DEC, DECIMAL, DECLARE, DEFAULT,
DEFERRABLE, DEFERRED, DEFINER, DELETE_P, DELIMITER, DELIMITERS,
DESC, DISTINCT, DO, DOMAIN_P, DOUBLE, DROP,
***************
*** 368,374 ****
ORDER, OUT_P, OUTER_P, OVERLAPS, OVERLAY, OWNER,
PARTIAL, PASSWORD, PATH_P, PENDANT, PLACING, POSITION,
! PRECISION, PRIMARY, PRIOR, PRIVILEGES, PROCEDURE,
PROCEDURAL,
READ, REAL, REFERENCES, REINDEX, RELATIVE, RENAME, REPLACE,
--- 370,376 ----
ORDER, OUT_P, OUTER_P, OVERLAPS, OVERLAY, OWNER,
PARTIAL, PASSWORD, PATH_P, PENDANT, PLACING, POSITION,
! PRECISION, PREPARE, PRIMARY, PRIOR, PRIVILEGES, PROCEDURE,
PROCEDURAL,
READ, REAL, REFERENCES, REINDEX, RELATIVE, RENAME, REPLACE,
***************
*** 486,491 ****
--- 488,494 ----
| CreateTrigStmt
| CreateUserStmt
| ClusterStmt
+ | DeallocateStmt
| DefineStmt
| DropStmt
| TruncateStmt
***************
*** 497,502 ****
--- 500,506 ----
| DropTrigStmt
| DropRuleStmt
| DropUserStmt
+ | ExecuteStmt
| ExplainStmt
| FetchStmt
| GrantStmt
***************
*** 526,531 ****
--- 530,536 ----
| ConstraintsSetStmt
| CheckPointStmt
| CreateConversionStmt
+ | PrepareStmt
| /*EMPTY*/
{ $$ = (Node *)NULL; }
;
***************
*** 3798,3803 ****
--- 3803,3877 ----
}
;
+ /*****************************************************************************
+ *
+ * QUERY:
+ * PREPARE <plan_name> [(args, ...)] AS <query>
+ *
+ *****************************************************************************/
+
+ PrepareStmt: PREPARE name prep_type_clause AS OptimizableStmt
+ {
+ PrepareStmt *n = makeNode(PrepareStmt);
+ n->name = $2;
+ n->argtypes = $3;
+ n->query = (Query *) $5;
+ $$ = (Node *) n;
+ }
+ ;
+
+ prep_type_clause: '(' prep_type_list ')' { $$ = $2; }
+ | /* EMPTY */ { $$ = NIL; }
+ ;
+
+ prep_type_list: Typename { $$ = makeList1($1); }
+ | prep_type_list ',' Typename
+ { $$ = lappend($1, $3); }
+ ;
+
+ /*****************************************************************************
+ *
+ * QUERY:
+ * EXECUTE <plan_name> [(params, ...)] [INTO ...]
+ *
+ *****************************************************************************/
+
+ ExecuteStmt: EXECUTE name execute_param_clause into_clause
+ {
+ ExecuteStmt *n = makeNode(ExecuteStmt);
+ n->name = $2;
+ n->params = $3;
+ n->into = $4;
+ $$ = (Node *) n;
+ }
+ ;
+
+ execute_param_clause: '(' execute_param_list ')' { $$ = $2; }
+ | /* EMPTY */ { $$ = NIL; }
+ ;
+
+ execute_param_list: a_expr { $$ = makeList1($1); }
+ | execute_param_list ',' a_expr { $$ = lappend($1, $3); }
+ ;
+
+ /*****************************************************************************
+ *
+ * QUERY:
+ * DEALLOCATE [PREPARE] <plan_name>
+ *
+ *****************************************************************************/
+
+ DeallocateStmt: DEALLOCATE opt_prepare name
+ {
+ DeallocateStmt *n = makeNode(DeallocateStmt);
+ n->name = $3;
+ $$ = (Node *) n;
+ }
+ ;
+
+ opt_prepare: PREPARE {}
+ | /* EMPTY */ {}
+ ;
/*****************************************************************************
* *
***************
*** 7287,7292 ****
--- 7361,7377 ----
* Keep enough information around to fill out the type of param nodes
* used in postquel functions
*/
+ parser_param_init(typev, nargs);
+ }
+
+ /*
+ * Do the initialization required for processing parameters. This
+ * is also called by transformPrepareStmt() to update the parser's
+ * view of the parameters in a prepared statement.
+ */
+ void
+ parser_param_init(Oid *typev, int nargs)
+ {
param_type_info = typev;
pfunc_num_args = nargs;
}
Index: src/backend/parser/keywords.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/parser/keywords.c,v
retrieving revision 1.122
diff -c -r1.122 keywords.c
*** src/backend/parser/keywords.c 18 Jul 2002 23:11:28 -0000 1.122
--- src/backend/parser/keywords.c 23 Jul 2002 16:25:53 -0000
***************
*** 92,97 ****
--- 92,98 ----
{"cycle", CYCLE},
{"database", DATABASE},
{"day", DAY_P},
+ {"deallocate", DEALLOCATE},
{"dec", DEC},
{"decimal", DECIMAL},
{"declare", DECLARE},
***************
*** 225,230 ****
--- 226,232 ----
{"placing", PLACING},
{"position", POSITION},
{"precision", PRECISION},
+ {"prepare", PREPARE},
{"primary", PRIMARY},
{"prior", PRIOR},
{"privileges", PRIVILEGES},
Index: src/backend/tcop/postgres.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/tcop/postgres.c,v
retrieving revision 1.272
diff -c -r1.272 postgres.c
*** src/backend/tcop/postgres.c 18 Jul 2002 23:11:28 -0000 1.272
--- src/backend/tcop/postgres.c 23 Jul 2002 16:25:53 -0000
***************
*** 2452,2457 ****
--- 2452,2469 ----
tag = "DROP CAST";
break;
+ case T_ExecuteStmt:
+ tag = "EXECUTE";
+ break;
+
+ case T_PrepareStmt:
+ tag = "PREPARE";
+ break;
+
+ case T_DeallocateStmt:
+ tag = "DEALLOCATE";
+ break;
+
default:
elog(LOG, "CreateCommandTag: unknown parse node type %d",
nodeTag(parsetree));
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/tcop/utility.c,v
retrieving revision 1.164
diff -c -r1.164 utility.c
*** src/backend/tcop/utility.c 18 Jul 2002 23:11:28 -0000 1.164
--- src/backend/tcop/utility.c 23 Jul 2002 16:25:53 -0000
***************
*** 30,35 ****
--- 30,36 ----
#include "commands/explain.h"
#include "commands/lockcmds.h"
#include "commands/portalcmds.h"
+ #include "commands/prepare.h"
#include "commands/proclang.h"
#include "commands/schemacmds.h"
#include "commands/sequence.h"
***************
*** 356,361 ****
--- 357,380 ----
SetQuerySnapshot();
DoCopy(stmt);
+ }
+ break;
+
+ case T_PrepareStmt:
+ {
+ PrepareQuery((PrepareStmt *) parsetree);
+ }
+ break;
+
+ case T_ExecuteStmt:
+ {
+ ExecuteQuery((ExecuteStmt *) parsetree, dest);
+ }
+ break;
+
+ case T_DeallocateStmt:
+ {
+ DeallocateQuery((DeallocateStmt *) parsetree);
}
break;
Index: src/backend/utils/adt/lockfuncs.c
===================================================================
RCS file: src/backend/utils/adt/lockfuncs.c
diff -N src/backend/utils/adt/lockfuncs.c
Index: src/include/commands/prepare.h
===================================================================
RCS file: src/include/commands/prepare.h
diff -N src/include/commands/prepare.h
*** /dev/null 1 Jan 1970 00:00:00 -0000
--- src/include/commands/prepare.h 23 Jul 2002 16:25:53 -0000
***************
*** 0 ****
--- 1,39 ----
+ /*-------------------------------------------------------------------------
+ *
+ * prepare.h
+ * PREPARE, EXECUTE and DEALLOCATE command prototypes
+ *
+ *
+ * Copyright (c) 2002, PostgreSQL Global Development Group
+ *
+ * $Id$
+ *
+ *-------------------------------------------------------------------------
+ */
+
+ #ifndef _PREPARE_H
+ #define _PREPARE_H
+
+ #include "nodes/parsenodes.h"
+ #include "tcop/dest.h"
+
+ /* All the data we need to execute a stored query */
+ typedef struct
+ {
+ char *key;
+ List *plan_list;
+ List *query_list;
+ int nargs;
+ Oid *argtypes;
+ MemoryContext context;
+ } QueryData;
+
+ extern void PrepareQuery(PrepareStmt *stmt);
+
+ extern void ExecuteQuery(ExecuteStmt *stmt, CommandDest outputDest);
+
+ extern void DeallocateQuery(DeallocateStmt *stmt);
+
+ extern QueryData *FetchQuery(const char *plan_name);
+
+ #endif
Index: src/include/nodes/nodes.h
===================================================================
RCS file: /var/lib/cvs/pgsql/src/include/nodes/nodes.h,v
retrieving revision 1.113
diff -c -r1.113 nodes.h
*** src/include/nodes/nodes.h 18 Jul 2002 23:11:32 -0000 1.113
--- src/include/nodes/nodes.h 23 Jul 2002 16:25:53 -0000
***************
*** 201,206 ****
--- 201,209 ----
T_CreateConversionStmt,
T_CreateCastStmt,
T_DropCastStmt,
+ T_DeallocateStmt,
+ T_ExecuteStmt,
+ T_PrepareStmt,
T_A_Expr = 700,
T_ColumnRef,
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /var/lib/cvs/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.193
diff -c -r1.193 parsenodes.h
*** src/include/nodes/parsenodes.h 18 Jul 2002 23:11:32 -0000 1.193
--- src/include/nodes/parsenodes.h 23 Jul 2002 16:25:53 -0000
***************
*** 1581,1584 ****
--- 1581,1623 ----
} DropCastStmt;
+ /* ----------------------
+ * PREPARE Statement
+ * ----------------------
+ */
+ typedef struct PrepareStmt
+ {
+ NodeTag type;
+ char *name; /* Name of plan, arbitrary */
+ int nargs; /* # of parameters */
+ List *argtypes; /* Types of parameters (TypeNames) */
+ Oid *argtoids; /* The OIDs of the param types */
+ Query *query; /* The query itself */
+ } PrepareStmt;
+
+
+ /* ----------------------
+ * EXECUTE Statement
+ * ----------------------
+ */
+
+ typedef struct ExecuteStmt
+ {
+ NodeTag type;
+ char *name; /* The name of the plan to execute */
+ RangeVar *into; /* The relation to store the results in */
+ List *params; /* Values of parameters */
+ } ExecuteStmt;
+
+
+ /* ----------------------
+ * DEALLOCATE Statement
+ * ----------------------
+ */
+ typedef struct DeallocateStmt
+ {
+ NodeTag type;
+ char *name; /* The name of the plan to remove */
+ } DeallocateStmt;
+
#endif /* PARSENODES_H */
Index: src/include/parser/gramparse.h
===================================================================
RCS file: /var/lib/cvs/pgsql/src/include/parser/gramparse.h,v
retrieving revision 1.23
diff -c -r1.23 gramparse.h
*** src/include/parser/gramparse.h 20 Jun 2002 20:29:51 -0000 1.23
--- src/include/parser/gramparse.h 23 Jul 2002 16:25:53 -0000
***************
*** 29,34 ****
--- 29,35 ----
/* from gram.y */
extern void parser_init(Oid *typev, int nargs);
+ extern void parser_param_init(Oid *typev, int nargs);
extern Oid param_type(int t);
extern int yyparse(void);
extern List *SystemFuncName(char *name);
Index: src/test/regress/parallel_schedule
===================================================================
RCS file: /var/lib/cvs/pgsql/src/test/regress/parallel_schedule,v
retrieving revision 1.13
diff -c -r1.13 parallel_schedule
*** src/test/regress/parallel_schedule 20 Jul 2002 05:16:59 -0000 1.13
--- src/test/regress/parallel_schedule 23 Jul 2002 16:25:53 -0000
***************
*** 74,80 ****
# The sixth group of parallel test
# ----------
# "plpgsql" cannot run concurrently with "rules"
! test: limit plpgsql temp domain rangefuncs copy2
test: without_oid
--- 74,80 ----
# The sixth group of parallel test
# ----------
# "plpgsql" cannot run concurrently with "rules"
! test: limit plpgsql temp domain rangefuncs copy2 prepare
test: without_oid
Index: src/test/regress/serial_schedule
===================================================================
RCS file: /var/lib/cvs/pgsql/src/test/regress/serial_schedule,v
retrieving revision 1.13
diff -c -r1.13 serial_schedule
*** src/test/regress/serial_schedule 20 Jul 2002 05:16:59 -0000 1.13
--- src/test/regress/serial_schedule 23 Jul 2002 16:25:53 -0000
***************
*** 85,88 ****
--- 85,89 ----
test: temp
test: domain
test: rangefuncs
+ test: prepare
test: without_oid
Index: src/test/regress/expected/prepare.out
===================================================================
RCS file: src/test/regress/expected/prepare.out
diff -N src/test/regress/expected/prepare.out
*** /dev/null 1 Jan 1970 00:00:00 -0000
--- src/test/regress/expected/prepare.out 23 Jul 2002 16:25:53 -0000
***************
*** 0 ****
--- 1,107 ----
+ -- Regression tests for prepareable statements
+ PREPARE q1 AS SELECT 1;
+ EXECUTE q1;
+ ?column?
+ ----------
+ 1
+ (1 row)
+
+ -- should fail
+ PREPARE q1 AS SELECT 2;
+ ERROR: Prepared statement with name "q1" already exists.
+ -- should succeed
+ DEALLOCATE q1;
+ PREPARE q1 AS SELECT 2;
+ EXECUTE q1;
+ ?column?
+ ----------
+ 2
+ (1 row)
+
+ -- sql92 syntax
+ DEALLOCATE PREPARE q1;
+ -- parametized queries
+ PREPARE q2(text) AS
+ SELECT datname, datdba, datistemplate, datallowconn
+ FROM pg_database WHERE datname = $1;
+ EXECUTE q2('regression');
+ datname | datdba | datistemplate | datallowconn
+ ------------+--------+---------------+--------------
+ regression | 1 | f | t
+ (1 row)
+
+ PREPARE q3(text, int, float, boolean, oid, smallint) AS
+ SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR
+ ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int);
+ EXECUTE q3('AAAAxx', 5::smallint, 10.5::float, false, 500::oid, 4::bigint);
+ unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
+ ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
+ 4502 | 412 | 0 | 2 | 2 | 2 | 2 | 502 | 502 | 4502 | 4502 | 4 | 5 | ERAAAA | WPAAAA | AAAAxx
+ 102 | 612 | 0 | 2 | 2 | 2 | 2 | 102 | 102 | 102 | 102 | 4 | 5 | YDAAAA | OXAAAA | AAAAxx
+ 7602 | 1040 | 0 | 2 | 2 | 2 | 2 | 602 | 1602 | 2602 | 7602 | 4 | 5 | KGAAAA | AOBAAA | AAAAxx
+ 902 | 1104 | 0 | 2 | 2 | 2 | 2 | 902 | 902 | 902 | 902 | 4 | 5 | SIAAAA | MQBAAA | AAAAxx
+ 4902 | 1600 | 0 | 2 | 2 | 2 | 2 | 902 | 902 | 4902 | 4902 | 4 | 5 | OGAAAA | OJCAAA | AAAAxx
+ 9502 | 1812 | 0 | 2 | 2 | 2 | 2 | 502 | 1502 | 4502 | 9502 | 4 | 5 | MBAAAA | SRCAAA | AAAAxx
+ 4702 | 2520 | 0 | 2 | 2 | 2 | 2 | 702 | 702 | 4702 | 4702 | 4 | 5 | WYAAAA | YSDAAA | AAAAxx
+ 1002 | 2580 | 0 | 2 | 2 | 2 | 2 | 2 | 1002 | 1002 | 1002 | 4 | 5 | OMAAAA | GVDAAA | AAAAxx
+ 2 | 2716 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | MAEAAA | AAAAxx
+ 802 | 2908 | 0 | 2 | 2 | 2 | 2 | 802 | 802 | 802 | 802 | 4 | 5 | WEAAAA | WHEAAA | AAAAxx
+ 6402 | 3808 | 0 | 2 | 2 | 2 | 2 | 402 | 402 | 1402 | 6402 | 4 | 5 | GMAAAA | MQFAAA | AAAAxx
+ 8602 | 5440 | 0 | 2 | 2 | 2 | 2 | 602 | 602 | 3602 | 8602 | 4 | 5 | WSAAAA | GBIAAA | AAAAxx
+ 8402 | 5708 | 0 | 2 | 2 | 2 | 2 | 402 | 402 | 3402 | 8402 | 4 | 5 | ELAAAA | OLIAAA | AAAAxx
+ 2102 | 6184 | 0 | 2 | 2 | 2 | 2 | 102 | 102 | 2102 | 2102 | 4 | 5 | WCAAAA | WDJAAA | AAAAxx
+ 4202 | 6628 | 0 | 2 | 2 | 2 | 2 | 202 | 202 | 4202 | 4202 | 4 | 5 | QFAAAA | YUJAAA | AAAAxx
+ 2902 | 6816 | 0 | 2 | 2 | 2 | 2 | 902 | 902 | 2902 | 2902 | 4 | 5 | QHAAAA | ECKAAA | AAAAxx
+ 2302 | 7112 | 0 | 2 | 2 | 2 | 2 | 302 | 302 | 2302 | 2302 | 4 | 5 | OKAAAA | ONKAAA | AAAAxx
+ 3202 | 7128 | 0 | 2 | 2 | 2 | 2 | 202 | 1202 | 3202 | 3202 | 4 | 5 | ETAAAA | EOKAAA | AAAAxx
+ 7802 | 7508 | 0 | 2 | 2 | 2 | 2 | 802 | 1802 | 2802 | 7802 | 4 | 5 | COAAAA | UCLAAA | AAAAxx
+ 4102 | 7676 | 0 | 2 | 2 | 2 | 2 | 102 | 102 | 4102 | 4102 | 4 | 5 | UBAAAA | GJLAAA | AAAAxx
+ 8302 | 7800 | 0 | 2 | 2 | 2 | 2 | 302 | 302 | 3302 | 8302 | 4 | 5 | IHAAAA | AOLAAA | AAAAxx
+ 1702 | 7940 | 0 | 2 | 2 | 2 | 2 | 702 | 1702 | 1702 | 1702 | 4 | 5 | MNAAAA | KTLAAA | AAAAxx
+ 2202 | 8028 | 0 | 2 | 2 | 2 | 2 | 202 | 202 | 2202 | 2202 | 4 | 5 | SGAAAA | UWLAAA | AAAAxx
+ 1602 | 8148 | 0 | 2 | 2 | 2 | 2 | 602 | 1602 | 1602 | 1602 | 4 | 5 | QJAAAA | KBMAAA | AAAAxx
+ 5602 | 8796 | 0 | 2 | 2 | 2 | 2 | 602 | 1602 | 602 | 5602 | 4 | 5 | MHAAAA | IANAAA | AAAAxx
+ 6002 | 8932 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 1002 | 6002 | 4 | 5 | WWAAAA | OFNAAA | AAAAxx
+ 3902 | 9224 | 0 | 2 | 2 | 2 | 2 | 902 | 1902 | 3902 | 3902 | 4 | 5 | CUAAAA | UQNAAA | AAAAxx
+ 9602 | 9972 | 0 | 2 | 2 | 2 | 2 | 602 | 1602 | 4602 | 9602 | 4 | 5 | IFAAAA | OTOAAA | AAAAxx
+ 8002 | 9980 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 3002 | 8002 | 4 | 5 | UVAAAA | WTOAAA | AAAAxx
+ (29 rows)
+
+ -- too few params
+ EXECUTE q3('bool');
+ ERROR: Wrong number of parameters, expected 6 but got 1
+ -- too many params
+ EXECUTE q3('bytea', 5::smallint, 10.5::float, false, 500::oid, 4::bigint, true);
+ ERROR: Wrong number of parameters, expected 6 but got 7
+ -- wrong param types
+ EXECUTE q3(5::smallint, 10.5::float, false, 500::oid, 4::bigint, 'bytea');
+ ERROR: Parameter $2 of type "double precision" cannot be coerced into the expected type ("integer").
+ You will need to rewrite or cast the expression.
+ -- invalid type
+ PREPARE q4(nonexistenttype) AS SELECT $1;
+ ERROR: Type "nonexistenttype" does not exist
+ -- execute into
+ PREPARE q5(int, text) AS
+ SELECT * FROM tenk1 WHERE unique1 = $1 OR stringu1 = $2;
+ EXECUTE q5(200, 'DTAAAA') INTO TEMPORARY q5_prep_results;
+ SELECT * FROM q5_prep_results;
+ unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
+ ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
+ 2525 | 64 | 1 | 1 | 5 | 5 | 25 | 525 | 525 | 2525 | 2525 | 50 | 51 | DTAAAA | MCAAAA | AAAAxx
+ 7257 | 1895 | 1 | 1 | 7 | 17 | 57 | 257 | 1257 | 2257 | 7257 | 114 | 115 | DTAAAA | XUCAAA | VVVVxx
+ 9961 | 2058 | 1 | 1 | 1 | 1 | 61 | 961 | 1961 | 4961 | 9961 | 122 | 123 | DTAAAA | EBDAAA | OOOOxx
+ 3877 | 4060 | 1 | 1 | 7 | 17 | 77 | 877 | 1877 | 3877 | 3877 | 154 | 155 | DTAAAA | EAGAAA | AAAAxx
+ 4553 | 4113 | 1 | 1 | 3 | 13 | 53 | 553 | 553 | 4553 | 4553 | 106 | 107 | DTAAAA | FCGAAA | HHHHxx
+ 7933 | 4514 | 1 | 1 | 3 | 13 | 33 | 933 | 1933 | 2933 | 7933 | 66 | 67 | DTAAAA | QRGAAA | OOOOxx
+ 6581 | 4686 | 1 | 1 | 1 | 1 | 81 | 581 | 581 | 1581 | 6581 | 162 | 163 | DTAAAA | GYGAAA | OOOOxx
+ 8609 | 5918 | 1 | 1 | 9 | 9 | 9 | 609 | 609 | 3609 | 8609 | 18 | 19 | DTAAAA | QTIAAA | OOOOxx
+ 5229 | 6407 | 1 | 1 | 9 | 9 | 29 | 229 | 1229 | 229 | 5229 | 58 | 59 | DTAAAA | LMJAAA | VVVVxx
+ 1173 | 6699 | 1 | 1 | 3 | 13 | 73 | 173 | 1173 | 1173 | 1173 | 146 | 147 | DTAAAA | RXJAAA | VVVVxx
+ 3201 | 7309 | 1 | 1 | 1 | 1 | 1 | 201 | 1201 | 3201 | 3201 | 2 | 3 | DTAAAA | DVKAAA | HHHHxx
+ 1849 | 8143 | 1 | 1 | 9 | 9 | 49 | 849 | 1849 | 1849 | 1849 | 98 | 99 | DTAAAA | FBMAAA | VVVVxx
+ 9285 | 8469 | 1 | 1 | 5 | 5 | 85 | 285 | 1285 | 4285 | 9285 | 170 | 171 | DTAAAA | TNMAAA | HHHHxx
+ 497 | 9092 | 1 | 1 | 7 | 17 | 97 | 497 | 497 | 497 | 497 | 194 | 195 | DTAAAA | SLNAAA | AAAAxx
+ 200 | 9441 | 0 | 0 | 0 | 0 | 0 | 200 | 200 | 200 | 200 | 0 | 1 | SHAAAA | DZNAAA | HHHHxx
+ 5905 | 9537 | 1 | 1 | 5 | 5 | 5 | 905 | 1905 | 905 | 5905 | 10 | 11 | DTAAAA | VCOAAA | HHHHxx
+ (16 rows)
+
Index: src/test/regress/sql/prepare.sql
===================================================================
RCS file: src/test/regress/sql/prepare.sql
diff -N src/test/regress/sql/prepare.sql
*** /dev/null 1 Jan 1970 00:00:00 -0000
--- src/test/regress/sql/prepare.sql 23 Jul 2002 16:25:53 -0000
***************
*** 0 ****
--- 1,45 ----
+ -- Regression tests for prepareable statements
+
+ PREPARE q1 AS SELECT 1;
+ EXECUTE q1;
+
+ -- should fail
+ PREPARE q1 AS SELECT 2;
+
+ -- should succeed
+ DEALLOCATE q1;
+ PREPARE q1 AS SELECT 2;
+ EXECUTE q1;
+
+ -- sql92 syntax
+ DEALLOCATE PREPARE q1;
+
+ -- parametized queries
+ PREPARE q2(text) AS
+ SELECT datname, datdba, datistemplate, datallowconn
+ FROM pg_database WHERE datname = $1;
+ EXECUTE q2('regression');
+
+ PREPARE q3(text, int, float, boolean, oid, smallint) AS
+ SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR
+ ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int);
+
+ EXECUTE q3('AAAAxx', 5::smallint, 10.5::float, false, 500::oid, 4::bigint);
+
+ -- too few params
+ EXECUTE q3('bool');
+
+ -- too many params
+ EXECUTE q3('bytea', 5::smallint, 10.5::float, false, 500::oid, 4::bigint, true);
+
+ -- wrong param types
+ EXECUTE q3(5::smallint, 10.5::float, false, 500::oid, 4::bigint, 'bytea');
+
+ -- invalid type
+ PREPARE q4(nonexistenttype) AS SELECT $1;
+
+ -- execute into
+ PREPARE q5(int, text) AS
+ SELECT * FROM tenk1 WHERE unique1 = $1 OR stringu1 = $2;
+ EXECUTE q5(200, 'DTAAAA') INTO TEMPORARY q5_prep_results;
+ SELECT * FROM q5_prep_results;
Rod Taylor wrote:
On Tue, 2002-07-23 at 11:34, Tom Lane wrote:
nconway@klamath.dyndns.org (Neil Conway) writes:
Regarding the syntax for EXECUTE, it occurs to me that it could be made
to be more similar to the PREPARE syntax -- i.e.PREPARE foo(text, int) AS ...;
EXECUTE foo('a', 1);
(rather than EXECUTE USING -- the effect being that prepared statements
now look more like function calls on a syntactical level, which I think
is okay.)Hmm, maybe *too* much like a function call. Is there any risk of a
conflict with syntax that we might want to use to invoke stored
procedures? If not, this is fine with me.Stored procedures would use PERFORM would they not?
I like the function syntax. It looks and acts like a temporary 'sql'
function.
FWIW, Oracle uses EXECUTE to execute stored procedures. It is not apart
of the SQL language, but a SQL*Plus command:
EXECUTE my_procedure();
The Oracle call interface defines a function to call stored procedures:
OCIStmtExecute();
Likewise, the privilege necessary to execute a stored procedure is
'EXECUTE' as in:
GRANT EXECUTE ON my_procedure TO mascarm;
Again, FWIW.
Mike Mascari
mascarm@mascari.com
Mike Mascari wrote:
FWIW, Oracle uses EXECUTE to execute stored procedures. It is not apart
of the SQL language, but a SQL*Plus command:EXECUTE my_procedure();
Also with Transact SQL (i.e. MSSQL and Sybase)
Syntax
Execute a stored procedure:
[[EXEC[UTE]]
{
[@return_status =]
{procedure_name [;number] | @procedure_name_var
}
[[@parameter =] {value | @variable [OUTPUT] | [DEFAULT]]
[,...n]
[WITH RECOMPILE]
However, as Peter E. has pointed out, SQL99 uses the keyword CALL:
15.1 <call statement>
Function
Invoke an SQL-invoked routine.
Format
<call statement> ::= CALL <routine invocation>
FWIW,
Joe
To expand on the Oracle implementation, the EXECUTE command in SQL*Plus
results in an anonymous pl/sql block (as opposed to a named procedure).
being sent over the wire such as the following:
begin
my_procedure();
end;
As mentioned in the previous post, the EXECUTE command is only a
SQL*Plus keyword (well, Server Manager too but that was killed in 9i).
Mike Mascari wrote:
Show quoted text
Rod Taylor wrote:
On Tue, 2002-07-23 at 11:34, Tom Lane wrote:
nconway@klamath.dyndns.org (Neil Conway) writes:
Regarding the syntax for EXECUTE, it occurs to me that it could be made
to be more similar to the PREPARE syntax -- i.e.PREPARE foo(text, int) AS ...;
EXECUTE foo('a', 1);
(rather than EXECUTE USING -- the effect being that prepared statements
now look more like function calls on a syntactical level, which I think
is okay.)Hmm, maybe *too* much like a function call. Is there any risk of a
conflict with syntax that we might want to use to invoke stored
procedures? If not, this is fine with me.Stored procedures would use PERFORM would they not?
I like the function syntax. It looks and acts like a temporary 'sql'
function.FWIW, Oracle uses EXECUTE to execute stored procedures. It is not apart
of the SQL language, but a SQL*Plus command:EXECUTE my_procedure();
The Oracle call interface defines a function to call stored procedures:
OCIStmtExecute();
Likewise, the privilege necessary to execute a stored procedure is
'EXECUTE' as in:GRANT EXECUTE ON my_procedure TO mascarm;
Again, FWIW.
Mike Mascari
mascarm@mascari.com---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
I've two queries -
1. emrxdbs=# explain select * from patient A where exists (select NULL from
patient B where B.mrn=A.mrn and B.dob=A.dob and B.sex=A.sex and
B.lastname=A.lastname and B.firstname=A.firstname group by B.mrn, B.dob,
B.sex, B.lastname, B.firstname having A.patseq < max(B.patseq)) limit 10;
NOTICE: QUERY PLAN:
Limit (cost=0.00..121.50 rows=10 width=141)
-> Seq Scan on patient a (cost=0.00..6955296.53 rows=572430 width=141)
SubPlan
-> Aggregate (cost=6.03..6.05 rows=1 width=42)
-> Group (cost=6.03..6.05 rows=1 width=42)
-> Sort (cost=6.03..6.03 rows=1 width=42)
-> Index Scan using patient_name_idx on patient
b (cost=0.00..6.02 rows=1 width=42)
2. emrxdbs=# explain select * from patient A where exists (select NULL from
patient B where B.mrn=A.mrn and B.dob=A.dob and B.sex=A.sex and
B.lastname=A.lastname and B.firstname=A.firstname and B.mrn='3471585' group
by B.mrn, B.dob, B.sex, B.lastname, B.firstname having A.patseq <
max(B.patseq)) limit 10;
NOTICE: QUERY PLAN:
Limit (cost=0.00..121.45 rows=10 width=141)
-> Seq Scan on patient a (cost=0.00..6951997.59 rows=572430 width=141)
SubPlan
-> Aggregate (cost=6.03..6.05 rows=1 width=42)
-> Group (cost=6.03..6.04 rows=1 width=42)
-> Sort (cost=6.03..6.03 rows=1 width=42)
-> Index Scan using patient_mrnfac_idx on
patient b (cost=0.00..6.02 rows=1 width=42)
The first query results come back fairly quick, the 2nd one just sits there
forever.
It looks similar in the two query plans.
Let me know.
thanks.
johnl
On Thu, 2002-07-25 at 15:55, John Liu wrote:
I've two queries -
1. emrxdbs=# explain select * from patient A where exists (select NULL from
patient B where B.mrn=A.mrn and B.dob=A.dob and B.sex=A.sex and
B.lastname=A.lastname and B.firstname=A.firstname group by B.mrn, B.dob,
B.sex, B.lastname, B.firstname having A.patseq < max(B.patseq)) limit 10;
NOTICE: QUERY PLAN:Limit (cost=0.00..121.50 rows=10 width=141)
-> Seq Scan on patient a (cost=0.00..6955296.53 rows=572430 width=141)
SubPlan
-> Aggregate (cost=6.03..6.05 rows=1 width=42)
-> Group (cost=6.03..6.05 rows=1 width=42)
-> Sort (cost=6.03..6.03 rows=1 width=42)
-> Index Scan using patient_name_idx on patient
b (cost=0.00..6.02 rows=1 width=42)2. emrxdbs=# explain select * from patient A where exists (select NULL from
patient B where B.mrn=A.mrn and B.dob=A.dob and B.sex=A.sex and
B.lastname=A.lastname and B.firstname=A.firstname and B.mrn='3471585' group
by B.mrn, B.dob, B.sex, B.lastname, B.firstname having A.patseq <
max(B.patseq)) limit 10;
NOTICE: QUERY PLAN:Limit (cost=0.00..121.45 rows=10 width=141)
-> Seq Scan on patient a (cost=0.00..6951997.59 rows=572430 width=141)
SubPlan
-> Aggregate (cost=6.03..6.05 rows=1 width=42)
-> Group (cost=6.03..6.04 rows=1 width=42)
-> Sort (cost=6.03..6.03 rows=1 width=42)
-> Index Scan using patient_mrnfac_idx on
patient b (cost=0.00..6.02 rows=1 width=42)The first query results come back fairly quick, the 2nd one just sits there
forever.
It looks similar in the two query plans.
It seems that using patient_mrnfac_idx instead of patient_name_idx is
not a good choice in your case ;(
try moving the B.mrn='3471585' from FROM to HAVING and hope that this
makes the DB use the same plan as for the first query
select *
from patient A
where exists (
select NULL
from patient B
where B.mrn=A.mrn
and B.dob=A.dob
and B.sex=A.sex
and B.lastname=A.lastname
and B.firstname=A.firstname
group by B.mrn, B.dob, B.sex, B.lastname, B.firstname
having A.patseq < max(B.patseq)
and B.mrn='3471585'
) limit 10;
-----------
Hannu
Neil Conway writes:
Regarding the syntax for EXECUTE, it occurs to me that it could be made
to be more similar to the PREPARE syntax -- i.e.PREPARE foo(text, int) AS ...;
EXECUTE foo('a', 1);
(rather than EXECUTE USING -- the effect being that prepared statements
now look more like function calls on a syntactical level, which I think
is okay.)
I'm not sure I like that. It seems too confusing. Why not keep it as the
standard says? (After all, it is the PREPARE part that we're adjusting,
not EXECUTE.)
--
Peter Eisentraut peter_e@gmx.net
On Thu, Jul 25, 2002 at 10:54:04PM +0200, Peter Eisentraut wrote:
I'm not sure I like that. It seems too confusing. Why not keep
it as the standard says? (After all, it is the PREPARE part that
we're adjusting, not EXECUTE.)
I think it's both, isn't it? My understanding of Tom's post is that the
features described by SQL92 are somewhat similar to the patch, but not
directly related.
On the other hand, if other people also find it confusing, that would be
a good justification for changing it. Personally, I think it's pretty
clear, but I'm not adamant about it.
Cheers,
Neil
--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC
Neil Conway writes:
On Thu, Jul 25, 2002 at 10:54:04PM +0200, Peter Eisentraut wrote:
I'm not sure I like that. It seems too confusing. Why not keep
it as the standard says? (After all, it is the PREPARE part that
we're adjusting, not EXECUTE.)I think it's both, isn't it? My understanding of Tom's post is that the
features described by SQL92 are somewhat similar to the patch, but not
directly related.
What I was trying to say is this: There is one "prepared statement"
facility in the standards that allows you to prepare a statement defined
in a host variable, whereas you are proposing one that specifies the
statement explicitly. However, both of these are variants of the same
concept, so the EXECUTE command doesn't need to be different.
--
Peter Eisentraut peter_e@gmx.net