pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
'<IDLE> in transaction' problem in mod_perl/DBI/DBD-Pg  environment.
5+ messages / 3 participants
[nested] [flat]

* '<IDLE> in transaction' problem in mod_perl/DBI/DBD-Pg  environment.
@ 2004-10-25 05:37  Rajesh Kumar Mallah <mallah@trade-india.com>
  0 siblings, 2 replies; 5+ messages in thread

From: Rajesh Kumar Mallah @ 2004-10-25 05:37 UTC (permalink / raw)
  To: pgsql-admin


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?

2. Is it advisable/safe to run a daemon that TERMs such blocking backend
    at regular interval.

3. Most Importantly , If a do not have access to to the host is it 
possible to
    TERMinate such backeds from  psql>

Regds
Mallah.




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: '<IDLE> in transaction' problem in mod_perl/DBI/DBD-Pg
@ 2004-10-25 07:48  Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
  parent: Rajesh Kumar Mallah <mallah@trade-india.com>
  1 sibling, 1 reply; 5+ messages in thread

From: Tsirkin Evgeny @ 2004-10-25 07:48 UTC (permalink / raw)
  To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: pgsql-admin

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
> 

-- 
Evgeny.



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: '<IDLE> in transaction' problem in mod_perl/DBI/DBD-Pg
@ 2004-10-25 11:26  Rajesh Kumar Mallah <mallah@trade-india.com>
  parent: Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
  0 siblings, 1 reply; 5+ messages in thread

From: Rajesh Kumar Mallah @ 2004-10-25 11:26 UTC (permalink / raw)
  To: Tsirkin Evgeny <tsurkin@mail.jct.ac.il>; +Cc: 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/			    |
+---------------------------------------------------+




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: '<IDLE> in transaction' problem in mod_perl/DBI/DBD-Pg
@ 2004-10-25 13:42  Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
  parent: Rajesh Kumar Mallah <mallah@trade-india.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Tsirkin Evgeny @ 2004-10-25 13:42 UTC (permalink / raw)
  To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: pgsql-admin

probably you had a deadlock and the postgresql solved it 
by itself if that is true - you will have another one soon.

On Mon, 25 Oct 2004, Rajesh Kumar Mallah wrote:

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

-- 
Evgeny.



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: '<IDLE> in transaction' problem in mod_perl/DBI/DBD-Pg environment.
@ 2004-10-26 01:36  Greg Sabino Mullane <greg@turnstep.com>
  parent: Rajesh Kumar Mallah <mallah@trade-india.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Greg Sabino Mullane @ 2004-10-26 01:36 UTC (permalink / raw)
  To: pgsql-admin; +Cc: mallah@trade-india.com


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
                                                                                                                             
                                                                                                                             
> Are there some precuation to be taken in perl/DBI programming
> in mod_perl environment to avoid backends getting into '<IDLE> in
> transaction' mode?
                                                                                                                             
If you are not using the latest version of DBD::Pg (1.32), then you
may be experiencing the problem where the "AutoCommit" setting
causes "execute->commit->begin->wait." This is fixed in 1.32: it
now does "begin->execute->commit->wait".
 
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200410252136
 
-----BEGIN PGP SIGNATURE-----
 
iD8DBQFBfapWvJuQZxSWSsgRAn/aAJsFNzN1ru5GpjTL30iM1Oj2EXQ9/QCfTFYa
LxQN3UVonFcFpKYEkf0ypog=
=Hc+B
-----END PGP SIGNATURE-----





^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2004-10-26 01:36 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-25 05:37 '<IDLE> in transaction' problem in mod_perl/DBI/DBD-Pg  environment. Rajesh Kumar Mallah <mallah@trade-india.com>
2004-10-25 07:48 ` Re: '<IDLE> in transaction' problem in mod_perl/DBI/DBD-Pg Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-10-25 11:26   ` Re: '<IDLE> in transaction' problem in mod_perl/DBI/DBD-Pg Rajesh Kumar Mallah <mallah@trade-india.com>
2004-10-25 13:42     ` Re: '<IDLE> in transaction' problem in mod_perl/DBI/DBD-Pg Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-10-26 01:36 ` Re: '<IDLE> in transaction' problem in mod_perl/DBI/DBD-Pg environment. Greg Sabino Mullane <greg@turnstep.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox