I have search exception handler in postgres it says to add
DECLARE
text_var1 text;
text_var2 text;
text_var3 text;
BEGIN
-- some processing which might cause an exception
...
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
text_var2 = PG_EXCEPTION_DETAIL,
text_var3 = PG_EXCEPTION_HINT;
-- call a function which log all this variable perform log_function (text_var1 ,text_var2 , text_var3 );
END;
If I do that for every function is this efficient?
Any performance lack by this approach?
Can't we have a global exception handler for all functions? In this approach I have added the exception handler for every function.
Then save them in variable and save log for that. Can we have any global error handler for that?
Edit
I have to log all exception coming in postgres into a table . I am following this step.
1) write GET STACKED block in all function (if i have 20 function then i write this block in all function)
2)Than log then in table.
What i want is there should be mechanism for global exception handler in postgres , So that i have to not write GET STACKED EXCEPTION block in postgres for all function
Using an EXCEPTION
clause in a PL/pgSQL block has a negative performance impact; see the “tip” in the documentation of error trapping.
There is no way to automatically handle all top-level exceptions in a PL/pgSQL function.
My recommendation is that you don't log errors in database functions, but from the application. For one, when your transaction rolls back, the log will be gone.