Python error: execute cannot be used while an asynchronous query is underway

Question

How do I prevent the error “ProgrammingError: execute cannot be used while an asynchronous query is underway”? From the docs it says that I should use psycopg2.extras.wait_select if I’m using a coroutine support like gevent., but I’m still get that error when I’m using it. I’ve isolated the error I’m getting in the snippet below.

con = psycopg2.connect(database=DATABASE_NAME, user=DATABASE_USERNAME)

def execute_query(cur, query, params):
    psycopg2.extras.wait_select(con)
    cur.execute(query, params)
    psycopg2.extras.wait_select(con)
    rows = cur.fetchall()
    print rows[0]

cur = con.cursor()
query = "SELECT * FROM mytable"
gevent.joinall([
     gevent.spawn(execute_query, cur, query, None),
     gevent.spawn(execute_query, cur, query, None),
     gevent.spawn(execute_query, cur, query, None),
     gevent.spawn(execute_query, cur, query, None)
])

Answer 1

You are trying to do more than one transaction simultaneously on a single connection. The psycopg documentation says that this is not thread safe and will lead to an error. See under Asynchronous support and Support for coroutine libraries

One possible solution is to use one database connection, each with one cursor, per coroutine (4 distinct connections and cursors in this case).