Thread: Rollback on include error in psql

On 12/28/2014 12:02 AM, Viktor Shitkovskiy wrote:
> Hello.
>
> I'm trying to execute an sql script file in a single transation. The
> file contains includes for some other scripts which in my example create
> some tables. It looks like this:
>      \include ../tables/table1.cre
>      \include ../tables/table2.cre
>      ...
>      \include ../tables/table10.cre
>
> I'm executing it using psql:
>      psql -X --set AUTOCOMMIT=off --set ON_ERROR_STOP=on -e
> --single-transaction -d my_db -f my_script.sql
>
> The problem is that errors with the include meta command do not cause a
> transactiopn rollback. e.g. if some of tableX.cre files is missing, any
> changes before its include will be commited. However, if there's some
> SQL syntax error, everyting works as expected.
>
> Is it possible to somehow handle include related errors and rollback the
> active transcation?
>

Where is the \include coming from?

What is in the tableX.cre files?

So if I am following you want a complete rollback on non-SQL or SQL
errors, correct?

> I'm using PostgreSQL 9.2.1.
>
> P.S. Initially I asked this question at dba.stackexchange.com
> <http://dba.stackexchange.com>:
> http://dba.stackexchange.com/questions/87040/rollback-on-include-error-in-psql


--
Adrian Klaver
adrian.klaver@aklaver.com

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. The
options you are passing to psql all have caveats:

AUTOCOMMIT

     When on (the default), each SQL command is automatically committed
upon successful completion. To postpone commit in this mode, you must
enter a BEGIN or START TRANSACTION SQL command. When off or unset, SQL
commands are not committed until you explicitly issue COMMIT or END. The
autocommit-off mode works by issuing an implicit BEGIN for you, just
before any command that is not already in a transaction block and is not
itself a BEGIN or other transaction-control command, nor a command that
cannot be executed inside a transaction block (such as VACUUM).

         Note: In autocommit-off mode, you must explicitly abandon any
failed transaction by entering ABORT or ROLLBACK. Also keep in mind that
if you exit the session without committing, your work will be lost.


--single-transaction

     When psql executes a script, adding this option wraps BEGIN/COMMIT
around the script to execute it as a single transaction. This ensures
that either all the commands complete successfully, or no changes are
applied.

     If the script itself uses BEGIN, COMMIT, or ROLLBACK, this option
will not have the desired effects. Also, if the script contains any
command that cannot be executed inside a transaction block, specifying
this option will cause that command (and hence the whole transaction) to
fail.

ON_ERROR_STOP

     By default, command processing continues after an error. When this
variable is set, it will instead stop immediately. In interactive mode,
psql will return to the command prompt; otherwise, psql will exit,
returning error code 3 to distinguish this case from fatal error
conditions, which are reported using error code 1. In either case, any
currently running scripts (the top-level script, if any, and any other
scripts which it may have in invoked) will be terminated immediately. If
the top-level command string contained multiple SQL commands, processing
will stop with the current command.


Without information on what is going on in the individual scripts or the
master script, it would be just a guessing game at this point.

> Yes, I want a complete rollback.
>
>
>     Where is the \include coming from?
>
>     What is in the tableX.cre files?
>
>     So if I am following you want a complete rollback on non-SQL or SQL
>     errors, correct?
>
>         I'm using PostgreSQL 9.2.1.
>
>         P.S. Initially I asked this question at dba.stackexchange.com
>         <http://dba.stackexchange.com>
>         <http://dba.stackexchange.com>__:
>         http://dba.stackexchange.com/__questions/87040/rollback-on-__include-error-in-psql
>         <http://dba.stackexchange.com/questions/87040/rollback-on-include-error-in-psql>
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

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.

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@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@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.klaver@aklaver.com

On 12/29/2014 07:59 AM, David Johnston wrote:
> On Mon, Dec 29, 2014 at 8:49 AM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@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.

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@aklaver.com

