Re: [sqlalchemy] Re: For each begin_nested() call, a corresponding rollback() or commit() must be issued.
On 5/30/2010 1:24 PM, Michael Bayer wrote: On May 28, 2010, at 1:46 PM, Kent Bower wrote: On 5/28/2010 10:08 AM, Michael Bayer wrote: Is the pattern that you want to keep re-issuing a savepoint repeatedly using the same name ? Does that have some different usage of resources versus issuing/closing distinct savepoints with different names ? As an aside, since oracle apparently has no 'release savepoint', I imagine the answer to your second question might be: yes, when using oracle with thousands of potential savepoints.I think I've worked around this for now, so I'm not asking you to necessarily do anything about it, but it might be somewhat of a minor enhancement at some point. Thanks again. just so we're on the same page, oracle has no release, so if we are doing this: begin_nested() commit() begin_nested() commit() begin_nested() commit() the ora conversation is SAVEPOINT x SAVEPOIINT y SAVEPOINT z i.e. cheaper to reuse the same savepoint since we aren't rolling back to y or x. in engine/base.py, seems like we would add logic to the __savepoint_seq counter to achieve this. I think that is what I was driving at. May/should be better with oracle (though I admit to not knowing how expensive a savepoint is, certainly if you've got thousands outstanding it would have been better to reissue the same one under the circumstances that it had already been 'released' with commit()). Although sqla doesn't allow the user to specify the savepoint name, the same could be accomplished given if support for the following were implemented: Let me ask: sp_a=begin_nested() ... ... sp_b=begin_nested() ... ... sp_c=begin_nested() ... ... #realize I need to go back to sp_a... sp_a.rollback() My understanding from the docs, is this is no good currently. I need to first do commit(), commit() and then rollback() or three rollback()s, correct? Again, I'm not requesting an enhancement, but would make the point that it would be more *intuitive* for sqla to figure that out for you, so you could arbitrarily say sp_a.rollback() and it knows to release or rollback all the nested transactions living under sp_a. 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.
Re: [sqlalchemy] Re: For each begin_nested() call, a corresponding rollback() or commit() must be issued.
On May 31, 2010, at 8:24 AM, Kent Bower wrote: Although sqla doesn't allow the user to specify the savepoint name, the same could be accomplished given if support for the following were implemented: Let me ask: sp_a=begin_nested() ... ... sp_b=begin_nested() ... ... sp_c=begin_nested() ... ... #realize I need to go back to sp_a... sp_a.rollback() My understanding from the docs, is this is no good currently. I need to first do commit(), commit() and then rollback() or three rollback()s, correct? Again, I'm not requesting an enhancement, but would make the point that it would be more *intuitive* for sqla to figure that out for you, so you could arbitrarily say sp_a.rollback() and it knows to release or rollback all the nested transactions living under sp_a. that's how the engine-level API works - you get at a Transaction object that you can roll back anywhere in the chain (its up to you to know that the other Transaction objects in the middle are no longer valid).In the ORM we wanted to keep things more simplistic. I would note that begin() and begin_nested() do return the SessionTransaction object though, which does itself include commit() and rollback() methods. You might want to try the above pattern with it, as it might accomplish this already ( I didn't write the SessionTransaction accounting logic). -- 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.
Re: [sqlalchemy] Re: For each begin_nested() call, a corresponding rollback() or commit() must be issued.
On 5/31/2010 9:55 AM, Michael Bayer wrote: On May 31, 2010, at 8:24 AM, Kent Bower wrote: Although sqla doesn't allow the user to specify the savepoint name, the same could be accomplished given if support for the following were implemented: Let me ask: sp_a=begin_nested() ... ... sp_b=begin_nested() ... ... sp_c=begin_nested() ... ... #realize I need to go back to sp_a... sp_a.rollback() My understanding from the docs, is this is no good currently. I need to first do commit(), commit() and then rollback() or three rollback()s, correct? Again, I'm not requesting an enhancement, but would make the point that it would be more *intuitive* for sqla to figure that out for you, so you could arbitrarily say sp_a.rollback() and it knows to release or rollback all the nested transactions living under sp_a. that's how the engine-level API works - you get at a Transaction object that you can roll back anywhere in the chain (its up to you to know that the other Transaction objects in the middle are no longer valid).In the ORM we wanted to keep things more simplistic. I would note that begin() and begin_nested() do return the SessionTransaction object though, which does itself include commit() and rollback() methods. You might want to try the above pattern with it, as it might accomplish this already ( I didn't write the SessionTransaction accounting logic). Ok, good news, that is already taken care of properly, as you suspected. Both the database rollback to the correct savepoint is issued and also the session data contains the correct data from before sp_a. This statement in the docs is what lead me to believe this is not implemented: For each begin_nested() call, a corresponding rollback() or commit() must be issued. (http://www.sqlalchemy.org/docs/session.html) That is misleading, I guess, maybe there is a better way to state that requirement? (I didn't need to issue a rollback or commit() for each begin_nested(), I am allowed to skip back to a previous one, very nice!) Thanks -- 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.
Re: [sqlalchemy] Re: For each begin_nested() call, a corresponding rollback() or commit() must be issued.
On May 31, 2010, at 10:28 AM, Kent Bower wrote: that's how the engine-level API works - you get at a Transaction object that you can roll back anywhere in the chain (its up to you to know that the other Transaction objects in the middle are no longer valid).In the ORM we wanted to keep things more simplistic. I would note that begin() and begin_nested() do return the SessionTransaction object though, which does itself include commit() and rollback() methods. You might want to try the above pattern with it, as it might accomplish this already ( I didn't write the SessionTransaction accounting logic). Ok, good news, that is already taken care of properly, as you suspected. Both the database rollback to the correct savepoint is issued and also the session data contains the correct data from before sp_a. This statement in the docs is what lead me to believe this is not implemented: For each begin_nested() call, a corresponding rollback() or commit() must be issued. (http://www.sqlalchemy.org/docs/session.html) That is misleading, I guess, maybe there is a better way to state that requirement? (I didn't need to issue a rollback or commit() for each begin_nested(), I am allowed to skip back to a previous one, very nice!) See, I didn't know that it actually worked, we don't have any coverage for that. I'd rather get some coverage in orm/test_transaction.py before I start advertising that functionality. -- 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.
Re: [sqlalchemy] Re: For each begin_nested() call, a corresponding rollback() or commit() must be issued.
On May 28, 2010, at 1:46 PM, Kent Bower wrote: On 5/28/2010 10:08 AM, Michael Bayer wrote: Is the pattern that you want to keep re-issuing a savepoint repeatedly using the same name ? Does that have some different usage of resources versus issuing/closing distinct savepoints with different names ? As an aside, since oracle apparently has no 'release savepoint', I imagine the answer to your second question might be: yes, when using oracle with thousands of potential savepoints.I think I've worked around this for now, so I'm not asking you to necessarily do anything about it, but it might be somewhat of a minor enhancement at some point. Thanks again. just so we're on the same page, oracle has no release, so if we are doing this: begin_nested() commit() begin_nested() commit() begin_nested() commit() the ora conversation is SAVEPOINT x SAVEPOIINT y SAVEPOINT z i.e. cheaper to reuse the same savepoint since we aren't rolling back to y or x. in engine/base.py, seems like we would add logic to the __savepoint_seq counter to achieve this. -- 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.
Re: [sqlalchemy] Re: For each begin_nested() call, a corresponding rollback() or commit() must be issued.
On May 28, 2010, at 8:47 AM, Kent wrote: Say I have this: session().begin_nested() try: session.add(obj) session.flush() session.commit() except: session.rollback() ... transaction.commit() 2 questions: * I assume that the session.rollback() undoes the add(obj) - obj is again transient? yes Also, this seems to give me the behavior I'd like, except that the session.commit() pukes due to zope, saying: File /home/rarch/tg2env/lib/python2.6/site-packages/ zope.sqlalchemy-0.4-py2.6.egg/zope/sqlalchemy/datamanager.py, line 201, in before_commit assert zope_transaction.get().status == 'Committing', Transaction must be committed using the transaction manager AssertionError: Transaction must be committed using the transaction manager. *shrugs* Later, the transaction.commit() seems to free the savepoint: I don't know what transaction here is, again if thats zope/tg/whatever it might be automating that process, sure. * Is there a way besides session.commit() to free the savepoint resource? Is there a way to provide the savepoint name, so I can use the same name over? Lastly, if you aren't the expert, where would you point me, zope group or TG group? you could issue the SAVEPOINT instructions manually if you don't like SQLA's abstraction of it, although then you don't get the Session's object management behavior around those savepoints (though it can be approximated). I don't really understand what you're trying to achieve, such that SQLA is forcing you to create thousands of savepoints when you only need one. Is the pattern that you want to keep re-issuing a savepoint repeatedly using the same name ? Does that have some different usage of resources versus issuing/closing distinct savepoints with different names ? -- 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.
Re: [sqlalchemy] Re: For each begin_nested() call, a corresponding rollback() or commit() must be issued.
* Is there a way besides session.commit() to free the savepoint resource? Is there a way to provide the savepoint name, so I can use the same name over? Lastly, if you aren't the expert, where would you point me, zope group or TG group? you could issue the SAVEPOINT instructions manually if you don't like SQLA's abstraction of it, although then you don't get the Session's object management behavior around those savepoints (though it can be approximated). Yeah, I've considered it. I don't really understand what you're trying to achieve, such that SQLA is forcing you to create thousands of savepoints when you only need one. Is the pattern that you want to keep re-issuing a savepoint repeatedly using the same name ? Does that have some different usage of resources versus issuing/closing distinct savepoints with different names ? I'm fine with how SQLA is designed, it isn't really a SQLA issue, I was just appealing to you to see if you could think of a workaround I believe the problem is in the framework tools we are using, whether it is Zope or TG. (I've posted to zope group now to see if they intended to support savepoints and how, etc.). Since that framework won't allow me to issue the command session.commit(), I cannot release the savepoints until the zope transaction commits, and by then I'm getting a python max recursion problem because there are so many outstanding savepoints for it to release. So where I thought you could help is if there is another (even non-public) way to release the savepoint without calling session.commit()? Ultimately I understand now that this is not a SQLA issue. Thanks -- 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.
RE: [sqlalchemy] Re: For each begin_nested() call, a corresponding rollback() or commit() must be issued.
Kent wrote: [SNIP] I'm fine with how SQLA is designed, it isn't really a SQLA issue, I was just appealing to you to see if you could think of a workaround I believe the problem is in the framework tools we are using, whether it is Zope or TG. (I've posted to zope group now to see if they intended to support savepoints and how, etc.). Since that framework won't allow me to issue the command session.commit(), I cannot release the savepoints until the zope transaction commits, and by then I'm getting a python max recursion problem because there are so many outstanding savepoints for it to release. From a quick reading of the 'transaction' package source, it looks like you should be able to create savepoints and roll them back something like this: savepoint = transaction.savepoint() try: # ... except: savepoint.rollback() raise I've no idea if that will confuse SA's accounting mechanisms though - I imagine you'd probably need to clean up some objects in the session. If you have a look at the tg.configuration module, the transaction middleware is added based on the config.use_transaction_manager value, so you could set that to False and implement whatever transaction management features you want in your own middleware. Hope that helps, Simon -- 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.
Re: [sqlalchemy] Re: For each begin_nested() call, a corresponding rollback() or commit() must be issued.
From a quick reading of the 'transaction' package source, it looks like
you should be able to create savepoints and roll them back something
like this:
savepoint = transaction.savepoint()
try:
# ...
except:
savepoint.rollback()
raise
Thanks for the interest in helping. I had already gone down that road
and abandoned it when I realized it wasn't playing nicely with
sqlalchemy session:
a=TranCode()
a.transactioncodeid='PMT'
sp=transaction.savepoint()
DBSession.add(a)
DBSession.flush()
15:53:52,852 INFO [sqlalchemy.engine.base.Engine.0x...88d0] SAVEPOINT
sa_savepoint_1
15:53:52,853 INFO [sqlalchemy.engine.base.Engine.0x...88d0] {}
/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.0.2-py2.6-linux-x86_64.egg/sqlalchemy/engine/default.py:500:
SAWarning: Unicode type received non-unicode bind param value 'PMT'
param[key.encode(encoding)] = processors[key](compiled_params[key])
15:53:52,855 INFO [sqlalchemy.engine.base.Engine.0x...88d0] INSERT INTO
trancodes (transactioncodeid, description, debitorcredit, posttogl,
userdefined, codetype) VALUES (:transactioncodeid, :description,
:debitorcredit, :posttogl, :userdefined, :codetype)
15:53:52,855 INFO [sqlalchemy.engine.base.Engine.0x...88d0]
{'description': None, 'userdefined': None, 'transactioncodeid': 'PMT',
'codetype': None, 'debitorcredit': None, 'posttogl': None}
after flush!!
sp.rollback()
15:53:52,859 INFO [sqlalchemy.engine.base.Engine.0x...88d0] ROLLBACK TO
SAVEPOINT sa_savepoint_1
15:53:52,860 INFO [sqlalchemy.engine.base.Engine.0x...88d0] {}
Traceback (most recent call last):
File console, line 1, in module
File
/home/rarch/tg2env/lib/python2.6/site-packages/transaction-1.0a1-py2.6.egg/transaction/_transaction.py,
line 662, in rollback
transaction._saveAndRaiseCommitishError() # reraises!
File
/home/rarch/tg2env/lib/python2.6/site-packages/transaction-1.0a1-py2.6.egg/transaction/_transaction.py,
line 659, in rollback
savepoint.rollback()
File
/home/rarch/tg2env/lib/python2.6/site-packages/zope.sqlalchemy-0.4-py2.6.egg/zope/sqlalchemy/datamanager.py,
line 147, in rollback
self.session.clear() # remove when Session.rollback does an
attribute_manager.rollback
AttributeError: 'Session' object has no attribute 'clear'
Apparently I need to look into whether zope has a SQLA 0.6.0 compatible release
yet...looks to be trying to call session.clear
--
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.
Re: [sqlalchemy] Re: For each begin_nested() call, a corresponding rollback() or commit() must be issued.
On May 28, 2010, at 1:24 PM, Kent wrote: On May 27, 6:39 pm, Michael Bayer mike...@zzzcomputing.com wrote: commit() releases the savepoint, if thats whats going on contextually. It doesnt actually commit the outer transaction if you've last called begin_nested(). In a SessionExtension, 'before_commit' is called for nested transactions, which do not actually perform a database commit, as well as the RootTransaction, which does issue a COMMIT. I believe the zope guys overlooked this when they wrote their 'def before_commit()' method of their SessionExtension, and I am going to attempt to fix this. def before_commit(self, session): assert zope_transaction.get().status == 'Committing', Transaction must be committed using the transaction manager So my question is, first, do you call before_commit() on SessionExtensions intentionally *even for nested transactions* which are not performing a database commit? I assume the answer is yes, that is by design. it is by design, yes. you ultimately need to look at the transaction itself if you want to determine if its the real commit. the entire session conversation could be nested inside of a larger begin/commit external to the session, for example (I just illustrated a unit test recipe that uses this technique if you search around yesterday's messages). Which leads to this question: Inside a SessionExtension's before_commit() method, how can I work out whether this is a nested transaction vs. the root transaction? you'd look at session.transaction for details. to get the engine.Transaction object you'd need to dig into its _connections dictionary, but for most purposes you can probably just look at transaction.nested. Thanks, 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. -- 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.
Re: [sqlalchemy] Re: For each begin_nested() call, a corresponding rollback() or commit() must be issued.
On 5/28/2010 10:08 AM, Michael Bayer wrote: Is the pattern that you want to keep re-issuing a savepoint repeatedly using the same name ? Does that have some different usage of resources versus issuing/closing distinct savepoints with different names ? As an aside, since oracle apparently has no 'release savepoint', I imagine the answer to your second question might be: yes, when using oracle with thousands of potential savepoints.I think I've worked around this for now, so I'm not asking you to necessarily do anything about it, but it might be somewhat of a minor enhancement at some point. Thanks again. -- 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.
