postgresql

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

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