pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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()



view thread (9+ messages)  latest in thread

Message-ID: <1178120369.2953.14.camel@state.g2switchworks.com>
Permalink:  ../1178120369.2953.14.camel@state.g2switchworks.com/
Also on:    postgresql.org/message-id/1178120369.2953.14.camel@state.g2switchworks.com

 · 

reply

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