Feedback-ID: i76984098:Fastmail
Message-ID: <dbdf44c2-3afd-44c5-a8cf-ec44ed05d3e9@aklaver.com>
Date: Sat, 28 Sep 2024 14:57:25 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Context variable in application and trigger code
To: yudhi s <learnerdatabase99@gmail.com>,
 pgsql-general <pgsql-general@lists.postgresql.org>
References: <CAEzWdqfxakfrPqNPSohRm12mO3XyD3Cr9QxM488J8y3x0gFMqA@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAEzWdqfxakfrPqNPSohRm12mO3XyD3Cr9QxM488J8y3x0gFMqA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/dbdf44c2-3afd-44c5-a8cf-ec44ed05d3e9%40aklaver.com>
Precedence: bulk

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