PostgreSQL Transactions via psycopg2: Per-Cursor or Per-Connection?

postgresql

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.

Best Answer

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?

Related Solutions

Postgresql – Span single transaction over several sequential psql sessions

Maybe it would be easiest to run a psql in the background, with it set to execute stdin, and connect its stdin to a named pipe. Then you can continually push data into that pipe, and finally push "end; \quit". Something like:

#!/bin/sh

psql_pipe=/tmp/psql$$
mkfifo -m 600 $psql_pipe
psql < $psql_pipe &
exec 3>$psql_pipe
psql_pid=$!

echo "> Started psql (pid=$psql_pid) reading from $psql_pipe"
trap '
  kill $psql_pid
  rm -f $psql_pipe
' EXIT

echo "begin;" >&3

echo "select now();" >&3

sleep 2

echo "select now();" >&3

sleep 2

echo "end; \quit" >&3

wait $psql_pid

Note that you can't simply do echo "sql" >$psql_pipe since the EOF would be transmitted to psql, which would then exit early-- the shell script has to keep its fd open.

PostgreSQL – Does it Support Different Transaction Isolation Per Connection?

Yes it does support different transaction isolation levels per-connection. You can set the transaction isolation level (as well as the read-only and deferrable status for transactions) for a connection with SET SESSION CHARACTERISTICS:

localhost:5432 postgres postgres  # SHOW transaction_isolation;
 transaction_isolation
-----------------------
 read committed
(1 row)


localhost:5432 postgres postgres  # SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET

localhost:5432 postgres postgres  # SHOW transaction_isolation;
 transaction_isolation
-----------------------
 repeatable read
(1 row)

You can override per-transaction with SET TRANSACTION ISOLATION LEVEL, or as you start a transaction with BEGIN TRANSACTION ISOLATION LEVEL:

localhost:5432 postgres postgres  # BEGIN;
BEGIN
Time: 0.227 ms
localhost:5432 postgres postgres * # SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET
Time: 0.229 ms
localhost:5432 postgres postgres * # SHOW transaction_isolation;
 transaction_isolation
-----------------------
 serializable
(1 row)


Time: 0.262 ms
localhost:5432 postgres postgres * # COMMIT;
COMMIT

localhost:5432 postgres postgres  # SHOW transaction_isolation;
 transaction_isolation
-----------------------
 repeatable read
(1 row)

The default is set with the default_transaction_isolation parameter:

localhost:5432 postgres postgres  # SHOW default_transaction_isolation;
 default_transaction_isolation
-------------------------------
 repeatable read
(1 row)

See the docs at http://www.postgresql.org/docs/current/static/sql-set-transaction.html

Best Answer

Related Solutions

Postgresql – Span single transaction over several sequential psql sessions

PostgreSQL – Does it Support Different Transaction Isolation Per Connection?

Related Question