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>
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-26 01:36 ` Re: '<IDLE> in transaction' problem in mod_perl/DBI/DBD-Pg environment. Greg Sabino Mullane <greg@turnstep.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 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 ` 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>
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 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 ` 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>
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 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 ` Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
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-25 05:37 '<IDLE> in transaction' problem in mod_perl/DBI/DBD-Pg environment. Rajesh Kumar Mallah <mallah@trade-india.com>
@ 2004-10-26 01:36 ` Greg Sabino Mullane <greg@turnstep.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