Search Postgresql Archives

Re: Rollback on include error in psql

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux