Re: Unique key constraint Issue

Thank you Tom!

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

On Mon, 25 Nov, 2024, 9:32 pm Tom Lane, <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> shashidhar Reddy <shashidharreddy001(at)gmail(dot)com> 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
>