pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18324: Duplicate value when I insert values in PK column
3+ messages / 3 participants
[nested] [flat]
* BUG #18324: Duplicate value when I insert values in PK column
@ 2024-02-02 13:05 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 3+ messages in thread
From: PG Bug reporting form @ 2024-02-02 13:05 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: mael.villat@gmail.com
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18324: Duplicate value when I insert values in PK column
@ 2024-02-02 13:45 David G. Johnston <david.g.johnston@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 3+ messages in thread
From: David G. Johnston @ 2024-02-02 13:45 UTC (permalink / raw)
To: mael.villat@gmail.com <mael.villat@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18324: Duplicate value when I insert values in PK column
@ 2024-02-02 15:16 Laurenz Albe <laurenz.albe@cybertec.at>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 3+ messages in thread
From: Laurenz Albe @ 2024-02-02 15:16 UTC (permalink / raw)
To: mael.villat@gmail.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-02-02 15:16 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-02 13:05 BUG #18324: Duplicate value when I insert values in PK column PG Bug reporting form <noreply@postgresql.org>
2024-02-02 13:45 ` David G. Johnston <david.g.johnston@gmail.com>
2024-02-02 15:16 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox