From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Richard van der Hoff <richard(at)matrix(dot)org>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Inexplicable duplicate rows with unique constraint |
Date: | 2020-01-16 17:27:25 |
Message-ID: | c7960226-4f03-8623-cd3c-b53af33eaa61@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 1/16/20 9:24 AM, Richard van der Hoff wrote:
>
> 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?
In psql who does:
\l the_database_name
show?
>
> To answer Adrian's question: the lengths of the values in the indexed
> columns are identical between the duplicated rows.
>
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Richard van der Hoff | 2020-01-16 17:28:46 | Re: Inexplicable duplicate rows with unique constraint |
Previous Message | Daniel Verite | 2020-01-16 17:24:48 | Re: Inexplicable duplicate rows with unique constraint |