Re: Context variable in application and trigger code

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(dot)klaver(at)aklaver(dot)com