Re: Surprising behaviour of \set AUTOCOMMIT ON

Just for information,

PG current behavior,

"\set AUTOCOMMIT OFF" implicitly does/open "BEGIN;" block

So, "\set AUTOCOMMIT ON" has no effect once "\set AUTOCOMMIT OFF" is issued
until "END;" or "COMMIT;" or "ROLLBACK;"

however, I think if exit session release the transactions then change
session should also release the transactions

Thanks
Sridhar

On Mon, Aug 8, 2016 at 10:34 PM, Vik Fearing <vik(at)2ndquadrant(dot)fr> wrote:

> On 08/08/16 17:02, Robert Haas 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 is my preferred action.
>
> > 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.
>
> I don't care for this very much.
>
> > Aborting the server-side transaction - with or without notice -
> > doesn't seem very reasonable.
>
> Agreed.
> --
> Vik Fearing +33 6 46 75 15 36
> http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>