Обсуждение: How can this INSERT fail?
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'
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''
-- --
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.
Which version of postgresql it is ?
Best Regards,
Abbas
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 HarrisSoftware EngineerHuzuTech Ltd.
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,
AbbasOn 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 HarrisSoftware EngineerHuzuTech Ltd.
-----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-----