Define custom variables instead of setting them in a function invoked on each session startup

55 views Asked by At

Function f1() is executed over a million times a day causing CPU spikes.

This function is invoked for every webpage, batch job etc. or we can say that if an application/batch job/etc. connects to the DB, then this function gets invoked first, which sets up the RLS parameters.

Example:

PERFORM set_config (namespace || '.'||attribute,value,false);

The function is lightweight and currently takes 2 ms to set up the session variables as described above. Can these session variables be cached so that application can use it from cache instead of executing the function?

Here is my function:

CREATE OR REPLACE FUNCTION f1() 
RETURNS character varying LANGUAGE plpgsql STABLE AS $function$ 
DECLARE
   v_status boolean; 
BEGIN 
   return current_setting('ctx_vpd'||'.'||'ctx_vpd_fil'); 
   exception when undefined_object then return 'ERROR'; 
END;
$function$;
0

There are 0 answers