psycopg, transactions and multiple cursors

[Diez B. Roggisch]

> 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... :(

thats actually the case for all DBs I know including e.g. oracle with jdbc -
so the abstraction layers usually use connection pooling to speed up
opening a connection thus the app doesn't suffer so much.


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

No idea why thats happening - on jdbc, one can set an "autocommit"
connection property that will do exactly that: enforce a commit if a
statement was successful. Maybe psycopg has that too?

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

I didn't find much on nested cursors on google, so I don't know how they
work - but I assume if they are part of psycopg, they somehow have to be
created using an existing cursor, as otherwise how should psycopg know that
what you want is a nested and not a new cursor.

So is there something on cursor objecst to get a new cursor, or at least the
connection so you can get a cursor on the very same connection?


-- 
Regards,

Diez B. Roggisch