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

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.

--   Richard Huxton  Archonet Ltd