Re: psql question: aborting a "script"

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

"

--
Adrian Klaver
adrian.klaver@aklaver.com

--

A fail-safe circuit will destroy others. -- Klipstein

Maranatha! <><
John McKown