Обсуждение: Cancel a query.

Hi

I have some code using psycopg in python.  Connecting in async mode.

I am trying to catch time outs etc, basically after a set amount of
time I am assuming something has failed.

I then want to use "select pg_cancel_backend(15209);" to cancel the
query.  But I can't unless I am connected as the postgres super user.

How can I get my app to cancel one of it's own queries while logged on
as a normal user?



Thanks.

--

Rob

On Sat, Nov 20, 2010 at 12:41 AM, Rob Brown-Bayliss
<r.brown.bayliss@gmail.com> wrote:
> I am trying to catch time outs etc, basically after a set amount of
> time I am assuming something has failed.

Just set a statement timeout before running your potentially long
queries.  Your client code will return an error which you can then
detect and react to properly.

You could create a function calling that query with "Security Definer"
(the function will be called with the privileges of the user that
created the function) :

CREATE OR REPLACE FUNCTION stop_query(myprocpid int)
RETURNS BOOLEAN AS $$
DECLARE
b boolean;
BEGIN
SELECT pg_cancel_backend(myprocpid) INTO b;
RETURN b;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Make sure this function is created with the super user. You can then
call it with any user.

Hope this helps.

Matthieu

Le 20/11/2010 06:41, Rob Brown-Bayliss a écrit :
> Hi
>
> I have some code using psycopg in python.  Connecting in async mode.
>
> I am trying to catch time outs etc, basically after a set amount of
> time I am assuming something has failed.
>
> I then want to use "select pg_cancel_backend(15209);" to cancel the
> query.  But I can't unless I am connected as the postgres super user.
>
> How can I get my app to cancel one of it's own queries while logged on
> as a normal user?
>
>
>
> Thanks.
>
> --
>
> Rob
>