From Newsgroup: comp.lang.python

On Sun, 8 Sep 2024 11:03:21 -0000 (UTC), Jon Ribbens wrote:

What if there's an exception in your exception handler? I'd put the
rollback in the 'finally' handler, so it's always called. If you've
already called 'commit' then the rollback does nothing of course.

In any DBMS worth its salt, rollback is something that happens
automatically if the transaction should fail to complete for any reason.

This applies for any failure reason, up to and including a program or
system crash.
--- Synchronet 3.20a-Linux NewsLink 1.114

From Newsgroup: comp.lang.python

On Mon, 9 Sep 2024 13:48:32 +1200, Greg Ewing wrote:

I would much prefer to have just *one* place where exceptions are caught
and logged.

Why catch exceptions at all? The only kind of database-related exception I’ve felt the need to catch so far is the occasional IntegrityError from trying to insert a record with a duplicate key (and that only in certain situations). Anything else (particularly SQL syntax errors) I would rather just leave to the default exception-handling mechanism -- why waste time reinventing its information-reporting abilities?
--- Synchronet 3.20a-Linux NewsLink 1.114

From Newsgroup: comp.lang.python

On 2024-09-08, Lawrence D'Oliveiro <ldo@nz.invalid> wrote:

On Sun, 8 Sep 2024 11:03:21 -0000 (UTC), Jon Ribbens wrote:

What if there's an exception in your exception handler? I'd put the
rollback in the 'finally' handler, so it's always called. If you've
already called 'commit' then the rollback does nothing of course.

In any DBMS worth its salt, rollback is something that happens
automatically if the transaction should fail to complete for any reason.

This applies for any failure reason, up to and including a program or
system crash.

If it's a program or system crash, sure, but anything less than that -
how would the database even know, unless the program told it?
--- Synchronet 3.20a-Linux NewsLink 1.114

From Newsgroup: comp.lang.python

On Mon, 9 Sep 2024 09:13:40 -0000 (UTC), Jon Ribbens wrote:

On 2024-09-08, Lawrence D'Oliveiro <ldo@nz.invalid> wrote:

On Sun, 8 Sep 2024 11:03:21 -0000 (UTC), Jon Ribbens wrote:

What if there's an exception in your exception handler? I'd put the
rollback in the 'finally' handler, so it's always called. If you've
already called 'commit' then the rollback does nothing of course.

In any DBMS worth its salt, rollback is something that happens
automatically if the transaction should fail to complete for any
reason.

This applies for any failure reason, up to and including a program or
system crash.

If it's a program or system crash, sure, but anything less than that -
how would the database even know, unless the program told it?

The database only needs to commit when it is explicitly told. Anything
less -- no commit.
--- Synchronet 3.20a-Linux NewsLink 1.114

From Newsgroup: comp.lang.python

Am Mon, Sep 09, 2024 at 10:00:11AM -0000 schrieb Jon Ribbens via Python-list:

The database only needs to commit when it is explicitly told. Anything
less -- no commit.

So the Python code is half-way through a transaction when it throws
a (non-database-related) exception and that thread of execution is
aborted. The database connection returns to the pool, and is re-used
by another thread which continues using it to perform a different
sequence of operations ... ending in a COMMIT, which commits
one-and-a-half transactions.

Right, but that's true only when writable connections are
being pooled, which should be avoidable in many cases.
Any pool worth its salt should rollback any potentially
pending transactions of a connection when it is given back
that pooled connection. Unless explicitely told not to.
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
--- Synchronet 3.20a-Linux NewsLink 1.114

From Newsgroup: comp.lang.python

Am Mon, Sep 09, 2024 at 10:00:11AM -0000 schrieb Jon Ribbens via Python-list:

So the Python code is half-way through a transaction when it throws
a (non-database-related) exception and that thread of execution is
aborted. The database connection returns to the pool,

How does it return to the pool ?
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
--- Synchronet 3.20a-Linux NewsLink 1.114

From Newsgroup: comp.lang.python

On Mon, 9 Sep 2024 10:00:11 -0000 (UTC), Jon Ribbens wrote:

On 2024-09-09, Lawrence D'Oliveiro <ldo@nz.invalid> wrote:

The database only needs to commit when it is explicitly told. Anything
less -- no commit.

So the Python code is half-way through a transaction when it throws a (non-database-related) exception and that thread of execution is
aborted. The database connection returns to the pool ...

The DBMS connection is deleted. The DBMS discards all context created for
this connection, including any transactions in progress. Gone.

The database structures on persistent storage are also carefully designed
with transaction safety in mind. So any partial transaction data saved on persistent storage that remains after a system crash can be identified as
such and discarded, leaving the database in its pre-transaction state.
--- Synchronet 3.20a-Linux NewsLink 1.114

From Newsgroup: comp.lang.python

On 2024-09-09, Lawrence D'Oliveiro <ldo@nz.invalid> wrote:

On Mon, 9 Sep 2024 10:00:11 -0000 (UTC), Jon Ribbens wrote:

On 2024-09-09, Lawrence D'Oliveiro <ldo@nz.invalid> wrote:

The database only needs to commit when it is explicitly told. Anything
less -- no commit.

So the Python code is half-way through a transaction when it throws a
(non-database-related) exception and that thread of execution is
aborted. The database connection returns to the pool ...

The DBMS connection is deleted.

How does that happen then?
--- Synchronet 3.20a-Linux NewsLink 1.114

From Newsgroup: comp.lang.python

On Mon, 9 Sep 2024 21:12:51 -0000 (UTC), Jon Ribbens wrote:

On 2024-09-09, Lawrence D'Oliveiro <ldo@nz.invalid> wrote:

