Re: BUG #5816: index not used in function - Mailing list pgsql-bugs

Frank, thanks for educating me.=20

        -- Korry

> -----Original Message-----
> From: Korry Douglas [mailto:korry.douglas@enterprisedb.com]=20
> 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
>=20
>=20=20
>=20
> > We may have different perceptions of something being a 'bug'. I always
>=20
> > have several simple ways of determining it. One of them is when a
>=20
> > work-around is in the proposal. Yours is one.
>=20
>=20=20
>=20
> It seems to me that the important question in this case is whether or not=
 the query produced the correct result.
>=20
> 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.
>=20
>=20=20
>=20
> You are complaining about a performance issue, not a correctness issue, r=
ight?
>=20
> No. I am pointing out a low-level-design/implementation defect. Poor perf=
ormance is the symptom. Poor performance due to sub-optimal execution of th=
e spec is a correctness issue. (See my comment on the nature of a function =
below)
>=20
>=20=20
>=20
> Kevin's work-around is meant to help you *gain better performance*, not t=
o obtain correct results when you are getting incorrect results.
>=20
> If it is not a bug, why do we need a work-around?
>=20
>=20=20
>=20
> > There can be quite a number of ways of looking at the issue. First, it
>=20
> > is truly an implementation matter (making it in the true sense a bug). I
>=20
> > do not believe that the spec would in formal way say that 'well, there
>=20
> > are caveats where you have to do this and that to work around'.
>=20
>=20=20
>=20
> The "spec" (by which I assume you mean the SQL standard) says nothing abo=
ut which execution plan will be selected (by) the optimizer.
>=20
> 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 i=
nside.
>=20
>=20=20
>=20
> > <snip>
>=20
> > If by 'kept from one execution to another' means that (the concept of) a
>=20
> > plan is implemented static, this can be a low level design issue, which
>=20
> > in general will still be regarded as implementation, thus a bug.
>=20
>=20=20
>=20
> The execution plan is not quite static - it is computed the first time yo=
u run the function (within a session) and is discarded when your session en=
ds (or when the compiled function/execution plan becomes obsolete because o=
f a change to a dependency).
>=20
>=20=20
>=20
> That is by design.
>=20
> Then the design is poor.
>=20
>=20=20
>=20
> 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.
>=20
>=20=20
>=20
> This seems far fetched and irrelevant. Whatever is truly static should be=
 implemented static; whatever is dynamic should be implemented dynamic; wha=
tever is partially static, the static part should be static and the dynamic=
 part should be dynamic. It is natural and correct treatment.
>=20
>=20=20
>=20
> Purely dynamic situation in which the final query can not be determined i=
n any fashion, will have to be constructed either outside of the function o=
r within, so EXECUTE is the only way to handle. What do you think of requir=
ing the caller to construct such a static statement as =93select count(*) f=
rom sometable=94 and use EXECUTE?
>=20
>=20=20
>=20
> By the same token, =93select thiscolumn from thistable where upper(thisco=
lumn) like $1=94 has to be treated statically for the static part. The only=
 unknown is the parameter, which can be, by the right design and implementa=
tion, 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 in=
terpreted 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 situ=
ation results in the loss of the said ability, it is a defect to be address=
ed. Whether one wants to address it is one issue. A defect is a defect.
>=20
>=20=20
>=20
> You seem to suggest that the plan was only built at (the first) execution=
. That is poor design/implementation.
>=20
>=20=20
>=20
> 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 a=
vailable. The user does not have to know what table or anything for that ma=
tter is involved. When you push for what is suggested as a work-around, it =
defeats one of the basic purposes for a function.
>=20
>=20=20
>=20
> More can be said, but why one wants to defend a defect is quite beyond me.
>=20
>=20=20
>=20
>                   -- Korry
>=20