From: Greg Sabino Mullane <greg@turnstep.com>
To: pgsql-sql@postgresql.org
Subject: Re: How can this INSERT fail?
Date: Wed, 25 Apr 2012 17:26:17 -0000
Message-ID: <dc95e526e636a3b5dd2777a9f98b978e@biglumber.com> (raw)
In-Reply-To: <CADP5CTO+Dw0qPq0U_YudL4=M70-VF7nLxYZNpUkO4nKTi7zsWA@mail.gmail.com>
-----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-----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: greg@turnstep.com
Subject: Re: How can this INSERT fail?
In-Reply-To: <dc95e526e636a3b5dd2777a9f98b978e@biglumber.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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