psql question: aborting a "script"
I'm likely abusing the psql program. What I have is an awk program which
reads a file and produces a number of INSERT INTO commands. I then feed
these commands into psql to execute them. Yes, a Perl program would be a
better idea. Anyway, sometimes the commands are rejected due to some
problem, such as duplicate primary key. What I wish is that the psql
command had a switch, or control command, which would say "exit from psql
if anything fails". To me, this is like the BASH "set -e" command for a
shell script. Does this sound like a useful addition. Or am I just missing
where it already exists?
--
A fail-safe circuit will destroy others. -- Klipstein
Maranatha! <><
John McKown
On 03/15/2016 07:33 AM, John McKown wrote:
I'm likely abusing the psql program. What I have is an awk program which
reads a file and produces a number of INSERT INTO commands. I then feed
these commands into psql to execute them. Yes, a Perl program would be a
better idea. Anyway, sometimes the commands are rejected due to some
problem, such as duplicate primary key. What I wish is that the psql
command had a switch, or control command, which would say "exit from
psql if anything fails". To me, this is like the BASH "set -e" command
for a shell script. Does this sound like a useful addition. Or am I just
missing where it already exists?
Would this help?:
http://www.postgresql.org/docs/9.5/interactive/app-psql.html
"-1
--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.
"
--
A fail-safe circuit will destroy others. -- KlipsteinMaranatha! <><
John McKown
--
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 Tue, Mar 15, 2016 at 9:38 AM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
On 03/15/2016 07:33 AM, John McKown wrote:
I'm likely abusing the psql program. What I have is an awk program which
reads a file and produces a number of INSERT INTO commands. I then feed
these commands into psql to execute them. Yes, a Perl program would be a
better idea. Anyway, sometimes the commands are rejected due to some
problem, such as duplicate primary key. What I wish is that the psql
command had a switch, or control command, which would say "exit from
psql if anything fails". To me, this is like the BASH "set -e" command
for a shell script. Does this sound like a useful addition. Or am I just
missing where it already exists?Would this help?:
Well, actually, no. It does force a ROLLBACK, but the individual INSERT
INTO commands are still being read and rejected, one by one. And there are
literally _thousands_ of them. It is not a "problem", per se. It's just
that it is "wasting" time and effort on the part of the system.
If you're wonder why I do it this way, it is because the commands that I
generate are simple SQL standard commands. And they can be fed into
programs which update different SQL data bases, such as Postgresql (psql
command), MariaDB (mysql command), SQLite3 (sqlite3 command), and so forth.
Basically, I'm lazy and don't want to code multiple RDMS-oriented commands,
or have a single command which can interface with multiple RDMS systems.
http://www.postgresql.org/docs/9.5/interactive/app-psql.html
"-1
--single-transactionWhen 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."
--
Adrian Klaver
adrian.klaver@aklaver.com
--
A fail-safe circuit will destroy others. -- Klipstein
Maranatha! <><
John McKown
On Tue, Mar 15, 2016 at 10:49 AM, John McKown <john.archie.mckown@gmail.com>
wrote:
On Tue, Mar 15, 2016 at 9:38 AM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:On 03/15/2016 07:33 AM, John McKown wrote:
I'm likely abusing the psql program. What I have is an awk program which
reads a file and produces a number of INSERT INTO commands. I then feed
these commands into psql to execute them. Yes, a Perl program would be a
better idea. Anyway, sometimes the commands are rejected due to some
problem, such as duplicate primary key. What I wish is that the psql
command had a switch, or control command, which would say "exit from
psql if anything fails". To me, this is like the BASH "set -e" command
for a shell script. Does this sound like a useful addition. Or am I just
missing where it already exists?Would this help?:
Well, actually, no. It does force a ROLLBACK, but the individual INSERT
INTO commands are still being read and rejected, one by one. And there are
literally _thousands_ of them. It is not a "problem", per se. It's just
that it is "wasting" time and effort on the part of the system.If you're wonder why I do it this way, it is because the commands that I
generate are simple SQL standard commands. And they can be fed into
programs which update different SQL data bases, such as Postgresql (psql
command), MariaDB (mysql command), SQLite3 (sqlite3 command), and so forth.
Basically, I'm lazy and don't want to code multiple RDMS-oriented commands,
or have a single command which can interface with multiple RDMS systems. http://www.postgresql.org/docs/9.5/interactive/app-psql.html
"-1
--single-transactionWhen 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."
--
Adrian Klaver
adrian.klaver@aklaver.com--
A fail-safe circuit will destroy others. -- KlipsteinMaranatha! <><
John McKown
What you really want is
"ON_ERROR_STOP
By default, command processing continues after an error. When this variable
is set to on, processing 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.
"
So just
SET ON_ERROR_STOP = ON
before any other statements
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
On Tue, Mar 15, 2016 at 9:57 AM, Melvin Davidson <melvin6925@gmail.com>
wrote:
On Tue, Mar 15, 2016 at 10:49 AM, John McKown <
john.archie.mckown@gmail.com> wrote:On Tue, Mar 15, 2016 at 9:38 AM, Adrian Klaver <adrian.klaver@aklaver.com
wrote:
On 03/15/2016 07:33 AM, John McKown wrote:
I'm likely abusing the psql program. What I have is an awk program which
reads a file and produces a number of INSERT INTO commands. I then feed
these commands into psql to execute them. Yes, a Perl program would be a
better idea. Anyway, sometimes the commands are rejected due to some
problem, such as duplicate primary key. What I wish is that the psql
command had a switch, or control command, which would say "exit from
psql if anything fails". To me, this is like the BASH "set -e" command
for a shell script. Does this sound like a useful addition. Or am I just
missing where it already exists?Would this help?:
Well, actually, no. It does force a ROLLBACK, but the individual INSERT
INTO commands are still being read and rejected, one by one. And there are
literally _thousands_ of them. It is not a "problem", per se. It's just
that it is "wasting" time and effort on the part of the system.If you're wonder why I do it this way, it is because the commands that I
generate are simple SQL standard commands. And they can be fed into
programs which update different SQL data bases, such as Postgresql (psql
command), MariaDB (mysql command), SQLite3 (sqlite3 command), and so forth.
Basically, I'm lazy and don't want to code multiple RDMS-oriented commands,
or have a single command which can interface with multiple RDMS systems. http://www.postgresql.org/docs/9.5/interactive/app-psql.html
"-1
--single-transactionWhen 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."
--
Adrian Klaver
adrian.klaver@aklaver.com--
A fail-safe circuit will destroy others. -- KlipsteinMaranatha! <><
John McKownWhat you really want is
"ON_ERROR_STOPBy default, command processing continues after an error. When this
variable is set to on, processing 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.
"
So just
SET ON_ERROR_STOP = ON
before any other statements
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
We have a winner! I knew I was overlooking something. Thanks.
--
A fail-safe circuit will destroy others. -- Klipstein
Maranatha! <><
John McKown
John McKown wrote:
I'm likely abusing the psql program. What I have is an awk program which reads a file and produces a
number of INSERT INTO commands. I then feed these commands into psql to execute them. Yes, a Perl
program would be a better idea. Anyway, sometimes the commands are rejected due to some problem, such
as duplicate primary key. What I wish is that the psql command had a switch, or control command, which
would say "exit from psql if anything fails". To me, this is like the BASH "set -e" command for a
shell script. Does this sound like a useful addition. Or am I just missing where it already exists?
Did you try "psql -v ON_ERROR_STOP=on"?
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hi Melvin:
On Tue, Mar 15, 2016 at 3:57 PM, Melvin Davidson <melvin6925@gmail.com> wrote:
What you really want is
"ON_ERROR_STOP
...
So just
SET ON_ERROR_STOP = ON
before any other statements
IIRC you are right with the variable ... BUT .. it is a psql setting,
not a session setting, so he'll probably need to use the \set psql
mettacommand:
\set ON_ERROR_STOP on
and also, use on as suggested on the docs, not ON, I'm not sure wether
PSQL is case sensitive.
Francisco Olarte.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general