skip to content

With SQLAlchemy's AsyncSession, why can reading a server-generated column right after a flush raise MissingGreenlet, and how does the mapper's eager_defaults setting prevent it?

level: seniorimportance: nice to knowfreq 18%

answer

  1. the database chose the value
  2. expired until fetched
  3. RETURNING on INSERT by default
  4. UPDATE needs eager_defaults=True

basics

~20 s

A server-generated value is unknown to Python after INSERT or UPDATE, so the ORM leaves it expired unless the flush fetched it. Under AsyncSession that later read raises. eager_defaults fetches it during the flush, through RETURNING or an extra SELECT.

solid answer

~40 s

A column with `server_default` or `server_onupdate` gets its value from the database, so after a flush the ORM either fetched it during the flush or marked it expired. Reading an expired attribute needs a SELECT, which under `AsyncSession` raises `MissingGreenlet`. The mapper option `eager_defaults` decides the fetch. Its default, `"auto"`, adds the columns to RETURNING on INSERT only, and only when the dialect supports RETURNING with insertmanyvalues: PostgreSQL and SQLite qualify, MySQL 8 does not. UPDATE is never covered by `"auto"`. Setting `__mapper_args__ = {"eager_defaults": True}` fetches on INSERT and UPDATE, with RETURNING where available and an extra SELECT otherwise. The alternatives are an explicit `await session.refresh(obj, ["updated_at"])` or `await obj.awaitable_attrs.updated_at`.

code

python · 44 lines
python
import asyncio
import datetime

from sqlalchemy import FetchedValue, String, func
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


class Order(Base):
    __tablename__ = "orders"
    id: Mapped[int] = mapped_column(primary_key=True)
    status: Mapped[str] = mapped_column(String(20))
    created_at: Mapped[datetime.datetime] = mapped_column(server_default=func.now())
    # set by a database trigger on UPDATE in production
    updated_at: Mapped[datetime.datetime] = mapped_column(
        server_default=func.now(), server_onupdate=FetchedValue()
    )

    # "auto" (the default) fetches server values on INSERT only;
    # True also adds RETURNING to the UPDATE
    __mapper_args__ = {"eager_defaults": True}


async def main() -> None:
    engine = create_async_engine("sqlite+aiosqlite://", echo=True)
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)
    Session = async_sessionmaker(engine, expire_on_commit=False)
    async with Session() as session:
        order = Order(status="new")
        session.add(order)
        await session.flush()  # INSERT ... RETURNING id, created_at, updated_at
        order.status = "paid"
        await session.flush()  # UPDATE ... RETURNING updated_at
        await session.commit()
    print(order.created_at is not None, order.updated_at is not None)
    await engine.dispose()


asyncio.run(main())

go deeper

for a junior

Remember that a value the database generates is not known to Python after the write unless SQLAlchemy fetched it, and that reading it later under asyncio can raise.

for a middle

Explain eager_defaults: "auto" fetches on INSERT through RETURNING where supported, True also covers UPDATE, and an unfetched column is left expired until read.

for a senior

Diagnose the single-column SELECT in the error, account for MySQL's missing RETURNING and the UPDATE gap, and pick between eager_defaults=True and an explicit refresh.

for a principal

Weigh where the database must own timestamps against the per-row UPDATE cost of eager_defaults=True, and keep client-side defaults where Python can be the source of truth.

