Psycopg psycopg

Comments and changes to this ticket

• 

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

  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.

• 

  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:
          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.