Re: Surprising behaviour of \set AUTOCOMMIT ON

2016-08-03 12:16 GMT+02:00 Rahila Syed <rahilasyed90(at)gmail(dot)com>:

> Hello,
>
> 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?

Regards

Pavel

>
> Thank you,
> Rahila Syed
>