Re: Another problem with indices?

Zlatko Calusic <zlatko(at)iskon(dot)hr> writes:
>>>> but if I add one '/' before wildcard '%' (as I initially would like to
>>>> do it):
>>
>>>> filedb=# select file from filenew where file like '/home/zcalusic/rcs/%';
>>>> file
>>>> ------
>>>> (0 rows)

> Postgres version is 7.0.2. (running on Debian GNU/Linux)
> I'm using hr_HR locale (Croatian locale), LATIN2 encoding.

>> Is there an index on filenew(file)? If so, does dropping it change
>> the results?

> I have an index on the column in the question, and yes, droping the
> index instantly recovers from the bad behavior i.e. queries return
> good results.

Hmm. Is it possible that you have mistakenly started the postmaster
with different LOCALE settings at different times in the past? That
can corrupt indexes (items get inserted in unexpected places because
the expected sort order changes with the locale). If things work OK
immediately after dropping and recreating the index on filenew(file),
then I'd suspect this mistake.

It's very easy to mess up this way if you sometimes start the postmaster
by hand while at other times it's started from a system boot script or
inittab. The boot scripts and init can have different environments
than your interactive session has...

regards, tom lane