How can I limit database query time during web requests?

We've got a pretty typical django app running on postgresql 9.0. We've recently discovered some db queries that have run for over 4 hours, due to inefficient searches in the admin interface. While we plan to fix these queries, as a safeguard we'd like to artific开发者_Go百科ially constrain database query time to 15 seconds--but only in the context of a web request; batch jobs and celery tasks should not be bounded by this constraint.

How can we do that? Or is it a terrible idea?

The best way to do this would be to set up a role/user that is only used to run the web requests, then set the statement_timeout on that role.

ALTER ROLE role_name SET statement_timeout = 15000

All other roles will use the global setting of statement_timeout (which is disabled in a stock install).

You will need to handle this manually. That is checking for the 15 second rule and killing the queries that violate it.

Query pg_stat_activity and find the violators and issue calls to pg_terminate_backend(procpid) to kill the offenders.

Something like this in a loop:

SELECT pg_terminate_backend(pg_stat_activity.procpid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB'
  AND usename = 'WEBUSERNAME'
  AND (now()-query_start) > '00:00:15';

As far as the timing goes, you could pass all of your queries through a class which, on instantiation, spawns two threads: one for the query, and one for a timer. If the timer reaches 15 seconds, then kill the thread with the query.

As far as figuring out if the query is instantiated from a web request, I don't know enough about Django to be able to help you. Simplistically, I would say, in your class that handles your database calls, an optional parameter to the constructor could be something like context, which could be http in the event of a web request and "" for anything else.