This project is archived and is in readonly mode.

Unable to cancel query 2.4.5-1 (debian stable)

Activity

Daniele Varrazzo
Hello,

yes, I know the issue. No, it's not fixed in the master and I don't know how to fix it; actually I don't think it's generally fixable in psycopg: you may have two thread running a query: should ctrl-c cancel all of them? What to do is obvious only when there is a single connection and a single thread but psycopg can run several query at once, each in a different thread.

You may try to run a parallel thread detecting the ctrl-c and cancelling the query you want using connection.cancel() on the connection you want, but you should check how python threads and signal interact.

Note that a way to cancel the query that is more gentle than a kill -9 is to pg_cancel_backend the backend pid of the connection: psycopg will break with an exception.

I'm leaving the issue here as a memo: it won't be fixed in psycopg but we may try and write a recipe to add to the docs to show how to deal with the issue.

June 30th, 2014 @ 09:28 PM

State changed from new to invalid

A way to make the query interruptable is to work in green mode with a wait callback that can tolerate being broken by ctrl-c:

from select import select
from psycopg2.extensions import POLL_OK, POLL_READ, POLL_WRITE

def wait_select_inter(conn):
    while 1:
        try:
            state = conn.poll()
            if state == POLL_OK:
                break
            elif state == POLL_READ:
                select([conn.fileno()], [], [])
            elif state == POLL_WRITE:
                select([], [conn.fileno()], [])
            else:
                raise conn.OperationalError("bad state from poll: %s" % state)
        except KeyboardInterrupt:
            conn.cancel()
            # the loop will be broken by a server error
            continue

psycopg2.extensions.set_wait_callback(wait_select_inter)

An interactive session would look like:

>>> cnn = psycopg2.connect('')
>>> cur = cnn.cursor()
>>> cur.execute("select pg_sleep(10)")
^C
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
QueryCanceledError: canceling statement due to user request

>>> cnn.rollback() # connection is in error now
# the connection can be reused now

I'm closing the bug as it won't be fixed in the lib but will try to make this recipe to float around.

July 20th, 2014 @ 10:38 AM