Re: Adding Connection.autocommit as standard extension to DB-API 2.0 (PEP 249)

--===============5763468824346141401==
Content-Type: multipart/alternative;
 boundary=517b15d7b77c45f1a8f724da1ed1ceaa

--517b15d7b77c45f1a8f724da1ed1ceaa
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable



On Thu, Nov 3, 2022, at 3:51 PM, Marc-Andre Lemburg wrote:
> On 03.11.2022 19:25, Tony Locke wrote:
>> Hello all, with pg8000 if autocommit is turned on while a transaction=
 is in progress, the autocommit behaviour only takes effect after the cu=
rrent transaction has been explicitly committed or rolled back. Of the o=
ptions given, I'd go for option 1, raising an exception. I don't like th=
e idea of SQL statements being executed implicitly, which is why I'd be =
against option 2.
> That's a fair point, but please remember that no statements are execut=
ed. The transaction control is completely with the driver library and ne=
gotiated with the server (some use implicit statements for this, but tha=
t's just a technical detail). In fact, users should *not* use transactio=
n statements on connections managed by drivers. That's why we have .roll=
back() and .commit() as separate APIs in the DB-API.
>=20
> I think we've already settled on option 3, with the added note that ma=
ny database modules implement option 2. pg8000 would not be one of those=
, but that's not a problem.
>=20
> Writing to the attribute will be deprecated at the same time as docume=
nting it, so that we can prepare for DB-API 3.0 using a method instead. =
This would then also address your concern.
>=20
>>=20
>> A radical suggestion for DBAPI-3.0 would be to remove autocommit comp=
letely. I think this would make the dbapi interface easier for people to=
 understand, because there would be no implicit SQL commands, which is w=
hat I think confuses people. Of course the downside is less portability,=
 but I think people accept that if they change their database a lot of t=
hings will be different, and autocommit is just one of them. A higher le=
vel library such as SQLAlchemy could still emit the correct SQL via the =
dialect so that users of SQLAlchemy wouldn't need to know about autocomm=
it.
> That's not going to work out :-) Autocommit is essential for some data=
base operations, e.g. to avoid locking. Many databases also don't permit=
 running certain DDL statements inside transactions.
>=20

I dont think I got Tony's email directly, but re: autocommit, the fact t=
hat basically all DBAPIs now implement autocommit is very advantageous t=
o SQLAlchemy and in version 2.0 we have finally removed all semblances o=
f SQLAlchemy's original homegrown "autocommit" feature, which we now ref=
er towards as "library level autocommit" in contrast to "driver level au=
tocommit".       What's important about "autocommit" is that in most cas=
es, it doesnt equate to the driver emitting "COMMIT" after every SQL sta=
tement, it instead allows the driver to forego emitting BEGIN in the fir=
st place, and the database itself runs in its own "autocommit" mode, whi=
ch is typically higher performing.

With DBAPI including the behavior, this first off allows us to simplify =
our library, as users who want to work in "autocommit" fashion can do so=
 by just setting up the driver to work in this way, without us having to=
 provide / test entirely different ways of working.  But that's only the=
 beginning of the advantages.    The bigger advantage is that support fo=
r many other styles of database connectivity is supported, including for=
 some PGBouncer configurations which don't tolerate transaction boundari=
es well, connecting to read-only replica databases where the overhead of=
 DBAPI's implicit "BEGIN" followed by our necessary ".rollback()" for a =
pooled connection can be removed (this is a big MySQL / MariaDB use case=
), and of course we can support the various DDL scenarios (mostly on Pos=
tgreSQL) that require autocommit.    When SQLAlchemy used "library level=
 autocommit", we had no way to affect that the DBAPI was still emitting =
"BEGIN" and that our pooled connections still had to use ROLLBACK.



--517b15d7b77c45f1a8f724da1ed1ceaa
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE html><html><head><title></title><style type=3D"text/css">p.Mso=
Normal,p.MsoNoSpacing{margin:0}</style></head><body><div><br></div><div>=
<br></div><div>On Thu, Nov 3, 2022, at 3:51 PM, Marc-Andre Lemburg wrote=
:<br></div><blockquote type=3D"cite" id=3D"qt" style=3D""><div class=3D"=
qt-moz-cite-prefix">On 03.11.2022 19:25, Tony Locke wrote:<br></div><blo=
ckquote type=3D"cite" cite=3D"mid:CAPpF0yO4OjNmJwCDZF2eG_Hym8sXY9=3DUHZP=
[email protected]"><div dir=3D"ltr"><div>Hello all, with pg=
8000 if autocommit is turned on while a
          transaction is in progress, the autocommit behaviour only
          takes effect after the current transaction has been explicitly
          committed or rolled back. Of the options given, I'd go for
          option 1, raising an exception. I don't like the idea of SQL
          statements being executed implicitly, which is why I'd be
          against option 2.<br></div></div></blockquote><p>That's a fair=
 point, but please remember that no statements are
      executed. The transaction control is completely with the driver
      library and negotiated with the server (some use implicit
      statements for this, but that's just a technical detail). In fact,
      users should <i>not</i> use transaction statements on connections
      managed by drivers. That's why we have .rollback() and .commit()
      as separate APIs in the DB-API.<br></p><p>I think we've already se=
ttled on option 3, with the added note
      that many database modules implement option 2. pg8000 would not be
      one of those, but that's not a problem.<br></p><p>Writing to the a=
ttribute will be deprecated at the same time as
      documenting it, so that we can prepare for DB-API 3.0 using a
      method instead. This would then also address your concern.<br></p>=
<blockquote type=3D"cite" cite=3D"mid:CAPpF0yO4OjNmJwCDZF2eG_Hym8sXY9=3D=
[email protected]"><div dir=3D"ltr"><div><br></div><div=
>A radical suggestion for DBAPI-3.0 would be to remove
          autocommit completely. I think this would make the dbapi
          interface easier for people to understand, because there would
          be no implicit SQL commands, which is what I think confuses
          people. Of course the downside is less portability, but I
          think people accept that if they change their database a lot
          of things will be different, and autocommit is just one of
          them. A higher level library such as SQLAlchemy could still
          emit the correct SQL via the dialect so that users of
          SQLAlchemy wouldn't need to know about autocommit.<br></div></=
div></blockquote><p>That's not going to work out :-) Autocommit is essen=
tial for some
      database operations, e.g. to avoid locking. Many databases also
      don't permit running certain DDL statements inside transactions.<b=
r></p></blockquote><div><br></div><div>I dont think I got Tony's email d=
irectly, but re: autocommit, the fact that basically all DBAPIs now impl=
ement autocommit is very advantageous to SQLAlchemy and in version 2.0 w=
e have finally removed all semblances of SQLAlchemy's original homegrown=
 "autocommit" feature, which we now refer towards as "library level auto=
commit" in contrast to "driver level autocommit".&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp; What's important about "autocommit" is that in most cases,=
 it doesnt equate to the driver emitting "COMMIT" after every SQL statem=
ent, it instead allows the driver to forego emitting BEGIN in the first =
place, and the database itself runs in its own "autocommit" mode, which =
is typically higher performing.<br></div><div><br></div><div>With DBAPI =
including the behavior, this first off allows us to simplify our library=
, as users who want to work in "autocommit" fashion can do so by just se=
tting up the driver to work in this way, without us having to provide / =
test entirely different ways of working.&nbsp; But that's only the begin=
ning of the advantages.&nbsp;&nbsp;&nbsp; The bigger advantage is that s=
upport for many other styles of database connectivity is supported, incl=
uding for some PGBouncer configurations which don't tolerate transaction=
 boundaries well, connecting to read-only replica databases where the ov=
erhead of DBAPI's implicit "BEGIN" followed by our necessary ".rollback(=
)" for a pooled connection can be removed (this is a big MySQL / MariaDB=
 use case), and of course we can support the various DDL scenarios (most=
ly on PostgreSQL) that require autocommit.&nbsp;&nbsp;&nbsp; When SQLAlc=
hemy used "library level autocommit", we had no way to affect that the D=
BAPI was still emitting "BEGIN" and that our pooled connections still ha=
d to use ROLLBACK.<br></div><div><br></div><div><br></div><div><br></div=
></body></html>
--517b15d7b77c45f1a8f724da1ed1ceaa--

--===============5763468824346141401==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

_______________________________________________
DB-SIG maillist  -  [email protected]
https://mail.python.org/mailman/listinfo/db-sig

--===============5763468824346141401==--