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

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.