Re: idle in transaction with mod_perl

From: <mallah(at)trade-india(dot)com>
To: <pgsql(at)pobox(dot)gr>
Cc: <pgsql-admin(at)postgresql(dot)org>
Subject: Re: idle in transaction with mod_perl
Date: 2002-10-04 14:34:37
Message-ID: 57630.203.145.129.3.1033742077.squirrel@mail.trade-india.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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/

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message mallah 2002-10-04 14:37:52 Re: Replication, High availability
Previous Message Shridhar Daithankar 2002-10-04 14:34:20 Re: Fast Deletion For Large Tables