Subject: Re: psycopg3 transactions
To: psycopg@lists.postgresql.org
References: <eb5cc5d20c520e32b5963a4044b70af1@paolodestefani.it>
 <CA+mi_8bXjHTrzDLQ+n_6K2MHVsr0J32X1HA28Ey9O+-Zm6JaeQ@mail.gmail.com>
 <9a99d2d110b121542d1ea0a05066858b@paolodestefani.it>
 <CA+mi_8afF2jMifiLSvL99+Ztupy1me7xDNAv0g47WxHWbwN9vw@mail.gmail.com>
 <CAH1rg6ZoQm=STwMR6AQ_FggxRxUn7qH_tLkRTfJTVYYdbTeL6w@mail.gmail.com>
From: Reuben Rissler <silrep@emypeople.net>
Message-ID: <c394395a-75c4-a076-65ac-20bdf3010953@emypeople.net>
Date: Wed, 13 Oct 2021 20:14:53 -0400
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.13.0
MIME-Version: 1.0
In-Reply-To: 
 <CAH1rg6ZoQm=STwMR6AQ_FggxRxUn7qH_tLkRTfJTVYYdbTeL6w@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------FA8A6B9C9906D9E8DC612053"
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/c394395a-75c4-a076-65ac-20bdf3010953%40emypeople.net>
Precedence: bulk

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


>
> Agree. The DBAPI choice to mandate that autocommit be off by default 
> is a strange and unfortunate choice that in my experience leads to a 
> never ending series of "surprises" such as this one.
>
> My conclusion is that the only sane thing to do is:
> 1. Only ever create connections in autocommit mode.
> 2. Only ever use `with connection.transaction()` to control transactions.
> 3. Forget that `connection.commit()` and `connection.rollback()` 
> exist, and never use them.
>
> I'm in favour of recommending this as strongly as reasonably possible 
> in the documentation.
>
> It's a shame that 1 is not the default. So you still have to remember 
> to not forget to do this explicitly, every time you create a connection.
>
> Dani


I was going to disagree with this, as some things don't work in an ACID 
way in autocommit. Then I read point 2, now I'm going to try this and 
see how I like transactions vs commit/rollback.

Reuben Rissler

--------------FA8A6B9C9906D9E8DC612053
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <br>
    <blockquote type="cite"
cite="mid:CAH1rg6ZoQm=STwMR6AQ_FggxRxUn7qH_tLkRTfJTVYYdbTeL6w@mail.gmail.com">
      <div dir="ltr">
        <div class="gmail_quote">
          <div><br>
          </div>
          <div>Agree. The DBAPI choice to mandate that autocommit be off
            by default is a strange and unfortunate choice that in my
            experience leads to a never ending series of "surprises"
            such as this one.</div>
          <div><br>
          </div>
          <div>My conclusion is that the only sane thing to do is:</div>
          <div>1. Only ever create connections in autocommit mode.</div>
          <div>2. Only ever use `with connection.transaction()` to
            control transactions.</div>
          <div>3. Forget that `connection.commit()` and
            `connection.rollback()` exist, and never use them.</div>
          <div><br>
          </div>
          <div>I'm in favour of recommending this as strongly as
            reasonably possible in the documentation.</div>
          <div><br>
          </div>
          <div>It's a shame that 1 is not the default. So you still have
            to remember to not forget to do this explicitly, every time
            you create a connection.</div>
          <div><br>
          </div>
          <div>Dani</div>
        </div>
      </div>
    </blockquote>
    <p><br>
    </p>
    <p>I was going to disagree with this, as some things don't work in
      an ACID way in autocommit. Then I read point 2, now I'm going to
      try this and see how I like transactions vs commit/rollback.</p>
    <div class="moz-signature">Reuben Rissler<br>
    </div>
  </body>
</html>

--------------FA8A6B9C9906D9E8DC612053--