Does autocommit in postgresql mean you cannot have transaction blocks?

Question

if autocommit is on, which is default, postgresql treats each statement as a transaction.

so if i have an explicit transaction block with start transaction and commit, does that mean that block is not executed atomically and cannot be rolled back? (since each statement inside the block would be a transaction in itself due to autocommit).

Answer 1

It's the opposite.

With autocommit turned on, using start transaction is the only way to combine multiple statements into a single transaction.

In fact, Postgres always uses autocommit on the server, unless the client uses start transaction (or begin transaction). When you turn off auto-commit the client will simply send those statements for you automatically.

Quote from the manual

BEGIN initiates a transaction block, that is, all statements after a BEGIN command will be executed in a single transaction until an explicit COMMIT or ROLLBACK is given. By default (without BEGIN), PostgreSQL executes transactions in “autocommit” mode

^{(Emphasis mine)}

BEGIN is the same as START TRANSACTION

Answer 2

Autocommit only affects statements outside of a transaciton. When you do your own start transaction and commit, autocommit has no effect.

When a statement is run outside a transaction, autocommit

• ON means that means it is committed when it finishes
• OFF means that a new transaction is implicitly started, and an explicit commit is required to commit the statement