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

Hello,

Please find below snippet from pgAdmin

[cid:image001(dot)png(at)01D9AA93(dot)93616B90]

I was able to enter duplicates from pgAdmin.

Regards,

Ajinkya

-----Original Message-----
From: Andres Freund <andres(at)anarazel(dot)de>
Sent: Wednesday, June 28, 2023 11:00 PM
To: Ajinkya Tankhiwale <ajinkya(dot)tankhiwale(at)tcs(dot)com>
Cc: 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-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

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