MissingGreenlet/Idle in transaction query

[gryevns]

Using FastAPI/sqlalchemy/postgres if you attempt to access a relationship without loading the results you will receive a sqlalchemy.exc.MissingGreenlet exception. This can leave the query/connection in an unexpected state whereby it is never invalidated/cleaned up.

In the following code item.children is the "bad" line which raises the MissingGreenlet exception. The sleep allows you to view the state of the transaction via postgres without the script exiting and the sqlalchemy pool being cleaned up. When executed you should see an "idle in transaction" query for the select statement after the exception is raised. This is not cleaned up even though we call rollback/close on the session.

import asyncio
import contextlib

from sqlalchemy import Column
from sqlalchemy import DateTime
from sqlalchemy import ForeignKey
from sqlalchemy import func
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy.future import select
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm import selectinload
from sqlalchemy.orm import sessionmaker

class _Base:
    __mapper_args__ = {"eager_defaults": True}

Base = declarative_base(cls=_Base)

class Parent(Base):
    __tablename__ = "a"
    id = Column(Integer, primary_key=True)
    data = Column(String())
    children = relationship("Child")

class Child(Base):
    __tablename__ = "b"
    id = Column(Integer, primary_key=True)
    parent_id = Column(ForeignKey("a.id"))

class UnitOfWorkFactory():
    def __init__(self, session_factory: sessionmaker):
        self._session_factory = session_factory

    @contextlib.asynccontextmanager
    async def __call__(self):
        uow = self._session_factory()
        try:
            yield uow
        finally:
            await uow.rollback()
            await uow.close()

async def async_main():
    engine = create_async_engine(
        "postgresql+asyncpg://postgres:password@127.0.0.1:5432",
        echo=True,
        future=True,
        pool_size=10,
        max_overflow=5,
        pool_timeout=5,
        pool_pre_ping=True,
    )

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

    session_factory = sessionmaker(engine, class_=AsyncSession, future=True)
    uow_factory = UnitOfWorkFactory(session_factory)

    async with uow_factory() as uow:
        uow.add(Parent(children=[Child()]))
        await uow.commit()

    try:
        async with uow_factory() as uow:
            stmt = select(Parent).with_for_update(of=Parent)
            result = await uow.execute(stmt)
            for item in result.scalars():
                item.data = "updated"
                item.children  # Raises sqlalchemy.exc.MissingGreenlet
    except:
        await asyncio.sleep(30)
        raise

asyncio.run(async_main())

postgres

postgres=# SELECT state, query FROM pg_stat_activity WHERE datname = 'postgres';
        state        |                                 query                                 
---------------------+-----------------------------------------------------------------------
 idle in transaction | SELECT a.id, a.data                                                  +
                     | FROM a FOR UPDATE OF a
 active              | SELECT state, query FROM pg_stat_activity WHERE datname = 'postgres';
(2 rows)

---

• Should sqlalchemy clean up this connection?
• Should we be catching this MissingGreenlet exception and cleaning up the connection?

Repo with reproduction steps: https://github.com/gryevns/sqlalchemy-idle-transaction/tree/main

Thanks!

[gryevns]

This is a work-in-progress work around for the issue described which cleans up the session correctly (no more idle in transaction query). However, it still outputs some runtime warnings to the console:

sys:1: RuntimeWarning: coroutine 'AsyncAdapt_asyncpg_cursor._prepare_and_execute' was never awaited
RuntimeWarning: Enable tracemalloc to get the object allocation traceback
sys:1: RuntimeWarning: coroutine 'Transaction.rollback' was never awaited
RuntimeWarning: Enable tracemalloc to get the object allocation traceback

[CaselIT]

Hi,

It seems to happen only when an update is present and only with asyncpg. Seems like there is a bug somewhere

import asyncio
from contextlib import asynccontextmanager
from sqlalchemy import ForeignKey
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker
from sqlalchemy.orm import DeclarativeBase, mapped_column, Mapped, relationship

class Base(DeclarativeBase):
    pass

class Foo(Base):
    __tablename__ = "foo"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]

class Bar(Base):
    __tablename__ = "bar"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str | None]
    foo_id: Mapped[int] = mapped_column(ForeignKey("foo.id"))
    foo: Mapped[Foo] = relationship()

engine = create_async_engine("postgresql+asyncpg://scott:tiger@localhost/test")

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

S = async_sessionmaker(engine)

@asynccontextmanager
async def session():
    async with S() as s:
        yield s

async def run():
    async with session() as s:
        async with s.begin():
            f = Foo(name="foo")
            s.add(f)
            b = Bar(foo=f)
            s.add(b)
            await s.flush()

    async with session() as s:
        b = await s.get_one(Bar, 1)
        b.name = "abc" # comment to remove "idle in transaction"
        print(b.foo.name)

async def main():
    await go()
    async with engine.connect() as conn:
        try:
            await run()
        except Exception as e:
            res = await conn.exec_driver_sql("SELECT state, query, datname FROM pg_stat_activity;")
            for row in res:
                print(row)
            print(e)

asyncio.run(main())

@zzzeek ideas on this one? The rollback is logged in the server log. Maybe there's something happening when there is a flush that's active in the session?
but in psycopg we don't have this behaviour, so it seems that issue is in the dialect

[zzzeek]

im still trying to understand this one and so far it looks like this pg_stat thing is only in the exception block, where the transaction has not been rolled back yet, so not clear yet if there's something unexpected

[zzzeek]

well, ok the session trans should be rolled back. there's some trickery going on here, two interleaving transactions, not sure yet

[CaselIT]

yes, when the stat query is run after the context manager has completely executed.
and in fact in the logs there is a rollback

[zzzeek]

OK bug is confirmed thanks! this is a release ASAP bug, hopefully release today

#11819

[gryevns]

Thanks for confirming, is it possible to backport for the 1.4.x release?

[zzzeek]

with difficulty