The following bug has been logged on the website:
Bug reference: 15967
Logged by: Christoph Ziegenberg
Email address: ziegenberg@web.de
PostgreSQL version: 11.5
Operating system: Windows / Linux
Description:
Problem:
Parallel API requests to an application which result in inserts into the
database cause an duplicate key error. The column is the primary key, which
value is generated by a sequence.The number of requests required to cause
this varies in dependence of the database server/load, in my tests it took
between 5 and 20 parallel requests.
Details:
For the inserts the next sequence value is selected using NEXTVAL(), done in
a separate query (not directly in the INSERT).
I did many tests:
- with/without transactions (which shouldn't affect the sequence),
- with/without a DEFAULT of NEXTVAL() for the primary key column
- several Postgres versions...
and could reduce the error to 64bit Postgres 10 / 11 installations on
Windows and Ubuntu (didn't test older versions).
Test results with different Postgres versions:
- Postgres 10.10, 64bit (Ubuntu): failed
- Postgres 10.10, 64bit (Windows): failed
- Postgres 11.5, 64bit (Windows): failed
- Postgres 10.10 32bit (Windows): works