RE: set autocommit only for select statements

-----Message d'origine-----
De : Holger Jakobs <holger(at)jakobs(dot)com>
Envoyé : mardi 12 avril 2022 08:31
À : pgsql-admin(at)lists(dot)postgresql(dot)org
Objet : Re: set autocommit only for select statements

Am 12.04.22 um 03:53 schrieb Mladen Gogala:
>
> The "autocommit" is a tool option which tells the tool whether to add
> "COMMIT" statement after each and every SQL. The RDBMS server only
> knows about transactions, as mandated by the ACID compliance. What the
> "autocommit" option of tools like psql actually does is to turn each
> of your SQL statements into a separate transaction.
>
What you write about psql sending a COMMIT statement after each statement is wrong. It may be true for other database systems.

PostgreSQL as a server commits each statement automatically unless the client has started a transaction with BEGIN or START TRANSACTION statements.

This can be proved easily by not using psql as a client, but some programming language.

Regards,

Holger

-- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
-----------------------------------------------------------------------------------

Hi,
It is a little more "complex" :-)
A session can be put in AUTOCOMMIT mode or transaction mode. Tool "psql" is by default in AUTOCOMMIT mode and can be put in transaction mode using
\set AUTOCOMMIT off

Pay attention to the variable case. It should be in capital letters. When this is done, You don't need to start transactions explicitly and the server behaves like Oracle but it is not an identical behavior. Other points matter like the reaction to errors when in a transaction...

Other programming environments could choose another default operating mode and however can offer to choose the operating mode at connection.

Michel SALAIS