Am Sat, Sep 07, 2024 at 02:09:28PM -0700 schrieb Adrian Klaver:

> >Right, and this was suggested elsewhere ;)
> >
> >And, yeah, the actual code is much more involved :-D
> >
>
> I see that.
>
> The question is does the full code you show fail?
>
> The code sample you show in your original post is doing something very different then
> what you show in your latest post. At this point I do not understand the exact problem
> we are dealing with.

We are not dealing with an unsolved problem. I had been
asking for advice where to best place that .commit() call in
case I am overlooking benefits and drawbacks of choices.

The

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

cadence is fairly Pythonic and elegant in that it ensures the
the .commit() will always be reached regardless of exceptions
being thrown or not and them being handled or not.

It is also insufficient because the .commit() itself may
elicit exceptions (from the database).

So there's choices:

Ugly:

try:
do something
except:
log something
finally:
try:
.commit()
except:
log some more

Fair but not feeling quite safe:

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

Boring and repetitive and safe(r):

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

I eventually opted for the last version, except for factoring
out the second try: except: block.

Best,
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

On 8/09/24 9:20 am, Karsten Hilbert wrote:
> try:
> do something
> except:
> log something
> finally:
> .commit()
>
> cadence is fairly Pythonic and elegant in that it ensures the
> the .commit() will always be reached regardless of exceptions
> being thrown or not and them being handled or not.

That seems wrong to me. I would have thought the commit should only
be attempted if everything went right.

What if there's a problem in your code that causes a non-SQL-related
exception when some but not all of the SQL statements in the
transaction bave been issued? The database doesn't know something
has gone wrong, so it will happily commit a partially-completed
transaction and possibly corrupt your data.

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.

--
Greg

On 2024-09-08, Greg Ewing <greg.ewing@canterbury.ac.nz> wrote:
> On 8/09/24 9:20 am, Karsten Hilbert wrote:
>> try:
>> do something
>> except:
>> log something
>> finally:
>> .commit()
>>
>> cadence is fairly Pythonic and elegant in that it ensures the
>> the .commit() will always be reached regardless of exceptions
>> being thrown or not and them being handled or not.
>
> That seems wrong to me. I would have thought the commit should only
> be attempted if everything went right.
>
> What if there's a problem in your code that causes a non-SQL-related
> exception when some but not all of the SQL statements in the
> transaction bave been issued? The database doesn't know something
> has gone wrong, so it will happily commit a partially-completed
> transaction and possibly corrupt your data.
>
> 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.

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.

Am Sun, Sep 08, 2024 at 12:48:50PM +1200 schrieb Greg Ewing via Python-list:

> On 8/09/24 9:20 am, Karsten Hilbert wrote:
> > try:
> > do something
> > except:
> > log something
> > finally:
> > .commit()
> >
> >cadence is fairly Pythonic and elegant in that it ensures the
> >the .commit() will always be reached regardless of exceptions
> >being thrown or not and them being handled or not.
>
> That seems wrong to me. I would have thought the commit should only
> be attempted if everything went right.
>
> What if there's a problem in your code that causes a non-SQL-related
> exception when some but not all of the SQL statements in the
> transaction bave been issued? The database doesn't know something
> has gone wrong, so it will happily commit a partially-completed
> transaction and possibly corrupt your data.

A-ha !

try:
run_some_SQL_that_succeeds()
print(no_such_name) # tongue-in-cheek
1 / 0 # for good measure
except SOME_DB_ERROR:
print('some DB error, can be ignored for now')
finally:
commit()

which is wrong, given that the failing *Python* statements
may very well belong into the *business level* "transaction"
which a/the database transaction is part of.

See, that's why I was asking in the first place :-)

I was overlooking implications.

Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

Am Sun, Sep 08, 2024 at 12:48:50PM +1200 schrieb Greg Ewing via Python-list:

> On 8/09/24 9:20 am, Karsten Hilbert wrote:
> > try:
> > do something
> > except:
> > log something
> > finally:
> > .commit()
> >
> >cadence is fairly Pythonic and elegant in that it ensures the
> >the .commit() will always be reached regardless of exceptions
> >being thrown or not and them being handled or not.
>
> That seems wrong to me. I would have thought the commit should only
> be attempted if everything went right.

It is only attempted when "everything" went right. The fault
in my thinking was what the "everything" might encompass.
When some SQL fails it won't matter whether I say
conn.commit() or conn.rollback() or, in fact, nothing at all
-- the (DB !) transaction will be rolled back in any case.

However, that reasoning missed this:

> What if there's a problem in your code that causes a non-SQL-related
> exception when some but not all of the SQL statements in the
> transaction bave been [-- even successfully --] issued?

Still, in this code pattern:

> try:
> do something
> .commit()
> except:
> log something
it doesn't technically matter whether I say .commit or .rollback here:
> .rollback()

.... but ...

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

explicit is better than implicit ;-)

Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

Jon Ribbens <jon+usenet@unequivocal.eu> wrote or quoted:
>What if there's an exception in your exception handler? I'd put the
>rollback in the 'finally' handler, so it's always called.

To make this happen, you'd better kick off with the rollback right
at the beginning of the "finally:" block. Otherwise, some gnarly
exception might rear its ugly head before the rollback gets called!

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.

On 8/09/24 11:03 pm, Jon Ribbens wrote:
> On 2024-09-08, Greg Ewing <greg.ewing@canterbury.ac.nz> wrote:
>> try:
>> do something
>> .commit()
>> except:
>> log something
>> .rollback()
>
> What if there's an exception in your exception handler? I'd put the
> rollback in the 'finally' handler, so it's always called.

Good point. Putting the rollback first would be safer/

--
Greg

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?

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.

On 2024-09-09, Lawrence D'Oliveiro <ldo@nz.invalid> wrote:
> 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.

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.

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

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

On 2024-09-09, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:
> 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 ?

It's just any circumstance in which a bit of your code uses a database
"cursor" (which isn't a cursor) that it didn't create moments before.

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.

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?

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.

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.

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

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.

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.

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.

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.

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.

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.

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”?