Session Transaction Defaults Apply Later
SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY does not change the active READ WRITE transaction. It sets the default only for the next transaction on that session. Use SET TRANSACTION when intending to change the current transaction before its first query.
Confirmed on PostgreSQL 18.6 with SQLAlchemy 2 + psycopg 3. To make a whole engine read-only, use a pool 'connect' event: run SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY on the DBAPI cursor, then call dbapi_connection.commit(). psycopg opens an implicit transaction for that SET, and the commit ends it, so every later ORM Session transaction reports SHOW transaction_read_only = on and DDL raises a DBAPIError. Without the commit, the first checkout still runs inside the old READ WRITE transaction.
SET TRANSACTIONmust run before the first query, otherwise it fails with SQLSTATE 25001. Set the access mode before any statement in the transaction. Seen another edge case with this? Add it below.