On Wed, 2007-05-02 at 03:33, Thomas Markus wrote: > I'm running 8.2.3 on ubuntu 6.06 (2.6.15-26-server SMP i686) > > sometimes i have SELECTs that never ends. Normally I drop connections by > killing the connection process (kill <PID>). You shouldn't do that. You should issue a cancel query to the backend running the query. If you do kill <pgbackendPID> then you would kill that one backend, but that's not necessarily transaction safe. What you're doing is killing the connecting program, and then the connection eventually will timeout when tcp_keepalive runs out and the OS detects the hung connection. You could reduce tcp_keepalive if killing the connecting process is the only way you have to do this. > But these hanging > connections (which blocks other statements infinitly) cant be killed. > the only way is a pg_ctl -m immediate stop or a brutal kill -9 That's pretty close to a hammer to the brain response. > what can i do to You can use select * from pg_stat_activity to see what the queries are, and select pg_cancel_backend(procpid) from the stat_activity table to kill individual backends. > - limit statement runtime (set hard timeout) Useful for lots reasons. note that you can change this per user and per database as well as per cluster in the postgresql.conf file. alter user bubba set statement_timeout=300; alter database loveshack set statement_timeout=600; > - remove these blocking connections without killing other connections pg_cancel_backend()
