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
