Getting a list of prepared statements

[Thue Janus Kristensen]

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

[Tom Lane-2]

"Thue Janus Kristensen" <thuejk@...> 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

--
Sent via pgsql-interfaces mailing list (pgsql-interfaces@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-interfaces

[Thue Janus Kristensen]

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@...> wrote:

"Thue Janus Kristensen" <thuejk@...> 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