Обсуждение: Rollback on include error in psql
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.
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
You did not show the complete script.
Did you remember to start the "transaction" with BEGIN; and end with COMMIT;?
eg:Did you remember to start the "transaction" with BEGIN; and end with COMMIT;?
BEGIN;
\include ../tables/table1.cre
\include ../tables/table2.cre
...
\include ../tables/table10.cre
On Sun, Dec 28, 2014 at 3:02 AM, Viktor Shitkovskiy <hanksmail@gmail.com> 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.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
On 12/28/2014 12:02 AM, Viktor Shitkovskiy wrote: > Hello. > > I'm trying to execute an sql script file in a single transation. The > file contains includes for some other scripts which in my example create > some tables. It looks like this: > \include ../tables/table1.cre > \include ../tables/table2.cre > ... > \include ../tables/table10.cre > > I'm executing it using psql: > psql -X --set AUTOCOMMIT=off --set ON_ERROR_STOP=on -e > --single-transaction -d my_db -f my_script.sql > > The problem is that errors with the include meta command do not cause a > transactiopn rollback. e.g. if some of tableX.cre files is missing, any > changes before its include will be commited. However, if there's some > SQL syntax error, everyting works as expected. > > Is it possible to somehow handle include related errors and rollback the > active transcation? > Where is the \include coming from? What is in the tableX.cre files? So if I am following you want a complete rollback on non-SQL or SQL errors, correct? > I'm using PostgreSQL 9.2.1. > > P.S. Initially I asked this question at dba.stackexchange.com > <http://dba.stackexchange.com>: > http://dba.stackexchange.com/questions/87040/rollback-on-include-error-in-psql -- Adrian Klaver adrian.klaver@aklaver.com
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 <melvin6925@gmail.com> wrote:
COMMIT;You did not show the complete script.eg:
Did you remember to start the "transaction" with BEGIN; and end with COMMIT;?
BEGIN;
\include ../tables/table1.cre
\include ../tables/table2.cre
...
\include ../tables/table10.cre--On Sun, Dec 28, 2014 at 3:02 AM, Viktor Shitkovskiy <hanksmail@gmail.com> 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-psqlMelvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
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>:
http://dba.stackexchange.com/questions/87040/rollback-on-include-error-in-psql
--
Adrian Klaver
adrian.klaver@aklaver.com
On 12/28/2014 10:06 AM, Viktor Shitkovskiy wrote: > I include my own scripts. Each of them creates some table or makes some > changes to existing tables. It is hard to say where to go from here without more information. The options you are passing to psql all have caveats: AUTOCOMMIT When on (the default), each SQL command is automatically committed upon successful completion. To postpone commit in this mode, you must enter a BEGIN or START TRANSACTION SQL command. When off or unset, SQL commands are not committed until you explicitly issue COMMIT or END. The autocommit-off mode works by issuing an implicit BEGIN for you, just before any command that is not already in a transaction block and is not itself a BEGIN or other transaction-control command, nor a command that cannot be executed inside a transaction block (such as VACUUM). Note: In autocommit-off mode, you must explicitly abandon any failed transaction by entering ABORT or ROLLBACK. Also keep in mind that if you exit the session without committing, your work will be lost. --single-transaction When psql executes a script, adding this option wraps BEGIN/COMMIT around the script to execute it as a single transaction. This ensures that either all the commands complete successfully, or no changes are applied. If the script itself uses BEGIN, COMMIT, or ROLLBACK, this option will not have the desired effects. Also, if the script contains any command that cannot be executed inside a transaction block, specifying this option will cause that command (and hence the whole transaction) to fail. ON_ERROR_STOP By default, command processing continues after an error. When this variable is set, it will instead stop immediately. In interactive mode, psql will return to the command prompt; otherwise, psql will exit, returning error code 3 to distinguish this case from fatal error conditions, which are reported using error code 1. In either case, any currently running scripts (the top-level script, if any, and any other scripts which it may have in invoked) will be terminated immediately. If the top-level command string contained multiple SQL commands, processing will stop with the current command. Without information on what is going on in the individual scripts or the master script, it would be just a guessing game at this point. > Yes, I want a complete rollback. > > > Where is the \include coming from? > > What is in the tableX.cre files? > > So if I am following you want a complete rollback on non-SQL or SQL > errors, correct? > > I'm using PostgreSQL 9.2.1. > > P.S. Initially I asked this question at dba.stackexchange.com > <http://dba.stackexchange.com> > <http://dba.stackexchange.com>__: > http://dba.stackexchange.com/__questions/87040/rollback-on-__include-error-in-psql > <http://dba.stackexchange.com/questions/87040/rollback-on-include-error-in-psql> > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver-4 wrote > On 12/28/2014 10:06 AM, Viktor Shitkovskiy wrote: >> I include my own scripts. Each of them creates some table or makes some >> changes to existing tables. > > It is hard to say where to go from here without more information. really? This seems like a documentation bug (or, at the least worth more documentation explanation) at minimum; two of them probably: 1) it is not documented that "\include" is a valid alias for "\i" 2) the implications of \include being a client-side mechanic and thus, invisible to the server, is not well explained. Specifically that a failure to include is the equivalent of simply omitting the statement altogether (aside from the psql warning). I would suggest an enhancement whereby psql will send a guaranteed-to-fail command to the server upon failing to find an included file - at least in non-interactive mode; in interactive mode the warning is likely sufficient though the interplay with auto-commit would be concerning. > The options you are passing to psql all have caveats: I'm not seeing how any of those caveats are coming into play here. The ON_ERROR_STOP behavior is actually surprising since psql does indeed return 3 but even with single transaction and auto-commit=off any updates prior to the include are committed. This isn't that difficult to test... [db] CREATE TABLE testtbl (col text PRIMARY KEY); INSERT INTO testtbl VALUES ( 'value' ); [script] UPDATE testtbl SET col = 'some other value'; \i some_missing_file.sql UPDATE testtbl SET col = 'yet another value'; [/script] execute using: psql --single-transaction --set ON_ERROR_STOP=1 --set AUTOCOMMIT=off -f <script> [db] see warning echo $? returns 3 value of testtbl.col is 'some other value' Based upon those caveats processing should have stopped immediately (which it does) and thus the transaction (which is there because of single-transaction) should have rolledback due to an explicit commit not being issued and the documented default behavior to discard the transaction. 9.3.5 - Ubuntu 12.04 - apt.postgresql.org David J. -- View this message in context: http://postgresql.nabble.com/Rollback-on-include-error-in-psql-tp5832192p5832233.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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.johnston@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
On 12/28/2014 05:04 PM, David G Johnston wrote: > Adrian Klaver-4 wrote >> On 12/28/2014 10:06 AM, Viktor Shitkovskiy wrote: >>> I include my own scripts. Each of them creates some table or makes some >>> changes to existing tables. >> >> It is hard to say where to go from here without more information. > > really? Yes. The if, ands and buts for each of the options by themselves much less in combination would indicate that an answer is dependent on what is actually happening in the scripts. We have a fragment of the main script and not much information as to what is actually happening in the called scripts. See below why this is important. > > This seems like a documentation bug (or, at the least worth more > documentation explanation) at minimum; two of them probably: > > 1) it is not documented that "\include" is a valid alias for "\i" > 2) the implications of \include being a client-side mechanic and thus, > invisible to the server, is not well explained. Specifically that a failure > to include is the equivalent of simply omitting the statement altogether > (aside from the psql warning). Agreed. > > I would suggest an enhancement whereby psql will send a guaranteed-to-fail > command to the server upon failing to find an included file - at least in > non-interactive mode; in interactive mode the warning is likely sufficient > though the interplay with auto-commit would be concerning. > > >> The options you are passing to psql all have caveats: > > I'm not seeing how any of those caveats are coming into play here. > > The ON_ERROR_STOP behavior is actually surprising since psql does indeed > return 3 but even with single transaction and auto-commit=off any updates > prior to the include are committed. > > This isn't that difficult to test... > [db] > CREATE TABLE testtbl (col text PRIMARY KEY); > INSERT INTO testtbl VALUES ( 'value' ); > > [script] > UPDATE testtbl SET col = 'some other value'; > \i some_missing_file.sql > UPDATE testtbl SET col = 'yet another value'; > [/script] > > execute using: > > psql --single-transaction --set ON_ERROR_STOP=1 --set AUTOCOMMIT=off -f > <script> [db] > > see warning > > echo $? returns 3 > > value of testtbl.col is 'some other value' > > Based upon those caveats processing should have stopped immediately (which > it does) and thus the transaction (which is there because of > single-transaction) should have rolledback due to an explicit commit not > being issued and the documented default behavior to discard the transaction. Except one is issued by --single-transaction: "When psql executes a script, adding this option wraps BEGIN/COMMIT around the script to execute it as a single transaction" Using your test case and looking at the logs: aklaver@panda:~> psql -d test -U aklaver -p 5452 --single-transaction --set ON_ERROR_STOP=1 --set AUTOCOMMIT=off -f test_script.sql UPDATE 1 psql:test_script.sql:2: some_missing_file.sql: No such file or directory test=> select * from testtbl ; col ------------------ some other value (1 row) aklaver-2014-12-29 06:56:56.889 PST-0LOG: statement: BEGIN aklaver-2014-12-29 06:56:56.889 PST-0LOG: statement: UPDATE testtbl SET col = 'some other value'; aklaver-2014-12-29 06:56:56.890 PST-129363LOG: statement: COMMIT Take --single-transaction out of the command: test=> update testtbl set col = 'value'; UPDATE 1 aklaver@panda:~> psql -d test -U aklaver -p 5452 --set ON_ERROR_STOP=1 --set AUTOCOMMIT=off -f test_script.sql UPDATE 1 psql:test_script.sql:2: some_missing_file.sql: No such file or directory test=> select * from testtbl ; col ------- value (0 rows) aklaver-2014-12-29 06:58:54.210 PST-0LOG: statement: BEGIN aklaver-2014-12-29 06:58:54.210 PST-0LOG: statement: UPDATE testtbl SET col = 'some other value'; Basically the take away is, there is quite a bit of transaction fiddling going on behind the scenes and ON_ERROR_STOP says it stops processing the command, not that it rollbacks the transaction. In any case Viktor what wants is to have the entire process either succeed or fail. That would seem to be best served by not using --single-transaction. Though getting back to caveats, this would depend on what transaction commands or not or non-transactional statements (VACUUM) are present in the scripts. > > 9.3.5 - Ubuntu 12.04 - apt.postgresql.org > > David J. > > > > > -- > View this message in context: http://postgresql.nabble.com/Rollback-on-include-error-in-psql-tp5832192p5832233.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
On 12/28/2014 05:04 PM, David G Johnston wrote:
> Adrian Klaver-4 wrote
>> On 12/28/2014 10:06 AM, Viktor Shitkovskiy wrote:
>>> I include my own scripts. Each of them creates some table or makes some
>>> changes to existing tables.
>>
>> It is hard to say where to go from here without more information.
>
> really?
Yes. The if, ands and buts for each of the options by themselves much less in
combination would indicate that an answer is dependent on what is actually
happening in the scripts. We have a fragment of the main script and
not much information as to what is actually happening in the called scripts. See
below why this is important.
>
> This seems like a documentation bug (or, at the least worth more
> documentation explanation) at minimum; two of them probably:
>
> 1) it is not documented that "\include" is a valid alias for "\i"
> 2) the implications of \include being a client-side mechanic and thus,
> invisible to the server, is not well explained. Specifically that a failure
> to include is the equivalent of simply omitting the statement altogether
> (aside from the psql warning).
Agreed.
>
> I would suggest an enhancement whereby psql will send a guaranteed-to-fail
> command to the server upon failing to find an included file - at least in
> non-interactive mode; in interactive mode the warning is likely sufficient
> though the interplay with auto-commit would be concerning.
>
>
>> The options you are passing to psql all have caveats:
>
> I'm not seeing how any of those caveats are coming into play here.
>
> The ON_ERROR_STOP behavior is actually surprising since psql does indeed
> return 3 but even with single transaction and auto-commit=off any updates
> prior to the include are committed.
>
> This isn't that difficult to test...
> [db]
> CREATE TABLE testtbl (col text PRIMARY KEY);
> INSERT INTO testtbl VALUES ( 'value' );
>
> [script]
> UPDATE testtbl SET col = 'some other value';
> \i some_missing_file.sql
> UPDATE testtbl SET col = 'yet another value';
> [/script]
>
> execute using:
>
> psql --single-transaction --set ON_ERROR_STOP=1 --set AUTOCOMMIT=off -f
> <script> [db]
>
> see warning
>
> echo $? returns 3
>
> value of testtbl.col is 'some other value'
>
> Based upon those caveats processing should have stopped immediately (which
> it does) and thus the transaction (which is there because of
> single-transaction) should have rolledback due to an explicit commit not
> being issued and the documented default behavior to discard the transaction.
Except one is issued by --single-transaction:
"When psql executes a script, adding this option wraps BEGIN/COMMIT around the script to execute it as a single transaction"
I'll still support that his report was sufficient for our needs...
Anyway, the third undocumented bug is that --single-transactions gets to send its COMMIT even if ON_ERROR_STOP
takes hold before the end of the script. I imagined it such that only if every statement in the "-f <script>" was called would the COMMIT be issued - thus the error_stop would supercede and leave the session uncommitted and by default rolledback.
Since both ON_ERROR_STOP and --single-transaction are psql-related that seems like a proper and logical solution.
That all said I agree that adding a manual BEGIN/COMMIT - that would behave the way you'd expect --single-transaction to behave - would be a more stable and explicit solution.
David J.
On 12/29/2014 07:59 AM, David Johnston wrote: > On Mon, Dec 29, 2014 at 8:49 AM, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>wrote: > > On 12/28/2014 05:04 PM, David G Johnston wrote: > > Adrian Klaver-4 wrote > >> On 12/28/2014 10:06 AM, Viktor Shitkovskiy wrote: > >>> I include my own scripts. Each of them creates some table or > makes some > >>> changes to existing tables. > >> > >> It is hard to say where to go from here without more information. > > > > really? > > Yes. The if, ands and buts for each of the options by themselves > much less in > combination would indicate that an answer is dependent on what is > actually > happening in the scripts. We have a fragment of the main script and > not much information as to what is actually happening in the called > scripts. See > below why this is important. > > > > > This seems like a documentation bug (or, at the least worth more > > documentation explanation) at minimum; two of them probably: > > > > 1) it is not documented that "\include" is a valid alias for "\i" > > 2) the implications of \include being a client-side mechanic and > thus, > > invisible to the server, is not well explained. Specifically > that a failure > > to include is the equivalent of simply omitting the statement > altogether > > (aside from the psql warning). > > Agreed. > > > > > I would suggest an enhancement whereby psql will send a guaranteed-to-fail > > command to the server upon failing to find an included file - at least in > > non-interactive mode; in interactive mode the warning is likely sufficient > > though the interplay with auto-commit would be concerning. > > > > > >> The options you are passing to psql all have caveats: > > > > I'm not seeing how any of those caveats are coming into play here. > > > > The ON_ERROR_STOP behavior is actually surprising since psql does > indeed > > return 3 but even with single transaction and auto-commit=off any > updates > > prior to the include are committed. > > > > This isn't that difficult to test... > > [db] > > CREATE TABLE testtbl (col text PRIMARY KEY); > > INSERT INTO testtbl VALUES ( 'value' ); > > > > [script] > > UPDATE testtbl SET col = 'some other value'; > > \i some_missing_file.sql > > UPDATE testtbl SET col = 'yet another value'; > > [/script] > > > > execute using: > > > > psql --single-transaction --set ON_ERROR_STOP=1 --set > AUTOCOMMIT=off -f > > <script> [db] > > > > see warning > > > > echo $? returns 3 > > > > value of testtbl.col is 'some other value' > > > > Based upon those caveats processing should have stopped > immediately (which > > it does) and thus the transaction (which is there because of > > single-transaction) should have rolledback due to an explicit > commit not > > being issued and the documented default behavior to discard the > transaction. > > Except one is issued by --single-transaction: > > "When psql executes a script, adding this option wraps BEGIN/COMMIT > around the script to execute it as a single transaction" > > > I'll still support that his report was sufficient for our needs... > > Anyway, the third undocumented bug is that --single-transactions gets to > send its COMMIT even if ON_ERROR_STOP > takes hold before the end of the script. I imagined it such that only > if every statement in the "-f <script>" was called would the COMMIT be > issued - thus the error_stop would supercede and leave the session > uncommitted and by default rolledback. Not seeing the bug. --single-transaction wraps the entire script in BEGIN/COMMIT, ON_ERROR_STOP stops 'processing' the command, nothing in there about stopping transaction or rollback. So the failed \i stops the script from processing anything after that and the session goes directly to the COMMIT. If you want to deal with transactions there is ON_ERROR_ROLLBACK. Though I did find something interesting about that, which will subject of another post. > > Since both ON_ERROR_STOP and --single-transaction are psql-related that > seems like a proper and logical solution. > > That all said I agree that adding a manual BEGIN/COMMIT - that would > behave the way you'd expect --single-transaction to behave - would be a > more stable and explicit solution. > > David J. > -- Adrian Klaver adrian.klaver@aklaver.com
On 12/29/2014 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.
David J.
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.
FWIW
The way this is written currently I am imagining something like this happens:
cat "BEGIN;" filename "COMMIT;" > script_to_execute
\i script_to_execute
David J.
On 12/29/2014 08:49 AM, David Johnston wrote: > On Mon, Dec 29, 2014 at 9:39 AM, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>wrote: > > On 12/29/2014 07:59 AM, David Johnston wrote: > > > Anyway, the third undocumented bug is that --single-transactions > gets to > send its COMMIT even if ON_ERROR_STOP > takes hold before the end of the script. I imagined it such > that only > if every statement in the "-f <script>" was called would the > COMMIT be > issued - thus the error_stop would supercede and leave the session > uncommitted and by default rolledback. > > > Not seeing the bug. --single-transaction wraps the entire script in > BEGIN/COMMIT, ON_ERROR_STOP stops 'processing' the command, nothing > in there about stopping transaction or rollback. So the failed \i > stops the script from processing anything after that and the session > goes directly to the COMMIT. If you want to deal with transactions > there is ON_ERROR_ROLLBACK. Though I did find something interesting > about that, which will subject of another post. > > > Then --single-transaction has nothing to do with the script file > at-all. It should be documented as issuing a BEGIN at session connect > and a COMMIT just before session disconnect - regardless of whether the > named script executes to completion, which can happen if it is combined > with ON_ERROR_STOP. Seems to me when you do: psql --single-transaction -f some_script the script is the session. ON_ERROR_STOP " ..psql will exit, returning error code 3 to distinguish this case from fatal error conditions, which are reported using error code 1" So psql does not see this a fatal error. This is one of those glass half full/empty situations, where it is down to the eye of the beholder. I would also say this a perfect example of why tests are written, to see what actually happens versus what you think happens. > > David J. > > > -- Adrian Klaver adrian.klaver@aklaver.com
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