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 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 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/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 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 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.
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 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.
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 "", line 1, in 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.
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.
* 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.
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.