Обсуждение: Implicit transaction not rolling back after error

I'm having difficulty locating the source of a problem our webapp has been running into. Multiple Google searches have
failedme, 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.

Stangely, I don't encounter this issue if I connect directly from my development machine.

   $ psql --version
   psql (PostgreSQL) 9.2.1
   $ psql --host $HOST test
   psql (9.2.1, server 9.0.5)
   WARNING: psql version 9.2, server version 9.0.
            Some psql features might not work.
   SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
   Type "help" for help.

   onelogin_production=> SELECT foo;
   ERROR:  column "foo" does not exist
   LINE 1: SELECT foo;
                  ^
   onelogin_production=> SELECT VERSION();
                                                      version
   -------------------------------------------------------------------------------------------------------------
    PostgreSQL 9.0.5 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit
   (1 row)

I also don't encounter the issue if I connect to the running 9.2 daemon on my development machine either.

Our production webapp also suffers from this problem (which is how we noticed it in the first place). It connects to
thePostgreSQL daemon through a Ruby library (pg) which wraps libpq5. I assumed the only commonality between psql and
ourapplication was libpq5, so upgraded it to a later version (9.2.2) with no luck. 

Does anyone know what this might be? Is it a bug? A feature? A configuration issue? I'm kind of at a loss at this
point.

Thanks in advance for your help.

--
Stephen Touset
Senior Software Engineer
stephen.touset@onelogin.com

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)