psycopg, transactions and multiple cursors
alban at magproductions.nl
Mon Nov 8 15:04:21 CET 2004
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
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
I cross my fingers that such may not be necessary.
Thanks for helping.
More information about the Python-list