Re: [GENERAL] Rollback on include error in psql - Mailing list pgsql-bugs

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