Re: Urgent: Key constraints behaving weirdly

"Russell Garrett" <rg(at)tcslon(dot)com> writes:
> last=> reindex index profile_pkey;
> ERROR: could not create unique index
> DETAIL: Table contains duplicated values.

Okay, it looks like the table actually contains duplicate rows but the
index has managed to lose track of that fact. You can see the
duplicates in the GROUP BY query (which isn't using the index) but
not when you do "select * from profile where id = 1017057", because that
query will use the index.

If you did "set enable_indexscan = off" then the "select * from profile
where id = 1017057" query would probably show you two rows. I'd be
interested to see the results of

select ctid, oid, xmin, xmax, cmin, cmax from profile where id = 1017057;

with enable_indexscan off.

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

No, it's building a new index from scratch, and so it notices the
duplicates.

What you've got here is definitely a case of index corruption that has
led to logical corruption of the table (ie, duplicate rows). To get
back to a valid state you will need to delete whichever of the
duplicates seems to be out-of-date, and then do a REINDEX. But I think
it is important first to try to determine what caused the corruption
(software error or hardware?). Again, if you can take a physical dump
of the data directory, that would provide an opportunity to study the
problem later after you get the production machine back on its feet.

regards, tom lane