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

How can this INSERT fail?

From: Peter Harris <peter(dot)harris(at)huzutech(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: How can this INSERT fail?
Date: 2012-04-25 11:45:18
Message-ID: CADP5CTO+Dw0qPq0U_YudL4=M70-VF7nLxYZNpUkO4nKTi7zsWA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
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.

Responses

pgsql-sql by date

Next:From: AbbasDate: 2012-04-25 12:29:36
Subject: Re: How can this INSERT fail?
Previous:From: Venkateswara Rao DokkuDate: 2012-04-25 05:55:22
Subject: Regarding report generated by the pgFouine

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