killing idle attaches without killing others
Hi:
8.3.4 on Linux
How can one kill just the <IDLE> processes I see attached to a DB (from pg_stat_activity) without disturbing the others? If I need to kill the idle pids one ata time, which signal_name should I use for that?
Thanks !
On Wed, Aug 4, 2010 at 10:03 AM, Gauthier, Dave <dave.gauthier@intel.com>wrote:
How can one kill just the <IDLE> processes I see attached to a DB (from
pg_stat_activity) without disturbing the others? If I need to kill the idle
pids one ata time, which signal_name should I use for that?
Connected to psql as a superuser, issue SELECT pg_cancel_backend(PID); where
PID is the pid of the process to close.
On 4 August 2010 15:18, Vick Khera <vivek@khera.org> wrote:
On Wed, Aug 4, 2010 at 10:03 AM, Gauthier, Dave <dave.gauthier@intel.com>
wrote:How can one kill just the <IDLE> processes I see attached to a DB (from
pg_stat_activity) without disturbing the others? If I need to kill the idle
pids one ata time, which signal_name should I use for that?Connected to psql as a superuser, issue SELECT pg_cancel_backend(PID); where
PID is the pid of the process to close.
That's a SIGINT, but it doesn't actually kill the process, it just
cancels it's current query.
pg_terminate_backend() sends a SIGTERM to terminate the backend. I
think that function is new to 8.4, but you can still manually send the
signal if you're on 8.3.
http://www.postgresql.org/docs/current/static/functions-admin.html
Regards,
Dean
Le 04/08/2010 16:18, Vick Khera a �crit :
On Wed, Aug 4, 2010 at 10:03 AM, Gauthier, Dave <dave.gauthier@intel.com>wrote:
How can one kill just the <IDLE> processes I see attached to a DB (from
pg_stat_activity) without disturbing the others? If I need to kill the idle
pids one ata time, which signal_name should I use for that?Connected to psql as a superuser, issue SELECT pg_cancel_backend(PID); where
PID is the pid of the process to close.
pg_cancel_backend doesn't kill anything. It stops a running query. But
there isn't any here as the connection is idle.
The OP needs pg_terminate_backend, but it only appears in 8.4. The only
thing to do is to upgrade to 8.4. Or fix the software that keeps idle
connections.
--
Guillaume
http://www.postgresql.fr
http://dalibo.com
On 08/04/10 7:03 AM, Gauthier, Dave wrote:
Hi:
8.3.4 on Linux
How can one kill just the <IDLE> processes I see attached to a DB
(from pg_stat_activity) without disturbing the others? If I need to
kill the idle pids one ata time, which signal_name should I use for that?
kill the programs that are making those idle connections.
How does PG determine if a process is <IDLE> ? It there some sort of timeout? I want to be able to distinguish between somene who's interrupted on the phone for a couple minutes vs the guy who left the program running over the weekend.
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Dean Rasheed
Sent: Wednesday, August 04, 2010 10:26 AM
To: Vick Khera
Cc: pgsql-general
Subject: Re: [GENERAL] killing idle attaches without killing others
On 4 August 2010 15:18, Vick Khera <vivek@khera.org> wrote:
On Wed, Aug 4, 2010 at 10:03 AM, Gauthier, Dave <dave.gauthier@intel.com>
wrote:How can one kill just the <IDLE> processes I see attached to a DB (from
pg_stat_activity) without disturbing the others? If I need to kill the idle
pids one ata time, which signal_name should I use for that?Connected to psql as a superuser, issue SELECT pg_cancel_backend(PID); where
PID is the pid of the process to close.
That's a SIGINT, but it doesn't actually kill the process, it just
cancels it's current query.
pg_terminate_backend() sends a SIGTERM to terminate the backend. I
think that function is new to 8.4, but you can still manually send the
signal if you're on 8.3.
http://www.postgresql.org/docs/current/static/functions-admin.html
Regards,
Dean
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, Aug 4, 2010 at 10:50 AM, Gauthier, Dave <dave.gauthier@intel.com> wrote:
How does PG determine if a process is <IDLE> ? It there some sort of timeout? I want to be able to distinguish between somene who's interrupted on the phone for a couple minutes vs the guy who left the program running over the weekend.
It is <IDLE> if it is not currently running a query.
On Wed, 2010-08-04 at 14:40 -0400, Vick Khera wrote:
On Wed, Aug 4, 2010 at 10:50 AM, Gauthier, Dave <dave.gauthier@intel.com> wrote:
How does PG determine if a process is <IDLE> ? It there some sort of timeout? I want to be able to distinguish between somene who's interrupted on the phone for a couple minutes vs the guy who left the program running over the weekend.
It is <IDLE> if it is not currently running a query.
There are two things here.
1. <IDLE>
I wouldn't touch these. It just means a query is not currently running
and will not cause any problems. Further it could cause problems if you
start terminating those backends because it could be an ETL process or
some other long running app that executes a query, takes the results and
starts to process them (which will cause <IDLE>) and then comes back to
do other stuff.
2. <IDLE> In Transaction
This is badness if it lasts for any length of time as it can conflict
with routine maintenance. However again, it could also be doing the same
thing as above.
Sincerely,
Joshua D. Drake
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt