MIME-Version: 1.0
In-Reply-To: <4A681A1A.1090902@archonet.com>
References: <bd36f99e0907222158k6b4b3a0al65766c0d9c4906cf@mail.gmail.com>
	<4A67F5B6.4080409@archonet.com>
	<bd36f99e0907222304x38cfa50bkeba19f7c9b2694@mail.gmail.com>
	<4A680603.9010707@archonet.com>
	<bd36f99e0907230039l29b66027ibf51ae2c715a44b6@mail.gmail.com>
	<4A681A1A.1090902@archonet.com>
Date: Sun, 26 Jul 2009 17:21:50 -0400
Message-ID: <bd36f99e0907261421g3199f708qfad92d1edb45d258@mail.gmail.com>
Subject: Re: Bit by "commands ignored until end of transaction block"
	again
From: Glenn Maynard <glenn@zewt.org>
To: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

> The above situation only arises if you run in autocommit mode which is th=
e 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@archonet.com> wrote:
>> I'm writing a Python library call. =C2=A0It has no idea whether the call=
er
>> 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 cod=
e
> 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.

--=20
Glenn Maynard