Duplicate key error

From: Andrus <kobruleht2(at)hot(dot)ee>
To: pgsql-general(at)postgresql(dot)org
Subject: Duplicate key error
Date: 2021-03-03 15:23:35
Message-ID: a8656d4f-d692-ffae-077e-304f348e25bf@hot.ee
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2021-03-03 15:42:20 Re: Duplicate key error
Previous Message Tom Lane 2021-03-03 15:15:46 Re: permission denied for pg_temp_XX when vacuuming