SELECT returning too many rows (?)

Hi list,

pgsql version 7.4.2
linux version 2.4.20
distro redhat 9

We appear to have some kind of problem on one of our internal production
systems and I can't think were first to look.

What follows is some selects. Note the OID. The column 'id' is primary
key. The production system makes selects based on status. As you can see
the same row is apparently returned when selecting on any column except
the primary key, however the EXPLAIN seems correct.

Could this be an INDEX corruption or a fixed bug in postgres ?

processing=# select oid, id, aid, status from q_certs where id = '224';
oid | id | aid | status
----------+-----+-------+--------
15282219 | 224 | 23604 | 1
(1 row)

processing=# select oid, id, aid, status from q_certs where oid =
15282219 ;
oid | id | aid | status
----------+-----+-------+--------
15282219 | 224 | 23604 | 1
15282219 | 224 | 23604 | 1
15282219 | 224 | 23604 | 1

processing=# explain select oid, id, aid, date, status from q_certs
where oid = 15282219 ;
QUERY PLAN
---------------------------------------------------------
Seq Scan on q_certs (cost=0.00..57.91 rows=1 width=26)
Filter: (oid = 15282219::oid)
(2 rows)

processing=# select oid, id, aid, date, status from q_certs where oid =
15282219 ;
oid | id | aid | date | status
----------+-----+-------+-------------------------------+--------
15282219 | 224 | 23604 | 2005-02-07 17:33:08.404069+00 | 1
15282219 | 224 | 23604 | 2005-02-07 17:33:08.404069+00 | 1
15282219 | 224 | 23604 | 2005-02-07 17:33:08.404069+00 | 1
(3 rows)

processing=# select count(*)from q_certs where oid = 15282219 ;
count
-------
3
(1 row)

processing=# Segmentation fault
$

Yikes!!!!! The above segfault has subsequently been unreproducible and
has never happened before.

processing=# \d q_certs;
Table "public.q_certs"
Column | Type |
Modifiers
-----------+--------------------------
+----------------------------------------------------
id | integer | not null default nextval
('"q_certs_id_seq"'::text)
aid | integer | not null
intcode | text | not null
seqid | integer | not null
objid | integer | not null
type | text | not null
status | text | not null
lastevent | text |
date | timestamp with time zone | not null
data | text | not null
Indexes:
"q_certs_pkey" primary key, btree (id)
Triggers:
tr_trans BEFORE INSERT OR UPDATE ON q_certs FOR EACH ROW EXECUTE
PROCEDURE tr_trans()
Inherits: _q

--
Rob Fielding
rob(at)dsvr(dot)net

www.dsvr.co.uk Development
Designer Servers Business Serve Plc

"I don't pretend to understand Brannigans Law. I merely enforce it"
- Zapp Brannigan