How to check if autocommit is on or not postgres' psql

postgresql autocommit
codec picture codec · Dec 23, 2016 · Viewed 23.8k times · Source

I am using postgres 9.5. How can I check if auto commit is on or off? I tried SHOW AUTOCOMMIT where I got ERROR: unrecognized configuration parameter "autocommit" then I did a \set autocommit off and then SHOW AUTOCOMMIT gives me blank output. How can identify if autocommit is on or off? Also can I set it to off while/after the database in created in my sql file?

Answer

McNets picture McNets · Dec 23, 2016

According to this Dustin Marx article, you can use:

\echo :AUTOCOMMIT

If it's desired to "always" have autocommit disabled, the \set AUTOCOMMIT off meta-command can be added to one's local ~/.psqlrc file. For an even more global setting, this meta-command can be placed in apsqlrc file in the database's system config directory (which can be located using PostgreSQL operating system-level command pg_config --sysconfdir).

Related questions

Large Objects may not be used in auto-commit mode

I have a Spring application which uses Hibernate on a PostgreSQL database. I'm trying to store files in a table of the database. It seems it stores the row with the file (I just use persist method on EntityManager), but …

Read More
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 …

Read More
Postgres setting autocommit off globally

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?

Read More