Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tG7rS-0014Jc-OT for pgsql-general@arkaria.postgresql.org; Wed, 27 Nov 2024 02:33:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tG7rR-00B8om-F8 for pgsql-general@arkaria.postgresql.org; Wed, 27 Nov 2024 02:33:01 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tG7rR-00B8od-3o for pgsql-general@lists.postgresql.org; Wed, 27 Nov 2024 02:33:01 +0000 Received: from mail-ej1-x62b.google.com ([2a00:1450:4864:20::62b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tG7rO-0044oW-HV for pgsql-general@postgresql.org; Wed, 27 Nov 2024 02:32:59 +0000 Received: by mail-ej1-x62b.google.com with SMTP id a640c23a62f3a-a9f1d76dab1so1017898566b.0 for ; Tue, 26 Nov 2024 18:32:58 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1732674777; x=1733279577; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=YrzOZG/MnisYQMMIBgZya3wMotiuUzavDJ8NTFfmXYc=; b=Zc4RKVkIoJgp83stT50adUH4+5pZ3VqCv2I4aSA4A9KzXsLlfB29drh6h7/i+LdooG IabkU/62jHlpLkWRfp7aYdSD5RMpBYccFVdtdKIaoGIKZocA16jNmTET3EiWAt035zqV X+XQ8DIln6U60kfEtxUo7qLE3tT5AvD3BRo2/BkX0w+VzRo/dpcLCZlinP5gOGtcHtuQ qFQyBHunMVtLAmtKwgKI6m9/PYaf3fErUvA9JG5AlUL9YSJ6hj97sVH3tU8S2HAlm38Z sE1sp0DrOoSVwADatmRq8uFEvQePhbVwNDy3voLGTM4Q7RzXVOI030894r9MmDuVYc3k kAnA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1732674777; x=1733279577; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=YrzOZG/MnisYQMMIBgZya3wMotiuUzavDJ8NTFfmXYc=; b=Zii/91gaxOZs6zjePYON4Fl+yKPzkUOYC9OLcYTCMiMfY8iXrSUS58r/KZHI7LW9RO CMcwUUcVTRld7qHPqF/Xl6uKCRYj0m6tMkDpBU4lxf671+5k2PD+o8quvWF6b9Zv37GV Qqr370sY2iXyDVi+VAKnXBRhcj4t1yWfHaU5ThMtBjPvZJtIJohvqsz2r1L6dpa3ln83 n8a35/tA3H7mD3G6lcP7r6AqDvWHA817/bBE2FLTm2yWBki+JZhnqYLcfoMC78YlcZ++ T74Ga8gZ+jzl359Bsrl/IXMFEVXTxFLBZ9SpzXV5x5AL+ICx9oT0xpTFX9WxqlZB2+x4 Hr0w== X-Forwarded-Encrypted: i=1; AJvYcCVHnDXpoMGJoVAoVsn6TY4hKagNFh/WcInLtTPvhiNOubHX2q15WEbcmYQTqwRHoF37nEH37cdnmcl+kZfH@postgresql.org X-Gm-Message-State: AOJu0YyT6i8EQC63yxUl3HQ/TBOMVWRV7lcuNqWK9BIbV6xvEwVTCjca BrmAyaMv2QafQaWE+gMPaCoyTO/FaHTP3kvQSIHqWA4O6tiqyzcNsQu8qHQNtNflyiRHLUEVXkL k1AbYC01AHWQgAaKfjtqm1tHjInZ3YEIO X-Gm-Gg: ASbGncuuf20fru+bdgH/D2vEeTqLCI4IE0A1hfgXzXO2pliVUsh2lXEZDitDgd1x72x xU3/2FrQjOJR5rnEH2SOJo1JZd0WpQE1b X-Google-Smtp-Source: AGHT+IEWFVUPBeZthKCTMBX/aeOh/GOtZWYbR3CBgHsIB8V4KNUHTIl5kSyKCparM/28bkjcCDLcfltEeUFq6ty0bsY= X-Received: by 2002:a17:906:3090:b0:aa5:1ce8:e4e0 with SMTP id a640c23a62f3a-aa580edfc21mr115115066b.10.1732674776907; Tue, 26 Nov 2024 18:32:56 -0800 (PST) MIME-Version: 1.0 References: <42ed36a1-2581-43f7-a606-2c139929b38f@aklaver.com> <24e0eb89-9cb3-4fc5-baa9-af1bb8036796@aklaver.com> In-Reply-To: <24e0eb89-9cb3-4fc5-baa9-af1bb8036796@aklaver.com> From: shashidhar Reddy Date: Wed, 27 Nov 2024 08:02:45 +0530 Message-ID: Subject: Re: Unique key constraint Issue To: Adrian Klaver Cc: ajit wangkhem , pgsql-general Content-Type: multipart/alternative; boundary="00000000000096c3530627dbc881" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000096c3530627dbc881 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thank you Adrian. We replicated the issue with OS upgrade. On Tue, 26 Nov, 2024, 11:06=E2=80=AFam Adrian Klaver, wrote: > On 11/25/24 19:07, shashidhar Reddy wrote: > > Hi Adrian, > > > > Thank you for your response. > > > > I don't think index corrupted as I can generate the ddl script from the > > What DDL script? > > > index and the index is still in use when we query. > > See Tom Lanes post for more detail. Short version the index will still > 'work' except for the part where it lets in duplicate entries. > > > To rebuild the index we are still removing duplicate entries. But > > nothing found in logs. > > > > On Mon, 25 Nov, 2024, 9:30=E2=80=AFpm Adrian Klaver, > > wrote: > > > > On 11/25/24 00:44, shashidhar Reddy wrote: > > > Hi Ajit, > > > > > > Thank you for your reply! > > > > > > The result of the query is same across all servers and yes > streaming > > > replication does not have any issue but the question is how did > > > duplicate values entered when there unique key in place. > > > > Corrupted index? > > > > Have you tried doing a REINDEX? > > > > > > -- > > Adrian Klaver > > adrian.klaver@aklaver.com > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > > --00000000000096c3530627dbc881 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thank you Adrian.

We replicated the issue with OS upgrade.

On Tue, 26 Nov, 2024, 1= 1:06=E2=80=AFam Adrian Klaver, <adrian.klaver@aklaver.com> wrote:
On 11/25/24 19:07, shashidhar Reddy wrote:
> Hi Adrian,
>
> Thank you for your response.
>
> I don't think index corrupted as I can generate the ddl script fro= m the

What DDL script?

> index and the index is still in use when we query.

See Tom Lanes post for more detail. Short version the index will still
'work' except for the part where it lets in duplicate entries.

>=C2=A0 =C2=A0To rebuild the index we are still removing duplicate entri= es. But
> nothing found in logs.
>
> On Mon, 25 Nov, 2024, 9:30=E2=80=AFpm Adrian Klaver, <adrian= .klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>=C2=A0 =C2=A0 =C2=A0On 11/25/24 00:44, shashidhar Reddy wrote:
>=C2=A0 =C2=A0 =C2=A0 > Hi Ajit,
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > Thank you=C2=A0for your reply!
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > The result of the query is same across all se= rvers and yes streaming
>=C2=A0 =C2=A0 =C2=A0 > replication does not have any issue but the q= uestion is how did
>=C2=A0 =C2=A0 =C2=A0 > duplicate values entered when there unique ke= y in place.
>
>=C2=A0 =C2=A0 =C2=A0Corrupted index?
>
>=C2=A0 =C2=A0 =C2=A0Have you tried doing a REINDEX?
>
>
>=C2=A0 =C2=A0 =C2=A0--
>=C2=A0 =C2=A0 =C2=A0Adrian Klaver
>=C2=A0 =C2=A0 =C2=A0adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>

--
Adrian Klaver
adrian.klaver@aklaver.com

--00000000000096c3530627dbc881--