Postgres set local begin/commit

Question

The documentation for set local states:

"Note that SET LOCAL will appear to have no effect if it is executed outside a BEGIN block, since the transaction will end immediately."

If I'm using SET LOCAL in the context of read only transactions do I need to indicate the end of the transaction with a COMMIT statement? Is there any difference if I do this or not?

Answer 1

If your connection is closed without a COMMIT, PostgreSQL will automatically issue a ROLLBACK. In the context of a read only transaction, this has no consequence.

If your connection stays open after your transaction, you might want to issue a ROLLBACK (or a COMMIT, but generally a ROLLBACK is less costly) in order for your next transaction to execute in a clean state.