Procedure call inside another procedure with Exception handling. same Timestamp for all calls and no-CodePudding

If got a simple procedure which does some logging handling for me:

CREATE PROCEDURE logging(name TEXT, message TEXT) 
BEGIN  
    INSERT INTO log_table
    SELECT NOW(), name,  message;
END

now i call my procedure several times from inside another procedure:

CREATE PROCEDURE my_proc()
BEGIN
   CALL logging('my_proc', 'start');
   
   insert into myTable
   select * from another_table;
   
   CALL logging('my_proc', 'end');
   EXCEPTION WHEN OTHERS THEN
     CALL logging('my_proc',SQLSTATE || ' - ' ||SQLERRM );
END

problem one: When the procedure runs without exception, start and finish have the exact same timestamp, altough the query did take some time:

"2022-11-16 15:11:01.322317" "my_proc" "start"
"2022-11-16 15:11:01.322317" "my_proc" "end"

changing the procedure to use the timestamp as parameter instead of generating it inside the procedure gives the same behaviour

problem two: when an error occurs, only the procedure call from within the exception will be executed, the start call will not be executed. So i only see the error message in my logging table, but not the start-Message.

So, how can i fix the timestamp so that he represents the time before and after the query instead of using the same time?

And how can i execute my first procedure call ('start'), even if an error with exception handling happens in the query afterwards?

CodePudding user response：

You can split you code via different transactions. For example:

CREATE OR REPLACE PROCEDURE my_proc()
LANGUAGE plpgsql
AS $procedure$
BEGIN
    CALL logging('my_proc', 'start');
   
    begin
        insert into myTable
        select * from another_table;
   
        CALL logging('my_proc', 'end');
        EXCEPTION WHEN OTHERS THEN
            CALL logging('my_proc',SQLSTATE || ' - ' ||SQLERRM );
    end;
END;
$procedure$

So, I explain you why you are gets same timestamp. On PostgreSQL current_date or now() gets same timestamp for same transactions. These are refreshing the time at the beginning of the transactions. You can use clock_timestamp(), this function is refreshing at the execution of the statement.