One thing I can think of is some kind of inadvertent SQL injection. Somebody put ; begin; into a string that got sent to the server unescaped? The only other thing I can think of is if there was a hole in the logic in one of your explicitly started transactions that allowed the handle to be returned to the pool with a transaction open. I don't know anything about java or tomcat, but it should be possible. Good luck. On 4/21/05, Palle Girgensohn <girgen@xxxxxxxxxxxx> wrote: > 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 > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your > message can get through to the mailing list cleanly > > > > ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
