SQL Workers and $waitforcomplete property
Doug Easterbrook
doug at artsman.com
Tue Feb 2 08:58:33 EST 2016
hi Andrea:
In the case of $waitforcomplete=kfalse — it is 100% expected that you get no errors back to omnis — and the reason (for fire and forget statements) is that you are telling the world that you do not want any response back, regardless of success or failure.
we did a long presentation on the worker design pattern at Euromnis when the concept was introduced. There are some rules about workers that you hae to get your head around — and once you know how to work with them, they are great for a number of things. Some of the rules are:
1) you can dispatch a worker to do something, but you must give him a complete unit of work.
2) you cannot interfere with the work being done in any way
3) you can monitor that the worker is running
4) you can tell a worker to stop doing it task
5) you can tell a worker to link its output to the input of another worker
This applies to any synchronous task that you are trying to make asynchronous. So it applies to sending emails, waiting for pop servers, sending sql to the database or retrieving row, running a posting process for accouting, etc.
so to explain all that a bit more.
if you dispatch the worker to do something, it must be a complete unit of work. This simply means that the worker must know all about the task it is doing and have all the code to do the task. If the task is quite complex, needs user interaction, etc — then it is far to big to e a worker.
Couple this with ‘you cannot interfere with the work being done in any way’. it means once the worker is running, it cannot call back to omnis and process individual rows and do some decision making based on the row.
what can you do out of that. Well, a lot. In our app, i’d say about 40% of the SQL is background workers because we do a lot of time loading data into lists in windows. If you have to process a list such as add column A and B together or put a style character into the list to display the row or text in a different colour than another row, you cannot do that in omnis while the data is loading… so have to do it in SQL
so we use things in the table class to make our SQl look like
Select var1,var1,var3,var4, (A+B) as varAandB, con(style(kescicon, ….) as icon, …… etc
that way, the server does all the work and omnis is not required as part of the return.
on inserts for web logs, we do things like build a multi insert statement that looks like
insert into table (A,B,C) values (1,2,3),(4,5,6),(7,8,9)
and fire that to the background worker to get multiple rows of data inserted. we look for the result, just to make sure it finished, and its really quite reliable.
Same can be said of making an SMTP email and firing it off
in a twist on the design pattern, we have built some omnis worker jobs using the database as the communication mechanism. So we insert some long running jobs (a report can be considered a long running job). but as long as you have the query parameters, name of the report, etc, then you can stick a request for that report into the database. Then some omnis server process can harvest those with the sole result of being a report binary put into the database.. when done, the user gets notified (you can use listen notify in postgres, or poll for the result)
but in all cases, the job must run without interference by another process that is making decisions for it. The job must have enough knowledge to do the work from beginning to end without interference.
what about ‘you can monitor that the job is running’ easy enough, you can see if it is running, or done. but thats it. you can give it no further instructions while it is running.
other that — you can tell it to stop. i.e. you don’t want it to finish, if you can catch it in time. in SQL think of this as starting a fetch of many rows of data — you can tell it to stop… and it should not give you the results back. or in the case of running a report — tell the worker that the report is no longer required, and it should stop making it.
and the final concept is that you can link the output of one worker to the input of another. Meaning, if you have a job that gets a bunch of raw data, then the output format can be designed to be the input format of another job. if it the first job is successful, it could thne kick off the second job.
example: sql to get a bunch of data for a report. The $completed of the sql could pass the results list to a worker (say as a list or json) that processes the list and turns it into an HTML report.
thus worker type A gets the list. if successful, creates a job where worker type B knows how to process the list/json and turn it into HTML output. But worker B never gets started if worker A does not finish.
With this worker notion really have to separate functions and get down to the granularity of the function.
Here’s an EXAMPLE of BAD DESIGN we used to do. We use oWrite to merge data into an email and send it. That sounds like one function and its what we used to do before we fully embraced the worker principle. The process is quite simple:
— read data from database
Repeat:
— run through owrite
— extract from owrite
— send it via SMTP
well, turns out we weren’t thinking right. There are two workers in the process, one of which (SMTP) is synchronous. Things kept failing and were not restartable. By failing, some smtp servers have a mind of their own and could reject emails. If they rejected emails, then the whole process could fail.
whats the right way to do it.
Worker #1 (job function make emails that can be send)
— read data from database
Repeat:
— run through owrite
— extract from owrite
— store ‘pending’ email in database
worker #2 (job function - simply send emails)
Repeat:
— get pending email from database
— send it via SMTP
Since we separated the functions, we can now terminate either job. we can restart either job where it died. we can monitor either job. and the output of job #1 (make an email) is ideally suited for the second job (send an email).
It can let users make the emails in the middle of the day and let the email sender queue them up to send when a customer is mostly likely to open it.
Sorry to be a bit longwinded …. but worker processes are truly great. But it does take a little wrapping your head around them to not abuse your workers.
funny … does sound like the real workplace — absolutely. workers like to be told what to do. like to go away and do it without interference. Can be told to stop and can be told to deliver their output to another worker.
But you can’t change what a worker is doing in the middle of the job. you can tell them to stop, and then give them another task. Semantic, perhaps.
hope that helps.
Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 536-1205 Fax (403) 536-1210
> On Feb 2, 2016, at 3:32 AM, Andrea Zen <a.zen at athesiavr.it> wrote:
>
> Thanks, now that's clearer.
> I've just tried launching in a loop 50 insert statements on the same table using one worker with $waitforcomplete = kFalse...most of the times it works and executes all of them, sometimes some inserts are missing, without any errors on the worker...strange.
>
> The 5000/100 select statements was just a test I was doing, nothing real...
> I have a real case where I thought I could use the workers, but now I think this would not improve performances, since I have some long queries to launch but among them there are also other logical operations on the resulting lists, plus some queries need the results of the previous ones.
>
> Anyway workers seem a good choice in order to load lists without any further process or to do batch operations.
>
> Dr. Andrea Zen
More information about the omnisdev-en
mailing list