pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedGetting a list of prepared statements
3+ messages / 2 participants
[nested] [flat]
* Getting a list of prepared statements
@ 2008-12-28 12:19 Thue Janus Kristensen <thuejk@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Thue Janus Kristensen @ 2008-12-28 12:19 UTC (permalink / raw)
To: pgsql-interfaces
It would be very nice to have a way to get a list of prepared statements.
Currently there does not seem to be any: [1]
My problem is this: I am using pgsql with PHP, connecting using
pg_pconnect(), which will reuse pgsql connections if possible. This means
that the pgsql connection returned to my freshly instantiated PHP instance
can have "predefined" prepared statements. It would be very nice for
performance if I were able to reuse those, however there is no way to know
what prepared statements are defined, since there is no pgsql interface to
list them. In fact, I am currently prefixing all my prepared query names
with a per-session random string to avoid name clashes from previous
sessions.
The only way to test if a prepared statement with a given name exists seems
to be to try to define a new one with the same name. If this gives an error
then there exist a statement with that name, but I don't think you can tell
whether it is the same actual query as the new one you wanted to define.
M. Bastin suggests in [2] to just keep a list in the client app. In my case
this would mean modifying PHP to keep the list together with its pool of
connections. I guess one could modify PHP, but it seems better and simpler
to just modify pgsql to return the list (the pgsql connection presumably
already keeps the list); that would fix the problem also for other
programming languages using persistent pgsql connections.
I don't know what the interface should be; a possibly ignorant suggestion is
implementing a build-in stored procedure such that "SELECT * FROM
_prepared_statement_list() WHERE name LIKE 'a%'" could return a table of
(name TEXT, query TEXT) rows?
Another possibility would be using something like "\d" in the psql client
(however this is implemented in pgsql), but the IMO the stored procedure
approach is better, as
-You can use SELECT and WHERE clause to filter the result.
-The query will be directly available to for example PHP, without having to
add a new postgresql function to access it. If fx PHP really wants such a
function, they can implement it by just executing such a query.
I am not a pgsql hacker, so I am probably not going to implement this
myself. But it would be nice if the pgsql project added it to the pgsql TODO
list. :)
[1] http://archives.postgresql.org/pgsql-novice/2004-07/msg00089.php
[2] http://archives.postgresql.org/pgsql-novice/2004-07/msg00090.php
Regards, Thue
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Getting a list of prepared statements
@ 2008-12-28 22:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: Thue Janus Kristensen <thuejk@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2008-12-28 22:15 UTC (permalink / raw)
To: Thue Janus Kristensen <thuejk@gmail.com>; +Cc: pgsql-interfaces
"Thue Janus Kristensen" <thuejk@gmail.com> writes:
> It would be very nice to have a way to get a list of prepared statements.
> Currently there does not seem to be any: [1]
select * from pg_prepared_statements ?
(exists since 8.2)
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Getting a list of prepared statements
@ 2008-12-28 22:26 Thue Janus Kristensen <thuejk@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Thue Janus Kristensen @ 2008-12-28 22:26 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-interfaces
Ah - thanks. :)
I must be trusting too much in Google, because all Google returned was a
pages telling me that it could not be done. But now I see that it is
described clearly at the obvious location,
http://www.postgresql.org/docs/8.3/interactive/sql-prepare.html :-/
However, this won't be a problem in the future, for already the top hit on
Google for "postgresql get list of prepared statements" is this email
thread, with me asking stupid questions the answer to which is clearly
visible in the manual *hmm*.
Regards, Thue
On Sun, Dec 28, 2008 at 11:15 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Thue Janus Kristensen" <thuejk@gmail.com> writes:
> > It would be very nice to have a way to get a list of prepared statements.
> > Currently there does not seem to be any: [1]
>
> select * from pg_prepared_statements ?
>
> (exists since 8.2)
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-12-28 22:26 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-12-28 12:19 Getting a list of prepared statements Thue Janus Kristensen <thuejk@gmail.com>
2008-12-28 22:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-12-28 22:26 ` Thue Janus Kristensen <thuejk@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox