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

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