Re: Urgent: Key constraints behaving weirdly

>> 2. The index is corrupt, possibly due to a hardware glitch.  (This
>> seems unlikely because the SELECT result appears normal, but I can't
>> rule it out entirely.)
>
> We had a table error a few weeks back, however we re-imported the
> table from scratch. So it may well be this. I doubt it's a actually a
> Postgres bug now, since we aren't doing anything particularly
> unusual, and it's been working fine for several weeks. Still, I'm not
> ruling it out.

OK, I'm not sure whether this is index corruption or a postgres bug or both:

last=> select id, count(id) AS a FROM profile GROUP BY id ORDER BY a DESC;
   id    | a
---------+---
 1017057 | 2
 1018316 | 2
       0 | 1
      80 | 1
...

last=> SELECT id from profile WHERE id = 1017057;
   id
---------
 1017057
(1 row)

last=> SELECT id from profile WHERE id = 1018316;
   id
---------
 1018316
(1 row)

last=> reindex index profile_pkey;
ERROR:  could not create unique index
DETAIL:  Table contains duplicated values.


Does REINDEX use the current index to check for duplicates? :)

Many thanks for all the help,

Russ