Content-Type: multipart/alternative;
 boundary="------------0B06P41QnedBOJuK0NdM2GDB"
Message-ID: <d557623b-5941-637d-9cd3-83e02285cf8f@gmail.com>
Date: Tue, 12 Apr 2022 07:55:03 -0400
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.7.0
Subject: Re: set autocommit only for select statements
Content-Language: en-US
To: pgsql-admin@lists.postgresql.org
References: <548f2ed6-3209-705d-09e2-a5afbc6fb71c@quadratum-braccas.com>
 <bc177652-b317-0f78-cc82-466f24315549@gmail.com>
 <6bfd5d91-bb06-1b56-f4f8-cc8f90307a35@jakobs.com>
 <005201d84e4b$dce0e6b0$96a2b410$@msym.fr>
From: Mladen Gogala <gogala.mladen@gmail.com>
In-Reply-To: <005201d84e4b$dce0e6b0$96a2b410$@msym.fr>
Archived-At: 
 <https://www.postgresql.org/message-id/d557623b-5941-637d-9cd3-83e02285cf8f%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------0B06P41QnedBOJuK0NdM2GDB
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 4/12/22 05:01, Michel SALAIS wrote:
> 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

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?

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

--------------0B06P41QnedBOJuK0NdM2GDB
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 4/12/22 05:01, Michel SALAIS wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:005201d84e4b$dce0e6b0$96a2b410$@msym.fr">
      <pre class="moz-quote-pre" wrap="">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</pre>
    </blockquote>
    <p>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:</p>
    <p>ttps://www.psycopg.org/docs/connection.html#connection.autocommit</p>
    <p>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?<br>
    </p>
    <pre class="moz-signature" cols="72">-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
<a class="moz-txt-link-freetext" href="https://dbwhisperer.wordpress.com">https://dbwhisperer.wordpress.com</a>
</pre>
  </body>
</html>

--------------0B06P41QnedBOJuK0NdM2GDB--