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

On Thu, Jul 23, 2009 at 2:41 AM, Richard Huxton<dev(at)archonet(dot)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.)

> 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

BEGIN;
SAVEPOINT a;
RELEASE SAVEPOINT a; -- will not commit, because savepoint "a" didn't
start the transaction

Of course, there are other details--it probably shouldn't allow
ROLLBACK or COMMIT on an implicit transaction block, for example.

> 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.

> 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.

--
Glenn Maynard