psycopg, transactions and multiple cursors

[Alban Hertroys]

Steve Holden wrote:
> Yes, most database connections will generate an implicit transaction the 
> first time a change is made to the database (in the absence of autocommit).

I quoted you on this in the class-file that is (supposedly) going to 
solve my problems.

>> Yes, it does have autoCommit, and thankfully it can be turned off 
>> (which I did, of course).
> 
> It certainly isn't too useful if you occasionally need to roll things 
> back. Structural changes to the database will frequently cause an 
> automatic commit anyway, though - you aren't modifying the database 
> structure at all, I take it?

Oh no, there's rarely a need for that and it tends to get you into trouble.
For example, I know a CMS (not Python related, sorry) that creates 
database tables for classes it uses for various site objects, but it 
apparently doesn't check for the class name being a reserved SQL 
keyword. Oops...

> Nested cursors aren;t nested transcations, though, right?

Indeed. My intention is actually to keep them in the same transaction.

>> A cursor is comparable to an iterator over a result set (where the 
>> cursor fetches one record from the database at a time).
>>
> B e careful that you don;t confuse the DB API curosrs with the cursors 
> you get with DECLARE CURSOR in PL/SQL, for example. The two aren;t 
> necessarily the same thing (and I've always felt that "cursor" was, for 
> that reason, not the best possible terminological choice for the DB API).

PHP uses 'resource identifier' or something similar. I can't say it's 
much clearer, but you won't confuse it with cursors. OTOH, a connection 
in PHP is also a 'resource identifier'.

>> However, if you loop through a result set (with a cursor) and you need 
>> to do a query based on the record that the cursor is currently 
>> positioned at, you can't use the same cursor again for that query, but 
>> need to open a new cursor. That's what I meant by "nesting cursors".
>>
> Bear in mind, though, that it will often be *much* more efficient to do 
> a fetchall() from the cursor and iterate over that result. This 
> typically avoids many round-trips by fetching all the data at once, 
> though it's less practical if data sets become huge.
> 
> There's sometimes a middle ground to be found with repeated calls to 
> fetchmany().
> 
> In that way the curosr can be reused with impunity once the data has 
> been fetched.

True enough, and I don't use nested cursors that way in my Python code. 
It is something I do frequently in PL/SQL, though, so I suppose I used 
that as an example.
In Python the problem is different, I used a bad example.

The reason I use multiple cursors in Python is not so much that I nest 
them, but that they are often in a local scope (in a method, a class, etc.).
As the cursor has to execute a different query in each scope anyway, I 
don't think it really matters whether I go through loops to reuse the 
previous cursor object (risking entering a nested loop unknowingly) or 
create a new one.
The examples I encountered do the same thing, so I suppose it's alright.

> I think we've already agreed that the psycopg cursors aren't DB API 
> compliant anyway, precisely because of the way they handle transactions. 
> An API-compliant library shares transaction state across all cursors 
> generated from the same connection, which (IMHO) gives the flexibility 
> one needs to to handle complex database interactions.

Indeed.

>> The number of questions is increasing...
> 
> Well, the number of answers is, too, but it seems to me you *are* kind 
> of wanting to have your cake and eat it. In previous threads you've 

I'm not familiar with that expression, but I suppose I would change that 
to: "I baked my own cake, and though it doesn't taste as well as I 
intended, I'm trying to eat it anyway".
It means a lot of work if some basic assumptions in a large project turn 
out to be wrong... I was hoping to get it working the way it is, but it 
seems that the "one connection" idea is not possible. Too bad...

> suggested that the psycopg cursor behavior is what you want, but now 
> that very behavior might (?) be biting you.

I am indeed starting to have my doubts about the usefulness of being 
able to commit a cursor. If it doesn't do anything to a transaction, or 
if you can't start a new transaction using a cursor after that commit 
(I'm not yet certain which of the two happens, if it isn't a third 
option), then it seems kind of pointless and confusing.

The possibility suggested (to me at least) that it would be possible to 
do multiple (maybe even parallel) transactions using only one database 
connection, by making the cursors handle the transactions. Alas, it's 
not so.

> Anyway, you couldn't be talking to a better bunch of guys to try and 
> solve this problem. c.l.py is sometimes persistent beyond all reasonable 
> limits. Good luck!

You guys are great indeed! I'm learning things that I thought I knew 
already. And you're quick too, all of my complicated questions so far 
have been answered/solved within a day or so.

But I have to admit, the complicated questions are usually the most fun ;)

Thanks for all the help, it's much appreciated.

Alban.