AsyncSession refresh throws exception

[ljiatu]

The following code throws an exception at line await session_2.refresh(obj_a_refetched). Is this the expected behavior? I was assuming that since I eager loaded A.bs in the previous query, refresh would also eager load it. But maybe not?

import asyncio

from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import backref, relationship, selectinload, sessionmaker

Base = declarative_base()


class B(Base):
    __tablename__ = "bs"

    id = Column(Integer, primary_key=True)
    a_id = Column(
        Integer,
        ForeignKey("as.id", onupdate="CASCADE", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )


class A(Base):
    __tablename__ = "as"

    id = Column(Integer, primary_key=True)
    data = Column(String)
    bs = relationship(
        "B",
        backref=backref("a", lazy="raise"),
        cascade="save-update, merge, delete, delete-orphan",
        passive_deletes=True,
        lazy="raise",
    )


async def async_main():
    engine = create_async_engine(
        "postgresql+asyncpg://localhost/platform_dev",
        echo=True,
    )

    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.drop_all)
        await conn.run_sync(Base.metadata.create_all)

    session_maker = sessionmaker(
        engine, class_=AsyncSession, expire_on_commit=False
    )

    async with session_maker() as session:
        obj_a = A(id=1, data="A")
        async with session.begin():
            session.add(obj_a)

    async with session_maker() as session_2:
        result = await session_2.execute(select(A).where(A.id == obj_a.id).options(selectinload(A.bs)))
        obj_a_refetched = result.scalar_one()
        await session_2.refresh(obj_a_refetched)

        print("-" * 80)
        print(obj_a_refetched.data)
        print("-" * 80)

    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.drop_all)


asyncio.run(async_main())

Exception stacktrace:

Traceback (most recent call last):
  File "script.py", line 150, in <module>
    asyncio.run(async_main())
  File "/usr/local/opt/python@3.8/Frameworks/Python.framework/Versions/3.8/lib/python3.8/asyncio/runners.py", line 43, in run
    return loop.run_until_complete(main)
  File "/usr/local/opt/python@3.8/Frameworks/Python.framework/Versions/3.8/lib/python3.8/asyncio/base_events.py", line 616, in run_until_complete
    return future.result()
  File "script.py", line 140, in async_main
    await session_2.refresh(obj_a_refetched)
  File "/Users/jiatu/Library/Caches/pypoetry/virtualenvs/server-4z3-4eze-py3.8/lib/python3.8/site-packages/sqlalchemy/ext/asyncio/session.py", line 110, in refresh
    return await greenlet_spawn(
  File "/Users/jiatu/Library/Caches/pypoetry/virtualenvs/server-4z3-4eze-py3.8/lib/python3.8/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 121, in greenlet_spawn
    result = context.switch(value)
  File "/Users/jiatu/Library/Caches/pypoetry/virtualenvs/server-4z3-4eze-py3.8/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2228, in refresh
    loading.load_on_ident(
  File "/Users/jiatu/Library/Caches/pypoetry/virtualenvs/server-4z3-4eze-py3.8/lib/python3.8/site-packages/sqlalchemy/orm/loading.py", line 372, in load_on_ident
    return load_on_pk_identity(
  File "/Users/jiatu/Library/Caches/pypoetry/virtualenvs/server-4z3-4eze-py3.8/lib/python3.8/site-packages/sqlalchemy/orm/loading.py", line 542, in load_on_pk_identity
    return result.one()
  File "/Users/jiatu/Library/Caches/pypoetry/virtualenvs/server-4z3-4eze-py3.8/lib/python3.8/site-packages/sqlalchemy/engine/result.py", line 1380, in one
    return self._only_one_row(True, True, False)
  File "/Users/jiatu/Library/Caches/pypoetry/virtualenvs/server-4z3-4eze-py3.8/lib/python3.8/site-packages/sqlalchemy/engine/result.py", line 566, in _only_one_row
    row = onerow(hard_close=True)
  File "/Users/jiatu/Library/Caches/pypoetry/virtualenvs/server-4z3-4eze-py3.8/lib/python3.8/site-packages/sqlalchemy/engine/result.py", line 1240, in _fetchone_impl
    return self._real_result._fetchone_impl(hard_close=hard_close)
  File "/Users/jiatu/Library/Caches/pypoetry/virtualenvs/server-4z3-4eze-py3.8/lib/python3.8/site-packages/sqlalchemy/engine/result.py", line 1627, in _fetchone_impl
    row = next(self.iterator, _NO_ROW)
  File "/Users/jiatu/Library/Caches/pypoetry/virtualenvs/server-4z3-4eze-py3.8/lib/python3.8/site-packages/sqlalchemy/orm/loading.py", line 124, in chunks
    rows = [proc(row) for row in fetch]
  File "/Users/jiatu/Library/Caches/pypoetry/virtualenvs/server-4z3-4eze-py3.8/lib/python3.8/site-packages/sqlalchemy/orm/loading.py", line 124, in <listcomp>
    rows = [proc(row) for row in fetch]
  File "/Users/jiatu/Library/Caches/pypoetry/virtualenvs/server-4z3-4eze-py3.8/lib/python3.8/site-packages/sqlalchemy/orm/loading.py", line 933, in _instance
    _populate_full(
  File "/Users/jiatu/Library/Caches/pypoetry/virtualenvs/server-4z3-4eze-py3.8/lib/python3.8/site-packages/sqlalchemy/orm/loading.py", line 1106, in _populate_full
    populator(state, dict_, row)
  File "/Users/jiatu/Library/Caches/pypoetry/virtualenvs/server-4z3-4eze-py3.8/lib/python3.8/site-packages/sqlalchemy/orm/strategies.py", line 1237, in load_immediate
    state.get_impl(self.key).get(state, dict_)
  File "/Users/jiatu/Library/Caches/pypoetry/virtualenvs/server-4z3-4eze-py3.8/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py", line 909, in get
    value = self.callable_(state, passive)
  File "/Users/jiatu/Library/Caches/pypoetry/virtualenvs/server-4z3-4eze-py3.8/lib/python3.8/site-packages/sqlalchemy/orm/strategies.py", line 822, in _load_for_state
    self._invoke_raise_load(state, passive, "raise")
  File "/Users/jiatu/Library/Caches/pypoetry/virtualenvs/server-4z3-4eze-py3.8/lib/python3.8/site-packages/sqlalchemy/orm/strategies.py", line 781, in _invoke_raise_load
    raise sa_exc.InvalidRequestError(
sqlalchemy.exc.InvalidRequestError: 'A.bs' is not available due to lazy='raise'

Thanks for answering my 3rd question in a week! Really appreciate it.

[zzzeek]

I guess not, because the selectinload operation has already fired off and isn't going to associate itself with obj_a after the load.

I will give you a hint, just dont use expire or refresh especially in asyncio. If you want to reload a bunch of objects, use populate existing. it does just what you want refresh to be doing and you can set whatever loader options you want, and as a bonus it wont expire things either.

[zzzeek]

not a regression, just a bug in the new feature. its that lazy=raise you have interfering. will be fixed in 1.4.8

[ljiatu]

Cool thanks! Does this mean once we install 1.4.8 refresh will load those relationships, or at least not raise an exception?

[zzzeek]

it will correctly refresh the relationships, yes. refresh is now documented as honoring eager load relationships at https://docs.sqlalchemy.org/en/14/changelog/migration_14.html#eager-loaders-emit-during-unexpire-operations and it was just the "raise" that wasnt expected. see #6252

[zzzeek]

you still will probably like populate_existing better though

[ljiatu]

Sounds good. Thanks again!