Обсуждение: BUG #18324: Duplicate value when I insert values in PK column
The following bug has been logged on the website:
Bug reference: 18324
Logged by: Mael Villat
Email address: mael.villat@gmail.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
On Friday, February 2, 2024, PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 18324
Logged by: Mael Villat
Email address: mael.villat@gmail.com
PostgreSQL version: 16.1
Operating system: Windows Server
Description:
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.
Given that nearly everyone uses generated keys the lack of reports regarding this issue reduces the believability that the bug is in the server versus something related to your environment. It also means that a test case that demonstrates the problem, required so it can be addressed, more likely than not will need to come from you.
David J.
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