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

Glenn Maynard wrote:
> On Thu, Jul 23, 2009 at 1:31 AM, Richard Huxton<dev@archonet.com> wrote:
>>>  - Let me use SAVEPOINT outside of a transaction,
>> You are never outside a transaction. All queries are executed within a
>> transaction.
> 
> "Transaction block", then, if you insist.
> 
>> I think this is the root of your problem - all queries are within a
>> transaction so either:
>> 1. You have a transaction that wraps a single statement. If you get an error
>> then only that statement was affected.
>> 2. You have an explicit BEGIN...COMMIT transaction which could use a
>> savepoint.
> 
> Savepoints can only be used inside transaction blocks.

Says who? Wouldn't work in plpgsql as exception handling if that was the 
case.
>  My function
> has no idea whether it's being called inside a transaction block.
> 
> From inside a transaction block, my function would need to call
> SAVEPOINT/RELEASE SAVEPOINT.
> 
> If it's not in a transaction block, it needs to call BEGIN/COMMIT
> instead.  SAVEPOINT will fail with "SAVEPOINT can only be used in
> transaction blocks".

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?

> This would be very simple and clean if the SAVEPOINT command
> transparently issued BEGIN if executed outside of a transaction block,
> marking the savepoint so it knows that when the savepoint is released
> or rolled back, the associated transaction block needs to be committed
> or rolled back, too.  At that point, you could stop using
> BEGIN/COMMIT/ROLLBACK entirely, and just let savepoints do it, if you
> wanted--with this, the transaction commands are essentially redundant.
> 
> I can't count the number of times I've wished for this.

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.

>> Typically, if you're in a plpgsql function you would just catch "unique"
>> exception codes from your insert. Or, update, see if any rows were affected,
>> if not try an insert and if that gives a duplicate go back and try the
>> update. You might want the second approach if 99% of the time the cache is
>> already populated.
> 
> It's just a simple INSERT, generated from a Model.objects.create() in Django.

OK, it sounds like create() isn't the method to call then, since you 
don't want to generate a simple INSERT.

>>> Lacking anything better, I'll probably end up dropping out of the ORM
>>> and using some uglier SQL to work around this, but this is so trivial
>>> that it's silly to have to do that.  I can't do it within the ORM; it
>>> doesn't have the vocabulary.
>> The ORM can't control transactions, can't call functions or can't set
>> savepoints?
> 
> It can't write the necessary SQL to say "insert this unless it already
> exists", namely:
> 
>     INSERT INTO cache (key, data) (
>         SELECT i.key, "data",
>         FROM
>             (VALUES ("key")) AS i(key)
>             LEFT JOIN cache prior_entry ON (prior_entry.key = "key")
>         WHERE
>             prior_entry.key IS NULL
>     )

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 know nothing of Django, but perhaps it's possible to subclass Model 
and add an "ensure" method that will call your plpgsql function?

> It--Django--also doesn't have a mature transaction/savepoint system;
> in fact, its transaction handling is an absolute mess.  I've written
> helpers for my main codebase that simply says "wrap this in a
> transaction block if one isn't already started, otherwise wrap it in a
> savepoint".  I don't want to use that code here, because it's nitty
> code: it needs to poke at Django internals to figure out whether it's
> in a transaction block or not, and dealing with other API
> compatibility issues.

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.

--   Richard Huxton  Archonet Ltd