Timers

Let the server run a procedure, a function or any SQL by itself at set times, so a table stays up to date.

• Category: Stored procedures
• Keywords: timer, timers, schedule, scheduled job, job, cron, every, hourly, daily, weekdays, sun-thu, mon-fri, trading week, trading days, holidays, trading nepse 15:05, catch up, skipped, monthly, retention, keep data for, run on time, back to back, timer_workers, --timer-workers, KDB_TIMER_WORKERS, GET /api/timers, POST /api/timers, POST /api/timers/<name>/run, _timers
• See also: Stored procedures, Saved functions, Triggers, Trading calendars, Backups on a schedule, HTTP API overview

A timer is saved SQL that the server runs by itself at set times, inside one database. Most timers call a procedure, so a table of totals, candles or reports is kept up to date without a cron job outside KDB.

• Timers run while kdb serve runs. They are saved in the table _timers of the hidden _system database, or of the served database when kdb serve runs on one folder, so they survive restarts and are part of every backup.
• A timer runs with the rights of the user who saved it last. Saved with the token, it may do everything.
• Who may do what. Reading the list needs read on the database. Saving, running, turning off and deleting a timer need admin on that database, as CREATE TRIGGER and CREATE PROCEDURE do, because a timer is saved code that runs later by itself.
• Audited. Every save, Run now, turn on or off and delete is one row in the audit log with the event timer, the user, the database and the timer's name.
• A timer works inside its own database: USE, CREATE DATABASE, DROP DATABASE, SET and user statements are refused.

Schedules

Times are the local time of the computer the server runs on, or the zone of kdb serve --timezone Asia/Kathmandu when it is set (a container runs on UTC, so set it there: daily 15:10 then runs at 15:10 Nepal time).

• mon-fri 11:00 is the same as weekdays 11:00.
• A trading timer needs its calendar when it is saved (there is no calendar 'nepse' in database ... otherwise). It follows the calendar as it changes: a holiday added in the morning is skipped that afternoon. The day is the date of the run on the timer clock above.
• When the calendar is dropped, the timer is due every day and each run fails with calendar 'nepse' does not exist, so it never stops quietly. Make the calendar again, or give the timer another schedule.

How a run works

• One at a time. A timer never runs twice at once. When it is still running at its next due time, that due time runs as soon as the run ends: a catch-up run (caught_up). More due times during the same run get no run of their own.
• No back-to-back runs. A catch-up runs only when the run before it took less than one interval and ended before the next regular time - for example a run that started late because no worker was free. A timer that takes longer than its interval (an every 10s that takes 11 s) keeps its regular times (0 s, 20 s, 40 s ...) instead of running all the time, and each due time in between is skipped with the reason.
• Nothing is dropped quietly. A due time that gets no run is counted in skipped, with the time (last_skip_ms) and the reason (last_skip_reason), and written to the server log and the activity log. This covers a run that is still going, a computer that was busy or asleep, a clock set forward, and a server that was stopped: after a start, the due times missed while it was down are counted, not run.
• One slow timer does not make others late. Two workers are always there; when both are busy and another timer is due, a worker is added (up to three), so the other timer starts on time. Each running timer is one more writer at the database, so the limit is small. Change it with timer_workers in kdb.conf, kdb serve --timer-workers N or KDB_TIMER_WORKERS (1 to 64; the flag wins, then kdb.conf, then the environment). 1 runs one timer at a time.
• Clock changes. The server sleeps on a clock that nobody sets, and looks at least once a second. When the computer's clock is set back, every next run is planned again; set forward, the due times jumped over are counted as skipped.
• Each statement commits by itself. To make all of it one step, put the SQL in a procedure: CALL runs a procedure as one transaction.
• A transaction left open is rolled back at the end of the run (for example BEGIN; INSERT ... without COMMIT).
• Time limit: a run is stopped after 10 minutes.
• On time on a busy database. A run's writes go into the database ahead of the requests that wait there (a checkpoint waiting for a bulk load, readers that keep coming). A run still waits for what is running right then: a long read, a schema change, or a checkpoint that is writing files. last_late_ms and max_late_ms show how late runs were.
• Errors are kept: the last error, how many runs failed, and the server log gets one line per failed run.

