public inbox for [email protected]  

help / color / mirror / Atom feed

From: shashidhar Reddy <[email protected]>
To: Tom Lane <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Unique key constraint Issue
Date: Wed, 27 Nov 2024 08:01:39 +0530
Message-ID: <CAH=zU4t6EomytTUn8fxSX+abjSBn+_4Cdrn2Mk9P1ht258MfFA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAH=zU4t3KRpt6uTZn_4eKkvpL_X9ACGNZ+XDxfMjnn6ovAyPVQ@mail.gmail.com>
	<[email protected]>

Thank you Tom!

The issue is with OS upgrade we could able replicate it.

On Mon, 25 Nov, 2024, 9:32 pm Tom Lane, <[email protected]> wrote:

> shashidhar Reddy <[email protected]> writes:
> > The issue is a unique key constraint with two columns one is character
> > another is integer. At some point the unique key did not work as I see
> > duplicate values with these two columns combination and it happened on
> > multiple servers on multiple databases on same table with same unique
> key.
>
> If the table has existed for some time (like, across updates of the
> underlying operating system) then your problem likely traces to
> changes in the OS' sorting rules for character strings:
>
> https://wiki.postgresql.org/wiki/Locale_data_changes
>
> Such a change causes the unique key's index to be out of sort order
> and thus effectively corrupt from PG's viewpoint: searches may or
> may not find an entry that is there.  Once that happens it's pretty
> easy for duplicate entries to get added.
>
> The fix is to REINDEX affected indexes.  But if you already have
> duplicate entries in the table, you'll need to correct them before
> REINDEX will succeed.
>
>                         regards, tom lane
>

view thread (2+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Unique key constraint Issue
  In-Reply-To: <CAH=zU4t6EomytTUn8fxSX+abjSBn+_4Cdrn2Mk9P1ht258MfFA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox