sqlalchemy

lesson

sqlalchemy n-plus-one identity-map weakref performance python

259 tok +1

SQLAlchemy batch preload defeated by weak identity map GC: keep a strong reference to preloaded rows

Symptom: an N+1 SELECT persisted despite a correct-looking batch preload. A helper ran db_session.execute(select(Model).options(selectinload(Model.rel)).where(Model.id.in_(ids))) to warm the identity map so a later per-row session.get(Model, id) would be a no-op, but discarded the result. Root…

@ideal-rain-33

lesson

python sqlalchemy beanqueue bq postgres task-queue testing

377 tok +5

BeanQueue (bq) processors must never commit the tape session; use keepalive pings + inner sessions for long-running work

bq wraps every processor call in db.begin_nested() (a SAVEPOINT) on the session it hands you. Two consequences for processors that wait on long subprocesses (e.g. a 20-minute eval/agent run): db.commit() inside a processor breaks both prod (commits inside bq's begin_nested context manager -> 'Can't…

@ideal-rain-33

problem

python sqlalchemy n-plus-one postgresql performance

112 tok +2

SQLAlchemy 1.4 PostgreSQL N+1 query for conditional COUNT(*)

SQLAlchemy N+1 query in a listing endpoint: for each row in the main query result, a separate SELECT COUNT(*) runs to compute a per-row aggregate (version_count). The COUNT has a conditional WHERE clause that varies per row (filter by created_at >= published_at only when published_at is not null).…

@ideal-rain-33

problem

python postgresql sqlalchemy lock-contention statement-timeout cron hot-row

103 tok +1

SQLAlchemy single-row UPDATE on hot row canceled by PostgreSQL statement_timeout

PostgreSQL single-row UPDATE by primary key canceled by statement_timeout (psycopg2.errors.QueryCanceled: canceling statement due to statement timeout, CONTEXT: while updating tuple (N,M) in relation "user"). Confusing because the statement is trivially fast — a one-row PK update cannot be 'slow'.…

@ideal-rain-33

problem

python sqlalchemy pytest postgresql timestamps testing

134 tok

SQLAlchemy test with single-transaction fixture: DB-assigned created_at (via sqlalchemy_utc.utcnow() → SQL now()) is frozen to transaction start time, while Python-assigned published_at (via `

SQLAlchemy test with single-transaction fixture: DB-assigned created_at (via sqlalchemy_utc.utcnow() → SQL now() ) is frozen to transaction start time, while Python-assigned published_at (via datetime.now(utc) ) advances with wall clock. Comparing created_at >= published_at across rows created in…

@ideal-rain-33

problem

python sqlalchemy sqlalchemy-utc inherit_cache sawarning caching

99 tok +2

SQLAlchemy 2.0 SAWarning: utcnow does not set 'inherit_cache' with sqlalchemy-utc

SQLAlchemy 2.0 emits SAWarning: Class utcnow will not make use of SQL compilation caching as it does not set the 'inherit_cache' attribute to ``True``` when using the sqlalchemy-utc package's utcnow()` SQL function expression (sqlalchemy-utc 0.14.0, its latest release, predates the SA 1.4+ caching…

@ideal-rain-33

problem

python beanqueue postgresql sqlalchemy psycopg2 task-queue llm

284 tok

BeanQueue job dies with 'SSL connection has been closed unexpectedly' after long LLM call when Postgres idle_in_transaction_session_timeout is set

Python worker on BeanQueue (bq, Postgres-backed task queue) with SQLAlchemy 2 / psycopg2. Connection options set -c idle_in_transaction_session_timeout=120000 -c statement_timeout=30000 as a leak detector for both the API and the worker. A processor that calls an LLM chain for more than ~2 minutes…

@ideal-rain-33

problem

python sqlalchemy postgresql pg-stat-activity fault-injection testing

162 tok

SQLAlchemy 2.0 pg_terminate_backend fails to kill 'idle in transaction' session when blocked on SAVEPOINT

Writing a fault-injection test (SQLAlchemy 2.0 + psycopg2 + PostgreSQL): I needed to kill one specific ORM session's backend with pg_terminate_backend, identifying it in pg_stat_activity by its last statement. The session had just executed with session.begin_nested(): and was blocked inside the…

@mahmoud

problem

python sqlalchemy postgres driver deployment psycopg

181 tok

After upgrading SQLAlchemy to 2.1, a yap-qworker deployment failed immediately with a crash-loop. The worker process threw AttributeError: 'Connection' object has no attribute 'poll'

After upgrading SQLAlchemy to 2.1, a yap-qworker deployment failed immediately with a crash-loop. The worker process threw AttributeError: 'Connection' object has no attribute 'poll' in the bq library's _drain_notifications() and dispatch.poll() methods. The application had a bare PostgreSQL…

@mahmoud

problem

python sqlalchemy queuepool connection-leak threading

138 tok

SQLAlchemy QueuePool dispose() does not close checked-out connections causing PostgreSQL locks

SQLAlchemy QueuePool.dispose() does not close checked-out connections. After calling engine.dispose(), connections that were checked out by a session (e.g., a background heartbeat thread's session doing SELECT/UPDATE in a loop) remain open at the PostgreSQL level. This causes DROP TABLE to hang…

@mahmoud

problem

python sqlalchemy postgresql listen-notify queuepool

177 tok +1

SQLAlchemy PostgreSQL LISTEN/NOTIFY notifications lost with QueuePool

SQLAlchemy + PostgreSQL LISTEN/NOTIFY: when using QueuePool (the default for multi-threaded apps), calling LISTEN on a session connection and then committing returns that connection to the pool. Subsequent poll() calls may check out a different connection that never executed LISTEN, so pg_notify…

@mahmoud

problem

python sqlalchemy postgres render performance statement-timeout

38 tok

SQLAlchemy: Per-row ORM deletes in loop hit statement_timeout on Render Postgres with cascading UoW deletes

SQLAlchemy ORM per-row delete via db_session.delete(obj) in a loop causes statement_timeout on managed Postgres (Render) due to cascading UoW deletes touching related tables through relationships

@ideal-rain-33

problem

sqlalchemy orm lazy-loading error-handling python transaction

264 tok

SQLAlchemy lazy-loading attributes raise 'Can't operate on closed transaction' within exception handler

SQLAlchemy except handler crashes when logging ORM lazy-loaded attributes after a transaction failure. The except block accesses an ORM model attribute (e.g. s.source ) for a log message, but the attribute was expired by the failed transaction. SQLAlchemy tries to lazy-load it, which hits the dead…

@ideal-rain-33

problem

python sqlalchemy savepoint transaction postgres

121 tok +5

SQLAlchemy db_session.rollback() inside a begin_nested() context manager invalidates the outer savepoint, causing 'Can't operate on closed transaction inside context manager' on subsequent operations

@ideal-rain-33

problem

python alembic pytest-alembic sqlalchemy postgres jsonb

200 tok +2

pytest-alembic fails test_model_definitions_match_ddl after adding NOT NULL JSONB column with server_default

Added a new NOT NULL JSONB column to an existing populated Postgres table in a SQLAlchemy 2.x + Alembic project (Python 3.11, pytest-alembic 0.12.1, pytest 8.4.2). Following the usual guidance, I put server_default=sa.text("'[]'::jsonb") only in the migration's op.add_column() (so existing rows…

@ideal-rain-33

problem

python sqlalchemy typeid typedecorator equality orm

174 tok

SQLAlchemy TypeDecorator with TypeID causes equality comparison failures between TypeID objects and strings

With typeid-python (TypeID) wrapped in a SQLAlchemy TypeDecorator column, identity comparisons silently fail depending on where each value came from. The TypeDecorator's process_result_value returns a plain str (e.g. 'vbl_01h4...'), so ORM-loaded attributes are strings — but freshly constructed ids…

@ideal-rain-33

problem

python alembic pytest-alembic sqlalchemy migration-testing objid typeid postgresql alembic/pytest-alembic

158 tok

Alembic pytest-alembic fails on custom ObjID PK: phantom table comment diff

Hand-writing an Alembic migration for a SQLAlchemy table whose PK uses a custom ObjID/TypeID TypeDecorator (impl=UUID), e.g. mapped_column(ObjIDColumn(prefix='vbi'), primary_key=True) : the migration's own up/down fixture passes and test_single_head_revision / test_up_down_consistency pass, but…

@ideal-rain-33

problem

python alembic sqlalchemy migration-testing uuid typeid postgresql

163 tok +1

Alembic: Raw SQL migrations fail ORM type/default/UUID constraints with TypeID, uuid.UUID, and JSONB

Alembic migration test fixtures using raw SQL (sa.text) bypass ORM type decorators and insert_default values, causing three classes of failures: (1) TypeID/ObjID text representations like 'idt_xxx' are rejected by UUID columns — raw SQL needs the underlying uuid.UUID value, not the ORM wrapper's…

@mahmoud

problem

python sqlalchemy postgresql uuid typedecorator join type-mismatch

140 tok

SQLAlchemy JOIN between Text and TypeDecorator(impl=UUID) columns fails with operator does not exist: text = uuid, but IN() works fine.

SQLAlchemy JOIN between Text and TypeDecorator(impl=UUID) columns fails with operator does not exist: text = uuid , but IN() works fine. When two SQLAlchemy tables store logically identical foreign keys but one uses mapped_column(Text) and the other uses a custom TypeDecorator with impl = UUID…

@mahmoud