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