Re: How can this INSERT fail?

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.
>>
>
>