From: | Rafael Martinez <r(dot)m(dot)guerrero(at)usit(dot)uio(dot)no> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Weird ..... (a=1 or a=2) <> (a=2 or a=1) |
Date: | 2006-05-16 16:53:41 |
Message-ID: | 1147798422.4700.19.camel@linux.site |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
On Tue, 2006-05-16 at 12:32 -0400, Tom Lane wrote:
> Rafael Martinez <r(dot)m(dot)guerrero(at)usit(dot)uio(dot)no> 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(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/
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Holm | 2006-05-16 16:53:54 | PGSQL Database Recovery in Portland Oregon Area needed ASAP |
Previous Message | Tom Lane | 2006-05-16 16:32:48 | Re: Weird ..... (a=1 or a=2) <> (a=2 or a=1) |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-05-16 17:01:51 | Re: Weird ..... (a=1 or a=2) <> (a=2 or a=1) |
Previous Message | Josh Berkus | 2006-05-16 16:50:16 | Re: audit table containing Select statements submitted |