Re: Bit by "commands ignored until end of transaction block" again - Mailing list pgsql-sql

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

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.

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

>> 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
cacheprior_entry ON (prior_entry.key = "key")    WHERE        prior_entry.key IS NULL) 

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.

--
Glenn Maynard