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

-----Original Message-----
From: Korry Douglas [mailto:korry.douglas@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