MIME-Version: 1.0
References: 
 <CAKJANSD8QG2XbkexZFpd=afVyvFfG=xNm6zVF-Z1DcK0+QrCig@mail.gmail.com>
 <CAJexoSJcq8taBbyFvCKnZF=A0DLK+-SdVdeVaGasopUCHukjdg@mail.gmail.com>
In-Reply-To: 
 <CAJexoSJcq8taBbyFvCKnZF=A0DLK+-SdVdeVaGasopUCHukjdg@mail.gmail.com>
From: intmail01 <intmail01@gmail.com>
Date: Sat, 9 Mar 2024 20:11:28 +0300
Message-ID: 
 <CAKJANSAkNPxoQdN8egChaK78=8qoKSD8_i2qrYNAH98EbO5GaQ@mail.gmail.com>
Subject: Re: How to know if all insertions are finished
To: Steve Midgley <science@misuse.org>
Cc: pgsql-sql@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000eb243106133d6438"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKJANSAkNPxoQdN8egChaK78%3D8qoKSD8_i2qrYNAH98EbO5GaQ%40mail.gmail.com>
Precedence: bulk

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

>I think you are saying in your last line that each insert line is treated
as an individual statement, so you get a bunch of trigger notifications
instead of just one, and >you don't know how to detect when you have
received the final insert?
Yes. That is the problem.

I do not code to insert data, it is done by drag/move from calc sheet to
table graphically.

The dummy row may be the solution with the risk you mention.

Thanks.

On Fri, Mar 8, 2024 at 7:27=E2=80=AFPM Steve Midgley <science@misuse.org> w=
rote:

>
>
> On Fri, Mar 8, 2024 at 4:01=E2=80=AFAM intmail01 <intmail01@gmail.com> wr=
ote:
>
>> Hi,
>>
>> Is there any way to know if several insertion are terminated.
>> I use libreoffice base to import at the same time many lines. When all
>> lines are inserted I have to do some data check. I dont find any way to
>> know if the insertion ends to start some function automatically.
>> I tried to use a STATEMENT trigger type but all lines are treated as
>> statement.
>>
>
> I think you are saying in your last line that each insert line is treated
> as an individual statement, so you get a bunch of trigger notifications
> instead of just one, and you don't know how to detect when you have
> received the final insert?
>
> The first thing I'd look for is whether I could wrap all the libreoffice
> inserts into a single transaction block, and then event off of the
> transaction block completing.
>
> Barring that, and noting I'm not familiar with how libreoffice would send
> the inserts, one option could be to put a listen/notify pattern at the en=
d
> of your insert code, if it's possible to add a line of sql at the end of
> the inserts? Of course, if you can do that, you might also be able to wra=
p
> the whole thing with a transaction, but listen/notify might be simpler to
> event off of..
>
> -- In session/connection 1
> BEGIN;
> -- Perform your bulk insert operation here
> COMMIT;
> NOTIFY inserts_done;
>
> -- In session/connection 2
> LISTEN inserts_done;
> -- Once notification is received, proceed with necessary operations
>
> If you can't do something like this, the only other (relatively horrible)
> things I can think of would be to:
>
>    1. Put a dummy row at the end of your insert data (if you can
>    guarantee it would be inserted last during the bulk operation), and th=
en
>    build a trigger/condition to detect insertion of a row matching the du=
mmy
>    data that deletes the dummy row and then proceeds to kick off whatever
>    other processes you want, or:
>    2. Use a time based model, where you build an understanding of how
>    long your inserts take to execute, and build a monitor that kicks off =
on
>    each insert, and if another insert is not detected within a certain am=
ount
>    of wait time, you conclude that there are no more inserts coming, and =
you
>    then kick off whatever other processes you want..
>
> Hopefully someone with deeper Postgres knowledge has other, more reliable
> solutions that leverage Postgres, but if your inserts are all discrete, a=
nd
> you don't have control over the sql itself.
>
> Steve
>

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

<div dir=3D"ltr"><div>&gt;I think you are saying in your last line that eac=
h insert line is=20
treated as an individual statement, so you get a bunch of trigger=20
notifications instead of just one, and &gt;you don&#39;t know how to detect=
 when
 you have received the final insert?</div><div></div><div>Yes. That is the =
problem.</div><div><br></div><div>I do not code to insert data, it is done =
by drag/move from calc sheet to table graphically.</div><div><br></div><div=
>The dummy row may be the solution with the risk you mention.</div><div><br=
></div><div>Thanks.<br></div></div><br><div class=3D"gmail_quote"><div dir=
=3D"ltr" class=3D"gmail_attr">On Fri, Mar 8, 2024 at 7:27=E2=80=AFPM Steve =
Midgley &lt;<a href=3D"mailto:science@misuse.org">science@misuse.org</a>&gt=
; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px=
 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div di=
r=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gma=
il_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Fri, Mar 8, 2024 at 4:01=
=E2=80=AFAM intmail01 &lt;<a href=3D"mailto:intmail01@gmail.com" target=3D"=
_blank">intmail01@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gm=
ail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,=
204,204);padding-left:1ex"><div dir=3D"ltr"><div>Hi,</div><br><div><div><di=
v>Is there any way to know if several insertion are terminated.</div><div>I=
 use libreoffice base to import at the same time many lines. When all lines=
 are inserted I have to do some data check. I dont find any way to know if =
the insertion ends to start some function automatically.</div><div>I tried =
to use a STATEMENT trigger type but all lines are treated as statement.<br>=
</div></div></div></div></blockquote><div><br></div><div>I think you are sa=
ying in your last line that each insert line is treated as an individual st=
atement, so you get a bunch of trigger notifications instead of just one, a=
nd you don&#39;t know how to detect when you have received the final insert=
?</div><div><br></div><div>The first thing I&#39;d look for is whether I co=
uld wrap all the libreoffice inserts into a single transaction block, and t=
hen event off of the transaction block completing.</div><div><br></div><div=
>Barring that, and noting I&#39;m not familiar with how libreoffice would s=
end the inserts, one option could be to put a listen/notify pattern at the =
end of your insert code, if it&#39;s possible to add a line of sql at the e=
nd of the inserts? Of course, if you can do that, you might also be able to=
 wrap the whole thing with a transaction, but listen/notify might be simple=
r to event off of..</div><div><br></div><div><div><font face=3D"monospace">=
-- In session/connection 1</font></div><div><font face=3D"monospace">BEGIN;=
</font></div><div><font face=3D"monospace">-- Perform your bulk insert oper=
ation here</font></div><div><font face=3D"monospace">COMMIT;</font></div><d=
iv><font face=3D"monospace">NOTIFY inserts_done;</font></div><div><font fac=
e=3D"monospace"><br></font></div><div><font face=3D"monospace">-- In sessio=
n/connection 2</font></div><div><font face=3D"monospace">LISTEN inserts_don=
e;</font></div><div><font face=3D"monospace">-- Once notification is receiv=
ed, proceed with necessary operations</font></div></div><div><br></div><div=
>If you can&#39;t do something like this, the only other (relatively horrib=
le) things I can think of would be to:</div><div><ol><li>Put a dummy row at=
 the end of your insert data (if you can guarantee it would be inserted las=
t during the bulk operation), and then build a trigger/condition to detect =
insertion of a row matching the dummy data that deletes the dummy row and t=
hen proceeds to kick off whatever other processes you want, or:</li><li>Use=
 a time based model, where you build an understanding of how long your inse=
rts take to execute, and build a monitor that kicks off on each insert, and=
 if another insert is not detected within a certain amount of wait time, yo=
u conclude that there are no more inserts coming, and you then kick off wha=
tever other processes you want..</li></ol><div>Hopefully someone with deepe=
r Postgres knowledge has other, more reliable solutions that leverage Postg=
res, but if your inserts are all discrete, and you don&#39;t have control o=
ver the sql itself.</div></div><div><br></div><div>Steve</div></div></div><=
/div>
</blockquote></div>

--000000000000eb243106133d6438--