Re: Rollback on include error in psql

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"