Re: [GENERAL] Rollback on include error in psql

On 12/29/2014 04:26 PM, David Johnston wrote:
> On Mon, Dec 29, 2014 at 5:09 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>wrote:
>
>     On 12/29/2014 03:56 PM, David Johnston wrote:
>
>         ​So you think psql should issue "COMMIT;" even if it is exiting
>         due to
>
>         "ON_ERROR_STOP"?
>
>
>     I say yes, if it is a non-SQL error. As Viktor stated, SQL errors
>     abort the transaction.
>
>
> ​Ok, so we disagree here because that distinction seems arbitrary and
> decidedly not useful.​
>
>
>         Whether you do or don't can you show me where in the
>         documentation the
>         current behavior is described?
>
>
>     Your biggest issue seems to be with --single-transaction and
>     ON_ERROR_STOP so:
>
>     --single-transaction
>
>          When psql executes a script, adding this option wraps
>     BEGIN/COMMIT around the script to execute it as a single transaction.
>
>     Therefore:
>
>     BEGIN;
>     script
>     COMMIT;
>
>     I would and have agreed with your previous statements that it is not
>     clear enough that \i is not an SQL command and an error with same is
>     ignored by the transaction. Outside of that I see no problem.
>
>
> ​That still leaves ambiguity.  How about:​
>
> ​--single-transaction
> ​    When psql executes a script using this option it explicitly begins
> a transaction at session start and commits that transaction at session
> end.  ​The transaction will commit even if the script is forced to exit
> early due to ON_ERROR_STOP: and if no SQL errors have occurred all
> statements prior to the error-inducing psql meta-command will be
> committed.  For this reason it is not recommended to combine this option
> and ON_ERROR_STOP

if you want the entire script to rollback instead of partially committing.

- instead omit this option and supply the transaction
> commands yourself.
>
> ON_ERROR_STOP
>      [existing wording]
>      As described under the --single-transaction option the commit
> issued at session end will occur prior to psql exiting and could result
> in running script being partially committed.

Works for me:)

>
> David J.
>


--
Adrian Klaver
adrian.klaver@aklaver.com