Re: Bit by "commands ignored until end of transaction block" again

> Date: Thu, 23 Jul 2009 09:06:50 +0100
> From: Richard Huxton <dev@archonet.com>
> To: Glenn Maynard <glenn@zewt.org>
> Cc: pgsql-sql@postgresql.org
> Subject: Re: Bit by "commands ignored until end of transaction block"
>      again
> Message-ID: <4A681A1A.1090902@archonet.com>
> 
> Glenn Maynard wrote:
>> On Thu, Jul 23, 2009 at 2:41 AM, Richard Huxton<dev@archonet.com> wrote:
>>> Ah [cue light-bulb effect], I think I understand. Your function isn't in the
>>> database is it? Surely your application knows if it's issuing BEGIN..COMMIT?
>> I'm writing a Python library call.  It has no idea whether the caller
>> happens to be inside a transaction already, and I don't want to
>> specify something like "always run this inside a transaction".
>> (Callers are equally likely to want to do either, and it's bad API to
>> force them to start a transaction--the fact that I'm using the
>> database at al should be transparent.)
> 
> That last bit is never going to work. There always needs to be some 
> basic level of understanding between systems and transactions really 
> have to be part of that for talking to a RDBMS. There will have to be a 
> piece of code responsible for managing transactions somewhere in the 
> middleware/application layers.
> 
>>> You'll have people with torches and pitchforks after you if you change
>>> RELEASE SAVEPOINT to mean COMMIT. I might even lend them my pitchfork.
>> RELEASE SAVEPOINT would only COMMIT the transaction *if* the savepoint
>> that it's releasing started it.  Every currently-valid case requires
>> that a transaction is already started, so no existing code would be
>> affected by this.
>>
>> SAVEPOINT a; -- implicitly issues BEGIN because one wasn't started
>> RELEASE SAVEPOINT a; -- implicitly issues COMMIT because savepoint "a"
>> issued the BEGIN, not the user
> [snip]
>> Of course, there are other details--it probably shouldn't allow
>> ROLLBACK or COMMIT on an implicit transaction block, for example.
> 
> All you're doing here is moving the point of confusion around, surely? 
> At some point you still need to know whether you can issue 
> BEGIN/ROLLBACK/COMMIT etc.
> 
>>> Could it generate: "SELECT ensure_cache_contains(key,data)"? Then ten lines
>>> of plpgsql will neatly encapsulate the problem. That plpgsql can be
>>> automatically generated easily enough too.
>> I don't think so, at least not without digging into internals.  Django
>> is built around knowing all data types, so it'd need to be givne types
>> explicitly--for example, to know whether a timestamp should be
>> formatted as a timestamp, date or time.  (I do have a couple other
>> columns here--timestamps for cache expiration, etc.)  I'll have to ask
>> Django-side if there's a public API to do this, but I don't think
>> there is.
> 
> Well, the types would be exactly the same as for your existing insert. 
> All it's really doing is changing the template those values get 
> substituted into. It presumably does mean patching the ORM (or 
> subclassing from it anyway).
> 
>>> Ah, the joys of badly designed ORMs. The nice thing is that there seem to be
>>> plenty of bad ones to choose from too. If your ORM doesn't handle
>>> transactions well, the more you use it the more difficult your life will
>>> become. I'd be tempted to tidy up your existing fixes and wrap Django's ORM
>>> as cleanly as you can. That's assuming they're not interested in patches.
>> The ORM on a whole is decent, but there are isolated areas where it's
>> very braindamaged--this is one of them.  They have a stable-release
>> API-compatibility policy, which I think just gets them stuck with some
>> really bad decisions for a long time.
> 
> Presumably they targetted MySQL first, where there's a lot less use in 
> multi-statement transactions with their different behaviour of their 
> various storage-engines.
> 

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
sqlcode') # SQL => '--some sql code'    }  }
 
} # SQL => 'COMMIT'

This is pretty kludgy but lets me start any arbitrary transaction 
without worrying if there's already another one running "on top" of me 
(which I gather is your worry in this case). Dunno if the approach could 
work using a class wrapper and the Django ORM, but I would have thought 
that the implementation shouldn't be too hard..

And apologies to the list for going a little off-track from Pg.

Steve