Re: psql question: aborting a "script"

On Tue, Mar 15, 2016 at 9:57 AM, Melvin Davidson <melvin6925(at)gmail(dot)com>
wrote:

>
>
> On Tue, Mar 15, 2016 at 10:49 AM, John McKown <
> john(dot)archie(dot)mckown(at)gmail(dot)com> wrote:
>
>> On Tue, Mar 15, 2016 at 9:38 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>>>
>>
>>
>>
>> --
>> A fail-safe circuit will destroy others. -- Klipstein
>>
>> Maranatha! <><
>> 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.
>

​We have a winner! I knew I was overlooking something. Thanks.​

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

Maranatha! <><
John McKown