Re: Implicit transaction not rolling back after error

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