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?