PostgreSQL存储过程在异常之上跳过代码?

huangapple go评论71阅读模式
英文:

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.

huangapple
  • 本文由 发表于 2023年5月10日 12:17:28
  • 转载请务必保留本文链接:https://go.coder-hub.com/76214855.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定