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

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.