Thread

1. index returns different output

   Viktor Przebinda <viktor@solon.colorado.edu> — 2000-11-04T21:25:10Z

   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
   
   
   

2. Re: index returns different output

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-11-05T00:45:11Z

   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
   
   

3. Re: index returns different output

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-11-05T01:38:37Z

   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
   
   

4. Re: index returns different output

   Lamar Owen <lamar.owen@wgcr.org> — 2000-11-05T01:51:22Z

   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