Thread: TODO item: list prepared queries
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
Attachment
Joachim Wieland wrote: > 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. This looks nice, but for consistency in naming, this should be about prepared *statements*. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut wrote:
> Joachim Wieland wrote:
> > 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.
>
> This looks nice, but for consistency in naming, this should be about
> prepared *statements*.
I have updated the TODO list to use 'statement' more often.
Also, does anyone know what this item means:
o Allow function argument names to be statements from PL/PgSQL
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
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
Attachment
On Tue, 2005-12-13 at 00:39 +0100, Joachim Wieland wrote: > Okay, the appended patch is basically a s/query/statement/g. Barring any objections, I'll review and apply the patch later this week. -Neil
On Mon, 2005-12-12 at 10:56 +0100, Joachim Wieland wrote: > I propose the attached patch for the TODO item: > > * %Allow pooled connections to list all prepared queries I think we should also return the parameters of each prepared statement. Probably the best way to do this is to add another column to pg_prepared_statements, containing an array of parameter type OIDs. I'll do that before applying the patch. One minor irritation is that the query string of prepared statements created via SQL has "PREPARE ... AS" prefixed to it, whereas statements prepared via the FE-BE protocol do not. This should probably be fixed, but I can't see a clean way to do it: I think we'd need to munge the actual SQL string itself and remove the "PREPARE ..." prefix. Thoughts? -Neil
Neil Conway wrote: > On Mon, 2005-12-12 at 10:56 +0100, Joachim Wieland wrote: > > I propose the attached patch for the TODO item: > > > > * %Allow pooled connections to list all prepared queries > > I think we should also return the parameters of each prepared statement. > Probably the best way to do this is to add another column to > pg_prepared_statements, containing an array of parameter type OIDs. I'll > do that before applying the patch. > > One minor irritation is that the query string of prepared statements > created via SQL has "PREPARE ... AS" prefixed to it, whereas statements > prepared via the FE-BE protocol do not. This should probably be fixed, > but I can't see a clean way to do it: I think we'd need to munge the > actual SQL string itself and remove the "PREPARE ..." prefix. Thoughts? Is there a way to do it in the parser/analyzer, and save only the actual prepared query instead of the whole thing? We could show additional columns in the pg_prepared_statements, indicating whether this is PREPARE (and the statement's name) or a Parse message. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Neil Conway <neilc@samurai.com> writes:
> One minor irritation is that the query string of prepared statements
> created via SQL has "PREPARE ... AS" prefixed to it, whereas statements
> prepared via the FE-BE protocol do not. This should probably be fixed,
That's debatable. Earlier today, I was busy being annoyed all over
again with the way that Bruce set up Parse/Bind/Execute logging to
deliberately obscure the difference between a SQL PREPARE command and a
protocol-level Parse operation. I think it's a good thing to be able to
tell which level a prepared statement came from. Yeah, much of the time
you may not care, but when you do care it's important.
regards, tom lane
Neil Conway <neilc@samurai.com> writes:
> One minor irritation is that the query string of prepared statements
> created via SQL has "PREPARE ... AS" prefixed to it, whereas statements
> prepared via the FE-BE protocol do not. This should probably be fixed,
> but I can't see a clean way to do it: I think we'd need to munge the
> actual SQL string itself and remove the "PREPARE ..." prefix. Thoughts?
BTW, pursuant to comments about David's proposal just now --- why is the
patch using text at all, rather than reverse-compiling the prepared
statement's querytree?
regards, tom lane
Tom Lane wrote: > Neil Conway <neilc@samurai.com> writes: >> One minor irritation is that the query string of prepared statements >> created via SQL has "PREPARE ... AS" prefixed to it, whereas statements >> prepared via the FE-BE protocol do not. This should probably be fixed, >> but I can't see a clean way to do it: I think we'd need to munge the >> actual SQL string itself and remove the "PREPARE ..." prefix. Thoughts? > > BTW, pursuant to comments about David's proposal just now --- why is the > patch using text at all, rather than reverse-compiling the prepared > statement's querytree? Well, I think for the driver or application, to recognize queries as their own, it seems much easier if the query is given exaclty as it was sent. I.e. even including PREPARE -- if it sent this way. I am not sure of the latter, but I would prefer to be given the original query string, PREPARE stripped or not. This comment is based on my assumption -- hopefully correct -- that the querytree has indeed changed from the original query. E.g. removed redundant parenthesis, added casts, etc. Best Regards, Michael Paesold
"Michael Paesold" <mpaesold@gmx.at> writes:
> Tom Lane wrote:
>> BTW, pursuant to comments about David's proposal just now --- why is the
>> patch using text at all, rather than reverse-compiling the prepared
>> statement's querytree?
> Well, I think for the driver or application, to recognize queries as their
> own, it seems much easier if the query is given exaclty as it was sent.
Depends on what the intended use of the view is, I suppose --- but I
should think that drivers would tend to just look at the statement name
to decide if it's something they sent, rather than comparing the text
of the body. Showing a reverse-compiled version would be more robust
in the face of cases like a subsequent change of schema search path,
RENAME commands, etc.
Also, while I have not looked at the patch to see where it's getting
the "original text" from, I'll bet lunch that it's wrong. The structure
of the parser doesn't permit easy extraction of the original text
corresponding to just one SQL command.
regards, tom lane
On December 14, 4:58 pm Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Michael Paesold" <mpaesold@gmx.at> writes: > > Well, I think for the driver or application, to recognize queries as > > their own, it seems much easier if the query is given exaclty as it > > was sent. > Depends on what the intended use of the view is, I suppose --- but I > should think that drivers would tend to just look at the statement name > to decide if it's something they sent, rather than comparing the text > of the body. Well, one could argue that relying on the identifier might be dangerous. Someone else could prepare a query with the identifier of another application and thus this application might execute something different than what it actually wants to, but then we're in the area of how to manage users and pooled connections. Anyway as you say it depends on what you want to use the view for. For an automatised usage the deparsed form is of no value, for your eye however it might be nicer. Another problem I just found out: you can drop a table a prepared query is referring to. Can the reverse-compiling function cope with that situation? Well, the cleanest solution might be to prevent this in the first place... Why not just display both versions? > Also, while I have not looked at the patch to see where it's getting > the "original text" from, I'll bet lunch that it's wrong. It uses the query string that was already in the prepared queries hash table. This one uses debug_query_string. I'm a poor student and can't afford paying you a lunch, but I'd offer you a coke if you tell me why this is wrong ;-) Joachim
"" <joe@mcknight.de> writes:
> It uses the query string that was already in the prepared queries hash
> table. This one uses debug_query_string. I'm a poor student and can't
> afford paying you a lunch, but I'd offer you a coke if you tell me why this
> is wrong ;-)
(1) Multiple statements in same query string. (2) Statements prepared
via paths other than front-door, client-submitted command; for instance,
inside a plpgsql function.
It was OK to not be very accurate about this as long as the string was
just being used for debugging purposes, but if it's going to be exposed
to users then I'm going to demand higher standards --- because those
corner cases *will* come back to us as bug reports.
regards, tom lane
Tom Lane wrote: > Neil Conway <neilc@samurai.com> writes: > > One minor irritation is that the query string of prepared statements > > created via SQL has "PREPARE ... AS" prefixed to it, whereas statements > > prepared via the FE-BE protocol do not. This should probably be fixed, > > That's debatable. Earlier today, I was busy being annoyed all over > again with the way that Bruce set up Parse/Bind/Execute logging to > deliberately obscure the difference between a SQL PREPARE command and a > protocol-level Parse operation. I think it's a good thing to be able to > tell which level a prepared statement came from. Yeah, much of the time > you may not care, but when you do care it's important. I have applied the following patch to CVS HEAD to mark client-side prepare/bind/execute statements with "[client]" so they can be easily distinguished from SQL commands. I hesitate to apply this logging change to 8.1.X. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 Index: src/backend/tcop/postgres.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/tcop/postgres.c,v retrieving revision 1.471 diff -c -c -r1.471 postgres.c *** src/backend/tcop/postgres.c 14 Dec 2005 17:06:27 -0000 1.471 --- src/backend/tcop/postgres.c 30 Dec 2005 22:27:51 -0000 *************** *** 1146,1152 **** if (log_statement == LOGSTMT_ALL) ereport(LOG, ! (errmsg("statement: PREPARE %s AS %s", (*stmt_name != '\0') ? stmt_name : "<unnamed>", query_string))); --- 1146,1152 ---- if (log_statement == LOGSTMT_ALL) ereport(LOG, ! (errmsg("statement: [client] PREPARE %s AS %s", (*stmt_name != '\0') ? stmt_name : "<unnamed>", query_string))); *************** *** 1449,1455 **** /* We need to output the parameter values someday */ if (log_statement == LOGSTMT_ALL) ereport(LOG, ! (errmsg("statement: <BIND> %s", portal_name))); /* * Fetch parameters, if any, and store in the portal's memory context. --- 1449,1455 ---- /* We need to output the parameter values someday */ if (log_statement == LOGSTMT_ALL) ereport(LOG, ! (errmsg("statement: [client] <BIND> %s", portal_name))); /* * Fetch parameters, if any, and store in the portal's memory context. *************** *** 1712,1718 **** if (log_statement == LOGSTMT_ALL) /* We have the portal, so output the source query. */ ereport(LOG, ! (errmsg("statement: %sEXECUTE %s [PREPARE: %s]", (execute_is_fetch) ? "FETCH from " : "", (*portal_name != '\0') ? portal_name : "<unnamed>", portal->sourceText ? portal->sourceText : ""))); --- 1712,1718 ---- if (log_statement == LOGSTMT_ALL) /* We have the portal, so output the source query. */ ereport(LOG, ! (errmsg("statement: [client] %sEXECUTE %s [PREPARE: %s]", (execute_is_fetch) ? "FETCH from " : "", (*portal_name != '\0') ? portal_name : "<unnamed>", portal->sourceText ? portal->sourceText : ""))); *************** *** 1821,1827 **** (save_log_min_duration_statement > 0 && usecs >= save_log_min_duration_statement * 1000)) ereport(LOG, ! (errmsg("duration: %ld.%03ld ms statement: %sEXECUTE %s [PREPARE: %s]", (long) ((stop_t.tv_sec - start_t.tv_sec) * 1000 + (stop_t.tv_usec - start_t.tv_usec) / 1000), (long) (stop_t.tv_usec - start_t.tv_usec) % 1000, --- 1821,1827 ---- (save_log_min_duration_statement > 0 && usecs >= save_log_min_duration_statement * 1000)) ereport(LOG, ! (errmsg("duration: %ld.%03ld ms statement: [client] %sEXECUTE %s [PREPARE: %s]", (long) ((stop_t.tv_sec - start_t.tv_sec) * 1000 + (stop_t.tv_usec - start_t.tv_usec) / 1000), (long) (stop_t.tv_usec - start_t.tv_usec) % 1000,
On Fri, Dec 30, 2005 at 05:55:23PM -0500, Bruce Momjian wrote: > Tom Lane wrote: > > Neil Conway <neilc@samurai.com> writes: > > > One minor irritation is that the query string of prepared statements > > > created via SQL has "PREPARE ... AS" prefixed to it, whereas statements > > > prepared via the FE-BE protocol do not. This should probably be fixed, > > > > That's debatable. Earlier today, I was busy being annoyed all over > > again with the way that Bruce set up Parse/Bind/Execute logging to > > deliberately obscure the difference between a SQL PREPARE command and a > > protocol-level Parse operation. I think it's a good thing to be able to > > tell which level a prepared statement came from. Yeah, much of the time > > you may not care, but when you do care it's important. > > I have applied the following patch to CVS HEAD to mark client-side > prepare/bind/execute statements with "[client]" so they can be easily > distinguished from SQL commands. I hesitate to apply this logging > change to 8.1.X. Could I suggest the reverse? That is, leave client statements alone and mark server side ones specially. It seems to me that "client" is the "normal" case and leaving it alone would be less intrusive. -dg -- David Gould daveg@sonic.net If simplicity worked, the world would be overrun with insects.
daveg wrote: > On Fri, Dec 30, 2005 at 05:55:23PM -0500, Bruce Momjian wrote: > > Tom Lane wrote: > > > Neil Conway <neilc@samurai.com> writes: > > > > One minor irritation is that the query string of prepared statements > > > > created via SQL has "PREPARE ... AS" prefixed to it, whereas statements > > > > prepared via the FE-BE protocol do not. This should probably be fixed, > > > > > > That's debatable. Earlier today, I was busy being annoyed all over > > > again with the way that Bruce set up Parse/Bind/Execute logging to > > > deliberately obscure the difference between a SQL PREPARE command and a > > > protocol-level Parse operation. I think it's a good thing to be able to > > > tell which level a prepared statement came from. Yeah, much of the time > > > you may not care, but when you do care it's important. > > > > I have applied the following patch to CVS HEAD to mark client-side > > prepare/bind/execute statements with "[client]" so they can be easily > > distinguished from SQL commands. I hesitate to apply this logging > > change to 8.1.X. > > Could I suggest the reverse? That is, leave client statements alone and > mark server side ones specially. It seems to me that "client" is the "normal" > case and leaving it alone would be less intrusive. Uh, the problem is that we don't normally mark SQL queries, so marking only the server prepares and leaving the client prepares alone seems inconsistent. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian wrote: > I have applied the following patch to CVS HEAD to mark client-side > prepare/bind/execute statements with "[client]" so they can be easily > distinguished from SQL commands. There is no such thing as a "client-side prepare/bind/execute" statement. The distinction is between SQL-level and protocol-level prepared queries. "[client]" seems wrong; perhaps "[protocol]" could be used instead? (I'm not thrilled by the idea of prefixing the statement log with "[...]" in any case: it makes it more difficult to determine the actual query string submitted by the user. However I can't see a better alternative...) > I hesitate to apply this logging change to 8.1.X. I don't see any reason to do that -- this is not a bug fix. Furthermore, there are backward-compatibility concerns. -Neil
Neil Conway wrote: > Bruce Momjian wrote: > > I have applied the following patch to CVS HEAD to mark client-side > > prepare/bind/execute statements with "[client]" so they can be easily > > distinguished from SQL commands. > > There is no such thing as a "client-side prepare/bind/execute" > statement. The distinction is between SQL-level and protocol-level > prepared queries. "[client]" seems wrong; perhaps "[protocol]" could be > used instead? Agreed. I never liked "client" either. It got me confused. I have changed it to protocol; patch attached. > (I'm not thrilled by the idea of prefixing the statement log with > "[...]" in any case: it makes it more difficult to determine the actual > query string submitted by the user. However I can't see a better > alternative...) Yep. > > I hesitate to apply this logging change to 8.1.X. > > I don't see any reason to do that -- this is not a bug fix. Furthermore, > there are backward-compatibility concerns. Right. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 Index: src/backend/tcop/postgres.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/tcop/postgres.c,v retrieving revision 1.473 diff -c -c -r1.473 postgres.c *** src/backend/tcop/postgres.c 30 Dec 2005 23:49:48 -0000 1.473 --- src/backend/tcop/postgres.c 31 Dec 2005 16:49:09 -0000 *************** *** 589,596 **** entry->query_string) { *prepare_string = palloc(strlen(entry->query_string) + ! strlen(" [client PREPARE: %s]") - 1); ! sprintf(*prepare_string, " [client PREPARE: %s]", entry->query_string); } } --- 589,596 ---- entry->query_string) { *prepare_string = palloc(strlen(entry->query_string) + ! strlen(" [protocol PREPARE: %s]") - 1); ! sprintf(*prepare_string, " [protocol PREPARE: %s]", entry->query_string); } } *************** *** 1146,1152 **** if (log_statement == LOGSTMT_ALL) ereport(LOG, ! (errmsg("statement: [client] PREPARE %s AS %s", (*stmt_name != '\0') ? stmt_name : "<unnamed>", query_string))); --- 1146,1152 ---- if (log_statement == LOGSTMT_ALL) ereport(LOG, ! (errmsg("statement: [protocol] PREPARE %s AS %s", (*stmt_name != '\0') ? stmt_name : "<unnamed>", query_string))); *************** *** 1449,1455 **** /* We need to output the parameter values someday */ if (log_statement == LOGSTMT_ALL) ereport(LOG, ! (errmsg("statement: [client] <BIND> %s", portal_name))); /* * Fetch parameters, if any, and store in the portal's memory context. --- 1449,1455 ---- /* We need to output the parameter values someday */ if (log_statement == LOGSTMT_ALL) ereport(LOG, ! (errmsg("statement: [protocol] <BIND> %s", portal_name))); /* * Fetch parameters, if any, and store in the portal's memory context. *************** *** 1712,1718 **** if (log_statement == LOGSTMT_ALL) /* We have the portal, so output the source query. */ ereport(LOG, ! (errmsg("statement: [client] %sEXECUTE %s [PREPARE: %s]", (execute_is_fetch) ? "FETCH from " : "", (*portal_name != '\0') ? portal_name : "<unnamed>", portal->sourceText ? portal->sourceText : ""))); --- 1712,1718 ---- if (log_statement == LOGSTMT_ALL) /* We have the portal, so output the source query. */ ereport(LOG, ! (errmsg("statement: [protocol] %sEXECUTE %s [PREPARE: %s]", (execute_is_fetch) ? "FETCH from " : "", (*portal_name != '\0') ? portal_name : "<unnamed>", portal->sourceText ? portal->sourceText : ""))); *************** *** 1821,1827 **** (save_log_min_duration_statement > 0 && usecs >= save_log_min_duration_statement * 1000)) ereport(LOG, ! (errmsg("duration: %ld.%03ld ms statement: [client] %sEXECUTE %s [PREPARE: %s]", (long) ((stop_t.tv_sec - start_t.tv_sec) * 1000 + (stop_t.tv_usec - start_t.tv_usec) / 1000), (long) (stop_t.tv_usec - start_t.tv_usec) % 1000, --- 1821,1827 ---- (save_log_min_duration_statement > 0 && usecs >= save_log_min_duration_statement * 1000)) ereport(LOG, ! (errmsg("duration: %ld.%03ld ms statement: [protocol] %sEXECUTE %s [PREPARE: %s]", (long) ((stop_t.tv_sec - start_t.tv_sec) * 1000 + (stop_t.tv_usec - start_t.tv_usec) / 1000), (long) (stop_t.tv_usec - start_t.tv_usec) % 1000,
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> daveg wrote:
>> Could I suggest the reverse? That is, leave client statements alone and
>> mark server side ones specially. It seems to me that "client" is the "normal"
>> case and leaving it alone would be less intrusive.
> Uh, the problem is that we don't normally mark SQL queries, so marking
> only the server prepares and leaving the client prepares alone seems
> inconsistent.
Yesterday I was going to complain that this patch makes things more
obscure rather than less so. daveg's confusion seems to confirm my
feeling about it. I'll try to think of some wording I like better.
regards, tom lane
Joachim Wieland wrote:
> I propose the attached patch for the TODO item:
>
> * %Allow pooled connections to list all prepared queries
Attached is a revised version of this patch, based on some improvements
sent to me offlist by Joachim, as well as some code review and fixes by
myself. Changes:
- the query string in the view is produced by deparsing the parsetree
after the parse-analysis phase using adt/ruleutils (but before the
rewriter or planner have been invoked).
- two new columns: "parameter_types" is an array of oid that contains
the OIDs of the prepared statement's parameters, and "from_sql" is a
boolean field that is true if the prepared statement was prepared via
SQL, and false if it was prepared via the FE/BE protocol.
The docs need some improvement, but I'm not aware of any major remaining
issues with the patch. Comments are welcome -- barring any major
problems, I'll apply the patch tomorrow.
-Neil
Index: doc/src/sgml/catalogs.sgml
===================================================================
RCS file: /Users/neilc/postgres/cvs_root/pgsql/doc/src/sgml/catalogs.sgml,v
retrieving revision 2.115
diff -c -r2.115 catalogs.sgml
*** doc/src/sgml/catalogs.sgml 4 Nov 2005 23:13:59 -0000 2.115
--- doc/src/sgml/catalogs.sgml 1 Jan 2006 06:22:50 -0000
***************
*** 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,4883 ----
</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 more
+ information about prepared statements.
+ </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 reverse compiled SQL command used for preparing this statement.
+ </entry>
+ </row>
+ <row>
+ <entry><structfield>prepare_time</structfield></entry>
+ <entry><type>timestamptz</type></entry>
+ <entry></entry>
+ <entry>
+ The time when the prepared statement was created.
+ </entry>
+ </row>
+ <row>
+ <entry><structfield>parameter_types</structfield></entry>
+ <entry><type>oid[]</type></entry>
+ <entry></entry>
+ <entry>
+ The expected parameter types for the prepared statement in the form of
+ an array of type OIDs.
+ </entry>
+ </row>
+ <row>
+ <entry><structfield>from_sql</structfield></entry>
+ <entry><type>boolean</type></entry>
+ <entry></entry>
+ <entry>
+ <literal>true</literal> is the prepared statement was created
+ via the <command>PREPARE</command> SQL statement;
+ <literal>false</literal> if the statement was prepared via the
+ frontend/backend protocol.
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ The <structname>pg_prepared_statements</structname> view is read only.
+ </para>
+
+ <para>
+ The <structfield>prepare_time</structfield> contains the time at
+ which the statement was 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>
Index: doc/src/sgml/ref/prepare.sgml
===================================================================
RCS file: /Users/neilc/postgres/cvs_root/pgsql/doc/src/sgml/ref/prepare.sgml,v
retrieving revision 1.16
diff -c -r1.16 prepare.sgml
*** doc/src/sgml/ref/prepare.sgml 15 Oct 2005 01:47:12 -0000 1.16
--- doc/src/sgml/ref/prepare.sgml 1 Jan 2006 01:09:36 -0000
***************
*** 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">
Index: src/backend/catalog/system_views.sql
===================================================================
RCS file: /Users/neilc/postgres/cvs_root/pgsql/src/backend/catalog/system_views.sql,v
retrieving revision 1.22
diff -c -r1.22 system_views.sql
*** src/backend/catalog/system_views.sql 6 Oct 2005 02:29:15 -0000 1.22
--- src/backend/catalog/system_views.sql 1 Jan 2006 06:04:04 -0000
***************
*** 156,161 ****
--- 156,167 ----
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.prepare_time, P.parameter_types, P.from_sql
+ FROM pg_prepared_statement() AS P
+ (name text, statement text, prepare_time timestamptz,
+ parameter_types oid[], from_sql boolean);
+
CREATE VIEW pg_settings AS
SELECT *
FROM pg_show_all_settings() AS A
Index: src/backend/commands/prepare.c
===================================================================
RCS file: /Users/neilc/postgres/cvs_root/pgsql/src/backend/commands/prepare.c,v
retrieving revision 1.44
diff -c -r1.44 prepare.c
*** src/backend/commands/prepare.c 14 Dec 2005 17:06:27 -0000 1.44
--- src/backend/commands/prepare.c 2 Jan 2006 01:05:29 -0000
***************
*** 16,30 ****
*/
#include "postgres.h"
#include "commands/explain.h"
#include "commands/prepare.h"
#include "executor/executor.h"
! #include "utils/guc.h"
#include "optimizer/planner.h"
#include "rewrite/rewriteHandler.h"
#include "tcop/pquery.h"
#include "tcop/tcopprot.h"
#include "tcop/utility.h"
#include "utils/hsearch.h"
#include "utils/memutils.h"
--- 16,35 ----
*/
#include "postgres.h"
+ #include "access/heapam.h"
+ #include "catalog/pg_type.h"
#include "commands/explain.h"
#include "commands/prepare.h"
#include "executor/executor.h"
! #include "funcapi.h"
! #include "parser/parsetree.h"
#include "optimizer/planner.h"
#include "rewrite/rewriteHandler.h"
#include "tcop/pquery.h"
#include "tcop/tcopprot.h"
#include "tcop/utility.h"
+ #include "utils/builtins.h"
+ #include "utils/guc.h"
#include "utils/hsearch.h"
#include "utils/memutils.h"
***************
*** 40,45 ****
--- 45,51 ----
static void InitQueryHashTable(void);
static ParamListInfo EvaluateParams(EState *estate,
List *params, List *argtypes);
+ static Datum build_oid_array(List *oid_list);
/*
* Implements the 'PREPARE' utility statement.
***************
*** 51,56 ****
--- 57,63 ----
Query *query;
List *query_list,
*plan_list;
+ char *query_string_deparse;
/*
* Disallow empty-string statement name (conflicts with protocol-level
***************
*** 98,103 ****
--- 105,117 ----
*/
query = copyObject(stmt->query);
+ /*
+ * Produce an SQL string corresponding to the prepared statement
+ * by deparsing the analyzed parsetree. Note that this is done
+ * before invoking the rewriter or planner.
+ */
+ query_string_deparse = deparse_query_list(list_make1(query));
+
/* Rewrite the query. The result could be 0, 1, or many queries. */
AcquireRewriteLocks(query);
query_list = QueryRewrite(query);
***************
*** 111,120 ****
*/
StorePreparedStatement(stmt->name,
debug_query_string,
commandTag,
query_list,
plan_list,
! stmt->argtype_oids);
}
/*
--- 125,136 ----
*/
StorePreparedStatement(stmt->name,
debug_query_string,
+ query_string_deparse,
commandTag,
query_list,
plan_list,
! stmt->argtype_oids,
! true);
}
/*
***************
*** 295,309 ****
void
StorePreparedStatement(const char *stmt_name,
const char *query_string,
const char *commandTag,
List *query_list,
List *plan_list,
! List *argtype_list)
{
PreparedStatement *entry;
MemoryContext oldcxt,
entrycxt;
char *qstring;
char key[NAMEDATALEN];
bool found;
--- 311,328 ----
void
StorePreparedStatement(const char *stmt_name,
const char *query_string,
+ const char *query_string_deparse,
const char *commandTag,
List *query_list,
List *plan_list,
! List *argtype_list,
! bool from_sql)
{
PreparedStatement *entry;
MemoryContext oldcxt,
entrycxt;
char *qstring;
+ char *qstring_deparse;
char key[NAMEDATALEN];
bool found;
***************
*** 339,344 ****
--- 358,364 ----
* incomplete (ie corrupt) hashtable entry.
*/
qstring = query_string ? pstrdup(query_string) : NULL;
+ qstring_deparse = pstrdup(query_string_deparse);
query_list = (List *) copyObject(query_list);
plan_list = (List *) copyObject(plan_list);
argtype_list = list_copy(argtype_list);
***************
*** 356,366 ****
--- 376,389 ----
/* Fill in the hash table entry with copied data */
entry->query_string = qstring;
+ entry->query_string_pretty = qstring_deparse;
entry->commandTag = commandTag;
entry->query_list = query_list;
entry->plan_list = plan_list;
entry->argtype_list = argtype_list;
entry->context = entrycxt;
+ entry->prepare_time = GetCurrentTimestamp();
+ entry->from_sql = from_sql;
MemoryContextSwitchTo(oldcxt);
}
***************
*** 383,389 ****
{
/*
* We can't just use the statement name as supplied by the user: the
! * hash package is picky enough that it needs to be NULL-padded out to
* the appropriate length to work correctly.
*/
StrNCpy(key, stmt_name, sizeof(key));
--- 406,412 ----
{
/*
* We can't just use the statement name as supplied by the user: the
! * hash package is picky enough that it needs to be NUL-padded out to
* the appropriate length to work correctly.
*/
StrNCpy(key, stmt_name, sizeof(key));
***************
*** 661,663 ****
--- 684,801 ----
if (estate)
FreeExecutorState(estate);
}
+
+ /*
+ * This set returning function reads all the prepared statements and
+ * returns a set of (name, statement, prepare_time, param_types).
+ */
+ Datum
+ pg_prepared_statement(PG_FUNCTION_ARGS)
+ {
+ FuncCallContext *funcctx;
+ HASH_SEQ_STATUS *hash_seq;
+ PreparedStatement *prep_stmt;
+
+ /* stuff done only on the first call of the function */
+ if (SRF_IS_FIRSTCALL())
+ {
+ TupleDesc tupdesc;
+ MemoryContext oldcontext;
+
+ /* 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 */
+ if (prepared_queries)
+ {
+ hash_seq = (HASH_SEQ_STATUS *) palloc(sizeof(HASH_SEQ_STATUS));
+ hash_seq_init(hash_seq, prepared_queries);
+ funcctx->user_fctx = (void *) hash_seq;
+ }
+ else
+ funcctx->user_fctx = NULL;
+
+ /*
+ * build tupdesc for result tuples. This must match the
+ * definition of the pg_prepared_statements view in
+ * system_views.sql
+ */
+ tupdesc = CreateTemplateTupleDesc(5, false);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name",
+ TEXTOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 2, "statement",
+ TEXTOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 3, "prepare_time",
+ TIMESTAMPTZOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 4, "parameter_types",
+ OIDARRAYOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 5, "from_sql",
+ BOOLOID, -1, 0);
+
+ funcctx->tuple_desc = BlessTupleDesc(tupdesc);
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ /* stuff done on every call of the function */
+ funcctx = SRF_PERCALL_SETUP();
+ hash_seq = (HASH_SEQ_STATUS *) funcctx->user_fctx;
+
+ /* if the hash table is uninitialized, we're done */
+ if (hash_seq == NULL)
+ SRF_RETURN_DONE(funcctx);
+
+ prep_stmt = hash_seq_search(hash_seq);
+ if (prep_stmt)
+ {
+ Datum result;
+ HeapTuple tuple;
+ Datum values[5];
+ bool nulls[5];
+
+ MemSet(nulls, 0, sizeof(nulls));
+ values[0] = DirectFunctionCall1(textin,
+ CStringGetDatum(prep_stmt->stmt_name));
+ values[1] = DirectFunctionCall1(textin,
+ CStringGetDatum(prep_stmt->query_string_pretty));
+ values[2] = TimestampTzGetDatum(prep_stmt->prepare_time);
+ values[3] = build_oid_array(prep_stmt->argtype_list);
+ values[4] = BoolGetDatum(prep_stmt->from_sql);
+
+ tuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
+ result = HeapTupleGetDatum(tuple);
+ SRF_RETURN_NEXT(funcctx, result);
+ }
+
+ SRF_RETURN_DONE(funcctx);
+ }
+
+ /*
+ * This utility function takes a List of Oids, and returns a Datum
+ * pointing to a Postgres array containing those OIDs.
+ */
+ static Datum
+ build_oid_array(List *oid_list)
+ {
+ ListCell *lc;
+ int len;
+ int i;
+ Datum *tmp_ary;
+ ArrayType *ary;
+
+ len = list_length(oid_list);
+ tmp_ary = (Datum *) palloc(len * sizeof(Datum));
+
+ i = 0;
+ foreach(lc, oid_list)
+ tmp_ary[i++] = ObjectIdGetDatum(lfirst_oid(lc));
+
+ /* XXX: this hardcodes assumptions about the OID type... */
+ ary = construct_array(tmp_ary, len, OIDOID, sizeof(Oid), true, 'i');
+ return PointerGetDatum(ary);
+ }
Index: src/backend/tcop/postgres.c
===================================================================
RCS file: /Users/neilc/postgres/cvs_root/pgsql/src/backend/tcop/postgres.c,v
retrieving revision 1.474
diff -c -r1.474 postgres.c
*** src/backend/tcop/postgres.c 31 Dec 2005 16:50:44 -0000 1.474
--- src/backend/tcop/postgres.c 2 Jan 2006 02:12:32 -0000
***************
*** 55,60 ****
--- 55,61 ----
#include "tcop/pquery.h"
#include "tcop/tcopprot.h"
#include "tcop/utility.h"
+ #include "utils/builtins.h"
#include "utils/flatfiles.h"
#include "utils/guc.h"
#include "utils/lsyscache.h"
***************
*** 1129,1134 ****
--- 1130,1136 ----
List *querytree_list,
*plantree_list,
*param_list;
+ char *query_string_deparse = NULL;
bool is_named;
bool save_log_statement_stats = log_statement_stats;
***************
*** 1271,1276 ****
--- 1273,1286 ----
if (log_parser_stats)
ShowUsage("PARSE ANALYSIS STATISTICS");
+ /*
+ * If this is a named statement, produce another version of
+ * query_string by deparsing the list of parsetrees. This is
+ * done before invoking the rewriter or planner.
+ */
+ if (is_named)
+ query_string_deparse = deparse_query_list(querytree_list);
+
querytree_list = pg_rewrite_queries(querytree_list);
/*
***************
*** 1301,1310 ****
{
StorePreparedStatement(stmt_name,
query_string,
commandTag,
querytree_list,
plantree_list,
! param_list);
}
else
{
--- 1311,1322 ----
{
StorePreparedStatement(stmt_name,
query_string,
+ query_string_deparse,
commandTag,
querytree_list,
plantree_list,
! param_list,
! false);
}
else
{
***************
*** 1313,1323 ****
--- 1325,1337 ----
pstmt = (PreparedStatement *) palloc0(sizeof(PreparedStatement));
/* query_string needs to be copied into unnamed_stmt_context */
pstmt->query_string = pstrdup(query_string);
+ pstmt->query_string_pretty = NULL; /* not needed */
/* the rest is there already */
pstmt->commandTag = commandTag;
pstmt->query_list = querytree_list;
pstmt->plan_list = plantree_list;
pstmt->argtype_list = param_list;
+ pstmt->from_sql = false;
pstmt->context = unnamed_stmt_context;
/* Now the unnamed statement is complete and valid */
unnamed_stmt_pstmt = pstmt;
Index: src/backend/utils/adt/ruleutils.c
===================================================================
RCS file: /Users/neilc/postgres/cvs_root/pgsql/src/backend/utils/adt/ruleutils.c,v
retrieving revision 1.212
diff -c -r1.212 ruleutils.c
*** src/backend/utils/adt/ruleutils.c 30 Dec 2005 18:34:22 -0000 1.212
--- src/backend/utils/adt/ruleutils.c 2 Jan 2006 01:01:47 -0000
***************
*** 1351,1356 ****
--- 1351,1377 ----
showimplicit, 0, 0);
}
+ char *
+ deparse_query_list(List *query_list)
+ {
+ ListCell *q;
+ StringInfoData buf;
+
+ initStringInfo(&buf);
+ foreach(q, query_list)
+ {
+ Query *query = lfirst(q);
+
+ /* separate queries with semi-colons */
+ if (buf.data[0] != '\0')
+ appendStringInfo(&buf, "; ");
+
+ get_query_def(query, &buf, NIL, NULL, PRETTYFLAG_PAREN, 0);
+ }
+
+ return buf.data;
+ }
+
/* ----------
* deparse_expression_pretty - General utility for deparsing expressions
*
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /Users/neilc/postgres/cvs_root/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.389
diff -c -r1.389 pg_proc.h
*** src/include/catalog/pg_proc.h 17 Nov 2005 22:14:54 -0000 1.389
--- src/include/catalog/pg_proc.h 1 Jan 2006 01:09:36 -0000
***************
*** 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_ ));
Index: src/include/catalog/pg_type.h
===================================================================
RCS file: /Users/neilc/postgres/cvs_root/pgsql/src/include/catalog/pg_type.h,v
retrieving revision 1.167
diff -c -r1.167 pg_type.h
*** src/include/catalog/pg_type.h 22 Nov 2005 18:17:30 -0000 1.167
--- src/include/catalog/pg_type.h 1 Jan 2006 03:12:52 -0000
***************
*** 406,411 ****
--- 406,412 ----
DATA(insert OID = 1008 ( _regproc PGNSP PGUID -1 f b t \054 0 24 array_in array_out array_recv array_send - i
xf 0 -1 0 _null_ _null_ ));
DATA(insert OID = 1009 ( _text PGNSP PGUID -1 f b t \054 0 25 array_in array_out array_recv array_send -
ix f 0 -1 0 _null_ _null_ ));
DATA(insert OID = 1028 ( _oid PGNSP PGUID -1 f b t \054 0 26 array_in array_out array_recv array_send - i
xf 0 -1 0 _null_ _null_ ));
+ #define OIDARRAYOID 1028
DATA(insert OID = 1010 ( _tid PGNSP PGUID -1 f b t \054 0 27 array_in array_out array_recv array_send - i
xf 0 -1 0 _null_ _null_ ));
DATA(insert OID = 1011 ( _xid PGNSP PGUID -1 f b t \054 0 28 array_in array_out array_recv array_send - i
xf 0 -1 0 _null_ _null_ ));
DATA(insert OID = 1012 ( _cid PGNSP PGUID -1 f b t \054 0 29 array_in array_out array_recv array_send - i
xf 0 -1 0 _null_ _null_ ));
Index: src/include/commands/prepare.h
===================================================================
RCS file: /Users/neilc/postgres/cvs_root/pgsql/src/include/commands/prepare.h,v
retrieving revision 1.16
diff -c -r1.16 prepare.h
*** src/include/commands/prepare.h 14 Dec 2005 17:06:28 -0000 1.16
--- src/include/commands/prepare.h 2 Jan 2006 00:44:06 -0000
***************
*** 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 */
! MemoryContext context; /* context containing this query */
} PreparedStatement;
--- 30,47 ----
typedef struct
{
/* dynahash.c requires key to be first field */
! char stmt_name[NAMEDATALEN];
! char *query_string; /* text of query, or NULL */
! char *query_string_pretty; /* text of queries, produced
! * via deparsing the Query list */
! const char *commandTag; /* command tag (a constant!), or NULL */
! List *query_list; /* list of queries, rewritten */
! List *plan_list; /* list of plans */
! List *argtype_list; /* list of parameter type OIDs */
! TimestampTz prepare_time; /* the time when the stmt was prepared */
! bool from_sql; /* stmt prepared via SQL, not
! * FE/BE protocol? */
! MemoryContext context; /* context containing this query */
} PreparedStatement;
***************
*** 51,60 ****
/* 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);
--- 56,67 ----
/* Low-level access to stored prepared statements */
extern void StorePreparedStatement(const char *stmt_name,
const char *query_string,
+ const char *query_string_deparse,
const char *commandTag,
List *query_list,
List *plan_list,
! List *argtype_list,
! bool from_sql);
extern PreparedStatement *FetchPreparedStatement(const char *stmt_name,
bool throwError);
extern void DropPreparedStatement(const char *stmt_name, bool showError);
Index: src/include/utils/builtins.h
===================================================================
RCS file: /Users/neilc/postgres/cvs_root/pgsql/src/include/utils/builtins.h,v
retrieving revision 1.268
diff -c -r1.268 builtins.h
*** src/include/utils/builtins.h 22 Nov 2005 18:17:32 -0000 1.268
--- src/include/utils/builtins.h 1 Jan 2006 03:01:25 -0000
***************
*** 513,518 ****
--- 513,519 ----
extern Datum pg_get_serial_sequence(PG_FUNCTION_ARGS);
extern char *deparse_expression(Node *expr, List *dpcontext,
bool forceprefix, bool showimplicit);
+ extern char *deparse_query_list(List *query_list);
extern List *deparse_context_for(const char *aliasname, Oid relid);
extern List *deparse_context_for_plan(int outer_varno, Node *outercontext,
int inner_varno, Node *innercontext,
***************
*** 861,864 ****
--- 862,868 ----
/* 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 */
Index: src/test/regress/expected/prepare.out
===================================================================
RCS file: /Users/neilc/postgres/cvs_root/pgsql/src/test/regress/expected/prepare.out,v
retrieving revision 1.5
diff -c -r1.5 prepare.out
*** src/test/regress/expected/prepare.out 20 Jul 2003 21:56:35 -0000 1.5
--- src/test/regress/expected/prepare.out 2 Jan 2006 01:41:25 -0000
***************
*** 1,9 ****
! -- Regression tests for prepareable statements
! PREPARE q1 AS SELECT 1;
EXECUTE q1;
! ?column?
! ----------
! 1
(1 row)
-- should fail
--- 1,22 ----
! -- Regression tests for prepareable statements. We query the content
! -- of the pg_prepared_statements view as prepared statements are
! -- created and removed.
! SELECT name, statement, parameter_types FROM pg_prepared_statements;
! name | statement | parameter_types
! ------+-----------+-----------------
! (0 rows)
!
! PREPARE q1 AS SELECT 1 AS a;
EXECUTE q1;
! a
! ---
! 1
! (1 row)
!
! SELECT name, statement, parameter_types FROM pg_prepared_statements;
! name | statement | parameter_types
! ------+---------------+-----------------
! q1 | SELECT 1 AS a | {}
(1 row)
-- should fail
***************
*** 18,29 ****
--- 31,69 ----
2
(1 row)
+ PREPARE q2 AS SELECT 2 AS b;
+ SELECT name, statement, parameter_types FROM pg_prepared_statements;
+ name | statement | parameter_types
+ ------+---------------+-----------------
+ q1 | SELECT 2 | {}
+ q2 | SELECT 2 AS b | {}
+ (2 rows)
+
-- sql92 syntax
DEALLOCATE PREPARE q1;
+ SELECT name, statement, parameter_types FROM pg_prepared_statements;
+ name | statement | parameter_types
+ ------+---------------+-----------------
+ q2 | SELECT 2 AS b | {}
+ (1 row)
+
+ DEALLOCATE PREPARE q2;
+ -- the view should return the empty set again
+ SELECT name, statement, parameter_types FROM pg_prepared_statements;
+ name | statement | parameter_types
+ ------+-----------+-----------------
+ (0 rows)
+
-- parameterized queries
PREPARE q2(text) AS
SELECT datname, datistemplate, datallowconn
FROM pg_database WHERE datname = $1;
+ SELECT name, statement, parameter_types FROM pg_prepared_statements;
+ name | statement | parameter_types
+ ------+---------------------------------------------------------------------------------------+-----------------
+ q2 | SELECT datname, datistemplate, datallowconn FROM pg_database WHERE datname::text = $1 | {25}
+ (1 row)
+
EXECUTE q2('regression');
datname | datistemplate | datallowconn
------------+---------------+--------------
***************
*** 33,38 ****
--- 73,85 ----
PREPARE q3(text, int, float, boolean, oid, smallint) AS
SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR
ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int);
+ SELECT name, statement, parameter_types FROM pg_prepared_statements;
+ name |
statement
| parameter_types
+
------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------
+ q2 | SELECT datname, datistemplate, datallowconn FROM pg_database WHERE datname::text = $1
| {25}
+ q3 | SELECT unique1, unique2, two, four, ten, twenty, hundred, thousand, twothousand, fivethous, tenthous, odd,
even,stringu1, stringu2, string4 FROM tenk1 WHERE string4::text = $1 AND (four = $2 OR ten = $3::bigint OR true = $4 OR
oid= $5 OR odd = $6::integer) | {25,23,701,16,26,21}
+ (2 rows)
+
EXECUTE q3('AAAAxx', 5::smallint, 10.5::float, false, 500::oid, 4::bigint);
unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even
|stringu1 | stringu2 | string4
---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
Index: src/test/regress/expected/rules.out
===================================================================
RCS file: /Users/neilc/postgres/cvs_root/pgsql/src/test/regress/expected/rules.out,v
retrieving revision 1.110
diff -c -r1.110 rules.out
*** src/test/regress/expected/rules.out 28 Nov 2005 04:35:32 -0000 1.110
--- src/test/regress/expected/rules.out 2 Jan 2006 01:41:36 -0000
***************
*** 1280,1285 ****
--- 1280,1286 ----
pg_group | SELECT pg_authid.rolname AS groname, pg_authid.oid AS grosysid, ARRAY(SELECT
pg_auth_members.memberFROM 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_classi ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace
tON ((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.prepare_time, p.parameter_types, p.from_sql FROM
pg_prepared_statement()p(name text, "statement" text, prepare_time timestamp with time zone, parameter_types oid[],
from_sqlboolean);
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,
dbidoid) 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,
'********'::textAS 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)
ASdefinition 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;
Index: src/test/regress/sql/prepare.sql
===================================================================
RCS file: /Users/neilc/postgres/cvs_root/pgsql/src/test/regress/sql/prepare.sql,v
retrieving revision 1.2
diff -c -r1.2 prepare.sql
*** src/test/regress/sql/prepare.sql 1 Jul 2003 00:04:31 -0000 1.2
--- src/test/regress/sql/prepare.sql 1 Jan 2006 01:31:20 -0000
***************
*** 1,8 ****
! -- Regression tests for prepareable statements
! PREPARE q1 AS SELECT 1;
EXECUTE q1;
-- should fail
PREPARE q1 AS SELECT 2;
--- 1,14 ----
! -- Regression tests for prepareable statements. We query the content
! -- of the pg_prepared_statements view as prepared statements are
! -- created and removed.
! SELECT name, statement, parameter_types FROM pg_prepared_statements;
!
! PREPARE q1 AS SELECT 1 AS a;
EXECUTE q1;
+ SELECT name, statement, parameter_types FROM pg_prepared_statements;
+
-- should fail
PREPARE q1 AS SELECT 2;
***************
*** 11,29 ****
--- 17,49 ----
PREPARE q1 AS SELECT 2;
EXECUTE q1;
+ PREPARE q2 AS SELECT 2 AS b;
+ SELECT name, statement, parameter_types FROM pg_prepared_statements;
+
-- sql92 syntax
DEALLOCATE PREPARE q1;
+ SELECT name, statement, parameter_types FROM pg_prepared_statements;
+
+ DEALLOCATE PREPARE q2;
+ -- the view should return the empty set again
+ SELECT name, statement, parameter_types FROM pg_prepared_statements;
+
-- parameterized queries
PREPARE q2(text) AS
SELECT datname, datistemplate, datallowconn
FROM pg_database WHERE datname = $1;
+
+ SELECT name, statement, parameter_types FROM pg_prepared_statements;
+
EXECUTE q2('regression');
PREPARE q3(text, int, float, boolean, oid, smallint) AS
SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR
ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int);
+ SELECT name, statement, parameter_types FROM pg_prepared_statements;
+
EXECUTE q3('AAAAxx', 5::smallint, 10.5::float, false, 500::oid, 4::bigint);
-- too few params
Neil Conway <neilc@samurai.com> writes:
> The docs need some improvement, but I'm not aware of any major remaining
> issues with the patch.
I object VERY strongly to the part of the patch that inserts a
deparse_query_list() call into exec_parse_message(). That is not a
cheap operation, and imposing that sort of overhead on every Parse
message is entirely unacceptable from a performance point of view.
I see no need for it either. What's wrong with regurgitating the
original source string, which is already saved in prepared queries?
Other than that show-stopper, the patch looks reasonable at first glance.
regards, tom lane
Tom Lane wrote: > I object VERY strongly to the part of the patch that inserts a > deparse_query_list() call into exec_parse_message(). That is not a > cheap operation, and imposing that sort of overhead on every Parse > message is entirely unacceptable from a performance point of view. Well, it doesn't insert a deparse_query_list() into the processing of *every* Parse message -- it only does so for Parse messages that create named prepared statements. I don't see that there is a fundamental difference between a named Parse and an SQL-level PREPARE: if adding deparse_query_list() to one is too expensive, ISTM it is too expensive for either. > I see no need for it either. What's wrong with regurgitating the > original source string, which is already saved in prepared queries? It is inconsistent to use the string supplied by the client for protocol-level prepared statements, but to use the SQL produced by deparsing for SQL PREPARE. One possibility would be to execute deparse_query_list() in the SRF (which is what Joachim's patch did originally), but that is fragile: if a table a prepared statement depends on is dropped, the view will be broken. We could workaround that by enclosing the deparse_query_list() call in a PG_TRY block (and displaying a NULL query string for broken prepared statements), but that doesn't prevent more subtle problems like the search_path changing. -Neil
Neil Conway <neilc@samurai.com> writes:
> Well, it doesn't insert a deparse_query_list() into the processing of
> *every* Parse message -- it only does so for Parse messages that create
> named prepared statements. I don't see that there is a fundamental
> difference between a named Parse and an SQL-level PREPARE: if adding
> deparse_query_list() to one is too expensive, ISTM it is too expensive
> for either.
I quite agree ;-)
> One possibility would be to execute deparse_query_list() in the SRF
> (which is what Joachim's patch did originally), but that is fragile: if
> a table a prepared statement depends on is dropped, the view will be
> broken. We could workaround that by enclosing the deparse_query_list()
> call in a PG_TRY block (and displaying a NULL query string for broken
> prepared statements), but that doesn't prevent more subtle problems like
> the search_path changing.
Arguably, deparsing when the view is read is the only correct way to
handle search-path changes. But I really think that storing the source
string is the most useful as well as fastest definition. The average
application that wants to use this view at all will be looking to see
"did I already prepare FOO". If it's using the query definition string
for this purpose, comparing source text is easy while comparing deparsed
text to source is a nightmare.
regards, tom lane
Tom Lane wrote: > The average application that wants to use this view at all will be > looking to see "did I already prepare FOO". If it's using the query > definition string for this purpose, comparing source text is easy > while comparing deparsed text to source is a nightmare. Well, I don't see why an application would want to look at the query string in the first place -- as you pointed out earlier, using the prepared statement's name seems a much easier way to identify prepared statements. In any case, if we use the query string as supplied by the user, how do we produce that string in the case of SQL PREPARE? Manually stripping a "PREPARE ... AS" prefix from the query string is difficult to do robustly, but it seems (a) expensive (b) inconsistent to deparse the Query for SQL PREPARE but not for Parse messages. We could just include the "PREPARE ... AS" prefix for SQL PREPAREs, but that seems ugly. -Neil
Neil Conway <neilc@samurai.com> writes:
> In any case, if we use the query string as supplied by the user, how do
> we produce that string in the case of SQL PREPARE? Manually stripping a
> "PREPARE ... AS" prefix from the query string is difficult to do
> robustly, but it seems (a) expensive (b) inconsistent to deparse the
> Query for SQL PREPARE but not for Parse messages. We could just include
> the "PREPARE ... AS" prefix for SQL PREPAREs, but that seems ugly.
I don't see the problem. Defining the view field as "the string sent to
the server to create the prepared statement" seems perfectly consistent
to me.
In practice, any given application will probably use one method to the
exclusion of the other, and wouldn't notice the "inconsistency" anyway.
If you are using both methods of preparing statements for some reason,
it's not improbable that you would want to know which way a given
statement was created, and seeing the PREPARE in there would be a useful
cue.
regards, tom lane
Tom Lane wrote: > In practice, any given application will probably use one method to the > exclusion of the other, and wouldn't notice the "inconsistency" anyway. > If you are using both methods of preparing statements for some reason, > it's not improbable that you would want to know which way a given > statement was created, and seeing the PREPARE in there would be a useful > cue. The "from_sql" field of the view is an infinitely better way to determine the source of the prepared statement. Anyway, if there was a reasonably cheap way to present the query strings of protocol-level and SQL prepared statements in the same manner, I think we should definitely do so. Since there doesn't appear to be one, I'm content to just use the query string as sent by the user. I'll post a revised patch that does that soon. -Neil
On Tue, 2006-01-03 at 18:00 -0500, Neil Conway wrote: > Anyway, if there was a reasonably cheap way to present the query strings > of protocol-level and SQL prepared statements in the same manner, I > think we should definitely do so. Since there doesn't appear to be one, > I'm content to just use the query string as sent by the user. I'll post > a revised patch that does that soon. Attached is the patch I applied to HEAD that uses the query string supplied by the client, without any rewriting. -Neil