Re: Inexplicable duplicate rows with unique constraint

Richard van der Hoff <richard(at)matrix(dot)org> writes:
> I'm trying to track down the cause of some duplicate rows in a table
> which I would expect to be impossible due to a unique constraint. I'm
> hoping that somebody here will be able to suggest something I might have
> missed.

Since these are text columns, one possibility you should be looking into
is that the indexes have become corrupt due to a change in the operating
system's sorting rules for the underlying locale. I don't recall details
at the moment, but I do remember that a recent glibc update changed the
sorting rules for some popular locale settings. If an installation had
applied such an update underneath an existing database, you'd have a
situation where existing entries in an index are not in-order according
to the new behavior of the text comparison operators, leading to havoc
because btree searching relies on the entries being correctly sorted.

Unless you happen to notice searches failing to find rows you know are
there, the first visible symptom is often appearance of "impossible"
duplicate rows, after the search to verify uniqueness of a new entry
fails to find the old entry.

> * At least one user reports that he has recently migrated his database
> from one server to another via a `pg_dump -C` and later piping into psql.

Dump-and-restore wouldn't cause this (and, indeed, is one way to clean up
the mess). But this is suspicious anyway because it suggests there may
have been some general system upgrades going on in the vicinity.

Reindexing all text indexes is the recommended remediation procedure
if you suspect a locale behavior change. There's some work afoot to
make PG notice the need for this automatically, but it's not done yet.

regards, tom lane