On 12/29/2014 08:49 AM, David Johnston wrote:
> On Mon, Dec 29, 2014 at 9:39 AM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>wrote:
>
>     On 12/29/2014 07:59 AM, David Johnston wrote:
>
>
>         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.
>
>
> ​Then --single-transaction has nothing to do with the script file
> at-all.  It should be documented as issuing a BEGIN at session connect
> and a COMMIT just before session disconnect - regardless of whether the
> named script executes to completion, which can happen if it is combined
> with ON_ERROR_STOP.

Seems to me when you do:

psql  --single-transaction -f some_script

the script is the session.

ON_ERROR_STOP
" ..psql will exit, returning error code 3 to distinguish this case from
fatal error conditions, which are reported using error code 1"

So psql does not see this a fatal error.

This is one of those glass half full/empty situations, where it is down
to the eye of the beholder. I would also say this a perfect example of
why tests are written, to see what actually happens versus what you
think happens.

>
> David J.
>
>
> ​


--
Adrian Klaver
adrian.klaver@aklaver.com

On 12/29/2014 09:38 AM, David Johnston wrote:
> Copying -bugs to gain broader attention and opinions.
>
> On Mon, Dec 29, 2014 at 10:06 AM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>wrote:
>
>     On 12/29/2014 08:49 AM, David Johnston wrote:
>
>         On Mon, Dec 29, 2014 at 9:39 AM, Adrian Klaver
>         <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>         <mailto:adrian.klaver@aklaver.__com
>         <mailto:adrian.klaver@aklaver.com>>>wrote:
>
>
>              On 12/29/2014 07:59 AM, David Johnston wrote:
>
>
>                  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.
>
>
>         ​Then --single-transaction has nothing to do with the script file
>         at-all.  It should be documented as issuing a BEGIN at session
>         connect
>         and a COMMIT just before session disconnect - regardless of
>         whether the
>         named script executes to completion, which can happen if it is
>         combined
>         with ON_ERROR_STOP.
>
>
>     Seems to me when you do:
>
>     psql  --single-transaction -f some_script
>
>     the script is the session.
>
>     ON_ERROR_STOP
>     " ..psql will exit, returning error code 3 to distinguish this case
>     from fatal error conditions, which are reported using error code 1"
>
>     So psql does not see this a fatal error.
>
>     This is one of those glass half full/empty situations, where it is
>     down to the eye of the beholder. I would also say this a perfect
>     example of why tests are written, to see what actually happens
>     versus what you think happens.
>
>
> ​If a user of our product needs to run a test to determine behavior then
> our documentation is flawed - which is the point I am making.

Still not seeing the flaw in the documentation.

>
> ​psql does not see any error due to meta-commands or SQL as fatal -
> which is why the ON_ERROR_STOP option exists.

And ON_ERROR_STOP does not change that. All it does is toggle whether
psql continues on after an error or stops processing commands.

>
> I believe that if ON_ERROR_STOP causes an abort that the COMMIT from
> --single-transaction should not run.  That is a behavior change.  But
> not documenting the known and deterministic interaction between the two
> options is a bug.

I am not seeing anything in the below that says an ABORT is issued:

ON_ERROR_STOP

     By default, command processing continues after an error. When this
variable is set, it will instead stop immediately. In interactive mode,
psql will return to the command prompt; otherwise, psql will exit,
returning error code 3 to distinguish this case from fatal error
conditions, which are reported using error code 1. In either case, any
currently running scripts (the top-level script, if any, and any other
scripts which it may have in invoked) will be terminated immediately. If
the top-level command string contained multiple SQL commands, processing
will stop with the current command.

I do see it here though:

ON_ERROR_ROLLBACK

     When on, if a statement in a transaction block generates an error,
the error is ignored and the transaction continues. When interactive,
such errors are only ignored in interactive sessions, and not when
reading script files. When off (the default), a statement in a
transaction block that generates an error aborts the entire transaction.
The on_error_rollback-on mode works by issuing an implicit SAVEPOINT for
you, just before each command that is in a transaction block, and rolls
back to the savepoint on error.


