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.

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.