On Mon, Dec 12, 2005 at 12:32:09PM +0100, Peter Eisentraut wrote:
> Joachim Wieland wrote:
> > * %Allow pooled connections to list all prepared queries

> This looks nice, but for consistency in naming, this should be about 
> prepared *statements*.

Okay, the appended patch is basically a s/query/statement/g.

Whoever reviews this patch could also apply this renaming to at least the
hash table in src/backend/commands/prepare.c:

static HTAB *prepared_queries = NULL;


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-13 00:08:45.000000000 
+0100
***************
*** 4373,4378 ****
--- 4373,4383 ----
       </row>
  
       <row>
+       <entry><link 
linkend="view-pg-prepared-statements"><structname>pg_prepared_statements</structname></link></entry>
+       <entry>available prepared statements 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,4862 ----
  
   </sect1>
  
+  <sect1 id="view-pg-prepared-statements">
+   <title><structname>pg_prepared_statements</structname></title>
+ 
+   <indexterm zone="view-pg-prepared-statements">
+    <primary>pg_prepared_statements</primary>
+   </indexterm>
+ 
+   <para>
+    The view <structname>pg_prepared_statements</structname> displays all 
available
+    prepared statements for the current session (see <xref 
linkend="sql-prepare"
+    endterm="sql-prepare-title"> for details).
+   </para>
+ 
+   <para>
+    <structname>pg_prepared_statements</structname> contains one row per
+    prepared statement. New entries get added when preparing new statements,
+    an entry gets deleted from the view when the associated prepared
+    statement is released by means of the <xref linkend="sql-deallocate"
+    endterm="sql-deallocate-title"> command.
+   </para>
+ 
+   <table>
+    <title><structname>pg_prepared_statements</> 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 statement.
+       </entry>
+      </row>
+      <row>
+       <entry><structfield>statement</structfield></entry>
+       <entry><type>text</type></entry>
+       <entry></entry>
+       <entry>
+        The SQL command used for preparing this statement.
+       </entry>
+      </row>
+      <row>
+       <entry><structfield>preparetime</structfield></entry>
+       <entry><type>timestamptz</type></entry>
+       <entry></entry>
+       <entry>
+        The time when the statement got prepared.
+       </entry>
+      </row>
+     </tbody>
+    </tgroup>
+   </table>
+ 
+   <para>
+    The <structname>pg_prepared_statements</structname> view is read only.
+   </para>
+ 
+   <para>
+    The <structfield>preparetime</structfield> reflects the real system time at
+    which the statement 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 
16:04:40.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_statements</> 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 
16:04:40.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_statements AS
+     SELECT P.name, P.statement, P.preparetime
+     FROM pg_prepared_statement() AS P
+     (name text, statement 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 
16:14:44.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_statement()
+  *
+  * This is a set returning function that reads all the prepared statements and
+  * returns a set of (name, statement, preparetime).
+  *
+  */
+ Datum
+ pg_prepared_statement(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, "statement",
+                                                  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 
16:04:40.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_statement PGNSP PGUID 12 f f t t s 0 
2249 "" _null_ _null_ _null_ pg_prepared_statement - _null_ ));
+ DESCR("get the prepared statements 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 
16:04:40.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 
16:04:40.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_statement(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 
16:59:07.000000000 +0100
***************
*** 1,4 ****
--- 1,11 ----
  -- Regression tests for prepareable statements
+ -- test pg_prepared_statements view output while adding and removing prepared
+ -- statements (we don't test the timestamp column of the view however)
+ SELECT name, statement FROM pg_prepared_statements;
+  name | statement 
+ ------+-----------
+ (0 rows)
+ 
  PREPARE q1 AS SELECT 1;
  EXECUTE q1;
   ?column? 
***************
*** 6,11 ****
--- 13,24 ----
          1
  (1 row)
  
+ SELECT name, statement FROM pg_prepared_statements;
+  name |        statement        
+ ------+-------------------------
+  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, statement FROM pg_prepared_statements;
+  name |        statement        
+ ------+-------------------------
+  q1   | PREPARE q1 AS SELECT 2;
+  q2   | PREPARE q2 AS SELECT 2;
+ (2 rows)
+ 
  -- sql92 syntax
  DEALLOCATE PREPARE q1;
+ SELECT name, statement FROM pg_prepared_statements;
+  name |        statement        
+ ------+-------------------------
+  q2   | PREPARE q2 AS SELECT 2;
+ (1 row)
+ 
+ DEALLOCATE PREPARE q2;
+ -- the view should return the empty set again
+ SELECT name, statement FROM pg_prepared_statements;
+  name | statement 
+ ------+-----------
+ (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 
16:59:07.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_statements   | SELECT p.name, p."statement", p.preparetime FROM 
pg_prepared_statement() p(name text, "statement" 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 
16:04:40.000000000 +0100
***************
*** 1,8 ****
--- 1,14 ----
  -- Regression tests for prepareable statements
+ -- test pg_prepared_statements view output while adding and removing prepared
+ -- statements (we don't test the timestamp column of the view however)
+ 
+ SELECT name, statement FROM pg_prepared_statements;
  
  PREPARE q1 AS SELECT 1;
  EXECUTE q1;
  
+ SELECT name, statement FROM pg_prepared_statements;
+ 
  -- 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, statement FROM pg_prepared_statements;
+ 
  -- sql92 syntax
  DEALLOCATE PREPARE q1;
  
+ SELECT name, statement FROM pg_prepared_statements;
+ 
+ DEALLOCATE PREPARE q2;
+ -- the view should return the empty set again
+ SELECT name, statement FROM pg_prepared_statements;
+ 
  -- 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