public inbox for [email protected]help / color / mirror / Atom feed
How to know if all insertions are finished 4+ messages / 3 participants [nested] [flat]
* How to know if all insertions are finished @ 2024-03-08 12:01 intmail01 <[email protected]> 2024-03-08 16:26 ` Re: How to know if all insertions are finished Steve Midgley <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: intmail01 @ 2024-03-08 12:01 UTC (permalink / raw) To: [email protected] 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. ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to know if all insertions are finished 2024-03-08 12:01 How to know if all insertions are finished intmail01 <[email protected]> @ 2024-03-08 16:26 ` Steve Midgley <[email protected]> 2024-03-08 16:52 ` How to know if all insertions are finished Wetmore, Matthew (CTR) <[email protected]> 2024-03-09 17:11 ` Re: How to know if all insertions are finished intmail01 <[email protected]> 0 siblings, 2 replies; 4+ messages in thread From: Steve Midgley @ 2024-03-08 16:26 UTC (permalink / raw) To: intmail01 <[email protected]>; +Cc: [email protected] On Fri, Mar 8, 2024 at 4:01 AM intmail01 <[email protected]> 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* How to know if all insertions are finished 2024-03-08 12:01 How to know if all insertions are finished intmail01 <[email protected]> 2024-03-08 16:26 ` Re: How to know if all insertions are finished Steve Midgley <[email protected]> @ 2024-03-08 16:52 ` Wetmore, Matthew (CTR) <[email protected]> 1 sibling, 0 replies; 4+ messages in thread From: Wetmore, Matthew (CTR) @ 2024-03-08 16:52 UTC (permalink / raw) To: Steve Midgley <[email protected]>; intmail01 <[email protected]>; +Cc: [email protected] <[email protected]> Begin work Select count(*) prior to insert. Insert from file Select count post insert If new count matches old count + new rows from insert file Commit/rollback From: Steve Midgley <[email protected]> Sent: Friday, March 8, 2024 8:26 AM To: intmail01 <[email protected]> Cc: [email protected] Subject: [EXTERNAL] Re: How to know if all insertions are finished On Fri, Mar 8, 2024 at 4:01 AM intmail01 <[email protected]<mailto:[email protected]>> 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to know if all insertions are finished 2024-03-08 12:01 How to know if all insertions are finished intmail01 <[email protected]> 2024-03-08 16:26 ` Re: How to know if all insertions are finished Steve Midgley <[email protected]> @ 2024-03-09 17:11 ` intmail01 <[email protected]> 1 sibling, 0 replies; 4+ messages in thread From: intmail01 @ 2024-03-09 17:11 UTC (permalink / raw) To: Steve Midgley <[email protected]>; +Cc: [email protected] >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 <[email protected]> wrote: > > > On Fri, Mar 8, 2024 at 4:01 AM intmail01 <[email protected]> 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 > ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-03-09 17:11 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-03-08 12:01 How to know if all insertions are finished intmail01 <[email protected]> 2024-03-08 16:26 ` Steve Midgley <[email protected]> 2024-03-08 16:52 ` Wetmore, Matthew (CTR) <[email protected]> 2024-03-09 17:11 ` intmail01 <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox