Eagerload is ignored if root object is already in session

[Milas Bowman]

I have a relationship defaulted to lazy='noload' and on specific queries override this using .options(eagerload()). However, if the object being queried is already loaded into the session, it will not perform the eager load if queried.

This is a regression in 1.0.9 -- it works properly in 1.0.8. I'm specifically seeing it in a unit test using SQLite in-memory database.

Here's a simple example (also attached):

from __future__ import print_function

from sqlalchemy import Column, Integer, ForeignKey, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import eagerload, relationship, sessionmaker

Base = declarative_base()
engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)


class Foo(Base):
    __tablename__ = 'foo'

    id = Column(Integer, primary_key=True)
    value = Column(String(50))
    bars = relationship('Bar', back_populates='foo', lazy='noload')


class Bar(Base):
    __tablename__ = 'bar'

    id = Column(Integer, primary_key=True)
    foo_id = Column(ForeignKey('foo.id'))
    foo = relationship('Foo', back_populates='bars', lazy='noload')


def initialize():
    Base.metadata.create_all(engine)

    session = Session()
    f = Foo(value='foobar')
    b = Bar(foo=f)
    session.add(b)
    session.commit()
    session.close()

if __name__ == '__main__':
    initialize()

    session = Session()

    bar = session.query(Bar).one()
    print('Foo should be None - ', bar.foo)

    bar_with_foo_query = session.query(Bar).options(eagerload(Bar.foo))

    bar_missing_foo = bar_with_foo_query.one()
    print('Foo is incorrectly None - ', bar_missing_foo.foo)

    # invalidate the session cache to force it to reload
    session.close()

    bar_with_foo = bar_with_foo_query.one()
    print('Foo is loaded correctly - ', bar_with_foo.foo)

Let me know if there's any additional information I can provide.

[Mike Bayer]

unfortunately it is 1.0.8 that has the regression against the 0.9 series, not 1.0.9 against 1.0.8 - the issue is #3510, and if you run your script under 0.9, it has the current behavior as well. eager loading only applies to unloaded relationships, and in this case "noload" means "never load", so it is considered "loaded". It's not intended to be a system of deferring loads. if you note the other issue you'll see the proposal for a "strict" loader that simply prevents lazyloading from proceeding with an exception (I'm assuming this is what you're looking for), this is issue #3512.

[Milas Bowman]

Thanks for the quick response!

I understand the mechanics of noload better after your explanation; I'll certainly need to be more careful in my usage with it. (Thankfully, I wouldn't foresee situations like this occurring frequently under normal usage patterns.)

I guess my ideal loader strategy is something between lazy and noload. I only want relationships loaded if I explicitly ask for them in the query, but I also expect a future eagerload to be respected even if it's for something already "loaded".

Furthermore, it'd be nice for session to maintain any relationships that happen to get loaded out of band. Take this example:

bar = session.query(Bar).one()
foo = session.query(Foo).filter(Foo.bars.any(id=bar.id)).one()

In the case of lazy=True, as soon as foo is loaded, bar.foo is populated with that instance, such that a future access of the property will not result in a query.

In the case of lazy='noload', when foo is loaded, the backref will not be populated, and bar.foo will be None still.

Anyway, thanks again for the clarification and apologies for the erroneous issue. :)

[Mike Bayer]

I guess my ideal loader strategy is something between lazy and noload. I only want relationships loaded if I explicitly ask for them in the query, but I also expect a future eagerload to be respected even if it's for something already "loaded".

sure, noload was added long ago before the loader system was very flexible so it is kind of mostly useless now, the thing you describe is like a lazyloader that I guess returns None? raises an error? but leaves the attribute unloaded, so that it is open to population by other loaders. there is no such loader strategy right now and it might require new mechanics to the attribute system to leave the status of the attribute as "unloaded" after the strategy proceeds.

In the case of lazy=True, as soon as foo is loaded, bar.foo is populated with that instance, such that a future access of the property will not result in a query.

That works now, but not in that way. Going around actually populating all the relationships that happen to refer to an object we just loaded is obviosuly infeasible since there could be tens of thousands of objects with such a relationship present. Instead, a many-to-one relationship always populates from the identity map upon lazy load so that no SQL is emitted.

In the case of lazy='noload', when foo is loaded, the backref will not be populated, and bar.foo will be None still.

yes because again the "noload" cancels the normal operation of the lazyloader.