Create Cancel function in sqlq

mergestat / sqlq

sqlq is a SQL-backed, persistent, job queuing solution.

MIT License

2 stars 0 forks source link

Create Cancel function in sqlq #28

Closed ramiroJCB closed 1 year ago

ramiroJCB commented 1 year ago

We need to create a way for a job cancellation , we should create a stop signal for it.

The requeriment is displayed here https://www.notion.so/mergestat/Infra-for-Background-Work-6b02aa61a13d4c6aaff9f392bae04b06

amenowanna commented 1 year ago

I don't think this is as simple as a UDF as it will need to be consumed by the worker itself. Can @ramiroJCB and @riyaz-ali please update this if that is correct

riyaz-ali commented 1 year ago

Agreed. Any sort of cancellation will require support from the runtime (and TBH, I'm not sure if we'll need a schema change or UDF for it; but let's keep that option as well). There are many ways (LISTEN/NOTIFY or polling) in which this can be done and we would probably need to research this more before implementing any.

For the time being, I don't think cancellation support is an immediate priority and this issue can be pushed back into the backlog. WDYS @ramiroJCB ?

ramiroJCB commented 1 year ago

@riyaz-ali we decided to move foward with this ticket over this week .This will help me to get more used to the sqlq code and patterns .In the research I made to make this possible we would need to exposure a method for the user to use and for that we will need to use Job returned by the enqueue function and use that ID, store it probably in our mergestat tables to then be used to find and try to cancel the job ad-hoc. My suggestions here are to change the ID of the job to UUID because it makes more sense and discuss the tables we need create in our mergestat schema to support this job cancelation feature

@patrickdevivo @amenowanna what do you guys think ?

riyaz-ali commented 1 year ago

How do you plan on "sending the cancellation signal" to the running job process? Is it going to be poll-based or LISTEN/NOTIFY-based?

+1 on switching to uuid

ramiroJCB commented 1 year ago

@patrickdevivo @amenowanna this morning Riyaz and I had a meeting around the flow and methods we could use for cancellation.We ended up agreeing on the diagram showed below but we still need to decide which approach we gonna take to cancel the job either polling or notify. We are gonna spike on each methods inside the sqlq project I will spike on the polling and Riyas will spike on the notify.

For the polling strategy we will use a udf that changes the current status of a job that is not completed to "cancelling" , then we need to create a another routine/channel to monitor the jobs in search of jobs with cancelling status to cancel their context and change it status to cancelled ,after that we will ad another if case in elsewhere to check whether the job have been cancelled and exit the whole job routine .
For the notify we will make use of the notify features of progres https://www.postgresql.org/docs/current/sql-notify.html

amenowanna commented 1 year ago

@ramiroJCB @riyaz-ali for now let's not spike on two fronts. Let's move forward with completing (not just spiking) the polling mechanism.