MIME-Version: 1.0
References: <CAEzWdqfbZqtMuc5MD3g+iQDqN75WubeWcEvO6eDSBC_QmbKDvg@mail.gmail.com>
In-Reply-To: <CAEzWdqfbZqtMuc5MD3g+iQDqN75WubeWcEvO6eDSBC_QmbKDvg@mail.gmail.com>
From: Juan Rodrigo Alejandro Burgos Mella <rodrigoburgosmella@gmail.com>
Date: Fri, 13 Sep 2024 06:58:05 -0300
Message-ID: <CAHbZ42zSrQAANMHaU78N9CBFYoK47kReRebijd8XS3+Oag8htA@mail.gmail.com>
Subject: Re: Manual query vs trigger during data load
To: yudhi s <learnerdatabase99@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000001f10590621fd43b4"
Archived-At: <https://www.postgresql.org/message-id/CAHbZ42zSrQAANMHaU78N9CBFYoK47kReRebijd8XS3%2BOag8htA%40mail.gmail.com>
Precedence: bulk

--0000000000001f10590621fd43b4
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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 <learnerdatabase99@gmail.com>
escribi=C3=B3:

> Hello All,
>
> We are having a table which is going to be inserted with 100's of million=
s
> 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 bi=
nd
> 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" fro=
m
> 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 increa=
se
> 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 :=3D (SELECT lookup_key FROM reference_tab1 WHERE lookup_=
key
> =3D old.column2);
>     RETURN NEW;
> END;
> $$ LANGUAGE plpgsql;
>
> Regards
> Yudhi
>

--0000000000001f10590621fd43b4
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<p dir=3D"ltr">Hello, I find it unlikely that the trigger will work properl=
y, since the reserved fields of the OLD subset have no value in an INSERT</=
p>
<p dir=3D"ltr">Atte <br>
JRBN</p>
<br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">El vie=
, 13 de sept de 2024, 04:32, yudhi s &lt;<a href=3D"mailto:learnerdatabase9=
9@gmail.com">learnerdatabase99@gmail.com</a>&gt; escribi=C3=B3:<br></div><b=
lockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px =
#ccc solid;padding-left:1ex"><div dir=3D"ltr">Hello All,<div><br>We are hav=
ing a table which is going to be inserted with 100&#39;s of millions of row=
s each day. And we now want to have a requirement in which we need to do so=
me transformation/lookup logic built on top of a few of the input bind valu=
es , 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 create=
d for the same?<br><br>For. e.g Below is the current Insert query used in t=
he Java code. We want to fetch the value for &quot;column2&quot;	from a loo=
kup table rather than directly inserting as it&#39;s coming from the custom=
er side. So I am thinking of a trigger like below. But at the same time I a=
lso want to compare the performance of a normal way of doing the lookup vs =
having it performed using triggers. <br><br>So one way i am thinking is fir=
st fetching the value of the &quot;column2&quot; from reference_tab1 using =
a separate &quot;select query&quot; in Java code itself, =C2=A0and then pas=
sing that to the below insert query, but i think that will increase the res=
ponse time as that will be a separate DB call.</div><div><br></div><div>1)S=
o,=C2=A0=C2=A0is there a way I can do it directly using the single INSERT q=
uery itself without additional SELECT query? And then will try to compare t=
hat with the trigger based approach.<br>2)Additionally , if this decision w=
ill impact a batch insert approach. i.e. say , in case of trigger , will th=
e batch insert fail because trigger=C2=A0will force it to make it row by ro=
w?<br><br>INSERT INTO tab_part1 (column1, column2, column3, column4, column=
5, part_date)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 VALUES (:v_col1, :v_col2, :v_c=
ol3, :v_col3, :v_col4,:v_col5, CURRENT_DATE);<br><br>CREATE OR REPLACE FUNC=
TION trg_func_populate_column2() RETURNS TRIGGER AS $$<br>BEGIN<br>=C2=A0 =
=C2=A0 -- Fetch reference value and populate column2<br>=C2=A0 =C2=A0 NEW.c=
olumn2 :=3D (SELECT lookup_key FROM reference_tab1 WHERE lookup_key =3D old=
.column2);<br>=C2=A0 =C2=A0 RETURN NEW;<br>END;<br>$$ LANGUAGE plpgsql;<br>=
</div><div><br></div><div>Regards</div><div>Yudhi</div></div>
</blockquote></div>

--0000000000001f10590621fd43b4--