Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lHXyG-0001Nb-1O for pgsql-general@arkaria.postgresql.org; Wed, 03 Mar 2021 20:19:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lHXyE-00084b-Ve for pgsql-general@arkaria.postgresql.org; Wed, 03 Mar 2021 20:19:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lHXyE-00083Z-2d for pgsql-general@lists.postgresql.org; Wed, 03 Mar 2021 20:19:46 +0000 Received: from out4-smtp.messagingengine.com ([66.111.4.28]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lHXy8-0004Rx-T7 for pgsql-general@postgresql.org; Wed, 03 Mar 2021 20:19:45 +0000 Received: from compute3.internal (compute3.nyi.internal [10.202.2.43]) by mailout.nyi.internal (Postfix) with ESMTP id 822B35C00E0; Wed, 3 Mar 2021 15:19:39 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute3.internal (MEProxy); Wed, 03 Mar 2021 15:19:39 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= subject:to:references:from:message-id:date:mime-version :in-reply-to:content-type:content-transfer-encoding; s=fm2; bh=s P0urP9TOQqQPgsxCNI2Z1F5T4ZAzWG9qANZRNVeLGc=; b=dtJ2nB7HkeAlvLyC7 i9FpZ623aqxkzatH1/mI4GE+ipgLRgoYcgKOCNDT0Vblx7ADdfjXpi62kJiy9QHS Id8IovXDChlWz570hXiqXhqxdH8nLixbpFOZuDsMJ67BY92YANu1Wxfj61ePrCR5 eLLBOtr646D4SYKawjYPOpcDFVT9U0LpS6HZB346pnQ09PXvdKK4pjRBDIXDAtUV 2rBcqyPt87myVjBHmAYlofAB86hF9WyZkSBEyBNbxqwXZmRbtL/geWVFPO1vYTvN Dg4VWmLb37ZsJc2ZWehW8hlED9d0+/BA7mIups33TOT5j9LSCFzxx7ibFEis9X+i jo7Kw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=content-transfer-encoding:content-type :date:from:in-reply-to:message-id:mime-version:references :subject:to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender :x-sasl-enc; s=fm2; bh=sP0urP9TOQqQPgsxCNI2Z1F5T4ZAzWG9qANZRNVeL Gc=; b=Tye5a+f0mdJ73rzVaI+WimIfZM19FAIgu/G5Tmqj5ftvaii6/7Lf1j4p7 K4PaNkJzas5oYwUZ9LJNw9LPiVSPsseAHCeRmy4/RC4F6QbzQZtWvuN0gtuHqkqv 1OTDJoNEMpSn0GOTKvmAVbTbOvAIIxHbtXoPCwNLaxuLVqGqZwtA0MQYqnSd+5Vy TpwTNsUclb7vcKK3XXYY6rQ62jyj1sjmEtRQGghwu79J1FiZ23fL+zYi5v+N4Iwb Hz7BmHtj7o0ZXx8fxwV5Xc/uNjsL3ZcRLV3hOQVQpBsJhD4+WvTTls1f7w9SUXpC H4SFaGi6q9JSBSG1BMy90RFlBNwLg== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeduledruddtvddgudeftdcutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfgh necuuegrihhlohhuthemuceftddtnecunecujfgurhepuffvfhfhkffffgggjggtgfesth ekredttdefjeenucfhrhhomheptegurhhirghnucfmlhgrvhgvrhcuoegrughrihgrnhdr khhlrghvvghrsegrkhhlrghvvghrrdgtohhmqeenucggtffrrghtthgvrhhnpeevveefve efueegffekkefhgeeiueffgfetueejvdfghfeujeejhfekjeelgedtgeenucfkphepleej rdduudefrddvudejrddvjeenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmh grihhlfhhrohhmpegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Received: from [192.168.1.4] (97-113-217-27.tukw.qwest.net [97.113.217.27]) by mail.messagingengine.com (Postfix) with ESMTPA id 810C11080059; Wed, 3 Mar 2021 15:19:38 -0500 (EST) Subject: Re: Duplicate key error To: Andrus , pgsql-general@postgresql.org References: <5b68b4b3-b957-5009-cf3e-4bbc9dcbd0d9@aklaver.com> <97577cda-b3fa-4edb-1c8a-2b39afad65c6@hot.ee> From: Adrian Klaver Message-ID: Date: Wed, 3 Mar 2021 12:19:37 -0800 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.8.0 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset=utf-8; format=flowed Content-Language: en-US Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 3/3/21 11:59 AM, Andrus wrote: > Hi! >> >> And if that name already exists there would be a duplicate key error. > > Name cannot exist:  First delete deletes its value. Tables are not > changed by external process during transaction. As double insurance, > insert perfoms additional existence check and adds only if key does not > exist. The error message: " duplicate key value violates unique constraint "session_pkey" Key (workplace)=(WIN-N9BSKUNKBC8 ) already exists. " says something else is inserting/updating using that key value. So obviously your script is not catching all the conflicts. At this point your best bet is to monitor the Postgres log and see what else is happening at the time of the error. I'm guessing you will find another process working on that table. > > >Last inserted row data should remain. >> >> I'm not understanding the above. > > This table should contain last login time, user and ip address. It > should be updated by every process on login. For this old entry is > removed if it exists and new entry with same primary key is added. > > Andrus. > > -- Adrian Klaver adrian.klaver@aklaver.com