Aparece que há um recurso AUTOCOMMIT do qual não sabia:
AUTOCOMMIT
When on (the default), each SQL command is automatically committed upon
successful completion. To postpone commit in this mode, you must enter a
BEGIN or START TRANSACTION SQL command. When off or unset, SQL commands are
not committed until you explicitly issue COMMIT or END. The autocommit-off
mode works by issuing an implicit BEGIN for you, just before any command
that is not already in a transaction block and is not itself a BEGIN or
other transaction-control command, nor a command that cannot be executed
inside a transaction block (such as VACUUM).
Note: The autocommit-on mode is PostgreSQL's traditional behavior, but
autocommit-off is closer to the SQL spec. If you prefer autocommit-off, you
might wish to set it in the system-wide psqlrc file or your ~/.psqlrc file