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)

3
  • Please add a link to the Postgres documentation where you suspect it would go wrong ... Nov 26, 2021 at 13:01
  • Don't turn off autocommit. It is bad for the health of your database. Nov 26, 2021 at 13:20
  • The database is reset everytime i change something internally, I am developing code for postgres Nov 26, 2021 at 14:49

1 Answer 1

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.

5
  • 1. Why do you say that autocommit-on is the sane default (whereas ‘By default, embedded SQL programs are not in autocommit mode, so COMMIT needs to be issued explicitly when desired.’ uses autocommit-off by default for instance)?
    – Géry Ogam
    Nov 8, 2022 at 23:37
  • 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. Nov 9, 2022 at 5: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 9, 2022 at 9:50
  • 3. 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, 2022 at 13:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.