Skip to content

DatabaseSessionService.list_sessions() returns unordered results — sessions[-1] is unreliable for "latest session" logic #6272

Closed

Description

@pr-maia

Describe the Bug:

DatabaseSessionService.list_sessions() executes a SELECT without an ORDER BY clause, so the returned list of sessions has no guaranteed ordering. Any consumer code that relies on positional indexing (e.g. sessions[-1] for "most recent") will intermittently pick up the wrong session depending on PostgreSQL's internal heap ordering.

In our case, this caused session fragmentation: a user with multiple sessions across weeks would randomly get an old session as sessions[-1], fail a "is this session current?" check, and create a new orphan session — losing all conversation history.

Steps to Reproduce:

  1. Create a DatabaseSessionService backed by PostgreSQL (asyncpg)
  2. Create 3+ sessions for the same (app_name, user_id) over different days
  3. Call list_sessions(app_name=..., user_id=...) multiple times
  4. Observe that response.sessions[-1] is not consistently the most recently created or most recently updated session

Expected Behavior:

list_sessions() should return sessions in a deterministic, documented order — ideally by create_time ASC (matching the intuition that sessions[-1] is the newest). Alternatively, the docstring should explicitly state that ordering is undefined.

Observed Behavior:

Results come back in PostgreSQL's arbitrary heap order. With 8 sessions for one user, sessions[-1] returned a 3-day-old orphan session instead of the actively-used one. This caused every subsequent message to create a new session, breaking conversation continuity.

The relevant code in database_session_service.py (v2.3.0, line ~583):

stmt = select(schema.StorageSession).filter(
schema.StorageSession.app_name == app_name
)
if user_id is not None:
stmt = stmt.filter(schema.StorageSession.user_id == user_id)

result = await sql_session.execute(stmt)
results = result.scalars().all()

No .order_by() is applied.

Environment Details:

  • ADK Library Version: 2.3.0
  • Desktop OS: Linux (Docker, python:3.11-slim on Debian Bookworm)
  • Python Version: 3.11

Model Information:

  • Are you using LiteLLM: No
  • Which model is being used: gemini-3.5-flash

Suggested Fix:

Add .order_by(schema.StorageSession.create_time.asc()) to the query in list_sessions():

stmt = select(schema.StorageSession).filter(
schema.StorageSession.app_name == app_name
)
if user_id is not None:
stmt = stmt.filter(schema.StorageSession.user_id == user_id)
stmt = stmt.order_by(schema.StorageSession.create_time.asc())

Alternatively, ordering by update_time may be more useful for consumers that want the most recently active session.

Workaround:

Iterate over all returned sessions instead of relying on sessions[-1]:

existing = await session_service.list_sessions(app_name=APP_NAME, user_id=user_id)
if existing.sessions:
latest = max(existing.sessions, key=lambda s: s.last_update_time)

How often has this issue occurred?:

  • Often (50%+) — depends on the number of sessions per user and PostgreSQL's heap layout. With 5+ sessions it reproduces reliably; with 1-2 sessions it's masked.