>
> ​Since the undesirable behavior can be easily worked around by simply
> omitting --single-transaction and writing your own BEGIN/COMMIT into the
> script I don't see that there is going to be a high priority or desire
> to change the behavior and introduce a backward incompatibility; fine.
>
> The other two bugs I see are:
>
> 1) it is not documented that "\include" is a valid alias for "\i"
> (simple fix, see meta-command "\c" or "\connect")
>
> 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.e., if in an actual
> transaction the server will not issue the standard "error has occurred,
> you must ROLLBACK." message for any subsequent statements in the
> script.  This is probably not to the level of a bug but it is related to
> the ON_ERROR_STOP bug.

I could see improving the wording on this, to let the user know that
includes are on them as Viktor already determined and took action on.

>
> I personally consider the issuance of COMMIT following a determination
> of ON_ERROR_STOP to be a bug as well.  Error handling mechanics should
> take precedence over transaction handling mechanics and if done as such
> the promise of --single-transaction would hold since the failure of
> \include would abort the session and cause an implicit rollback.

>
> David J.​
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 12/29/2014 02:28 PM, David Johnston wrote:
> On Mon, Dec 29, 2014 at 3:07 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>wrote:
>
>     On 12/29/2014 09:38 AM, David Johnston wrote:
>
>
>              This is one of those glass half full/empty situations,
>         where it is
>              down to the eye of the beholder. I would also say this a
>         perfect
>              example of why tests are written, to see what actually happens
>              versus what you think happens.
>
>
>         ​If a user of our product needs to run a test to determine
>         behavior then
>         our documentation is flawed - which is the point I am making.
>
>
>     Still not seeing the flaw in the documentation.
>
>
> ​...
> ​
>
>
>
>         ​psql does not see any error due to meta-commands or SQL as fatal -
>         which is why the ON_ERROR_STOP option exists.
>
>
>     And ON_ERROR_STOP does not change that. All it does is toggle
>     whether psql continues on after an error or stops processing commands.
>
>
> ​If it walks and talks like a duck...the fact that ON_ERROR_STOP makes
> psql halt processing means that it now treats them like it does any
> other fatal error.​

But it does not:

ON_ERROR_STOP

     By default, command processing continues after an error. When this
variable is set, it will instead stop immediately. In interactive mode,
psql will return to the command prompt; otherwise,

<HIGHLIGHT> psql will exit, returning error code 3 to distinguish this
case from fatal error conditions, which are reported using error code
1.<HIGHLIGHT>

In either case, any currently running scripts (the top-level script, if
any, and any other scripts which it may have in invoked) will be
terminated immediately. If the top-level command string contained
multiple SQL commands, processing will stop with the current command.

>
>
>
>
>         I believe that if ON_ERROR_STOP causes an abort that the COMMIT from
>         --single-transaction should not run.  That is a behavior
>         change.  But
>         not documenting the known and deterministic interaction between
>         the two
>         options is a bug.
>
>
>     I am not seeing anything in the below that says an ABORT is issued:
>
>
> ​I was using term in its non-SQL sense: to stop processing and return
> control to the user.​

So if is non-SQL why should the transaction care about it?

>
>         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.e., if in an actual
>         transaction the server will not issue the standard "error has
>         occurred,
>         you must ROLLBACK." message for any subsequent statements in the
>         script.  This is probably not to the level of a bug but it is
>         related to
>         the ON_ERROR_STOP bug.
>
>
>     I could see improving the wording on this, to let the user know that
>     includes are on them as Viktor already determined and took action on.
>
>
> ​I think you have a typo somewhere here 'cause that sentence fragment
> (...includes and on them as) makes no sense to me.​

Should have been clearer. I am saying that it would be good to tell
users that using \i(nclude) puts the burden on them to verify the
included scripts actually can be found.

>
> The overall complaint is that a missing \include file, without
> ON_ERROR_STOP, ​ends up being totally ignored even while in
> non-interactive mode.  I get the benefit to that behavior in interactive
> mode and so being required to use ON_ERROR_STOP in script mode (which is
> the safest practice anyway) isn't that big a deal as long as in that
> mode a failure causes an immediate stop without any other SQL being sent
> to the server and, by extension, the session closing and effecting a
> rollback in the process if in --single-transaction mode just like that
> mode promises.
>
> I'm not sure why --single-transaction even exists TBH.  The script
> should determine its desired transaction modes and not leave the
> decision up to the caller.  If the script relies on all-or-nothing it
> should have explicit BEGIN/COMMIT statements.
>
> That said it does exist so it should play nicely with ON_ERROR_STOP.  It
> currently does not nor is the not-nice interaction documented anywhere.
>
> David J.


