This project is archived and is in readonly mode.

#221 ✓invalid
Psycopg website

Unable to cancel query 2.4.5-1 (debian stable)

Reported by Psycopg website | June 30th, 2014 @ 05:07 PM

Submitted by: TimRex

I seem to have a problem running psycopg2 version 2.4.5-1 (debian stable) against Python 3 v3.2.3-6

I'm unable to signal any python process that is currently executing a query, it simply ignores the signal until the query has completed (in some cases, these can be very long queries).

In production, this is less of an issue we won't really be wanting to kill any processes as a matter of habit (and we can always -9 them). - but when developing, it really gets in the way.. A simple ctrl-c just won't cut it.

Is this fixed in more recent versions? Convincing our draconian admins to allow non-stable packages on their precious boxes ought to be fun.


Comments and changes to this ticket

  • Daniele Varrazzo

    Daniele Varrazzo June 30th, 2014 @ 09:28 PM


    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.

  • Daniele Varrazzo

    Daniele Varrazzo July 20th, 2014 @ 10:38 AM

    • 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:
                state = conn.poll()
                if state == POLL_OK:
                elif state == POLL_READ:
                    select([conn.fileno()], [], [])
                elif state == POLL_WRITE:
                    select([], [conn.fileno()], [])
                    raise conn.OperationalError("bad state from poll: %s" % state)
            except KeyboardInterrupt:
                # the loop will be broken by a server error

    An interactive session would look like:

    >>> cnn = psycopg2.connect('')
    >>> cur = cnn.cursor()
    >>> cur.execute("select pg_sleep(10)")
    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.

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป

<b>WARNING:</b> the informations in this tracker are archived. Please submit new tickets or comments to <a href="">the new tracker</a>.
Psycopg is the most used PostgreSQL adapter for the Python programming language. At the core it fully implements the Python DB API 2.0 specifications. Several extensions allow access to many of the features offered by PostgreSQL.

Shared Ticket Bins