launch background tasks, scheduled jobs

[setop]

What are you building with SQLPage ?

I'm rebuilding this webapp : https://instances.mobilizon.org/

It is currently in Nodejs but I'm not happy with it. I know it's gonna be better (according to my criteria :)) in sqlpage.

What is your problem ?

This webapp displays some statistics that are collected on a daily basis. I need to write a cronjob.

What are you currently doing ?

I plan to use an external tool (crond + python).

Describe the solution you'd like

I need a way to launch background tasks within sqlpage, that is, tasks that are not par of a request/response cycle.
I need to be able to schedule these task on regular basis, that is, every such period of time or at this time of the day. A way to describe the scheduling could be cron syntax.

In addiction to scheduled job, the solution could allow to launch one off background tasks triggered by a user request but for which the response will not wait for the task to end (like text indexing, image processing, send email, etc). In that case, the response to the request could be a job id.

Describe alternatives you've considered

Use an external crond and curl a private URL of the webapp. This is not convenient as a request/response should be quick (<10s) but a job can be long.

[lovasoa]

Hello, and thank you for getting in touch !

A cron system is sensitive, complex, and needs to be reliable. Decoupling it from your web server (in this case, sqlpage) is the right move. Using crond (or systemd timers) to invoke a local HTTP endpoint is clean and manageable. You’ll still write your data processing code in sql, and leverage OS-level timer reliability, timezone management, persistence, etc.

Long-running HTTP calls over the internet can be brittle, but since you'll be making the requests locally, the reliability concern is probably minimal.

For one-off heavy jobs, I think the best design is to have a simple job queue table in your db, where jobs are added by SQLPage, but picked up by external workers. Tasks that involve heavy processing probably won't be written in sql anyway. I have a sqlpage app at work that does this and the user experience has been quite good so far. We have a sqlpage app that populates a table when the user needs to launch a processing job, and sends a postgres NOTIFY, which is picked up by a python worker that pops jobs from the table, and populates another table with the results.

[kryskool]

Hi @setop

Like @lovasoa said, you can use NOTIFY / LISTEN in PostgreSQL to do this (NOTIFY can be send by SQLPage page or cron with command psql:

• NOTIFY 'task_1', 'My text, can be a JSON string';
• SELECT pg_notify('task_1', 'My text, can be a JSON string');

If you want to use python, you can check the project pg-notify-webhook

Regards,