Re: BUG #18324: Duplicate value when I insert values in PK column

On Fri, 2024-02-02 at 13:05 +0000, PG Bug reporting form wrote:
> I have a table called mdt_prestation with the following column:
> colId integer NOT NULL GENERATED BY DEFAULT AS IDENTITY
>
> I have a timesheet app with only one query that INSERT values in this table
> (extract from the psql log):
> INSERT INTO myTable (Client, Employee)VALUES('1150','185') RETURNING colId
>
> I have about 35 users that uses this app and sometimes we have the following
> error:
> ERREUR: la valeur d'une clé dupliquée rompt la contrainte unique «
> myTable_pkey» (error duplicate value key, unique constraint violation, )
>
> DETAIL: La clé « (colid)=(1704236) » existe déjà  (key 1704236 already
> exist)
>
> Most of the time we don't have this issue. And the sequence is correct
> (sequence = max id value). I'm sure that nobody insert manuel id.
> So I believe that there is a concurrency issue in postgresql server with the
> sequence when we insert a value.

That is very unlikely.

I recommend that you use GENERATED ALWAYS AS IDENTITY, so that the problem
cannor happen any more.

Yours,
Laurenz Albe