Re: Rollback on include error in psql

On 12/28/2014 10:06 AM, Viktor Shitkovskiy wrote:
> I include my own scripts. Each of them creates some table or makes some
> changes to existing tables.

It is hard to say where to go from here without more information. The
options you are passing to psql all have caveats:

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.


--single-transaction

     When psql executes a script, adding this option wraps BEGIN/COMMIT
around the script to execute it as a single transaction. This ensures
that either all the commands complete successfully, or no changes are
applied.

     If the script itself uses BEGIN, COMMIT, or ROLLBACK, this option
will not have the desired effects. Also, if the script contains any
command that cannot be executed inside a transaction block, specifying
this option will cause that command (and hence the whole transaction) to
fail.

ON_ERROR_STOP

     By default, command processing continues after an error. When this
variable is set, it will instead stop immediately. In interactive mode,
psql will return to the command prompt; otherwise, psql will exit,
returning error code 3 to distinguish this case from fatal error
conditions, which are reported using error code 1. In either case, any
currently running scripts (the top-level script, if any, and any other
scripts which it may have in invoked) will be terminated immediately. If
the top-level command string contained multiple SQL commands, processing
will stop with the current command.


Without information on what is going on in the individual scripts or the
master script, it would be just a guessing game at this point.

> Yes, I want a complete rollback.
>
>
>     Where is the \include coming from?
>
>     What is in the tableX.cre files?
>
>     So if I am following you want a complete rollback on non-SQL or SQL
>     errors, correct?
>
>         I'm using PostgreSQL 9.2.1.
>
>         P.S. Initially I asked this question at dba.stackexchange.com
>         <http://dba.stackexchange.com>
>         <http://dba.stackexchange.com>__:
>         http://dba.stackexchange.com/__questions/87040/rollback-on-__include-error-in-psql
>         <http://dba.stackexchange.com/questions/87040/rollback-on-include-error-in-psql>
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com