I have a procedure, to do thing like insert, delete, truncate table in postgresql database. Then i want to monitoring the job in procedure, then i do some insert/update to 'hungcht_proc_monitoring', by using technique exception, the proc still run but it skips all code above. I make some search but i can't find the issue and solution. Hope you who are professional in plsql dev can fix my code below, thank you and apreciate so much.
The code run well, but skipp the code above exception. enter image description here
CREATE OR REPLACE PROCEDURE "y4a_fin_analyst"."hungcht_update_table_database_monitoring"()
AS $BODY$
DECLARE
_sql_state TEXT;
_message TEXT;
BEGIN
-- Step 1: insert into proc monitoring
insert into "y4a_fin_analyst"."hungcht_proc_monitoring"
(proc_name, run_date, end_date, status, err_message, err_sqlstate)
VALUES
('hungcht_update_table_database_monitoring',
current_timestamp AT TIME ZONE 'Asia/Bangkok',
null,null,null,null);
commit;
-- Step 2: Insert new table created
INSERT into y4a_fin_analyst.hungcht_table_database_monitoring
select * from y4a_fin_analyst.hungcht_check_table A
where not exists (select 1 from y4a_fin_analyst.hungcht_table_database_monitoring b
where a.table_name = b.table_name
and a.schema_name = b.schema_name
and a.owner_name = b.owner_name);
commit;
-- Step 3: Update ROW_COUNT value and updated time
TRUNCATE y4a_fin_analyst.hungcht_table_database_monitoring_tmp;
COMMIT;
INSERT INTO y4a_fin_analyst.hungcht_table_database_monitoring_tmp
select
a.table_name,
a.schema_name,
a.owner_name,
a.row_count,
b."row_count" as last_row_count,
a."row_count" - b."row_count" as row_change_cnt,
COALESCE(b.table_type,a.table_type) as table_type,
b.create_date,
a.update_date
from y4a_fin_analyst.hungcht_check_table A
join y4a_fin_analyst.hungcht_table_database_monitoring b
on a.table_name = b.table_name
and a.schema_name = b.schema_name
and a.owner_name = b.owner_name
and a."row_count" <> b."row_count";
commit;
delete from y4a_fin_analyst.hungcht_table_database_monitoring A
where exists (select 1 from y4a_fin_analyst.hungcht_table_database_monitoring_tmp b
where a."table_name" = b."table_name"
and a."schema_name" = b."schema_name"
and a."owner_name" = b."owner_name");
commit;
insert into y4a_fin_analyst.hungcht_table_database_monitoring
select * from y4a_fin_analyst.hungcht_table_database_monitoring_tmp;
commit;
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS
_message := MESSAGE_TEXT,
_sql_state := RETURNED_SQLSTATE;
update y4a_fin_analyst.hungcht_proc_monitoring
set
end_date = current_timestamp AT TIME ZONE 'Asia/Bangkok',
status = 'error',
err_message = _message,
err_sqlstate = _sql_state
where proc_name = 'hungcht_update_table_database_monitoring'
and end_date is null;
commit;
END;
$BODY$
LANGUAGE plpgsql
My expect, the code can work with code above exception, and can handle update error to monitoring table when error occurs: enter image description here
I think i can find the reason of the problem that is multiple 'commit', so just remove all the 'commit' it would be solved, but i dont know the basic inside why commit would cause all skip the code when procedure have exception, if we remove the exception part, the commit wont affect to procedure.