Re: How can this INSERT fail?

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