Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id E13CB9FBB9F for ; Wed, 17 Oct 2007 08:16:12 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 73029-10 for ; Wed, 17 Oct 2007 08:15:52 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from rv-out-0910.google.com (rv-out-0910.google.com [209.85.198.191]) by postgresql.org (Postfix) with ESMTP id 887B79FBBE8 for ; Wed, 17 Oct 2007 08:16:07 -0300 (ADT) Received: by rv-out-0910.google.com with SMTP id l15so1507184rvb for ; Wed, 17 Oct 2007 04:16:06 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=FnIFjr9T7HjszYqkRoZVPWiy7nr5gBnZkETWCXqCsmo=; b=UxDG7t6+Rya4PlxZZVCKwxulwApH9WiJqrw0OjIYWK8K8JnCGUnzp5UojdCd1lbcOt5MrvRoQD9LRQ5SqQkTh4vX/QRh0Dja5f7EcMtNeDMALcvzopPou1A0IxC0jOirN78R5aROFvzmVN8eCelnSkQxJZ+EoeqPm3INg1MFz8U= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=SpjaMn6HkXwaXHqZDICKomXJu+0r5BNCdrQ+XmPvkntmtqJkTh6YEpkXlNMiLQ60VX1GUSqKTlHd+xQmG8WLoFEI2XUKFwnCZ4nndEvCsmQ8duCyPGEc8yogVdPrBDbOtiG+OKjvOgslJslksku81iL4sagjKBX3UT77ELt3Exc= Received: by 10.141.107.13 with SMTP id j13mr4096139rvm.1192619765855; Wed, 17 Oct 2007 04:16:05 -0700 (PDT) Received: by 10.141.90.15 with HTTP; Wed, 17 Oct 2007 04:16:05 -0700 (PDT) Message-ID: <82e1a9bd0710170416p77d67fc2sd28aa07f531450f0@mail.gmail.com> Date: Wed, 17 Oct 2007 16:46:05 +0530 From: "Moiz Kothari" To: "Jonah H. Harris" Subject: Re: Is there a way to kill a connection from the pg_stat_activitly list? Cc: "Jessica Richard" , "Scott Marlowe" , "Tommy Gildseth" , pgsql-admin@postgresql.org In-Reply-To: <36e682920710160605n338de99fk300cd3062bd0417e@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_25344_32202908.1192619765859" References: <379974.90208.qm@web56402.mail.re3.yahoo.com> <36e682920710160605n338de99fk300cd3062bd0417e@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/152 X-Sequence-Number: 27066 ------=_Part_25344_32202908.1192619765859 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi, Please do not use kill, it sometimes resets all the connection on your db, you might want to use pkill -f to kill the connection you want to. Regards, Moiz Kothari On 10/16/07, Jonah H. Harris wrote: > > On 10/16/07, Jessica Richard wrote: > > select pg_cancel_backend(procpid) solved half of my problem...at least > it > > terminated the query for that user... but it is still holding a user > > connection in IDLE state....If I have too many of those, Postgres may > run of > > out of user connections.... > > > > I already knew how to kill a connection if the connection is from the > local > > host. But I have many remote connections coming from different > machines... > > hard to kill with unix command "kill"... One time, I was testing to > kill a > > particular connection on a testing machine, the entrie Postgres was > brought > > down.... > > That's why I said to write a C stored procedure to do it and install > it on the server. That way you could call it the same way as > pg_cancel_backend. > > > I need to find a safer, cleaner way to disconnect a user from Postgres > when > > needed. > > At this point in time, there isn't one. > > -- > Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 > EnterpriseDB Corporation | fax: 732.331.1301 > 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com > Edison, NJ 08837 | http://www.enterprisedb.com/ > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Hobby Site : http://dailyhealthtips.blogspot.com ------=_Part_25344_32202908.1192619765859 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi,

Please do not use kill, it sometimes resets all the connection on your db, you might want to use

pkill -f <string in ps -ef> to kill the connection you want to.

Regards,
Moiz Kothari

On 10/16/07, Jonah H. Harris <jonah.harris@gmail.com> wrote:
On 10/16/07, Jessica Richard <rjessil@yahoo.com> wrote:
> select pg_cancel_backend(procpid) solved half of my problem...at least it
> terminated the query for that user... but it is still holding a user
> connection in IDLE state....If I have too many of those, Postgres may run of
> out of user connections....
>
> I already knew how to kill a connection if the connection is from the local
> host. But I have many remote connections coming from different machines...
> hard to kill with unix command "kill"...  One time, I was testing to kill a
> particular connection on a testing machine, the entrie Postgres was brought
> down....

That's why I said to write a C stored procedure to do it and install
it on the server.  That way you could call it the same way as
pg_cancel_backend.

> I need to find a safer, cleaner way to disconnect a user from Postgres when
> needed.

At this point in time, there isn't one.

--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation                | fax: 732.331.1301
499 Thornall Street, 2nd Floor          | jonah.harris@enterprisedb.com
Edison, NJ 08837                        | http://www.enterprisedb.com/

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly



--
Hobby Site : http://dailyhealthtips.blogspot.com ------=_Part_25344_32202908.1192619765859--