Re: How to know if all insertions are finished

>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 PM Steve Midgley <science@misuse.org> wrote:

>
>
> On Fri, Mar 8, 2024 at 4:01 AM intmail01 <intmail01@gmail.com> wrote:
>
>> 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 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 amount
>    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, and
> you don't have control over the sql itself.
>
> Steve
>