From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | Stephen Touset <stephen(dot)touset(at)onelogin(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Implicit transaction not rolling back after error |
Date: | 2012-12-22 05:32:24 |
Message-ID: | CAOR=d=0f4Aw39XcR=sUH6auK3B=u_wUWo5-gtJ3_FfHwBGx7GQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Dec 20, 2012 at 4:03 PM, Stephen Touset
<stephen(dot)touset(at)onelogin(dot)com> wrote:
> I'm having difficulty locating the source of a problem our webapp has been running into. Multiple Google searches have failed me, so I'm hoping someone here can help troubleshoot.
>
> When some clients (psql, the webapp) connect to our production database, they become stuck in an aborted transaction after any failed statement. For example:
>
> $ psql --version
> psql (PostgreSQL) 9.0.5
> $ psql test
> psql (9.0.5)
> SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
> Type "help" for help.
>
> test=> SELECT foo;
> ERROR: column "foo" does not exist
> LINE 1: SELECT foo;
> ^
> test=> SELECT VERSION();
> ERROR: current transaction is aborted, commands ignored until end of transaction block
>
> Of course, there is no explicit transaction around the first statement, but no commands can be issued until after a ROLLBACK.
Unless you are running a very specific and fairly old version of
postgresql, there is no such thing as autocommit off / implicit
transactions. I.e. the client IS starting a transaction somewhere
along the line. So you need to figure out where it's happening.
Note that psql has a \set autocommit=on setting that tells psql to
initiate a transaction implicitly. This is not a backend command.
The backend only supports explicit transactions (again, unless you're
running a very specific and old pg version that did support it. It
was killed off quickly due to problems created by that change)
From | Date | Subject | |
---|---|---|---|
Next Message | John R Pierce | 2012-12-22 06:51:11 | Re: Frequent update - how to do? |
Previous Message | Scott Marlowe | 2012-12-22 05:28:20 | Re: data type troubles |