Re: Surprising behaviour of \set AUTOCOMMIT ON

On Mon, Aug 8, 2016 at 10:10 AM, Rahila Syed <rahilasyed90(at)gmail(dot)com> wrote:
> Thank you for inputs everyone.
>
> The opinions on this thread can be classified into following
> 1. Commit
> 2. Rollback
> 3. Error
> 4. Warning
>
> As per opinion upthread, issuing implicit commit immediately after switching
> autocommit to ON, can be unsafe if it was not desired. While I agree that
> its difficult to judge users intention here, but if we were to base it on
> some assumption, the closest would be implicit COMMIT in my opinion.There is
> higher likelihood of a user being happy with issuing a commit when setting
> autocommit ON than a transaction being rolled back. Also there are quite
> some interfaces which provide this.
>
> As mentioned upthread, issuing a warning on switching back to autocommit
> will not be effective inside a script. It won't allow subsequent commands to
> be committed as set autocommit to ON is not committed. Scripts will have to
> be rerun with changes which will impact user friendliness.
>
> While I agree that issuing an ERROR and rolling back the transaction ranks
> higher in safe behaviour, it is not as common (according to instances stated
> upthread) as immediately committing any open transaction when switching back
> to autocommit.

I think I like the option of having psql issue an error. On the
server side, the transaction would still be open, but the user would
receive a psql error message and the autocommit setting would not be
changed. So the user could type COMMIT or ROLLBACK manually and then
retry changing the value of the setting.

Alternatively, I also think it would be sensible to issue an immediate
COMMIT when the autocommit setting is changed from off to on. That
was my first reaction.

Aborting the server-side transaction - with or without notice -
doesn't seem very reasonable.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company