From: Glenn Maynard <glenn@zewt.org>
To: pgsql-sql@postgresql.org
Subject: Re: Bit by "commands ignored until end of transaction block" again
Date: Sun, 26 Jul 2009 17:21:50 -0400
Message-ID: <bd36f99e0907261421g3199f708qfad92d1edb45d258@mail.gmail.com> (raw)
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>
> 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@archonet.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: glenn@zewt.org
Subject: Re: Bit by "commands ignored until end of transaction block" again
In-Reply-To: <bd36f99e0907261421g3199f708qfad92d1edb45d258@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox