Обсуждение: 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 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.
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.
test=> SELECT foo;
ERROR: column "foo" does not exist
LINE 1: SELECT foo;
^
test=> 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 the PostgreSQL daemon
through a Ruby library (pg) which wraps libpq5. I assumed the only
commonality between psql and our application 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 12/20/2012 03:11 PM, Stephen Touset 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. > > > > 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 the PostgreSQL daemon > through a Ruby library (pg) which wraps libpq5. I assumed the only > commonality between psql and our application 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. Well for the psql case check out: http://www.postgresql.org/docs/9.0/interactive/app-psql.html " AUTOCOMMIT When on (the default), each SQL command is automatically committed upon successful completion. To postpone commit in this mode, you must enter a BEGIN or START TRANSACTION SQL command. When off or unset, SQL commands are not committed until you explicitly issue COMMIT or END. The autocommit-off mode works by issuing an implicit BEGIN for you, just before any command that is not already in a transaction block and is not itself a BEGIN or other transaction-control command, nor a command that cannot be executed inside a transaction block (such as VACUUM). Note: In autocommit-off mode, you must explicitly abandon any failed transaction by entering ABORT or ROLLBACK. Also keep in mind that if you exit the session without committing, your work will be lost. Note: The autocommit-on mode is PostgreSQL's traditional behavior, but autocommit-off is closer to the SQL spec. If you prefer autocommit-off, you might wish to set it in the system-wide psqlrc file or your ~/.psqlrc file. " Not sure about the Ruby case. > > Thanks in advance for your help. > -- Adrian Klaver adrian.klaver@gmail.com
On Dec 20, 2012, at 3:27 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
> When on (the default), each SQL command is automatically committed upon successful completion. To postpone commit in
thismode, you must enter a BEGIN or START TRANSACTION SQL command. When off or unset, SQL commands are not committed
untilyou explicitly issue COMMIT or END. The autocommit-off mode works by issuing an implicit BEGIN for you, just
beforeany command that is not already in a transaction block and is not itself a BEGIN or other transaction-control
command,nor a command that cannot be executed inside a transaction block (such as VACUUM).
>
> Note: In autocommit-off mode, you must explicitly abandon any failed transaction by entering ABORT or ROLLBACK. Also
keepin mind that if you exit the session without committing, your work will be lost.
> Note: The autocommit-on mode is PostgreSQL's traditional behavior, but autocommit-off is closer to the SQL spec. If
youprefer autocommit-off, you might wish to set it in the system-wide psqlrc file or your ~/.psqlrc file.
In this case, not only is AUTOCOMMIT set to "on", but I wasn't even able to turn it off for testing purposes.
test=# SET AUTOCOMMIT TO off;
ERROR: SET AUTOCOMMIT TO OFF is no longer supported
So yes, AUTOCOMMIT is definitely on.
--
Stephen Touset
Senior Software Engineer
stephen.touset@onelogin.com
On Dec 20, 2012, at 3:27 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
> When on (the default), each SQL command is automatically committed upon successful completion. To postpone commit in
thismode, you must enter a BEGIN or START TRANSACTION SQL command. When off or unset, SQL commands are not committed
untilyou explicitly issue COMMIT or END. The autocommit-off mode works by issuing an implicit BEGIN for you, just
beforeany command that is not already in a transaction block and is not itself a BEGIN or other transaction-control
command,nor a command that cannot be executed inside a transaction block (such as VACUUM).
>
> Note: In autocommit-off mode, you must explicitly abandon any failed transaction by entering ABORT or ROLLBACK. Also
keepin mind that if you exit the session without committing, your work will be lost.
> Note: The autocommit-on mode is PostgreSQL's traditional behavior, but autocommit-off is closer to the SQL spec. If
youprefer autocommit-off, you might wish to set it in the system-wide psqlrc file or your ~/.psqlrc file.
Actually, you may be onto something.
test=> COMMIT;
WARNING: there is no transaction in progress
COMMIT
onelogin_production=> SHOW AUTOCOMMIT;
autocommit
------------
on
(1 row)
test=> COMMIT;
COMMIT
If I try to do a bare "COMMIT", I get a warning that there's no transaction in progress. But if I do a simple SHOW, and
COMMITafterward, I get no such warning (indicating that I'm still inside of an uncommitted transaction). However,
obviously,the client is telling me explicitly in the provided log that AUTOCOMMIT is on.
--
Stephen Touset
Senior Software Engineer
stephen.touset@onelogin.com
On 12/20/2012 04:33 PM, Stephen Touset wrote: > > On Dec 20, 2012, at 3:27 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > >> When on (the default), each SQL command is automatically committed upon successful completion. To postpone commit in thismode, you must enter a BEGIN or START TRANSACTION SQL command. When off or unset, SQL commands are not committed untilyou explicitly issue COMMIT or END. The autocommit-off mode works by issuing an implicit BEGIN for you, just beforeany command that is not already in a transaction block and is not itself a BEGIN or other transaction-control command,nor a command that cannot be executed inside a transaction block (such as VACUUM). >> >> Note: In autocommit-off mode, you must explicitly abandon any failed transaction by entering ABORT or ROLLBACK. Also keepin mind that if you exit the session without committing, your work will be lost. >> Note: The autocommit-on mode is PostgreSQL's traditional behavior, but autocommit-off is closer to the SQL spec. If youprefer autocommit-off, you might wish to set it in the system-wide psqlrc file or your ~/.psqlrc file. > > In this case, not only is AUTOCOMMIT set to "on", but I wasn't even able to turn it off for testing purposes. > > test=# SET AUTOCOMMIT TO off; > ERROR: SET AUTOCOMMIT TO OFF is no longer supported > > So yes, AUTOCOMMIT is definitely on. > What does \set show when entered from the psql command line?
On Dec 20, 2012, at 3:40 PM, Rob Sargent <robjsargent@gmail.com> wrote: > On 12/20/2012 04:33 PM, Stephen Touset wrote: > >> So yes, AUTOCOMMIT is definitely on. > > What does \set show when entered from the psql command line? test=> \set AUTOCOMMIT = 'OFF' *facepalm*. Turns out someone put a .psqlrc with autocommit off in /etc/skel when the box was originally set up as a replacement forour previous app server. Account users were created afterwards, and the change propagated to our application account aswell as all of our individual accounts. Why, though, would `SHOW AUTOCOMMIT` lie? And `SET AUTOCOMMIT TO off` says that capability is disabled. So how does the configfile manage to do it? Thanks for the insight! -- Stephen Touset Senior Software Engineer stephen.touset@onelogin.com
On Thu, Dec 20, 2012 at 7:04 PM, Stephen Touset <stephen.touset@onelogin.com> wrote: > On Dec 20, 2012, at 3:40 PM, Rob Sargent <robjsargent@gmail.com> wrote: > >> On 12/20/2012 04:33 PM, Stephen Touset wrote: >> >>> So yes, AUTOCOMMIT is definitely on. >> >> What does \set show when entered from the psql command line? > > test=> \set > AUTOCOMMIT = 'OFF' > > *facepalm*. \set is a psql command > Turns out someone put a .psqlrc with autocommit off in /etc/skel when the box was originally set up as a replacement forour previous app server. Account users were created afterwards, and the change propagated to our application account aswell as all of our individual accounts. > > Why, though, would `SHOW AUTOCOMMIT` lie? And `SET AUTOCOMMIT TO off` says that capability is disabled. So how does theconfig file manage to do it? show variable is a SQL command to the backend engine. The backend does not support autocommit on / off (it did once upon a time for a little while but it broke lots of stuff and got reverted). autocommit is now firmly a client side behavior, not a backend behavior.