Instance <...> is not bound to a Session while using before_flush/after_flush

[Roman Dubina]

This is a floating bug and I can't tell for sure why it occurs. I will show you example. Here is my code:

class Order(BaseMixin, Base):
    __tablename__ = 'orders'

    id = Column(BigInteger, nullable=False, primary_key=True, autoincrement=True)
    name = Column(Unicode, nullable=False)
    __table_args__ = ({'implicit_returning': False})    # need this because of trigger

@event.listens_for(Session, 'before_flush')
def order_before_flush(session, flush_context, instances):
    for obj in list(session.new) + list(session.dirty):
        obj.name = obj.name + u'whatever'

event.listen(Order.__table__, 'after_create', DDL("""CREATE OR REPLACE FUNCTION orders_insert_trigger()
      RETURNS trigger AS
    $BODY$
    __SOME_TRIGGER_CODE__
    RETURN NULL;
    END;
    $BODY$
      LANGUAGE plpgsql VOLATILE
      COST 100;

    CREATE TRIGGER insert_orders_trigger
      BEFORE INSERT
      ON orders
      FOR EACH ROW
      EXECUTE PROCEDURE orders_insert_trigger();"""))

So when I do something like that:

order = Order()
order.name = u'test name'
Session.add(order)
Session.commit()

Sometimes after commit I get error: DetachedInstanceError: Instance <Order at 0x813495a50> is not bound to a Session; attribute refresh operation cannot proceed

I thought it was because of the trigger but if I just remove event everything works fine all the time.

[Mike Bayer]

hi - full stack trace please? that would help.

[Roman Dubina]

Sorry, forgot.

--------------------------------------------------------------------------------
Traceback (most recent call last):
  File "/order.py", line 69, in create_order
    **extra_args)
  File "/order.py", line 152, in create_order
    if not order.id:
  File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.9.9-py2.7-freebsd-10.1-RELEASE-amd64.egg/sqlalchemy/orm/attributes.py", line 239, in __get__
    return self.impl.get(instance_state(instance), dict_)
  File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.9.9-py2.7-freebsd-10.1-RELEASE-amd64.egg/sqlalchemy/orm/attributes.py", line 589, in get
    value = callable_(state, passive)
  File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.9.9-py2.7-freebsd-10.1-RELEASE-amd64.egg/sqlalchemy/orm/state.py", line 433, in __call__
    self.manager.deferred_scalar_loader(self, toload)
  File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.9.9-py2.7-freebsd-10.1-RELEASE-amd64.egg/sqlalchemy/orm/loading.py", line 567, in load_scalar_attributes
    (state_str(state)))
DetachedInstanceError: Instance <Order at 0x813495a50> is not bound to a Session; attribute refresh operation cannot proceed

[Roman Dubina]

Sorry I was not clear. I'm not getting any error on commit, but the order is not saved and exception occurs when I'm trying to get id. Btw is it safe to call Session.Begin_nested() in this events?

[Mike Bayer]

begin_nested() in a before_flush(). Hmm... it's definitely risky, when are you closing the scope of that transaction?

There's nothing I can do here without a script that at least sometimes shows the error. the things you have above are not going to cause any problem like this. I can imagine maybe a trigger affecting the primary key of something, but that wouldn't "detach" the whole object. If the session persisted an object and then didn't get a new PK back, it would complain loudly about that as well. It's not possible for it to silently fail to send an object to the persistent state, unless something in your before_flush() was dumping out the object. But even then the "Detached" error is not the error you'd be getting.

What happens if you disable just the trigger? Can you produce a script that has all the pieces in place so I can run it?

[Roman Dubina]

Tried all day to reproduce this error to appear permanently, but no luck still. I've noticed that sequence were incremented, user statistics modified(my other table), but object is not inserted in DB and that happened even in after_flush event which according to docs is called after flush but before commit. My algorithm is next: in the after_flush event I calculate stats with nested session(closing it in the same scope) and change some "Order" fields. But the main question is why object still has no PK in the after_flush event?

[Mike Bayer]

it sort of sounds like the object that is to be flushed is not in the session at all when the flush occurs.

[Roman Dubina]

Yeah, looks like that, but I honestly do

Session.add(order)
Session.commit()

[Mike Bayer]

:) well you need to unwrap it from your application into something that can be poked at :/. are you familiar with pdb ? I'd step in and see where the object is getting dumped.

[Mike Bayer]

can't do anything with this for now, feel free to post more detail when you have it!