Vertica SET ON_ERROR_STOP and Examples

As a part of the database table refresh, you might execute multiple SQL statements on the Vertica analytical system. SQL statements could be some standalone or multiple statements in the single SQL file. You should have a mechanism to stop the execution in case one of the SQL statement encountered error inside the script. In this article, we will check one of such mechanism. i.e. Vertica SET ON_ERROR_STOP with some examples on how to use that variable.

Vertica SET ON_ERROR_STOP

The Vertica vsql command with option -f executes all SQL statements in the files. If you want the vsql command to stop and exit when it encounters an error with a query, include -v ON_ERROR_STOP=1 on the command line or include the ON_ERROR_STOP variable inside the SQL query that you will be running on Vertica server.

As mentioned, you can either use Vertica VSQL variable substitution method to set ON_ERROR_STOP variable or set it in SQL query file itself. Both methods are equally easy, you can use any approach that is appropriate to your application.

Related Article:

• Run SQL Script File using Vertica vsql Variable Substitution

If you create an ON_ERROR_STOP block inside a SQL query file, as in this example:

<initial SQL queries>
\set ON_ERROR_STOP ON
 <queries inside block> 
\unset ON_ERROR_STOP
<trailing SQL queries>

\unset section is ignored when there is an ON_ERROR_STOP block. The success or failure of the commands inside the block determine the exit value.

Vertica SET ON_ERROR_STOP Example

Let’s check how ON_ERRO_STOP with an example. In this example, we have added some queries to vertica_test_file.sql file. Just to verify the ON_ERROR_STOP variable, we have added few queries with error in them.

Below is the cat output of my vertica_test_file.sql file:

[dbadmin@localhost ~]$ cat vertica_test_file.sql
\set ON_ERROR_STOP ON;
select * from training.table_1;
select * from training.table_2;

-- This table is does not exists in the database so Vertica will stop execution at this point

select * from training.table_23;
select * from training.table_1;
\unset ON_ERROR_STOP
[dbadmin@localhost ~]$

Let’s execute the file and see how Vertica behaves with ON_ERROR_STOP variable:

[dbadmin@localhost ~]$ vsql -U dbadmin -w password -d vmart -f '/home/dbadmin/vertica_test_file.sql'
 id | name
----+------
  1 | A
  2 | B
  3 | C
  4 | D
(4 rows)

 id | name
----+------
  1 | A
  2 | B
  3 | C
  4 | D
(4 rows)

vsql:/home/dbadmin/vertica_test_file.sql:7: ERROR 4568:  Relation "training.table_23" does not exist

As you can see in the examples, Vertica stops execution as soon as it encountered ‘table does not exist’ error, otherwise Vertica would continue execution ignoring previous error.

Hope this helps 🙂