public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: yudhi s <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: Context variable in application and trigger code
Date: Sat, 28 Sep 2024 14:57:25 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAEzWdqfxakfrPqNPSohRm12mO3XyD3Cr9QxM488J8y3x0gFMqA@mail.gmail.com>
References: <CAEzWdqfxakfrPqNPSohRm12mO3XyD3Cr9QxM488J8y3x0gFMqA@mail.gmail.com>

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
[email protected]

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Context variable in application and trigger code
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox