Обсуждение: 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 betweensomene 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 betweensomene 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 betweensomene 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
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 betweensomene 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