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