Обсуждение: Re: [GENERAL] Rollback on include error in psql
Copying -bugs to gain broader attention and opinions.
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.
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.
psql does not see any error due to meta-commands or SQL as fatal - which is why the ON_ERROR_STOP option exists.
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.
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 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.
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 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.
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.
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.
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.
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: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 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 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.
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.
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.
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.
Why? Most script languages will report an error to the user if a specified file is missing and provide them a means to respond to that error. psql lacks formal error handling capabilities (e.g., try/catch) but it does offer ON_ERROR_STOP and users should be able to rely on that to behave in a sane manner - i.e., STOPping - without explicitly committing - since something went wrong.
David J.
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 Mon, Dec 29, 2014 at 4:38 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
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.
OK - what do we disagree on? This is nit-picking on a few word choices.
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
Not productive - since plpython is outside of its purvue it cannot control that. However, right now if that function raises an error the script should stop and the open transaction should be rolled back (by default). If something is non-transaction and cannot be rolled back (notify, writing to file system, etc...) then that effect remains just like it would in any other situation. But psql does have full control over "\include" and should handle a failure to do so like any other scripting language interpreter would.
Just not seeing it. At this point I have made my arguments. Will be interested whether others have comments or even care.
So you think psql should issue "COMMIT;" even if it is exiting due to "ON_ERROR_STOP"?
Whether you do or don't can you show me where in the documentation the current behavior is described?
David J.
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 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 - 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.
David J.
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