To: pgsql-general@postgresql.org
From: Andrus <kobruleht2@hot.ee>
Subject: Duplicate key error
Message-ID: <a8656d4f-d692-ffae-077e-304f348e25bf@hot.ee>
Date: Wed, 3 Mar 2021 17:23:35 +0200
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:78.0) Gecko/20100101
 Thunderbird/78.8.0
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------CCCED139404092781967E516"
Content-Language: et
Archived-At: 
 <https://www.postgresql.org/message-id/a8656d4f-d692-ffae-077e-304f348e25bf%40hot.ee>
Precedence: bulk

This is a multi-part message in MIME format.
--------------CCCED139404092781967E516
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

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.


--------------CCCED139404092781967E516
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>Hi!</p>
    <p>Sometimes duplicate key error <br>
      <br>
          duplicate key value violates unique constraint "session_pkey"<br>
          Key (workplace)=(WIN-N9BSKUNKBC8 ) already exists.<br>
      <br>
      occurs in script:<br>
      <br>
          delete from session where workplace='WIN-N9BSKUNKBC8' ;<br>
          INSERT INTO session (workplace,ipaddress,logintime,loggeduser)<br>
          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' )<br>
      <br>
      Sript is running form windows task scheduler on every 10 minutes.
      Error occurs only sometimes.<br>
      <br>
      How to fix this ?<br>
      Table is defined as<br>
      <br>
          CREATE TABLE public.session<br>
          (<br>
              loggeduser character(10) ,<br>
              workplace character(16) NOT NULL,<br>
              ipaddress character(20) ,<br>
              logintime character(28) ,<br>
              activity timestamp with time zone,<br>
              CONSTRAINT session_pkey PRIMARY KEY (workplace)<br>
          )<br>
      <br>
      <br>
      Environment:<br>
      <br>
          PostgreSQL 13.1, compiled by Visual C++ build 1914, 64-bit<br>
          Windows server 2019<br>
          psqlODBC driver 13.00.0000</p>
    <p><br>
    </p>
    <p>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.</p>
    <p>However this issue still exists in Postgres 13.1</p>
    <p>Andrus.<br>
    </p>
  </body>
</html>

--------------CCCED139404092781967E516--