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

Asked 23/12, 2016 at 9:3 Answered 9/9, 2022 at 20:29

Solved database postgresql command-line psql autocommit

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?

Anastassia answered 23/12, 2016 at 9:3 Comment(2)

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. – Preussen 23/12, 2016 at 9:10

yeah in postgres auto commit is on by default. we can switch it off using \set AUTOCOMMIT off – Anastassia 23/12, 2016 at 9:14

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

Disoperation answered 23/12, 2016 at 9:9 Comment(7)

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? – Anastassia 23/12, 2016 at 9:12

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 – Anastassia 23/12, 2016 at 9:22

@love2code: put the set statement into ~/.psqlrc as mentioned in the answer – Millham 23/12, 2016 at 9: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. – Anastassia 23/12, 2016 at 9:50

I guess the error is in using \set in the .psqlrc - it should be plain set autocommit = off – Iguana 28/7, 2017 at 10:17

I think set autocommit = off in .psqlrc only works in older versions. – Dyad 28/11, 2018 at 22:6

Works great on Postgres 14. Thanks! – Hithermost 13/2, 2022 at 10:16

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

Kirkwood answered 9/9, 2022 at 20:29 Comment(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

Sentimentalize answered 6/8, 2019 at 18:1 Comment(0)

Recommended topics

Hot tags