This problem was reported by Paolo Lopez in pgsql-es-ayuda. Those who can read spanish can probably get a better picture by seeing the archives there. The initial post in the thread is this one: http://archives.postgresql.org/pgsql-es-ayuda/2006-04/msg00095.php
This one, by Oswaldo Hernandez, has a detailed test case and more exploration of problem conditions: http://archives.postgresql.org/pgsql-es-ayuda/2006-04/msg00204.php Apparently the point is that it fails when there is an index scan using the primary key. So maybe the problem is that the index is corrupt. I observe that Paolo was using 8.1.0 and Oswaldo 8.1.1. I can't reproduce the problem here, but my system is Linux. Oswaldo writes (translated): > Windows XP SP1 > postgres=# select version(); > version > ------------------------------------------------------------------------------------------ > PostgreSQL 8.1.1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 > (mingw-special) > (1 fila) > > Even with this expression I can reproduce the problem on my system: > > CREATE TABLE tablita ( > d int4 , > e int4 , > f int4 , > dia int4 , > primary key (d, e, f, dia) > ); > > > INSERT INTO tablita (d, e, f, dia) VALUES (1, 1, 1, 1); > INSERT INTO tablita (d, e, f, dia) VALUES (1, 1, 1, 3); > INSERT INTO tablita (d, e, f, dia) VALUES (1, 1, 1, 5); > INSERT INTO tablita (d, e, f, dia) VALUES (1, 1, 2, 2); > INSERT INTO tablita (d, e, f, dia) VALUES (1, 1, 2, 4); > INSERT INTO tablita (d, e, f, dia) VALUES (1, 1, 2, 5); > > select dia from tablita where d = 1 and e = 1 order by dia; > > The result, both on pgadmin and psql is: > > dia > ----- > 1 > 3 > 5 > 2 > 4 > 5 > (6 filas) > > > * I think I've found something: > > 1. Change the values of column 'e': > > INSERT INTO tablita (d, e, f, dia) VALUES (1, 2, 1, 1); > INSERT INTO tablita (d, e, f, dia) VALUES (1, 2, 1, 3); > INSERT INTO tablita (d, e, f, dia) VALUES (1, 2, 1, 5); > INSERT INTO tablita (d, e, f, dia) VALUES (1, 2, 2, 2); > INSERT INTO tablita (d, e, f, dia) VALUES (1, 2, 2, 4); > INSERT INTO tablita (d, e, f, dia) VALUES (1, 2, 2, 5); > > select dia from tablita where d = 1 and e = 2 order by dia; > > The result is correct: > > dia > ----- > 1 > 2 > 3 > 4 > 5 > 5 > (6 filas) > > > 2. Change the values of columns 'd' and 'e' and put the same value to > both, but different from '1': > > INSERT INTO tablita (d, e, f, dia) VALUES (21512, 21512, 1, 1); > INSERT INTO tablita (d, e, f, dia) VALUES (21512, 21512, 1, 3); > INSERT INTO tablita (d, e, f, dia) VALUES (21512, 21512, 1, 5); > INSERT INTO tablita (d, e, f, dia) VALUES (21512, 21512, 2, 2); > INSERT INTO tablita (d, e, f, dia) VALUES (21512, 21512, 2, 4); > INSERT INTO tablita (d, e, f, dia) VALUES (21512, 21512, 2, 5); > > select dia from tablita where d = 21512 and e = 21512 order by dia; > > Result is wrong again: > dia > ----- > 1 > 3 > 5 > 2 > 4 > 5 > (6 filas) > > 3. Put the same value in 'd' and 'e', but change the where condition: > > INSERT INTO tablita (d, e, f, dia) VALUES (1, 1, 1, 1); > INSERT INTO tablita (d, e, f, dia) VALUES (1, 1, 1, 3); > INSERT INTO tablita (d, e, f, dia) VALUES (1, 1, 1, 5); > INSERT INTO tablita (d, e, f, dia) VALUES (1, 1, 2, 2); > INSERT INTO tablita (d, e, f, dia) VALUES (1, 1, 2, 4); > INSERT INTO tablita (d, e, f, dia) VALUES (1, 1, 2, 5); > > postgres=# select dia from tablita where d > 0 and e > 0 order by dia; > dia > ----- > 1 > 2 > 3 > 4 > 5 > 5 > (6 filas) > > The result is correct: > > 4. > > More tests changing WHERE conditions: > > postgres=# select dia from tablita where e = d and e = 1 order by dia; > dia > ----- > 1 > 3 > 5 > 2 > 4 > 5 > (6 filas) > Wrong > > postgres=# select dia from tablita where d between 1 and 1 and e between 1 > and 1 order by dia; > dia > ----- > 1 > 2 > 3 > 4 > 5 > 5 > (6 filas) > Correct > > postgres=# select dia from tablita where e = d and e > 0 order by dia; > dia > ----- > 1 > 2 > 3 > 4 > 5 > 5 > (6 filas) > Correct > > > Summary: > It looks like the failure only presents itself when: > en WHERE both conditions are present > 'd' and 'e' have the same value > the WHERE clause uses operator = for both conditions -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
