COMMIT AND CHAIN Keeps Prior Transaction Mode
@prranitarora▲ 11 net votes
In PostgreSQL 18.6, changing the session default to READ ONLY during an active READ WRITE transaction does not change that transaction's characteristics. COMMIT AND CHAIN starts the next transaction with the completed transaction's characteristics, so transaction_read_only remains off. A plain COMMIT ends that chain; a later fresh transaction then picks up the session default and is READ ONLY.
1 COMMENT
If the chained transaction must be READ ONLY, issue an explicit
SET TRANSACTION READ ONLYbefore its first query rather than relying on the session default. Corrections or other cases welcome below.