cleveragents/cleveragents-core

Fork 3

BUG-HUNT: [resource-leak] Session not closed in non-auto_commit read methods of SessionRepository and related repos #7720

New issue

Open

opened 2026-04-12 03:20:39 +00:00 by HAL9000 · 3 comments

HAL9000 commented

2026-04-12 03:20:39 +00:00

Owner

Bug Report: Resource Leak — Sessions Not Closed in Read Methods

Severity Assessment

Impact: Database connection pool exhaustion in long-running processes; sessions accumulate without being returned to the pool
Likelihood: High — read methods are called frequently and consistently lack finally: session.close()
Priority: High

Location

File: src/cleveragents/infrastructure/database/repositories.py
Function/Class: SessionRepository.get_by_id, SessionRepository.list_all, SessionRepository.count_for_session, SessionMessageRepository.get_for_session, SessionMessageRepository.count_for_session, AutomationProfileRepository.get_by_name, AutomationProfileRepository.list_all, SkillRepository.get_by_name, SkillRepository.list_all, and others
Lines: ~4098-4116, ~4128-4139, ~4338-4351, ~4308-4326, ~4418-4431, ~4440-4455, ~4824-4831, ~4846-4854

Description

Multiple repository read methods use the pattern:

db_session = self._session()
try:
    # query...
except ...:
    raise

When auto_commit=False (the default), there is no finally: db_session.close() block, so sessions obtained from the session factory are never explicitly closed after read operations. In a connection-pool environment, this causes sessions to accumulate.

Contrast this with the mutating methods that use finally: db_session.close() only when auto_commit=True, but read methods have no session cleanup in either mode.

Evidence

# repositories.py lines 4097-4116 - SessionRepository.get_by_id
@database_retry
def get_by_id(self, session_id: str) -> Any | None:
    db_session = self._session()
    try:
        row = (
            db_session.query(SessionModel).filter_by(session_id=session_id).first()
        )
        if row is None:
            return None
        return row.to_domain()
    except (OperationalError, SQLAlchemyDatabaseError) as exc:
        raise DatabaseError(f"Failed to get session {session_id}: {exc}") from exc
    finally:
        if self._auto_commit:   # <-- only closes when auto_commit=True
            db_session.close()

When auto_commit=False (default), a session is obtained but NEVER closed after a read.

Expected Behavior

Read methods should close the session in a finally block unconditionally (or at least when not managed by an external UoW context).

Actual Behavior

Sessions are only closed in finally blocks when self._auto_commit is True. In the default mode (auto_commit=False), read methods never close their sessions.

Suggested Fix

For read-only methods (no mutation, no commit), unconditionally close the session:

db_session = self._session()
try:
    # query...
    return result
except (OperationalError, SQLAlchemyDatabaseError) as exc:
    raise DatabaseError(...) from exc
finally:
    db_session.close()  # always close read sessions

Alternatively, use a context manager for session lifecycle, or switch to SQLAlchemy 2.0-style with session: blocks.

TDD Note

After this bug issue is verified, a corresponding Type/Testing issue will be created for TDD.

Automated by CleverAgents Bot
Supervisor: Bug Hunting | Agent: bug-hunter

## Bug Report: Resource Leak — Sessions Not Closed in Read Methods ### Severity Assessment - **Impact**: Database connection pool exhaustion in long-running processes; sessions accumulate without being returned to the pool - **Likelihood**: High — read methods are called frequently and consistently lack `finally: session.close()` - **Priority**: High ### Location - **File**: `src/cleveragents/infrastructure/database/repositories.py` - **Function/Class**: `SessionRepository.get_by_id`, `SessionRepository.list_all`, `SessionRepository.count_for_session`, `SessionMessageRepository.get_for_session`, `SessionMessageRepository.count_for_session`, `AutomationProfileRepository.get_by_name`, `AutomationProfileRepository.list_all`, `SkillRepository.get_by_name`, `SkillRepository.list_all`, and others - **Lines**: ~4098-4116, ~4128-4139, ~4338-4351, ~4308-4326, ~4418-4431, ~4440-4455, ~4824-4831, ~4846-4854 ### Description Multiple repository read methods use the pattern: ```python db_session = self._session() try: # query... except ...: raise ``` When `auto_commit=False` (the default), there is no `finally: db_session.close()` block, so sessions obtained from the session factory are never explicitly closed after read operations. In a connection-pool environment, this causes sessions to accumulate. Contrast this with the mutating methods that use `finally: db_session.close()` only when `auto_commit=True`, but read methods have no session cleanup in either mode. ### Evidence ```python # repositories.py lines 4097-4116 - SessionRepository.get_by_id @database_retry def get_by_id(self, session_id: str) -> Any | None: db_session = self._session() try: row = ( db_session.query(SessionModel).filter_by(session_id=session_id).first() ) if row is None: return None return row.to_domain() except (OperationalError, SQLAlchemyDatabaseError) as exc: raise DatabaseError(f"Failed to get session {session_id}: {exc}") from exc finally: if self._auto_commit: # <-- only closes when auto_commit=True db_session.close() ``` When `auto_commit=False` (default), a session is obtained but NEVER closed after a read. ### Expected Behavior Read methods should close the session in a `finally` block unconditionally (or at least when not managed by an external UoW context). ### Actual Behavior Sessions are only closed in `finally` blocks when `self._auto_commit` is `True`. In the default mode (`auto_commit=False`), read methods never close their sessions. ### Suggested Fix For read-only methods (no mutation, no commit), unconditionally close the session: ```python db_session = self._session() try: # query... return result except (OperationalError, SQLAlchemyDatabaseError) as exc: raise DatabaseError(...) from exc finally: db_session.close() # always close read sessions ``` Alternatively, use a context manager for session lifecycle, or switch to SQLAlchemy 2.0-style `with session:` blocks. ### Category resource-leak ### TDD Note After this bug issue is verified, a corresponding Type/Testing issue will be created for TDD. --- **Automated by CleverAgents Bot** Supervisor: Bug Hunting | Agent: bug-hunter

HAL9000 added this to the v3.2.0 milestone

2026-04-12 03:42:25 +00:00

HAL9000 referenced this issue

2026-04-12 03:47:56 +00:00

[AUTO-PROJ-OWN] Project Owner Report (Cycle 1) #7724

HAL9000 added the

labels

2026-04-12 07:16:03 +00:00

freemo added the

Type

Bug

label

2026-04-12 18:33:57 +00:00

HAL9000 added

and removed

labels

2026-04-14 05:58:53 +00:00

HAL9000 commented

2026-04-14 05:58:53 +00:00

Author

Owner

✅ Verified — Resource leak: Session not closed in non-auto_commit read methods. MoSCoW: Should-have. Priority: Medium.

Automated by CleverAgents Bot
Supervisor: Project Owner | Agent: project-owner-pool-supervisor

✅ **Verified** — Resource leak: Session not closed in non-auto_commit read methods. MoSCoW: Should-have. Priority: Medium. --- **Automated by CleverAgents Bot** Supervisor: Project Owner | Agent: project-owner-pool-supervisor

HAL9000 commented

2026-04-14 06:12:08 +00:00

Author

Owner

✅ Verified — Resource leak: Session not closed in non-auto_commit read methods. MoSCoW: Should-have. Priority: Medium.

Automated by CleverAgents Bot
Supervisor: Project Owner | Agent: project-owner-pool-supervisor