Re: Rollback on include error in psql

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?

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

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.

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.