## Where the value comes from A **server-generated column** gets its value from the database rather than from Python: a `server_default=func.now()` on INSERT, a trigger that sets `updated_at` on UPDATE (declared with `server_onupdate=FetchedValue()`). After the ORM flushes the INSERT or UPDATE, Python does not know that value. The ORM has two options: fetch it as part of the flush, or mark the attribute **expired** so that the next read loads it. In synchronous code an expired attribute is harmless, because the next read quietly runs a SELECT. Under `AsyncSession` that read is implicit IO from plain attribute access, and it raises `MissingGreenlet`. So an async handler that does `session.add(order)`, `await session.flush()`, and then returns `order.created_at` can fail on some databases and work on others. ## The eager_defaults mapper option `eager_defaults` is a mapper parameter, set through `__mapper_args__` on a declarative class. It takes three values: | Value | INSERT | UPDATE | |---|---|---| | `"auto"` (default) | fetched through RETURNING if the dialect supports RETURNING and insertmanyvalues; otherwise not fetched | not fetched | | `True` | fetched, through RETURNING or an extra SELECT after the statement | fetched, through RETURNING or an extra SELECT | | `False` | not fetched | not fetched | Primary keys are always fetched, by RETURNING or the driver's own mechanism, because the ORM needs the identity; `eager_defaults` concerns the other server-generated columns. ## Which databases are covered by "auto" - **PostgreSQL and SQLite 3.35+** support RETURNING, so with `"auto"` the INSERT becomes `INSERT INTO orders (status) VALUES (?) RETURNING id, created_at, updated_at` and the value is present after flush. - **MySQL 8** has no RETURNING, so `"auto"` fetches nothing and `created_at` stays expired after the flush. - A table declared with `implicit_returning=False` (commonly because a trigger is incompatible with RETURNING) also falls back to not fetching under `"auto"`. ## The UPDATE gap The `"auto"` setting deliberately ignores UPDATE. The reason is cost: INSERT RETURNING can be batched through insertmanyvalues, but UPDATE RETURNING needs one statement per row, so SQLAlchemy only does it when asked. A trigger-maintained `updated_at` therefore stays expired after every UPDATE flush, even on PostgreSQL, and reading it in an async handler raises. Setting `eager_defaults=True` turns the UPDATE into `UPDATE orders SET status=? WHERE orders.id = ? RETURNING updated_at`. ## Choosing a remedy 1. **`eager_defaults=True` on the mapper** when the value is read after most writes. Cost: UPDATE statements sent one row at a time with RETURNING, or an extra SELECT after each statement on databases without RETURNING. 2. **An explicit read** when only some paths need it: `await session.refresh(order, ["updated_at"])`, or `await order.awaitable_attrs.updated_at` with the `AsyncAttrs` mixin. Cost: one SELECT, paid only where needed. 3. **A client-side default** (`default=` or `onupdate=` on `mapped_column()`) when the database does not have to be the source of truth; then Python knows the value and nothing is expired. `expire_on_commit=False` does not help here: it stops expiry at commit, but these attributes were expired by the flush itself and stay expired. ## A flush, step by step 1. `session.add(order)` records a pending object; nothing is sent. 2. `await session.flush()` sends the INSERT. With RETURNING available and `eager_defaults` at `"auto"` or `True`, the server-generated columns come back in the same round trip and are set on the object. 3. Without RETURNING and with `"auto"`, those attributes are marked expired. With `True`, the ORM sends an extra SELECT inside the same awaited flush, so the value is still present. 4. A later UPDATE flush repeats the decision, except that `"auto"` never fetches on UPDATE. 5. Any attribute still expired after step 4 is a `MissingGreenlet` waiting for the first plain read. ## Spotting it - With `echo=True`, look at the INSERT or UPDATE: if the column is missing from its RETURNING clause and no SELECT follows, the attribute is expired. - The error's printed SQL selects only the unfetched columns by primary key, such as `SELECT orders.updated_at FROM orders WHERE orders.id = ?`, a strong hint that a server value was not fetched.

  • Why does eager_defaults="auto" leave UPDATE out even on PostgreSQL?
    INSERT RETURNING can be batched for many rows through insertmanyvalues, so fetching server defaults on INSERT costs almost nothing. UPDATE has no equivalent batching: with RETURNING, each row's UPDATE must be sent individually. SQLAlchemy therefore only adds RETURNING to UPDATE when `eager_defaults=True` asks for it.
  • Does expire_on_commit=False keep the server-generated value readable after commit?
    No. The attribute was expired by the flush, because the ORM never learned its value; `expire_on_commit=False` only stops the commit from expiring attributes that were loaded. The column stays expired, so reading it inside the session raises `MissingGreenlet` and after the session closes raises `DetachedInstanceError`, unless it was fetched with `eager_defaults` or read explicitly.

  • With eager_defaults="auto" an UPDATE on PostgreSQL returns server_onupdate columns automatically.
  • server_default values are computed by SQLAlchemy in Python and set on the object.
  • expire_on_commit=False makes server-generated columns readable after commit.
  • MySQL 8 returns server defaults through RETURNING just like PostgreSQL.
  • eager_defaults is a parameter of async_sessionmaker.