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.
