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 ?
>
> Savepoint release
> -----------------
>
> Some data managers may only support a limited number of savepoints.
>
>>>> dm['bob-balance'] = 100.0
>>>> dm['bob-balance']
> 100.0
>>>> savepoint1 = transaction.savepoint()
>
>>>> dm['bob-balance'] = 200.0
>>>> dm['bob-balance']
> 200.0
>>>> savepoint2 = transaction.savepoint()
>
>>>> dm['bob-balance'] = 300.0
>>>> dm['bob-balance']
> 300.0
>>>> savepoint3 = transaction.savepoint()
>
> To release resources on the data manager, a savepoint may be released:
>
>>>> savepoint2.release()
>
> The savepoint then becomes invalid and may no longer be used:
>
>>>> savepoint2.rollback()
> Traceback (most recent call last):
> ...
> InvalidSavepointError
>
> Subsequent savepoints remain valid:
>
>>>> dm['bob-balance'] = 400.0
>>>> dm['bob-balance']
> 400.0
>>>> savepoint3.rollback()
>>>> dm['bob-balance']
> 300.0
>
> As do previous savepoints:
>
>>>> savepoint1.rollback()
>>>> dm['bob-balance']
> 100.0
>
>>>> transaction.abort()
>
>
> Laurence
>
>
> [1]
> http://groups.google.com/group/sqlalchemy/browse_thread/thread/b2594ff621538f3f
>
> [2] http://svn.zope.org/repos/main/transaction/branches/elro-savepoint-release
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
-- You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to [email protected].
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
