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

> The above situation only arises if you run in autocommit mode which is the default for psql (which I have *never* understood).

This is the short answer, in practice--assume that either a
transaction is started or will be started by the SAVEPOINT command,
and that if a COMMIT is needed (as a result of the SAVEPOINT or which
was already needed), that the caller will do it.

(I hate non-autocommit. It defies basic code design instincts, which
tell me that whoever starts a transaction should finish it. I
shouldn't be issuing a non-autocommit SAVEPOINT/RELEASE, and then
assuming the caller will COMMIT the transaction that was started
automatically. I'm stuck with it in Django. Yuck, but oh well;
battling the framework's idioms isn't going to help anything.)

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

It's never 100% transparent--the case of making calls during a
transaction and then rolling the whole thing back still needs to be
documented. The point, though, is that this isn't a database-centric
operation, so it shouldn't have usage restrictions like "must always"
or "must never be inside a transaction".

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

Not at all--I don't need to use any of these commands. I just do this:

SAVEPOINT s;
INSERT INTO table ...;
RELEASE SAVEPOINT s;

to guarantee that my code's effect on the database is atomic.

someone else wrote:
> So, what you're really asking for boils down to nestable transactions?

That's how I've thought of savepoints from day one. When I use them
in Python code, I use a with_transaction wrapper, which transparently
uses a transaction or a savepoint.

--
Glenn Maynard