DetachedInstanceError

[Michael Hipp]

I'm obviously missing some key concept as regards the management of sessions.
This seemingly simple usage fails:

def get_new():
sess = Session()
new = Something() # new orm object
sess.add(new)
sess.commit()
sess.close()
return new

new = get_new() # request a new Something
print new
print new.id

Those last 2 print lines throw:

DetachedInstanceError: Instance <Something at 0x2873ed0> is not bound to
a Session; attribute refresh operation cannot proceed

I seem to keep butting heads with the session needing to be a global eternal
thing (opposite what the docs recommend). I could create another session and
add 'new' to it, but that seems like a lot of boilerplate when all I wanted to
do was get a bit of info from the returned object.

Can someone explain how this is supposed to be done?

Thanks,
Michael

[Michael Bayer]

On Aug 14, 2010, at 12:53 PM, Michael Hipp wrote:

I'm obviously missing some key concept as regards the management of sessions. This seemingly simple usage fails:


 def get_new():
   sess = Session()
   new = Something()  # new orm object
   sess.add(new)
   sess.commit()
   sess.close()
   return new

 new = get_new()  # request a new Something
 print new
 print new.id

Those last 2 print lines throw:

 DetachedInstanceError: Instance <Something at 0x2873ed0> is not bound to
 a Session; attribute refresh operation cannot proceed

I seem to keep butting heads with the session needing to be a global eternal thing (opposite what the docs recommend).

heh....no, the session is completely ad hoc.  What you're missing is that the objects associated with the session should also in most situations be treated as ad-hoc - they represent the state of data within a particular transaction.   

If you use them outside of a transaction, and not associated  with a session that would otherwise have the ability to associate them with a transaction, they are considered to be "detached".  "detached" is described at:

http://www.sqlalchemy.org/docs/session.html#quickie-intro-to-object-states

Where you'll note that "expired" attributes cannot be loaded back from the database.

Why are they expired ?  Let's look at commit():

http://www.sqlalchemy.org/docs/session.html#committing

Second paragraph.  "Another behavior of commit() is that by default it expires the state of all instances present after the commit is complete".  

Why does it do this ?   Well, when we have a detached object, and there's no transaction going on (i.e. no connection that can query the DB), we know nothing about what is in the database at that point, so all state on the object is expired.   After all, if it had id =12, but some other transaction has deleted row 12, that object is invalid.   Without a transaction associated, it would be wrong for us to tell you otherwise.  Because we don't know.

Now lets assume you don't like this behavior, and your application is just set of operations at a time and nobody else is updating your row (assumptions SQLAlchemy has chosen not to make).  Fine.   Turn off expire_on_commit.    Then when you detach your objects, all their attributes are still present, and you can access them freely.

So what if we made this the default.  What kinds of complaints, which btw we never get anymore, would we have then ?   Well, we'd have (and we had, all the time) this complaint:

sess1 = Session()

x1 = sess1.query(X).first()

x1.foo = 'bar'

sess1.commit()

sess2 = Session()

x2 = sess2.query(X).first()

x2.foo = 'bat'

sess2.commit()

# x1 is still present in the Session's identity map

x1 = sess1.query(X).first()

assert x1.foo == 'bat' # ugh SQLALCHEMY IS BROKEN !

so we default to the more "transaction hugging" behavior by default - where the error message you get is at least very straightforward, instead of a subtle effect like this one.

Can someone explain how this is supposed to be done?

When you work with mapped objects, you're working with your database.   A Session() should be in place and a transaction is in progress.    Its only if you want to store mapped objects in some kind of offline cache, or pass them to other usage contexts, that you'd want to keep "detached" objects around.   And when you go to use a "detached" object, you put it back into a context where it again is a proxy to some ongoing database operation, i.e. put it in the session for the current operation - often this transfer of state is done via merge(), so that if the destination session already has the object in question present, it will reconcile the incoming state with what it already has.   The "load=False" setting of merge() prevents the usage of a SELECT from loading existing state, if you are working with long term immutable data and don't want the extra SELECT emitted.

Alternatively, if you really want to pass around detached objects and make use of their detached state, even though that state may be stale or even deleted vs. what's in the database, you can disable expire_on_commit - if you are making ad-hoc sessions for usage in single functions, just pass it to that specific session constructor.

[Michael Hipp]

Michael

Thanks for taking the time to formulate a very thorough answer. (Now if I can
make my understanding be as thorough.)

If you could suffer me one more question ... it appears there are two* ways to
handle this inside a method that may not know where it's called from.

def alternative1(thing):
sess = Session()
sess.merge(thing)
thing.name = "Foo"
sess.commit()
sess.close()

def alternative2(thing, sess=None):
if sess is None:
sess = Session()
sess.merge(thing)
thing.name = "Foo"
sess.commit() # incomplete, must do sess.close()

Am I getting anywhere close? Can either one be said to be better?

Again, thanks.
Michael

* For now, I'm taking it as an article of faith that I should stay away from
expire_on_commit at least until I better understand the implications.

> <http://www.sqlalchemy.org/docs/reference/orm/sessions.html#sqlalchemy.orm.session.Session.commit>

[Michael Bayer]

On Aug 14, 2010, at 3:00 PM, Michael Hipp wrote:

> Michael
>
> Thanks for taking the time to formulate a very thorough answer. (Now if I can make my understanding be as thorough.)
>
> If you could suffer me one more question ... it appears there are two* ways to handle this inside a method that may not know where it's called from.
>
> def alternative1(thing):
> sess = Session()
> sess.merge(thing)
> thing.name = "Foo"
> sess.commit()
> sess.close()
>
> def alternative2(thing, sess=None):
> if sess is None:
> sess = Session()
> sess.merge(thing)
> thing.name = "Foo"
> sess.commit() # incomplete, must do sess.close()
>
> Am I getting anywhere close? Can either one be said to be better?

If you're looking for that approach, it is usually:

from sqlalchemy.orm import object_session

def foo(thing):
session = object_session(thing)
if not session:
local_sess = Session(expire_on_commit=False)
local_sess.add(thing)
thing.name = 'foo'
if not session:
local_sess.commit()

What we've done above is, if the "thing" is already part of a session, we don't assume to know what the state of the transaction is - we don't commit it. If it was detached, and we made our own session, then we committed it.

You can also make a decorator that does the same:

import decorator # pypi package

@decorator
def force_a_session(fn, item):
session = object_session(item)
if not session:
local_sess = Session(expire_on_commit=False)
local_sess.add(item)
try:
try:
return fn(item)
finally:
if not session:
local_sess.commit()
except:
if not session:
local_sess.rollback()
raise

The approach above may be fine for your needs but I wouldn't encourage it. The demarcation of transaction boundaries shouldn't be an ad-hoc thing IMO and granular functions shouldn't be deciding whether or not they are setting up a transaction.

> --
> 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.
>

[Michael Hipp]

On 8/14/2010 2:29 PM, Michael Bayer wrote:
> The approach above may be fine for your needs but I wouldn't encourage it. The demarcation of transaction boundaries shouldn't be an ad-hoc thing IMO and granular functions shouldn't be deciding whether or not they are setting up a transaction.

Thanks. Yes, I was beginning to suspect such. Makes more sense to manage the
session and commit/rollback issues at the top of the call stack. I was trying
too hard to not have to pass the session down in argument lists, but looks like
I should.

Thanks,
Michael

[Michael Bayer]

well, you can either call object_session() in the methods to get the current object's session, or set up a registry like scoped_session that you access globally - I'd try to avoid having to pass the session around too.