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

Question

I'm 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?

Good question. Autocommit seems to be always ON and I also got the same messages when I tried to see setting. PG 9.4 was at least showing text that autocommit cannot be set to OFF. But 9.5 and 9.6 gives this error although documentation still contains set and show autocommit commands. — JosMac, Dec 23 '16 at 09:10
yeah in postgres auto commit is on by default. we can switch it off using `\set AUTOCOMMIT off` — codec, Dec 23 '16 at 09:14

McNets · Accepted Answer · 2016-12-23T09:16:46.227

25

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).

edited Dec 23 '16 at 09:16

answered Dec 23 '16 at 09:09

McNets

10,352
3
32
61

1

Thanks. what is the best place to set it off permanently? In my application I have a sql file where I create the database. Should I set it off there? – codec Dec 23 '16 at 09:12
1

I got `You need to install postgresql-server-dev-X.Y for building a server-side extension or libpq-dev for building a client-side application.` when I did `pg_config --sysconfdir` – codec Dec 23 '16 at 09:22
@love2code: put the `set` statement into `~/.psqlrc` as mentioned in the answer – Dec 23 '16 at 09:35
I did try that. cat ~/.psqlrc gives `-- -- system-wide psql configuration file -- -- This file is read before the .psqlrc file in the user's home directory. -- -- Copy this to your installation's sysconf directory and rename it psqlrc. -- The sysconf directory can be identified via "pg_config --sysconfdir". -- \set AUTOCOMMIT off` logged out and logged in again but `echo :AUTOCOMMIT` is always on. – codec Dec 23 '16 at 09:50
I guess the error is in using `\set` in the .psqlrc - it should be plain `set autocommit = off` – DejanLekic Jul 28 '17 at 10:17
I think `set autocommit = off` in .psqlrc only works in older versions. – Kjetil S. Nov 28 '18 at 22:06
Works great on Postgres 14. Thanks! – Binita Bharati Feb 13 '22 at 10:16

score 1 · Answer 2 · edited Feb 09 '23 at 05:17

1

With the command below on psql, you can check if AUTOCOMMIT is on or off. *AUTOCOMMIT must be uppercase:

\echo :AUTOCOMMIT

This is if AUTOCOMMIT is on:

postgres=# \echo :AUTOCOMMIT
on

This is if AUTOCOMMIT is off:

postgres=# \echo :AUTOCOMMIT
off

edited Feb 09 '23 at 05:17

Henry Ecker

34,399
18
41
57

answered Sep 09 '22 at 20:29

Super Kai - Kazuya Ito

22,221
10
124
129

score 0 · Answer 3 · answered Aug 06 '19 at 18:01

0

One quick and dirty way is to just execute commit; and look for the below message to infer auto-commit is on or not.

Error report - SQL Error: Cannot commit when autoCommit is enabled.

Note: I used SQL developer so not sure about other IDEs

answered Aug 06 '19 at 18:01

Deep Sehgal

1,052
9
13

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

3 Answers3

Linked