Hi!
I have a huge problem here. On a busy server (a web system, mainly java
servlets/jsp running on tomcat) running postgresql-8.0.2, all of a sudden,
one of the postgres processes gets into the state "idle in transation"
(revealed by `ps axU pgsql').
We use transaction very sparesly, so this is a mystery to me. I've swithed
on logging of all statements, and also added the transaction id. part of
the log is attached. As you can see, the transaction id is incremented, for
every query, until suddenly, it is not incremented anymore, but stays the
same. It seems to me, from lookin at the logs, that postgres enters a
transaction although no BEGIN statement was issued? I hope I'm doing a
misinterpretation of the logs here?
So, in a couple of minutes, all connections are taken and a hundred
postgres procs sit here waiting for the transaction to finish so they can
issue their inserts.
First, there shouldn't be a transaction here at all, how come it starts?
And how come there's no BEGIN in the log, if one is actually started?
My solution when this happens (has happened a couple of times a day on a
busy server) is to kill the process that is reported "idle in transaction".
That way we loose data, I'd rather get it to commit. Is there a way to do
that?
Here are all the logging directives in postgresql.conf:
log_destination = 'syslog'
silent_mode = true
log_line_prefix = '<%x>'
log_statement = 'all'
The attached log was grepped on the problematic PID, btw.
postgresql-8.0.2 running on FreeBSD 4.10. jdbc 8.0.311
The icu patch is applied
(http://people.freebsd.org/~girgen/postgresql-icu/) but I can't see how
that would interfere with transaction handling?
Thanks,
Palle