CREATE JOB
A job is a catalog object that runs one SQL statement on a schedule, inside the server. It lives in a schema next to tables and views, is persisted with the database, and keeps running across restarts until it is suspended or dropped.
Examples
Roll up yesterday's events every day at 02:00 UTC, and run it once right away:
CREATE JOB doc_rollup EVERY 1 DAY OFFSET 2 HOURS AS INSERT INTO doc_daily SELECT kind, count(*) FROM doc_events GROUP BY kind;
EXECUTE JOB doc_rollup;
SELECT kind, events FROM doc_daily ORDER BY kind;kind | events --------+-------- login | 2 logout | 1
Syntax
CREATE [ OR REPLACE ] JOB [ IF NOT EXISTS ] name
{ EVERY interval [ OFFSET interval ] [ RANDOMIZE FOR interval ] [ CONCURRENT ]
| AFTER interval [ RANDOMIZE FOR interval ] }
[ SUSPENDED ]
AS statement
ALTER JOB [ IF EXISTS ] name SUSPEND
ALTER JOB [ IF EXISTS ] name RESUME
ALTER JOB [ IF EXISTS ] name SET SCHEDULE
{ EVERY interval [ OFFSET interval ] [ RANDOMIZE FOR interval ] [ CONCURRENT ]
| AFTER interval [ RANDOMIZE FOR interval ] }
ALTER JOB [ IF EXISTS ] name RENAME TO new_name
ALTER JOB name OWNER TO role
EXECUTE JOB name
DROP JOB [ IF EXISTS ] name [ CASCADE | RESTRICT ]
interval is an interval literal (INTERVAL '15 minutes', INTERVAL (2 * 3) MINUTE), the short form <number> <unit>, repeatable (30 SECONDS, 1 HOUR, 1 MONTH, 1 WEEK 2 DAYS, 5 DAYS 2 HOURS), or any constant expression in parentheses ((INTERVAL '1 hour' * 2)); it is evaluated once, when the statement runs. The body is any single statement except a transaction statement; it cannot use parameters. It is parsed when the job is created, stored as normalized SQL text (the body column of sdb_jobs()), and bound again on every run, so it always sees the current definition of the objects it references.
EXECUTE JOB name is shorthand for CALL execute_job('name').
Schedules
The two schedule kinds follow ClickHouse's refreshable materialized views.
EVERY interval [OFFSET offset]runs on a fixed calendar grid in UTC. An interval of days, weeks or time counts from Monday 1969-12-29 at midnight and a month-based interval from January 1970, as in ClickHouse:EVERY 1 HOURruns at the top of every hour,EVERY 15 MINUTESat :00, :15, :30 and :45,EVERY 1 DAYat midnight,EVERY 1 WEEKon Monday midnight,EVERY 1 MONTHon the first of the month.OFFSETshifts the grid:EVERY 1 DAY OFFSET 2 HOURSruns at 02:00. The offset must be shorter than the interval, and a month-based interval cannot be combined with days or time.AFTER intervalrunsintervalafter the previous run finished, so runs never overlap and a slow run pushes the next one back. The first run happensintervalafter the job is created or the server starts.AFTERtakes noOFFSET.
EVERY 1 DAY -- every day at midnight
EVERY 1 MONTH -- on the 1st of every month, at midnight
EVERY 1 MONTH OFFSET 5 DAYS 2 HOURS -- on the 6th of every month, at 02:00
EVERY 2 WEEKS OFFSET 5 DAYS 15 HOURS 10 MINUTES -- every other Saturday, at 15:10
EVERY 30 MINUTES -- at 00:00, 00:30, 01:00, 01:30, ...
AFTER 30 MINUTES -- 30 minutes after the previous run finished
EVERY 1 WEEK 2 DAYS -- every 9 days: when the day number since 1969-12-29 is divisible by 9
EVERY 5 MONTHS -- when the month number since 1970-01 is divisible by 5
EVERY 1 DAY OFFSET 2 HOURS RANDOMIZE FOR 1 HOUR -- every day at a random time between 01:30 and 02:30
RANDOMIZE FOR spread moves each run by a pseudo-random amount within spread, centered on the scheduled time, so that many jobs on the same grid do not all start at once. The amount is fixed per job and per scheduled time, so next_run stays the same however often it is computed, and a manual EXECUTE JOB does not move it. The spread must be shorter than the interval.
CREATE JOB doc_every_15m EVERY INTERVAL '15 minutes' AS SELECT 1;
CREATE JOB doc_monthly EVERY 1 MONTH OFFSET 1 DAY AS SELECT 1;
CREATE JOB doc_backoff AFTER 30 SECONDS AS SELECT 1;
SELECT job_name, schedule_kind, scheduleFROM sdb_jobs() WHERE job_name IN ('doc_every_15m', 'doc_monthly', 'doc_backoff')ORDER BY job_name;job_name | schedule_kind | schedule ---------------+---------------+------------------------------------------------ doc_backoff | AFTER | AFTER INTERVAL '00:00:30' doc_every_15m | EVERY | EVERY INTERVAL '00:15:00' doc_monthly | EVERY | EVERY INTERVAL '1 mon' OFFSET INTERVAL '1 day'
A job never runs twice at the same time unless it is CONCURRENT. If an EVERY run is still going when the next grid point arrives, that tick is skipped and the job runs again at the first grid point after the run finishes. Ticks that fall while the server is down are not replayed.
CONCURRENT lets the runs of an EVERY job overlap: every grid point starts a run, even while earlier runs are still going, and EXECUTE JOB runs alongside them. Use it for bodies that are independent of each other; every run takes a session and a background thread of its own, so a body slower than its interval keeps several of them busy. AFTER jobs cannot be CONCURRENT, since each run is counted from the end of the previous one. ALTER JOB ... SET SCHEDULE turns it on or off with the rest of the schedule.
CREATE JOB doc_ping EVERY 1 MINUTE CONCURRENT AS SELECT 1;
SELECT schedule FROM sdb_jobs() WHERE job_name = 'doc_ping';
ALTER JOB doc_ping SET SCHEDULE EVERY 1 MINUTE;
SELECT schedule FROM sdb_jobs() WHERE job_name = 'doc_ping';
DROP JOB doc_ping;
DROP JOB doc_owned;
DROP JOB doc_broken;
DROP JOB doc_every_15m;
DROP JOB doc_monthly;
DROP JOB doc_backoff;
DROP DATABASE doc_jobs_db;
DROP TABLE doc_daily;
DROP TABLE doc_events;
REVOKE CREATE ON SCHEMA public FROM doc_job_owner;
DROP ROLE doc_job_owner;schedule -------------------------------------- EVERY INTERVAL '00:01:00' CONCURRENT schedule --------------------------- EVERY INTERVAL '00:01:00' # DOCS_TEST_END
Scheduled runs execute on the server's background thread pool (--background_threads), the pool that also runs index maintenance, so a long job does not hold a client session.
SUSPENDED creates the job paused.
Changing a job
ALTER JOB suspends and resumes a job, replaces its schedule, or renames it. A suspended job keeps its definition and history but is not scheduled; a run already in progress finishes normally. CREATE OR REPLACE JOB replaces the whole definition, including the body.
ALTER JOB doc_rollup SUSPEND;
SELECT suspended, next_run IS NULL AS idle FROM sdb_jobs() WHERE job_name = 'doc_rollup';
ALTER JOB doc_rollup SET SCHEDULE EVERY 6 HOURS;
ALTER JOB doc_rollup RESUME;
ALTER JOB doc_rollup RENAME TO doc_rollup_6h;
SELECT job_name, schedule, suspended FROM sdb_jobs() WHERE job_name = 'doc_rollup_6h';suspended | idle -----------+------ t | t job_name | schedule | suspended ---------------+---------------------------+----------- doc_rollup_6h | EVERY INTERVAL '06:00:00' | f
Running a job on demand
EXECUTE JOB runs the body once, synchronously, and reports the body's error if it fails. The body runs in the job's own session and transaction, exactly as a scheduled run would, so its effects commit independently of the calling transaction. It works for suspended jobs too and does not change the schedule of an EVERY job; for an AFTER job the next run is counted from the end of this one. It fails if the job is already running, unless the job is CONCURRENT.
A body can run another job with EXECUTE JOB: that job runs in a session of its own while the calling run waits for it. A chain of such calls holds at most sdb_job_max_depth runs, 16 by default: a CONCURRENT job that executes itself, directly or through other jobs, fails with Max job depth limit of 16 exceeded, and every run in the chain is recorded as failed.
Monitoring
sdb_jobs() lists the jobs visible to the session with their schedule (schedule, schedule_kind, schedule_interval, schedule_offset, schedule_randomize), their body and full CREATE JOB statement (sql), and their run state: running, next_run, last_start, last_finish, last_status (success or failed), last_error, run_count and failure_count. sdb_job_runs() returns the most recent runs of all jobs (up to sdb_job_history_size, 1024 by default), with trigger set to schedule or manual. Run state is kept in memory only: a restart empties sdb_job_runs(), resets running, the last_* columns, run_count and failure_count, and computes next_run again from the schedule. Notices and warnings that a run raises, scheduled or started with EXECUTE JOB, go to the server log as warnings prefixed with the job's name.
CREATE JOB doc_broken AFTER 1 HOUR AS INSERT INTO doc_daily VALUES ('oops', 'not a number');
EXECUTE JOB doc_broken;
SELECT job_name, run_count, failure_count, last_statusFROM sdb_jobs() WHERE job_name IN ('doc_rollup_6h', 'doc_broken') ORDER BY job_name;
SELECT DISTINCT job_name, trigger, success FROM sdb_job_runs()WHERE job_name IN ('doc_rollup', 'doc_broken') ORDER BY job_name;db error: ERROR: Could not convert string 'not a number' to INT64(varies) job_name | run_count | failure_count | last_status ---------------+-----------+---------------+------------- doc_broken | 1 | 1 | failed doc_rollup_6h | 1 | 0 | success job_name | trigger | success ------------+---------+--------- doc_broken | manual | f doc_rollup | manual | t
Security
A job runs as its owner, the role that created it, in a session of its own: the body is checked against the owner's privileges, exactly as if the owner had typed it. Creating a job needs the CREATE privilege on the schema. Only the owner (or a member of the owning role, or a superuser) can alter, drop or execute the job; ALTER JOB ... OWNER TO hands it to another role, which from then on is the identity the job runs as.
SET ROLE doc_job_owner;
CREATE JOB doc_owned AFTER 1 HOUR AS INSERT INTO doc_daily SELECT current_user, 0;
RESET ROLE;
EXECUTE JOB doc_owned;
SELECT kind FROM doc_daily WHERE events = 0;kind --------------- doc_job_owner
The job session starts with the job's schema first on the search path and reads global settings; session settings of the role that created the job do not apply.
A search path holds only top-level schemas, so a job in a nested schema (CREATE JOB outer.inner.job ...) runs with the database's default search path ("$user", public), as a new session in that database would, instead of its own schema. Qualify the names in the body of such a job (INSERT INTO outer.inner.events ...).
Jobs in other databases
A job can live in any SereneDB database, not only the current one; wherever it lives it runs as its owner, with its own schema first on the search path:
CREATE JOB doc_jobs_db.public.doc_db_job AFTER 1 HOUR AS INSERT INTO doc_session_log VALUES (1);
EXECUTE JOB doc_jobs_db.public.doc_db_job;
SELECT count(*) AS runs FROM doc_jobs_db.public.doc_session_log;
CREATE TEMPORARY JOB doc_session_job AFTER 1 HOUR AS SELECT 1; runs
------
1
db error: ERROR: (varies)Jobs are not supported by this catalog(varies)Jobs exist only in SereneDB databases, like text search dictionaries and inverted indexes: CREATE TEMPORARY JOB, and jobs in memory or in an attached DuckDB file, are rejected with Jobs are not supported by this catalog.
Dropping a job
DROP JOB removes the job. A run that is in progress is interrupted.
DROP JOB IF EXISTS doc_rollup_6h;
DROP JOB IF EXISTS doc_does_not_exist;Jobs created by the server
The reindex_interval option of an inverted index over a view is implemented as a job named after the index; see Automatic refresh.