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

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: mael(dot)villat(at)gmail(dot)com
Subject: BUG #18324: Duplicate value when I insert values in PK column
Date: 2024-02-02 13:05:56
Message-ID: 18324-c399192389d72f74@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 18324
Logged by: Mael Villat
Email address: mael(dot)villat(at)gmail(dot)com
PostgreSQL version: 16.1
Operating system: Windows Server
Description:

Dear Dev team,

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.

Best regards
Mael

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message David G. Johnston 2024-02-02 13:45:00 Re: BUG #18324: Duplicate value when I insert values in PK column
Previous Message Christian Maurer 2024-02-02 10:21:46 Aw: BUG #18312: libpq: PQsetdbLogin() not thread-safe