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?

Melodramatize

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

Landel

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. – Melodramatize

\set AUTCOMMIT off most definitively works. Did you maybe use lowercase? Variable names in psql are case sensitive. – Landel

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? – Melodramatize

Quote from the manual "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 – Landel

To check if autocommit has been set: \echo :AUTOCOMMIT https://mcmap.net/q/531116/-how-to-check-if-autocommit-is-on-or-not-postgres-39-psql – Donothingism

Answer 2

\set AUTCOMMIT 'off';

The off value should be in single quotes

Gingerich

Typo! I've lost 'O' :( – Protocol

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;

Psychotherapy