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.