How to prevent psycopg2 locking a table

Question

I have a table on a PostgreSQL 9.1 server like this:

CREATE TABLE foo(id integer PRIMARY KEY);

In an interactive Python shell with psycopg2 (≥ 2.4.2), I might start a connection and cursor, and query this table:

import psycopg2
conn = psycopg2.connect('dbname=...')
curs = conn.cursor()
curs.execute('SELECT * FROM foo;')
curs.fetchall()

However, if I then try to modify the table:

ALTER TABLE foo ADD COLUMN bar integer;

this starts a virtual deadlock until I do conn.close() from Python.

How can I start a simple connection with psycopg2 that prevents it from creating deadlocks caused by DDL changes elsewhere? The connection in Python can be read-only.

Priggery

Answer 1

The solution I found is to use set_session like this:

conn.set_session(readonly=True, autocommit=True)

The documentation for autocommit warns:

By default, any query execution, including a simple SELECT will start a transaction: for long-running programs, if no further action is taken, the session will remain “idle in transaction”, an undesirable condition for several reasons (locks are held by the session, tables bloat...). For long lived scripts, either ensure to terminate a transaction as soon as possible or use an autocommit connection.

This sums up the experience with a simple SELECT in the question.

Priggery

You could also just do a conn.commit() in instead of setting autocommit to True – Anticyclone

Answer 2

Just as an FYI, I ran into this very same issue doing concurrent writing with psycopg2. The documentation states:

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.

Basically psycopg2 locks the table for all transactions using the same connection.

Donniedonnish

In addition to the answers above, you could also prevent a table lock by prefixing the SQL statement with a "BEGIN;" as well as finishing it with an "END;". It really just depends on the scope of what you're trying to do. – Donniedonnish