Thread: FE/BE protocol vs. parameterized queries

Picking out a specific point from the thread on prepared queries:

Currently, the handling of Parse/Bind on the unnamed statement seems to
go like this:
 - Parse on the unnamed statement does analysis and rewriting but does   not plan, storing the query in a special
memorycontext dedicated to   the unnamed statement
 
 - Bind on the unnamed statement plans the query (using the supplied   parameters) and stores the plan back in the
unnamedstatement's context
 

I believe this could usefully (and transparently to clients) be changed
so that Bind on the unnamed statement does _not_ store the plan back in
the unnamed statement's context, but instead produces a plan which is
only used _for that specific portal_. Thus, it would promote the parameters
to constants before planning, knowing that the plan could only be run once;
this would, I believe, allow the planner to produce a plan that was
equivalent to that of a non-parameterized query.

This would hopefully remove all cases where it is currently necessary to
use PQexec rather than PQexecParams, such as where parameterized limits,
immutable functions of parameters, partial indexes etc. are involved.

-- 
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

Andrew - Supernews <andrew+nonews@supernews.com> writes:
> I believe this could usefully (and transparently to clients) be changed
> so that Bind on the unnamed statement does _not_ store the plan back in
> the unnamed statement's context, but instead produces a plan which is
> only used _for that specific portal_.

That seems OK to me, since we document the unnamed statement/portal as
being optimized for one-shot execution.  Unfortunately it's probably
less than a trivial change, because the planner never assumes that
Params are constants; that would have to be changed somehow.
        regards, tom lane

I wrote:
> Andrew - Supernews <andrew+nonews@supernews.com> writes:
>> I believe this could usefully (and transparently to clients) be changed
>> so that Bind on the unnamed statement does _not_ store the plan back in
>> the unnamed statement's context, but instead produces a plan which is
>> only used _for that specific portal_.

> That seems OK to me, since we document the unnamed statement/portal as
> being optimized for one-shot execution.  Unfortunately it's probably
> less than a trivial change, because the planner never assumes that
> Params are constants; that would have to be changed somehow.

I've applied a patch to do this --- the planner change turned out to be
pretty trivial after all.

The infrastructure for the former planning method (using the first
Bind's parameters as sample values for estimation, but not as constants)
is still there, but it's not being used now.  Does anyone want to argue
for changing things to plan named statements that way?  I'm of two minds
about it myself; you can make a good case that it'd usually be a win,
but it's also not hard to envision scenarios where it'd be a loss.
        regards, tom lane

Tom Lane wrote:
> The infrastructure for the former planning method (using the first
> Bind's parameters as sample values for estimation, but not as constants)
> is still there, but it's not being used now.  Does anyone want to argue
> for changing things to plan named statements that way?  I'm of two minds
> about it myself; you can make a good case that it'd usually be a win,
> but it's also not hard to envision scenarios where it'd be a loss.

Although I don't have a clear opinion myself, I sometimes read on this list 
that people are using prepared statements to get safe, stable plans, i.e. 
plans that don't depend on the specific parameter input.

If you change that, I don't think they will be happy at all. I suggest 
leaving it as-is for 8.2. I think the user (i.e. driver) should be able to 
tell the backend, if they want planning for the first bind, or right at 
prepare.

Best Regards
Michael Paesold

> Although I don't have a clear opinion myself, I sometimes read on this list 
> that people are using prepared statements to get safe, stable plans, i.e. 
> plans that don't depend on the specific parameter input.

I definitely want the possibility of getting stable plans. That's only
possible if the planner does NOT take into account any parameter values.
If the statistics get quicker out of date than it's practical to run
analyze, but the plans would stay stable, it's better not to have
parameter values taken into account.
> If you change that, I don't think they will be happy at all. I suggest 
> leaving it as-is for 8.2. I think the user (i.e. driver) should be able to 
> tell the backend, if they want planning for the first bind, or right at 
> prepare.

That would be nice. We would probably use all 3 forms: - unnamed statement: prepare based on constant parameters; -
namedstatement: prepare based on the first set of parameter values; - named statement: prepare generic plan without
consideringparameter
 
values;

Cheers,
Csaba.