Re: JDBC, prepared queries, and partitioning

Oliver Jowett wrote:
> Simon Riggs wrote:
>
>> What we need is a "send parameters inline" mode that can be set as an
>> option for a PreparedStatement. Nobody wants to use V2, we just want the
>> ability to re-plan a query every time. Perhaps that is best implemented
>> as a server side option that can be exposed via JDBC option, since this
>> is an SQL requirement and nothing to do with Java.
>
> Unfortunately you can't have your cake and eat it too. Many of the
> features specific to V3 *require* that parameters be sent out of line
> (e.g. parameter metadata, bytea streaming, possibly OUT parameters)
>
> As Kris said earlier in this thread what we really need is a way to tell
> the server "I will only ever use this statement once, with these
> particular parameter values, go ahead and optimize the plan on that basis"

Another possibility is to make the unnamed statement behaviour in the
server more aggressive.

The current behaviour is that planning only happens on the first Bind of
a particular unnamed statement, and parameter placeholders are left as
placeholders in the plan tree, with logic to pull in actual parameter
values when doing cost estimates involving those nodes.

Instead, we could change that so that whenever a Bind for the unnamed
statement is received, the query is replanned. Before the normal
optimization is run, the actual parameter values are substituted into
the parse/plan tree as constants (and so are subject to all the normal
optimizations such as constant folding and the constraint/partition
stuff). After execution we should discard the plan, since it's tied to
specific values. If that same unnamed statement gets reexecuted with
different parameter values later, no harm done - we replan it again with
the new values.

I vaguely remember suggesting something like this back in my changes to
8.0, but it was going to be too much of a change to the protocol
behaviour. Given the hindsight of a few major versions, are there
actually clients that reuse the unnamed statement in use, and would they
be badly affected by this change?

-O