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@xxxxxxxxxxx
