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

Kevin,

Very thrilled to hear back from you (and surprised at the promptness,
too)!

Please do not take this personal as it is a tech issue and I am treating
it thus.

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.

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'.

I gave specific parameters in my example (if you go back to my original
email/bug report). To be precise, the parameter does not start with a
wild card. By giving '%X%', you may have changed the nature a bit.
Regardless, it is still the same issue: the implementation did not
faithfully reflect the specification, which did not explicitly (nor
implicitly) endorse such behavior. From a tech perspective, you are
actually partially correct, but only partially.

How can a leading wild-card warrant the use of index? It can not, for
sure. However, the implementation is still defective. Let me be
specific. The logic should be a conditional: if (at run time when the
argument is parsed and examined) a leading wild-card is found, it should
branch to not use index (as it can not), otherwise it should. The
compilation already sees the text [upper("thisColumn")]. Assuming there
are no other issues and embedded wild-cards are correctly handled (e.g.
through index search and then a filter), the above described simple
logic is the only thing needed to be thrown in.

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.

Lastly, to expose it as a work-around, let us take the situation where I
input n parameters, the different combinations will be used to query
against a number of tables (inside the function). Due to the issue, I
have to input all the actual query text as parameters into the function
(which by now is no longer truly a function). But that could be
factorial!

Anyway, I hope this can be re-considered. I do not want to create
headaches, but if resource constraint is a factor, I may offer to fix
the problem if you could kindly provide with your lexer code and the
code files that are/may be affected, as well as POC for QA.

Once again, I appreciate your reply and thank you for the attention.

Regards,
Frank

-----Original Message-----
From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
Sent: Thursday, January 06, 2011 12:50 PM
To: pgsql-bugs@postgresql.org; frank
Subject: Re: [BUGS] BUG #5816: index not used in function

"frank" <frank@ros-i.com> wrote:

> WHERE upper("thisColumn") like $1

The function's plan is kept from one execution to another, and it
can't know what will be in the first parameter -- perhaps '%X%'?  If
you build up the statement in a string and EXECUTE it, you might get
the desired behavior.

Anyway, next time you have an issue like this, please post to the
performance list; this is not a bug.

-Kevin