>> 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