Need community's opinion on following behaviour of \set AUTOCOMMIT
If \set AUTOCOMMIT ON is issued after \set AUTOCOMMIT OFF the commands which follow after AUTOCOMMIT is set ON are not committed until an explicit COMMIT is issued. Its can be surprising to the user to not see results of the commands fired after AUTOCOMMIT is set to ON.
bash-4.2$ psql -d postgres -U rahila psql (9.6beta3) Type "help" for help.
postgres=# \set AUTOCOMMIT OFF postgres=# create table test1(i int); CREATE TABLE postgres=# \set AUTOCOMMIT ON postgres=# create table test2(j int); CREATE TABLE postgres=# \c postgres rahila You are now connected to database "postgres" as user "rahila". postgres=# \dt; No relations found.
The ongoing transaction is left running when there is this change in mode from AUTOCOMMIT OFF to AUTOCOMMIT ON. This happens because \set AUTOCOMMIT ON is fired within a transaction block started when first command after \set AUTOCOMMIT OFF is executed. Hence it requires an explicit COMMIT to be effective.
Should changing the value from OFF to ON automatically either commit or rollback transaction in progress?
FWIW, running set autocommit through ecpg commits the ongoing transaction when autocommit is set to ON from OFF. Should such behaviour be implemented for \set AUTOCOMMIT ON as well?
I dislike automatic commit or rollback here. What about raising warning if some transaction is open?
