Re: Autocommit off in psql

Wolfgang Wilhelm wrote:
> Thomas Kellerer <spam_eater@gmx.net> schrieb am 9:25 Mittwoch, 5.November 2014:
>>> Is there any way to make psql work a little bit more like sqlplus?
>>> "Set autocommit off" is obviously no solution as it's not valid
>>> anymore.

>> You can use
>> 
>>   \set AUTOCOMMIT off
>> 
>> in psql to turn off autocommit mode (note that this is case-sensitive!)> first of all thank you for your answer.

> This is basically what I found via Google, too, but is that up to date information? I found some more
> info that that setting isn't valid anymore.
> 
> When I do that command which you sent, it seems to execute but when I do
> 
> 
> show AUTOCOMMIT;
> 
> 
> I get as as a result:
> 
>  autocommit
> ------------
>  on
> 
> (1 line)
> 
> When I do the command which you mentioned with a semicolon at the end I get an error message: Unknow
> boolean value: "on" assumed - well, at least I guess it should be something like that because I get
> that in german.
> 
> I tried with several other boolean values like 0, false, none, all with the same result of show
> AUTOCOMMIT;
> 
> Just a minute ago I realized that the output of show AUTOCOMMIT is somehow, well, misleading. First I
> did setting autocommit off. Then I inserted some data in a table. Another insert, a third. If
> autocommit would be on I'd expect it to do a commit after every insert. But a rollback made them all
> three disappear. I found that in the help text of the show command AUTOCOMMIT is not included. The
> question is why show outputs some info. But that shouldn't be your problem.

The autocommit parameter you see with SHOW is a leftover of the removed server feature.
It is there for compatibility reasons (I guess) but is read-only:

test=> SET autocommit=off;
ERROR:  SET AUTOCOMMIT TO OFF is no longer supported

You are getting confused because this is a server-side setting (and indeed, there is no way
to turn autocommit off on the server side), whereas \set AUTOCOMMIT is a client side feature.
The latter works by automatically inserting a BEGIN at the appropriate time; nothing
changes on the server side.

Yours,
Laurenz Albe