Re: Rollback on include error in psql

On 12/29/2014 07:59 AM, David Johnston wrote:
> On Mon, Dec 29, 2014 at 8:49 AM, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto: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.

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.

>
> 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.​
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com