On Mon, Aug 8, 2016 at 10:10 AM, Rahila Syed <rahilasyed90@gmail.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.
