MIME-Version: 1.0
References: <CAEzWdqfbZqtMuc5MD3g+iQDqN75WubeWcEvO6eDSBC_QmbKDvg@mail.gmail.com>
 <CAHbZ42zSrQAANMHaU78N9CBFYoK47kReRebijd8XS3+Oag8htA@mail.gmail.com>
 <f76b71b5-1f99-43bd-bdca-202999ea94be@aklaver.com> <67f2a664-b480-40ea-892d-2ab2147ad390@aklaver.com>
In-Reply-To: <67f2a664-b480-40ea-892d-2ab2147ad390@aklaver.com>
From: yudhi s <learnerdatabase99@gmail.com>
Date: Sat, 14 Sep 2024 00:54:49 +0530
Message-ID: <CAEzWdqe0VDpTi26xby+cNTMUp6goyZE1tENua08Cg-_az1mzqQ@mail.gmail.com>
Subject: Re: Manual query vs trigger during data load
To: Adrian Klaver <adrian.klaver@aklaver.com>, 
	Juan Rodrigo Alejandro Burgos Mella <rodrigoburgosmella@gmail.com>, Rob Sargent <robjsargent@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000ec82650622052d15"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqe0VDpTi26xby%2BcNTMUp6goyZE1tENua08Cg-_az1mzqQ%40mail.gmail.com>
Precedence: bulk

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

On Fri, Sep 13, 2024 at 8:27=E2=80=AFPM Adrian Klaver <adrian.klaver@aklave=
r.com>
wrote:

> On 9/13/24 07:50, Adrian Klaver wrote:
> > On 9/13/24 02:58, Juan Rodrigo Alejandro Burgos Mella wrote:
> >> 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
> >
> > I'm not seeing that the OP is asking for OLD.* values, they are just
> > looking to include the result of a lookup on another table in the INSER=
T.
>
> My mistake I see the OLD reference now.
>
> >
>

My mistake.The trigger was supposed to use "new.col2" and fetch the
corresponding lookup value from the lookup table and insert that value to
the target table.

Now my question was ,in such a situation , the trigger will work fine , but
is that the optimal way of doing ? Or should we convert the query someway
such that the lookup table can be queried along with the INSERT at one shot
from the database with a single DB call? And is it true that the trigger on
the target table will suppress the batch insert and make it row by row,
even if we call it in a batch fashion?

As "thiemo" mentioned , it can be done as below method, but if we have
multiple lookup tables to be populated for multiple columns , then , how
can the INSERT query be tweaked to cater the need here? And I understand ,
the lookup table can be cached in Java and refreshed at a certain point in
time, but I was trying to understand if this can be doable by directly
querying the database, considering the lookup tables are having large data
sets in them.

Insert into tab1 (val1, val2)
Select valA, valB
From tab2
Where valC =3D :param1

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote"><div=
 dir=3D"ltr" class=3D"gmail_attr">On Fri, Sep 13, 2024 at 8:27=E2=80=AFPM A=
drian Klaver &lt;<a href=3D"mailto:adrian.klaver@aklaver.com">adrian.klaver=
@aklaver.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" styl=
e=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddin=
g-left:1ex">On 9/13/24 07:50, Adrian Klaver wrote:<br>
&gt; On 9/13/24 02:58, Juan Rodrigo Alejandro Burgos Mella wrote:<br>
&gt;&gt; Hello, I find it unlikely that the trigger will work properly, sin=
ce <br>
&gt;&gt; the reserved fields of the OLD subset have no value in an INSERT<b=
r>
&gt; <br>
&gt; I&#39;m not seeing that the OP is asking for OLD.* values, they are ju=
st <br>
&gt; looking to include the result of a lookup on another table in the INSE=
RT.<br>
<br>
My mistake I see the OLD reference now.<br>
<br>
&gt;<br></blockquote><div><br></div><div>My mistake.The trigger was suppose=
d=C2=A0to use &quot;new.col2&quot; and fetch the corresponding lookup value=
 from the lookup table and insert that value to the target table.</div><div=
><br></div><div>Now my question was ,in such a situation , the trigger=C2=
=A0will work fine , but is that the optimal way of doing ? Or should we con=
vert the query someway such that the lookup table can be queried along with=
 the INSERT at one shot from the database with a single DB call? And is it =
true that the=C2=A0trigger on the target table will suppress the batch inse=
rt and make it row by row, even if we call it in a batch fashion?</div><div=
><br></div><div>As &quot;thiemo&quot; mentioned , it can be done as below m=
ethod, but if we have multiple=C2=A0lookup tables to be populated for multi=
ple columns , then , how can the INSERT query be tweaked to cater the need =
here? And I understand , the lookup table can be cached in Java and refresh=
ed at a certain point in time, but I was trying to understand if this can b=
e doable by directly querying the database, considering the lookup tables a=
re having large data sets in them.</div><div><br></div><div><span dir=3D"lt=
r" style=3D"margin-top:0px;margin-bottom:0px">Insert into tab1 (val1, val2)=
</span><br><span dir=3D"ltr" style=3D"margin-top:0px;margin-bottom:0px">Sel=
ect valA, valB</span><br><span dir=3D"ltr" style=3D"margin-top:0px;margin-b=
ottom:0px">From tab2</span><br><span dir=3D"ltr" style=3D"margin-top:0px;ma=
rgin-bottom:0px">Where valC =3D :param1</span><br></div><div>=C2=A0</div></=
div></div>

--000000000000ec82650622052d15--