Re: transaction started without BEGIN??? Please help!

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(at)pingpong(dot)net> 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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>
>
>