Hi:
Attached is a loadable module (and a small backend patch) that allows a client to list the available query plans (created with PREPARE, or with a 'parse' protocol message) on a particular connection.
This work was done in response to an item in the TODO:
* Allow pooled connections to list all prepared queries
This would allow an application inheriting a pooled connection to know the queries prepared in the current session.
I've done the following:
* Extended PostgreSQL's SQL grammar to capture the original SQL query string for PREPARE statements. Previously, the PostgreSQL kernel provided access to a prepared query's original SQL, but only for statements prepared with a libpq "parse" message - not with a PREPARE statement.
* Modified backend/commands/prepare.c to keep some additional statistics in the prepared statement hash table (plan creation time, execution count, etc.)
* Added an accessor function to allow for "raw" access to the prepared statement hash table (necessary for sequential access).
* Implemented a PostgreSQL function to list the available query plans on the current connection. This function, called pg_prepared_query_plans, returns a set of tuples, each of which contain a plan name, the SQL query string associated with the plan name, the number of times the plan has been executed, the plan creation time, and the plan's last access time.
This should provide a way for clients sharing a connection pool to also share prepared query plans. When a client inherits a connection from the pool, it can use the results of a 'select * from pg_prepared_query_plans()' to fill a (sql -> plan_name) hash table. By probing this hash table before executing a PREPARE, duplicate PREPAREs can be skipped, even if the initial PREPARE was performed by a different client.
I've attached three files: one is a diff against the backend, the other two are the loadable module (source + create script).
If anyone is interested, I've also attached a small proof-of-concept patch for DBD::Pg - it does server-side plan caching as described above (by leaving the prepared plans on the connection at disconnect, and filling a hash with the list of prepared plans at connect), and uses a simple LRU deallocation policy ($ENV{'PLANCACHE_MAX'} is the high watermark, and $ENV{'PLANCACHE_REAP'} is the number of plans below the high watermark to target when deallocating - both should be set prior to DBI->connect).
All of this was done while experimenting with plan caching for a database systems course. I have a more detailed write-up (with some synthetic benchmarks) if it would be helpful.
HTH,
- Dave
diff --exclude Makefile.global --exclude pg_config.h --exclude gram.c --exclude
scan.c --exclude parse.h -ur
postgresql-7.4.5-dist/src/backend/commands/prepare.c
postgresql-7.4.5/src/backend/commands/prepare.c
--- postgresql-7.4.5-dist/src/backend/commands/prepare.c 2003-08-08
14:41:32.000000000 -0700
+++ postgresql-7.4.5/src/backend/commands/prepare.c 2004-12-01
17:57:57.000000000 -0800
@@ -16,6 +16,7 @@
*/
#include "postgres.h"
+#include <sys/time.h>
#include "commands/explain.h"
#include "commands/prepare.h"
#include "executor/executor.h"
@@ -25,7 +26,6 @@
#include "tcop/pquery.h"
#include "tcop/tcopprot.h"
#include "tcop/utility.h"
-#include "utils/hsearch.h"
#include "utils/memutils.h"
@@ -95,7 +95,7 @@
/* Save the results. */
StorePreparedStatement(stmt->name,
- NULL, /* text form
not available */
+ stmt->query_string,
commandTag,
query_list,
plan_list,
@@ -195,6 +195,9 @@
if (estate)
FreeExecutorState(estate);
+ /* Completed: update execution statistics */
+ entry->exec_count++;
+
/* No need to pfree other memory, MemoryContext will be reset */
}
@@ -285,12 +288,14 @@
List *argtype_list)
{
PreparedStatement *entry;
+ struct timeval create_time;
MemoryContext oldcxt,
entrycxt;
char *qstring;
char key[NAMEDATALEN];
bool found;
+
/* Initialize the hash table, if necessary */
if (!prepared_queries)
InitQueryHashTable();
@@ -327,6 +332,9 @@
query_list = (List *) copyObject(query_list);
plan_list = (List *) copyObject(plan_list);
argtype_list = listCopy(argtype_list);
+
+ if (gettimeofday(&create_time, NULL) != 0)
+ elog(ERROR, "failed to read current system time");
/* Now we can add entry to hash table */
entry = (PreparedStatement *) hash_search(prepared_queries,
@@ -346,6 +354,8 @@
entry->plan_list = plan_list;
entry->argtype_list = argtype_list;
entry->context = entrycxt;
+ entry->create_time = entry->access_time = create_time;
+ entry->exec_count = 0;
MemoryContextSwitchTo(oldcxt);
}
@@ -388,6 +398,9 @@
errmsg("prepared statement \"%s\" does not
exist",
stmt_name)));
+ if (entry && gettimeofday(&entry->access_time, NULL) != 0)
+ elog(ERROR, "failed to read current system time");
+
return entry;
}
@@ -434,6 +447,17 @@
}
/*
+ * Return a pointer to the hash table of prepared statements.
+ * This should be used by callers needing low-level access to
+ * the list of PreparedStatement structs (e.g. to list them).
+ */
+HTAB *
+FetchPreparedStatementHtab(void)
+{
+ return prepared_queries;
+}
+
+/*
* Implements the 'DEALLOCATE' utility statement: deletes the
* specified plan from storage.
*/
@@ -557,4 +581,7 @@
if (estate)
FreeExecutorState(estate);
+
+ /* Completed: update execution statistics */
+ entry->exec_count++;
}
diff --exclude Makefile.global --exclude pg_config.h --exclude gram.c --exclude
scan.c --exclude parse.h -ur postgresql-7.4.5-dist/src/backend/parser/gram.y
postgresql-7.4.5/src/backend/parser/gram.y
--- postgresql-7.4.5-dist/src/backend/parser/gram.y 2003-11-24
08:54:15.000000000 -0800
+++ postgresql-7.4.5/src/backend/parser/gram.y 2004-12-21 09:19:59.000000000
-0800
@@ -66,6 +66,8 @@
extern List *parsetree; /* final parse result is
delivered here */
+extern char *base_yytext;
+
static bool QueryIsRule = FALSE;
/*
@@ -223,7 +225,7 @@
opt_select_limit opclass_item_list
transaction_mode_list
transaction_mode_list_or_empty
TableFuncElementList
- prep_type_clause prep_type_list
+ prep_as prep_type_clause prep_type_list
execute_param_clause
%type <range> into_clause OptTempTableName
@@ -4096,16 +4098,43 @@
*
*****************************************************************************/
-PrepareStmt: PREPARE name prep_type_clause AS PreparableStmt
+PrepareStmt: PREPARE name prep_type_clause prep_as PreparableStmt
{
PrepareStmt *n = makeNode(PrepareStmt);
n->name = $2;
n->argtypes = $3;
+ n->query_string = (char *) $4;
n->query = (Query *) $5;
$$ = (Node *) n;
}
;
+prep_as: AS
+ {
+ /* Save original query string:
+ We want to save the string
representation of the
+ prepared query - that is, the string
following 'AS' in
+ the PREPARE statement. We reach into
the lexographic
+ analyzer's scan buffer to do this.
+
+ In flex(1), $yytext is a pointer into
$scanbuf. As
+ each token is processed, flex inserts a
null terminator
+ into $scanbuf - this separates $scanbuf
into two halves,
+ the left half consisting of
already-processed
+ characters, and the right half
consisting of yet-to-be
+ processed characters. Taken by itself,
$yytext points to
+ the start of the current token in
$scanbuf; the null
+ terminator of $yytext is the same null
terminator that
+ divides $scanbuf.
+
+ We want the right half of $scanbuf. */
+
+ char *p = base_yytext + strlen(base_yytext) + 1;
+ while (isspace(*p)) ++p;
+ $$ = (void *) pstrdup(p);
+ }
+ ;
+
prep_type_clause: '(' prep_type_list ')' { $$ = $2; }
| /* EMPTY */ { $$ =
NIL; }
;
diff --exclude Makefile.global --exclude pg_config.h --exclude gram.c --exclude
scan.c --exclude parse.h -ur
postgresql-7.4.5-dist/src/include/commands/prepare.h
postgresql-7.4.5/src/include/commands/prepare.h
--- postgresql-7.4.5-dist/src/include/commands/prepare.h 2003-08-08
14:42:40.000000000 -0700
+++ postgresql-7.4.5/src/include/commands/prepare.h 2004-12-01
16:26:34.000000000 -0800
@@ -16,6 +16,7 @@
#include "executor/executor.h"
#include "nodes/parsenodes.h"
#include "tcop/dest.h"
+#include "utils/hsearch.h"
/*
@@ -37,8 +38,11 @@
List *plan_list; /* list of plans */
List *argtype_list; /* list of parameter type OIDs */
MemoryContext context; /* context containing this query */
-} PreparedStatement;
+ unsigned long exec_count; /* number of successful executions */
+ struct timeval create_time; /* wall time at which entry was created
*/
+ struct timeval access_time; /* wall time at which entry was last
accessed */
+} PreparedStatement;
/* Utility statements PREPARE, EXECUTE, DEALLOCATE, EXPLAIN EXECUTE */
extern void PrepareQuery(PrepareStmt *stmt);
@@ -59,4 +63,7 @@
extern List *FetchPreparedStatementParams(const char *stmt_name);
extern TupleDesc FetchPreparedStatementResultDesc(PreparedStatement *stmt);
+/* Low-level access to the prepared statement hash table */
+extern HTAB *FetchPreparedStatementHtab(void);
+
#endif /* PREPARE_H */
diff --exclude Makefile.global --exclude pg_config.h --exclude gram.c --exclude
scan.c --exclude parse.h -ur
postgresql-7.4.5-dist/src/include/nodes/parsenodes.h
postgresql-7.4.5/src/include/nodes/parsenodes.h
--- postgresql-7.4.5-dist/src/include/nodes/parsenodes.h 2003-09-16
21:25:29.000000000 -0700
+++ postgresql-7.4.5/src/include/nodes/parsenodes.h 2004-12-01
16:20:53.000000000 -0800
@@ -1670,6 +1670,7 @@
List *argtypes; /* Types of parameters (TypeNames) */
List *argtype_oids; /* Types of parameters (OIDs) */
Query *query; /* The query itself */
+ char *query_string; /* The query's string representation */
} PrepareStmt;
#include <unistd.h> #include <time.h>
#include "postgres.h"
#include "fmgr.h"
#include "funcapi.h"
#include "commands/prepare.h"
/*
format_timeval:
Write a string representation of $tv into $buf,
writing at most $max bytes. Return the number of
bytes written, excluding the null terminator. On
failure, return a negative value.
*/
static int format_timeval(char *buf, const size_t max,
const struct timeval *const tv)
{
struct tm tm;
size_t ret, s;
if (!gmtime_r(&(tv->tv_sec), &tm))
return -1;
if ((ret = strftime(buf, max, "%F %H:%M:%S", &tm)) < 0)
return -1;
/* Overwrite strftime's null terminator:
The ($buf += $ret) and ($max - $ret) are correct: $ret is the number
of characters written by strftime *excluding* the null terminator.
Furthermore, we don't need to check for overflow here - it is
guaranteed by strftime that (ret < max). In the extreme case of
(ret = max - 1), (max - ret = 1), (buf + ret = buf + max - 1), and
(buf[ret] = buf[max - 1] = '\0'), so snprintf simply overwrites the
null terminator and returns. */
if ((s = snprintf(buf + ret, max - ret, ".%.3lu UTC", tv->tv_usec / 1000)) <
0)
return -1;
return (ret + s);
}
/*
pg_prepared_query_plans:
This function returns a set of records which, collectively,
represent the state of the $prepared_queries hash table.
*/
PG_FUNCTION_INFO_V1(pg_prepared_query_plans);
Datum pg_prepared_query_plans(PG_FUNCTION_ARGS)
{
TupleDesc tupdesc;
TupleTableSlot *slot;
AttInMetadata *attinmeta;
FuncCallContext *funcctx;
PreparedStatement *e;
HASH_SEQ_STATUS *hash_status;
HTAB *prepared_queries;
/* First call:
Set-returning functions act as forward iterators - they are called
repeatedly, with each call returning a successive member of the
set. If this is the first call, we need to set up our hash table
iterator and gather the type information for our return tuple. */
if (SRF_IS_FIRSTCALL())
{
MemoryContext old_context;
funcctx = SRF_FIRSTCALL_INIT();
/* Locate prepared_queries hash table:
Use a function from command/prepare.h to get a pointer to the
prepared queries hash table. If the table hasn't been allocated
yet, no prepares have been issued yet, and we have nothing to do. */
prepared_queries = FetchPreparedStatementHtab();
if (!prepared_queries)
SRF_RETURN_DONE(funcctx);
/* Switch memory context:
By default, each function invocation gets an ephermeral memory pool
which is freed upon its return. In order for us to allocate memory
that will persist across multiple function invocations, we need to
allocate from a different pool. Postgres provides one for us. */
old_context = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
/* Build tuple description and return slot:
To return a composite type (e.g. a tuple), we need to fetch type
information for the composite type, and allocate a return "slot"
for the composite type instance to be placed in after it is built.
We save the slot, as well as the composite type metadata - we need
the latter to build the instance, and the former to return it. */
tupdesc = RelationNameGetTupleDesc("pg_prepared_query_plan");
attinmeta = TupleDescGetAttInMetadata(tupdesc);
funcctx->attinmeta = attinmeta;
slot = TupleDescGetSlot(tupdesc);
funcctx->slot = slot;
/* Set up hash table iterator:
The hash table iterator is the only state we need to keep across
function calls. We store this in the provided "user context"
pointer of Postgres' function call context structure. */
hash_status = (HASH_SEQ_STATUS *) palloc(sizeof(HASH_SEQ_STATUS));
hash_seq_init(hash_status, prepared_queries);
funcctx->user_fctx = (void *) hash_status;
/* Switch back:
The remainder of memory allocations will use the ephemeral pool. */
MemoryContextSwitchTo(old_context);
}
/* Per-call setup:
For every call, pull our hash iterator and tuple description out of
the function call context - we need them to return results. */
funcctx = SRF_PERCALL_SETUP();
slot = funcctx->slot;
attinmeta = funcctx->attinmeta;
hash_status = (HASH_SEQ_STATUS *) funcctx->user_fctx;
/* Finally, return a result:
Iterate forward one hash entry, format a tuple, and return it. */
if ((e = (PreparedStatement *) hash_seq_search(hash_status)) != NULL)
{
char **values;
HeapTuple tuple;
Datum result;
values = (char **) palloc(5 * sizeof(char *));
values[0] = e->stmt_name;
values[1] = e->query_string;
values[2] = palloc(16 * sizeof(char));
snprintf(values[2], 16, "%lu", e->exec_count);
values[3] = palloc(32 * sizeof(char));
if (format_timeval(values[3], 32, &e->create_time) < 0)
elog(ERROR, "unable to format create_time for output");
values[4] = palloc(32 * sizeof(char));
if (format_timeval(values[4], 32, &e->access_time) < 0)
elog(ERROR, "unable to format access_time for output");
/* Create tuple */
tuple = BuildTupleFromCStrings(attinmeta, values);
/* Bind to return slot */
result = TupleGetDatum(slot, tuple);
/* Don't free $values:
The memory pool cleanup will take care of this automatically. */
SRF_RETURN_NEXT(funcctx, result);
}
SRF_RETURN_DONE(funcctx);
}
drop type pg_prepared_query_plan cascade; create type pg_prepared_query_plan as ( plan_name varchar, plan_sql varchar, exec_count integer, create_time timestamp with time zone, access_time timestamp with time zone ); create or replace function pg_prepared_query_plans() returns setof pg_prepared_query_plan as 'prepare' language c immutable strict;
Only in DBD-Pg-1.32-dist: Makefile.old
diff -ur DBD-Pg-1.32-dist/Pg.pm DBD-Pg-1.32/Pg.pm
--- DBD-Pg-1.32-dist/Pg.pm 2004-02-25 10:25:52.000000000 -0800
+++ DBD-Pg-1.32/Pg.pm 2004-12-12 17:56:15.000000000 -0800
@@ -145,7 +145,15 @@
});
# Connect to the database..
- DBD::Pg::db::_login($dbh, $dbname, $user, $auth) or return
undef;
+ DBD::Pg::db::_login($dbh, $dbname, $user, $auth) or return
undef;
+
+ # Fill list of prepared query plans:
+ # This initializes the per-dbh plan cache, by
+ # asking Postgres for a list of existing query plans.
+
+ DBD::Pg::db::_set_plancache_max($dbh, $ENV{'PLANCACHE_MAX'});
+ DBD::Pg::db::_set_plancache_reap($dbh, $ENV{'PLANCACHE_REAP'});
+ DBD::Pg::db::_init_plancache($dbh) or return undef;
$dbh;
}
@@ -158,21 +166,248 @@
use strict;
use Carp ();
+ # plancache:
+ # This hash maps sql statements to plan cache entries,
+ # avoiding the need to query pg_prepared_query_plans()
+ # before each and every prepare.
+
+ my %plancache;
+
+ # plancache_max, plancache_reap:
+ # Tuning knobs for the simple server-side LRU code. The
+ # $plancache_max setting is the maximum number of cached
+ # plans that may exist. The $plancache_reap setting
+ # is the number of entries (below $plancache_max) that an
+ # invocation of the LRU code will try to remove.
+
+ my $plancache_max;
+ my $plancache_reap;
+
+ # quote_ident:
+ # Quote an identifier. A quoted identifier refers to an object
+ # (e.g. column, table), and can contain any literal character
except
+ # for a double quote (`"'). Double quotes must be escaped with a
+ # double quote (e.g. `"' -> `""'). This is a direct analogue of
the
+ # server-side quote_ident() function. See section 4.1.1 of the
+ # PostgreSQL manual for more information.
+
+ sub quote_ident {
+ my ($dbh, $s) = @_;
+ $s =~ s/"/""/g;
+ return "\"$s\"";
+ }
+
+ # _prepare_with_plancache:
+ # Call _prepare. If the there is a cached plan that is
+ # suitable, tell _prepare to use it. Otherwise, call _prepare
+ # without specifying a plan (a new one will be created).
+
+ sub _prepare_with_plancache {
+ my ($dbh, $sth, $stmt, @attribs) = @_;
+ my $hr, my $plan, my $atime;
+
+ # Check cache
+ if ($hr = $plancache{$stmt}) {
+ $plan = $$hr{'plan_name'};
+ }
+
+ # Execute, possibly with cached plan
+ ($plan = DBD::Pg::st::_prepare($sth, $stmt, $plan, @attribs))
+ or return undef;
+
+ # Update cache:
+ # Only do this if the statement qualified for a
server-side prepare.
+
+ if ($plan != 1) {
+ $$hr{'plan_name'} = $plan;
+ $plancache{$stmt} = $hr;
+ }
+
+ $sth;
+ }
+
+ # _init_plancache:
+ # Get information about the prepared statements on the
+ # current connection. Place this information in the
+ # %plancache hash, destroying what was there before.
+ #
+ # Successive calls to prepare() can use the information
+ # in this hash to determine whether or not a particular
+ # statement has already been prepared on this connection.
+ # Usage of an existing plan eliminates lots of parsing
+ # and query optimization work that would otherwise be
+ # duplicated.
+ #
+ # This initialization is helpful only in conjunction with
+ # an external pools or load balancer. In all other cases
+ # (notably Apache::DBI), the DBD::Pg::st object travels
+ # with the underlying connection socket.
+
+ sub _init_plancache {
+ my ($dbh) = @_;
+ my $stmt = 'select * from pg_prepared_query_plans()';
+
+ my $sth = DBI::_new_sth($dbh, {'Statement' => $stmt});
+
+ undef %plancache;
+
+ _prepare_with_plancache($dbh, $sth, $stmt)
+ or return undef;
+
+ $sth->execute()
+ or return undef;
+
+ while ((my $hr = $sth->fetchrow_hashref())) {
+ $$hr{'plan_name'} = quote_ident($dbh,
$$hr{'plan_name'});
+ $plancache{$$hr{'plan_sql'}} = $hr;
+ }
+
+ return 1;
+ }
+
+ # _reap_plancache_single:
+ # Remove $stmt from the client-side and server-side
+ # query caches.
+
+ sub _reap_plancache_single {
+ my ($dbh, $stmt) = @_;
+
+ my $d_stmt = 'deallocate prepare ' .
+
$plancache{$stmt}{'plan_name'};
+
+ my $d_sth = DBI::_new_sth($dbh, {'Statement' => $d_stmt});
+
+ # Ineligible for server-side preparation:
+ # A deallocation will never generate a server-side query
+ # plan - even if we pass it to _prepare_with_plancache.
+ # To be explicit about this, we call _prepare directly.
+
+ DBD::Pg::st::_prepare($d_sth, $d_stmt)
+ or return undef;
+
+ $d_sth->execute()
+ or return undef;
+
+ # Remove from client-side cache
+ delete $plancache{$stmt};
+
+ return 1;
+ }
+
+ # _reap_plancache_lru:
+ # Deallocate the $count least frequently used prepared
+ # statements from the server's query plan cache. While
+ # the LRU calculation could be done on the client side,
+ # it'd involve keeping a seperate data structure to
+ # index %plancache by access_time, which would need to
+ # be updated from prepare - this is more code, impacts the
+ # fast path, and may not be appreciably faster than just
+ # doing the calculation on the server (save for cases of
+ # cache ping-pong, which we want to avoid altogether,
+ # rather than optimize).
+
+ sub _reap_plancache_lru {
+ my ($dbh, $count) = @_;
+
+ if ($count <= 0)
+ { return 1; }
+
+ my $stmt = 'select * from pg_prepared_query_plans()' .
+ ' order by access_time
asc';
+
+ my $sth = DBI::_new_sth($dbh, {'Statement' => $stmt});
+
+ _prepare_with_plancache($dbh, $sth, $stmt)
+ or return undef;
+
+ $sth->execute()
+ or return undef;
+
+ while (($count--) && (my $hr = $sth->fetchrow_hashref())) {
+ _reap_plancache_single($dbh, $$hr{'plan_sql'});
+ }
+
+ return 1;
+ }
+
+ # _reap_plancache_full:
+ # Deallocate the entire plan cache.
+
+ sub _reap_plancache_full {
+ my ($dbh) = @_;
+
+ foreach my $sql (keys %plancache) {
+ _reap_plancache_single($dbh, $sql);
+ }
+ }
+
+ # _rewrite_prepare_statement:
+ # Perform some rule-based rewriting of queries.
+ # This could include whitespace elimination, placeholder
+ # reordering, and other transformations.
+
+ sub _rewrite_prepare_statement {
+ my($dbh, $stmt, @attribs)= @_;
+ return $stmt;
+ }
+
+ # _set_plancache_max:
+ # Set the maximum number of statements that can be in the
+ # cache at any particular time. Returns the old value.
+
+ sub _set_plancache_max {
+ my($dbh, $max)= @_;
+
+ my $old_max = $plancache_max;
+ $plancache_max = ($max > 0 ? $max : 0);
+
+ return $old_max;
+ }
+
+ # _set_plancache_reap:
+ # Set the number of statements that will be deallocated
+ # in a single run of _reap_plancache_lru.
+
+ sub _set_plancache_reap {
+ my($dbh, $count)= @_;
+
+ my $old_reap = $plancache_reap;
+ $plancache_reap = ($count > 0 ? $count : 0);
+
+ return $old_reap;
+ }
+
+ # prepare:
+ # API function. See documentation.
+
sub prepare {
- my($dbh, $statement, @attribs)= @_;
+ my($dbh, $stmt, @attribs)= @_;
- # create a 'blank' sth
+ # Perform rule-based statement rewriting
+ $stmt = _rewrite_prepare_statement($dbh, $stmt);
- my $sth = DBI::_new_sth($dbh, {
- 'Statement' => $statement,
- });
+ # Invoke LRU code:
+ # If the plan cache is "disabled", trash all prepared
statements.
+ # Otherwise, use the server-side LRU algorithm to prune
old ones.
+
+ if ($plancache_max == 0) {
+ _reap_plancache_full($dbh);
+ } else {
+ if ((my $cur = scalar(keys(%plancache))) >=
$plancache_max) {
+ _reap_plancache_lru($dbh, $cur - $plancache_max
+ $plancache_reap)
+ or return undef;
+ }
+ }
+
+ # Prepare statement
+ my $sth = DBI::_new_sth($dbh, {'Statement' => $stmt});
- DBD::Pg::st::_prepare($sth, $statement, @attribs) or return
undef;
+ _prepare_with_plancache($dbh, $sth, $stmt, @attribs)
+ or return undef;
$sth;
}
-
sub ping {
my($dbh) = @_;
local $SIG{__WARN__} = sub { } if $dbh->{PrintError};
diff -ur DBD-Pg-1.32-dist/Pg.xsi DBD-Pg-1.32/Pg.xsi
--- DBD-Pg-1.32-dist/Pg.xsi 2004-12-05 11:16:03.000000000 -0800
+++ DBD-Pg-1.32/Pg.xsi 2004-12-05 10:28:21.000000000 -0800
@@ -403,15 +403,24 @@
void
-_prepare(sth, statement, attribs=Nullsv)
+_prepare(sth, statement, force_plan = Nullsv, attribs = Nullsv)
SV * sth
char * statement
+ SV * force_plan
SV * attribs
CODE:
{
+ STRLEN lna;
D_imp_sth(sth);
DBD_ATTRIBS_CHECK("_prepare", sth, attribs);
- ST(0) = dbd_st_prepare(sth, imp_sth, statement, attribs) ? &sv_yes :
&sv_no;
+
+ if (force_plan)
+ imp_sth->plan = (SvOK(force_plan) ? SvPV(force_plan, lna) : NULL);
+
+ if (dbd_st_prepare(sth, imp_sth, statement, attribs))
+ ST(0) = (imp_sth->plan ? newSVpv(imp_sth->plan, 0) : &sv_yes);
+ else
+ ST(0) = &sv_no;
}
diff -ur DBD-Pg-1.32-dist/dbdimp.c DBD-Pg-1.32/dbdimp.c
--- DBD-Pg-1.32-dist/dbdimp.c 2004-02-03 11:50:22.000000000 -0800
+++ DBD-Pg-1.32/dbdimp.c 2004-12-11 07:48:55.000000000 -0800
@@ -382,7 +382,7 @@
/* We assume that disconnect will always work */
/* since most errors imply already disconnected.
* XXX: Um we turn active off, then return 0 on a rollback failing?
- * Check to see what happenens -- will we leak memory? :rl
+ * Check to see what happens -- will we leak memory? :rl
*/
DBIc_ACTIVE_off(imp_dbh);
@@ -538,9 +538,9 @@
if (dbis->debug >= 1) { PerlIO_printf(DBILOGFP, "dbd_st_prepare:
statement = >%s<\n", statement); }
/* scan statement for '?', ':1' and/or ':foo' style placeholders */
- if((dbd_preparse(sth, imp_sth, statement)) == 0)
+ if((dbd_preparse(sth, imp_sth, statement, NULL)) == 0)
return 0;
-
+
if (is_tx_stmt(statement)) {
warn("please use DBI functions for transaction handling");
return(0);
@@ -562,19 +562,23 @@
const char *statement;
{
static unsigned int prep_stmt_id = 0;
+ static char prep_stmt_str[48];
int place_holder_count, stmt_len, status;
- int digits, i;
+ int plan_strlen = 0, i;
int offset = 0;
D_imp_dbh_from_sth;
-
- ++prep_stmt_id;
- digits = 0;
- i = prep_stmt_id;
- do {
- ++digits;
- i /=10;
- } while (i>0); /* 12*/
-
+ char *plan;
+
+ if (imp_sth->plan) {
+ plan = imp_sth->plan;
+ plan_strlen = strlen(plan);
+ } else {
+ ++prep_stmt_id;
+ plan_strlen = snprintf(prep_stmt_str, 48,
+ "\"DBD::ChurlPg::cached_query %i\"",
prep_stmt_id);
+ plan = prep_stmt_str;
+ }
+
/* //PerlIO_printf(DBILOGFP, "Statement: %s \n", statement); */
prescan_stmt(statement, &stmt_len, &place_holder_count);
@@ -582,11 +586,11 @@
/* add space for placeholders candidates */
stmt_len += calc_ph_space(place_holder_count);
-
- offset += strlen ("PREPARE \"DBD::ChurlPg::cached_query \" (");
- offset += digits; /* number of digits in prep_statement_id */
+ offset += strlen ("PREPARE ");
+ offset += strlen(" ()");
+ offset += plan_strlen;
offset += place_holder_count*strlen("varchar, ");
- offset += strlen(") AS");
+ offset += strlen(" AS ");
stmt_len += offset;
++stmt_len; /* for term \0 */
@@ -613,23 +617,27 @@
server side prepare this statement TODO: remalloc*/
if (!is_dml(imp_sth->statement+offset) || imp_dbh->version.ver < 7.3)
return 1;
-
- /* 1 == PREPARE -- TODO: Fix ugly number thing*/
- build_preamble(imp_sth->statement, 1, place_holder_count, prep_stmt_id);
-
- /* //PerlIO_printf(DBILOGFP, "Rewritten stmt: %s\n",
imp_sth->statement); */
-
- imp_sth->result = PQexec(imp_dbh->conn, imp_sth->statement);
- status = imp_sth->result ? PQresultStatus(imp_sth->result) : -1;
- if (status != PGRES_COMMAND_OK) {
- pg_error(sth,status, PQerrorMessage(imp_dbh->conn));
- return 0;
+
+ if (!imp_sth->plan) {
+ /* 1 == PREPARE -- TODO: Fix ugly number thing*/
+ build_preamble(imp_sth->statement, 1, place_holder_count, plan);
+
+ imp_sth->result = PQexec(imp_dbh->conn, imp_sth->statement);
+ status = imp_sth->result ? PQresultStatus(imp_sth->result) : -1;
+
+ if (status != PGRES_COMMAND_OK) {
+ pg_error(sth,status, PQerrorMessage(imp_dbh->conn));
+ return 0;
+ }
+ if (imp_sth->result)
+ PQclear(imp_sth->result);
+
+ /* Return dynamically-generated plan name */
+ imp_sth->plan = plan;
}
- if (imp_sth->result)
- PQclear(imp_sth->result);
-
+
/* 2 == EXECUTE -- TODO: Fix ugly number thing & remalloc*/
- build_preamble(imp_sth->statement, 2, place_holder_count, prep_stmt_id);
+ build_preamble(imp_sth->statement, 2, place_holder_count, plan);
/* //PerlIO_printf(DBILOGFP, "Rewritten stmt: %s\n",
imp_sth->statement); */
imp_sth->server_prepared = 1;
@@ -1116,14 +1124,15 @@
{
if (dbis->debug >= 1) { PerlIO_printf(DBILOGFP, "dbd_st_destroy\n"); }
- /* Free off contents of imp_sth */
-
+ /* Free off contents of imp_sth:
+ Skip this. We want prepared statements to remain in the cache.
*/
+#if 0
if (imp_sth->server_prepared)
if (deallocate_statement(sth, imp_sth) < 1)
warn("Something Ugly Happened. And whatever it was, it
caused"
"us not to be able to deallocate the
prepared statement. "
"Prolly a tx went bad or something
like that");
-
+#endif
Safefree(imp_sth->statement);
if (imp_sth->place_holders)
Safefree(imp_sth->place_holders);
diff -ur DBD-Pg-1.32-dist/dbdimp.h DBD-Pg-1.32/dbdimp.h
--- DBD-Pg-1.32-dist/dbdimp.h 2004-01-21 15:47:18.000000000 -0800
+++ DBD-Pg-1.32/dbdimp.h 2004-12-05 10:24:59.000000000 -0800
@@ -63,6 +63,7 @@
/* Input Details */
char *statement; /* sql (see sth_scan) */
+ char *plan; /* existing plan name to use, or null to use counter */
HV *all_params_hv; /* all params, keyed by name */
bool server_prepared; /* Did we prepare this server side?*/
diff -ur DBD-Pg-1.32-dist/prescan_stmt.c DBD-Pg-1.32/prescan_stmt.c
--- DBD-Pg-1.32-dist/prescan_stmt.c 2004-01-13 08:59:26.000000000 -0800
+++ DBD-Pg-1.32/prescan_stmt.c 2004-12-11 07:48:42.000000000 -0800
@@ -1,5 +1,5 @@
/*******************
- * pre_scan_stmt()
+ * prescan_stmt()
* returns the length of the statement and
* an estimate of how many place holders it contains.
*/
@@ -28,7 +28,7 @@
/*******************
- * clc_ph_space()
+ * calc_ph_space()
* givin a place_holder count, retuns the
* string space needed to hold them.
*/
@@ -56,7 +56,7 @@
/*******************
* is_dml()
- * givin a statement/fragment makes a guess as to whether
+ * given a statement/fragment makes a guess as to whether
* it be a DML statement
*/
@@ -81,10 +81,7 @@
as they actually look at the field type. Until I get a fix for this
we don't prepare them
*/
- if (0/* !strcasecmp(token, "SELECT")
- || !strcasecmp(token, "DELETE") */
- /*|| !strcasecmp(token, "UPDATE")
- || !strcasecmp(token, "INSERT")*/ )
+ if (!strcasecmp(token, "SELECT") || !strcasecmp(token, "DELETE"))
{
/* //PerlIO_printf(DBILOGFP, "Is DML\n"); */
return 1;
@@ -135,7 +132,7 @@
/*******************
- * scan_placeholders()
+ * rewrite_placeholders()
* old preparse. this one takes a statement and sets up
* the place holder SV*
*/
@@ -165,27 +162,7 @@
src = statement;
dest = internal;
- /* // PerlIO_printf(DBILOGFP, "HERE: stmt: %s\n", src); */
while ((ch = *src++)) {
- if (in_comment) {
- /* SQL-style and C++-style */
- if ((in_comment == '-' || in_comment == '/') &&
- '\n' == ch)
- {
- in_comment = '\0';
-
- } else if (in_comment == '*' && '*' == ch &&
- '/' == *src) /* C style */
- {
- /* *dest++ = ch; */
- /* avoids asterisk-slash-asterisk issues */
- ch = *src++;
- in_comment = '\0';
- }
- /* *dest++ = ch; */
- continue;
- }
-
if (in_literal) {
/* check if literal ends but keep quotes in literal */
if (ch == in_literal) {
@@ -201,32 +178,7 @@
}
*dest++ = ch;
continue;
- }
-
- /* Look for comments: SQL-style or C++-style or C-style */
- if (('-' == ch && '-' == *src) ||
- ('/' == ch && '/' == *src) ||
- ('/' == ch && '*' == *src))
- {
- in_comment = *src;
- /* We know *src & the next char are to be copied, so do
- it. In the case of C-style comments, it happens to
- help us avoid slash-asterisk-slash oddities. */
- /* *dest++ = ch; */
- continue;
- }
-
-
- /* collapse whitespace */
- if ('\n' == ch) {
- *(src-1) = ' ';
- ch = ' ';
- }
- if (isSPACE(ch) && src-2 > statement &&
- isSPACE(*(src-2)) )
- {
- continue;
- }
+ }
/* check if no placeholders */
if (':' != ch && '?' != ch && '$' != ch) {
@@ -249,8 +201,7 @@
if (ch != '?' && !isALNUM(*src))
continue;
-
- sprintf(dest," $%d", ++place_holder_count);
+ sprintf(dest,"$%d", ++place_holder_count);
namelen = strlen(dest);
dest += namelen;
@@ -286,9 +237,6 @@
imp_sth->all_params_hv = newHV();
}
- /* //PerlIO_printf(DBILOGFP, "phs name start:%s len: %i
Index:%i\n", */
- /* // ph_name_start,namelen, place_holder_count); */
-
hv =hv_fetch(imp_sth->all_params_hv,ph_name_start,namelen,0);
if (NULL == hv) {
@@ -328,15 +276,16 @@
* sticks the SQL needed to prepare/execute a statement
* at the head of the statement.
* type: is one of PREPARE or EXECUTE
+ * plan_name: a quoted and escaped plan name
*/
void
-build_preamble (statement, type, place_holder_count, prep_stmt_id)
+build_preamble (statement, type, place_holder_count, plan_name)
char *statement;
/* const char *type; */
int type;
int place_holder_count;
- int prep_stmt_id;
+ const char *plan_name;
{
int i;
char *keyword;
@@ -348,11 +297,9 @@
else
croak("error");
+ sprintf(statement, "%s %s", keyword, plan_name);
- sprintf(statement,
- "%s \"DBD::ChurlPg::cached_query %i\"", keyword, prep_stmt_id);
-
- /* //PerlIO_printf(DBILOGFP, "statement: %s\n", statement); */
+ /* //PerlIO_printf(DBILOGFP, "statement: %s\n", statement); */
if (!place_holder_count) {
statement += strlen(statement);
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
