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
duplicaterecords. 
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
definitionand the inserts that are allowed to happen despite being duplicates.  Even better would be a way to reproduce
theissue. 

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