Postgres setting autocommit off globally

Question

How do you set autocommit off in psql 8.4 at a global level? is there a configuration attribute that i can change that will introduce this behaviour for all dbs on a cluster to start db sessions with autocommit off?

Vaasta

There is no such configuration setting. – Mamelon

+1 for including PostgreSQL version. You don't turn autocommit off globally, you must BEGIN an explicit transaction if you don't want autocommit. – Neeley

Answer 1

Simply add the following to ~/.psqlrc:

\set AUTOCOMMIT off 

Note that this only works when using the psql shell! I assume this is what you are talking about?

Buroker

Answer 2

Use a transaction if you want want a (open) transaction:

BEGIN;
  INSERT ...;
  UPDATE ...;
COMMIT; -- when you're done

Osteomalacia

Answer 3

Like exhuma said, you can set it to off in your personal ~/.psqlrc file.

\set AUTOCOMMIT off 

But since you asked for a global level, the right file on Ubuntu is psqlrc (without leading dot) in /etc/postgresql-common/

To evaluate the right directory on your system, you may use:

pg_config --sysconfdir 

There is a sample psqlrc-file in

/usr/share/postgresql/12/psqlrc.sample

(for Version 12, obviously) but it doesn't contain further information.

Locate it on your system with

locate psqlrc 

I can't say, whether this information is valid for older versions than 12, but from my memory, it is, at least for the personal rc-file.

Scopula