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.