<p dir="auto"><strong>Migrated issue, originally created by Anonymous</strong></p>
<h1 dir="auto">Use case for loading transient objects</h1>
<ul dir="auto">
<li>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.</li>
<li>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.
<ul dir="auto">
<li>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.</li>
<li>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.</li>
</ul>
</li>
</ul>
<h2 dir="auto">Discussion</h2>
<p dir="auto">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).</p>
<ul dir="auto">
<li>
<p dir="auto">Examples:</p>
<ul dir="auto">
<li>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.
<ul dir="auto">
<li>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.</li>
</ul>
</li>
<li>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:</li>
</ul>
<p dir="auto">line = OrderLine()<br>
line.productid = u'SKU3433'</p>
<h1 dir="auto">"lazy" load the product from the database for further business logic</h1>
<p dir="auto">line.product</p>
<ul dir="auto">
<li>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 <code class="notranslate">'order.store'</code> 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 <code class="notranslate">Order</code> mapped relationship <code class="notranslate">'store'</code>.</li>
</ul>
</li>
<li>
<p dir="auto">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.)</p>
</li>
<li>
<p dir="auto">This methodology has worked quite well for us for nearly a year of live use.</p>
</li>
</ul>
<h2 dir="auto">Detached instance relationship loading</h2>
<ul dir="auto">
<li>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.</li>
</ul>
<h2 dir="auto">See also</h2>
<ul dir="auto">
<li><a href="http://groups.google.com/group/sqlalchemy/browse_thread/thread/6367487e915d653" rel="nofollow">http://groups.google.com/group/sqlalchemy/browse_thread/thread/6367487e915d653</a></li>
<li><a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="384621455" data-permission-text="Title is private" data-url="https://github.com/sqlalchemy/sqlalchemy/issues/1910" data-hovercard-type="issue" data-hovercard-url="/sqlalchemy/sqlalchemy/issues/1910/hovercard" href="https://github.com/sqlalchemy/sqlalchemy/issues/1910">#1910</a></li>
<li><a href="http://groups.google.com/group/sqlalchemy/browse_thread/thread/e9b99d7120e63591" rel="nofollow">http://groups.google.com/group/sqlalchemy/browse_thread/thread/e9b99d7120e63591</a></li>
</ul>
<p dir="auto"><strong>Migrated issue, originally created by Anonymous</strong></p>
<p dir="auto">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.</p>
<p dir="auto">Here is the faulting code line, in latest development branch, in sqlalchemy/orm/session.py, line 1930:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="        except:
            transaction.rollback(_capture_exception=True)
            raise"><pre class="notranslate"><code class="notranslate">        except:
            transaction.rollback(_capture_exception=True)
            raise
</code></pre></div>
<p dir="auto">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 ?</p>
<p dir="auto">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):</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="File &quot;/home/pimsprod/PIMS/src/pims/processing/processor_abstract.py&quot;, line 213, in retrieve_user
    session.commit() # COMMIT SAVEPOINT
  File &quot;/home/pimsprod/PIMS/src/pims/database_tools.py&quot;, line 85, in commit
    super(SafeSessionClass, self).commit(*args, **kwargs)
  File &quot;/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py&quot;, line 645, in commit
    self.transaction.commit()
  File &quot;/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py&quot;, line 313, in commit
    self._prepare_impl()
  File &quot;/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py&quot;, line 297, in _prepare_impl
    self.session.flush()
  File &quot;/home/pimsprod/PIMS/src/pims/database_tools.py&quot;, line 77, in flush
    super(SafeSessionClass, self).flush(*args, **kwargs)
  File &quot;/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py&quot;, line 1547, in flush
    self._flush(objects)
  File &quot;/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py&quot;, line 1634, in _flush
    transaction.rollback(_capture_exception=True)
  File &quot;/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py&quot;, line 338, in rollback
    transaction._rollback_impl()
  File &quot;/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py&quot;, line 356, in _rollback_impl
    t[1](1).rollback()
  File &quot;/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py&quot;, line 1960, in rollback
    self._do_rollback()
  File &quot;/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py&quot;, line 2020, in _do_rollback
    self._savepoint, self._parent)
  File &quot;/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py&quot;, line 1249, in _rollback_to_savepoint_impl
    self.engine.dialect.do_rollback_to_savepoint(self, name)
  File &quot;/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py&quot;, line 321, in do_rollback_to_savepoint
    connection.execute(expression.RollbackToSavepointClause(name))
  File &quot;/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py&quot;, line 1399, in execute
    params)
  File &quot;/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py&quot;, line 1532, in _execute_clauseelement
    compiled_sql, distilled_params
  File &quot;/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py&quot;, line 1640, in _execute_context
    context)
  File &quot;/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py&quot;, line 1633, in _execute_context
    context)
  File &quot;/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py&quot;, line 330, in do_execute
    cursor.execute(statement, parameters)
  File &quot;/usr/local/lib/python2.7/site-packages/MySQLdb/cursors.py&quot;, line 174, in execute
    self.errorhandler(self, exc, value)
  File &quot;/usr/local/lib/python2.7/site-packages/MySQLdb/connections.py&quot;, line 36, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (OperationalError) (1305, 'SAVEPOINT sa_savepoint_1 does not exist') 'ROLLBACK TO SAVEPOINT sa_savepoint_1' ()"><pre class="notranslate"><code class="notranslate">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' ()
</code></pre></div>