MIME-Version: 1.0
References: 
 <CAKJANSD8QG2XbkexZFpd=afVyvFfG=xNm6zVF-Z1DcK0+QrCig@mail.gmail.com>
In-Reply-To: 
 <CAKJANSD8QG2XbkexZFpd=afVyvFfG=xNm6zVF-Z1DcK0+QrCig@mail.gmail.com>
From: Steve Midgley <science@misuse.org>
Date: Fri, 8 Mar 2024 08:26:21 -0800
Message-ID: 
 <CAJexoSJcq8taBbyFvCKnZF=A0DLK+-SdVdeVaGasopUCHukjdg@mail.gmail.com>
Subject: Re: How to know if all insertions are finished
To: intmail01 <intmail01@gmail.com>
Cc: pgsql-sql@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000bae952061328a9c3"
Archived-At: 
 <https://www.postgresql.org/message-id/CAJexoSJcq8taBbyFvCKnZF%3DA0DLK%2B-SdVdeVaGasopUCHukjdg%40mail.gmail.com>
Precedence: bulk

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

On Fri, Mar 8, 2024 at 4:01=E2=80=AFAM intmail01 <intmail01@gmail.com> wrot=
e:

> 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 end
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 wrap
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 then build a
   trigger/condition to detect insertion of a row matching the dummy data t=
hat
   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 amount of
   wait time, you conclude that there are no more inserts coming, and you t=
hen
   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, and
you don't have control over the sql itself.

Steve

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

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=
=3D"gmail_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">int=
mail01@gmail.com</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);pa=
dding-left:1ex"><div dir=3D"ltr"><div>Hi,</div><br><div><div><div>Is there =
any way to know if several insertion are terminated.</div><div>I use libreo=
ffice base to import at the same time many lines. When all lines are insert=
ed I have to do some data check. I dont find any way to know if the inserti=
on ends to start some function automatically.</div><div>I tried to use a ST=
ATEMENT trigger type but all lines are treated as statement.<br></div></div=
></div></div></blockquote><div><br></div><div>I think you are saying in you=
r 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&=
#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 could wrap al=
l the libreoffice inserts into a single transaction block, and then event o=
ff of the transaction block completing.</div><div><br></div><div>Barring th=
at, and noting I&#39;m not familiar with how libreoffice would send the ins=
erts, 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 end of the i=
nserts? Of course, if you can do that, you might also be able to wrap the w=
hole thing with a transaction, but listen/notify might be simpler to event =
off of..</div><div><br></div><div><div><font face=3D"monospace">-- In sessi=
on/connection 1</font></div><div><font face=3D"monospace">BEGIN;</font></di=
v><div><font face=3D"monospace">-- Perform your bulk insert operation here<=
/font></div><div><font face=3D"monospace">COMMIT;</font></div><div><font fa=
ce=3D"monospace">NOTIFY inserts_done;</font></div><div><font face=3D"monosp=
ace"><br></font></div><div><font face=3D"monospace">-- In session/connectio=
n 2</font></div><div><font face=3D"monospace">LISTEN inserts_done;</font></=
div><div><font face=3D"monospace">-- Once notification is received, proceed=
 with necessary operations</font></div></div><div><br></div><div>If you can=
&#39;t do something like this, the only other (relatively horrible) 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 last during th=
e bulk operation), and then build a trigger/condition to detect insertion o=
f a row matching the dummy data that deletes the dummy row and then proceed=
s to kick off whatever other processes you want, or:</li><li>Use a time bas=
ed 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 amount of wait time, you conclude =
that there are no more inserts coming, and you then kick off whatever other=
 processes you want..</li></ol><div>Hopefully someone with deeper Postgres =
knowledge has other, more reliable solutions that leverage Postgres, but if=
 your inserts are all discrete, and you don&#39;t have control over the sql=
 itself.</div></div><div><br></div><div>Steve</div></div></div></div>

--000000000000bae952061328a9c3--