Re: [PATCHES] TODO item: list prepared queries
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 *** doc/src/sgml/catalogs.sgml 29fbade056e00ee4a48ba6a3f686627f62a103cf --- doc/src/sgml/catalogs.sgml f265188b9a381a6c2e67f94290b56f9dc993f5d1 *** *** 4373,4378 --- 4373,4383 + pg_prepared_statements + current prepared statements + + + pg_prepared_xacts currently prepared transactions *** *** 4778,4783 --- 4783,4883 + + pg_prepared_statements + + +pg_prepared_statements + + + +The pg_prepared_statements view displays +all the prepared statements that are available in the current +session. See for more information about prepared +statements. + + + +pg_prepared_statements contains one row +for each prepared statement. Rows are added to the view when a new +prepared statement is created, and removed when a prepared +statement is released (for example, via the +command). + + + +pg_prepared_statements Columns + + + + + Name + Type + References + Description + + + + + name + text + + +The identifier of the prepared statement. + + + + statement + text + + +The query string submitted by the client to create this +prepared statement. For prepared statements created via SQL, +this is the PREPARE statement submitted by +the client. For prepared statements created via the +frontend/backend protocol, this is the text of the prepared +statement itself. + + + + prepare_time + timestamptz + + +The time at which the prepared statement was created. + + + + parameter_types + oid[] + + +The expected parameter types for the prepared statement in the form of +an array of type OIDs. + + + + from_sql + boolean + + +true if the prepared statement was created +via the PREPARE SQL statement; +false if the statement was prepared via the +frontend/backend protocol. + + + + + + + +The pg_prepared_statements view is read only. + + + pg_prepared_xacts *** doc/src/sgml/ref/prepare.sgml 17fce269c43549b6ffa7bf3d5770da9fbdf18896 --- doc/src/sgml/ref/prepare.sgml 98824b3ad9ac4ffa50677f3b8ac821f8a1c84e16 *** *** 145,150 --- 145,155 the documentation. + + +You can see all available prepared statements of a session by querying the +pg_prepared_statements system view. + *** src/backend/catalog/system_views.sql 307260ff7bc30a48c0c60a40d4130f70310ebff2 --- src/backend/catalog/system_views.sql 7b92550bbcaf9d0ee99c12527f7785385cfeefe7 *** *** 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 *** src/backend/commands/prepare.c 3c1a8b677a84566407472a0b7b85b4cb86587956 --- src/backend/commands/prepare.c dc237de8d42ca2cd72d75e3b9bf64b8786702e5a *** *** 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
Re: [PATCHES] TODO item: list prepared queries
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] TODO item: list prepared queries
Neil Conway <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] TODO item: list prepared queries
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] TODO item: list prepared queries
Neil Conway <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] TODO item: list prepared queries
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] TODO item: list prepared queries
Neil Conway <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] TODO item: list prepared queries
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 - 2.115 --- doc/src/sgml/catalogs.sgml 1 Jan 2006 06:22:50 - *** *** 4373,4378 --- 4373,4383 + pg_prepared_statements + available prepared statements for the current session + + + pg_prepared_xacts currently prepared transactions *** *** 4778,4783 --- 4783,4883 + + pg_prepared_statements + + +pg_prepared_statements + + + +The view pg_prepared_statements displays all +available prepared statements for the current session. See for more +information about prepared statements. + + + +pg_prepared_statements 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 command. + + + +pg_prepared_statements Columns + + + + + Name + Type + References + Description + + + + + name + text + + +The identifier of the prepared statement. + + + + statement + text + + +The reverse compiled SQL command used for preparing this statement. + + + + prepare_time + timestamptz + + +The time when the prepared statement was created. + + + + parameter_types + oid[] + + +The expected parameter types for the prepared statement in the form of +an array of type OIDs. + + + + from_sql + boolean + + +true is the prepared statement was created +via the PREPARE SQL statement; +false if the statement was prepared via the +frontend/backend protocol. + + + + + + + +The pg_prepared_statements view is read only. + + + +The prepare_time contains the time at +which the statement was prepared (and not the transaction start +time of the preparing transaction). + + + + pg_prepared_xacts 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 - 1.16 --- doc/src/sgml/ref/prepare.sgml 1 Jan 2006 01:09:36 - *** *** 145,150 --- 145,155 the documentation. + + +You can see all available prepared statements of a session by querying the +pg_prepared_statements system view. + 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.sql6 Oct 2005 02:29:15 - 1.22 --- src/backend/catalog/system_views.sql1 Jan 2006 06:04:04 - *** *** 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:
Re: [PATCHES] TODO item: list prepared queries
Bruce Momjian 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] TODO item: list prepared queries
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 - 1.473
--- src/backend/tcop/postgres.c 31 Dec 2005 16:49:09 -
***
*** 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 : "",
query_string)));
--- 1146,1152
if (log_statement == LOGSTMT_ALL)
ereport(LOG,
! (errmsg("statement: [protocol] PREPARE %s AS
%s",
(*stmt_name != '\0') ?
stmt_name : "",
query_string)));
***
*** 1449,1455
/* We need to output the parameter values someday */
if (log_statement == LOGSTMT_ALL)
ereport(LOG,
! (errmsg("statement: [client] %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] %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 : "",
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 : "",
por
Re: [PATCHES] TODO item: list prepared queries
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] TODO item: list prepared queries
daveg wrote: > On Fri, Dec 30, 2005 at 05:55:23PM -0500, Bruce Momjian wrote: > > Tom Lane wrote: > > > Neil Conway <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] TODO item: list prepared queries
On Fri, Dec 30, 2005 at 05:55:23PM -0500, Bruce Momjian wrote: > Tom Lane wrote: > > Neil Conway <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] If simplicity worked, the world would be overrun with insects. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] TODO item: list prepared queries
Tom Lane wrote:
> Neil Conway <[EMAIL PROTECTED]> 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 - 1.471
--- src/backend/tcop/postgres.c 30 Dec 2005 22:27:51 -
***
*** 1146,1152
if (log_statement == LOGSTMT_ALL)
ereport(LOG,
! (errmsg("statement: PREPARE %s AS %s",
(*stmt_name != '\0') ?
stmt_name : "",
query_string)));
--- 1146,1152
if (log_statement == LOGSTMT_ALL)
ereport(LOG,
! (errmsg("statement: [client] PREPARE %s AS %s",
(*stmt_name != '\0') ?
stmt_name : "",
query_string)));
***
*** 1449,1455
/* We need to output the parameter values someday */
if (log_statement == LOGSTMT_ALL)
ereport(LOG,
! (errmsg("statement: %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] %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 : "",
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 : "",
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_s
Re: [PATCHES] TODO item: list prepared queries
"" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] TODO item: list prepared queries
On December 14, 4:58 pm Tom Lane <[EMAIL PROTECTED]> wrote: > "Michael Paesold" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] TODO item: list prepared queries
"Michael Paesold" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] TODO item: list prepared queries
Tom Lane wrote: Neil Conway <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] TODO item: list prepared queries
Neil Conway <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] TODO item: list prepared queries
Neil Conway <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] TODO item: list prepared queries
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] TODO item: list prepared queries
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] TODO item: list prepared queries
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] TODO item: list prepared queries
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.sgml2005-11-05 00:13:59.0
+0100
--- cvs.build/pgsql/doc/src/sgml/catalogs.sgml 2005-12-13 00:08:45.0
+0100
***
*** 4373,4378
--- 4373,4383
+ pg_prepared_statements
+ available prepared statements for the current session
+
+
+
pg_prepared_xacts
currently prepared transactions
***
*** 4778,4783
--- 4783,4862
+
+ pg_prepared_statements
+
+
+pg_prepared_statements
+
+
+
+The view pg_prepared_statements displays all
available
+prepared statements for the current session (see for details).
+
+
+
+pg_prepared_statements 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 command.
+
+
+
+pg_prepared_statements Columns
+
+
+
+
+ Name
+ Type
+ References
+ Description
+
+
+
+
+ name
+ text
+
+
+The identifier of the prepared statement.
+
+
+
+ statement
+ text
+
+
+The SQL command used for preparing this statement.
+
+
+
+ preparetime
+ timestamptz
+
+
+The time when the statement got prepared.
+
+
+
+
+
+
+
+The pg_prepared_statements view is read only.
+
+
+
+The preparetime reflects the real system time at
+which the statement got prepared (and not the transaction start time of the
+preparing transaction).
+
+
+
+
pg_prepared_xacts
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.0
+0200
--- cvs.build/pgsql/doc/src/sgml/ref/prepare.sgml 2005-12-12
16:04:40.0 +0100
***
*** 145,150
--- 145,155
the
documentation.
+
+
+You can see all available prepared statements of a session by querying the
+pg_prepared_statements system view.
+
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.0 +0200
--- cvs.build/pgsql/src/backend/catalog/system_views.sql2005-12-12
16:04:40.0 +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.c2005-11-29 02:25:49.0
+0100
--- cvs.build/pgsql/src/backend/commands/prepare.c 2005-12-12
16:14:44.0 +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_S
Re: [PATCHES] TODO item: list prepared queries
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] TODO item: list prepared queries
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/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
