Issue 1:
Migrated issue, originally created by Anonymous

Use case for loading transient objects

Your server framework environment only ever has one session. (For example, TurboGears framework, using ScopedSession.) Or you can readily determine to which session a transient instance would belong.

You build up simple or complex sqlalchemy transient instances with related instances and collections all fleshed out before actually adding the instance (with its relations) to the database.

In our case, this happens because the objects are being build up client side, in memory, with the desirable side effect that the server side can be stateless. The client keeps a copy of this transient object. While the object and its relationships are being built up (Order with Customer and OrderLines with their Products, etc.) the client often serializes the object (with its relationships) and sends it to the server, who deserializes it, translates it into transient objects that it can work with (applying business logic, loading relationships, etc), and sends those changes back to the client.

Only when the client side user is happy to save the changes does he click "Save" and then, this time after the server has deserialized and reconstructed these transient objects, it actually adds them to the session (merges) and they become pending/persistent objects.

Discussion

It is often the case that a related record exists in the database that would be useful to inspect (without the restriction that it be part of the session yet).

Examples:

The ZipCode records with their relationships containing tax information, shipping information, etc. are already in the database. You are building a customer instance where the zipcode foreign key is known.

My new customer instance would fail if I tried to add it to the database at the moment because I haven't even provided all the required (non nullable) fields. Still, given that I know my session, I should be able to load this transient customer's zipcode related object and all its related collections so I can calculate tax/shipping as soon as I know the zipcode.

An OrderLine already knows the productid, the foreign key to the Product object the server would like to load, which must already exist in the database. From there, we can find prices, related items, substitutions, etc naturally through the transient object's already defined relationships:

line = OrderLine()

line.productid = u'SKU3433'

"lazy" load the product from the database for further business logic

line.product

You are writing an Order that does not yet exist in the database, but you know the store it is being written from. Your server logic needs to reference that Store's instance 'order.store' regardless of whether this order is transient and not yet saved or persistent and being modified. Either way, you would like to refer to the related object through the Order mapped relationship 'store'.

It is extremely beneficial for the server code to be able to reference relationships and apply business logic, agnostic of whether the instance is currently transient or pending or persistent (or detached for some cases). (The record's ''session status'' rarely affects ''business logic'' needed.)

This methodology has worked quite well for us for nearly a year of live use.

Detached instance relationship loading

E.g. you still need to calculate changes, run business logic, etc for a record that you are forbidden to change in the database (think archived records which can't change for accounting reasons) so you expunge() it, calculate the deltas you need and record them elsewhere.

See also

http://groups.google.com/group/sqlalchemy/browse_thread/thread/6367487e915d653

#1910

http://groups.google.com/group/sqlalchemy/browse_thread/thread/e9b99d7120e63591

Issue 2:
Migrated issue, originally created by Anonymous

We got weird ("(OperationalError) (1305, 'SAVEPOINT sa_savepoint_1 does not exist') in our prod, and after investigation, this is due to the fact that our mysql server fails a commit (we don't know why), and the handling of that exception in sqlalchemy leads to an automatic rollback to last savepoint. But that auto-rollback fails too (probably because mysql has already automatically rolled back by itself, and so destroyed savepoints), and thus the new exception "SAVEPOINT does not exist" hides the original error that caused all that trouble.

Here is the faulting code line, in latest development branch, in sqlalchemy/orm/session.py, line 1930:

except:
transaction.rollback(_capture_exception=True)
raise

Since we don't know the actual state of the mysql transaction at that moment, I guess that this automatic rollback should be protected by a try...except, so that if it fails, it's the original error that will be reraised in any case. Or shouldn't it ?

Here is the full traceback we got in prod (with sqlalchemy 0.7.3, but the faulty lines are still present in latest sqlalchemy versions):

File "/home/pimsprod/PIMS/src/pims/processing/processor_abstract.py", line 213, in retrieve_user
session.commit() # COMMIT SAVEPOINT
File "/home/pimsprod/PIMS/src/pims/database_tools.py", line 85, in commit
super(SafeSessionClass, self).commit(*args, **kwargs)
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 645, in commit
self.transaction.commit()
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 313, in commit
self._prepare_impl()
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 297, in _prepare_impl
self.session.flush()
File "/home/pimsprod/PIMS/src/pims/database_tools.py", line 77, in flush
super(SafeSessionClass, self).flush(*args, **kwargs)
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1547, in flush
self._flush(objects)
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1634, in _flush
transaction.rollback(_capture_exception=True)
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 338, in rollback
transaction._rollback_impl()
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 356, in _rollback_impl
t[1](1).rollback()
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1960, in rollback
self._do_rollback()
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2020, in _do_rollback
self._savepoint, self._parent)
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1249, in _rollback_to_savepoint_impl
self.engine.dialect.do_rollback_to_savepoint(self, name)
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 321, in do_rollback_to_savepoint
connection.execute(expression.RollbackToSavepointClause(name))
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1399, in execute
params)
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1532, in _execute_clauseelement
compiled_sql, distilled_params
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1640, in _execute_context
context)
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1633, in _execute_context
context)
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 330, in do_execute
cursor.execute(statement, parameters)
File "/usr/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 174, in execute
self.errorhandler(self, exc, value)
File "/usr/local/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
raise errorclass, errorvalue
OperationalError: (OperationalError) (1305, 'SAVEPOINT sa_savepoint_1 does not exist') 'ROLLBACK TO SAVEPOINT sa_savepoint_1' ()