Hi Reiner,
The email process is already set up and running correctly. Emails are sent out for all of the different events.
The problem I have is that an internal error in the Postgres database does not trigger the on-failure event.
I want to cause an error in this job by calling a program that does not exist. The job below was flagged as “Succeeded”.
I want the “On Failure” notification to be triggered because the Postgres job failed.
Question: Is it possible to do this?
Here is the workflow:
export PGHOST=@globals.url_host@
export PGPASSWORD=@option.user_password@
#–
psql -p 5432 -U @globals.any_user_id@ -d @globals.database_name@ -w <<’!’
\conninfo
DO $$
DECLARE
BEGIN
RAISE NOTICE ‘------------OUTPUT---------------------------------------------------------’;
RAISE NOTICE ‘Start of program: utility.error_testing %’, current_timestamp;
CALL utility.error_testing2();
RAISE NOTICE ‘------------Job Complete---------------------------------------------------’;
EXCEPTION
WHEN OTHERS then
RAISE EXCEPTION ‘Error in Postgres % %’, SQLERRM, SQLSTATE;
END $$;
\q
!
This is the results of the Postgres job.
NOTICE: ------------OUTPUT---------------------------------------------------------
NOTICE: Start of program: utility.error_testing 2022-12-22 08:53:32.361827-05
ERROR: Error in Postgres procedure utility.error_testing2() does not exist 42883
CONTEXT: PL/pgSQL function inline_code_block line 14 at RAISE