For each begin_nested() call, a corresponding rollback() or commit() must be issued.
Kent
rollback() or commit() must be issued."
In PostgreSql, according to my understanding, if there is ever a
database exception, a rollback must be issued.
This means a main reason to issue a SAVEPOINT is as a hedge against an
error.
As database transactions go, I want this entire thing to be a single
transaction, so now I don't know how to continue...
For example,
DBSession.begin_nested() #savepoint
DBSession.add(obj)
try:
DBSession.flush()
except IntegrityError:
DBSession.rollback()
else:
# now what? I do not want to commit, i have much
# more work to do than this which should be part of
# this transaction, but if I don't commit now,
# i need to issue 2 commits later()??
Is releasing the savepoint a choice instead of rolling() or commit()?
Michael Bayer
commit() releases the savepoint, if thats whats going on contextually. It doesnt actually commit the outer transaction if you've last called begin_nested().
your block should be like:
session.begin_nested()
try:
...
session.flush()
session.commit()
except:
session.rollback()
Kent Bower
I failed to recognize I'm using the TurboGears foundation, which uses
zope & transaction:
Is there a way to still accomplish this?
>>> DBSession.begin_nested()
<sqlalchemy.orm.session.SessionTransaction object at 0xe9d5150>
>>>
>>> DBSession.commit()
Traceback (most recent call last):
File "<console>", line 1, in <module>
File
"/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.0.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/scoping.py",
line 129, in do
return getattr(self.registry(), name)(*args, **kwargs)
File
"/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.0.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py",
line 655, in commit
self.transaction.commit()
File
"/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.0.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py",
line 368, in commit
self._prepare_impl()
File
"/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.0.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py",
line 344, in _prepare_impl
ext.before_commit(self.session)
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
Michael Bayer
> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>
Kent
>
> >> session.begin_nested()
> >> try:
> >> ...
> >> session.flush()
> >> session.commit()
> >> except:
> >> session.rollback()
>
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?
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/
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
"""
Later, the transaction.commit() seems to free the savepoint:
>>> transaction.commit()
13:41:41,039 INFO [sqlalchemy.engine.base.Engine.0x...2c10] RELEASE
SAVEPOINT sa_savepoint_2
13:41:41,039 INFO [sqlalchemy.engine.base.Engine.0x...2c10] {}
13:41:41,040 INFO [sqlalchemy.engine.base.Engine.0x...2c10] COMMIT
>>>
However, by then I'll have hundreds/thousands of savepoints to release
since this is being called as part of data-takeon. I'd rather not
take up thousands of savepoint resources when I only need one.
So the second question(s) is:
* 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?
Thanks in advance.
Michael Bayer
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 ?
Kent Bower
>> * 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 ?
>
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
King Simon-NFHD78
[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
Kent Bower
> 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
Kent
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().
>
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):
"Transaction must be committed using the transaction manager"
SessionExtensions intentionally *even for nested transactions* which
are not performing a database commit? I assume the answer is "yes,
that is by design".
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?
Thanks,
Kent
Kent
Michael Bayer
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
Kent Bower
> 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 ?
>
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.
Michael Bayer
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.
Kent Bower
(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
Michael Bayer
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).
Kent Bower
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
Michael Bayer
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.