Re: Cached Query Plans (was: global prepared statements)

> If cached plans would be implemented, the dependence on parameter values
> could be solved too: use special "fork" nodes in the plan which execute
> different sub-plans depending on special parameter values/ranges,
> possibly looking up the stats at runtime, so that the plan is in a
> compiled state with the "decision points" wired in.
>
> This of course would mean a lot heavier planning and possibly a lot
> bigger plans, but you could afford that if you cache the plan. You could
> even have a special command to plan a query this way.

And, the "fork node" could mutter to itself "Strange, I'm getting 10000
rows instead of the 2 for which I was planned, perhaps I should switch to
a different plan..."

I have made another very simple hack to test for another option :

Bind message behaviour was modified :
- If the user asks for execution of a named prepared statement, and the
named statement does not exist in PG's prepared statements cache, instead
of issuing an error and borking the transaction, it Binds to an empty
statement, that takes no parameters, and returns no result. Parameters
sent by the user are consumed but not used.

The application was modified thusly :
- Calls to pg_query_params were changed to calls to the following function
:

function pg_query_cached( $sql, $params )
{
// Try to execute it, using the query string as statement name.
$q = pg_execute( $sql, $params );
if( !$q ) die( pg_last_error() );

// If it worked, return result to caller.
if( pg_result_status( $q, PGSQL_STATUS_STRING ) != "" )
return $q;

// If we got an empty query result (not a result with 0 rows which is
valid) then prepare the query
$q = pg_prepare( $sql, $sql );
if( !$q ) die( pg_last_error() );

// and execute it again
$q = pg_execute( $sql, $params );
if( !$q ) die( pg_last_error() );

return $q;
}

Pros :
- It works
- It is very very simple
- The user can choose between caching plans or not by calling
pg_query_params() (no cached plans) or pg_query_cached() (cached plans)
- It works with persistent connections

Cons :
- It is too simple
- Plans are cached locally, so memory use is proportional to number of
connections
- It is still vulnerable to search_path problems