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

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&gt;:
http://dba.stackexchange.com/questions/87040/rollback-on-include-error-in-psql

--
Adrian Klaver
adrian.klaver@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

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:

I include my own scripts. Each of them creates some table or makes some
changes to existing tables.
Yes, I want a complete rollback.

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&gt;
<http://dba.stackexchange.com&gt;__:
http://dba.stackexchange.com/__questions/87040/rollback-on-__include-error-in-psql
<http://dba.stackexchange.com/questions/87040/rollback-on-include-error-in-psql&gt;

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@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

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.

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

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:

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

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

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

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

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

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

David J.

​

On Mon, Dec 29, 2014 at 9:49 AM, David Johnston <david.g.johnston@gmail.com>
wrote:

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

​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

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

Copying -bugs to gain broader attention and opinions.

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

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

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

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

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

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

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