Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 8BAB71E62E85 for ; Wed, 25 Apr 2012 14:26:30 -0300 (ADT) Received: from zql.com ([206.222.31.58]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SN5z3-000270-E7 for pgsql-sql@postgresql.org; Wed, 25 Apr 2012 17:26:30 +0000 Received: from localhost.localdomain ([127.0.0.1] helo=localhost) by zql.com with smtp (Exim 4.68) (envelope-from ) id 1SN5yr-0000Uq-27; Wed, 25 Apr 2012 13:26:17 -0400 From: "Greg Sabino Mullane" To: pgsql-sql@postgresql.org Subject: Re: How can this INSERT fail? X-PGP-Key: 2529 DF6A B8F7 9407 E944 45B4 BC9B 9067 1496 4AC8 X-Request-PGP: http://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 In-Reply-To: Date: Wed, 25 Apr 2012 17:26:17 -0000 X-Mailer: JoyMail 2.4.0 Message-ID: X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201204/70 X-Sequence-Number: 36547 -----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-----