--
Adrian Klaver
adrian.klaver@aklaver.com

On 12/29/2014 02:55 PM, David Johnston wrote:
> On Mon, Dec 29, 2014 at 3:37 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>wrote:
>
>     On 12/29/2014 02:28 PM, David Johnston wrote:
>
>         On Mon, Dec 29, 2014 at 3:07 PM, Adrian Klaver
>         <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>         <mailto:adrian.klaver@aklaver.__com
>         <mailto:adrian.klaver@aklaver.com>>>wrote:
>
>              On 12/29/2014 09:38 AM, David Johnston wrote:
>
>
>                       This is one of those glass half full/empty situations,
>                  where it is
>                       down to the eye of the beholder. I would also say
>         this a
>                  perfect
>                       example of why tests are written, to see what
>         actually happens
>                       versus what you think happens.
>
>
>                  ​If a user of our product needs to run a test to determine
>                  behavior then
>                  our documentation is flawed - which is the point I am
>         making.
>
>
>              Still not seeing the flaw in the documentation.
>         ​​
>         ​...
>         ​
>                  ​psql does not see any error due to meta-commands or
>         SQL as fatal -
>                  which is why the ON_ERROR_STOP option exists.
>
>
>              And ON_ERROR_STOP does not change that. All it does is toggle
>              whether psql continues on after an error or stops
>         processing commands.
>
>
>
> If it walks and talks like a duck...the fact that ON_ERROR_STOP
>         makes
>         psql halt processing means that it now treats them like it does any
>         other fatal error.​
>
>
>     But it does not:
>
>     ON_ERROR_STOP
>
>          By default, command processing continues after an error. When
>     this variable is set, it will instead stop immediately. In
>     interactive mode, psql will return to the command prompt; otherwise,
>
>     <HIGHLIGHT> psql will exit, returning error code 3 to distinguish
>     this case from fatal error conditions, which are reported using
>     error code 1.<HIGHLIGHT>
>
>     In either case, any currently running scripts (the top-level script,
>     if any, and any other scripts which it may have in invoked) will be
>     terminated immediately. If the top-level command string contained
>     multiple SQL commands, processing will stop with the current command.
>
>
> ​I am not seeing what point you are trying to make here.​  psql exits -
> my contention is that it should do so before issuing "COMMIT;" if
> --single-transaction was specified.  I really don't care what made psql
> exit - a fatal error or a non-fatal one while running under ON_ERROR_STOP.

I am having trouble keeping up with this line of reasoning:

"​psql does not see any error due to meta-commands or SQL as fatal -
which is why the ON_ERROR_STOP option exists.
"

"
If it walks and talks like a duck...the fact that ON_ERROR_STOP
makes psql halt processing means that it now treats them like it does
any other fatal error.​

"
"I really don't care what made psql exit.."

At this point I agree to disagree.

>
> I can find out the root cause by checking for either a 3 or a 1 but what
> am I supposed to do with that information?  More specifically, what
> should I do if I see a 3 that I wouldn't do if I see a 1; and
> vice-versa.  As a user I really don't care I just want to know that any
> changes my script may have performed prior to the error have been rolled
> back if psql exits with a non-zero status.

Then why have return status codes?

>
>
>                  I believe that if ON_ERROR_STOP causes an abort that
>         the COMMIT from
>                  --single-transaction should not run.  That is a behavior
>                  change.  But
>                  not documenting the known and deterministic interaction
>         between
>                  the two
>                  options is a bug.
>
>
>              I am not seeing anything in the below that says an ABORT is
>         issued:
>
>
>         ​I was using term in its non-SQL sense: to stop processing and
>         return
>         control to the user.​
>
>
>     So if is non-SQL why should the transaction care about it?
>
>
> ​The transaction doesn't - but psql allows me to do non-SQL stuff along
> side of SQL stuff and I want the entire thing to fail if either the SQL
> or the non-SQL stuff has a problem.  It is incumbent upon psql to make
> the boundary between the two as invisible as possible and right now it
> does not do as good a job as it could.

