Brian McCane <bmccane@mccons.net> writes:
> An interesting point was that the duplicated key was actually in the
> table, but the existing unique index was happy as a clam.
Hmm. What datatype is the key, and what PG version are you running?
The only known cause of such problems at the moment is that if you
have LOCALE support compiled in, then the correct sort ordering of
textual datatypes depends on locale. Change the locale, and presto
your index is out of order --- and therefore corrupt. (The btree
algorithms do not cope at all well with out-of-order index data.
Failing to find entries that are there would be a very common result.)
Prior to 7.1 you could easily shoot yourself in the foot this way
by starting the postmaster with different locale environment variables
at different times. As of 7.1, we save the locale seen at initdb time
and adopt that at every postmaster startup, so in theory this class of
problems is gone in 7.1.
If you've got an example that doesn't fit into this case then I'd
like to know about it, especially if you still have the broken index
available for examination ...
regards, tom lane