From: Ajinkya Tankhiwale <ajinkya.tankhiwale@tcs.com>
To: Andres Freund <andres@anarazel.de>
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: RE: BUG #18002: Duplicate entries of row possible even after having primary key
Date: Wed, 28 Jun 2023 08:38:03 +0000
Message-ID: <MA0PR01MB7660CF4D25A524E5DDBAD3F38E24A@MA0PR01MB7660.INDPRD01.PROD.OUTLOOK.COM> (raw)
In-Reply-To: <20230627203640.fkh66zps3p2jpxvd@awork3.anarazel.de>
References: <18002-00f2b6cb2b9c0b02@postgresql.org>
<20230627203640.fkh66zps3p2jpxvd@awork3.anarazel.de>
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 NULL,
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.postgresql.org
Subject: Re: BUG #18002: Duplicate entries of row possible even after having 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 allowed to happen despite being duplicates. Even better would be a way to reproduce the issue.
You said that you migrated from 13.7 to 14.7 recently - did you also change your OS version?
Greetings,
Andres Freund
=====-----=====-----=====
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
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: pgsql-bugs@postgresql.org
Cc: ajinkya.tankhiwale@tcs.com, andres@anarazel.de, pgsql-bugs@lists.postgresql.org
Subject: RE: BUG #18002: Duplicate entries of row possible even after having primary key
In-Reply-To: <MA0PR01MB7660CF4D25A524E5DDBAD3F38E24A@MA0PR01MB7660.INDPRD01.PROD.OUTLOOK.COM>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox