Skip site navigation (1) Skip section navigation (2)

Re: How can this INSERT fail?

From: Peter Harris <peter(dot)harris(at)huzutech(dot)com>
To: Abbas <abbas(dot)dba(at)gmail(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: How can this INSERT fail?
Date: 2012-04-25 13:01:54
Message-ID: CADP5CTPjtPyuGXA+KQfbtrBfvKbg8269EiuiyhJpLRTEmx9=YA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
It is 9.0.4 if that helps.
Is there some reason it should work differently on 9.1 or 8.x?

On 25 April 2012 13:29, Abbas <abbas(dot)dba(at)gmail(dot)com> wrote:

> Which version of postgresql it is ?
>
> Best Regards,
> Abbas
>
>
>
> On Wed, Apr 25, 2012 at 5:15 PM, Peter Harris <peter(dot)harris(at)huzutech(dot)com>wrote:
>
>> I'm trying to INSERT into a table, avoiding duplicates, using this sort
>> of thing:
>> -- --
>> insert into buddyinvite (bi_user, bi_invited)
>>   select 'a', 'b'
>>   except
>>   select bi_user, bi_invited from buddyinvite where bi_user = 'a' and
>> bi_invited = 'b'
>> -- --
>> I thought this should work, because if such a row already exists, the
>> EXCEPT should remove the ('a','b') row from the select, and insert 0 rows.
>>
>> When I do this from psql it acts as I expect.
>>
>> But in my webserver logs, I get this error sometimes:
>> -- --
>> [23/Apr/2012:15:31:16]  ERROR: error 'ERROR:  duplicate key value
>> violates unique constraint "buddyinvite_pkey"
>> DETAIL:  Key (bi_user, bi_invited)=(a, b) already exists.
>> ' in 'insert into buddyinvite (bi_user, bi_invited) select 'a', 'b'
>> except  select bi_user, bi_invited from buddyinvite where bi_user = 'a' and
>> bi_invited = 'b''
>> -- --
>> (verbatim except for anonymised user IDs)
>>
>> I can only assume it is possible for multiple transactions to overlap and
>> one of them to miss the row so it doesn't appear in the EXCEPT SELECT but
>> the row appears before the transaction commits and so an error occurs. My
>> first thought was SELECT ... FOR UPDATE, but I can't do that within an
>> EXCEPT.
>>
>> Can someone confirm whether I could avoid these errors by some form of
>> SET TRANSACTION ISOLATION LEVEL, or let me know if I am just Doing It Wrong?
>>
>> To be honest, if there's no good solution I'm happy to simply swallow the
>> exceptions, because I don't care (in this case) which of two competing
>> transactions gets to insert the row. However, if I am doing something
>> stupid I'd like to be put right!
>>
>> Peter Harris
>> Software Engineer
>> HuzuTech Ltd.
>>
>
>

In response to

pgsql-sql by date

Next:From: Greg Sabino MullaneDate: 2012-04-25 17:26:17
Subject: Re: How can this INSERT fail?
Previous:From: AbbasDate: 2012-04-25 12:29:36
Subject: Re: How can this INSERT fail?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group