On Mon, 9 Sep 2024 10:00:11 -0000 (UTC), Jon Ribbens wrote:

On 2024-09-09, Lawrence D'Oliveiro <ldo@nz.invalid> wrote:

The database only needs to commit when it is explicitly told.
Anything less -- no commit.

So the Python code is half-way through a transaction when it throws a
(non-database-related) exception and that thread of execution is
aborted. The database connection returns to the pool ...

The DBMS connection is deleted.

How does that happen then?

You write code to do it.
--- Synchronet 3.20a-Linux NewsLink 1.114

From Newsgroup: comp.lang.python

On 2024-09-09, Lawrence D'Oliveiro <ldo@nz.invalid> wrote:

On Mon, 9 Sep 2024 21:12:51 -0000 (UTC), Jon Ribbens wrote:

On 2024-09-09, Lawrence D'Oliveiro <ldo@nz.invalid> wrote:

On Mon, 9 Sep 2024 10:00:11 -0000 (UTC), Jon Ribbens wrote:

On 2024-09-09, Lawrence D'Oliveiro <ldo@nz.invalid> wrote:

The database only needs to commit when it is explicitly told.
Anything less -- no commit.

So the Python code is half-way through a transaction when it throws a
(non-database-related) exception and that thread of execution is
aborted. The database connection returns to the pool ...

The DBMS connection is deleted.

How does that happen then?

You write code to do it.

Ok. So we've moved away from "In any DBMS worth its salt, rollback is
something that happens automatically" and now you're saying it isn't
automatic after all, "you write code to do it". That was my point.
The database provides the tools, but it isn't psychic.
--- Synchronet 3.20a-Linux NewsLink 1.114

From Newsgroup: comp.lang.python

Am Tue, Sep 10, 2024 at 08:38:30AM -0000 schrieb Jon Ribbens via Python-list:

Ok. So we've moved away from "In any DBMS worth its salt, rollback is something that happens automatically"

Nope. The original post asked something entirely different.

and now you're saying it isn't automatic after all,

No again, such shenanigans only start to happen when pooling
is brought into the equation.
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
--- Synchronet 3.20a-Linux NewsLink 1.114

From Newsgroup: comp.lang.python

On 2024-09-10, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:

Am Tue, Sep 10, 2024 at 08:38:30AM -0000 schrieb Jon Ribbens via Python-list:

Ok. So we've moved away from "In any DBMS worth its salt, rollback is
something that happens automatically"

Nope. The original post asked something entirely different.

No it didn't.

and now you're saying it isn't automatic after all,

No again, such shenanigans only start to happen when pooling
is brought into the equation.

No they don't.
--- Synchronet 3.20a-Linux NewsLink 1.114

From Newsgroup: comp.lang.python

On 2024-09-10, Lawrence D'Oliveiro <ldo@nz.invalid> wrote:

On Tue, 10 Sep 2024 08:38:30 -0000 (UTC), Jon Ribbens wrote:

On 2024-09-09, Lawrence D'Oliveiro <ldo@nz.invalid> wrote:

On Mon, 9 Sep 2024 21:12:51 -0000 (UTC), Jon Ribbens wrote:

On 2024-09-09, Lawrence D'Oliveiro <ldo@nz.invalid> wrote:

On Mon, 9 Sep 2024 10:00:11 -0000 (UTC), Jon Ribbens wrote:

On 2024-09-09, Lawrence D'Oliveiro <ldo@nz.invalid> wrote:

The database only needs to commit when it is explicitly told.
Anything less -- no commit.

So the Python code is half-way through a transaction when it throws >>>>>> a (non-database-related) exception and that thread of execution is >>>>>> aborted. The database connection returns to the pool ...

The DBMS connection is deleted.

How does that happen then?

You write code to do it.

Ok. So we've moved away from "In any DBMS worth its salt, rollback is
something that happens automatically" and now you're saying it isn't
automatic after all, "you write code to do it".

The database code already performs that function. As far as the client is concerned, the function happens automatically.

... but only if "you write code to do it".

And it’s not just code, it’s data. The database structures on persistent storage are also carefully designed with transaction safety in mind. So
any partial transaction data saved on persistent storage that remains
after a system crash can be identified as such and discarded, leaving the database in its pre-transaction state.

Yes, nobody's disputing that. A good database will do what you tell it,
and keep the data you give it. But what if you tell it the wrong thing
or give it the wrong data? It's like, for example, a RAID array will
save you from a faulty disk, but will not save you from the software
writing incorrect data, which the RAID array will then faithfully copy
across to all the disks overwriting the good data.
--- Synchronet 3.20a-Linux NewsLink 1.114

From Newsgroup: comp.lang.python

On 2024-09-11, Lawrence D'Oliveiro <ldo@nz.invalid> wrote:

On Tue, 10 Sep 2024 22:48:36 -0000 (UTC), Jon Ribbens wrote:

But what if you tell it the wrong thing ...

To get back to the original point of this thread, all that rigmarole to
try to ensure to call “rollback” in case of an exception is completely unnecessary: the DBMS will take care of that for you.

No, it won't.
--- Synchronet 3.20a-Linux NewsLink 1.114

From Newsgroup: comp.lang.python

On Sun, 8 Sep 2024 12:48:50 +1200, Greg Ewing wrote:

This is how I normally do things like this:

try:
do something .commit()
except:
log something .rollback()

Doing an explicit rollback ensures that the transaction is always rolled
back if it is interrupted for any reason.

Don’t bother. Let the DBMS deal with that for you. That’s what it’s for.

Also, maybe you meant “finally” instead of “except”?
--- Synchronet 3.20a-Linux NewsLink 1.114