Thread: Another problem with indices?
Hi!
Not long after I resolved my last problem with indices ('vaccum
analyze'), I stumbled upon another.
Check this:
filedb=# select file from filenew where file like '/home/zcalusic/rcs%';
returns
file
----------------------------------------------
/home/zcalusic/rcs
/home/zcalusic/rcs/linux
/home/zcalusic/rcs/linux/0_REPORTING-,v
/home/zcalusic/rcs/linux/10_bug-list.t,v
/home/zcalusic/rcs/linux/11_00-INDEX,v
...
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)
No rows???
How can that be, when output above shows I DO have rows that satisfy
this query?
--
Zlatko
Zlatko Calusic <zlatko@iskon.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)
> No rows???
Which Postgres version are you running, and in what LOCALE setting
on what platform?
Is there an index on filenew(file)? If so, does dropping it change
the results?
regards, tom lane
I am having the exact same problem. I am quite sure this used to work
in 7.0.0, but it certainly seems broken under 7.0.2. Does anyone have a
solution? I am going to try to reinstall 7.0.0 to verify that it worked
in that version.
thanks,
--Barry
Zlatko Calusic wrote:
>
> Hi!
>
> Not long after I resolved my last problem with indices ('vaccum
> analyze'), I stumbled upon another.
>
> Check this:
>
> filedb=# select file from filenew where file like '/home/zcalusic/rcs%';
>
> returns
>
> file
> ----------------------------------------------
> /home/zcalusic/rcs
> /home/zcalusic/rcs/linux
> /home/zcalusic/rcs/linux/0_REPORTING-,v
> /home/zcalusic/rcs/linux/10_bug-list.t,v
> /home/zcalusic/rcs/linux/11_00-INDEX,v
> ...
>
> 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)
>
> No rows???
>
> How can that be, when output above shows I DO have rows that satisfy
> this query?
> --
> Zlatko
Tom Lane <tgl@sss.pgh.pa.us> writes: > Zlatko Calusic <zlatko@iskon.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) > > > No rows??? > > Which Postgres version are you running, and in what LOCALE setting > on what platform? > 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. -- Zlatko
Zlatko Calusic <zlatko@iskon.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
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