From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Thomas Markus <t.markus@proventis.net>
Cc: pgsql-admin@postgresql.org
Subject: Re: infinite blocking statements in 8.2.3
Date: Wed, 02 May 2007 10:39:29 -0500
Message-ID: <1178120369.2953.14.camel@state.g2switchworks.com> (raw)
In-Reply-To: <46384CEA.9080906@proventis.net>
References: <46384CEA.9080906@proventis.net>
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()
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: smarlowe@g2switchworks.com, t.markus@proventis.net
Subject: Re: infinite blocking statements in 8.2.3
In-Reply-To: <1178120369.2953.14.camel@state.g2switchworks.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox