On Mon, Dec 29, 2014 at 8:49 AM, Adrian Klaver <adrian.kla...@aklaver.com> 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. 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.
