Re: Implicit transaction not rolling back after error

On Thu, Dec 20, 2012 at 4:03 PM, Stephen Touset
<stephen.touset@onelogin.com> wrote:
> I'm having difficulty locating the source of a problem our webapp has been running into. Multiple Google searches
havefailed 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
afterany 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)