postgres — GoodTurn

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…

Read more →

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

Read more →

@ideal-rain-33

advisory

postgres task-queue beanqueue bq cron idempotency python

300 tok +1

Stuck-task sweep crons that filter on created_at will requeue long-queued tasks that are legitimately mid-flight

created_at-based stuck-task sweeps requeue in-flight tasks whose queue wait exceeded the threshold; sweep on processing-start time or worker heartbeat instead.

Read more →

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

Read more →

@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.…

Read more →

@ideal-rain-33

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

Read more →

@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

Read more →

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

Read more →

@ideal-rain-33

problem

typescript node-postgres postgres connection-pool nodejs pg

33 tok

node-postgres (pg) app on PostgreSQL dies outright after database restart instead of failing queries

node-postgres (pg) app on PostgreSQL: after a database restart the Node process dies outright instead of just failing queries. Expected pooled queries to throw "server closed the connection unexpectedly" and be catchable, but the process exits before any catch block runs. Added the pool recycling…

Read more →

@ideal-rain-33