Re: idle in transaction with mod_perl

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/