Exiting a vsql Script Following an Error

Jim_Knicely · September 2018

By default, if a vsql script command results in an error, for example, because of a malformed command or invalid data format, processing continues. If you set ON_ERROR_STOP to “on” in a vsql script and an error occurs during processing, the script terminates immediately.

Example:

dbadmin=> CREATE TABLE tbl1 (pkid INT NOT NULL);
CREATE TABLE

dbadmin=> CREATE TABLE tbl1_temp (pkid INT);
CREATE TABLE

dbadmin=> CREATE TABLE tbl2 (pkid INT NOT NULL);
CREATE TABLE

dbadmin=> CREATE TABLE tbl2_temp (pkid INT);
CREATE TABLE

dbadmin=> INSERT INTO tbl1_temp SELECT NULL;
OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO tbl2 SELECT 1;
OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO tbl2_temp SELECT 2;
OUTPUT
--------
      1
(1 row)

dbadmin=> COMMIT;
COMMIT

dbadmin=> \q
[dbadmin@s18384357 ~]$

[dbadmin@s18384357 ~]$ cat fail.sql
SET SESSION AUTOCOMMIT TO OFF;
BEGIN TRANSACTION;
        delete from tbl1 where pkid in (select pkid from tbl1_temp);
        insert into tbl1 select * from tbl1_temp;
        delete from tbl2 where pkid in (select pkid from tbl2_temp);
        insert into tbl2 select * from tbl2_temp;

COMMIT;

[dbadmin@s18384357 ~]$ vsql -f fail.sql
SET
BEGIN
OUTPUT
--------
      0
(1 row)

vsql:fail.sql:5: ERROR 2501:  Cannot set a NOT NULL column (pkid) to a NULL value in INSERT/UPDATE statement
OUTPUT
--------
      0
(1 row)

OUTPUT
--------
      1
(1 row)

COMMIT

[dbadmin@s18384357 ~]$ vsql -c "SELECT * FROM tbl1;"
pkid
------
(0 rows)

[dbadmin@s18384357 ~]$ vsql -c "SELECT * FROM tbl2;"
pkid
------
    1
    2
(2 rows)

Wait a second. I don’t want that value of 2 in the table TBL2 because an error occurred previously. How do I avoid that?

By first setting the ON_ERROR_STOP variable to “ON” my SQL script will abort immediately thus avoiding any subsequent DML operations!

Example:

[dbadmin@s18384357 ~]$ vsql -c "DELETE FROM tbl2 WHERE pkid = 2; COMMIT;"
COMMIT

[dbadmin@s18384357 ~]$ cat fail2.sql
\set ON_ERROR_STOP ON;
delete from tbl1 where pkid in (select pkid from tbl1_temp);
insert into tbl1 select * from tbl1_temp;
delete from tbl2 where pkid in (select pkid from tbl2_temp);
insert into tbl2 select * from tbl2_temp;

COMMIT;

[dbadmin@s18384357 ~]$ vsql -f fail2.sql
OUTPUT
--------
      0
(1 row)

vsql:fail2.sql:4: ERROR 2501:  Cannot set a NOT NULL column (pkid) to a NULL value in INSERT/UPDATE statement

[dbadmin@s18384357 ~]$ vsql -c "SELECT * FROM tbl2;"
pkid
------
    1
(1 row)

This time the value of 2 was not inserted into TBL2 because the script exited immediately after the error occurred.

Have fun!

Helpful links:
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/ConnectingToVertica/vsql/Variables/ON_ERROR_STOP.htm

Have fun!