Re: [GENERAL] Rollback on include error in psql

On 12/29/2014 02:55 PM, David Johnston wrote:
> On Mon, Dec 29, 2014 at 3:37 PM, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>>wrote:
>
> On 12/29/2014 02:28 PM, David Johnston wrote:
>
> On Mon, Dec 29, 2014 at 3:07 PM, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> <mailto:adrian(dot)klaver(at)aklaver(dot)__com
> <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com