postgresqlplsqlprocedure

Postgresql procedure skip code above Exception?


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


Solution

  • 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.