Re: Allow pooled connections to list all prepared queries

David Brown <dave@spoonguard.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