Thread: Context variable in application and trigger code
Hi,
We have a trigger function called from a trigger which executes before inserting rows in the table (say TAB1). This trigger function does some conversion of code to description and persists the description in the table in respective columns. We want to keep this trigger as light as possible as there will be 100's million rows inserted into this table from multiple input sources and the conversion should only happen the inserts which happen from a specific input data stream.
There are 4-5 different input sources which will ingest data into this table (some are using file based processing framework and some are using other streaming technology). Say for example we want this description to only be fetched for input source - 1, but not others. We don't have any such column persisted in the table which can be checked for the input data stream such as this code to describe decoding can be made conditional.
There are 4-5 different input sources which will ingest data into this table (some are using file based processing framework and some are using other streaming technology). Say for example we want this description to only be fetched for input source - 1, but not others. We don't have any such column persisted in the table which can be checked for the input data stream such as this code to describe decoding can be made conditional.
Are there any techniques possible in which we can set some parameter or session context variable in application code level to determine the input data source, which can then be checked within the trigger function code at the very first and thus will avoid querying the "CODE" table every time the trigger executes?
Existing sample trigger code:-
CREATE OR REPLACE FUNCTION code_to_desc( )
RETURNS trigger
LANGUAGE plpgsql
AS $function$
declare code_description code.code%TYPE;
begin
select currency_description_text into code_description
from code
where code = new.code_input;
CREATE OR REPLACE FUNCTION code_to_desc( )
RETURNS trigger
LANGUAGE plpgsql
AS $function$
declare code_description code.code%TYPE;
begin
select currency_description_text into code_description
from code
where code = new.code_input;
IF FOUND THEN NEW.code_input := code_description;
END IF;
return NEW;
end;
$function$;
end;
$function$;
Regards
Yudhi
On 9/28/24 14:37, yudhi s wrote: > Hi, > We have a trigger function called from a trigger which executes before > inserting rows in the table (say TAB1). This trigger function does some > conversion of code to description and persists the description in the > table in respective columns. We want to keep this trigger as light as > possible as there will be 100's million rows inserted into this table > from multiple input sources and the conversion should only happen the > inserts which happen from a specific input data stream. > > There are 4-5 different input sources which will ingest data into this > table (some are using file based processing framework and some are using > other streaming technology). Say for example we want this description to > only be fetched for input source - 1, but not others. We don't have any > such column persisted in the table which can be checked for the input > data stream such as this code to describe decoding can be made conditional. > > Are there any techniques possible in which we can set some parameter or > session context variable in application code level to determine the > input data source, which can then be checked within the trigger function > code at the very first and thus will avoid querying the "CODE" table > every time the trigger executes? There is: https://www.postgresql.org/docs/16/runtime-config-logging.html#GUC-APPLICATION-NAME Though if you have multiple inputs happening concurrently I am not sure how you would sort out which is supplying the data for a given trigger instance. Also, by this time you are basically done anyway so I don't see how would save enough time. Seems to me this calls for either doing the conversion in the application or include a throw way field in the stream data that indicates it is coming from the stream or stream to an intake table and then convert when transferring to final table. > > > *Existing sample trigger code:-* > > CREATE OR REPLACE FUNCTION code_to_desc( ) > RETURNS trigger > LANGUAGE plpgsql > AS $function$ > declare code_description code.code%TYPE; > begin > select currency_description_text into code_description > from code > where code = new.code_input; > > IF FOUND THEN NEW.code_input := code_description; > END IF; > > return NEW; > end; > $function$; > > Regards > Yudhi -- Adrian Klaver adrian.klaver@aklaver.com