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