Re: Allow pooled connections to list all prepared queries

David Brown <dave(at)spoonguard(dot)org> writes:
> 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.

> * Extended PostgreSQL's SQL grammar to capture the original SQL query
> string for PREPARE statements.

This seems much the ugliest and most invasive part of the patch. I'd
suggest doing something similar to what pg_proc.c is doing:

/* We can get the original query text from the active portal (hack...) */
Assert(ActivePortal && ActivePortal->status == PORTAL_ACTIVE);
queryText = ActivePortal->sourceText;

> * Modified backend/commands/prepare.c to keep some additional statistics
> in the prepared statement hash table (plan creation time, execution
> count, etc.)

The usefulness of this seems pretty dubious. You aren't going to have a
bunch of random bits of code sharing a connection; it's going to be a
single application that probably knows perfectly well exactly which
queries it needs prepared. So I don't think the stats will pay for
themselves.

> * Added an accessor function to allow for "raw" access to the prepared
> statement hash table (necessary for sequential access).

It would have been better to put the function that needs this access
into prepare.c. There is no point in trying to hide a data structure
inside a module if we then turn around and expose the data structure
to the world...

regards, tom lane