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

Reply via email to