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 block, so I matched query LIKE 'SAVEPOINT%' — zero backends matched, ever, even though the session was demonstrably 'idle in transaction'. After switching the fingerprint to the ORM SELECT that opened the transaction, the kill worked but then the test's own session backend got terminated too, failing teardown on Session.remove() with:
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.raised from dbapi_connection.rollback() inside Connection._rollback_impl — even though that session had only done add() + commit() and its last statement should have been COMMIT.
Two separate SQLAlchemy behaviors break pg_stat_activity fingerprinting:
1. Session.begin_nested() emits SAVEPOINT lazily (since 1.4). Entering the context manager sends no SQL. The SAVEPOINT statement is only emitted when the session next executes something inside the block. If the code inside the block never touches that session (e.g. it does its work on a separate engine/connection, or is pure Python), no SAVEPOINT ever hits the wire and pg_stat_activity.query still shows whatever statement opened the transaction (typically the initial ORM SELECT). Pre-1.4 behavior (immediate SAVEPOINT) is what intuition expects, and the context-manager form makes it look transactional-on-entry.
2. Reading an expired attribute after commit() silently re-fingerprints the backend. With default expire_on_commit=True, obj.id after session.commit() triggers a refresh SELECT ... FROM <table> WHERE <table>.id = ... and — because sessions autobegin — leaves the backend 'idle in transaction' with that SELECT as its last statement. Any pg_stat_activity pattern matching the target session's fetch-by-id SELECT will also match the observer session that merely read an attribute.
Working recipe for killing exactly one session's backend:
# target: a worker session blocked inside processing, opened with
# session.query(Task).filter(Task.id == task_id).one()
# fingerprint = the multi-line ORM SELECT; LIKE '%' crosses newlines fine
pattern = "SELECT%FROM bq_tasks%WHERE bq_tasks.id =%"
# in the test session: neutralize your own fingerprint first —
# reading task.id refreshed the row and left an open transaction
task_id = task.id
session.rollback() # last statement becomes ROLLBACK; pattern no longer matches
killed = conn.execute(text(
"SELECT pid, pg_terminate_backend(pid) FROM pg_stat_activity"
" WHERE datname = current_database() AND pid != pg_backend_pid()"
" AND query LIKE :pat"), {"pat": pattern}).all()General rule: pg_stat_activity.query shows the last statement sent to the server, which for SQLAlchemy sessions is often not the last ORM operation you performed — lazy SAVEPOINTs, autobegin, and expire-on-commit refreshes all decouple the two.