One potential surprise for someone familiar with Oracle database‘s SQL*Plus when being introduced to PostgreSQL database‘s psql may be psql‘s default enabling of autocommit. This post provides an overview of psql’s handling of autocommit and some related nuances.
By default, Oracle’s SQL*Plus command-line tool does not automatically commit DML statements and the operator must explicitly commit these statements as part of a transaction (or exit from the session without rolling back). Because of this, developers and administrators familiar with using SQL*Plus to work with the Oracle database might be a bit surprised when the opposite is true for PostgreSQL and its psql command-line tool. Auto-commit is turned on by default in psql, meaning that every statement (including DML statements such as INSERT, UPDATE, and DELETE statements) are automatically committed once submitted.
One consequence of PostgreSQL’s psql enabling autocommit by default is that COMMIT statements are unnecessary. When one tries to submit a commit; in psql with autocommit enabled, the WARNING-level message “there is no transaction in progress” is shown. This is demonstrated in the next screen snapshot.
The remainder of this post looks at how to turn off this automatic committing of all manipulation statements in psql.
One often cited approach to overriding psql‘s autocommit default is to explicitly begin a transaction with the BEGIN keyword and then psql won’t commit until an explicit commit is provided. However, this can become a bit tedious over time and fortunately PostgreSQL’s psql provides a convenient way of configuring psql to have autocommit disabled.
Before getting into the easy approach used to disable autocommit in psql, I’ll point out here that one should not confuse the advise for ECPG (Embedded SQL in C). When using ECPG, the “SET AUTOCOMMIT” section of the PostgreSQL documentation on ECPG applies. Although this only applies to ECPG and does NOT apply to psql, it might be easy to not realize that as one of the first responses to a Google search for “psql autocommit” is this ECPG-specific manual page. That ECPG-specific manual page states that the command looks like “SET AUTOCOMMIT { = | TO } { ON | OFF }” and adds, “By default, embedded SQL programs are not in autocommit mode, so COMMIT needs to be issued explicitly when desired.” This is like Oracle’s SQL*Plus and is not how psql behaves by default.
Fortunately, it’s very easy to disable autocommit in psql. One merely needs to enter the following at the psql command prompt (AUTOCOMMIT is case sensitive and should be all uppercase):
\set AUTOCOMMIT off
This simple command disables autocommit for the session. One can determine whether autocommit is enabled with a simple \echo meta-command like this (AUTOCOMMIT is case sensitive and all uppercase and prefixed with colon indicating it’s a variable):
\echo :AUTOCOMMIT
The next screen snapshot demonstrates the discussion so far. It uses an \echo to indicate the default nature of autocommit (on) and how use of \set AUTOCOMMIT allows it to be disabled (off).
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 a psqlrc file in the database’s system config directory (which can be located using PostgreSQL operating system-level command pg_config --sysconfdir as shown in the next screen snapshot).
One last nuance to be wary of when using psql and dealing with autocommit, is to realize that show AUTOCOMMIT; is generally not useful. In PostgreSQL 9.5, as the next screen snapshot demonstrates, an error message makes it clear that it’s not even available anymore.
Conclusion
Although autocommit is enabled by default in PostgreSQL database’s psql command-line tool, it can be easily disabled using \set AUTOCOMMIT off explicitly in a session or via configuration in the personal ~/.psqlrc file or in the global system configuration psqlrc file.
| Reference: | AutoCommit in PostgreSQL’s psql from our JCG partner Dustin Marx at the Inspired by Actual Events blog. |
