Are transactions in PostgreSQL via `psycopg2` per-cursor or per-connection?

Question

I'm doing some work with PostgreSQL 9.3 using the psycopg2 database API.

I have the DB API set in the minimum isolation level ("autocommit" mode), and am managing my own transactions directly via SQL. Example:

cur = self.conn.cursor()
cur.execute("BEGIN;")
cur.execute("SELECT dbId, downloadPath, fileName, tags FROM {tableName} WHERE dlState=%s".format(tableName=self.tableName), (2, ))
ret = cur.fetchall()
cur.execute("COMMIT;")

Basically, Is the transaction that is started by the cur.execute("BEGIN;") limited to just that cursor, or is it for the whole connection (self.conn.cursor())?

Some of the more complex things I am doing involve multiple separate database operations, that I logically break down into functions. Since this is all in a class which has the connection as a member, it's a lot more convenient to create cursors within each function. However, I'm not sure how creating cursors within a transaction works.

Basically, if transactions are per-connection, I can just create lots of cursors on-the-fly within the transaction. If they're per-cursor, that means I have to pass the cursor around everywhere. Which is it?

The documentation does not touch on this, though the fact that you can call connection.commit() makes me fairly confident the transaction-control is per-connection.

Answer 1

Transactions are per-session, i.e. per-connection.

PostgreSQL doesn't support suspending and resuming transactions, so psycopg2 couldn't make them per-cursor unless it implicitly created new connections behind the scenes.

In practice I don't find psycopg2's cursors particularly useful. They can retain result sets if you're not using incremental fetch from the server, but I don't find them good for much else.

Why manually issue begin and commit though, rather than using the connection methods for them?

• Fake Name: AFICT from the documentation, the whole "DB API" model doesn't really support explicit transactions at all.
• Craig Ringer: @FakeName You don't have to explicitly begin. If no transaction is open, a new one is started for you. You just commit to delineate transactions. So yes, the DB-API model does support explicit transactions.
• Fake Name: If the DB api is doing it automatically without my specifically directing it to do so, that is very much an implicit begin. And besides, it's irrelevant, since (as I stated in the question), I'm using the autocommit mode, because I don't want those automatic BEGIN statements. I don't want psycopg2 to create a new transaction for each SELECT.
• Fake Name: TL;DR basically I want to know the exact scope of all the transactions going on because A. I'm crazy that way, and B. it helps a lot with debugging.
• Craig Ringer: I wouldn't be surprised to see odd bugs popping up with this. AFAIK autocommit is really meant to be autocommit not manual-transaction-management. If you really want to manually manage transaction scopes, an extra BEGIN is harmless and will just be ignored by PostgreSQL with WARNING: there is already a transaction in progress.

Answer 2

From the psycopg2 documentation:

In Psycopg transactions are handled by the connection class. By default, the first time a command is sent to the database (using one of the cursors created by the connection), a new transaction is created. The following database commands will be executed in the context of the same transaction – not only the commands issued by the first cursor, but the ones issued by all the cursors created by the same connection. Should any command fail, the transaction will be aborted and no further command will be executed until a call to the rollback() method.

At the same time, from version 2.4.2, there is the autocommit attribute (emphasis added):

Read/write attribute: if True, no transaction is handled by the driver and every statement sent to the backend has immediate effect; if False a new transaction is started at the first command execution: the methods commit() or rollback() must be manually invoked to terminate the transaction.

The autocommit mode is useful to execute commands requiring to be run outside a transaction, such as CREATE DATABASE or VACUUM.

The default is False (manual commit) as per DBAPI specification.