X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 508403A3B0F for ; Mon, 25 Oct 2004 12:25:12 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 45523-03 for ; Mon, 25 Oct 2004 11:25:03 +0000 (GMT) Received: from trade-india.com (unknown [203.101.29.36]) by svr1.postgresql.org (Postfix) with SMTP id 1233A3A2B23 for ; Mon, 25 Oct 2004 12:25:01 +0100 (BST) Received: (qmail 18190 invoked from network); 25 Oct 2004 11:19:13 -0000 Received: from valium.trade-india-local.com (HELO ?192.168.0.100?) (192.168.0.100) by system66.trade-india-local.com with SMTP; 25 Oct 2004 11:19:13 -0000 Message-ID: <417CE2DA.4080804@trade-india.com> Date: Mon, 25 Oct 2004 16:56:18 +0530 From: Rajesh Kumar Mallah User-Agent: Mozilla Thunderbird 0.7.3 (X11/20040803) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tsirkin Evgeny Cc: pgsql-admin@postgresql.org Subject: Re: ' in transaction' problem in mod_perl/DBI/DBD-Pg References: <417C912E.3080109@trade-india.com> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/327 X-Sequence-Number: 15320 Hi, Somehow those pids automatically got terminated and my problem was solved. I did not issue any kill as i do not have access to the server. curious how it happened any idea?? Regds mallah. Tsirkin Evgeny wrote: >On Mon, 25 Oct 2004, Rajesh Kumar Mallah wrote: > > > >>Hi, >> >>We run our webserver using mod_perl and perl/DBI/DBD-Pg . >>I have observed that most of the pg backends that block other queries >>are in ' in transaction' state. Usually sending a -INT or -TERM >>to such blocking backends solve the problem. >> >>My question is >> >>1. Are there some precuation to be taken in perl/DBI programming >> in mod_perl environment to avoid backends getting into ' in >>transaction' >> mode? >> >> >Yes.Be very carefull with transaction - that is: >[1] try the backends that are entering transactions to make the updates in >the same order ,so you will not get a deadlock.(read the manual for more >info). >[2] try to make the transactions as short as you can,if you don't need >transactions - don't use it ,turn the Autocommit on! > >[3] befor your updates /deletes it sometimes VERY usefull to do a select >for update which will lock the rows befor entering the transactions ,that >will avoid two backends updating same rows to enter transaction and ,so >avoid deadlocks. > > > >>2. Is it advisable/safe to run a daemon that TERMs such blocking backend >> at regular interval. >> >> > >I don't think so - that will not let postgres end the transactions and >so no changes will be applied,more then that - this may kill the all the >postgres processes . > > > >>3. Most Importantly , If a do not have access to to the host is it >>possible to >> TERMinate such backeds from psql> >> >> > >Don't know. > > >>Regds >>Mallah. >> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 5: Have you checked our extensive FAQ? >> >> http://www.postgresql.org/docs/faqs/FAQ.html >> >> >> > > > -- regds Mallah. Rajesh Kumar Mallah +---------------------------------------------------+ | Tradeindia.com (3,11,246) Registered Users | | Indias' Leading B2B eMarketPlace | | http://www.tradeindia.com/ | +---------------------------------------------------+