Hi,
I propose the attached patch for the TODO item:
* %Allow pooled connections to list all prepared queries
The patch adds a new SRF and a new view that contain all prepared queries
available in the session.
Besides the name of the plan and the actual query the view also displays
the timestamp of the preparation. This can help applications decide whether
or not they want to replan an existing prepared query.
Joachim
diff -cr cvs/pgsql/doc/src/sgml/catalogs.sgml
cvs.build/pgsql/doc/src/sgml/catalogs.sgml
*** cvs/pgsql/doc/src/sgml/catalogs.sgml 2005-11-05 00:13:59.000000000
+0100
--- cvs.build/pgsql/doc/src/sgml/catalogs.sgml 2005-12-12 10:10:59.000000000
+0100
***************
*** 4373,4378 ****
--- 4373,4383 ----
</row>
<row>
+ <entry><link
linkend="view-pg-prepared-queries"><structname>pg_prepared_queries</structname></link></entry>
+ <entry>available prepared queries for the current session</entry>
+ </row>
+
+ <row>
<entry><link
linkend="view-pg-prepared-xacts"><structname>pg_prepared_xacts</structname></link></entry>
<entry>currently prepared transactions</entry>
</row>
***************
*** 4778,4783 ****
--- 4783,4861 ----
</sect1>
+ <sect1 id="view-pg-prepared-queries">
+ <title><structname>pg_prepared_queries</structname></title>
+
+ <indexterm zone="view-pg-prepared-queries">
+ <primary>pg_prepared_queries</primary>
+ </indexterm>
+
+ <para>
+ The view <structname>pg_prepared_queries</structname> displays all
available
+ prepared queries for the current session (see <xref linkend="sql-prepare"
+ endterm="sql-prepare-title"> for details).
+ </para>
+
+ <para>
+ <structname>pg_prepared_queries</structname> contains one row per prepared
+ query. New entries get added when preparing new queries, entries get
deleted
+ from the view when releasing the prepared query by means of the <xref
+ linkend="sql-deallocate" endterm="sql-deallocate-title"> command.
+ </para>
+
+ <table>
+ <title><structname>pg_prepared_queries</> Columns</title>
+
+ <tgroup cols=4>
+ <thead>
+ <row>
+ <entry>Name</entry>
+ <entry>Type</entry>
+ <entry>References</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><structfield>name</structfield></entry>
+ <entry><type>text</type></entry>
+ <entry></entry>
+ <entry>
+ The identifier of the prepared query.
+ </entry>
+ </row>
+ <row>
+ <entry><structfield>query</structfield></entry>
+ <entry><type>text</type></entry>
+ <entry></entry>
+ <entry>
+ The SQL command used for preparing this query.
+ </entry>
+ </row>
+ <row>
+ <entry><structfield>preparetime</structfield></entry>
+ <entry><type>timestamptz</type></entry>
+ <entry></entry>
+ <entry>
+ The time when the query got prepared.
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ The <structname>pg_prepared_queries</structname> view is read only.
+ </para>
+
+ <para>
+ The <structfield>preparetime</structfield> reflects the real system time at
+ which the query got prepared (and not the transaction start time of the
+ preparing transaction).
+ </para>
+
+ </sect1>
+
<sect1 id="view-pg-prepared-xacts">
<title><structname>pg_prepared_xacts</structname></title>
diff -cr cvs/pgsql/doc/src/sgml/ref/prepare.sgml
cvs.build/pgsql/doc/src/sgml/ref/prepare.sgml
*** cvs/pgsql/doc/src/sgml/ref/prepare.sgml 2005-10-15 03:47:12.000000000
+0200
--- cvs.build/pgsql/doc/src/sgml/ref/prepare.sgml 2005-12-12
10:10:59.000000000 +0100
***************
*** 145,150 ****
--- 145,155 ----
the <xref linkend="sql-analyze" endterm="sql-analyze-title">
documentation.
</para>
+
+ <para>
+ You can see all available prepared statements of a session by querying the
+ <structname>pg_prepared_queries</> system view.
+ </para>
</refsect1>
<refsect1 id="sql-prepare-examples">
diff -cr cvs/pgsql/src/backend/catalog/system_views.sql
cvs.build/pgsql/src/backend/catalog/system_views.sql
*** cvs/pgsql/src/backend/catalog/system_views.sql 2005-10-06
04:29:15.000000000 +0200
--- cvs.build/pgsql/src/backend/catalog/system_views.sql 2005-12-12
10:10:59.000000000 +0100
***************
*** 156,161 ****
--- 156,166 ----
LEFT JOIN pg_authid U ON P.ownerid = U.oid
LEFT JOIN pg_database D ON P.dbid = D.oid;
+ CREATE VIEW pg_prepared_queries AS
+ SELECT P.name, P.query, P.preparetime
+ FROM pg_prepared_query() AS P
+ (name text, query text, preparetime timestamptz);
+
CREATE VIEW pg_settings AS
SELECT *
FROM pg_show_all_settings() AS A
diff -cr cvs/pgsql/src/backend/commands/prepare.c
cvs.build/pgsql/src/backend/commands/prepare.c
*** cvs/pgsql/src/backend/commands/prepare.c 2005-11-29 02:25:49.000000000
+0100
--- cvs.build/pgsql/src/backend/commands/prepare.c 2005-12-12
10:14:05.000000000 +0100
***************
*** 27,32 ****
--- 27,36 ----
#include "tcop/utility.h"
#include "utils/hsearch.h"
#include "utils/memutils.h"
+ #include "funcapi.h"
+ #include "catalog/pg_type.h"
+ #include "utils/builtins.h"
+ #include "access/heapam.h"
/*
***************
*** 361,366 ****
--- 365,371 ----
entry->plan_list = plan_list;
entry->argtype_list = argtype_list;
entry->context = entrycxt;
+ entry->prepare_time = GetCurrentTimestamp();
MemoryContextSwitchTo(oldcxt);
}
***************
*** 637,639 ****
--- 642,759 ----
if (estate)
FreeExecutorState(estate);
}
+
+
+ /* GetPreparedStatementSeqSearch:
+ *
+ * The caller must make sure not to call any other function that accesses the
+ * hash while executing the search.
+ *
+ * First call is with the argument set to false (this won't return any data)
+ * Later calls are with the argument set to true
+ * Returns NULL if no more data is available.
+ *
+ */
+ static char*
+ GetPreparedStatementSeqSearch(bool initsearch)
+ {
+ static HASH_SEQ_STATUS status;
+ PreparedStatement *hentry;
+
+ /* check if there is a table at all already */
+ if (!prepared_queries)
+ return NULL;
+
+ if (initsearch)
+ {
+ hash_seq_init(&status, prepared_queries);
+ return NULL;
+ }
+
+ hentry = (PreparedStatement *) hash_seq_search(&status);
+ if (hentry != NULL)
+ return hentry->stmt_name;
+
+ return NULL;
+ }
+
+ /* pg_prepared_query()
+ *
+ * This is a set returning function that reads all the prepared queries and
+ * returns a set of (name, query).
+ *
+ */
+ Datum
+ pg_prepared_query(PG_FUNCTION_ARGS)
+ {
+ FuncCallContext *funcctx;
+ MemoryContext oldcontext;
+ TupleDesc tupdesc;
+ char *stmt_name;
+ Datum dvalues[3];
+ bool nulls[3];
+ Datum result;
+ HeapTuple tuple;
+
+ /* stuff done only on the first call of the function */
+ if (SRF_IS_FIRSTCALL())
+ {
+ /* create a function context for cross-call persistence */
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ /*
+ * switch to memory context appropriate for multiple function
+ * calls
+ */
+ oldcontext =
MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ /* allocate memory for user context */
+ /* we need no user_fctx */
+ funcctx->user_fctx = NULL;
+
+ tupdesc = CreateTemplateTupleDesc(3, false);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name",
+ TEXTOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 2, "query",
+ TEXTOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 3, "preparetime",
+ TIMESTAMPTZOID, -1, 0);
+ BlessTupleDesc(tupdesc);
+
+ funcctx->tuple_desc = tupdesc;
+
+ (void) GetPreparedStatementSeqSearch(true);
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ /* stuff done on every call of the function */
+ funcctx = SRF_PERCALL_SETUP();
+
+ tupdesc = funcctx->tuple_desc;
+ /* GetPreparedStatementSeqSearch() returns NULL if we are done */
+ stmt_name = GetPreparedStatementSeqSearch(false);
+ if (stmt_name)
+ {
+ PreparedStatement *entry;
+
+ entry = FetchPreparedStatement(stmt_name, true);
+ Assert(entry != NULL);
+ Assert(entry->stmt_name != NULL);
+ Assert(entry->query_string != NULL);
+
+ dvalues[0] = DirectFunctionCall1(textin,
+
CStringGetDatum(entry->stmt_name));
+ dvalues[1] = DirectFunctionCall1(textin,
+
CStringGetDatum(entry->query_string));
+ dvalues[2] = TimestampTzGetDatum(entry->prepare_time);
+ nulls[0] = false;
+ nulls[1] = false;
+ nulls[2] = false;
+
+ tuple = heap_form_tuple(tupdesc, dvalues, nulls);
+ result = HeapTupleGetDatum(tuple);
+ SRF_RETURN_NEXT(funcctx, result);
+ }
+ else
+ SRF_RETURN_DONE(funcctx);
+ }
diff -cr cvs/pgsql/src/include/catalog/pg_proc.h
cvs.build/pgsql/src/include/catalog/pg_proc.h
*** cvs/pgsql/src/include/catalog/pg_proc.h 2005-11-17 23:14:54.000000000
+0100
--- cvs.build/pgsql/src/include/catalog/pg_proc.h 2005-12-12
10:10:59.000000000 +0100
***************
*** 3617,3622 ****
--- 3617,3624 ----
DESCR("constraint description with pretty-print option");
DATA(insert OID = 2509 ( pg_get_expr PGNSP PGUID 12 f f t f s 3
25 "25 26 16" _null_ _null_ _null_ pg_get_expr_ext - _null_ ));
DESCR("deparse an encoded expression with pretty-print option");
+ DATA(insert OID = 2510 ( pg_prepared_query PGNSP PGUID 12 f f t t s 0 2249
"" _null_ _null_ _null_ pg_prepared_query - _null_ ));
+ DESCR("get the prepared queries for this session");
/* non-persistent series generator */
DATA(insert OID = 1066 ( generate_series PGNSP PGUID 12 f f t t v 3 23 "23
23 23" _null_ _null_ _null_ generate_series_step_int4 - _null_ ));
diff -cr cvs/pgsql/src/include/commands/prepare.h
cvs.build/pgsql/src/include/commands/prepare.h
*** cvs/pgsql/src/include/commands/prepare.h 2005-11-29 02:25:50.000000000
+0100
--- cvs.build/pgsql/src/include/commands/prepare.h 2005-12-12
10:10:59.000000000 +0100
***************
*** 30,41 ****
typedef struct
{
/* dynahash.c requires key to be first field */
! char stmt_name[NAMEDATALEN];
! char *query_string; /* text of query, or NULL */
! const char *commandTag; /* command tag (a constant!), or NULL */
! List *query_list; /* list of queries */
! List *plan_list; /* list of plans */
! List *argtype_list; /* list of parameter type OIDs */
MemoryContext context; /* context containing this query */
} PreparedStatement;
--- 30,42 ----
typedef struct
{
/* dynahash.c requires key to be first field */
! char stmt_name[NAMEDATALEN];
! char *query_string; /* text of query, or NULL */
! const char *commandTag; /* command tag (a constant!),
or NULL */
! List *query_list; /* list of queries */
! List *plan_list; /* list of plans */
! List *argtype_list; /* list of parameter type OIDs
*/
! TimestampTz prepare_time; /* when did the query
get prepared */
MemoryContext context; /* context containing this query */
} PreparedStatement;
***************
*** 50,62 ****
/* Low-level access to stored prepared statements */
extern void StorePreparedStatement(const char *stmt_name,
! const char *query_string,
! const char *commandTag,
! List *query_list,
! List *plan_list,
! List *argtype_list);
extern PreparedStatement *FetchPreparedStatement(const char *stmt_name,
! bool throwError);
extern void DropPreparedStatement(const char *stmt_name, bool showError);
extern List *FetchPreparedStatementParams(const char *stmt_name);
extern TupleDesc FetchPreparedStatementResultDesc(PreparedStatement *stmt);
--- 51,63 ----
/* Low-level access to stored prepared statements */
extern void StorePreparedStatement(const char *stmt_name,
! const char
*query_string,
! const char
*commandTag,
! List
*query_list,
! List
*plan_list,
! List
*argtype_list);
extern PreparedStatement *FetchPreparedStatement(const char *stmt_name,
!
bool throwError);
extern void DropPreparedStatement(const char *stmt_name, bool showError);
extern List *FetchPreparedStatementParams(const char *stmt_name);
extern TupleDesc FetchPreparedStatementResultDesc(PreparedStatement *stmt);
diff -cr cvs/pgsql/src/include/utils/builtins.h
cvs.build/pgsql/src/include/utils/builtins.h
*** cvs/pgsql/src/include/utils/builtins.h 2005-11-22 19:17:32.000000000
+0100
--- cvs.build/pgsql/src/include/utils/builtins.h 2005-12-12
10:10:59.000000000 +0100
***************
*** 861,864 ****
--- 861,867 ----
/* catalog/pg_conversion.c */
extern Datum pg_convert_using(PG_FUNCTION_ARGS);
+ /* commands/prepare.c */
+ extern Datum pg_prepared_query(PG_FUNCTION_ARGS);
+
#endif /* BUILTINS_H */
diff -cr cvs/pgsql/src/test/regress/expected/prepare.out
cvs.build/pgsql/src/test/regress/expected/prepare.out
*** cvs/pgsql/src/test/regress/expected/prepare.out 2003-07-20
23:56:35.000000000 +0200
--- cvs.build/pgsql/src/test/regress/expected/prepare.out 2005-12-12
10:10:59.000000000 +0100
***************
*** 1,4 ****
--- 1,11 ----
-- Regression tests for prepareable statements
+ -- test pg_prepared_queries view output while adding and removing prepared
+ -- queries (we don't test the timestamp column of the view however)
+ SELECT name, query FROM pg_prepared_queries;
+ name | query
+ ------+-------
+ (0 rows)
+
PREPARE q1 AS SELECT 1;
EXECUTE q1;
?column?
***************
*** 6,11 ****
--- 13,24 ----
1
(1 row)
+ SELECT name, query FROM pg_prepared_queries;
+ name | query
+ ------+-------------------------
+ q1 | PREPARE q1 AS SELECT 1;
+ (1 row)
+
-- should fail
PREPARE q1 AS SELECT 2;
ERROR: prepared statement "q1" already exists
***************
*** 18,25 ****
--- 31,59 ----
2
(1 row)
+ PREPARE q2 AS SELECT 2;
+ SELECT name, query FROM pg_prepared_queries;
+ name | query
+ ------+-------------------------
+ q1 | PREPARE q1 AS SELECT 2;
+ q2 | PREPARE q2 AS SELECT 2;
+ (2 rows)
+
-- sql92 syntax
DEALLOCATE PREPARE q1;
+ SELECT name, query FROM pg_prepared_queries;
+ name | query
+ ------+-------------------------
+ q2 | PREPARE q2 AS SELECT 2;
+ (1 row)
+
+ DEALLOCATE PREPARE q2;
+ -- the view should return the empty set again
+ SELECT name, query FROM pg_prepared_queries;
+ name | query
+ ------+-------
+ (0 rows)
+
-- parameterized queries
PREPARE q2(text) AS
SELECT datname, datistemplate, datallowconn
diff -cr cvs/pgsql/src/test/regress/expected/rules.out
cvs.build/pgsql/src/test/regress/expected/rules.out
*** cvs/pgsql/src/test/regress/expected/rules.out 2005-11-28
05:35:32.000000000 +0100
--- cvs.build/pgsql/src/test/regress/expected/rules.out 2005-12-12
10:10:59.000000000 +0100
***************
*** 1280,1285 ****
--- 1280,1286 ----
pg_group | SELECT pg_authid.rolname AS groname,
pg_authid.oid AS grosysid, ARRAY(SELECT pg_auth_members.member FROM
pg_auth_members WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist FROM
pg_authid WHERE (NOT pg_authid.rolcanlogin);
pg_indexes | SELECT n.nspname AS schemaname, c.relname AS
tablename, i.relname AS indexname, t.spcname AS "tablespace",
pg_get_indexdef(i.oid) AS indexdef FROM ((((pg_index x JOIN pg_class c ON
((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN
pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON
((t.oid = i.reltablespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind =
'i'::"char"));
pg_locks | SELECT l.locktype, l."database", l.relation,
l.page, l.tuple, l.transactionid, l.classid, l.objid, l.objsubid,
l."transaction", l.pid, l."mode", l."granted" FROM pg_lock_status() l(locktype
text, "database" oid, relation oid, page integer, tuple smallint, transactionid
xid, classid oid, objid oid, objsubid smallint, "transaction" xid, pid integer,
"mode" text, "granted" boolean);
+ pg_prepared_queries | SELECT p.name, p.query, p.preparetime FROM
pg_prepared_query() p(name text, query text, preparetime timestamp with time
zone);
pg_prepared_xacts | SELECT p."transaction", p.gid, p."prepared",
u.rolname AS "owner", d.datname AS "database" FROM ((pg_prepared_xact()
p("transaction" xid, gid text, "prepared" timestamp with time zone, ownerid
oid, dbid oid) LEFT JOIN pg_authid u ON ((p.ownerid = u.oid))) LEFT JOIN
pg_database d ON ((p.dbid = d.oid)));
pg_roles | SELECT pg_authid.rolname, pg_authid.rolsuper,
pg_authid.rolinherit, pg_authid.rolcreaterole, pg_authid.rolcreatedb,
pg_authid.rolcatupdate, pg_authid.rolcanlogin, pg_authid.rolconnlimit,
'********'::text AS rolpassword, pg_authid.rolvaliduntil, pg_authid.rolconfig,
pg_authid.oid FROM pg_authid;
pg_rules | SELECT n.nspname AS schemaname, c.relname AS
tablename, r.rulename, pg_get_ruledef(r.oid) AS definition FROM ((pg_rewrite r
JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid
= c.relnamespace))) WHERE (r.rulename <> '_RETURN'::name);
***************
*** 1320,1326 ****
shoelace_obsolete | SELECT shoelace.sl_name, shoelace.sl_avail,
shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM
shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor
= shoelace.sl_color))));
street | SELECT r.name, r.thepath, c.cname FROM ONLY road
r, real_city c WHERE (c.outline ## r.thepath);
toyemp | SELECT emp.name, emp.age, emp."location", (12 *
emp.salary) AS annualsal FROM emp;
! (44 rows)
SELECT tablename, rulename, definition FROM pg_rules
ORDER BY tablename, rulename;
--- 1321,1327 ----
shoelace_obsolete | SELECT shoelace.sl_name, shoelace.sl_avail,
shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM
shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor
= shoelace.sl_color))));
street | SELECT r.name, r.thepath, c.cname FROM ONLY road
r, real_city c WHERE (c.outline ## r.thepath);
toyemp | SELECT emp.name, emp.age, emp."location", (12 *
emp.salary) AS annualsal FROM emp;
! (45 rows)
SELECT tablename, rulename, definition FROM pg_rules
ORDER BY tablename, rulename;
diff -cr cvs/pgsql/src/test/regress/sql/prepare.sql
cvs.build/pgsql/src/test/regress/sql/prepare.sql
*** cvs/pgsql/src/test/regress/sql/prepare.sql 2003-07-01 02:04:31.000000000
+0200
--- cvs.build/pgsql/src/test/regress/sql/prepare.sql 2005-12-12
10:10:59.000000000 +0100
***************
*** 1,8 ****
--- 1,14 ----
-- Regression tests for prepareable statements
+ -- test pg_prepared_queries view output while adding and removing prepared
+ -- queries (we don't test the timestamp column of the view however)
+
+ SELECT name, query FROM pg_prepared_queries;
PREPARE q1 AS SELECT 1;
EXECUTE q1;
+ SELECT name, query FROM pg_prepared_queries;
+
-- should fail
PREPARE q1 AS SELECT 2;
***************
*** 11,19 ****
--- 17,34 ----
PREPARE q1 AS SELECT 2;
EXECUTE q1;
+ PREPARE q2 AS SELECT 2;
+ SELECT name, query FROM pg_prepared_queries;
+
-- sql92 syntax
DEALLOCATE PREPARE q1;
+ SELECT name, query FROM pg_prepared_queries;
+
+ DEALLOCATE PREPARE q2;
+ -- the view should return the empty set again
+ SELECT name, query FROM pg_prepared_queries;
+
-- parameterized queries
PREPARE q2(text) AS
SELECT datname, datistemplate, datallowconn
***************
*** 43,45 ****
--- 58,61 ----
SELECT * FROM tenk1 WHERE unique1 = $1 OR stringu1 = $2;
CREATE TEMPORARY TABLE q5_prep_results AS EXECUTE q5(200, 'DTAAAA');
SELECT * FROM q5_prep_results;
+
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
