Refetching an object doesn't refresh what's in the session when expire_on_commit=False

[ljiatu]

In the following example, print(A.data) prints "A", after its data has been modified by another session to "B". So it seems like refetching an object that's still in the session doesn't refresh the data in the session. Is this the expected behavior?

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 sessionmaker

Base = declarative_base()


class A(Base):
    __tablename__ = "as"

    id = Column(Integer, primary_key=True)
    data = Column(String)


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_1:
        obj_a = A(id=1, data="A")
        async with session_1.begin():
            session_1.add(obj_a)

        async with session_maker() as session_2:
            async with session_2.begin():
                obj_a_again = await session_2.get(A, obj_a.id)
                obj_a_again.data = "B"

        result = await session_1.execute(select(A).where(A.id == obj_a.id))
        obj_a_refetched = result.scalar_one()
        print("-" * 80)
        print(obj_a_refetched.data)  # prints "A"
        print("-" * 80)

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


asyncio.run(async_main())

Thanks for your help!

[zzzeek]

yes this is expected.

Database transactions are isolated so based on the isolation configuration of the database in use, it might not be possible for an ongoing transaction to "see" what's been committed outside of that transaction since it started.

In your example above, you in fact are committing both transactions, so when you begin a second transaction on the original Session, you would be able to see these changes from a database perspective. But you set expire_on_commit=False, which means that even though you've committed your work, the objects within that Session are non-expired and will not refresh by default, even if a new SELECT statement is emitted that touches them.

To have the new SELECT statement actually impact these already-loaded, non-expired records, the populate_existing option should be used indicating this intent. See that section for additional background.

See also:

I’m re-loading data with my Session but it isn’t seeing changes that I committed elsewhere

[ljiatu]

Got it. Thanks got the quick response as always!

[ljiatu]

Another somewhat related question: is there a way to specify expire_on_flush = False?

[zzzeek]

flush does not expire attributes with the exception of column_property() attributes that are against arbitrary SQL expressions.

[ljiatu]

Oh you are right. I saw an exception but it's because I'm requesting data that was not explicitly added when I was adding the obect. Thanks again!