Redshift SET ON_ERROR_STOP using psql and Examples

On Redshift data warehouse appliance, you will be executing multiple SQL statement in single SQL file. In this article, we will discuss about the Redshift SET ON_ERROR_STOP variable to stop the execution in case one of the sql statement encountered error inside SQL script file. Without setting this system variable, Amazon Redshitf will continue with other statements ignoring the failed query.

PostgreSQL (psql) EXIT Status

psql returns following EXIT status when you are executing Redshfit SQL from files:

0: to the shell if it finished sql execution normally.
1: if a fatal error of its own occurs (e.g. out of memory, file not found).
2: if the connection to the server failed and the session was not interactive.
3: if an error occurred in a script and the system variable ON_ERROR_STOP was set.

Redshift SET ON_ERROR_STOP

psql command with option -f executes all sql statements in the given file. If you want the psql to stop and exit when it encounters an error with a query, include -v ON_ERROR_STOP=ON on the command line or set ON_ERROR_STOP variable inside the sql query that you are going to run.

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

\set ON_ERROR_STOP on
select * from PAT_1; 
select * from PAT_2;

-- This table is does not exists in the database so psql will stop execution at this poin and gives error
select * from PAT_23;

select * from PAT_1; 
\unset ON_ERROR_STOP

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

Redshift SET ON_ERROR_STOP to ON Example

Let’s check how ON_ERRO_STOP with an examples.

\set ON_ERROR_STOP on
select * from PAT_1; 
select * from PAT_2;  -- This table is does not exists in the database so psql will stop execution at this poin and gives error

select * from PAT_23;  
select * from PAT_1; 
\unset ON_ERROR_STOP

Lets execute the sql file using psql and see how Redshift behaves:

$ psql -f 'sample_sql.sql' 
 id | name 
----+------ 
 1 | ABC 
 2 | BCD 
 3 | CDE 
 4 | DEF 
(4 rows) 
 id | name 
----+------ 
 5 | AEF 
 6 | BEF 
 7 | CEF 
(3 rows) 
psql:sample_sql.sql:6: ERROR: relation "pat_23" does not exist

If you execute script without setting variable. Psql will continue after displaying error:

$ psql -f 'sample_sql.sql' 
 id | name 
----+------ 
 1 | ABC 
 2 | BCD 
 3 | CDE 
 4 | DEF 
(4 rows) 
 id | name 
----+------ 
 5 | AEF 
 6 | BEF 
 7 | CEF 
(3 rows) 
psql:sample_sql.sql:5: ERROR: relation "pat_23" does not exist 
 id | name 
----+------ 
 1 | ABC 
 2 | BCD 
 3 | CDE 
 4 | DEF 
(4 rows)

Redshift SET ON_ERROR_STOP using psql -v Option

$ psql -v ON_ERROR_STOP=ON -f 'sample_sql.sql' 
 id | name 
----+------ 
 1 | ABC 
 2 | BCD 
 3 | CDE 
 4 | DEF 
(4 rows) 
 id | name 
----+------ 
 5 | AEF 
 6 | BEF 
 7 | CEF 
(3 rows) 
psql:sample_sql.sql:5: ERROR: relation "pat_23" does not exist

Read:

Hope this helps 🙂