Re: idle in transaction with mod_perl - Mailing list pgsql-admin
From | |
---|---|
Subject | Re: idle in transaction with mod_perl |
Date | |
Msg-id | 57630.203.145.129.3.1033742077.squirrel@mail.trade-india.com Whole thread Raw |
In response to | idle in transaction with mod_perl (Kostis <pgsql@pobox.gr>) |
Responses |
Re: idle in transaction with mod_perl
(Andrew Sullivan <andrew@libertyrms.info>)
|
List | pgsql-admin |
Hey Kotis! I too use mod_perl + postgresql its perfectly ok to have "idle in transaction..." Becoz as soon as DBI -> commits a given transaction the mod_perl process starts a new transaction. the $dbhs' are in a perpetual transaction mode. I gues you must have done DBI->{AutoCommit} = 0 for that to happen. the persistant connections makes all the difference. One advise : please upgrade to postgresql 7.2.2 Regds Mallah. > Hi all. > > Let me start by saying that I've been using postgres 7.1.3 since it came out and have found it > to be rock solid and simply wonderful! :-) > I particularly like the unlimited size varchars which I was waiting like crazy for. ( actually > i think they came out in a previous release which i missed) > > Also for all anoraks out there let me say that performance wise the thing kicks ass! I've been > using it on linux in a dual 1.2Ghz AMD box with SCSI as a web crawler database with up to 30 > crawlers hammering two tables simultaneously, without a glitch. (a table has over 4,000,000 > records and still holds fine) > > Anyway, sorry for the long intro. Back to the point. > > I've a got a production machine running the above version of pg with mod_perl > (with standard DBI v1.13, not the Apache flavor) accessing a database and > i'm getting some idle connections that make me worry. > > postgres 28252 0.0 0.2 6832 3708 ? S 02:59 0:00 postgres: nobody mydbname > [local] idle in transaction > > I can appreciate them being "idle" but why would they be in a "transaction"? > > I've checked all my code and found that it always does a rollback or a commit after each > INSERT/UPDATE SQL statement. Further to this I am very-very sure that statement handles for > SELECTs are always "finished" ($sth->finish) once I'm done reading rows from them. ( I spent > hours verifying this) > > To make things simpler, I tested a senario where the only staments issued where SELECTs. But > the idle connections still come up (ps -auwwx|grep postg) as being in a transaction. > > So what's going on? > > I read a post by another guy that had a similar problem and found out that SELECT statements > can also be considered as transactions which is fine. > > In a desperate attempt to make things as tidy as possible I even went as far as always calling > rollback at the end of the script just to try and clear whatever it is that is in transaction > but nope.... they're still there. > > When I run psql and execute a couple of statements through it and then leave in peace, the > connection becomes idle but not in a transaction, which sort of gives me a warm feeling > inside. Can I achieve this in my situation? > > Any help, solutions, suggestions? > Should I be worried in eirher case? > Can the "idle in transaction" connections turn sour and corrupt my DB or worst still bring > down my server? > > I appreciate any help. > > Thanks, > Kostis > > ---------------------------(end of broadcast)--------------------------- TIP 6: Have you > searched our list archives? > > http://archives.postgresql.org ----------------------------------------- Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/
pgsql-admin by date: