I have a plpgsql Procedure where I am simply trying to handle any possible exceptions, since I will be running these Procedures on pg_cron (automated) and I do not want anything to fail. The basic skeleton of the procedure looks like this:

CREATE OR REPLACE PROCEDURE marketing_offers.stackover_overflow_ex_question(limit_size integer)
    LANGUAGE plpgsql
AS
$procedure$
DECLARE
    n_rec_cnt  bigint;
   
BEGIN
        LOOP
            EXIT WHEN n_rec_cnt = 0;
          
           WITH cte AS (SELECT *
                        FROM master_table mt
                        where mt.created_date <= '1999-01-01'::time
                        LIMIT limit_size)
            INSERT INTO some_archive_table (SELECT * FROM cte)
            COMMIT;
           
           GET DIAGNOSTICS n_rec_cnt = row_count;
       
           RAISE EXCEPTION 'Max retry count exceeded';
          
            begin
           EXCEPTION 
           WHEN OTHERS then
            GET STACKED DIAGNOSTICS text_var1 = message_text,
                          text_var2 = PG_EXCEPTION_DETAIL,
                          text_var3 = PG_EXCEPTION_HINT;
            RAISE NOTICE 'error msg is %', text_var1;
              
            UPDATE job_log
            SET error_msg  = text_var1
            return;
           end;
          
           
        END LOOP;      
END;
$procedure$
;

The problem is the RAISE NOTICE with text_var1, which is supposed to hold the SQL exception message, never gets logged neither does the UPDATE statement to my job_log table that should hold the message also.

I also would like to add that , I had to surround the EXCEPTION block with another begin and end , because when I did not, I would get a syntax error.

I am simply trying to catch the exception from my SQL script - should I be using a different EXCEPTION type and should I be looking for specific SQL codes? I'm kind of confused what the best practices are here

CodePudding user response：

You should really start indenting your code. This is not just about being pretty, but it would immediately show you the problem with your code.

Your code, properly indented, looks like this:

BEGIN
   LOOP
      EXIT WHEN n_rec_cnt = 0;

      COMMIT;
           
      RAISE EXCEPTION 'Max retry count exceeded';
          
      begin
      EXCEPTION 
          WHEN OTHERS then
             RAISE NOTICE 'error msg is %', text_var1;
              
             UPDATE job_log
                SET error_msg  = text_var1
                return;
      end;
   END LOOP;      
END;

There are two things obvious:

1. You forgot the semicolon in front of the RETURN → syntax error

2. The EXCEPTION clause is part of the block that starts with the BEGIN in the immediately preceding line.

   Since an EXCEPTION clause will only catch exceptions thrown in the block to which it belongs, and that block is empty, execution can never reach the exception handler.

You are obviously fighting with the restriction that COMMIT cannot be executed inside a block with an EXCEPTION clause. But since it is not clear what you want to do (for example, the unconditional RAISE EXCEPTION seems pointless), it is difficult to help you.