How to do global exception handler in postgres?

Question

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

yes - you can log errors to log and use csv log format to import it to a table - I think this is the meant way to catch all exceptions — Vao Tsun
– Vao Tsun, Commented Oct 4, 2017 at 8:25

Laurenz Albe · Accepted Answer · 2017-10-04 08:37:38Z

1

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.

edited Oct 4, 2017 at 8:37

answered Oct 4, 2017 at 7:40

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Himanshu sharma Over a year ago

that code is sample how the exception handled currently. For current process i have to write this block in all function . can we do that by any global handler in postgres?

Himanshu sharma Over a year ago

I have added more info and use case in edit section.

Collectives™ on Stack Overflow

How to do global exception handler in postgres?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related