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"

Using your test case and looking at the logs:

aklaver(at)panda:~> psql -d test -U aklaver -p 5452 --single-transaction --set ON_ERROR_STOP=1 --set AUTOCOMMIT=off -f test_script.sql
UPDATE 1
psql:test_script.sql:2: some_missing_file.sql: No such file or directory

test=> select * from testtbl ;
col
------------------
some other value
(1 row)

aklaver-2014-12-29 06:56:56.889 PST-0LOG: statement: BEGIN
aklaver-2014-12-29 06:56:56.889 PST-0LOG: statement: UPDATE testtbl SET col = 'some other value';
aklaver-2014-12-29 06:56:56.890 PST-129363LOG: statement: COMMIT

Take --single-transaction out of the command:

test=> update testtbl set col = 'value';
UPDATE 1

aklaver(at)panda:~> psql -d test -U aklaver -p 5452 --set ON_ERROR_STOP=1 --set AUTOCOMMIT=off -f test_script.sql
UPDATE 1
psql:test_script.sql:2: some_missing_file.sql: No such file or directory

test=> select * from testtbl ;
col
-------
value
(0 rows)

aklaver-2014-12-29 06:58:54.210 PST-0LOG: statement: BEGIN
aklaver-2014-12-29 06:58:54.210 PST-0LOG: statement: UPDATE testtbl SET col = 'some other value';

Basically the take away is, there is quite a bit of transaction fiddling going on behind
the scenes and ON_ERROR_STOP says it stops processing the command, not that it rollbacks the transaction.
In any case Viktor what wants is to have the entire process either succeed or fail. That would
seem to be best served by not using --single-transaction. Though getting back to caveats, this
would depend on what transaction commands or not or non-transactional statements (VACUUM) are present
in the scripts.

>
> 9.3.5 - Ubuntu 12.04 - apt.postgresql.org
>
> David J.
>
>
>
>
> --
> View this message in context: http://postgresql.nabble.com/Rollback-on-include-error-in-psql-tp5832192p5832233.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>

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