Re: Rollback on include error in psql - Mailing list pgsql-general

On 12/29/2014 08:49 AM, David Johnston wrote:

On Mon, Dec 29, 2014 at 9:39 AM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>wrote:

    On 12/29/2014 07:59 AM, David Johnston wrote:


        Anyway, the third undocumented bug is that --single-transactions
        gets to
        send its COMMIT even if ON_ERROR_STOP​
        ​takes hold before the end of the script.  I imagined it such
        that only
        if every statement in the "-f <script>" was called would the
        COMMIT be
        issued - thus the error_stop would supercede and leave the session
        uncommitted and by default rolledback.


    Not seeing the bug. --single-transaction wraps the entire script in
    BEGIN/COMMIT, ON_ERROR_STOP stops 'processing' the command, nothing
    in there about stopping transaction or rollback. So the failed \i
    stops the script from processing anything after that and the session
    goes directly to the COMMIT. If you want to deal with transactions
    there is ON_ERROR_ROLLBACK. Though I did find something interesting
    about that, which will subject of another post.


​Then --single-transaction has nothing to do with the script file
at-all.  It should be documented as issuing a BEGIN at session connect
and a COMMIT just before session disconnect - regardless of whether the
named script executes to completion, which can happen if it is combined
with ON_ERROR_STOP.

Seems to me when you do:

psql  --single-transaction -f some_script

the script is the session.

ON_ERROR_STOP
" ..psql will exit, returning error code 3 to distinguish this case from fatal error conditions, which are reported using error code 1"

So psql does not see this a fatal error.

This is one of those glass half full/empty situations, where it is down to the eye of the beholder. I would also say this a perfect example of why tests are written, to see what actually happens versus what you think happens.