Glenn Maynard wrote:
On Thu, Jul 23, 2009 at 1:31 AM, Richard Huxton<d...@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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
