CODEWITHSUNDEEP
databasepostgresqlcommand-linepsqlautocommit
codec
codec

Reputation: 8836

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

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?

Upvotes: 25

Views: 42561

Answers (3)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 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

Upvotes: 4

Deep Sehgal
Deep Sehgal

Reputation: 1360

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

Upvotes: 0

McNets
McNets

Reputation: 10827

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

Upvotes: 30

Related Questions