In the console

Open Timers in the web console. The list shows each timer's schedule, the next and last run, the result and the last error. New timer saves one, Run now runs one straight away, and the switch turns one off or on.

The Monitor (the flyout on the right) has a Timers block with the timers of every database you may read, trouble first: when each last ran and whether it failed, how many seconds late it started, how many runs failed in the last 24 hours, and the last error. It comes from timers in GET /api/stats, which holds the same fields as below.

Keep data for... is for old data: pick the tables and how long to keep (6 months, 1 year, ...). It either sets a TTL on each table, so KDB drops the old partitions by itself, or saves one monthly timer that runs ALTER TABLE ... DROP PARTITION OLDER THAN ... for every table on the day and time you choose. Both drop whole partitions and never single rows.

GET /api/timers

Syntax:

GET /api/timers[?db=<name>]

The timers of one database (the default database without db=), and how their runs went. Needs read rights on the database.

now_ms at the top is the server's clock, to show "in 3 minutes" correctly.

POST /api/timers

Syntax:

POST /api/timers[?db=<name>]
Content-Type: application/json

{"name": "...", "schedule": "...", "sql": "...", "enabled": true}

Creates a timer, or replaces the one with that name. Needs admin rights (a timer is saved code that later runs with the rights of whoever saved it, like a trigger), and is written to the audit log. Returns the timer. A bad name, schedule or SQL is a 400 that says what is wrong.

HTTP: A timer that keeps the shop totals up to date every 5 minutes

Setup:

CREATE TABLE sales (time TIMESTAMP, shop STRING, amount DOUBLE) TIMESTAMP(time);
CREATE TABLE shop_totals (shop STRING PRIMARY KEY, amount DOUBLE, updated TIMESTAMP);
INSERT INTO sales VALUES (now(), 'north', 120.5), (now(), 'south', 80), (now(), 'north', 42);
CREATE PROCEDURE refresh_shop_totals() AS BEGIN
  FOR s IN (SELECT shop, sum(amount) AS amount FROM sales GROUP BY shop) DO
    INSERT OR REPLACE INTO shop_totals VALUES (s.shop, s.amount, now());
  END FOR;
END;

POST /api/timers
Content-Type: application/json

{"name": "shop_totals", "schedule": "every 5m", "sql": "CALL refresh_shop_totals()"}
###
POST /api/timers/shop_totals/run
###
POST /exec?compat=0

SELECT shop, amount FROM shop_totals ORDER BY shop

Response:

POST /api/timers

HTTP 200 (application/json)

{
  "name": "shop_totals",
  "database": "demo",
  "schedule": "every 5m",
  "sql": "CALL refresh_shop_totals()",
  "enabled": true,
  "created_ms": 1792647871200,
  "updated_ms": 1792647871200,
  "created_by": "",
  "next_run_ms": 1792647900000,
  "last_run_ms": 0,
  "last_due_ms": 0,
  "last_late_ms": 0,
  "max_late_ms": 0,
  "last_ok": null,
  "last_error": "",
  "last_took_ms": 0,
  "last_rows": 0,
  "runs": 0,
  "failures": 0,
  "failures_24h": 0,
  "skipped": 0,
  "last_skip_ms": 0,
  "last_skip_reason": "",
  "caught_up": 0,
  "running": false
}

POST /api/timers/shop_totals/run

HTTP 200 (application/json)

{"name": "shop_totals", "finished": true, "ok": true, "error": "", "took_ms": 2, "rows": 2}

POST /exec?compat=0

HTTP 200 (application/json)

{
  "query": "SELECT shop, amount FROM shop_totals ORDER BY shop",
  "results": [
    {
      "statement": "SELECT shop, amount FROM shop_totals ORDER BY shop",
      "columns": [
        {"name": "shop", "type": "STRING"},
        {"name": "amount", "type": "DOUBLE"}
      ],
      "dataset": [
        ["north", 162.5],
        ["south", 80]
      ],
      "count": 2,
      "truncated": false,
      "rows_affected": 0,
      "message": "",
      "timings": {"parse_us": 38, "execute_us": 76}
    }
  ],
  "timings": {"parse_us": 38, "execute_us": 76}
}

