Frank, thanks for educating me.
-- Korry
> -----Original Message-----
> From: Korry Douglas [mailto:korry.doug...@enterprisedb.com]
> Sent: Sunday, January 09, 2011 2:34 PM
> To: frank
> Cc: 'Kevin Grittner'; pgsql-bugs@postgresql.org
> Subject: Re: [BUGS] BUG #5816: index not used in function
>
>
>
> > We may have different perceptions of something being a 'bug'. I always
>
> > have several simple ways of determining it. One of them is when a
>
> > work-around is in the proposal. Yours is one.
>
>
>
> It seems to me that the important question in this case is whether or not the
> query produced the correct result.
>
> The important question by nature is not whether correct result is produce
> eventually. In this case, the important thing is the inability to use the
> index when in fact, if designed and implemented correctly, it could.
>
>
>
> You are complaining about a performance issue, not a correctness issue, right?
>
> No. I am pointing out a low-level-design/implementation defect. Poor
> performance is the symptom. Poor performance due to sub-optimal execution of
> the spec is a correctness issue. (See my comment on the nature of a function
> below)
>
>
>
> Kevin's work-around is meant to help you *gain better performance*, not to
> obtain correct results when you are getting incorrect results.
>
> If it is not a bug, why do we need a work-around?
>
>
>
> > There can be quite a number of ways of looking at the issue. First, it
>
> > is truly an implementation matter (making it in the true sense a bug). I
>
> > do not believe that the spec would in formal way say that 'well, there
>
> > are caveats where you have to do this and that to work around'.
>
>
>
> The "spec" (by which I assume you mean the SQL standard) says nothing about
> which execution plan will be selected (by) the optimizer.
>
> No. Whatever the spec, it will never say that a function will not work as a
> function, or something that works outside one will not work once moved inside.
>
>
>
> > <snip>
>
> > If by 'kept from one execution to another' means that (the concept of) a
>
> > plan is implemented static, this can be a low level design issue, which
>
> > in general will still be regarded as implementation, thus a bug.
>
>
>
> The execution plan is not quite static - it is computed the first time you
> run the function (within a session) and is discarded when your session ends
> (or when the compiled function/execution plan becomes obsolete because of a
> change to a dependency).
>
>
>
> That is by design.
>
> Then the design is poor.
>
>
>
> If you want a dynamic plan that is re-computed each time you execute the
> query, you can get that behavior by using dynamic SQL, as Kevin suggested.
>
>
>
> This seems far fetched and irrelevant. Whatever is truly static should be
> implemented static; whatever is dynamic should be implemented dynamic;
> whatever is partially static, the static part should be static and the
> dynamic part should be dynamic. It is natural and correct treatment.
>
>
>
> Purely dynamic situation in which the final query can not be determined in
> any fashion, will have to be constructed either outside of the function or
> within, so EXECUTE is the only way to handle. What do you think of requiring
> the caller to construct such a static statement as “select count(*) from
> sometable” and use EXECUTE?
>
>
>
> By the same token, “select thiscolumn from thistable where upper(thiscolumn)
> like $1” has to be treated statically for the static part. The only unknown
> is the parameter, which can be, by the right design and implementation,
> delayed till execution (runtime). The code to deal with this is what I
> pointed out (via a conditional). If the plan is a piece of code, then the
> conditional will be in it. If the plan is a piece of text to be further
> interpreted for actual execution (why would one want to do it that way?), the
> conditional could contain a text reference to two pieces of code (w/o the use
> of the index). If it is not properly designed/implemented and such situation
> results in the loss of the said ability, it is a defect to be addressed.
> Whether one wants to address it is one issue. A defect is a defect.
>
>
>
> You seem to suggest that the plan was only built at (the first) execution.
> That is poor design/implementation.
>
>
>
> Lastly, what is a function? One of the fundamental features of a function is
> encapsulation. One is guaranteed some well-defined output based on
> well-defined input. No implementation detail is necessary or is obliged to be
> available. The user does not have to know what table or anything for that
> matter is involved. When you push for what is suggested as a work-around, it
> defeats one of the basic purposes for a function.
>
>
>
> More can be said, but why one wants to defend a defect is quite beyond me.
>
>
>
> -- Korry
>
