pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow can this INSERT fail?
4+ messages / 3 participants
[nested] [flat]
* How can this INSERT fail?
@ 2012-04-25 11:45 Peter Harris <peter.harris@huzutech.com>
0 siblings, 2 replies; 4+ messages in thread
From: Peter Harris @ 2012-04-25 11:45 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How can this INSERT fail?
@ 2012-04-25 12:29 Abbas <abbas.dba@gmail.com>
parent: Peter Harris <peter.harris@huzutech.com>
1 sibling, 1 reply; 4+ messages in thread
From: Abbas @ 2012-04-25 12:29 UTC (permalink / raw)
To: Peter Harris <peter.harris@huzutech.com>; +Cc: pgsql-sql
Which version of postgresql it is ?
Best Regards,
Abbas
On Wed, Apr 25, 2012 at 5:15 PM, Peter Harris <peter.harris@huzutech.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.
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How can this INSERT fail?
@ 2012-04-25 13:01 Peter Harris <peter.harris@huzutech.com>
parent: Abbas <abbas.dba@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Peter Harris @ 2012-04-25 13:01 UTC (permalink / raw)
To: Abbas <abbas.dba@gmail.com>; +Cc: pgsql-sql
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.dba@gmail.com> wrote:
> Which version of postgresql it is ?
>
> Best Regards,
> Abbas
>
>
>
> On Wed, Apr 25, 2012 at 5:15 PM, Peter Harris <peter.harris@huzutech.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.
>>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How can this INSERT fail?
@ 2012-04-25 17:26 Greg Sabino Mullane <greg@turnstep.com>
parent: Peter Harris <peter.harris@huzutech.com>
1 sibling, 0 replies; 4+ messages in thread
From: Greg Sabino Mullane @ 2012-04-25 17:26 UTC (permalink / raw)
To: pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
> 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?
Yes, there is a race condition. You can solve it by setting the isolation
to repeatable read. As long as your application doesn't *need* the default
read committed level, it should be logically safe and not affect performance.
(But make sure you use repeatable read and not serializable, as they are
distinct in recent versions of Postgres and the latter does carry a
potential performance penalty).
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 201204251322
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iEYEAREDAAYFAk+YM6IACgkQvJuQZxSWSshlSQCg8qVLbaYcEEJ9vOU43f2Irawv
2nwAnAxidDPeAohXOOTPa7mK0ORz2wc9
=xIfz
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-04-25 17:26 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-04-25 11:45 How can this INSERT fail? Peter Harris <peter.harris@huzutech.com>
2012-04-25 12:29 ` Abbas <abbas.dba@gmail.com>
2012-04-25 13:01 ` Peter Harris <peter.harris@huzutech.com>
2012-04-25 17:26 ` Greg Sabino Mullane <greg@turnstep.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox