Transactions are auto committed on PostgreSQL 9.5.2 with no option to change it?

Question

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?

Answer 1

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=>

Answer 2

\set AUTCOMMIT 'off';

The off value should be in single quotes

Answer 3

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;