Skip site navigation (1) Skip section navigation (2)

Re: '<IDLE> in transaction' problem in mod_perl/DBI/DBD-Pg

From: Rajesh Kumar Mallah <mallah(at)trade-india(dot)com>
To: Tsirkin Evgeny <tsurkin(at)mail(dot)jct(dot)ac(dot)il>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: '<IDLE> in transaction' problem in mod_perl/DBI/DBD-Pg
Date: 2004-10-25 11:26:18
Message-ID: 417CE2DA.4080804@trade-india.com (view raw or flat)
Thread:
Lists: pgsql-admin
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 '<IDLE> 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 '<IDLE> 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/			    |
+---------------------------------------------------+


In response to

Responses

pgsql-admin by date

Next:From: Jodi KanterDate: 2004-10-25 12:59:03
Subject: db rename
Previous:From: Rigmor UkuheDate: 2004-10-25 11:02:46
Subject: Index relation size

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group