Re: Unable to commit: transaction marked for rollback

Hi,

It can be caused by other reasones then SQL error. You wrote that error occurs
after 5 hours, if you processing such long time in one transaction, then
server can mark transcation for rollback automaticlly (it can "think",
deadlock occured, too slow, infinite loop, etc...). You need to check Geronimo,
setting about transactions timeouts.

I've looked at error from your example, because I didn't belived such
behavior. Unfortunatly it's true. Postgresql disallow to execute next
statement if error occured - this is realy bad approach, because error can be
from dupliacte keys etc. :(

Regards,
Radek

Dnia czwartek 01 lipiec 2010 o 23:08:02 Kevin Grittner napisał(a):
> David Kerr <dmk(at)mr-paradox(dot)net> wrote:
> >> Have you checked the PostgreSQL log file for clues? If you're
> >> not seeing what you need, you could tweak the logging to show
> >> more.
> >
> > We're trying that, it's (of course) intermittent and happens after
> > like 5 hours. so huge logfiles, etc as usual. With
> > log_statements=none turned on PG logs database errors. so it seems
> > like whatever's happening isn't being considered an "error". more
> > of a natural database transaction (like a lock) or something. does
> > that sound about right?
>
> Hmmm.... I was just guessing at the cause. With default logging,
> if I do this:
>
> test=# begin;
> BEGIN
> test=# select foo;
> ERROR: column "foo" does not exist
> LINE 1: select foo;
> ^
> test=# select 'foo';
> ERROR: current transaction is aborted, commands ignored until end
> of transaction block
> test=# commit;
> ROLLBACK
>
> I see this in the log:
>
> ERROR: column "foo" does not exist at character 8
> STATEMENT: select foo;
> ERROR: current transaction is aborted, commands ignored until end
> of transaction block
> STATEMENT: select 'foo';
>
> If you aren't seeing something like that, I'm probably off-base in
> my guess at the cause. Nothing else leaps to mind, unfortunately.
>
> :-(
>
> -Kevin