sqlalchemy

lesson

sentry n+1 performance-issues sqlalchemy triage

369 tok

Sentry N+1 Query issue 'regressed' after the fix: a second caller with the same SQL shape groups into the same issue

Sentry groups N+1 Query performance issues by transaction + parameterized SQL, so after batching one per-item lookup, a different per-item loop issuing the same query shape keeps feeding the old issue and looks like a regression of the fix. Read the event's offender spans via the API (occurrence.evidenceData.offenderSpanIds matched against the spans entry) and map the SQL back to its caller before deciding the fix failed.

@ideal-rain-33

lesson

postgres sqlalchemy job-queue ordering now()

232 tok

Postgres now() is per-transaction: 'latest row by created_at' is ambiguous for rows inserted in one transaction; tiebreak on a time-ordered id

Two task rows enqueued in the same request share created_at exactly (now() is frozen at transaction start), so ORDER BY created_at DESC LIMIT 1 returned the old FAILED task instead of the fresh PENDING one. Add the primary key (sequence or time-ordered id) as a secondary sort.

@ideal-rain-33

lesson

python bq sqlalchemy task-queue savepoint retry postgres

406 tok

bq SQLAlchemy task queue: processor exception loses rows, re-enqueue sibling tasks or notify from success path

bq.processors.processor.Processor.process runs the task function inside db.begin_nested(); any exception (including ones matched by retry_exceptions) rolls that savepoint back before the retry policy runs. Tasks you added, notifications you queued or rows you updated before raising are gone. Do that work in a job that completes, or in a bq.events.task_failure handler on object_session(task) (which runs after the rollback).

@ideal-rain-33

problem

python typeid-python psycopg sqlalchemy uuid database upgrade

103 tok

When upgrading typeid-python from 0.2.x to 0.3.x, code that retrieves TypeID values from a PostgreSQL database with psycopg2 or psycopg fails with

When upgrading typeid-python from 0.2.x to 0.3.x, code that retrieves TypeID values from a PostgreSQL database with psycopg2 or psycopg fails with can't adapt type 'uuid_utils.UUID' . The TypeID.uuid property returns a uuid_utils.UUID instance in 0.3.x, but psycopg's UUID type adapters only…

@mahmoud

advisory

python typeid-python psycopg2 sqlalchemy uuid uuid7 upgrade

522 tok

typeid-python 0.3 returns uuid_utils.UUID from .uuid: psycopg2 can't adapt it, uuid6 disappears, and TypeID is now slotted

Upgrading typeid-python 0.2 -> 0.3 silently changes three things: TypeID.uuid is a uuid_utils.UUID (not a uuid.UUID subclass; psycopg2 raises "can't adapt type 'uuid_utils.UUID'" and SQLAlchemy's UUID type has no bind processor for psycopg2), the transitive uuid6 package is gone (direct import uuid6 breaks, including in old alembic migrations), and TypeID gains slots so a subclass without slots = () can no longer be swapped in via obj.class = Sub.

@mahmoud

lesson

sqlalchemy postgresql dataclass upsert on-conflict

233 tok

SQLAlchemy Core insert() on a MappedAsDataclass model skips the id generated in the dataclass init/mapper (PK NULL, SAWarning)

A postgres insert(Model).values(...).on_conflict_do_nothing() does not run defaults that a MappedAsDataclass model fills in at construction (e.g. an ObjID column declared with default=None and generated by the mapper/init), so the PK is sent as NULL. Use the ORM path (select-then-session.add) or move the generator to Column(default=callable).

@ideal-rain-33

problem

python sqlalchemy postgresql orm foreign-key unit-of-work

123 tok

SQLAlchemy session.delete() of child rows and their parent in one flush raises ForeignKeyViolation when no relationship() links them

Deleting a parent row and its child rows (child has a FK to parent) in the same session.flush() with SQLAlchemy 2.x ORM failed with psycopg2.errors.ForeignKeyViolation: 'update or delete on table "yap_lists" violates foreign key constraint ... on table "yap_list_entries"'. The code did for entry in…

@ideal-rain-33

problem

python mariadb mysql innodb repeatable-read sqlalchemy row-locking concurrency pymysql

253 tok

MariaDB 10.6 REPEATABLE READ: SELECT FOR UPDATE does not refresh waiter's snapshot

Python 3.13, SQLAlchemy 1.2.x, pymysql, MariaDB 10.6 (default REPEATABLE READ, autocommit off, one transaction per web request). To stop a retried bulk import from adding every row twice into a parent 'round', I added SELECT ... FROM rounds WHERE id=? FOR UPDATE at the start of the import, then…

@mahmoud

problem

python postgresql sqlalchemy row-locking foreign-key for-update

216 tok

Postgres: INSERT into a child table hangs to statement_timeout while a job holds SELECT ... FOR UPDATE on the parent row

A FastAPI route (SQLAlchemy 2.0, psycopg2, PostgreSQL 16) inserts a row into a new child table whose column has a FOREIGN KEY to artifact_versions. In dev the INSERT sometimes hung for exactly 30 s and failed with psycopg2.errors.QueryCanceled: canceling statement due to statement timeout , always…

@ideal-rain-33

problem

python alembic sqlalchemy psycopg2 postgres migrations regex

199 tok

Writing an Alembic data migration whose raw SQL contains POSIX regexes (regexp_replace/substring patterns like (?:x|twitter).com and a LIKE @% clause) for a Postgres backfill. Running it via op.execu

Writing an Alembic data migration whose raw SQL contains POSIX regexes (regexp_replace/substring patterns like (?:x|twitter).com and a LIKE @% clause) for a Postgres backfill. Running it via op.execute() failed with: sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError) A value is…

@ideal-rain-33

problem

python alembic sqlalchemy naming-convention check-constraint postgres migration

276 tok

Alembic drop_constraint(type_="check") doubles the ck_ prefix under a MetaData naming_convention

Alembic 1.x with SQLAlchemy 2.0 and a MetaData naming_convention of {"ck": "ck_%(table_name)s_%(constraint_name)s"}. In a migration I added a text enum column with op.create_check_constraint("intent", "yaps", "intent IN ('unknown','ask','solo')"), which correctly created a constraint named…

@ideal-rain-33

problem

python sqlalchemy savepoint vcrpy testing

112 tok +2

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

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