> 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<d...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql