Queries unresponsive to SIGINT / Keyboard-interupt in Interactive Shell

[pik]

Overly intensive queries or queries on a hung-connection are entirely unresponsive to keyboard-interrupt and require a very, very long wait for timeout or termination of the current interactive shell.

*Python 3.4.3, Linux 4.0.4 / Mac OS X 10.10

[dvarrazzo]

I don't think we can do anything about that. What you can do is ctrl-z and then kill the script, or pg_cancel_backend() the session to stop the query.

[pik]

I don't think we can do anything about that

This doesn't happen with any other python database adapter. The user should be able to interrupt a hung connection with SIGINT.

edit: I would be willing to work on this - but imho this issue needs to be reopened - I really don't think this kind of bug is reasonable to accept as "working as normal" in what is probably the most popular python postgres library especially given that no other libraries (at least that I have used) exhibit the above behaviour.

[dvarrazzo]

If you find a solution please feel free to contribute. I will leave this bug open for some time and will close it if I don't find any activity on it.

Note that in green mode you should find the queries easier to interrupt:

In [1]: import psycopg2
In [2]: import psycopg2.extras
In [3]: psycopg2.extensions.set_wait_callback(psycopg2.extras.wait_select)
In [4]: cnn = psycopg2.connect('')
In [5]: cur = cnn.cursor()
In [6]: cur.execute("select pg_sleep(5)")
^C---------------------------------------------------------------------------
KeyboardInterrupt                         Traceback (most recent call last)
<ipython-input-6-88ee0d06f68b> in <module>()
----> 1 cur.execute("select pg_sleep(5)")

.../psycopg2/extras.pyc in wait_select(conn)
    580             break
    581         elif state == POLL_READ:
--> 582             select.select([conn.fileno()], [], [])
    583         elif state == POLL_WRITE:
    584             select.select([], [conn.fileno()], [])

KeyboardInterrupt: 

but in normal mode the waiting is done by the libpq and I don't know how to send it messages. By the way the above trashes the connection.

[pik]

Hello Daniele, Thanks for opening this back up.

So I don't know the library very well but looking at the async foremost - it seems the problem is how it is handled in green.c? Specifically there are only two exit paths - either psyco_wait returns a non-zero exit status (which doesn't distinguish between an exception in Python e.g. SIGINT or one in the c code) in which case green_panic is called and the connection is scrapped, or psyco_wait returns 0, in which case even if no result is available pg_get_last_result blocks waiting for the connection even if it isn't ready or timed out.

    if (0 != psyco_wait(conn)) {
        green_panic(conn);
        goto end;
    }

    /* Now we can read the data without fear of blocking. */
    result = pq_get_last_result(conn);

It would be nice to give a clean exit path from psyco_wait which doesn't trash the connection (i.e. trying PQcancel first). And/or allowing an on_wait_exit hook or something of the like to happen before the entire call blocks on pq_get_last_result(conn).

Basically with the current behaviour if I handle an exception within a custom wait callback (e.g. except KeyboardInterrupt: return) - It will simply get stuck waiting on pg_get_last_result, so currently the only real choice is raising and letting green_panic scrap the connection.

[dvarrazzo]

you could try running conn.cancel() in the callback.

Actually, I went to google for connection cancel to make sure of the function name and I discovered an article about cancelling a query from Python. Written by me one year ago. Wow, I'm growing old :(

http://initd.org/psycopg/articles/2014/07/20/cancelling-postgresql-statements-python/

[pik]

@dvarrazzo
Thanks for the feedback. I've tested this and it appears to work as intended. (Although I'm actually not sure why the QueryCanceledError doesn't trash the connection on the next conn.poll).

Would be great if this could make it into the docs or extensions.py.

[dvarrazzo]

It could be a FAQ entry, yes. Feel free to propose a patch, the way you would have liked to find in the docs, thanks.

[dvarrazzo]

I have fixed the wait_select callback so that it handles the Ctrl-C without trashing the connection and added a FAQ entry. Feedback about the docs is welcome, thank you.

[megahall]

For people finding this from the Internet, on recent versions of the library, use this:

psycopg2.extensions.set_wait_callback(psycopg2.extras.wait_select)