From: Andrus <kobruleht2@hot.ee>
To: pgsql-general@postgresql.org
Subject: Duplicate key error
Date: Wed, 3 Mar 2021 17:23:35 +0200
Message-ID: <a8656d4f-d692-ffae-077e-304f348e25bf@hot.ee> (raw)
Hi!
Sometimes duplicate key error
duplicate key value violates unique constraint "session_pkey"
Key (workplace)=(WIN-N9BSKUNKBC8 ) already exists.
occurs in script:
delete from session where workplace='WIN-N9BSKUNKBC8' ;
INSERT INTO session (workplace,ipaddress,logintime,loggeduser)
SELECT 'WIN-N9BSKUNKBC8' ,
inet_client_addr()::CHAR(14),current_timestamp::CHAR(28),CURRENT_USER
WHERE NOT EXISTS (SELECT 1 FROM session WHERE workplace='WIN-N9BSKUNKBC8' )
Sript is running form windows task scheduler on every 10 minutes. Error
occurs only sometimes.
How to fix this ?
Table is defined as
CREATE TABLE public.session
(
loggeduser character(10) ,
workplace character(16) NOT NULL,
ipaddress character(20) ,
logintime character(28) ,
activity timestamp with time zone,
CONSTRAINT session_pkey PRIMARY KEY (workplace)
)
Environment:
PostgreSQL 13.1, compiled by Visual C++ build 1914, 64-bit
Windows server 2019
psqlODBC driver 13.00.0000
I already asked this in this list many years ago. In this case Tom
replies that is looks like index race condition bug which was fixed in
later Postgres 9 version.
However this issue still exists in Postgres 13.1
Andrus.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: kobruleht2@hot.ee
Subject: Re: Duplicate key error
In-Reply-To: <a8656d4f-d692-ffae-077e-304f348e25bf@hot.ee>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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