HTTP: A schedule that is not valid

POST /api/timers
Content-Type: application/json

{"name": "bad_one", "schedule": "every day", "sql": "SELECT 1"}

Response:

HTTP 400 (application/json)

{
  "error": "bad \"every\" schedule 'every day'; use a schedule like \"every 10s\", \"every 5m\", \"every 2h\", \"hourly\", \"hourly :15\", \"daily 16:00\", \"daily 02:00,13:30\", \"weekdays 11:00\", \"sun-thu 11:00\", \"mon,wed,fri 09:30\", \"monthly 1 02:00\" or \"trading nepse 15:05\" (the trading days of CREATE CALENDAR nepse)"
}

Run, turn off, delete

Syntax:

POST /api/timers/<name>/run[?db=<name>]
POST /api/timers/<name>/enable[?db=<name>]     {"enabled": false}
POST /api/timers/<name>/delete[?db=<name>]

An unknown timer is a 404. All three need admin rights and POST, and each is written to the audit log.

HTTP: Turn a timer off, then delete it

POST /api/timers
Content-Type: application/json

{"name": "nightly", "schedule": "daily 02:00", "sql": "SELECT 1"}
###
POST /api/timers/nightly/enable
Content-Type: application/json

{"enabled": false}
###
POST /api/timers/nightly/delete
###
GET /api/timers

Response:

POST /api/timers

HTTP 200 (application/json)

{
  "name": "nightly",
  "database": "demo",
  "schedule": "daily 02:00",
  "sql": "SELECT 1",
  "enabled": true,
  "created_ms": 1792647871158,
  "updated_ms": 1792647871158,
  "created_by": "",
  "next_run_ms": 1792700100000,
  "last_run_ms": 0,
  "last_due_ms": 0,
  "last_late_ms": 0,
  "max_late_ms": 0,
  "last_ok": null,
  "last_error": "",
  "last_took_ms": 0,
  "last_rows": 0,
  "runs": 0,
  "failures": 0,
  "failures_24h": 0,
  "skipped": 0,
  "last_skip_ms": 0,
  "last_skip_reason": "",
  "caught_up": 0,
  "running": false
}

POST /api/timers/nightly/enable

HTTP 200 (application/json)

{
  "name": "nightly",
  "database": "demo",
  "schedule": "daily 02:00",
  "sql": "SELECT 1",
  "enabled": false,
  "created_ms": 1792647871158,
  "updated_ms": 1792647871196,
  "created_by": "",
  "next_run_ms": 0,
  "last_run_ms": 0,
  "last_due_ms": 0,
  "last_late_ms": 0,
  "max_late_ms": 0,
  "last_ok": null,
  "last_error": "",
  "last_took_ms": 0,
  "last_rows": 0,
  "runs": 0,
  "failures": 0,
  "failures_24h": 0,
  "skipped": 0,
  "last_skip_ms": 0,
  "last_skip_reason": "",
  "caught_up": 0,
  "running": false
}

POST /api/timers/nightly/delete

HTTP 200 (application/json)

{"deleted": true}

GET /api/timers

HTTP 200 (application/json)

{
  "now_ms": 1792647871223,
  "timers": []
}

Good to know

• DROP DATABASE deletes the database's timers too, so a new database with the same name starts with none. A timer left over from an older KDB (or a database folder removed by hand) can still be deleted: POST /api/timers/<name>/delete?db=<name> works while the database is gone, for a server admin.
• A timer that runs often writes its last result back to _timers only when the result changes, or once a minute.
• For alerts, /metrics has kdb_timer_failures_total and kdb_timer_late_seconds_max for all timers, and per timer kdb_timer_failures_24h{database,timer} and kdb_timer_last_late_seconds{database,timer}.
• For backups at set times use the backup schedule, not a timer.