psql is a client not an all knowing entity. Not sure it is in its remit
to monitor all possible interactions of database commands and non
database commands. For instance, you have in a script a function written
in plpythonu that sends email and in the same script a line that runs
that function to send an email. Do you expect psql to abort everything
if the receiving email server rejects the message? A contrived example
to be sure, but not entirely out of the realm of possibility and journey
done a tortuous path.

>
>  From the standpoint of psql \include should be just as much a part of
> the transaction as SELECT * FROM tbl - at least when operating in
> file/script mode.  My issue is with psql - how it manages the underlying
> session/transaction to make that works is its problem and should be an
> implementation detail I do not have to worry about.
>
> Note: This all likely extends to "\!" as well but I haven't gone and
> explored that dynamic.

Just not seeing it. At this point I have made my arguments. Will be
interested whether others have comments or even care.


> ​David J.​
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 12/29/2014 03:56 PM, David Johnston wrote:
>
>

>
>
> ​So you think psql should issue "COMMIT;" even if it is exiting due to
> "ON_ERROR_STOP"?

I say yes, if it is a non-SQL error. As Viktor stated, SQL errors abort
the transaction.

>
> Whether you do or don't can you show me where in the documentation the
> current behavior is described?

Your biggest issue seems to be with --single-transaction and
ON_ERROR_STOP so:

--single-transaction

     When psql executes a script, adding this option wraps BEGIN/COMMIT
around the script to execute it as a single transaction.

Therefore:

BEGIN;
script
COMMIT;

I would and have agreed with your previous statements that it is not
clear enough that \i is not an SQL command and an error with same is
ignored by the transaction. Outside of that I see no problem.

>
> ​David J.​
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 12/29/2014 04:26 PM, David Johnston wrote:
> On Mon, Dec 29, 2014 at 5:09 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>wrote:
>
>     On 12/29/2014 03:56 PM, David Johnston wrote:
>
>         ​So you think psql should issue "COMMIT;" even if it is exiting
>         due to
>
>         "ON_ERROR_STOP"?
>
>
>     I say yes, if it is a non-SQL error. As Viktor stated, SQL errors
>     abort the transaction.
>
>
> ​Ok, so we disagree here because that distinction seems arbitrary and
> decidedly not useful.​
>
>
>         Whether you do or don't can you show me where in the
>         documentation the
>         current behavior is described?
>
>
>     Your biggest issue seems to be with --single-transaction and
>     ON_ERROR_STOP so:
>
>     --single-transaction
>
>          When psql executes a script, adding this option wraps
>     BEGIN/COMMIT around the script to execute it as a single transaction.
>
>     Therefore:
>
>     BEGIN;
>     script
>     COMMIT;
>
>     I would and have agreed with your previous statements that it is not
>     clear enough that \i is not an SQL command and an error with same is
>     ignored by the transaction. Outside of that I see no problem.
>
>
> ​That still leaves ambiguity.  How about:​
>
> ​--single-transaction
> ​    When psql executes a script using this option it explicitly begins
> a transaction at session start and commits that transaction at session
> end.  ​The transaction will commit even if the script is forced to exit
> early due to ON_ERROR_STOP: and if no SQL errors have occurred all
> statements prior to the error-inducing psql meta-command will be
> committed.  For this reason it is not recommended to combine this option
> and ON_ERROR_STOP

if you want the entire script to rollback instead of partially committing.

- instead omit this option and supply the transaction
> commands yourself.
>
> ON_ERROR_STOP
>      [existing wording]
>      As described under the --single-transaction option the commit
> issued at session end will occur prior to psql exiting and could result
> in running script being partially committed.

Works for me:)

>
> David J.
>


--
Adrian Klaver
adrian.klaver@aklaver.com