Subject: set autocommit only for select statements

From: Sbob <[email-masked]>
Date: Mon, 11 Apr 2022 10:14:57 -0600

Hi;

Is there a way to set "autocommit = on" for all select statements and 
have "autocommit = off" for all other statements?

Thanks in advance

---

From: "David G. Johnston" <[email-masked]>
Date: Mon, 11 Apr 2022 09:19:13 -0700

> have "autocommit = off" for all other statements?
In what?  The server doesn't have an autocommit option.

But I doubt it; regardless of the answer to the previous question.

David J.

---

From: Mladen Gogala <[email-masked]>
Date: Mon, 11 Apr 2022 21:53:59 -0400

> have "autocommit = off" for all other statements?
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. That can have some 
drawbacks, but it also has some positive sides. Your lock duration is 
much shorter and you don't get lock waits. However, some things may 
surprise you:

[mgogala@umajor ~]$ psql
Password for user mgogala:
psql (13.6, server 14.2)
WARNING: psql major version 13, server major version 14.
          Some psql features might not work.
Type "help" for help.

mgogala=# select ename,sal from emp where deptno=20 for update;
  ename | sal
-------+------
  SMITH |  800
  JONES | 2975
  SCOTT | 3000
  ADAMS | 1100
  FORD  | 3000
(5 rows)

mgogala=# select l.locktype,d.datname,r.relname from pg_locks l join
mgogala-# pg_database d on (l.database=d.oid) join pg_class r on 
(l.relation=r.oid);
  locktype | datname |              relname
----------+---------+-----------------------------------
  relation | mgogala | pg_class_tblspc_relfilenode_index
  relation | mgogala | pg_class_relname_nsp_index
  relation | mgogala | pg_class_oid_index
  relation | mgogala | pg_class
  relation | mgogala | pg_locks
(5 rows)

mgogala=#

As you can see, I did "SELECT FOR UPDATE" from the table named "emp". 
When I check the locks from pg_locks, there are no locks on the "emp" 
table. That is because psql (and not the database) has executed "COMMIT" 
immediately after "SELECT FOR UPDATE", thereby ending the transaction 
and releasing the locks. However, if I open another session and do the 
following:

mgogala=# begin transaction;
BEGIN
mgogala=*# select ename,sal from emp where deptno=20 for update;
  ename | sal
-------+------
  SMITH |  800
  JONES | 2975
  SCOTT | 3000
  ADAMS | 1100
  FORD  | 3000
(5 rows)

The result of query to pg_locks is now very different:

mgogala=# select l.locktype,l.mode,d.datname,r.relname from pg_locks l join
pg_database d on (l.database=d.oid) join pg_class r on (l.relation=r.oid);
  locktype |      mode       | datname | relname
----------+-----------------+---------+-----------------------------------
_*relation | RowShareLock    | mgogala | emp_pkey*__*
*__* relation | RowShareLock    | mgogala | emp*_
  relation | AccessShareLock | mgogala | pg_class_tblspc_relfilenode_index
  relation | AccessShareLock | mgogala | pg_class_relname_nsp_index
  relation | AccessShareLock | mgogala | pg_class_oid_index
  relation | AccessShareLock | mgogala | pg_class
  relation | AccessShareLock | mgogala | pg_locks
(7 rows)

Now, there are two locks in RowShare mode on the  emp table and its 
primary key. That is because the transaction on the "emp" table has not 
finished and locks are still intact. BTW, you don't have to turn off the 
autocommit mode to use "BEGIN TRANSACTION". The morals of the story is 
that the "autocommit option" is something that regulates the behavior of 
the tool, not the database.

The point of "SELECT FOR UPDATE" is to lock certain rows in the database 
to modify them later. That will not work without "BEGIN TRANSACTION". In 
the autocommit mode, each SQL is a separate transaction, delineated by 
the transaction terminating statements by the tool executing the SQL.

Regards

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

---

From: Holger Jakobs <[email-masked]>
Date: Tue, 12 Apr 2022 08:31:15 +0200

Am 12.04.22 um 03:53 schrieb Mladen Gogala:
> "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

---

From: "Michel SALAIS" <[email-masked]>
Date: Tue, 12 Apr 2022 11:01:13 +0200

-----Message d'origine-----
De : Holger Jakobs <[email-masked]> 
Envoyé : mardi 12 avril 2022 08:31
À : [email-masked]
Objet : Re: set autocommit only for select statements

> 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

---

From: Mladen Gogala <[email-masked]>
Date: Tue, 12 Apr 2022 07:55:03 -0400

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

RDBMS, in order to be ACID compliant, deals with transactions, not with 
single statements. It is the client who starts transaction, not the 
database. It is also the client who sends commit. And yes, I am also 
using Python which allows me to set autocommit property:

ttps://www.psycopg.org/docs/connection.html#connection.autocommit

If I set autocommit connection property to True, each cursor.execute 
will send a commit. Autocommit cannot be set or disabled on the database 
level because the database deals with transactions. The point where the 
transaction is started and finished is the client. That is so for MySQL, 
that is so for Oracle and that is so for Postgres.BTW, speaking of 
Python, has anybody here tried Psycopg3? I know it was released but I am 
not sure whether it's stable enough to use in production?

---

From: Mladen Gogala <[email-masked]>
Date: Tue, 12 Apr 2022 08:00:35 -0400

> This can be proved easily by not using psql as a client, but some 

If what you are saying was true, then autocommit would be a database 
mode, not a tool mode. It isn't a database mode. Q.E.D.

---

From: Simon Riggs <[email-masked]>
Date: Tue, 12 Apr 2022 13:49:39 +0100

> have "autocommit = off" for all other statements?

Please explain what it is you are hoping to achieve?

-- 
Simon Riggs                http://www.EnterpriseDB.com/

---

From: Julien Rouhaud <[email-masked]>
Date: Tue, 12 Apr 2022 22:20:28 +0800

On Tue, Apr 12, 2022 at 08:00:35AM -0400, Mladen Gogala wrote:
> If what you are saying was true, then autocommit would be a database mode,
> not a tool mode. It isn't a database mode. Q.E.D.

That's actually mostly true.  Postgres will start an implicit transaction for
every query (or multiple queries) outside a transaction and will implicitly
commit it after a successful execution.  The only difference is there you won't
see a BEGIN / COMMIT anywhere, but the same underlying code will be executed.