Re: [GENERAL] Rollback on include error in psql

[Adrian Klaver]

On 12/29/2014 04:26 PM, David Johnston wrote:

On Mon, Dec 29, 2014 at 5:09 PM, Adrian Klaver
mailto:adrian.kla...@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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Rollback on include error in psql

[David Johnston]

On Mon, Dec 29, 2014 at 5:09 PM, Adrian Klaver 
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 -
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.

Re: [GENERAL] Rollback on include error in psql

[Adrian Klaver]

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Rollback on include error in psql

[David Johnston]

On Mon, Dec 29, 2014 at 4:38 PM, Adrian Klaver 
wrote:

> On 12/29/2014 02:55 PM, David Johnston wrote:
>
>> On Mon, Dec 29, 2014 at 3:37 PM, Adrian Klaver
>> mailto:adrian.kla...@aklaver.com>>wrote:
>>
>> On 12/29/2014 02:28 PM, David Johnston wrote:
>>
>> On Mon, Dec 29, 2014 at 3:07 PM, Adrian Klaver
>> mailto:adrian.kla...@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,
>>
>>  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 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.​

Re: [GENERAL] Rollback on include error in psql

[Adrian Klaver]

On 12/29/2014 02:55 PM, David Johnston wrote:

On Mon, Dec 29, 2014 at 3:37 PM, Adrian Klaver
mailto:adrian.kla...@aklaver.com>>wrote:

On 12/29/2014 02:28 PM, David Johnston wrote:

On Mon, Dec 29, 2014 at 3:07 PM, Adrian Klaver
mailto:adrian.kla...@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,

 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 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 

Re: [GENERAL] Rollback on include error in psql

[David Johnston]

On Mon, Dec 29, 2014 at 3:37 PM, Adrian Klaver 
wrote:

> On 12/29/2014 02:28 PM, David Johnston wrote:
>
>> On Mon, Dec 29, 2014 at 3:07 PM, Adrian Klaver
>> mailto:adrian.kla...@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,
>
>  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 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(nclud

Re: [GENERAL] Rollback on include error in psql

[Adrian Klaver]

On 12/29/2014 02:28 PM, David Johnston wrote:

On Mon, Dec 29, 2014 at 3:07 PM, Adrian Klaver
mailto:adrian.kla...@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,


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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Rollback on include error in psql

[David Johnston]

On Mon, Dec 29, 2014 at 3:07 PM, Adrian Klaver 
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.​


>
>
>
>> 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.

Re: [GENERAL] Rollback on include error in psql

[Adrian Klaver]

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
mailto:adrian.kla...@aklaver.com>>wrote:

On 12/29/2014 08:49 AM, David Johnston wrote:

On Mon, Dec 29, 2014 at 9:39 AM, Adrian Klaver
mailto:adrian.kla...@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 

Re: [GENERAL] Rollback on include error in psql

[David Johnston]

Copying -bugs to gain broader attention and opinions.

On Mon, Dec 29, 2014 at 10:06 AM, Adrian Klaver 
wrote:

> On 12/29/2014 08:49 AM, David Johnston wrote:
>
>> On Mon, Dec 29, 2014 at 9:39 AM, Adrian Klaver
>> mailto:adrian.kla...@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 

Re: [GENERAL] Rollback on include error in psql

[Adrian Klaver]

On 12/29/2014 08:49 AM, David Johnston wrote:

On Mon, Dec 29, 2014 at 9:39 AM, Adrian Klaver
mailto:adrian.kla...@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 

Re: [GENERAL] Rollback on include error in psql

[David Johnston]

On Mon, Dec 29, 2014 at 9:49 AM, David Johnston 
wrote:

> On Mon, Dec 29, 2014 at 9:39 AM, Adrian Klaver 
> 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 

Re: [GENERAL] Rollback on include error in psql

[David Johnston]

On Mon, Dec 29, 2014 at 9:39 AM, Adrian Klaver 
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 

Re: [GENERAL] Rollback on include error in psql

[Adrian Klaver]

On 12/29/2014 07:59 AM, David Johnston wrote:

On Mon, Dec 29, 2014 at 8:49 AM, Adrian Klaver
mailto: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
 > 

Re: [GENERAL] Rollback on include error in psql

[David Johnston]

On Mon, Dec 29, 2014 at 8:49 AM, Adrian Klaver 
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
> > 

Re: [GENERAL] Rollback on include error in psql

[Adrian Klaver]

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
> 

Re: [GENERAL] Rollback on include error in psql

[Viktor Shitkovskiy]

So should I report a bug somewhere?

As a workaround I'm currently using a wrapper bash script that parses the
source psql script and checks if the 'include' and 'copy-from' files do
really exist.


On Mon, Dec 29, 2014 at 4:04 AM, David G Johnston <
david.g.johns...@gmail.com> wrote:
>
> 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.
>
>
> --
> 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.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Rollback on include error in psql

[David G Johnston]

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

Re: [GENERAL] Rollback on include error in psql

[Adrian Klaver]

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/__questions/87040/rollback-on-__include-error-in-psql





--
Adrian Klaver
adrian.kla...@aklaver.com 





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Rollback on include error in psql

[Viktor Shitkovskiy]

I include my own scripts. Each of them creates some table or makes some
changes to existing tables.
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/questions/87040/rollback-on-
>> include-error-in-psql
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Rollback on include error in psql

[Viktor Shitkovskiy]

I use --single-transaction flag. But anyway, adding BEGIN and COMMIT
doesn't change anything. I stil get that problem.

On Sun, Dec 28, 2014 at 5:43 PM, Melvin Davidson 
wrote:

> You did not show the complete script.
> Did you remember to start the "transaction" with BEGIN; and end with
> COMMIT;?
> eg:
> BEGIN;
> \include ../tables/table1.cre
> \include ../tables/table2.cre
> ...
> \include ../tables/table10.cre
> COMMIT;
>
>
> On Sun, Dec 28, 2014 at 3: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?
>>
>> I'm using PostgreSQL 9.2.1.
>>
>> P.S. Initially I asked this question at dba.stackexchange.com:
>> http://dba.stackexchange.com/questions/87040/rollback-on-include-error-in-psql
>>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>

Re: [GENERAL] Rollback on include error in psql

[Adrian Klaver]

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/questions/87040/rollback-on-include-error-in-psql



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Rollback on include error in psql

[Melvin Davidson]

You did not show the complete script.
Did you remember to start the "transaction" with BEGIN; and end with
COMMIT;?
eg:
BEGIN;
\include ../tables/table1.cre
\include ../tables/table2.cre
...
\include ../tables/table10.cre
COMMIT;


On Sun, Dec 28, 2014 at 3: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?
>
> I'm using PostgreSQL 9.2.1.
>
> P.S. Initially I asked this question at dba.stackexchange.com:
> http://dba.stackexchange.com/questions/87040/rollback-on-include-error-in-psql
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.