Re: Surprising behaviour of \set AUTOCOMMIT ON

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.

Thank you,
Rahila Syed

On Sun, Aug 7, 2016 at 4:42 AM, Matt Kelly <mkellycs(at)gmail(dot)com> wrote:

> Its worth noting that the JDBC's behavior when you switch back to
> autocommit is to immediately commit the open transaction.
>
> Personally, I think committing immediately or erroring are unsurprising
> behaviors. The current behavior is surprising and obviously wrong.
> Rolling back without an error would be very surprising (no other database
> API I know of does that) and would take forever to debug issues around the
> behavior. And committing after the next statement is definitely the most
> surprising behavior suggested.
>
> IMHO, I think committing immediately and erroring are both valid. I think
> I'd prefer the error in principle, and per the law of bad code I'm sure,
> although no one has ever intended to use this behavior, there is probably
> some code out there that is relying on this behavior for "correctness". I
> think a hard failure and making the dev add an explicit commit is least
> likely to cause people serious issues. As for the other options, consider
> me opposed.
>
> - Matt K.
>