Getting a list of prepared statements

From: "Thue Janus Kristensen" <thuejk(at)gmail(dot)com>
To: pgsql-interfaces(at)postgresql(dot)org
Subject: Getting a list of prepared statements
Date: 2008-12-28 12:19:29
Message-ID: 2fa647f60812280419t3c54d14bgd1612354e789b50c@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-interfaces by date

  From Date Subject
Next Message Tom Lane 2008-12-28 22:15:29 Re: Getting a list of prepared statements
Previous Message Whit Armstrong 2008-12-20 17:18:30 Re: simple example of copying data from a c/c++ array into postgres