Greenplum SET ON_ERROR_STOP using psql and Examples

  • Post author:
  • Post last modified:May 7, 2019
  • Post category:Greenplum
  • Reading time:5 mins read

On Greenplum MPP data warehouse appliance, you will be executing multiple SQL statement in the single SQL file. This is required when you are refreshing fact or dimension table by creating intermediate stage tables. In such a refresh SQL file, there will be multiple create, delete, and drop statements. You have to take care of any errors that might occur in any of the SQL statements in file. In this article, we will discuss about the Greenplum SET ON_ERROR_STOP variable to stop the execution in case one of the SQL statement encountered error in SQL script file.

Greenplum SET ON_ERROR_STOP using psql

PostgreSQL psql EXIT Status

The psql returns following EXIT status when you are executing 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.

Greenplum SET ON_ERROR_STOP using psql

The PostgreSQL psql provides ‘-f’ options to execute SQL from the file. There are two option to stop and exit execution when encountered error with query file.

  • Include -v ON_ERROR_STOP=ON on the command line.
  • set ON_ERROR_STOP variable inside the SQL query file.

Greenplum SET ON_ERROR_STOP to ON Example

Let’s check how ON_ERRO_STOP with an example.

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

\set ON_ERROR_STOP on
select * from t1;
select * from t2;  -- This table is does not exists in the database so psql will stop execution at this point
select * from t23;  
select * from t1;
\unset ON_ERROR_STOP

Let us execute the query file and check output:

[gpadmin@server-d861 ~]$ psql -d template1 -h 192.168.200.185 -f '/home/gpadmin/sample_test.sql'
 i
---
(0 rows)

 i
---
(0 rows)

psql:/home/gpadmin/sample_test.sql:4: ERROR:  relation "t23" does not exist
LINE 1: select * from t23;
                      ^
[gpadmin@server-d861 ~]$

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

[gpadmin@server-d861 ~]$ psql -d template1 -h 192.168.200.185 -f '/home/gpadmin/sample_test.sql'
 i
---
(0 rows)

 i
---
(0 rows)

psql:/home/gpadmin/sample_test.sql:4: ERROR:  relation "t23" does not exist
LINE 1: select * from t23;
                      ^
 i
---
(0 rows)

[gpadmin@server-d861 ~]$

Greenplum SET ON_ERROR_STOP using psql -v Option

You can also set ON_ERROR_STOP variable using -v option available in psql. You don’t have to change anything in SQL file if you use this option.

[gpadmin@server-d861 ~]$ psql -d template1 -h 192.168.200.185 -v ON_ERROR_STOP=ON -f '/home/gpadmin/sample_test.sql'
 i
---
(0 rows)

 i
---
(0 rows)

psql:/home/gpadmin/sample_test.sql:4: ERROR:  relation "t23" does not exist
LINE 1: select * from t23;
                      ^
[gpadmin@server-d861 ~]$

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

Hope this helps 🙂

Tags: