Change revisions: GET /api/rev and kdb_rev()

A cheap number per table and per symbol that changes when committed rows change, so a chart polls it and reads its candles again only when they changed.

• Category: HTTP API
• Keywords: revision, revisions, rev, GET /api/rev, api/rev, kdb_rev, candle rev, candle-rev, change signal, did it change, poll, polling, chart refresh, table_rev, boot, rev column, per-symbol revision
• See also: Performance tips (result-cache), Pinned queries, Run SQL over HTTP (/exec, /exp), CREATE TABLE

A chart that shows NABIL's candles does not need to read them every few seconds. It can ask for NABIL's revision instead, and read the candles again only when the revision changed.

• A revision is a short text such as a67f6ff8e720.1532. Compare it for equality only; the number is not a count and not a time.
• It changes after every committed change of rows holding that value: INSERT, UPDATE, DELETE, POST /write, POST /imp, a trigger, a CALL.
• A change inside an open transaction does not move it. It moves at COMMIT. A rollback may move it too; the rows are then the same as before, and a read again simply gives them.
• Another value's writes never move it: a new NEPSE candle leaves NABIL's revision alone.
• A restart changes every revision (the part before the dot is new).
• It never waits for the database lock: it answers in well under a millisecond, even while a long CALL or bulk load writes.

Which column has revisions

Each table keeps revisions for the values of one column, when it has one of these:

A table without a rev column still works: its values then change together, whenever any row of the table changes.

Revisions per value are for columns with up to 10,000 values, such as the symbols of a market. They live in memory, outside --memory. When a table's rev column gets more values than that (a SYMBOL primary key of customer ids, say), the table drops its list and goes on as if it had no rev column: every write then moves every value's revision. rev_values in GET /api/stats counts the values kept.

The same values decide what the result cache keeps: a cached WHERE symbol = 'NABIL' result stays valid while only other symbols are written.

GET /api/rev

Syntax:

GET /api/rev?table=<name>&symbol=<value>[&symbol=<value> ...][&column=<name>][&db=<name>]

The answer:

It needs read rights on the table: role read on the database, or GRANT SELECT on the table. An unknown table is a 404.

HTTP: The revisions of two symbols

Setup:

CREATE TABLE candles (time TIMESTAMP, symbol SYMBOL, close DOUBLE) TIMESERIES(time)
  PARTITION BY YEAR, SYMBOL(symbol) SORT BY (symbol, time) DEDUP KEY (symbol, time);
INSERT INTO candles VALUES ('2026-09-24T10:00:00Z', 'NABIL', 1510), ('2026-09-24T10:00:00Z', 'NEPSE', 2750);

GET /api/rev?table=candles&symbol=NABIL&symbol=NEPSE

Response:

HTTP 200 (application/json)

{
  "database": "demo",
  "table": "candles",
  "column": "symbol",
  "boot": "52589fb436f3",
  "table_rev": "52589fb436f3.0",
  "revs": {"NABIL": "52589fb436f3.0", "NEPSE": "52589fb436f3.0"}
}

HTTP: Asking by the wrong column says which one has revisions

Setup:

CREATE TABLE candles (time TIMESTAMP, symbol SYMBOL, close DOUBLE) TIMESERIES(time)
  PARTITION BY YEAR, SYMBOL(symbol) SORT BY (symbol, time) DEDUP KEY (symbol, time);

GET /api/rev?table=candles&column=close&symbol=NABIL

Response:

HTTP 400 (application/json)

{"error": "table 'candles' keeps revisions per value of column 'symbol', not 'close'"}

A page that polls:

let seen = null;
setInterval(async () => {
  const r = await (await fetch('/api/rev?table=candles&symbol=NABIL')).json();
  if (r.revs.NABIL !== seen) {
    seen = r.revs.NABIL;
    drawChart(await readCandles('NABIL'));  // only when NABIL changed
  }
}, 2000);

kdb_rev

Syntax:

kdb_rev('<table>')
kdb_rev('<table>', '<value>')

The same revisions in SQL, for clients that only speak the PostgreSQL protocol. With one argument: the table's revision (table_rev). With two: the revision of that value of the rev column. It gives the same text as GET /api/rev, and needs the same right: reading the table (a user whose SELECT on it was revoked gets an error). Unlike the route it runs as a statement, so it waits for the database lock like any SELECT. A NULL argument gives NULL; an unknown table is an error.

Example: A symbol's revision and the table's (sample data: sample)

SELECT length(kdb_rev('ochl_minutes', 'CITY')) > 0 AS has_rev,
       kdb_rev('ochl_minutes', 'CITY') = kdb_rev('ochl_minutes', 'CITY') AS stable;

Result:

(1 row)