Message-ID: <4A6DA03C.1040404@misuse.org>
Date: Mon, 27 Jul 2009 08:40:28 -0400
From: Science <science@misuse.org>
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
MIME-Version: 1.0
To: Craig Ringer <craig@postnewspapers.com.au>
CC: pgsql-sql@postgresql.org
Subject: Re: Bit by "commands ignored until end of transaction block"
 again
References: <4A6CE37B.9020709@misuse.org> <1248652828.2303.5.camel@ayaki>
In-Reply-To: <1248652828.2303.5.camel@ayaki>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Craig Ringer wrote:
> On Sun, 2009-07-26 at 19:15 -0400, Science wrote:
> 
>> FWIW, the way the Rails ORM ActiveRecord (another fairly damaged ORM) 
>> handles this is by allowing you to open any number of transaction 
>> blocks, but only the outer transaction block commits (in Pg):
>>
>> Property.transaction { # SQL => 'BEGIN'
>>    User.transaction {
>>      Foo.transaction {
>>        Foo.connection.execute('--some sql code') # SQL => '--some sql code'
>>      }
>>    }
>> } # SQL => 'COMMIT'
> 
> What happens if, Foo.transaction does something that causes an error,
> though, or issues a rollback? It's not creating savepoints, so if
> Foo.transaction rolls back it throws out the work of User.transaction
> and Property.transaction too.
> 
> Ugh.
> 
> That design would be quite good _IF_ it used savepoints:
> 
> 
> Property.transaction { # SQL => 'BEGIN'
>    User.transaction {  # SQL => SAVEPOINT User
>      Foo.transaction { # SQL => SAVEPOINT Foo
>        Foo.connection.execute('--some sql code') # SQL => '--some sql code'
>      }                 # SQL => RELEASE SAVEPOINT Foo
>    }                   # SQL => RELEASE SAVEPOINT User
> }                      # SQL => 'COMMIT'
> 
> ... so that inner transactions could ROLLBACK TO SAVEPOINT on error ,
> and so that asking for a rollback would give you a ROLLBACK TO SAVEPOINT
> if the transaction is a subtransaction.
> 

For all I know that's how it works these days. I haven't looked at the 
code underneath this in a couple of years. It should be trivial to 
implement in the way you describe based on how the Ruby codebase is set 
up -- hopefully all this will help OP with the Django/Python version of 
the same problem.

Steve