SELECT returning too many rows (?) - Mailing list pgsql-bugs

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=3D#  select oid, id, aid, status from q_certs where id =3D '224';
   oid    | id  |  aid  | status=20
----------+-----+-------+--------
 15282219 | 224 | 23604 | 1
(1 row)


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

processing=3D# explain select oid, id, aid, date, status from q_certs
where oid =3D 15282219 ;
                       QUERY PLAN=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20
---------------------------------------------------------
 Seq Scan on q_certs  (cost=3D0.00..57.91 rows=3D1 width=3D26)
   Filter: (oid =3D 15282219::oid)
(2 rows)

processing=3D#  select oid, id, aid, date, status from q_certs where oid =
=3D
15282219 ;
   oid    | id  |  aid  |             date              | status=20
----------+-----+-------+-------------------------------+--------
 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=3D#  select count(*)from q_certs where oid =3D 15282219 ;
 count=20
-------
     3
(1 row)

processing=3D# Segmentation fault
$


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


processing=3D# \d q_certs;
                                  Table "public.q_certs"
  Column   |           Type           |
Modifiers=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
-----------+--------------------------
+----------------------------------------------------
 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                     |=20
 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


--=20
Rob Fielding
rob@dsvr.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