Re: Implicit transaction not rolling back after error

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)