Re: infinite blocking statements in 8.2.3

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()