Tom Lane <tgl@sss.pgh.pa.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