Обсуждение: index returns different output

I have come across a query that produces different output depending on
weather or not an index is imposed on the relation. I am using
postgresql-7.0.2-2 installed as a binary from an rpm downloaded from the
postgres site. My machine is an Intel dual PII 300 with 256MB of ram.
The system runs Linux 2.2.16-3smp (redhat linux 6.2). postmaster is
running as user postgres.

1) create a database (call it cu_database)
2) open that database by doing psql cu_database
3) create the relation by entering the following sql:
create table cu_fall_00_test_2 (name varchar(40), phone varchar(20),
address varchar(60), major char(4), standing char(10), title char(20),
department varchar(40), email varchar(50), home_page varchar(50));
4) create a temporary file, download the attached names.txt file.
5) open a new window, switch to root (using su) and then switch from
root to the postgres account.
6) execute psql cu_database
7) enter the following sql (replace the path so that it finds your
names.txt file)
COPY cu_fall_00_test_2 from 'names.txt' using DELIMITERS '\t';
8) exit the root psql window, switch back to your origional psql
process.
9) enter the following sql:
select * from cu_fall_00_test_2 where name like 'PRZ%';
10) you notice that all fields are returned (as they should)
11) enter the following sql:
create index name on cu_fall_00_test_2 (name);
select * from cu_fall_00_test_2 where name like 'PRZ%';
12) if you see what I see, the same query will now return 0 rows!


--
Viktor Przebinda


PRZEBINDA, VIKTOR    none    1600 HILL SIDE RD. BOULDER, CO 80303    CSEN    Sophomore    none    none
Viktor.Przebinda@Colorado.EDU   none 
PRZESTRZELSKI, LAURA    (303) 460-7180    1395 W 12 AVENUE BROOMFIELD, CO 80020    none    none    none    none    none
  none 
PRZONEK, JEFFREY KEITH    (303) 445-9552    1860 WALNUT ST NO 6 BOULDER, CO 80302    ENVS    Senior    none    none
Jeffrey.Przonek@Colorado.EDU   none 
PRZYSINDA, CARLY HANA    (716) 271-5117    571 CLAYBOURNE ROAD ROCHESTER, NY 14618    OPNO    Sophomore    none    none
  Carly.Przysinda@Colorado.EDU    none 

What LOCALE setting do you run the postmaster in?

This looks like it might be the known problem with LIKE index
optimization not coping very well with non-ASCII collation orders.
(It tries, but if you have collation rules where multicharacter
patterns are treated specially, it tends to do the wrong thing...)

You can find lots of discussion about this in the pgsql mailing
list archives.  I don't think anyone's worked out a complete solution
short of not doing the optimization at all in non-ASCII locales :-(

            regards, tom lane

Viktor Przebinda <viktor@solon.Colorado.EDU> writes:
> Tom Lane wrote:

>> What LOCALE setting do you run the postmaster in?

> Output of the locale program is as follows:
> LANG=en_US
> LC_CTYPE="en_US"
> LC_NUMERIC="en_US"
> LC_TIME="en_US"
> LC_COLLATE="en_US"
> LC_MONETARY="en_US"
> LC_MESSAGES="en_US"
> LC_ALL=

> I believe the data set that I sent you contains only ASCII characters.

Well, for what it's worth, I do NOT see a failure with your test example
on my machine (HPUX 10.20), using either 7.0.2 or current sources.
So I still think there is a local environment issue.

One thing to check: the fact that your own shell is running in US
locale does not necessarily mean that the postmaster was started the
same way ... and the postmaster's setting is what counts.

We've seen prior reports of RedHat 6.2-specific collation issues.
See for example the thread at
http://www.postgresql.org/mhonarc/pgsql-bugs/2000-09/msg00060.html

I seem to recall something about another internationalization config
file that might cause problems on RH6.2, particularly if you upgraded
from an earlier release instead of doing a cold install of 6.2.  But
I'm not finding it in the archives right now.  Anyone remember
something like that?

            regards, tom lane

Tom Lane wrote:
> I seem to recall something about another internationalization config
> file that might cause problems on RH6.2, particularly if you upgraded
> from an earlier release instead of doing a cold install of 6.2.  But
> I'm not finding it in the archives right now.  Anyone remember
> something like that?

/etc/sysconfig/i18n is the file in question.  A cold 6.2 install has the
file (as does 7.0) -- and upgrade from prior to 6.2 does not.  The
collation with the file is different than without.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11