Re: Backpatching of "Teach the regular expression functions to do case-insensitive matching"

On Friday, May 06, 2011 04:30:01 AM Robert Haas wrote:
> On Thu, May 5, 2011 at 5:21 AM, Andres Freund <andres(at)anarazel(dot)de> wrote:
> > In my opinion this is actually a bug in < 9.0. As its a (imo) low impact
> > fix thats constrained to two files it seems sensible to backpatch it now
> > that the solution has proven itself in the field?
> > The issue is hard to find and has come up several times in the field. And
> > it has been slightly embarassing more than once ;)
> Can you share some more details about your experiences?
About the embarassing or hard to find part?

One of the hard to find part parts involved a search (constraining word order
after a tsearch search) where slightly fewer than usual search results were
returned in production.
Nobody had noticed during testing that case insensitive search worked for most
things except multibyte chars as the tested case was something like: SELECT
'ÖFFENTLICHKEIT' ~* 'Öffentlichkeit' and the regex condition was only relevant
when searching for multiple words.

One of the emarassing examples was that I suggested moving away from a
solution using several ILIKE rules to one case insenitive regular expression.
Totally forgetting that I knew that this was only fixed in 9.0. This turned out
to be faster. And it turned out to be wrong. In production :-(.

Both sum up that the problem is often not noticed as most of the people
realizing that that case could be a problem don't have a knowledge of the
content and don't notice the problem until later...

Andres