Re: psycopg2 positioning of .commit() (Posting On Python-List Prohibited)

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.