Re: Avoiding bad prepared-statement plans.

Kris Jurka wrote:
>
> The JDBC driver has two methods of disabling permanently planned prepared
> statements:
>
> 1) Use the version two frontend/backend protocol via adding
> protocolVersion=2 to your URL. This interpolates all parameters into
> the query on the client side.
>
> 2) Execute PreparedStatements using the unnamed statement rather than a
> named statement via adding prepareThreshold=0 to your URL. A named
> statement is expected to be re-used for later execution and ignores the
> passed parameters for planning purposes. An unnamed statement may be
> re-used, but it doesn't expect to be. The unnamed statement uses the
> passed parameters for planning purposes, but still cannot make certain
> optimatizations based on the parameter values because it may be
> re-executed again later with different parameters. For example a LIKE
> query with a parameter value of 'ABC%' cannot be transformed into range
> query because the next execution may use a different parameter value for
> which the transform is not valid. By default the driver switches to using
> a named statement after the same PreparedStatement object is executed five
> times.
>
> http://jdbc.postgresql.org/documentation/84/connect.html#connection-parameters
> http://jdbc.postgresql.org/documentation/84/server-prepare.html

Can someone explain to me why we only do "delayed binding" for unnamed
prepared queries? Why do we not allow this option for named protocol
prepared queries and SQL prepared queries?

Here is what our documentation has in the protocols section:

The unnamed prepared statement is likewise planned during Parse processing
if the Parse message defines no parameters. But if there are parameters,
query planning occurs during Bind processing instead. This allows the
planner to make use of the actual values of the parameters provided in
the Bind message when planning the query.

and here is someone who is having problems with the generic plans we
create:

http://www.odecee.com.au/blogs/?p=134

Can we not document this better?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +