Re: Killing "stuck" queries and preventing queries from getting "stuck"

On Tue, Sep 28, 2010 at 3:39 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Tim Uckun <timuckun(at)gmail(dot)com> writes:
>> Is there a way to tell postgres to stop any query that runs longer
>> than a specified amount of time? Say an hour?
>
> Setting statement_timeout would do that.  You ought to figure out
> what's causing the performance problem, though, instead of just
> zapping things ...

Well the query is pretty heavy but it gets run a lot. There is a
distinct in there which seems to be the cause of most of the headaches
but it's going to take a while to redo the application to not use
distinct.

The query gets run a lot and 99.99% of the time it runs succesfully
and the daemon goes on it's merry way. Occasionally it seems to "get
stuck" and killing the daemon does not unstick it. I have not been
willing to kill -9 the process and at this stage I can afford to
restart the postgres. Eventually I won't be able to do that though so
I want to fix the app so it uses a more reasonable query and detect
and stop stuck queries in case other queries sneak into the process
during development.