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/
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
