DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:mime-version:content-type;
	b=oesUByDPF/YncCGuyI/+XFd0otddHW7shO9v4sYX3wWv0al6qCdQrHkdSIyWA8BS04
	rtAA2GFlPkgmDC+ZWGHms4xby+dNusyH7Ll3rzkhdSZTurwbGUsZfiQc4E2JUXueiAZU
	efFmyURCDgV2jWqbObQ084HiYKFRRl3Be1U9c=
Message-ID: <2fa647f60812280419t3c54d14bgd1612354e789b50c@mail.gmail.com>
Date: Sun, 28 Dec 2008 13:19:29 +0100
From: "Thue Janus Kristensen" <thuejk@gmail.com>
To: pgsql-interfaces@postgresql.org
Subject: Getting a list of prepared statements
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_131930_15714846.1230466769098"

------=_Part_131930_15714846.1230466769098
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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

------=_Part_131930_15714846.1230466769098
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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]<br><br>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 &quot;predefined&quot; 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.<br>
<br>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&#39;t think you can tell whether it is the same actual query as the new
one you wanted to define.<br>
<br>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.<br>
<br>I don&#39;t know what the interface should be; a possibly ignorant
suggestion is implementing a build-in stored procedure such that &quot;SELECT * FROM _prepared_statement_list() WHERE name LIKE &#39;a%&#39;&quot; could return a table of
(name TEXT, query TEXT) rows?<br>Another possibility would be using something like &quot;\d&quot; in the psql client (however this is implemented in pgsql), but the IMO the stored procedure approach is better, as<br>-You can use SELECT and WHERE clause to filter the result.<br>
-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.<br><br>
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. :)<br>
<br>[1] <a href="http://archives.postgresql.org/pgsql-novice/2004-07/msg00089.php">http://archives.postgresql.org/pgsql-novice/2004-07/msg00089.php</a><br>[2] <a href="http://archives.postgresql.org/pgsql-novice/2004-07/msg00090.php">http://archives.postgresql.org/pgsql-novice/2004-07/msg00090.php</a><br>
<br>Regards, Thue

------=_Part_131930_15714846.1230466769098--