Re: Surprising behaviour of \set AUTOCOMMIT ON

On Tue, Aug 9, 2016 at 1:02 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> 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.
>

This makes more sense as the user who is doing it would realise that the
transaction has been left open.

> 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.
>

Issuing commit would indicate that, open transactions will be committed
which is not a good idea in my opinion. If the user is issuing AUTOCOMMIT =
ON, then it means all the transactions initiated after issuing this must be
committed, whereas it is committing the previously pending transactions as
well.

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

Agreed. Traditionally, open transactions in the database must be left open
until user issues a COMMIT or ROLLBACK. If the session is changed or
killed, then, the transaction must be rolled back.

Regards,
Venkata B N

Fujitsu Australia