Subject: Re: Duplicate key error
To: pgsql-general@lists.postgresql.org
References: <a8656d4f-d692-ffae-077e-304f348e25bf@hot.ee>
 <5b68b4b3-b957-5009-cf3e-4bbc9dcbd0d9@aklaver.com>
 <97577cda-b3fa-4edb-1c8a-2b39afad65c6@hot.ee>
 <c22a9fa0-9f3a-8b68-c1d4-a430cf7286bc@aklaver.com>
 <abc069b9-5e92-b461-f215-68ad67a533b6@hot.ee>
 <d669cc09-aa93-cdf3-5030-37318d2a9001@aklaver.com>
 <6a9dcf88-7b28-3b8f-0a1a-8a163f45a2a8@hot.ee>
From: Ron <ronljohnsonjr@gmail.com>
Message-ID: <19beef53-95f4-672a-deda-41e236b03e38@gmail.com>
Date: Thu, 4 Mar 2021 00:57:57 -0600
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.10.0
MIME-Version: 1.0
In-Reply-To: <6a9dcf88-7b28-3b8f-0a1a-8a163f45a2a8@hot.ee>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/19beef53-95f4-672a-deda-41e236b03e38%40gmail.com>
Precedence: bulk

On 3/4/21 12:14 AM, Andrus wrote:
> Hi!
>> 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.
>
> It looks like other process has added same row during trancaction.
>
> How to change script so that it works starting at Postgres 9.0 and does 
> not cause error in this case ?
>

Having all the processes which try to update that table explicitly set a 
serializable transaction would probably eliminate the duplicate key error.  
There's no free lunch, though: you'd have to handle the blocking.

-- 
Angular momentum makes the world go 'round.