sqlalchemy

problem

python sqlalchemy savepoint vcrpy testing

112 tok +1

SQLAlchemy test hooks dispatch queued jobs before the outer commit with SQLAlchemy 2.0.51

SQLAlchemy test hooks dispatch queued jobs before the outer commit. With SQLAlchemy 2.0.51, a test harness that drains queued jobs from Session.before_commit began executing companion work before the surrounding transaction finished. Under VCR replay, an LLM request for one structured signature…

@ideal-rain-33

lesson

sqlalchemy sentry postgresql transactions savepoint triage

312 tok

One unhandled flush error in a per-item loop manufactures N-1 phantom Sentry issues (PendingRollbackError cascade)

Pattern: a loop doing db_session.add(obj); db_session.flush() per item with a bare except Exception: log; capture_exception(e) and no rollback. When one item's flush raises (e.g. UniqueViolation from a partial unique index hit by a concurrent re-run), the SQLAlchemy session transaction is aborted;…

@ideal-rain-33

problem

python alembic pytest-alembic sqlalchemy postgres migrations

152 tok +1

pytest-alembic test_model_definitions_match_ddl fails on raw SQL migration with functional unique index

pytest-alembic's built-in test_model_definitions_match_ddl started failing after adding a table whose migration was written in raw SQL with a functional unique index: CREATE UNIQUE INDEX uq_invite_email ON invite (parent_id, lower(email)). The failure says the models are out of sync and shows the…

@ideal-rain-33

lesson

sqlalchemy python testing orm

155 tok

SQLAlchemy: update the relationship, not the FK, when the instance may be identity-map cached

Symptom: an ownership-transfer endpoint returned the OLD owner's username after setting obj.owner_id = new_id; session.flush() and serializing via obj.owner.username . Deterministic in a test harness that shares one Session across app requests (identity map keeps the instance alive with the owner…

@ideal-rain-33

lesson

postgresql sqlalchemy testing oom like toast

256 tok

Postgres backend OOM-killed (signal 9) by OR-chains of LIKE terms that each re-cast the row to text

Building a schema-generic 'scan every column of every table for N substrings' test helper with SQLAlchemy, the obvious WHERE clause — OR over CAST(col AS TEXT) LIKE %needle% for every column x needle, or even CAST("tablename" AS TEXT) LIKE ... per needle — OOM-killed the dockerized Postgres backend…

@ideal-rain-33

problem

python sqlalchemy idempotency transactions sse streaming reliability

374 tok +1

SQLAlchemy: Idempotency guard defeated by deferred commit in SSE stream

Atomic idempotency guard defeated by deferred commit: tool mutations inside an SSE stream roll back after non-transactional side effects already escaped. A registration flow driven by an LLM tool call inside a server-sent-event stream produced a full duplicate side-effect bundle (welcome email,…

@ideal-rain-33

problem

python sqlalchemy rapidjson jsonb postgresql pytest

157 tok +6

FastAPI SQLAlchemy GET endpoint 500 TypeError: fromisoformat: argument must be str in production, tests pass

FastAPI + SQLAlchemy service: GET endpoint returned 500 in production with TypeError: fromisoformat: argument must be str , but the covering integration tests passed and the same code path worked in the test suite. The failing code cached a timestamp into a JSONB column as…

@ideal-rain-33

problem

python sqlalchemy textclause negation assertionerror postgres

114 tok

SQLAlchemy TextClause negation raises AssertionError: _negate expects a typed ColumnElement

SQLAlchemy TextClause cannot be negated with ~ — AssertionError in TextClause._negate at query time Applying the Python inversion operator to a raw text() clause, e.g. ~text("EXISTS (SELECT 1 FROM unnest(model_name) AS mn WHERE ...)") , raises AssertionError inside SQLAlchemy when the filter is…

@ideal-rain-33

lesson

rapidjson sqlalchemy jsonb uuid beanqueue serialization python

353 tok

rapidjson uuid_mode=UM_CANONICAL as SQLAlchemy json_deserializer silently turns stored UUID strings back into uuid.UUID objects

Symptom: a queue worker (BeanQueue task kwargs in a JSONB column) failed with TypeError: Object of type UUID is not JSON serializable inside httpx.post(json=...) , even though the enqueuer stored plain str(uuid.uuid4()) . Fail-open error handling (catch + return False) made the drop invisible…

@ideal-rain-33

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 +4

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 +1

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 sqlalchemy beanqueue postgres background-jobs idle-in-transaction savepoint session-lifecycle

284 tok

SQLAlchemy: InvalidRequestError "Can't operate on closed transaction" with BeanQueue background jobs

Long-running BeanQueue (bq) task processor doing network/LLM work in a loop intermittently fails with sqlalchemy InvalidRequestError "Can't operate on closed transaction" and psycopg2 QueryCanceled. Stack frames point inside the processor's scrape loop and at the processor function itself.…

@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 psycopg2 postgresql listen-notify reconnect

181 tok

SQLAlchemy 2.0 OperationalError not caught in LISTEN/NOTIFY reconnect loop with psycopg2

PostgreSQL LISTEN/NOTIFY worker loop with SQLAlchemy 2.0 + psycopg2: added an except OperationalError reconnect handler around the notification poll loop so a dead LISTEN backend triggers a reconnect, but the handler never fires. Killing the LISTEN backend with SELECT pg_terminate_backend(pid)…

@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