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.m.guerrero@usit.uio.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.m.guerrero@usit.uio.no>
Center for Information Technology Services
University of Oslo, Norway

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