How to log errors to job_errors table when user defined job is failed?

TimescaleDB and PostgreSQL
, ,
1

Hi! Is there a possibility to log errors of user defined jobs which run UDF?

Thanks in advance.
Best regards,
Denis

2

I understood how to log error of user defined action. Both of UDA are valid:

CREATE OR REPLACE PROCEDURE test.test_raise_exception(job_id int, config jsonb)
LANGUAGE plpgsql
AS $$
DECLARE
	v_returned_sqlstate TEXT;
	v_message_text TEXT;
	v_pg_exception_detail TEXT;
	v_pg_exception_hint TEXT;
	v_pg_exception_context TEXT;
BEGIN
	SELECT 1 / 0;
EXCEPTION WHEN OTHERS THEN 
	GET STACKED DIAGNOSTICS
		v_returned_sqlstate = returned_sqlstate,
		v_message_text = message_text,
		v_pg_exception_detail = pg_exception_detail,
		v_pg_exception_hint = pg_exception_hint,
		v_pg_exception_context = pg_exception_context;
	RAISE EXCEPTION USING
		ERRCODE = v_returned_sqlstate,
		MESSAGE = v_message_text,
		DETAIL = v_pg_exception_detail,
		HINT = v_pg_exception_hint;
END;$$;

SELECT add_job(
	proc => 'test.test_raise_exception',
	schedule_interval => INTERVAL '1 minute',
	initial_start => TIMESTAMP '2023-04-21 07:00:00');

or

CREATE OR REPLACE PROCEDURE test.test_raise_exception(job_id int, config jsonb)
LANGUAGE plpgsql
AS $$
BEGIN
	SELECT 1 / 0;
END;$$;