英文:
Postgresql procedure skip code above Exception?
问题
我有一个过程,可以执行插入、删除、截断表等操作在postgresql数据库中。
然后我想监视过程中的作业,然后我对'hungcht_proc_monitoring'做一些插入/更新,使用异常技术,该过程仍在运行,但跳过了所有上面的代码。我进行了一些搜索,但找不到问题和解决方法。希望你们是PLSQL开发专业人士可以修复我下面的代码,谢谢你们,非常感谢。
代码运行良好,但跳过异常上面的代码。
英文:
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
答案1
得分: 0
我认为我可以找到问题的原因,那就是多次提交,所以只需移除所有的“commit”即可解决,但我不知道为什么提交会导致在过程中出现异常时跳过所有代码的基本原因,如果我们移除异常部分,提交就不会影响过程。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论