Implement cronjob system #22

New issue

Closed

opened 2020-11-10 06:31:10 +00:00 by dxs · 3 comments

dxs commented

2020-11-10 06:31:10 +00:00

Owner

Some sort of a system that uses the database to orchestrate running jobs on a regular interval without executing more than necessary.

dxs commented

2020-11-13 03:34:43 +00:00

Author

Owner

Functions that need this:

Bans
Updating empty metadata
Retrieving followers and subscribers

Functions that need this: - Bans - Updating empty metadata - Retrieving followers and subscribers

dxs commented

2020-11-13 04:42:30 +00:00

Author

Owner

CREATE TABLE cron (
    id integer GENERATED ALWAYS AS IDENTITY,
    name varchar NOT NULL, -- Application-recognizable name for the job
    runinterval interval, -- Duration between runs
    lastSuccess timestamptz -- Last successful run - only gets updated if run is successful
);

BEGIN; -- Transaction necessary for locking
SELECT * FROM cron
  WHERE (lastSuccess + runInterval) < now() -- Next run should be before now
  FOR UPDATE SKIP LOCKED LIMIT 1; -- Lock one not-already-locked row
-- If no rows returned by select, either no pending jobs or all pending jobs are already being handled. Either way, nothing to do, so application subroutine should exit
COMMIT;

``` CREATE TABLE cron ( id integer GENERATED ALWAYS AS IDENTITY, name varchar NOT NULL, -- Application-recognizable name for the job runinterval interval, -- Duration between runs lastSuccess timestamptz -- Last successful run - only gets updated if run is successful ); BEGIN; -- Transaction necessary for locking SELECT * FROM cron WHERE (lastSuccess + runInterval) < now() -- Next run should be before now FOR UPDATE SKIP LOCKED LIMIT 1; -- Lock one not-already-locked row -- If no rows returned by select, either no pending jobs or all pending jobs are already being handled. Either way, nothing to do, so application subroutine should exit COMMIT; ```

dxs added the

feature

label 2020-11-28 18:21:34 +00:00

dxs added this to the v1.0 milestone 2020-11-28 18:21:37 +00:00

dxs commented

2020-11-28 18:31:46 +00:00

Author

Owner

Updated version:

CREATE TABLE cron (
    id integer GENERATED ALWAYS AS IDENTITY,
    name varchar NOT NULL, -- Application-recognizable name for the job
    runinterval interval, -- Duration between runs
    lastSuccess timestamptz -- Last successful run - only gets updated if run is successful
);

BEGIN; -- Transaction necessary for locking
SELECT * FROM cron
  WHERE (lastSuccess + runInterval) < now() -- Next run should be before now
  AND name = 'jobName' -- Application will iterate through jobs it knows about, specifying job name in query
  FOR UPDATE SKIP LOCKED; -- Lock row, do not include rows currently locked
-- If no rows returned by select, either no pending jobs or all pending jobs are already being handled. Either way, nothing to do, so application subroutine should exit
COMMIT;

Changes from app saying "give me a job to do" to "does this job need done?". This way, the application will never get a job it doesn't know how to handle, and will guarantee all jobs that need to get run do so in one cron cycle.

Updated version: ``` CREATE TABLE cron ( id integer GENERATED ALWAYS AS IDENTITY, name varchar NOT NULL, -- Application-recognizable name for the job runinterval interval, -- Duration between runs lastSuccess timestamptz -- Last successful run - only gets updated if run is successful ); BEGIN; -- Transaction necessary for locking SELECT * FROM cron WHERE (lastSuccess + runInterval) < now() -- Next run should be before now AND name = 'jobName' -- Application will iterate through jobs it knows about, specifying job name in query FOR UPDATE SKIP LOCKED; -- Lock row, do not include rows currently locked -- If no rows returned by select, either no pending jobs or all pending jobs are already being handled. Either way, nothing to do, so application subroutine should exit COMMIT; ``` Changes from app saying "give me a job to do" to "does this job need done?". This way, the application will never get a job it doesn't know how to handle, and will guarantee all jobs that need to get run do so in one cron cycle.

dxs referenced this issue

2020-11-28 18:35:41 +00:00

Allow manual triggering by streamer of streamer-specific cronjobs #25

dxs referenced this issue from a commit

2020-11-29 06:27:10 +00:00

Implement cron

dxs closed this issue

2020-11-29 06:27:35 +00:00

dxs referenced this issue from a commit

2022-01-06 04:41:26 +00:00

Implement cron