RE: BUG #18002: Duplicate entries of row possible even after having primary key

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(at)anarazel(dot)de>
Sent: Tuesday, June 27, 2023 10:37 PM
To: Ajinkya Tankhiwale <ajinkya(dot)tankhiwale(at)tcs(dot)com>; pgsql-bugs(at)lists(dot)postgresql(dot)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(dot)tankhiwale(at)tcs(dot)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