Subject: Re: Bit by "commands ignored until end of transaction block"
 again
From: Craig Ringer <craig@postnewspapers.com.au>
To: Science <science@misuse.org>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <4A6CE37B.9020709@misuse.org>
References: <4A6CE37B.9020709@misuse.org>
Content-Type: text/plain
Date: Mon, 27 Jul 2009 08:00:28 +0800
Message-Id: <1248652828.2303.5.camel@ayaki>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

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.

-- 
Craig Ringer