[Buildbot-devel] IntegrityError on postgresql, "claimed_at" violates not-null constraint

[Georges Racinet]

On Sep 22, 2012, at 3:55 PM, Dustin J. Mitchell wrote:
>
> Transactions don't assure read-before-write atomicity in any of the
> databases we support.  That would be "serializable" isolation
>  http://en.wikipedia.org/wiki/Isolation_%28database_systems%29#Isolation_levels
> which basically means that if any of the data returned by a SELECT
> during the transaction has changed when the transaction is committed,
> then the commit fails.
>
> I think postgres *can* support this,

It seems so, but only starting in 9.1, but 8.4 is quite commony found.

In older versions, "Serializable" in Postgres is what's now called  
"Repeatable Read".
Quoting http://www.postgresql.org/docs/9.1/static/transaction-iso.html :

    "Note: Prior to PostgreSQL version 9.1, a request for the  
Serializable transaction isolation level provided exactly the same  
behavior described here. To retain the legacy Serializable behavior,  
Repeatable Read should now be requested."

Interestingly, the example that piece of doc (v9.1) gives of a  
rollbacked transaction due to a concurrent update impacting a SELECT  
is exactly the one it gave as a limitation of the Serialized mode in  
prior versions : two transactions writing different new lines with  
values read from lines updated by the other one.

Regards,
--
Georges Racinet
Anybox SAS, http://anybox.fr
Bureau: 09 53 53 72 97 Portable: 06 51 32 07 27
GPG: 0x33AB0A35, sur serveurs publics