Re: [ADMIN] ' in transaction' problem in mod_perl/DBI/DBD-Pg

[Rajesh Kumar Mallah]

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/			    |
+---------------------------------------------------+

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
     subscribe-nomail command to [EMAIL PROTECTED] so that your
     message can get through to the mailing list cleanly