Activity

  1. added
    services[Component] This issue is related to runtime services, e.g. sessions, memory, artifacts, etc
    on Jul 3, 2026
  2. added theissue type on Jul 3, 2026
  3. godququ5-code commented on Jul 3, 2026

    @godququ5-code

    Hi! I verified that DatabaseSessionService.list_sessions currently executes the filtered select without an ORDER BY, and the existing session-service test compares sessions without asserting order. I can add a deterministic ordering clause and a regression test using the existing in-memory SQLite setup. Before implementing, could a maintainer confirm the intended contract: create_time ascending, update_time ordering, or another key? I would also use id as a tie-breaker if deterministic ordering for equal timestamps is desired. Is anyone already working on this?

  4. surajksharma07 commented on Jul 6, 2026

    @surajksharma07
    Collaborator

    @pr-maia Confirmed — list_sessions() has no ORDER BY, so row order on Postgres just follows heap layout rather than create_time.
    Your suggested fix matches PR #6276, which already adds create_time ASC with an id tie-breaker plus a regression test.

    One thing worth settling before that merges: your own workaround sorts by last_update_time, not create_time.
    Do you actually want "most recently created" or "most recently active" as the ordering contract?
    #6276 picked create_time — worth confirming that's the one you need!

  5. added
    request clarification[Status] The maintainer need clarification or more information from the author
    on Jul 6, 2026
  6. pr-maia commented on Jul 6, 2026

    @pr-maia
    Author

    Hi, @surajksharma07

    Thanks a lot for the pointer

    To answer your question: update_time is the ordering we actually need, not create_time. Orphaned sessions have a newer create_time but a stale update_time so most recently active is the correct ordering contract.

  7. surajksharma07 commented on Jul 13, 2026

    @surajksharma07
    Collaborator

    @pr-maia Thanks for clarifying — that actually changes the fix.
    PR #6276 as it stands sorts by create_time ASC, but by your own description the orphan sessions have a newer create_time and a stale update_time, so create_time ordering would still hand sessions[-1] to the orphan rather than the active session — it fixes determinism but not your actual bug.

    We'll flag this to @Sehlani042 so the sort key (or at least the tie-break) gets switched to update_time before merge.
    Still unpatched on latest main (v2.4.0) in the meantime, so keep using the max(sessions, key=lambda s: s.last_update_time) workaround for now!

    Team is looking more into it.
    Thanks!

  8. removed
    request clarification[Status] The maintainer need clarification or more information from the author
    on Jul 13, 2026
  9. Sehlani042 commented on Jul 13, 2026

    @Sehlani042
    Contributor

    Thanks for clarifying the intended contract. I updated #6276 to order by update_time ASC, with user_id and id as stable tie-breakers.

    The new regression makes the distinction explicit: an orphan session has the newest create_time but the oldest update_time, while two active sessions share the latest update timestamp. It failed under the previous create-time ordering and now passes; the full session-service test file also passes (135 passed).

  10. surajksharma07 commented on Jul 17, 2026

    @surajksharma07
    Collaborator

    Pulled the #6276 diff to check this before replying — the update_time ASC ordering with user_id/id tie-breakers is correct for the actual bug. Since orphan sessions have a stale update_time (per your own description), ASC sorts them to the front rather than sessions[-1], so the most recently active session lands last deterministically.
    The orphan/middle/active_a/active_b test covers exactly that distinction.

    Confirmed list_sessions() still has zero ORDER BY on latest main (v2.5.0), so this isn't superseded by anything upstream.
    The existing test suite is also order-blind (set comparison), so it wouldn't have caught a regression here either.

    Team is looking more into it.
    Thanks!

  11. Q01P commented on Jul 20, 2026

    @Q01P

    FYI — SqliteSessionService.list_sessions() has the same missing ORDER BY. Filed as #6431 with fix in #6432, mirroring the update_time ASC contract settled here.

  12. added
    spam[Status] Issues suspected of having comments which are spam
    on Jul 21, 2026
  13. deleted a comment from adk-bot on Jul 22, 2026
  14. removed
    spam[Status] Issues suspected of having comments which are spam
    on Jul 22, 2026
  15. surajksharma07 commented on Jul 28, 2026

    @surajksharma07
    Collaborator

    @Q01P good catch — confirmed SqliteSessionService.list_sessions() runs raw SELECT queries with zero ORDER BY too (both the user-scoped and app-wide branches), same class of bug as this one.
    #6432 mirrors the exact update_time ASC + user_id/id tie-breaker contract we settled on for #6276, so the two backends will be consistent once both land.

    Both PRs are still open and awaiting review (#6276 → wyf7107, #6432 → sasha-gitg), neither merged yet — confirmed still zero ORDER BY on latest main.

    @pr-maia nothing's changed for you yet — keep using the max(sessions, key=lambda s: s.last_update_time) workaround until #6276 merges!

  16. pr-maia commented on Jul 28, 2026

    @pr-maia
    Author

    Thanks, Suraj

  17. added a commit that references this issue on Aug 6, 2026
    8455cf8
  18. added a commit that references this issue on Aug 6, 2026
    53e1afb
  19. added 2 commits that reference this issue on Aug 11, 2026
    f39b5c5
    d04cf96
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Labels

services[Component] This issue is related to runtime services, e.g. sessions, memory, artifacts, etc

Type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions