public inbox for [email protected]  

help / color / mirror / Atom feed

From: Juan Rodrigo Alejandro Burgos Mella <[email protected]>
To: yudhi s <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Manual query vs trigger during data load
Date: Fri, 13 Sep 2024 06:58:05 -0300
Message-ID: <CAHbZ42zSrQAANMHaU78N9CBFYoK47kReRebijd8XS3+Oag8htA@mail.gmail.com> (raw)
In-Reply-To: <CAEzWdqfbZqtMuc5MD3g+iQDqN75WubeWcEvO6eDSBC_QmbKDvg@mail.gmail.com>
References: <CAEzWdqfbZqtMuc5MD3g+iQDqN75WubeWcEvO6eDSBC_QmbKDvg@mail.gmail.com>

Hello, I find it unlikely that the trigger will work properly, since the
reserved fields of the OLD subset have no value in an INSERT

Atte
JRBN

El vie, 13 de sept de 2024, 04:32, yudhi s <[email protected]>
escribió:

> Hello All,
>
> We are having a table which is going to be inserted with 100's of millions
> of rows each day. And we now want to have a requirement in which we need to
> do some transformation/lookup logic built on top of a few of the input bind
> values , while inserting the data. So I wanted to understand ,is it
> possible to do it along with the INSERT query or is it better to have a
> trigger created for the same?
>
> For. e.g Below is the current Insert query used in the Java code. We want
> to fetch the value for "column2" from a lookup table rather than directly
> inserting as it's coming from the customer side. So I am thinking of a
> trigger like below. But at the same time I also want to compare the
> performance of a normal way of doing the lookup vs having it performed
> using triggers.
>
> So one way i am thinking is first fetching the value of the "column2" from
> reference_tab1 using a separate "select query" in Java code itself,  and
> then passing that to the below insert query, but i think that will increase
> the response time as that will be a separate DB call.
>
> 1)So,  is there a way I can do it directly using the single INSERT query
> itself without additional SELECT query? And then will try to compare that
> with the trigger based approach.
> 2)Additionally , if this decision will impact a batch insert approach.
> i.e. say , in case of trigger , will the batch insert fail because
> trigger will force it to make it row by row?
>
> INSERT INTO tab_part1 (column1, column2, column3, column4, column5,
> part_date)
>         VALUES (:v_col1, :v_col2, :v_col3, :v_col3, :v_col4,:v_col5,
> CURRENT_DATE);
>
> CREATE OR REPLACE FUNCTION trg_func_populate_column2() RETURNS TRIGGER AS
> $$
> BEGIN
>     -- Fetch reference value and populate column2
>     NEW.column2 := (SELECT lookup_key FROM reference_tab1 WHERE lookup_key
> = old.column2);
>     RETURN NEW;
> END;
> $$ LANGUAGE plpgsql;
>
> Regards
> Yudhi
>

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: Manual query vs trigger during data load
  In-Reply-To: <CAHbZ42zSrQAANMHaU78N9CBFYoK47kReRebijd8XS3+Oag8htA@mail.gmail.com>

* 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