[Pypgsql-users] Re: How to stop query execution from Python/pyPgS QL

[Gerhard <ger...@op...>]

Billy G. Allie <bga=Ndj...@pu...> wrote:
> [cancelling a query]
> Since PgSQL is built upon the libpq module, you can access the
> PGrequestCancel function as follows (assuming 'cx' is the connection
> running the request you wish to cancel):
> 
> 	cx.conn.requestCancel()
> 
> This would have to be issued by a different thread of execution or from a
> signal handler since the execute method does not return until the query
> has finished processing at the backend.

Can this work? I was under the impression that this was one of the functions
that's supposed to be used under the asynchronous API.

Incidentally, I've tried to use the async functions in pyPgSQL.libpq a few
days back (and cancelling a query), but didn't succeed.  Today, I've found
out why and committed a change to pgconnection.c. I believe you'll have to
use the CVS version of pyPgSQL to cancel queries for now.

Here's a toy example with which I tested this functionality:

#v+
# Experiment with asynchronous query processing and cancelling queries.

from pyPgSQL import libpq
import select

conn = libpq.PQconnectdb("host=myhost dbname=mydb")

class BreakOut(Exception): pass

counter = 0

for i in range(10):
    try:
        print "-" * 50
        print "Starting query."
        conn.sendQuery("SELECT NAME FROM TEST") # 100000 rows
        while True:
            counter += 1
            if counter == 300:
                raise BreakOut

            fds = select.select([conn.socket], [], [])
            if len(fds) != 0:
                conn.consumeInput()
                if not conn.isBusy:
                    break

        res = conn.getResult()
        # here we have the entire resultset

        res = conn.getResult()
        assert res == None
        print "Query successfully processed."
    except BreakOut:
        # Cancel currently running query.
        print "Cancelling query ..."
        conn.requestCancel()
        while True:
            fds = select.select([conn.socket], [], [])
            if len(fds) != 0:
                conn.consumeInput()
                if not conn.isBusy:
                    break
        try:
            res = conn.getResult()
        except libpq.OperationalError, e:
            assert str(e).strip() == "ERROR:  Query was cancelled."
            res = conn.getResult()
            assert res == None
        print "Query sucessfully cancelled."

conn.finish()
#v-

ciao,

Gerhard
-- 
Gerhard Häring
OPUS GmbH München
Tel.: +49 89 - 889 49 7 - 32
http://www.opus-gmbh.net/