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

Hello,

Please find below snippet from pgAdmin

I was able to enter duplicates from pgAdmin.

Regards,

Ajinkya

-----Original Message-----
From: Andres Freund <andres@anarazel.de>
Sent: Wednesday, June 28, 2023 11:00 PM
To: Ajinkya Tankhiwale <ajinkya.tankhiwale@tcs.com>
Cc: 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-28 08:38:03 +0000, Ajinkya Tankhiwale wrote:

> 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) );

Just to check, the non-detected uniqueness violations are in this table?

How exactly are you inserting?

As the primary key is using a sequence, you shouldn't even get to the situation that there could be a uniqueness violation.

> 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.

If the relevant constraints were on some form of a string, I'd suspect that you ran into a corrupted index due to collation changes between el7 and el8. But the pkey of the table above is on a bigint...

Greetings,

Andres Freund