On Jan 16, 6:39 pm, Michael Bayer <mike...@zzzcomputing.com> wrote: > On Jan 16, 2010, at 12:35 PM, Laurence Rowe wrote: > > > Hi, > > > Following a request[1] for savepoint release support in > > zope.sqlalchemy, I've been looking into how this might be done. Adding > > the necessary support to Zope's transaction module was quite simple > > [2], but the mapping of Zope transaction savepoints -> SQLAlchemy > > nested transactions -> database savepoints gives a problem... > > > How can I get SQLAlchemy to release a savepoint, without also > > releasing all subsequent savepoints (the nested transactions? This is > > demonstrated by the doctest below from my branch of Zope's transaction > > module: > > OK, the first thing I see here, unless I'm misunderstanding, is that you're > looking to remove a savepoint from an arbitrary point in the nesting. I.e. > not just the "endpoint". > > So the next road I went down, OK, we would need a way to manipulate the list > of Transactions at the engine level and SessionTransactions at the ORM level, > such that a node can be removed from the middle of the list, issuing a > RELEASE, and leaving the list otherwise with the same nodes and endpoints. > > I then made a quick patch against engine/base.py to try this. A test > looks like this: > > trans = conn.begin() > conn.execute("select 1") > trans2 = conn.begin_nested() > conn.execute("select 1") > trans3 = conn.begin_nested() > conn.execute("select 1") > trans2.release() > trans3.release() > > trans.commit() > > Do I have this right ? trans2 is the first savepoint, trans3 is the second. > We want to release trans2 first, leaving trans3 intact. > > PG at least does not allow this (trimmed): > > BEGIN > select 1 > SAVEPOINT sa_savepoint_1 > select 1 > SAVEPOINT sa_savepoint_2 > select 1 > RELEASE SAVEPOINT sa_savepoint_1 > RELEASE SAVEPOINT sa_savepoint_2 > > sqlalchemy.exc.InternalError: (InternalError) no such savepoint > 'RELEASE SAVEPOINT sa_savepoint_2' {} > > Here's what PG docs have to say: > > http://www.postgresql.org/docs/8.1/static/sql-release-savepoint.html > > RELEASE SAVEPOINT also destroys all savepoints that were established after > the named savepoint was established. > > I.e. that statement is in direct contradiction to your request "without also > releasing all subsequent savepoints". > > So Postgresql, which I consider the gold standard of transaction operation, > would appear to not support this. Do we know that this is a viable feature > to be pursuing ?
Thanks for looking into this. I hadn't realized that subsequent savepoints would also be destroyed. I'll make Zope's transaction module match the PostgreSQL behaviour. As savepoints and nested transactions are then equivalent I shouldn't need anything more from SQLAlchemy. Laurence
-- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.