From: Ajinkya Tankhiwale <ajinkya.tankhiwale@tcs.com>
To: Andres Freund <andres@anarazel.de>, "pgsql-bugs@lists.postgresql.org"
	<pgsql-bugs@lists.postgresql.org>
Subject: RE: BUG #18002: Duplicate entries of row possible even after having
 primary key
Thread-Topic: BUG #18002: Duplicate entries of row possible even after having
 primary key
Thread-Index: AQHZqOkAk7uc3GzoKE+Nrsiv4um9JK+fHFUAgADBfnA=
Date: Wed, 28 Jun 2023 08:38:03 +0000
Message-ID: 
 <MA0PR01MB7660CF4D25A524E5DDBAD3F38E24A@MA0PR01MB7660.INDPRD01.PROD.OUTLOOK.COM>
References: <18002-00f2b6cb2b9c0b02@postgresql.org>
 <20230627203640.fkh66zps3p2jpxvd@awork3.anarazel.de>
In-Reply-To: <20230627203640.fkh66zps3p2jpxvd@awork3.anarazel.de>
Accept-Language: en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?us-ascii?Q?tItiM0VMihtD0bs9aG/qsmWtqfuB3heZfuWbt2bb8zPLLdmawZVt0h6d8MjG?=
 =?us-ascii?Q?s6hmzchU4pqXvfFvAbw1toDWB8nmIo4+22L5ZJpSAP09eeygmrLWjFsenshX?=
 =?us-ascii?Q?yPkLqbvca9Zb1il/MUV0uGjPkZGA2Aizp+v4JHOPAhyepFVgjlor5BsRox2e?=
 =?us-ascii?Q?lGk2JZu22Sk0nukqAxXwG9xB+CkxIrxZ4bk8JJ3SEGalPsRXjuGWm1XkFJVu?=
 =?us-ascii?Q?3tCDErobfwM40I/G65f9eqwEZN1Cw1tQKJoz4suQ+mX/W8VmVb7N7k9yr28C?=
 =?us-ascii?Q?fwHXgAPS67x5Q9AsJaoPe/o84nv9kv/A79cO7Y3moQYWrem8RWAOrs+dFPzG?=
 =?us-ascii?Q?fRV0olkr/uHnPeObNfSjfHemUUMEka8b+1JNIhjp/iVqfOSe2zR9hoql1n74?=
 =?us-ascii?Q?H/E27d+XxnytMADqJhHBf6OlqraBqzGgvPpo2BuSdJ6M373RN3yNVvJg8VCm?=
 =?us-ascii?Q?AV+BJcBlZML0+5LgQkQzgx7w1/BcEd2QZ2K81leKOOWkyiEk+l1tnEGs0R+e?=
 =?us-ascii?Q?lvyZdawHW9+/Cty0DU+ZCR/M2m6SE/xIQ8u5G4EYiFbpnkKTWv072uczU1g8?=
 =?us-ascii?Q?JsolxMVNK90BNDJ8JPpWIcZ0k0c0y+exVCNiColjz0kjOF9UJMUyS7cIDDmA?=
 =?us-ascii?Q?0X5fZWUBMKw1cyEjcML+ITdpxyYNlBrE+Un5kyAJNc+p9H0StmdqGeYf19cw?=
 =?us-ascii?Q?XsdzCgLDEkgvqSYnRkBIcgF5gJQ7rHfawyLeZ+qjO+JxUk6MIiP0iW1kIEK9?=
 =?us-ascii?Q?UvN8iuD01IlCyA4fTe1kksoo4Sxb/3+BC9KhOAN60Mt94YqTF0+3fqQ9iG9h?=
 =?us-ascii?Q?zXvD8SoeVbcbn4xFODukYGxLCyOsRoxM0+PtuKhiZKs9sYUHihKYpLnVeRnj?=
 =?us-ascii?Q?Sp+xOrUISGPEPSb+1l1if4/APeXI3OPUUc8WQgtJ377EY16wUBTAYMB4Z3HH?=
 =?us-ascii?Q?AMmuRdn0j31thQL4LOjrjKSXmNVmCu+7SB8ylXmrK4pAQMILmB9MRIQAv6YN?=
 =?us-ascii?Q?UwbIyq2YMTbfoLdKH4mxLDdDxmd2L8iJw64o+Qn98E8eGIUTMd3cnLDsyJjH?=
 =?us-ascii?Q?98HRpTfhTit53CrpTSQeLqGL0I9fhYQNOY4R03uRlV9nDT98E22LD/fNdlr9?=
 =?us-ascii?Q?/M9N0DmRUt6e/lij9MPsXHOM9h2GusQjdCPy6KilYPkZi9mDZDnqYUJDF0SW?=
 =?us-ascii?Q?NayisLjJapcIuI4H8CIMwDIXBcrZ2PTn+0NSR2+YxGXlz2N8lT9UxGnONJH4?=
 =?us-ascii?Q?V0ItIe4/JJkq7o2yERNorUAgau6/B91yKap7M5YmwWUKfGfJeXJqih4/nr3Y?=
 =?us-ascii?Q?ouXjyWV9YaS/bbNGisWL7639A7Xr/F+dItVBjGB/WVrtPYfoxr2l1UTyx1xr?=
 =?us-ascii?Q?01Kd4c1DX9FX0ceBt9xOu1OcxFHqZAf8pOQYeCYWTmx2tDu9nxj17kLPz9ve?=
 =?us-ascii?Q?w6uHGKjXLFRPzTH5vEJ72sxZTPAyUoh1eACfPsAc47v8Pa0uCWqSrf5KfQdG?=
 =?us-ascii?Q?o5p4/Jee12vMmjwrCPq8quNi8x8lhHG4PYAVj+WM4FmJBmwH9hWtkORClGu+?=
 =?us-ascii?Q?N91upRmpJOdQZUHa0fg=3D?=
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
X-MS-Exchange-AntiSpam-ExternalHop-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-ExternalHop-MessageData-0: 
 Y3RGkgYdwnSqfbovuNcC2ZKQ2sN42Wf7Q9kiy6DWsdfW6G5ybfOH2vZbIsiH23tm4h4qr0LFS5tItBbqCWxOk00Tl20x2V7L5igyZTq9e+QcXRA/gTJMz2Ot96oPUtPujHuZCYew+kxToxOoSvigFvor/3+2EcrC48nMGKUCURxXyvr/PPP3bUezyMPpJp8WDU+lGqsVpY/MMT8al3Xb8yyywEZHw2tzdIqjpJTEWOEuun/l1ez76FgDC5ShcZ+XYZrhJQmLS0IKwxMkfGrP4eLLdRZ0zai3jhg8WfLQJDj4EExWorCO55EiFMXJM8bUyFbfjbKf1G/xhbwuMSNkhvLU14NVIxvqIHWE/QlQe4E/xht1Dtqj0BwJgBBMKICMMZVb51ma2rCut673JYXSyqohN7cyKKb6il0PoZxGPd7Z90BR7TPsq5sEeXN0woTLmxsA0RbllzKdUFKnDQI2apl/Uk3Veo2NjCfhjzXZbXI+S1wn5641ycDCBk1OHA9cYJHCMNo5Xq/Fkd0GH3CW2jOe52COPbF9c+XSBA3wzb7NRa0h0m/hV7LmbRnosmrYAOLJ317UB0NFPeK6VZ9WeAiZliD3Ufm6zVTvBuAjPlU9uJCtWidjgaMNC2a6BNCpSLCm3FfK9zHtETWMjmBwdCKXz+sc4jAPVPy+V1zh4KKjUPUXJcGBiVhqs6fqkpDc240p6qG+vktNfbW/t+APvSUHQvujxfNpTC9uYaogFuoB8iVAByKAzb05+dfyiwcwaaTM9lreYe8x6BTkLVKNAjCyWXAD8tsauTPWWSY0e+K/iOg4pWILGahqyFxsT+X1RXNPj2QU+QEezppqSIyZeg==
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: MA0PR01MB7660.INDPRD01.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 fcb55748-e058-4f82-5300-08db77b2fd1d
X-MS-Exchange-CrossTenant-originalarrivaltime: 28 Jun 2023 08:38:03.7177
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 404b1967-6507-45ab-8a6d-7374a3f478be
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: 
 wmqKXkcnEnja7NoJ2h7bMtn1WkT+koEcagr6w/chZM7bP4CfeKFicAB2Z1G9Gf0A
X-MS-Exchange-Transport-CrossTenantHeadersStamped: MAZPR01MB9228
X-TM-AS-Product-Ver: SMEX-14.0.0.3092-9.0.1002-27686.007
X-TM-AS-Result: No-10--12.491700-8.000000
X-TMASE-MatchedRID: fE0JoqABJp1jDV//SvkH3rqQyAveNtg6mrsOIhm4m/i7N2IdiDgmMzsX
	0C3LJPao05uvOBM7hCel9Qu635f1KNJeyDzAa2AdEXjPIvKd74DqcQyHuPAzJcohIFetPiGNSik
	P5EIJEGRdscK0TZCtwxY7fCCrNU4YUDneiKV4uvnum6Nvy6t3NhmyTBaqiJvc99drVcuxTPVtlr
	59vY7mBz17oKQUjkv772t+I8P6Bx4O0G36v8LMKvrNPkGWAHDfC//1TMV5chNvHNkxfU8EWKm7P
	pyf7RSRnkydiliJhmdWddvLzXoMWHD0Qjd3H2Ri2Sa33ZGXWdYe39feJyE1ga4LsT8h8VCmXWjv
	A8TpWFiKS30ugY2WTKNZGaGDglIlU6K87V98CkSIf3m0sUfx5wb2MDJXlfx+RxNBZ2mITdWjxYy
	RBa/qJcaUO+wtQNba9xS3mVzWUuA4wHSyGpeEej/D9/YgpK+q+m3EHplmsV5eLV0d5HV0ulWK0Y
	LHMzBTgxrquslm0zI=
X-TM-AS-User-Approved-Sender: No
X-TM-AS-User-Blocked-Sender: No
X-TMASE-Result: 10--12.491700-8.000000
X-TMASE-Version: SMEX-14.0.0.3092-9.0.1002-27686.007
X-TM-SNTS-SMTP: 
 1323A1E070FAB53238BB29042A0F61A11E14E41B15DED637F63AEC6F4378BE8A2000:8
X-OriginatorOrg: TCS.COM
Content-Transfer-Encoding: quoted-printable
List-Id: <pgsql-bugs.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-bugs@lists.postgresql.org>
List-Owner: <mailto:pgsql-bugs-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-bugs>
Archived-At: 
 <https://www.postgresql.org/message-id/MA0PR01MB7660CF4D25A524E5DDBAD3F38E24A%40MA0PR01MB7660.INDPRD01.PROD.OUTLOOK.COM>
Precedence: bulk

Hello,

Please find below DDL for one of the table

CREATE SEQUENCE IF NOT EXISTS seq_ao_id
    CYCLE
    INCREMENT 1
    START 1
    MINVALUE 1
    MAXVALUE 999999999999999999
    CACHE 1;

CREATE TABLE IF NOT EXISTS offer
(
    action_id bigint NOT NULL DEFAULT nextval('trade.seq_ao_id'::regclass),
    action_type character varying COLLATE pg_catalog."default" NOT NULL,
    buy_sell_indicator character varying COLLATE pg_catalog."default" NOT N=
ULL,
    creation_date_time timestamp without time zone NOT NULL,
    modification_date_time timestamp without time zone NOT NULL,
    CONSTRAINT pk_ado PRIMARY KEY (action_id)
);
 =

CREATE INDEX IF NOT EXISTS idx_ado_active_action_id
    ON offer USING btree
    (action_id ASC NULLS LAST)
    TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS idx_ao_modify_create_date_time
    ON offer USING btree
    (modification_date_time DESC NULLS FIRST, creation_date_time DESC NULLS=
 FIRST)
    TABLESPACE pg_default;

Interesting point here is, if I create new table then constraint violation =
is thrown, on old table I could enter duplicate records.
Also, we migrated from el7 to el8.

Regards,
Ajinkya

-----Original Message-----
From: Andres Freund <andres@anarazel.de> =

Sent: Tuesday, June 27, 2023 10:37 PM
To: Ajinkya Tankhiwale <ajinkya.tankhiwale@tcs.com>; pgsql-bugs@lists.postg=
resql.org
Subject: Re: BUG #18002: Duplicate entries of row possible even after havin=
g primary key

"External email. Open with Caution"

Hi,

On 2023-06-27 11:03:16 +0000, PG Bug reporting form wrote:
> Bug reference:      18002
> Logged by:          Ajinkya Tankhiwale
> Email address:      ajinkya.tankhiwale@tcs.com
> PostgreSQL version: 14.7
> Operating system:   RHEL 8.8
> Description:        =

> =

> We migrated from 13.7 to 14.7 recently, in current data setup, we say =

> that duplicate records are present in table even after primary key. I =

> see that in the past, same issue was reported and possible solution =

> was to run full vacuum or re-index. We tried both, but still able to =

> enter duplicate records.
> =

> This is blocker for us and would like to have solution for this as =

> soon as possible.

You haven't provided information that'd allow us to debug the issue. At the=
 very least we'd need table + index definition and the inserts that are all=
owed to happen despite being duplicates.  Even better would be a way to rep=
roduce the issue.

You said that you migrated from 13.7 to 14.7 recently - did you also change=
 your OS version?

Greetings,

Andres Freund

=3D=3D=3D=3D=3D-----=3D=3D=3D=3D=3D-----=3D=3D=3D=3D=3D
Notice: The information contained in this e-mail
message and/or attachments to it may contain =

confidential or privileged information. If you are =

not the intended recipient, any dissemination, use, =

review, distribution, printing or copying of the =

information contained in this e-mail message =

and/or attachments to it are strictly prohibited. If =

you have received this communication in error, =

please notify us by reply e-mail or telephone and =

immediately and permanently delete the message =

and any attachments. Thank you