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
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 :
Show quoted text
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.