Re: Inexplicable duplicate rows with unique constraint

On 16/01/2020 17:12, Magnus Hagander wrote:
> On Thu, Jan 16, 2020 at 6:08 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>
>> 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.
>
> See https://wiki.postgresql.org/wiki/Locale_data_changes for hints on
> which linux distros updated when.
>
Right, thanks to all who have suggested this.

It seems like a plausible explanation but it's worth noting that all the
indexed data here is (despite being in text columns), plain ascii. I'm
surprised that a change in collation rules would change the sorting of
such strings, and hence that it could lead to this problem. Am I naive?

To answer Adrian's question: the lengths of the values in the indexed
columns are identical between the duplicated rows.