Exiting a vsql Script Following an Error: Quick Tip

Jim Knicely authored this tip. 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. Helpful link: https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/ConnectingToVertica/vsql/Variables/ON_ERROR_STOP.htm Have fun!