Re: [HACKERS] Bug in index scans with Locale support enabled

Barry Lind <barry(at)xythos(dot)com> writes:
> Now the real problem comes in when either the like or regex operators
> are used in a sql statement.

Right. As of 7.1beta1 we are dealing with this by suppressing LIKE/regex
index optimization in all locales other than "C". That's a pretty crude
answer but it seems the only reliable one :-(.

> Over the last couple of months that I have been on the postgres mail
> lists there have been a few people who reported that queries of the form
> "like '/aaa/bbb/%' don't work. From the above information I have
> determined that such queries don't work if:
> a) database is built with Locale support enabled (--enable-locale)
> b) the database is running with locale en_US
> c) the column the like is being performed on is indexed
> d) the query execution plan uses the above index

en_US is not the only dangerous locale, unfortunately.

I suspect that there are some non-C locales in which we could still do
the optimization safely. The trick is to know which ones have collation
rules that are affected by character combinations, multi-pass ordering
rules, etc. Do you have any info on that?

BTW, thanks for the very clear explanation --- we can point people at
this next time the question comes up, which it does regularly...

regards, tom lane