1

I am working with PostgreSQL version 11, and would like to turn of autocommit. Either permanently or per query through a pl/pgsql command.

In the documentation it says it is turned off by default, else where it says it is turn on by default, not even the documentation is right about this, as psql does not recognize the config-parameter "autocommit", though the documentation mentions this variable explicitly.

Does anyone know how to turn it off?

(I am just running tests and "autocommit" might be one of the reasons that hinders performance)

Clebo Sevic
  • 113
  • 1
  • 1
  • 4

1 Answers1

4

Setting "autocommit" is a feature of Postgres client programs.

You can set it for the default interactive terminal psql as instructed in the manual here.

The manual also mentions the config-parameter AUTOCOMMIT for ecpg (Embedded SQL in C).

When autocommit is on (the sane default in psql), you can "turn it off" by starting a transaction manually with BEGIN. That may be what you are looking for. The manual:

BEGIN initiates a transaction block, that is, all statements after a BEGIN command will be executed in a single transaction until an explicit COMMIT or ROLLBACK is given. By default (without BEGIN), PostgreSQL executes transactions in “autocommit” mode, that is, each statement is executed in its own transaction and a commit is implicitly performed at the end of the statement (if execution was successful, otherwise a rollback is done).

Transaction handling is not possible in PL/pgSQL before Postgres 11, though. See:

But you cannot set autocommit "for PL/pgSQL". That doesn't make sense.

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
  • Doesn’t the SQL standard promote autocommit-off (because ‘In the standard, it is not necessary to issue START TRANSACTION to start a transaction block: any SQL command implicitly begins a block.’)?
    • – Géry Ogam Nov 08 '22 at 23:42
  • 1
    @Maggyero psql (interactive terminal) and ecpg (embedded SQL in C) are rather different for obvious reasons. "autocommit on" is certainly the sane default for psql. I clarified a bit above. – Erwin Brandstetter Nov 09 '22 at 05:01
  • Thanks for the clarification. It is interesting that you say that autocommit-on is better suited to interactive sessions and autocommit-off is better suited to non-interactive sessions. I suppose that it is because in interactive sessions the user requests single database operations more often than database transactions, while in non-interactive sessions the program requests database transactions more often than single database operations. – Géry Ogam Nov 09 '22 at 09:50
  • Is ‘autocommit mode’ a configuration of the database engine (e.g. PostgreSQL server)/driver (e.g. psql client), or is it a scope of SQL code outside of START TRANSACTION and COMMIT/ROLLBACK pairs? The SQLite database engine seems to use the second meaning: ‘The sqlite3_get_autocommit() interface returns non-zero or zero if the given database connection is or is not in autocommit mode, respectively. Autocommit mode is on by default. Autocommit mode is disabled by a BEGIN statement. Autocommit mode is re-enabled by a COMMIT or ROLLBACK.’
    • – Géry Ogam Nov 11 '22 at 13:31