SQLAlchemySession on PostgreSQL returns items out of order and pops the wrong item when created_at is not monotonic

Aperta Adatta ai principianti
  • Qualcosa non va?
#5,459 4 commenti 0 reazioni 0 assegnatari Vedi su GitHub

I maintainer di solito rispondono entro 1 giorno

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
2/5
Tempo stimato
1-3 ore
Idoneità per principianti
85/100
Tipo di issue
Bug
Chiarezza
Specificata chiaramente
Stato di attività
Attiva
Stack tecnologico
postgresql, python, sqlalchemy
Ambito
databases

Direzione di ricerca

Inizia in src/agents/extensions/memory/sqlalchemy_session.py, dove get_items e pop_item ordinano per created_at, id. Leggi la definizione di Column per created_at e le chiamate order_by sia nei metodi sincroni sia in quelli asincroni. Cambia l'ordinamento impostando solo id oppure salva i timestamp come UTC, poi esegui lo script di riproduzione dell'issue per confermare che i turni vengono restituiti nell'ordine di aggiunta. Fatto significa che get_items e pop_item corrispondono al comportamento di SQLiteSession.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

Please read this first
  • Have you read the docs? Agents SDK docs
    Yes
  • Have you searched for related issues? Others may have faced similar issues.
    Yes. #1900 fixed ties between equal timestamps by adding id as a tie-breaker. This report is about timestamps that go backwards.
Describe the bug

Currently, SQLAlchemySession.get_items and pop_item order rows by created_at first and id second. The column is TIMESTAMP(timezone=False) with server_default=CURRENT_TIMESTAMP. On PostgreSQL that value is the transaction start time, converted to the wall clock of the connection's TimeZone setting. It does not follow the order in which rows were appended:

  • writers whose connections use different TimeZone settings write timestamps that differ by the UTC offset;
  • on a server whose time zone observes daylight saving time, the wall clock repeats an hour when the clocks fall back, so rows written during the repeated hour can sort before rows written in the hour just before it.

In each case get_items() returns turns out of order, get_items(limit=1) returns an item that is not the latest, and pop_item() deletes and returns an item that is not the most recent. #1900 shows what such a reordering does to the next run: a reasoning item that sorts after its message makes the Responses API reject the input with Item 'msg_...' of type 'message' was provided without its required 'reasoning' item. SQLiteSession and AsyncSQLiteSession order by id and return the append order for the same calls. The Session docstrings say get_items returns items "in chronological order" and pop_item removes "the most recent item".

The same ordering is on main (src/agents/extensions/memory/sqlalchemy_session.py):

            Column(
                "created_at",
                TIMESTAMP(timezone=False),
                server_default=sql_text("CURRENT_TIMESTAMP"),
                nullable=False,
            ),

                .order_by(
                    self._messages.c.created_at.desc(),
                    self._messages.c.id.desc(),
                )
Debug information
  • Agents SDK version: 0.24.0
  • Related library versions (optional, e.g. any-llm, litellm, or pydantic): SQLAlchemy 2.1.6, asyncpg 0.32.0, PostgreSQL 17.11
  • Python version: 3.12.13
  • Operating system: macOS 26.6.2, arm64 (Darwin 25.6.0)
  • Model and model provider: none (session API only)
  • Does the issue reproduce with the latest Agents SDK release? Yes, 0.24.0; main has the same code.
  • Does the issue occur consistently or intermittently? Consistently with the script below.
Repro steps
import asyncio
from sqlalchemy.ext.asyncio import create_async_engine
from agents.extensions.memory import SQLAlchemySession

URL = "postgresql+asyncpg://postgres@localhost:5432/postgres"


async def main():
    # two app instances whose connections use different TimeZone settings
    utc = create_async_engine(URL, connect_args={"server_settings": {"timezone": "UTC"}})
    toronto = create_async_engine(URL, connect_args={"server_settings": {"timezone": "America/Toronto"}})
    a = SQLAlchemySession("conv", engine=utc, create_tables=True)
    b = SQLAlchemySession("conv", engine=toronto, create_tables=True)
    await a.clear_session()

    await a.add_items([{"role": "user", "content": "turn 1"}])
    await a.add_items([{"role": "user", "content": "turn 2"}])
    await b.add_items([{"role": "user", "content": "turn 3"}])

    print([i["content"] for i in await a.get_items()])
    # actual:   ['turn 3', 'turn 1', 'turn 2']
    # expected: ['turn 1', 'turn 2', 'turn 3']
    print([i["content"] for i in await a.get_items(limit=1)])
    # actual:   ['turn 2']
    # expected: ['turn 3']
    print((await a.pop_item())["content"])
    # actual:   turn 2
    # expected: turn 3


asyncio.run(main())

With both engines set to the same TimeZone, the output is the expected one. A server whose own time zone observes daylight saving time behaves like the second engine after the clocks fall back. With TimeZone set to America/Toronto, SELECT '2026-11-01 05:59:00+00'::TIMESTAMPTZ::TIMESTAMP, '2026-11-01 06:00:00+00'::TIMESTAMPTZ::TIMESTAMP returns 2026-11-01 01:59:00 and 2026-11-01 01:00:00: a row written one minute later gets a created_at 59 minutes earlier.

Expected behavior

get_items returns items in the order they were added, and pop_item removes the last item added, as SQLiteSession does for the same calls. Storing created_at as TIMESTAMP WITH TIME ZONE (or always in UTC) keeps the current created_at, id ordering correct; ordering by id alone also gives that order.

Lingua principale
Python
Stelle
30.4k
Fork
4.9k
Merge medio
19h 5m
PR unite (30g)
90

Preparare l'ambiente

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di openai/openai-agents-python

Tutte le issue di openai/openai-agents-python

Issue simili

Altre issue su Python

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.