Hi:
Attached is a loadable module (and a small backend patch) that allows a
client to list the available query plans (created with PREPARE, or with a
'parse' protocol message) on a particular connection.
This work was done in response to an item in the TODO:
* Allow pooled connections to list all prepared queries
This would allow an application inheriting a pooled connection to know
the queries prepared in the current session.
I've done the following:
* Extended PostgreSQL's SQL grammar to capture the original SQL query
string for PREPARE statements. Previously, the PostgreSQL kernel provided
access to a prepared query's original SQL, but only for statements
prepared with a libpq "parse" message - not with a PREPARE statement.
* Modified backend/commands/prepare.c to keep some additional statistics
in the prepared statement hash table (plan creation time, execution
count, etc.)
* Added an accessor function to allow for "raw" access to the prepared
statement hash table (necessary for sequential access).
* Implemented a PostgreSQL function to list the available query plans on
the current connection. This function, called pg_prepared_query_plans,
returns a set of tuples, each of which contain a plan name, the SQL query
string associated with the plan name, the number of times the plan has
been executed, the plan creation time, and the plan's last access time.
This should provide a way for clients sharing a connection pool to also
share prepared query plans. When a client inherits a connection from the
pool, it can use the results of a 'select * from pg_prepared_query_plans()'
to fill a (sql -> plan_name) hash table. By probing this hash table before
executing a PREPARE, duplicate PREPAREs can be skipped, even if the initial
PREPARE was performed by a different client.
I've attached three files: one is a diff against the backend, the other two
are the loadable module (source + create script).
If anyone is interested, I've also attached a small proof-of-concept patch
for DBD::Pg - it does server-side plan caching as described above (by
leaving the prepared plans on the connection at disconnect, and filling a
hash with the list of prepared plans at connect), and uses a simple LRU
deallocation policy ($ENV{'PLANCACHE_MAX'} is the high watermark, and
$ENV{'PLANCACHE_REAP'} is the number of plans below the high watermark to
target when deallocating - both should be set prior to DBI->connect).
All of this was done while experimenting with plan caching for a database
systems course. I have a more detailed write-up (with some synthetic
benchmarks) if it would be helpful.
HTH,
- Dave