5

I've just set up a new PostgreSQL 9.5.2, and it seems that all my transactions are auto committed.

Running the following SQL:

CREATE TABLE test (id NUMERIC PRIMARY KEY);
INSERT INTO test (id) VALUES (1);
ROLLBACK;

results in a warning:

WARNING: there is no transaction in progress
ROLLBACK

on a different transaction, the following query:

SELECT * FROM test;

actually returns the row with 1 (as if the insert was committed).

I tried to set autocommit off, but it seems that this feature no longer exists (I get the unrecognized configuration parameter error).

What the hell is going on here?

Eyal Roth
  • 3,895
  • 6
  • 34
  • 45

3 Answers3

10

autocommit in Postgres is controlled by the SQL client, not on the server.

In psql you can do this using 

\set AUTOCOMMIT off

Details are in the manual:
http://www.postgresql.org/docs/9.5/static/app-psql.html#APP-PSQL-VARIABLES

In that case every statement you execute starts a transaction until you run commit (including select statements!)

Other SQL clients have other ways of enabling/disabling autocommit.

Alternatively you can use begin to start a transaction manually.

http://www.postgresql.org/docs/current/static/sql-begin.html

psql (9.5.1)
Type "help" for help.

postgres=> \set AUTCOMMIT on
postgres=> begin;
BEGIN
postgres=> create table test (id integer);
CREATE TABLE
postgres=> insert into test values (1);
INSERT 0 1
postgres=> rollback;
ROLLBACK
postgres=> select * from test;
ERROR:  relation "test" does not exist
LINE 1: select * from test;
                      ^
postgres=>
  • The `\set` command doesn't seem to affect anything. The `begin` does. Seems like a bug to me. I'm using `psql` on Windows 10. – Eyal Roth May 04 '16 at 14:03
  • `\set AUTCOMMIT off` most definitively works. Did you maybe use lowercase? Variable names in `psql` are case sensitive. –  May 04 '16 at 14:05
  • Yeah, it works; it's case sensitive (why there's no error when I get it wrong, though?). Is there anyway to set the client to load with a default auto-commit off setting? – Eyal Roth May 04 '16 at 14:13
  • 1
    [Quote from the manual](http://www.postgresql.org/docs/9.5/static/app-psql.html#APP-PSQL-VARIABLES) "*If you prefer autocommit-off, you might wish to set it in the system-wide psqlrc file or your ~/.psqlrc file*". Details about the psqlrc file [are also in the manual](http://www.postgresql.org/docs/9.5/static/app-psql.html#AEN96655) –  May 04 '16 at 14:15
  • To check if autocommit has been set: `\echo :AUTOCOMMIT` https://stackoverflow.com/a/41298374/5986661 – Omkar Neogi Jan 06 '20 at 18:50
2
\set AUTCOMMIT 'off';

The off value should be in single quotes

hering
  • 1,956
  • 4
  • 28
  • 43
Tal Benami
  • 21
  • 1
1

This should work. \set AUTOCOMMIT off. See the example below.

account_dept=# \set AUTOCOMMIT off
account_dept=# update account set ACCT_BALANCE= acct_balance + 200 WHERE ACCT_NUM=1;
UPDATE 1
account_dept=# rollback;
NIMISHAN
  • 1,265
  • 4
  • 20
  • 29
Femsy
  • 11
  • 1