Richard van der Hoff <richard@xxxxxxxxxx> 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
