On Tue, 2006-05-16 at 11:14 -0400, Tom Lane wrote: > "Rafael Martinez, Guerrero" <r.m.guerrero@xxxxxxxxxxx> writes: > > I have found out the sql statement with 'problems'. Can anybody explain > > me why A) returns 12 rows and B) returns 13 rows?. The only different is > > the "open OR new" / "new OR open" part. Should not they return the samme > > result? > > That is ... simply bizarre. Could we see all the system columns for the > rows in question, ie > select ctid,oid,xmin,xmax,cmin,cmax, id,effectiveid, ... > (the rest as in your second query that gets all the rows) > Leave out "oid" if you have the table made without oids. > rtprod=# SELECT ctid,oid,xmin,xmax,cmin,cmax,id,effectiveid,status,type,queue FROM Tickets 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; ctid | oid | xmin | xmax | cmin | cmax | id | effectiveid | status | type | queue -----------+---------+-----------+------+------+------+-------+-------------+--------+--------+------- (880,18) | 5080015 | 431831923 | 149 | 149 | 0 | 37775 | 37775 | new | ticket | 29 (1004,13) | 5103190 | 440233858 | 0 | 0 | 0 | 37968 | 37968 | open | ticket | 29 (1004,10) | 5112604 | 440233792 | 0 | 0 | 0 | 38052 | 38052 | open | ticket | 29 (995,13) | 5130149 | 440233870 | 0 | 0 | 0 | 38176 | 38176 | open | ticket | 29 (1020,2) | 5132134 | 441184224 | 0 | 0 | 0 | 38185 | 38185 | open | ticket | 29 (1004,5) | 5160459 | 440828297 | 38 | 38 | 0 | 38386 | 38386 | open | ticket | 29 (1004,3) | 5161571 | 440233745 | 0 | 0 | 0 | 38394 | 38394 | open | ticket | 29 (1020,5) | 5163792 | 441195836 | 38 | 38 | 0 | 38403 | 38403 | open | ticket | 29 (1019,3) | 5164449 | 441183696 | 38 | 38 | 0 | 38406 | 38406 | open | ticket | 29 (1015,14) | 5167225 | 441188439 | 38 | 38 | 0 | 38422 | 38422 | open | ticket | 29 (1021,3) | 5172082 | 441185101 | 38 | 38 | 0 | 38474 | 38474 | open | ticket | 29 (968,37) | 5176170 | 440990670 | 0 | 0 | 0 | 38530 | 38530 | open | ticket | 29 (1015,11) | 5177554 | 441183605 | 0 | 0 | 0 | 38539 | 38539 | open | ticket | 29 (13 rows) > 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. > But before you try that, > please save a physical copy of the index file (immediately after doing a > CHECKPOINT, if the database is being actively modified). I would like > to dig through it and try to understand the nature of the corruption, > if that's what the problem is. > -------------------------------------------------- rtprod=# SELECT relname,relfilenode from pg_class where relname = 'tickets6'; relname | relfilenode ----------+------------- tickets6 | 5177103 rtprod=# checkpoint; CHECKPOINT -bash-2.05b$ ls -l 5177103 -rw------- 1 postgres pgdba 1056768 May 16 18:10 5177103 -------------------------------------------------- The index file after a CHECKPOINT can be downloaded from http://folk.uio.no/rafael/5177103 A new reindex does not help: -------------------------------------------------- rtprod=# reindex index tickets6; REINDEX SELECT id,effectiveid,status,type,queue FROM Tickets 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 -------+-------------+--------+--------+------- 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 (12 rows) rtprod=# SELECT id,effectiveid,status,type,queue FROM Tickets 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) -------------------------------------------------- -- Rafael Martinez, <r.m.guerrero@xxxxxxxxxxx> Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/
