Re: SELECT returning too many rows (?)

On Tue, 8 Feb 2005, rob wrote:

> 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

Hmm, could you run the above including the system-columns ctid, xmin,
cmin, xmax, cmax?

It doesn't look like plain index corruption to me since it looks like that
was from a seq scan of the table.

>  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

Just for completeness sake, what is _q and what does the trigger do?