Re: Weird ..... (a=1 or a=2) <> (a=2 or a=1)

On Tue, 2006-05-16 at 11:14 -0400, Tom Lane wrote:
> "Rafael Martinez, Guerrero" <r(dot)m(dot)guerrero(at)usit(dot)uio(dot)no> 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(dot)m(dot)guerrero(at)usit(dot)uio(dot)no>
Center for Information Technology Services
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/