Re: Delaying the planning of unnamed statements until Bind

Oliver Jowett <oliver(at)opencloud(dot)com> writes:
> Following a recent discussion on the JDBC list
> (http://archives.postgresql.org/pgsql-jdbc/2004-05/msg00100.php) I'm
> looking at modifying when unnamed statements received via a v3 protocol
> Parse message are planned. The idea is to delay planning until the Bind
> message arrives, so that the planner benefits from knowing the actual
> parameter values in use. This means that clients such as JDBC can use an
> unnamed Parse/Bind as a way to provide typed and/or binary parameter
> values without running the risk of worsening the query plan.

I'm a bit concerned about driving such a change in behavior off nothing
more than whether the statement is named or not. The protocol spec does
say this:

: In addition, an "unnamed" prepared statement and portal exist. Although
: these behave largely the same as named objects, operations on them are
: optimized for the case of executing a query only once and then
: discarding it, whereas operations on named objects are optimized on the
: expectation of multiple uses.

so you could argue that this change is just an optimization of that
kind. It had better be documented though.

> 3. In execute_bind_message, if the source statement is not planned
> (plan_list == NULL), plan it, passing the concrete parameter values
> received in the Bind message.

I am not sure you can rely on plan_list == NULL to be the driving test;
consider an empty query string for instance. It'd be better to add a
boolean planning_done field to the struct.

> The planning happens using the target
> portal's memory context; the resulting plan_list is only used for this
> portal and the source statement remains unplanned.

I don't like that at all. Do the planning using the given parameters,
but save the plan. Otherwise you have substantially pessimized the
behavior for the case where an unnamed statement is reused.

> 4. In planner(), if parameters are provided, replace Param nodes with
> Const nodes in the query tree where appropriate.

No, you can't replace Params with Consts. You can use the values of
Params in the places where selfuncs.c wants to extract constant values
for estimation purposes.

> ... it requires threading the parameter values through many
> planner functions. There are quite a few (20-30ish?) functions affected.

This would be a pretty thorough mess, especially after you got done
propagating the info to selectivity functions. Frankly I'd suggest
using a planner global variable instead; see PlannerParamList for a
model.

regards, tom lane