Re: How can this INSERT fail?

-----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(at)turnstep(dot)com
PGP Key: 0x14964AC8 201204251322
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAk+YM6IACgkQvJuQZxSWSshlSQCg8qVLbaYcEEJ9vOU43f2Irawv
2nwAnAxidDPeAohXOOTPa7mK0ORz2wc9
=xIfz
-----END PGP SIGNATURE-----