Thread: Autocommit off in psql

Wolfgang Wilhelm schrieb am 05.11.2014 um 09:08:
> There's an annoying difference between the database command line
> tools. Oracle doesn't have a BEGIN for a transaction start but needs
> a commit for saving changes. Psql on the other hand requires BEGIN to
> start an transaction or it will be in autocommit mode. Guess how much
> not so nice words I've heard in the last days because my team forgets
> that transaction begin...
>
> 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!)

I have that line in my psqlrc file so autocommit is automatically turned off.
(although I rarely use psql or sqlplus)


Regards
Thomas

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

Wolfgang Wilhelm wrote:
> yes, indeed I'm a little confused. I'm not the only one, some Pg-Guys from other companies which I
> asked about the topic were a little bit puzzled, too.
> 
> If this is really a left over and just for portablity when will be time for a clean up? May be not for
> the next years' version - there should be in good PG manner just an information that this feature is
> deprecated, if the Hackers agree - but may be for the PG-version of 2017 or 2018?
> 
> 
> As you as a contributor are more included in the development of PG code, do you see chances to get
> that out of the code? If so I'm willing to have a look in the code whether there are comments about
> that feature.

Tom Lane has taken care of it before I even got to read your e-mail:
http://www.postgresql.org/message-id/E1XmB2w-0008Ho-NO@gemulon.postgresql.org
It will be gone in 9.5.

Yours,
Laurenz Albe