Re: Another problem with indices?

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

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

Nope. I'm quite sure I'm running with the same locale from the start.

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

I tried recreating the index, but once again query stops working.

filedb=# create index filenew_file_key on filenew(file);
CREATE
filedb=# select file from filenew where file like
'/home/zcalusic/.netscape%';

file
------------------------------------------------------------------
/home/zcalusic/.netscape
/home/zcalusic/.netscape/abook.nab
/home/zcalusic/.netscape/archive
/home/zcalusic/.netscape/bookmarks.html
...

filedb=# select file from filenew where file like
'/home/zcalusic/.netscape/%';
file
------
(0 rows)

Nothing!?!

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

Unfortunately I think this is a genuine bug.

But if you have some other idea, please don't hesitate to ask me to
try. Do you want the program itself (it's a Perl DBI application,
unfinished... :))?

--
Zlatko