Laurence Rowe, author of zope.sqlalchemy, suggested we use this
mailing list for this discussion.
I've installed the savepoint-release branch of zope.sqlalchemy and
transaction. I'd like to get this working for sqla 0.6, and so there
are a few issues I would like to list here:
*********************************************************************
ISSUE A: Beginning a transaction with a savepoint
--------------------------------------
Currently, this does not seem to work properly. If you begin a
transaction immediately with a transaction.savepoint() command, from
my inspection of the code, there is no datamanager joined to the
transaction so a normal "BEGIN" and "ROLLBACK" are issued instead of
"SAVEPOINT sa_savepoint_1" and "ROLLBACK TO SAVEPOINT sa_savepoint_1"
should the database statement fail.
For example, take this short code segment:
=====================================
obj_a=Customer()
obj_a.customerid='7' # already exists in database, will cause
IntegrityError
obj_b=Customer()
obj_b.customerid='77'
sp_a = transaction.savepoint()
DBSession.add(obj_a)
try:
DBSession.flush() # this issues no savepoint
except IntegrityError:
sp_a.rollback() # transaction rolledback instead of to
savepoint
else:
sp_a.release()
sp_b = transaction.savepoint() # now the transaction is already over/
rolledback
DBSession.add(obj_b)
try:
DBSession.flush()
except IntegrityError:
sp_b.rollback()
else:
sp_b.release()
Here is the output:
===========================
>>> obj_a=Customer()
>>> obj_a.customerid='7' # already exists in database, will cause
>>> IntegrityError
>>>
>>> obj_b=Customer()
>>> obj_b.customerid='77'
>>>
>>> sp_a = transaction.savepoint()
>>> DBSession.add(obj_a)
>>> try:
... DBSession.flush()
... except IntegrityError:
... sp_a.rollback()
... else:
... sp_a.release()
...
INFO [sqlalchemy.engine.base.Engine.0x...5150] SELECT USER FROM DUAL
INFO [sqlalchemy.engine.base.Engine.0x...5150] {}
INFO [sqlalchemy.engine.base.Engine.0x...5150] BEGIN
/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.0.4dev-
py2.6-linux-x86_64.egg/sqlalchemy/engine/default.py:495: SAWarning:
Unicode type received non-unicode bind param value '7'
param[key.encode(encoding)] = processors[key](compiled_params[key])
INFO [sqlalchemy.engine.base.Engine.0x...5150] INSERT INTO customers
(customerid, phonenumber, businessphone, firstname, lastname,
address1, address2, city, state, zip, email, type, accountopendate)
VALUES
(:customerid, :phonenumber, :businessphone, :firstname, :lastname, :address1,
:address2, :city, :state, :zip, :email, :type, :accountopendate)
INFO [sqlalchemy.engine.base.Engine.0x...5150] {'city': None,
'accountopendate': datetime.date(2010, 5, 31), 'businessphone': None,
'firstname': None, 'zip': None, 'lastname': None, 'address2': None,
'email': None, 'state': None, 'phonenumber': None, 'address1': None,
'type': 'I', 'customerid': '7'}
INFO [sqlalchemy.engine.base.Engine.0x...5150] ROLLBACK
>>>
>>>
>>> sp_b = transaction.savepoint() # now the transaction is already
>>> over/rolledback
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/home/rarch/tg2env/lib/python2.6/site-packages/transaction-0-
py2.6.egg/transaction/_manager.py", line 99, in savepoint
return self.get().savepoint(optimistic)
File "/home/rarch/tg2env/lib/python2.6/site-packages/transaction-0-
py2.6.egg/transaction/_transaction.py", line 253, in savepoint
self._saveAndRaiseCommitishError() # reraises!
File "/home/rarch/tg2env/lib/python2.6/site-packages/transaction-0-
py2.6.egg/transaction/_transaction.py", line 250, in savepoint
savepoint = Savepoint(self, optimistic, *self._resources)
File "/home/rarch/tg2env/lib/python2.6/site-packages/transaction-0-
py2.6.egg/transaction/_transaction.py", line 651, in __init__
raise TypeError("Savepoints unsupported", datamanager)
TypeError: ('Savepoints unsupported',
<zope.sqlalchemy.datamanager.SessionDataManager object at 0x14f57650>)
*********************************************************************
*********************************************************************
ISSUE B: clearing entire sqla session
--------------------------------------
The zope.sqlalchemy.datamanager.SessionSavepoint class contains this
rollback() method:
def rollback(self):
# no need to check validity, sqlalchemy should raise an
exception. I think.
self.transaction.rollback()
_SESSION_STATE[id(self.session)] = self.state
self.session.clear() # remove when Session.rollback does an
attribute_manager.rollback
(Note that in sqla 0.6 this would be self.session.expunge_all()
instead of clear() but that isn't the issue I'm reporting...)
I'd like to make the appeal that session.clear() is not quite the
behavior we really want, since it clears the entire session, not just
back to the savepoint. This is causing problems for me and I think
breaks what sqlalchemy has already worked out for us.
As a simple example:
=====================================
manager = DBSession.query(User).get(1)
#update something
manager.password = 'changedpasswd'
#now add a permission unless it already exists (which it does in this
case)
p = Permission()
p.permission_name=u'manage'
sp = transaction.savepoint()
DBSession.add(p)
try:
DBSession.flush()
except IntegrityError:
sp.rollback() # this *should* clear 'p' from the session, but not
touch manager
else:
sp.release()
#now, continue with working with manager...
perms = manager.permissions # oops ... manager has been thrown away,
but should not have been
=====================================
Output:
-----------------------------------
>>> manager = DBSession.query(User).get(1)
INFO [sqlalchemy.engine.base.Engine.0x...8150] SELECT USER FROM DUAL
INFO [sqlalchemy.engine.base.Engine.0x...8150] {}
INFO [sqlalchemy.engine.base.Engine.0x...8150] BEGIN
INFO [sqlalchemy.engine.base.Engine.0x...8150] SELECT
tg_user.password AS tg_user_password, tg_user.user_id AS
tg_user_user_id, tg_user.user_name AS tg_user_user_name,
tg_user.email_address AS tg_user_email_address, tg_user.display_name
AS tg_user_display_name, tg_user.created AS tg_user_created,
tg_group_1.group_id AS tg_group_1_group_id, tg_group_1.group_name AS
tg_group_1_group_name, tg_group_1.display_name AS
tg_group_1_display_name, tg_group_1.created AS tg_group_1_created,
tg_permission_1.permission_id AS tg_permission_1_permission_id,
tg_permission_1.permission_name AS tg_permission_1_permissi_1,
tg_permission_1.description AS tg_permission_1_description
FROM tg_user, tg_user_group tg_user_group_1, tg_group tg_group_1,
tg_group_permission tg_group_permission_1, tg_permission
tg_permission_1
WHERE tg_user.user_id = :param_1 AND tg_permission_1.permission_id(+)
= tg_group_permission_1.permission_id AND tg_group_1.group_id =
tg_group_permission_1.group_id(+) AND tg_group_1.group_id(+) =
tg_user_group_1.group_id AND tg_user.user_id =
tg_user_group_1.user_id(+)
INFO [sqlalchemy.engine.base.Engine.0x...8150] {'param_1': 1}
>>>
>>>
>>> #update something
>>> manager.password = 'changedpasswd'
>>> #now add a permission unless it already exists (which it does in this case)
>>> p = Permission()
>>> p.permission_name=u'manage'
>>>
>>> sp = transaction.savepoint()
INFO [sqlalchemy.engine.base.Engine.0x...8150] UPDATE tg_user SET
password=:password WHERE tg_user.user_id = :tg_user_user_id
INFO [sqlalchemy.engine.base.Engine.0x...8150] {'tg_user_user_id': 1,
'password':
'7e008eb76b967bf6fa56b54e39f393777203e28ebcfdcb0ba603ad6b226f71a49b31acea519d22d8'}
>>> DBSession.add(p)
>>> try:
... DBSession.flush()
... except IntegrityError:
... sp.rollback() # this should clear 'p' from the session, but
not touch manager
... else:
... sp.release()
...
INFO [sqlalchemy.engine.base.Engine.0x...8150] SAVEPOINT
sa_savepoint_1
INFO [sqlalchemy.engine.base.Engine.0x...8150] {}
INFO [sqlalchemy.engine.base.Engine.0x...8150] INSERT INTO
tg_permission (permission_id, permission_name, description) VALUES
(tg_permission_pk_seq.nextval, :permission_name, :description)
RETURNING tg_permission.permission_id INTO :ret_0
INFO [sqlalchemy.engine.base.Engine.0x...8150] {'description': None,
'permission_name': 'manage', 'ret_0': <cx_Oracle.NUMBER with value
None>}
INFO [sqlalchemy.engine.base.Engine.0x...8150] ROLLBACK TO SAVEPOINT
sa_savepoint_1
INFO [sqlalchemy.engine.base.Engine.0x...8150] {}
>>> #now, continue with working with manager...
>>> perms = manager.permissions # oops ... manager has been thrown away, but
>>> should not have been
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/home/rarch/trunk/src/appserver/pylotengine/model/auth.py",
line 158, in permissions
for g in self.groups:
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.6.0.4dev-py2.6-linux-x86_64.egg/sqlalchemy/orm/
attributes.py", line 159, in __get__
return self.impl.get(instance_state(instance),
instance_dict(instance))
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.6.0.4dev-py2.6-linux-x86_64.egg/sqlalchemy/orm/
attributes.py", line 377, in get
value = callable_(passive=passive)
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.6.0.4dev-py2.6-linux-x86_64.egg/sqlalchemy/orm/
strategies.py", line 578, in __call__
(mapperutil.state_str(state), self.key)
DetachedInstanceError: Parent instance <User at 0x19fa2490> is not
bound to a Session; lazy load operation of attribute 'groups' cannot
proceed
>>>
>>>
======================================
SQLAlchemy should already have removed those items from the session
which were added since the begin_nested() was issued when the
rollback() is called. Therefore, I believe we want to remove the
self.session.clear() [expunge_all()] statment from the
SessionSavepoint.rollback() method. I do not believe it is needed.
*********************************************************************
Laurence,
Thanks for looking into this. Let me know if I can help further.
Kent
--
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.
