Re: Rollback on include error in psql

On Mon, Dec 29, 2014 at 8:49 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 12/28/2014 05:04 PM, David G Johnston wrote:
> > Adrian Klaver-4 wrote
> >> 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.
> >
> > really?
>
> Yes. The if, ands and buts for each of the options by themselves much less
> in
> combination would indicate that an answer is dependent on what is actually
> happening in the scripts. We have a fragment of the main script and
> not much information as to what is actually happening in the called
> scripts. See
> below why this is important.
>
> >
> > This seems like a documentation bug (or, at the least worth more
> > documentation explanation) at minimum; two of them probably:
> >
> > 1) it is not documented that "\include" is a valid alias for "\i"
> > 2) the implications of \include being a client-side mechanic and thus,
> > invisible to the server, is not well explained. Specifically that a
> failure
> > to include is the equivalent of simply omitting the statement altogether
> > (aside from the psql warning).
>
> Agreed.
>
> >
> > I would suggest an enhancement whereby psql will send a
> guaranteed-to-fail
> > command to the server upon failing to find an included file - at least in
> > non-interactive mode; in interactive mode the warning is likely
> sufficient
> > though the interplay with auto-commit would be concerning.
> >
> >
> >> The options you are passing to psql all have caveats:
> >
> > I'm not seeing how any of those caveats are coming into play here.
> >
> > The ON_ERROR_STOP behavior is actually surprising since psql does indeed
> > return 3 but even with single transaction and auto-commit=off any updates
> > prior to the include are committed.
> >
> > This isn't that difficult to test...
> > [db]
> > CREATE TABLE testtbl (col text PRIMARY KEY);
> > INSERT INTO testtbl VALUES ( 'value' );
> >
> > [script]
> > UPDATE testtbl SET col = 'some other value';
> > \i some_missing_file.sql
> > UPDATE testtbl SET col = 'yet another value';
> > [/script]
> >
> > execute using:
> >
> > psql --single-transaction --set ON_ERROR_STOP=1 --set AUTOCOMMIT=off -f
> > <script> [db]
> >
> > see warning
> >
> > echo $? returns 3
> >
> > value of testtbl.col is 'some other value'
> >
> > Based upon those caveats processing should have stopped immediately
> (which
> > it does) and thus the transaction (which is there because of
> > single-transaction) should have rolledback due to an explicit commit not
> > being issued and the documented default behavior to discard the
> transaction.
>
> Except one is issued by --single-transaction:
>
> "When psql executes a script, adding this option wraps BEGIN/COMMIT around
> the script to execute it as a single transaction"
>
>
​I'll still support that his report was sufficient for our needs...

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.

Since both ON_ERROR_STOP and --single-transaction are psql-related that
seems like a proper and logical solution.

That all said I agree that adding a manual BEGIN/COMMIT - that would behave
the way you'd expect --single-transaction to behave - would be a more
stable and explicit solution.

David J.​