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

Re: How can this INSERT fail?

From: Abbas <abbas(dot)dba(at)gmail(dot)com>
To: Peter Harris <peter(dot)harris(at)huzutech(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: How can this INSERT fail?
Date: 2012-04-25 12:29:36
Message-ID: CAHq+KHJ2tJRNsp-OoDuTzFV2y+H8hxC2H4-5ESwCskTLb5nF+Q@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
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

Responses

pgsql-sql by date

Next:From: Peter HarrisDate: 2012-04-25 13:01:54
Subject: Re: How can this INSERT fail?
Previous:From: Peter HarrisDate: 2012-04-25 11:45:18
Subject: How can this INSERT fail?

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