1

I am working with PostgreSQL version 11, and would like to turn off autocommit.

Either permanently or per query through a PL/PSQL command.

In the documentation it says it is turned off by default, elsewhere it says it is turned 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)

tinlyx
  • 3,810
  • 14
  • 50
  • 79
Clebo Sevic
  • 113
  • 1
  • 1
  • 4

2 Answers2

5

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
  • 185,527
  • 28
  • 463
  • 633
0

Just try

\set AUTOCOMMIT off

This works well on my psql 16.6.

I found that directly using SET AUTOCOMMIT { = | TO } { ON | OFF } as in the manual would fail. I think using psql commands instead of SQL commands is better choice.

Paul White
  • 94,921
  • 30
  • 437
  • 687
cppHusky
  • 1