MIME-Version: 1.0
References: <CAH=zU4t3KRpt6uTZn_4eKkvpL_X9ACGNZ+XDxfMjnn6ovAyPVQ@mail.gmail.com>
 <1348980.1732550558@sss.pgh.pa.us>
In-Reply-To: <1348980.1732550558@sss.pgh.pa.us>
From: shashidhar Reddy <shashidharreddy001@gmail.com>
Date: Wed, 27 Nov 2024 08:01:39 +0530
Message-ID: <CAH=zU4t6EomytTUn8fxSX+abjSBn+_4Cdrn2Mk9P1ht258MfFA@mail.gmail.com>
Subject: Re: Unique key constraint Issue
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000a73ab90627dbc4ca"
Archived-At: <https://www.postgresql.org/message-id/CAH%3DzU4t6EomytTUn8fxSX%2BabjSBn%2B_4Cdrn2Mk9P1ht258MfFA%40mail.gmail.com>
Precedence: bulk

--000000000000a73ab90627dbc4ca
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Thank you Tom!

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

On Mon, 25 Nov, 2024, 9:32=E2=80=AFpm Tom Lane, <tgl@sss.pgh.pa.us> wrote:

> shashidhar Reddy <shashidharreddy001@gmail.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
>

--000000000000a73ab90627dbc4ca
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"auto">Thank you Tom!<div dir=3D"auto"><br></div><div dir=3D"aut=
o">The issue is with OS upgrade we could able replicate it.</div></div><br>=
<div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Mon, 25=
 Nov, 2024, 9:32=E2=80=AFpm Tom Lane, &lt;<a href=3D"mailto:tgl@sss.pgh.pa.=
us">tgl@sss.pgh.pa.us</a>&gt; wrote:<br></div><blockquote class=3D"gmail_qu=
ote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex=
">shashidhar Reddy &lt;<a href=3D"mailto:shashidharreddy001@gmail.com" targ=
et=3D"_blank" rel=3D"noreferrer">shashidharreddy001@gmail.com</a>&gt; write=
s:<br>
&gt; The issue is a unique key constraint with two columns one is character=
<br>
&gt; another is integer. At some point the unique key did not work as I see=
<br>
&gt; duplicate values with these two columns combination and it happened on=
<br>
&gt; multiple servers on multiple databases on same table with same unique =
key.<br>
<br>
If the table has existed for some time (like, across updates of the<br>
underlying operating system) then your problem likely traces to<br>
changes in the OS&#39; sorting rules for character strings:<br>
<br>
<a href=3D"https://wiki.postgresql.org/wiki/Locale_data_changes" rel=3D"nor=
eferrer noreferrer" target=3D"_blank">https://wiki.postgresql.org/wiki/Loca=
le_data_changes</a><br>
<br>
Such a change causes the unique key&#39;s index to be out of sort order<br>
and thus effectively corrupt from PG&#39;s viewpoint: searches may or<br>
may not find an entry that is there.=C2=A0 Once that happens it&#39;s prett=
y<br>
easy for duplicate entries to get added.<br>
<br>
The fix is to REINDEX affected indexes.=C2=A0 But if you already have<br>
duplicate entries in the table, you&#39;ll need to correct them before<br>
REINDEX will succeed.<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
</blockquote></div>

--000000000000a73ab90627dbc4ca--