sqlpostgresqltransactionsautocommit

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

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?

Solution

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