Repository navigation
Description
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:
- Create a DatabaseSessionService backed by PostgreSQL (asyncpg)
- Create 3+ sessions for the same (app_name, user_id) over different days
- Call list_sessions(app_name=..., user_id=...) multiple times
- 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
godququ5-code commentedon Jul 3, 2026
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?
surajksharma07 commentedon Jul 6, 2026
@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!
pr-maia commentedon Jul 6, 2026
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.
surajksharma07 commentedon Jul 13, 2026
@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!
Sehlani042 commentedon Jul 13, 2026
Thanks for clarifying the intended contract. I updated #6276 to order by
update_time ASC, withuser_idandidas stable tie-breakers.The new regression makes the distinction explicit: an orphan session has the newest
create_timebut the oldestupdate_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).surajksharma07 commentedon Jul 17, 2026
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!
Q01P commentedon Jul 20, 2026
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.
surajksharma07 commentedon Jul 28, 2026
@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!
pr-maia commentedon Jul 28, 2026
Thanks, Suraj
fix: order database sessions deterministically
fix: order list_sessions results by last update time
fix: order database sessions deterministically
fix: order list_sessions results by last update time