On Tue, 2006-05-16 at 12:32 -0400, Tom Lane wrote: > Rafael Martinez <r.m.guerrero@xxxxxxxxxxx> writes: > > On Tue, 2006-05-16 at 11:14 -0400, Tom Lane wrote: > >> I suspect this may be a question of a corrupt index, in which case > >> REINDEXing the index being used would fix it. > > > This is what I thought when I found out the problem. So before I sent my > > first e-mail I executed a "reindex index tickets6" but it did not help. > > So much for that theory. If you copy the table (create table foo as > select * from tickets) and build a similar index on the copy, does the > behavior persist in the copy? > The new table behaves well: ---------------------------------------------- rtprod=# CREATE TABLE foo as select * from tickets; SELECT rtprod=# CREATE INDEX foo6 on foo (status); CREATE INDEX rtprod=# SELECT id,effectiveid,status,type,queue FROM foo main WHERE ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted'))AND ((main.Type = 'ticket')) AND ((main.Queue = '29')AND ( (main.Status= 'open') OR (main.Status = 'new') ) ) ORDER BY main.id ASC LIMIT 50; id | effectiveid | status | type | queue -------+-------------+--------+--------+------- 37775 | 37775 | new | ticket | 29 37968 | 37968 | open | ticket | 29 38052 | 38052 | open | ticket | 29 38176 | 38176 | open | ticket | 29 38185 | 38185 | open | ticket | 29 38386 | 38386 | open | ticket | 29 38394 | 38394 | open | ticket | 29 38403 | 38403 | open | ticket | 29 38406 | 38406 | open | ticket | 29 38422 | 38422 | open | ticket | 29 38474 | 38474 | open | ticket | 29 38530 | 38530 | open | ticket | 29 38539 | 38539 | open | ticket | 29 (13 rows) rtprod=# SELECT id,effectiveid,status,type,queue FROM foo main WHERE ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted'))AND ((main.Type = 'ticket')) AND ((main.Queue = '29')AND ( (main.Status= 'new') OR (main.Status = 'open') ) ) ORDER BY main.id ASC LIMIT 50; id | effectiveid | status | type | queue -------+-------------+--------+--------+------- 37775 | 37775 | new | ticket | 29 37968 | 37968 | open | ticket | 29 38052 | 38052 | open | ticket | 29 38176 | 38176 | open | ticket | 29 38185 | 38185 | open | ticket | 29 38386 | 38386 | open | ticket | 29 38394 | 38394 | open | ticket | 29 38403 | 38403 | open | ticket | 29 38406 | 38406 | open | ticket | 29 38422 | 38422 | open | ticket | 29 38474 | 38474 | open | ticket | 29 38530 | 38530 | open | ticket | 29 38539 | 38539 | open | ticket | 29 (13 rows) ---------------------------------------------- > I'm wondering a little bit about encoding issues. What encoding does > the database have (see \l) SQL_ASCII > and what do SHOW LC_COLLATE and SHOW LC_CTYPE > show? > ---------------------------------------------- rtprod=# SHOW LC_COLLATE; lc_collate ------------ C (1 row) rtprod=# SHOW LC_CTYPE; lc_ctype ---------- C (1 row) ---------------------------------------------- -- Rafael Martinez, <r.m.guerrero@xxxxxxxxxxx> Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/
