psycopg, transactions and multiple cursors

[Alban Hertroys]

Diez B. Roggisch wrote:
> As the error is about integrity violation, its safe to assume that the
> problem is postgresql related.

Well, actually it merely shows that the queries aren't in the same 
transaction, or that there was never a transaction to begin with. That's 
what I hoped to illustrate with this example; Apparently, I could have 
been more clear...

> And changes made in one transaction can't be seen in another until they are
> commited _and_ the other transaction is opened after the first one is
> commited - that makes sense, as otherwise the second transaction could
> create references that then are invalidated by rolling back the first
> transaction, creating undefined state.

Of course, that's on of the reasons to have transactions; to make sure 
that 'other' users don't see data that cannot be guaranteed to be valid yet.

> Now looking at your code, one sees that BEGIN is called on the cursor1 - so
> the transaction boundaries are around cursor1, thus they are not visible to

AFAIK, you can't open a transaction w/o using a cursor; You need a query 
that says "BEGIN;".
You can commit a connection object, however. It would be nice to be able 
to start a transaction on a connection object, but then you still could 
only have one transaction per connection... :(

This would make it impossible to have nested loops of queries inside a 
transaction (the cursor gets repositioned in the inner fetch of the loop 
and the outer fetch looses track of which record it was positioned at).

Maybe the transaction shouldn't be closed until the cursor is closed, 
even if other cursors are opened (in the same transaction) before that 
happens.
Aside from that, it turns out that the first query gets committed, even 
though the transaction should have failed. This probably just indicates 
that the transaction indeed is closed too soon, however, I didn't tell 
it to commit. Shouldn't it issue a rollback instead? [This would 
probably confuse people, but is it correct to do otherwise? Shouldn't it 
rollback if you don't commit?]

I know PostgreSQL can do transactions on nested cursors, I have used 
that now and then in stored procedures (pl/pgsql).

  > So the cursor2 seems to be using its own connection, thus its own
> transaction. This is also in the README for psycopg.

So that's why I couldn't find the documentation... (Though the DBAPI 2.0 
document linked from the Python site is rather helpful).
It would probably be nice to have those documents on the psycopg site 
somewhere... (Or if they are, put clear pointers to them from the main 
site - I've never been able to find them).

> As I don't have psycopg running here, I can't experiment myself, but I think
> you should use cursor1 for all of your sql statements that belong to one
> transaction. 

I cross my fingers that such may not be necessary.

Thanks for helping.