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/
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 |