SQL reference

This page lists the SQL that kinedb runs today. Every example in a plain sql block ran on the current build, in the order of this page, in one database. So a later example can use a table that an earlier example created. A -- expect: comment shows a part of the output.

The examples use the embedded mode of the binary, which starts no server:

kinedb --data-dir ./data -e "SHOW TABLES"

Some statements need a running server (kinedb --connect host:port, POST /sql, or a client library). The text before such an example says so. The embedded mode refuses them with requires a running server.

Data types

• INT: a 64-bit signed integer.
• FLOAT: a 64-bit floating-point number (IEEE 754).
• BOOL: true or false.
• TEXT: a UTF-8 string.
• BLOB: the manifest of a file — the list of the content blocks the file is made of, not the bytes themselves. For raw bytes in a cell, use BYTES. A file enters through INSERT … FILE, which makes the server read a path on the shard leader, or through the put_file call of a client library, which sends the bytes over the wire. See Files.
• BYTES (alias BYTEA): raw bytes. Write a value with the X'<hex>' literal or with a bytes parameter of sql(text, params). A BYTES column takes no DEFAULT and no index, and it cannot be UNIQUE. SHOW COLUMNS reports the type as Bytes.
• TIMESTAMP: microseconds since the Unix epoch.
• VECTOR: a list of numbers.
• COUNTER: a 64-bit signed integer that MERGE BRANCH merges as a sum. When the branch and main both change a counter cell of one row, the merge adds the amount of each side to the old value (see "Merge branches").
• COUNTER(FLOAT): a counter that holds a 64-bit floating-point number. The sum is a floating-point sum, so it can round: 0.1 + 0.2 gives 0.30000000000000004.
• ARRAY(<type>) (also written <type>[]): an ordered list of values of one type. See Array columns.

A COUNTER cell reads, compares, sorts, indexes and aggregates as an INT cell, and a COUNTER(FLOAT) cell as a FLOAT cell. A query result reports the column as INT or FLOAT; DESCRIBE and SHOW COLUMNS report Counter(Int) or Counter(Float).

A merge adds to a counter, so a merge could change a key or make two rows equal. So a COUNTER column cannot be the primary key or UNIQUE, and it cannot be in a UNIQUE index. A plain index on a counter is allowed.

-- expect: OK
CREATE TABLE page_views (page TEXT PRIMARY KEY, hits COUNTER, seconds COUNTER(FLOAT));
-- expect: OK
INSERT INTO page_views (page, hits, seconds) VALUES ('home', 10, 1.5);
-- expect: OK
UPDATE page_views SET hits = hits + 1 WHERE page = 'home';
-- expect: 11
SELECT hits FROM page_views WHERE page = 'home';
-- error: a counter cannot be in a UNIQUE index
CREATE UNIQUE INDEX page_views_hits ON page_views (hits);
CREATE INDEX page_views_hits ON page_views (hits);
DROP TABLE page_views;

A number literal can have a fraction and an exponent: 42, -7, 1.5, 1e300, 2.5E-3, -1.5E+10. A literal with a fraction or an exponent is a FLOAT. A FLOAT literal that does not fit in a 64-bit float, such as 1e400, is refused with an error; kinedb never stores it as infinity. A vector literal takes the same numbers, for example [1e-3, 2, -1.5E+2], and refuses a component that does not fit in a 32-bit float.

-- expect: OK
CREATE TABLE literals (id INT PRIMARY KEY, x FLOAT);
-- expect: OK
INSERT INTO literals (id, x) VALUES (1, 1e300), (2, 2.5E-3), (3, -1.5E+10);
-- expect: 1 rows
SELECT id FROM literals WHERE x > 1e299;
-- error: out of range
INSERT INTO literals (id, x) VALUES (4, 1e400);
-- expect: OK
DROP TABLE literals;

The common SQL type names are aliases of these types:

Alias	Type
INTEGER, BIGINT, SMALLINT	INT
VARCHAR(n), CHAR(n), CHARACTER(n), CHARACTER VARYING(n), STRING	TEXT
BOOLEAN	BOOL
REAL, DOUBLE, DOUBLE PRECISION	FLOAT

A VARCHAR(n) or CHAR(n) column is a TEXT column, and the length n is not a constraint: the column stores the whole value. SMALLINT and BIGINT store the same 64-bit integer as INT.

-- expect: OK
CREATE TABLE aliases (id INTEGER PRIMARY KEY, name VARCHAR(50), ok BOOLEAN);
-- expect: OK
DROP TABLE aliases;

-- expect: OK
CREATE TABLE demo (
  id INT PRIMARY KEY,
  amount FLOAT,
  is_active BOOL,
  name TEXT,
  data BLOB,
  ts TIMESTAMP,
  embedding VECTOR
);

Column types at a write

A write stores a value only when its type is the type of the column. This applies to INSERT, to a multi-row INSERT, to UPDATE, to a parameter and to a bulk load. Two values are converted, because the conversion keeps the value exact:

• An integer in a TIMESTAMP column is stored as that many microseconds.
• An integer in a FLOAT column is stored as a FLOAT, when the double holds it exactly (at most 2^53). A larger integer is refused.

A VECTOR(n) column refuses a vector of another length. Every other value of another type is refused, and nothing is written. One bad row refuses the whole statement. A write never converts text to a number: '12' in an INT column is refused. The refusal names the column, its type and the type of the value.

An ARRAY(<type>) column applies the same rule to each element: each element must fit the element type, or it is one of the two conversions above. See Array columns.

-- expect: OK
CREATE TABLE typed (id INT PRIMARY KEY, n INT, x FLOAT);
-- error: does not match that type
INSERT INTO typed (id, n) VALUES (1, '12');
-- error: does not match that type
INSERT INTO typed (id, n) VALUES (2, 1.5);
-- expect: OK
INSERT INTO typed (id, x) VALUES (3, 5);
-- expect: OK
DROP TABLE typed;

Column types at a read

A WHERE on a table compares a value only with a column of a comparable type. This applies to =, <>, <, <=, >, >=, IN and BETWEEN, in SELECT, UPDATE and DELETE, and to a parameter. These types are comparable:

• INT, FLOAT and TIMESTAMP are numbers. A number compares with each of the three types.
• TEXT, BOOL and BYTES compare only with a value of the same type.

Every other value is refused, and the statement reads or writes nothing. A read never converts text to a number or to a date: ts >= '2026-10-16' on a TIMESTAMP column is refused. Compare a TIMESTAMP column with an integer of microseconds since the epoch (UTC). The refusal names the column, its type and the type of the value. NULL is always correct, and IS NULL does not change. A document collection has no column types, so this rule does not apply to it.

-- expect: OK
CREATE TABLE events (id INT PRIMARY KEY, n INT, ts TIMESTAMP);
-- expect: OK
INSERT INTO events (id, n, ts) VALUES (1, 5, 1760572800000000);
-- error: cannot be compared with that type
SELECT COUNT(*) FROM events WHERE ts >= '2026-10-16';
-- error: cannot be compared with that type
DELETE FROM events WHERE n = '5';
-- an integer of microseconds: the count is 1
SELECT COUNT(*) FROM events WHERE ts >= 1760572800000000;
-- an INT column compares with a FLOAT: the answer is row 1
SELECT id FROM events WHERE n > 1.5;
-- expect: OK
DROP TABLE events;

Array columns

An ARRAY(<type>) column holds an ordered list of values of one type. <type>[] is the same type: TEXT[] is ARRAY(TEXT). Use the type in CREATE TABLE and in ALTER TABLE ADD COLUMN. DESCRIBE reports the type as, for example, ARRAY(TEXT).

The element type is one of BOOL, INT, FLOAT, TEXT, BYTES and TIMESTAMP. The SQL aliases work: INTEGER[], VARCHAR(10)[] and ARRAY(DOUBLE PRECISION) are valid. The server refuses any other element type by name: JSON, BLOB, VECTOR, COUNTER and a nested array such as INT[][].

Write an array with the literal ARRAY[<value>, …]. The literal […] stays the vector literal, and an ARRAY column refuses a vector.

-- expect: OK
CREATE TABLE posts (id INT PRIMARY KEY, tags ARRAY(TEXT), scores INT[]);
-- expect: OK
INSERT INTO posts (id, tags, scores) VALUES (1, ARRAY['red', 'blue', 'red'], ARRAY[3, 5]);
-- expect: OK
INSERT INTO posts (id, tags, scores) VALUES (2, ARRAY[], NULL);
-- expect: OK
UPDATE posts SET tags = ARRAY['green'] WHERE id = 2;

NULL. The whole cell can be NULL. The empty array ARRAY[] is a value, not NULL. An element is never NULL: ARRAY['a', NULL] is refused with an error that names the NULL element.

Order and duplicates. The array keeps the order of the literal and keeps the duplicates. ARRAY['red', 'blue', 'red'] is read back as written.

Size. One cell holds at most 65 536 elements. A write with more elements is refused, and the error names the limit.

Write rule. Each element follows the rule of Column types at a write. An integer in an ARRAY(FLOAT) column is stored as a FLOAT when the double holds it exactly. An integer in an ARRAY(TIMESTAMP) column is a timestamp. A text is never parsed into a number, and a float is never cut into an INT. A scalar is not wrapped into an array: 1 in an ARRAY(INT) column is refused.

Empty arrays in a result. A result column whose cells are all empty arrays has the typed header ARRAY(TEXT). The client reads empty lists.

UPDATE. UPDATE … SET tags = ARRAY[…] replaces the whole cell. There is no statement that changes one element.

Read. col[i] gives element i, and the first element is 0. An index out of range gives NULL, and a NULL cell gives NULL. col[i] works in the SELECT list and in WHERE. cardinality(col) gives the number of elements, and NULL for a NULL cell. It works in the SELECT list and on the right side of UPDATE … SET, but not in WHERE.

-- expect: blue
SELECT tags[1] FROM posts WHERE id = 1;
-- expect: 3
SELECT cardinality(tags) FROM posts WHERE id = 1;

Compare. = has two meanings, by the type of the right side:

• col = <value> is contains: it is true when one element equals the value. An empty array gives false, and a NULL cell gives unknown. Write NOT (col = 'x') for "does not contain".
• col = ARRAY[…] and col <> ARRAY[…] compare the whole array: the same number of elements, the same elements, the same order.

-- expect: 1 rows
SELECT id FROM posts WHERE tags = 'blue';
-- expect: 1 rows
SELECT id FROM posts WHERE tags = ARRAY['green'];

Aggregate. COUNT(col) works. MIN, MAX, SUM and AVG of an array column are refused.

Refused by name. The server refuses these on an array column, and the error names the column and its type:

• <> with a single value, <, <=, >, >=, IN, BETWEEN and LIKE;
• ORDER BY and GROUP BY;
• UNIQUE and PRIMARY KEY;
• DEFAULT, in CREATE TABLE and in ALTER TABLE ADD COLUMN;
• ALTER COLUMN … TYPE, to an array type or from an array type.

Index. CREATE INDEX posts_tags ON posts (tags) makes an index on an array column. It has one entry for each DISTINCT element of a cell. A NULL cell has one entry, and ARRAY[] has none. -0.0 and 0.0 are one value. A NaN element is refused by name.

• One row can have at most 1,024 entries in one index. The limit counts the DISTINCT elements, not cardinality(col). A write that adds an entry to a row above the limit is refused, and a CREATE INDEX over such a row creates nothing. See Troubleshooting.
• The cluster setting cluster.index.max_array_entries changes the limit for a server or for every node of a cluster: SET CLUSTER cluster.index.max_array_entries = <n>, with <n> from 1 to 65536. It works on a single server and on a cluster, and the value stays after a restart. The next write and the next CREATE INDEX read it. A lower limit removes no row and no entry: a row above it stays, and only a write that adds an entry to it is refused. A limit above 1,024 makes the largest element that one write can hold smaller (see the next item). From 14,463 elements up, no element fits an UPDATE that replaces every element on a node with a 64 MiB --memory budget. On a node with no --memory budget, the effect of such a large write is not known yet. An embedded database keeps the limit 1,024.
• The bytes of one write grow with the elements. Measured at 1,024 elements: an UPDATE that replaces every element costs about 10 bytes per element byte plus 232 bytes per element, so it fits the smallest write batch limit (3.2 MiB, a node with a 64 MiB --memory budget) up to about 304 bytes per element. An INSERT of 1,024 elements of 796 bytes is 2,573,360 bytes and fits. A larger write is refused with exceeds this node's write-pipeline budget, and nothing is written: store fewer or smaller elements, or write the array in smaller steps.
• Every node, also a node with no --memory budget, refuses a write whose Raft entry is above about 63.9 MiB (67,043,328 bytes: the 64 MiB peer frame less 64 KiB), also when the entry compresses well. The error names the size and the limit (above the entry ceiling), and nothing is written. Split the write.
• An INSERT over an existing key is checked against the row that the receiving node reads, if it holds one: the same elements then pass. On a cluster, a node that does not hold the shard of the row reads no row, so the same elements above a lowered limit are refused there. This check only decides whether the write is accepted. It never changes what a node stores.
• CREATE UNIQUE INDEX, an index of several columns with an array column, and a FULLTEXT index on an array column are refused. The element type must be TEXT, INT, TIMESTAMP, BOOL or FLOAT.
• col = <value> (contains) can read the index, when the planner picks it (an index of several columns that covers more conditions of the WHERE can win): a SELECT, a COUNT(*), an UPDATE and a DELETE with WHERE tags = 'blue' then read only the rows whose array holds 'blue'. DESCRIBE SELECT … WHERE tags = 'blue' shows the step index scan (prefix) with the name of the index. Each row comes back once, also when it holds the value two times. The whole WHERE is checked again on each row that the index gives.
• These forms do not read the index; they read the rows: col = ARRAY[…] and col <> ARRAY[…] (the whole array), col IS NULL and col IS NOT NULL, an element col[i] = <value>, and col = <value> under NOT or OR. A value of another type than the element gives the same answer as a full scan. The index serves some of them (for example xs = 2 on ARRAY(FLOAT)), and others read the rows (for example ns = 3.0 on ARRAY(INT)).

MERGE BRANCH. The merge treats an array cell as one cell. When the branch and main both change the cell, the normal rule for a cell conflict decides. The merge does not combine the elements. See Merge branches.

-- expect: OK
DROP TABLE posts;

Vector search

ORDER BY <column> <-> [vector] sorts the rows by the cosine distance of the column to the query vector, the nearest first. The distance is 0 for the same direction and 2 for the opposite direction. A row with no vector in the column is not in the result.

The query vector must have the length of the column. For a VECTOR(n) column, a query vector of another length is refused, also on an empty table. For a VECTOR column with no length, a row whose vector has another length refuses the query. A vector of the wrong model then gives an error, not a wrong order.

-- expect: OK
CREATE TABLE vdocs (id TEXT PRIMARY KEY, emb VECTOR(3));
-- expect: OK
INSERT INTO vdocs (id, emb) VALUES ('a', [1, 0, 0]), ('b', [0.9, 0.1, 0]);
-- expect: 2 rows
SELECT id FROM vdocs ORDER BY emb <-> [1, 0, 0] LIMIT 2;
-- error: vector dimension mismatch
SELECT id FROM vdocs ORDER BY emb <-> [1, 0] LIMIT 2;
-- expect: OK
DROP TABLE vdocs;

Tables

A table has typed columns and a primary key.

Write PRIMARY KEY after the type of the key column: id INT PRIMARY KEY. A table has one primary key, so a second PRIMARY KEY is refused. The table clause PRIMARY KEY (id) after the columns is also accepted, and it gives the same table.

-- error: a table has one PRIMARY KEY
CREATE TABLE two_keys (a INT PRIMARY KEY, b INT PRIMARY KEY);

The primary key accepts only a value of its own type. A TEXT key takes a string, a BOOL key takes true or false, and an INT or TIMESTAMP key takes an integer. A write with a value of another type is refused, and nothing is written: primary key column '<name>' has type <type>; the value <value> does not match that type. This applies to INSERT, to INSERT … IF NOT EXISTS, to a multi-row INSERT (one bad row refuses the whole statement) and to INSERT … FILE, whose file name is the key, so the target table needs a TEXT primary key.

-- expect: OK
CREATE TABLE accounts (
  account_id INT PRIMARY KEY,
  owner TEXT,
  balance FLOAT,
  created_at TIMESTAMP
);

List the tables, and show the columns of one table:

-- expect: 2 rows
SHOW TABLES;
-- expect: account_id
DESCRIBE accounts;
-- expect: account_id
SHOW COLUMNS FROM accounts;

SHOW COLUMNS answers column_name, data_type, primary_key, stored_slot, not_null and default. The stored_slot cell is the position of the column inside a stored row, as text, and it is empty for the primary key. A slot never moves: DROP COLUMN retires its slot, and ADD COLUMN takes a new one. not_null is YES for a NOT NULL column, and default is the SQL literal of the DEFAULT (for example 'free' or 5). Both are empty for a column without the constraint. Every cell of the two statements is text.

DESCRIBE answers column_name, data_type, primary_key, indexes, not_null, default and schema_version. The indexes cell names the indexes on the column.

schema_version is the version of the schema of the table, as a decimal text. Each row holds the same value. It is the stamp of the last DDL on the table. On a cluster it is the index of that DDL in the metadata log, so each node gives the same value. Use it to know if the schema changed since you last read it:

• Every DDL on the table changes it: ALTER TABLE, CREATE INDEX, DROP INDEX, SET CONFLICT POLICY. A GRANT or a policy change on the table also changes it. Then the columns can be the same as before. On a cluster the new value is always larger. Compare the values for equality, not for order.
• A row write (INSERT, UPDATE, DELETE) never moves it.
• DROP TABLE and a new CREATE TABLE of the same name and shape give a new value.
• A table on a branch that has no copy of the table shows the value of the base.
• A value of the embedded engine is a local stamp. Do not compare it with the value of a server for the same table.
• 0 means that no stamp is known (a table from a very old release).

SHOW COLUMNS does not have this column.

Drop a table and its data:

CREATE TABLE temp_table (id INT PRIMARY KEY);
DROP TABLE temp_table;

A schema file can run again and again. CREATE TABLE IF NOT EXISTS and CREATE COLLECTION IF NOT EXISTS do nothing when the name is present, and answer OK. They do not compare the columns: the present table stays as it is. DROP TABLE IF EXISTS and DROP COLLECTION IF EXISTS do nothing when the name is absent, and answer OK. On a cluster, two clients that send the same IF NOT EXISTS at the same time both get OK, and the name is created once. Without IF NOT EXISTS, a CREATE of a present name is refused with already exists, and the present table stays as it is. Without IF EXISTS, a DROP of an absent name is refused with not found. A single node and a cluster give the same answer.

-- expect: OK
CREATE TABLE IF NOT EXISTS settings (k TEXT PRIMARY KEY, v TEXT);
-- expect: OK
CREATE TABLE IF NOT EXISTS settings (k TEXT PRIMARY KEY, v TEXT);
-- expect: OK
DROP TABLE IF EXISTS settings;
-- expect: OK
DROP TABLE IF EXISTS settings;

DROP TABLE drops a table or a ledger. DROP COLLECTION drops a KV or DOCUMENT collection. A DROP that names the wrong kind is refused, for example docs is a DOCUMENT collection — use DROP COLLECTION docs, and the object stays unchanged. A cluster refuses it before it replicates the statement, so every node keeps the object.

Column constraints

Write the constraints of a column after its type, in any order: PRIMARY KEY, NOT NULL, NULL, DEFAULT <literal> and UNIQUE. A repeated or contradictory constraint (NULL NOT NULL, two DEFAULT) is refused.

• NOT NULL refuses a write that leaves the column NULL: an INSERT that omits the column, a cell that is NULL, and UPDATE … SET <col> = NULL. The refusal is NOT NULL: column '<col>' of table '<table>' cannot be NULL, and nothing is written. NULL says the default rule in words and changes nothing.
• DEFAULT <literal> fills the column when an INSERT omits it. The literal is a number, a string, TRUE, FALSE or NULL, and it must fit the column type. An integer fits a TIMESTAMP (microseconds) and a FLOAT. An explicit NULL stays NULL. The primary key and a COUNTER take no DEFAULT. NOT NULL is checked after the DEFAULT fills the column.
• UNIQUE creates a UNIQUE index named <table>_<column>_key (see Indexes). Two NULLs do not collide. SHOW INDEXES shows the index, and DROP INDEX drops the constraint. The CREATE TABLE is refused, and creates nothing, when that index name is taken in the database. UNIQUE on the primary key changes nothing: the key is unique.
• AUTO_INCREMENT is refused. No key generator exists yet (B1806). A TEXT primary key that an INSERT omits gets a generated id.

-- expect: OK
CREATE TABLE members (
  id INT PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  plan TEXT NOT NULL DEFAULT 'free',
  note TEXT NULL
);
-- expect: OK
INSERT INTO members (id, email) VALUES (1, 'a@example.com');
-- expect: free
SELECT plan FROM members WHERE id = 1;
-- error: NOT NULL: column 'email' of table 'members' cannot be NULL
INSERT INTO members (id) VALUES (2);
-- error: duplicate key
INSERT INTO members (id, email) VALUES (3, 'a@example.com');
-- error: NOT NULL: column 'plan' of table 'members' cannot be NULL
UPDATE members SET plan = NULL WHERE id = 1;
-- expect: members_email_key
SHOW INDEXES FROM members;
-- error: AUTO_INCREMENT on column 'id' is not supported yet
CREATE TABLE counters (id INT PRIMARY KEY AUTO_INCREMENT, n INT);
-- expect: OK
DROP TABLE members;

ALTER TABLE … ADD COLUMN takes no constraint: the rows that exist are not rewritten. RENAME COLUMN keeps the constraints of the column. ALTER COLUMN … TYPE checks the DEFAULT against the new type, and refuses the change when the default does not fit.

Conflict policy

A table can declare the conflict policy that the sync ingress applies to an offline write. Write WITH CONFLICT POLICY after the column list, or change it later with ALTER TABLE … SET CONFLICT POLICY. CREATE COLLECTION … WITH CONFLICT POLICY takes the same clause.

-- expect: OK
CREATE TABLE notes (id INT PRIMARY KEY, body TEXT) WITH CONFLICT POLICY REJECT;
DROP TABLE notes;

REJECT refuses an offline write when the row changed on the server after the client read it. It is also the policy of a table that declares none. It stores nothing extra.

LWW (last writer wins) gives each row of the table a clock: the time of the commit that wrote the row last, in microseconds. On a cluster it is the clock of the leader that ordered the write, so every node stores the same clock. A DELETE keeps the clock of the delete. Only an LWW table stores the clock; a REJECT table keeps its rows as they are. MERGE BRANCH uses the clock (see "Merge branches"). After ALTER TABLE … SET CONFLICT POLICY LWW, a row that was written before the change has no clock until its next write. Every write gives it the clock, also an UPDATE … SET c = <value> WHERE id = … that changes only some columns, and an UPDATE … SET n = n + 1.

The sync push uses the clock too. Each offline write carries the time at which the device made it. When the row changed on the server after the device read it, the newer write wins the whole row. On equal clocks, the row with the larger cells wins. A row that the push writes keeps the time of the device write, not the time of the push. When the server deleted the row, the write competes with the time of the delete: a write at the same time or later wins, and the row comes back with the device row. A write loses, and comes back as refused, when the server row or the delete is newer, or has no clock. The same rule applies when the device never read the row: an INSERT of a key that the server deleted competes with the time of the delete. In an LPW table, a write over a deleted row comes back as refused. A write whose clock is more than 5 minutes after the server clock is refused: correct the clock of the device.

LWW and LPW trust the clock of the device. A device clock that is fast by less than 5 minutes is accepted, and its writes win over newer writes from correct clocks, with no error. When a lost update is not acceptable, use REJECT or FUNCTION for that table.

-- expect: OK
CREATE TABLE drafts (id INT PRIMARY KEY, body TEXT) WITH CONFLICT POLICY LWW;
DROP TABLE drafts;

LPW (last property wins) gives each column of each row its own clock: the time of the last commit that changed that column. A write that gives a column the value that it already has keeps the clock of the column. MERGE BRANCH then merges a row that both sides changed column by column. A DOCUMENT collection takes LPW too: each property of a JSON document has its own clock. A property is a value, null, an array or an empty object at one path of the document; an array is one property. A KV collection refuses LPW, because a KV value has no columns and no properties. Under LWW, each document of a DOCUMENT collection has one clock. After ALTER TABLE … SET CONFLICT POLICY LPW, a column that no write changed since the policy change has no clock. After a change from LPW to LWW or REJECT, the old rows stay readable, and the next write of a row gives it the format of the new policy.

The sync push merges an LPW row column by column. An offline UPDATE changed the columns whose values differ from the row that the device read. When the row changed on the server after that read, each changed column with a newer clock wins, and every other column keeps the server value. When the written row keeps a server value, the write is merged, and the next sync() brings the merged row down. A column with no clock, or a row that the server deleted, makes the write a conflict.

-- expect: OK
CREATE TABLE profiles (id INT PRIMARY KEY, name TEXT, city TEXT) WITH CONFLICT POLICY LPW;
DROP TABLE profiles;
-- expect: OK
CREATE COLLECTION settings TYPE DOCUMENT WITH CONFLICT POLICY LPW;
DROP COLLECTION settings;

The declared policy is part of the schema of the table: the create writes it in the same commit on every node of a cluster, and TRUNCATE keeps it.

FUNCTION <name> gives the conflict to a kd function of the application (see Functions and tickers). The function runs ON THE SERVER, never on the client:

CREATE FUNCTION concat_body(ancestor ROW NULL, server ROW NULL, incoming ROW NULL) RETURNS ROW NULL LANGUAGE kd AS $$
  return {id: server.id, body: server.body + "|" + incoming.body};
$$;
CREATE TABLE notes (id INT PRIMARY KEY, body TEXT) WITH CONFLICT POLICY FUNCTION concat_body;

• A conflict function has the fixed signature (ancestor ROW NULL, server ROW NULL, incoming ROW NULL) RETURNS ROW NULL. Each side can be absent, and a null result deletes the row.
• The server calls the function with three values of the row: the ancestor (the row before both changes), the server row and the incoming row. Each value is an object of column name to value, or null when that side has no row.
• The function returns the merged row. The row must name every column and keep the primary key; an unknown column, a missing column or another key refuses the conflict. return null merges to "no row" (a delete).
• refuse('<reason>') refuses the conflict. The conflict then acts as REJECT, and the error carries the reason.
• The function must not write. A write statement, insert, update, delete or remove refuses the conflict.
• The function runs as its RUN AS user; without RUN AS it runs as the user who pushes or merges. That user needs no INVOKE right. The user who declares the policy needs INVOKE on the function, and the function must exist.
• DROP FUNCTION of a function that a conflict policy names is refused; the error names every such table. Change the policy of those tables first.
• The server runs the function before the write goes into the replicated log, and it writes only the result. When the row changes again before that write, the server runs the function again on the new row, at most 3 times.

SHOW COLLECTIONS prints the declared policy. It answers three columns: name, type and conflict_policy. The conflict_policy cell is reject, lww, lpw or function <name> for a declared policy, and the empty text for a table or a collection that declares none. kd-tool backup reads this cell, and kd-tool restore --create declares the same policy again.

SHOW CONFLICT STATE OF <table> gives the conflict state of a table or a ledger, for a backup. It answers the columns of the table, then #kd_deleted (BOOL) and #kd_conflict (BYTES). A row gives its cells, false and its stored conflict header (empty for a row with no clock). A deleted row whose delete kept a clock gives its key, NULL in every other cell, true and the clock of the delete. The statement needs the SELECT right on the whole table. kd-tool backup uses it for a table with LWW or LPW, and kd-tool restore gives each row its dumped clock again (see Backup, restore and import).

SHOW CONFLICT STATE OF and SHOW MISTYPED CELLS OF are routed like a SELECT * of the table (B2247). A node that is current on every shard of the table reads its own copies. Otherwise it reads each shard from a current copy on another node and concatenates the rows, so every node answers. When a shard has no current copy, the statement gets a retryable error that names the shard and the cause (B2156).

SHOW MISTYPED CELLS OF <table> finds the stored cells whose type is not the type of their column. An older version of kinedb did not check the column type at a write, so it could store a value of another type in a typed column, for example the text '12' in an INT column. A read of a column that no ALTER COLUMN … TYPE changed returns such a cell unchanged, so '12' reads as text. The statement gives one row for each such cell. A NULL, the primary key and a cell of the correct type give no row. A COUNTER column takes the cells of its number type. A VECTOR(n) column takes a vector of length n; a VECTOR with no length takes a vector of any length. The columns are:

• key: the primary key of the row, with the type of the key column;
• column (TEXT): the name of the column;
• column_type (TEXT): the declared type of the column, for example INT;
• stored_type (TEXT): the type of the stored cell, for example TEXT;
• stored_value (TEXT): the stored cell as text;
• reading (TEXT): the value of the cell in the column type, as text. It is NULL when the cell has no such value, for example the text 'abc' in an INT column.

The statement reads the stored cells before any conversion. It never writes. To repair a cell, write the correct value: UPDATE <table> SET <column> = <value> WHERE <key> = …. The statement needs the SELECT right on the whole table. It reads a table or a ledger only, and it is refused inside a transaction (a transaction() call).

TRUNCATE TABLE

TRUNCATE TABLE <name> removes every row of a table, or every entry of a KV or DOCUMENT collection. It keeps the whole schema: the columns, the primary key, the kind of the collection, the grants, the conflict policy and the indexes. Only the rows go.

TRUNCATE is refused on a table with an AFTER trigger until #3471 gives a deterministic carry (AFTER triggers).

A write that the server prepared before a TRUNCATE, or before a DROP TABLE and a CREATE TABLE of the same name, never writes into the new table. When it arrives after the change, the server refuses it with a retryable error that contains [B2114_STALE_TABLE] and writes nothing (B2114). The client sends the statement again, and the server prepares it again against the new table.

ALTER TABLE

ALTER TABLE adds, drops and renames a column. The change reads no stored row and writes no stored row, so it takes the same time on an empty table and on a large one.

-- expect: OK
CREATE TABLE people (id INT PRIMARY KEY, name TEXT, city TEXT);
-- expect: OK
INSERT INTO people (id, name, city) VALUES (1, 'Alice', 'Oslo');
-- expect: OK
ALTER TABLE people ADD COLUMN email TEXT;
-- expect: 1 rows
SELECT id, email FROM people WHERE email IS NULL;

A row that existed before ADD COLUMN reads NULL in the new column. The word COLUMN is optional: ALTER TABLE people ADD email TEXT is the same statement.

-- expect: OK
ALTER TABLE people RENAME COLUMN city TO town;
-- expect: Oslo
SELECT town FROM people WHERE id = 1;
-- expect: OK
ALTER TABLE people DROP COLUMN town;
-- expect: OK
ALTER TABLE people ADD COLUMN town TEXT;
-- expect: 1 rows
SELECT id FROM people WHERE town IS NULL;

RENAME COLUMN also renames the column in every index of the table. DROP COLUMN removes the column and its values: a column that you add later with the same name starts as NULL in every existing row. Drop the indexes on a column before you drop the column. The primary key column cannot be dropped. A policy that names a column (see Users, roles and permissions) keeps the column from a drop or a rename; change the policy first. AS OF reads (see Time travel and history) show the columns that the table had at that commit.

DROP TABLE people;

ALTER COLUMN TYPE

ALTER TABLE … ALTER COLUMN … TYPE changes the type a column declares. A stored value keeps its own bytes, so the column reads back in the new type and no row is rewritten: the change takes the same time on an empty table and on a large one.

-- expect: OK
CREATE TABLE readings (id INT PRIMARY KEY, amount TEXT);
-- expect: OK
INSERT INTO readings (id, amount) VALUES (1, '9'), (2, '10'), (3, '100');
-- expect: OK
ALTER TABLE readings ALTER COLUMN amount TYPE INT;
-- expect: 2 rows
SELECT id FROM readings WHERE amount > 9;

Under TEXT the value '9' sorts after '100', because the comparison reads the characters. Under INT it does not. An index on the column is built again as part of the change, so it answers in the order of the new type. A FULLTEXT index holds a TEXT column only, so a change of its column to another type is refused and names the index; drop the index first. The word COLUMN is optional, and ALTER TABLE readings ALTER COLUMN amount SET DATA TYPE INT is the same statement.

On a cluster, the change of an indexed column runs as steps. The first step changes the type and drops each index on the column in one commit. Then a CREATE INDEX for each index builds it again. A refusal or a retryable answer of the first step means that nothing changed. After another failure of the first step, the type change and the drops applied together or not at all. That error names the indexes and shows their CREATE INDEX statements. Read the type of the column before you send the ALTER again.

A failure of a later step is never retryable, because a part of the statement applied. The error names the dropped indexes, says that the type change applied, names the indexes the rebuild built, and shows the CREATE INDEX statement of each missing index. Run those statements to create the missing indexes again. Do not send the ALTER again when the answer says that it applied.

kinedb keeps every stored value whole. When a value in the column has no reading in the new type, the statement names that value and changes nothing:

-- expect: OK
ALTER TABLE readings ALTER COLUMN amount TYPE TEXT;
-- expect: OK
INSERT INTO readings (id, amount) VALUES (4, 'seven');
-- error: holds 'seven'
ALTER TABLE readings ALTER COLUMN amount TYPE INT;
-- expect: seven
SELECT amount FROM readings WHERE id = 4;

AS OF reads (see Time travel and history) show the type that the column had at that commit.

DROP TABLE readings;

INSERT

Insert one row, or several rows in one statement:

INSERT INTO accounts (account_id, owner, balance, created_at)
VALUES (1, 'Alice', 1000.0, 1000000);
INSERT INTO accounts (account_id, owner, balance, created_at)
VALUES
  (2, 'Bob', 2000.0, 2000000),
  (3, 'Charlie', 3000.0, 3000000),
  (4, 'Diana', 4000.0, 4000000);
-- expect: 4
SELECT COUNT(*) FROM accounts;

INSERT writes the row at its primary key. When a row is already there, the new row replaces the whole old row, and the answer is the same 1 row(s) inserted. INSERT never refuses a key that exists, and it never keeps two rows under one key. Use UPDATE to change some columns and keep the others.

-- expect: OK
INSERT INTO accounts (account_id, owner, balance, created_at)
VALUES (4, 'Diana', 4000.0, 4000001);
-- expect: 4000001
SELECT created_at FROM accounts WHERE account_id = 4;
-- expect: 4
SELECT COUNT(*) FROM accounts;

INSERT … IF NOT EXISTS is the strict form. It writes a row only when no row has its primary key. When a row is there, the statement is refused with duplicate key: '<key>' already exists in '<table>', and nothing is written. With several rows, all rows are written or none: one existing key refuses the whole statement, also when the rows are on different shards. A key that is two times in the statement is refused too. The refusal is final, so a client must not retry it. When several clients send IF NOT EXISTS for one key at the same time, exactly one gets OK and all others are refused, on a single server and on a cluster. So you can use it to claim a key, for example a block of ids.

IF NOT EXISTS is also allowed as an item of a transaction (a transaction() call). The check reads the state that the earlier items of the transaction leave: after a DELETE of the key, the insert succeeds, and after an INSERT of the key, it is refused. When the key exists, the WHOLE transaction is refused with the same duplicate key: error, and no item of it is written, on a single server and on a cluster. A later item of the transaction can change the inserted row.

-- expect: OK
INSERT INTO accounts (account_id, owner, balance, created_at)
VALUES (9, 'Grace', 900.0, 9000000) IF NOT EXISTS;
-- error: duplicate key: '1' already exists in 'accounts'
INSERT INTO accounts (account_id, owner, balance, created_at)
VALUES (10, 'Heidi', 100.0, 10000000), (1, 'Mallory', 0.0, 1) IF NOT EXISTS;
-- expect: 0 rows
SELECT owner FROM accounts WHERE account_id = 10;
-- expect: Alice
SELECT owner FROM accounts WHERE account_id = 1;
DELETE FROM accounts WHERE account_id = 9;

A table holds rows, not JSON bodies. A table refuses INSERT INTO <table> {json} and PUT '<key>' … INTO <table>, and nothing is written. Use the column form of INSERT.

-- error: accounts is a Table collection
INSERT INTO accounts {"account_id": 11, "owner": "Ivan"};
-- error: accounts is a Table collection
PUT 'k' 1 INTO accounts;

SELECT

-- expect: Alice
SELECT * FROM accounts WHERE account_id = 1;
-- expect: Bob
SELECT owner, balance FROM accounts WHERE owner = 'Bob';

A SELECT from a name that does not exist refuses with table not found: <name>, the same refusal as UPDATE and DELETE. So a typo in a table name never reads as an empty table. An empty table answers 0 rows. A document collection that an INSERT created without CREATE COLLECTION exists, and it reads.

-- error: table not found: acounts
SELECT * FROM acounts;

WHERE

The comparison operators are =, !=, <, <=, > and >=. AND, OR, NOT and parentheses combine conditions.

-- expect: 1 rows
SELECT * FROM accounts WHERE balance > 2000.0 AND owner != 'Diana';
-- expect: 3 rows
SELECT owner FROM accounts WHERE NOT account_id = 1;
-- expect: Bob
SELECT owner FROM accounts WHERE (account_id = 1 OR account_id = 2) AND balance > 1500.0;

IN, BETWEEN, LIKE, ILIKE (not case-sensitive) and IS NULL:

-- expect: 2 rows
SELECT owner FROM accounts WHERE account_id IN (1, 2);
-- expect: 2 rows
SELECT owner FROM accounts WHERE balance BETWEEN 1000.0 AND 2000.0;
-- expect: Alice
SELECT owner FROM accounts WHERE owner LIKE 'Al%';
-- expect: Alice
SELECT owner FROM accounts WHERE owner ILIKE 'al%';
-- expect: 0 rows
SELECT owner FROM accounts WHERE owner IS NULL;

A constant condition is also valid. WHERE false gives no row and keeps the column header. WHERE true gives every row. A comparison of two literals, such as 1 = 0, is the same.

-- expect: 0 rows
SELECT owner FROM accounts WHERE false;
-- expect: 4 rows
SELECT owner FROM accounts WHERE 1 = 1;

Full-text search: MATCH

MATCH finds the rows whose text holds the words of a query. kinedb makes the text lower case and splits it at each character that is not a letter or a digit. So Acme-Holding holds the two words acme and holding. The query text gets the same split.

• name MATCH 'acme holding' — the text holds every word, in any order.
• name MATCH 'acme OR beta' — an upper-case OR between words gives the rows that match either side. 'a b OR c' is (a AND b) OR c. A lower-case or is a word.
• name MATCH '"acme holding"' — a phrase in double quotes. The words must be adjacent and in this order. OR inside the quotes is a word. A phrase of one word is that word.
• name NOT MATCH '…' — the other rows. A row with a NULL text is in neither answer.

A phrase compares WORDS, not characters. "acme holding" matches ACME-holding and Acme Holding. It does not match acme holdings, because holdings is a different word. The index stores no word positions. A FULLTEXT index finds the rows that hold every word, and kinedb then checks the order of the words in the text of each of these rows. A quote with no closing quote and a phrase with no word are errors.

CREATE TABLE companies (id INT, name TEXT, PRIMARY KEY (id));
INSERT INTO companies (id, name) VALUES (1, 'Acme Holding ApS');
INSERT INTO companies (id, name) VALUES (2, 'Acme Big Holding');
INSERT INTO companies (id, name) VALUES (3, 'Holding Acme');
INSERT INTO companies (id, name) VALUES (4, 'acme holdings');
INSERT INTO companies (id, name) VALUES (5, 'Beta Corp');
CREATE FULLTEXT INDEX ft_companies_name ON companies (name);
-- expect: 3 rows
SELECT id FROM companies WHERE name MATCH 'acme holding';
-- expect: Acme Holding ApS
SELECT name FROM companies WHERE name MATCH '"acme holding"';
-- expect: 2 rows
SELECT id FROM companies WHERE name MATCH '"acme holding" OR beta';
-- expect: 4 rows
SELECT id FROM companies WHERE name NOT MATCH '"acme holding"';
-- error: quote
SELECT id FROM companies WHERE name MATCH '"acme holding';
DROP TABLE companies;

Subqueries

The WHERE of a SELECT can hold a subquery in two forms:

• col IN (SELECT c FROM …) and col NOT IN (SELECT c FROM …). The subquery gives a list of values.
• col <op> (SELECT c FROM …), with =, !=, <, <=, > or >=. The subquery must give one row or no row. More than one row is refused with more than one row returned by a subquery used as an expression.

-- expect: 2 rows
SELECT owner FROM accounts WHERE account_id IN (SELECT account_id FROM accounts WHERE balance > 2500.0);
-- expect: 3 rows
SELECT owner FROM accounts WHERE account_id NOT IN (SELECT account_id FROM accounts WHERE owner LIKE 'A%');
-- expect: Diana
SELECT owner FROM accounts WHERE balance = (SELECT MAX(balance) FROM accounts);
-- expect: 0 rows
SELECT owner FROM accounts WHERE account_id IN (SELECT account_id FROM accounts WHERE balance > 9000.0);

The subquery runs first, as the same user, with the same permissions and row rules. Its result then goes into the statement as a list of values. On a cluster the subquery reads every shard of its table. The subquery and the outer SELECT are two reads, as two reads of different shards are.

The rules of the result follow SQL:

• An empty result makes IN false for each row and NOT IN true for each row.
• A NULL in the result makes NOT IN select no row.
• A subquery with no row, or with a NULL, makes col <op> (SELECT …) select no row.
• The subquery must give exactly one column. SELECT * in a subquery is refused.
• A result with more than 100 000 values is refused. It is never cut.

These forms are refused with … is not supported:

• A correlated subquery: the subquery names a table or an alias of the outer query.
• EXISTS (SELECT …).
• A qualified table name (db.table, db@branch.table) in a statement with a subquery. USE the database (and USE BRANCH) and write the bare names.
• A subquery in UPDATE, DELETE or INSERT, and on a server any subquery inside a transaction. The read would not be part of the write.
• A subquery in the select list, in FROM, in a JOIN … ON, in GROUP BY, HAVING, ORDER BY or LIMIT.

-- error: EXISTS (subquery) is not supported
SELECT owner FROM accounts WHERE EXISTS (SELECT account_id FROM accounts);
-- error: a subquery in the UPDATE statement is not supported
UPDATE accounts SET balance = 0.0 WHERE account_id IN (SELECT account_id FROM accounts);

ORDER BY, LIMIT and OFFSET

ORDER BY sorts by one or more columns, ASC (the default) or DESC. OFFSET comes after LIMIT. SELECT TOP n … is the same statement as SELECT … LIMIT n; a statement with both TOP and LIMIT is refused.

-- expect: Diana
SELECT owner FROM accounts ORDER BY balance DESC LIMIT 1;
-- expect: Diana
SELECT TOP 1 owner FROM accounts ORDER BY balance DESC;
-- expect: 2 rows
SELECT * FROM accounts LIMIT 2;
-- expect: Charlie
SELECT owner FROM accounts ORDER BY account_id LIMIT 1 OFFSET 2;

ORDER BY <primary key> LIMIT n reads only the page, not the table. The rows of a table are stored in the order of the primary key, so the query walks the table from the matching end and stops after n rows. DESC starts at the last row. This makes "the newest row" a fast query on a table whose primary key grows, and it costs the same on a big table as on a small one. EXPLAIN shows the walk as pk-ordered scan.

The walk serves the statement alone. A WHERE, a second ORDER BY column, an aggregate, a GROUP BY or an ORDER BY on another column sorts the table as before. An OFFSET rides along: the query reads n + offset rows.

-- expect: pk-ordered scan
DESCRIBE SELECT owner FROM accounts ORDER BY account_id DESC LIMIT 1;
-- expect: Diana
SELECT owner FROM accounts ORDER BY account_id DESC LIMIT 1;
-- expect: Alice
SELECT owner FROM accounts ORDER BY account_id ASC LIMIT 1;

A range on the primary key reads only the rows in the range. A WHERE with <, <=, >, >= or BETWEEN on the primary key, at the top level of the WHERE or joined with AND, starts the walk at the first key of the range and stops it after the last key. So WHERE id < 10 costs the same on a big table as on a small one. When the WHERE has several bounds on the key, the walk takes the narrowest range. The query checks the whole WHERE and the row permissions on each row it reads. A bound under OR or NOT, or a value of another type than the key (7.5 on an INT key), does not narrow the walk; the answer is the same. An index or an IN list that serves the WHERE comes first. EXPLAIN shows the walk as pk range scan (B3154).

An UPDATE or a DELETE with a range on the primary key finds its rows with the same walk. The write permission check reads the same range. DESCRIBE UPDATE and DESCRIBE DELETE show the walk as pk range scan (B3169).

-- expect: pk range scan
DESCRIBE SELECT owner FROM accounts WHERE account_id >= 2 AND account_id < 4;
-- expect: pk range scan
DESCRIBE DELETE FROM accounts WHERE account_id < 2;
-- expect: 2 rows
SELECT owner FROM accounts WHERE account_id >= 2 AND account_id < 4;
-- expect: 3
SELECT COUNT(*) FROM accounts WHERE account_id BETWEEN 2 AND 4;

ORDER BY vector distance

ORDER BY <column> <-> [<numbers>] sorts the rows by the cosine distance of a VECTOR cell to the given vector, the nearest row first. No index serves this sort: the query reads the table, keeps the rows that match the WHERE, sorts them and then applies OFFSET and LIMIT. A row with no vector in the column is not in the result. When the SELECT does not name the vector column, the result has an extra distance column.

-- expect: OK
CREATE TABLE passages (id TEXT PRIMARY KEY, lang TEXT, emb VECTOR(3));
-- expect: OK
INSERT INTO passages (id, lang, emb) VALUES ('north','en',[1,0,0]), ('berg','de',[0.9,0.1,0]), ('west','en',[0,1,0]), ('coast','en',[0.7,0.7,0]);
-- expect: coast
SELECT id FROM passages WHERE lang = 'en' ORDER BY emb <-> [1,0,0] LIMIT 1 OFFSET 1;
-- expect: 1 rows
SELECT id FROM passages WHERE id = 'west' ORDER BY emb <-> [1,0,0] LIMIT 2;

Aggregates

COUNT(*), SUM, AVG, MIN and MAX:

-- expect: 4
SELECT COUNT(*) FROM accounts;
-- expect: 10000
SELECT SUM(balance) FROM accounts;
-- expect: 2500
SELECT AVG(balance) FROM accounts;
-- expect: 1000
SELECT MIN(balance) FROM accounts;
-- expect: 4000
SELECT MAX(balance) FROM accounts;

GROUP BY and HAVING

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  account_id INT,
  amount FLOAT
);
INSERT INTO orders (order_id, account_id, amount)
VALUES
  (101, 1, 100.0),
  (102, 1, 200.0),
  (103, 2, 150.0),
  (104, 3, 300.0),
  (105, 3, 250.0);
-- expect: 3 rows
SELECT account_id, COUNT(*) as order_count, SUM(amount) as total
FROM orders
GROUP BY account_id;
-- expect: 2 rows
SELECT account_id, SUM(amount) as total
FROM orders
GROUP BY account_id
HAVING SUM(amount) > 150.0;

On a cluster, a table whose shards are on different nodes gives the same answer to GROUP BY, HAVING, AVG and every other aggregate as a table on one node. Each shard aggregates its own rows, and the node that received the query combines the results.

A table shards by ranges of its primary key. A query that orders by the primary key and has a LIMIT, for example SELECT id FROM codes ORDER BY id DESC LIMIT 1, reads the shards in key order from the correct end. It stops when it has enough rows. When the last shard holds a row, the query reads only that shard (B1722).

JOIN

JOIN (inner), LEFT JOIN and RIGHT JOIN join two tables on a condition.

-- expect: Alice
SELECT accounts.owner, orders.order_id
FROM accounts
JOIN orders ON accounts.account_id = orders.account_id
WHERE accounts.account_id = 1;
-- expect: 6 rows
SELECT accounts.owner, orders.order_id
FROM accounts
LEFT JOIN orders ON accounts.account_id = orders.account_id;
-- expect: 5 rows
SELECT accounts.owner, orders.order_id
FROM accounts
RIGHT JOIN orders ON accounts.account_id = orders.account_id;

A join works from every node of the cluster, also from a node that does not hold a copy of each table. Such a node moves the rows of the tables between the nodes and gives the same result as a single node. The ON condition must be an equality (a.x = b.y). These joins are refused with an error, never answered with missing rows: FULL OUTER JOIN, an ON with an operator that is not =, a join with AS OF, and, on a node that does not hold every table, a join of 3 or more tables with a LEFT / RIGHT edge or with 2 or more large tables.

A node that holds every joined table reads only the rows that the join can use. A WHERE condition on one table narrows the read of that table. Then a table whose join column is its primary key, or the first column of an index, is read only at the join values of a table that is already read, when these values are few compared with its rows. Thus SELECT … FROM a JOIN b ON a.id = b.a_id WHERE a.id = 5 reads one row of a and only the matching rows of b (B3810). The result is always the same as with a full read.

A join can also read the commit history views kinedb_commits and kinedb_commit_tables. Such a join runs only on a node that holds a copy of every joined table. See The commit history views.

On a node that starts with --memory, the rows that a join moves to one node are bounded by bytes from that budget (B1854). When the rows of a small table are larger than the bound, the node uses a different join method. When even one part of the join is larger than the bound, the node refuses the join. A re-send of the same join cannot succeed: raise --memory, or narrow the join with a WHERE clause. A node without --memory has no such bound.

join buffer: partition 3 of 64 of the repartition join holds at least <n> bytes, over the
bound of <m> bytes for one join buffer on this <x> MiB --memory node. Raise --memory, or
narrow the join with a WHERE clause. The join did not run, and a re-send of it unchanged
cannot succeed.

A node that holds a copy of every table runs the join on its own copies only when it is current on each of them: it holds the read lease of each shard, as for a SELECT, and its apply reached the commit index that it read when the join arrived. When one copy is not current, the node waits a short time. If the copy is still not current, the node refuses the join with a retryable error that names the shard and the cause, never with missing rows (B2156):

LocalJoin: this node is not a caught-up read-lease holder for shard <id> — retry shortly
(placement/split/backfill/apply-lag in flight) [reason=no-lease …]

UPDATE

UPDATE accounts SET balance = 1500.0 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100.0;
-- expect: 1600
SELECT balance FROM accounts WHERE account_id = 1;

An UPDATE without WHERE changes every row.

UPDATE changes the columns of a row. It does not move the row. The primary key is the identity of the row, so an assignment to the primary-key column is refused with cannot SET <column>: it is the row's primary key, not a field — DELETE the row and INSERT it under the new key, and nothing is written. The refusal covers every form: with a WHERE, without one, and with the key next to other columns — one assignment to the key refuses the whole statement, and the other columns stay as they were. To give a row another key, DELETE the row and INSERT it under the new key. A document collection answers the same way for _id.

The WHERE of UPDATE takes the same predicate as SELECT: AND, OR, NOT and parentheses. A row changes only when the whole predicate is true for that row. When no row matches, the statement changes nothing and reports 0 rows.

UPDATE accounts SET balance = balance - 100.0 WHERE owner = 'Bob' AND balance > 2000.0;
-- expect: 2000
SELECT balance FROM accounts WHERE account_id = 2;
-- expect: 0 row(s) updated
UPDATE accounts SET balance = 0.0 WHERE account_id = 1 AND owner = 'Bob';

Expressions in SET

The right side of a SET can be an expression. It can read the columns of the row, call a function, and use CAST, CASE, || and arithmetic.

CREATE TABLE staff (id INT PRIMARY KEY, name TEXT, first TEXT, age TEXT, n INT);
INSERT INTO staff (id, name, first, age, n) VALUES (1, 'Ada Lovelace', NULL, '36', 3);
INSERT INTO staff (id, name, first, age, n) VALUES (2, 'Alan Turing', NULL, 'n/a', 4);
UPDATE staff SET first = split_part(name, ' ', 1);
-- expect: Alan
SELECT first FROM staff WHERE id = 2;
UPDATE staff SET first = lower(first) || '!', n = n * 2 + 1;
-- expect: ada!
SELECT first FROM staff WHERE id = 1;
UPDATE staff SET age = CASE WHEN age = 'n/a' THEN NULL ELSE age END;
UPDATE staff SET n = CAST(age AS INT) WHERE age IS NOT NULL;
-- expect: 36
SELECT n FROM staff WHERE id = 1;

Every right side reads the row as it was BEFORE the statement. So SET a = b, b = a swaps the two values.

The forms:

• A column name, a literal, NULL, and parentheses.
• Arithmetic: +, -, *, /, %, and a unary -. Two INT values give an INT; / on two INT values truncates toward zero. A FLOAT on one side gives a FLOAT.
• a || b joins two texts. A NULL on either side gives NULL. One side must be TEXT; an INT, FLOAT or BOOL on the other side becomes its text.
• A comparison (=, !=, <>, <, <=, >, >=), AND, OR, NOT, and IS [NOT] NULL. They follow the same three-valued logic as a WHERE.
• CAST(<expression> AS <type>). The type is a column type or one of its aliases (INTEGER, VARCHAR(n), …). A CAST can read a number from a text: CAST('42' AS INT) gives 42. A FLOAT becomes an INT rounded half away from zero.
• CASE WHEN <condition> THEN <value> … [ELSE <value>] END, and the simple form CASE <value> WHEN <value> THEN <value> … END. With no ELSE, a row that matches no WHEN gets NULL.

The functions (a name matches in any case):

Function	Result
lower(s), upper(s)	the text in lower or upper case
length(s)	the number of characters
substr(s, start [, count]), substring(s FROM start [FOR count])	the characters from position start (the first is 1)
split_part(s, delimiter, n)	field n (the first is 1); a negative n counts from the end; past the end it gives ''
trim(s [, chars]), ltrim(…), rtrim(…)	the text without the spaces (or the chars) at both ends, the start, or the end
replace(s, from, to)	the text with each from replaced by to
concat(a, b, …)	the texts joined; a NULL argument is skipped
left(s, n), right(s, n)	the first or the last n characters
abs(x), round(x [, digits]), floor(x), ceil(x)	the number; round rounds half away from zero
coalesce(a, b, …)	the first argument that is not NULL
nullif(a, b)	NULL when a = b, else a

A function other than coalesce, concat and nullif gives NULL when an argument is NULL.

These errors refuse the whole statement, and the statement writes no row:

• An integer overflow, a division by zero, and a result that is not a finite number.
• A CAST that cannot convert its value, for example CAST('abc' AS INT).
• An unknown function: unknown function in an UPDATE SET: <name>().
• A function that is not deterministic, for example now(), random(), current_timestamp or gen_random_uuid(). A value of a write must not depend on the node or the time that computes it, so pass the value as a literal or a parameter.
• A value of the wrong type for the column. The write does not convert a text into a number: write a CAST.
• A NULL for a NOT NULL column.

-- error: division by zero
UPDATE staff SET n = n / 0;
-- error: unknown function in an UPDATE SET: frobnicate()
UPDATE staff SET first = frobnicate(name);
-- error: now() is not deterministic
UPDATE staff SET n = now();

A document collection does not take an expression in SET yet. It refuses with an expression in the SET of a document UPDATE is not supported yet. The forms SET f = <literal> and SET f = f ± <number> work on a document.

DELETE

The WHERE of DELETE takes the same predicate as SELECT and UPDATE.

The answer counts the rows that the statement removed. A DELETE that matches no row answers count: 0. This is also true for a primary-key point that names no row, on a single node and on a cluster. The keyed form DELETE '<key>' FROM <collection> follows the same rule (see Key-value collections).

DELETE FROM accounts WHERE account_id = 4 OR (owner = 'Diana' AND NOT balance < 5000.0);
-- expect: 3
SELECT COUNT(*) FROM accounts;
-- expect: 0 row(s) deleted
DELETE FROM accounts WHERE account_id = 99;

Parameters

Give a value from a caller as a parameter, never as joined SQL text. A client library sends the statement with $1, $2, … and the values with sql(text, params). The server puts one literal of the value's type in the place of each $n, so a string from a caller cannot change the statement. A $1 inside a string, a $$ body or a comment is text, not a parameter.

await db.sql('INSERT INTO people (id, name) VALUES ($1, $2)', [7, "it's'); DELETE FROM people; --"]);
await db.sql('SELECT id FROM people WHERE name = $1', ["it's'); DELETE FROM people; --"]);

Each value carries its type on the binary wire. The client library picks the type from the value:

Type	JavaScript	Python	Literal in the statement
INT	an integer number, a bigint	int	the number (the full 64-bit range)
FLOAT	a number with a fraction	float	the number; NaN and infinity are refused
TEXT	string	str	a string, with each ' doubled
BOOL	boolean	bool	true / false
TIMESTAMP	Date	datetime (with a time zone)	the microseconds since the epoch
bytes	Uint8Array	bytes, bytearray	X'<hex>'
VECTOR	Float32Array	—	a vector
NULL	null, undefined	None	NULL

A plain object or an array is refused. A JavaScript integer number outside ±2^53-1 is refused: give it as a bigint. The call refuses a $0, a $n without a value and a value that no $n uses. A statement with a $n and no parameters is refused: "unbound parameter $1: pass its value with sql(text, params), or in kd as query(sql, [args])".

A parameter does what a literal of its type does in the same place. A write checks the column type, as a literal does (see Column types at a write). A NULL parameter in a VALUES list writes NULL; a NOT NULL column refuses it. WHERE col = $1 with a NULL is refused, as col = NULL is: write col IS NULL.

X'<hex>' is the bytes literal: an even number of hex digits. A BYTES column holds it (see Data types).

kd functions bind with query(sql, [args]) and the same binder (parameters).

Document collections

A document collection holds JSON documents. Each document has a key, _id.

-- expect: OK
CREATE COLLECTION documents TYPE DOCUMENT;

The FIELDS of a document are schemaless, but its storage shape is fixed: the key _id and the body document, both Text. SHOW COLUMNS and DESCRIBE report that shape, so a client can discover a collection's primary key the same way it discovers a table's.

-- expect: _id
SHOW COLUMNS FROM documents;

SHOW COLLECTIONS tells you which of the two a name is. Read the type cell: a document collection answers DOCUMENT and a table answers TABLE. Do NOT tell them apart by their columns — a table declared (_id TEXT PRIMARY KEY, document TEXT) reports exactly what a collection reports, and the two need different DDL.

The first write to a name that no table or collection has creates the collection. A JSON INSERT, a PUT '<id>' {...} or an UPSERT creates a DOCUMENT collection, and a key-value PUT creates a KV collection. The node runs CREATE COLLECTION IF NOT EXISTS first, then the write. So the new collection is in SHOW TABLES and SHOW COLLECTIONS, and DESCRIBE answers for it. This is the same on a standalone server and on a cluster. A bulk load with append is a first write too: a load of 0 documents into a new name creates an empty DOCUMENT collection. Two first writes at the same time make one collection. A SQL row INSERT never creates one: it answers table not found. A file write never creates one either: it is refused. Create the collection first with CREATE COLLECTION. The write right on a name that does not exist cannot be granted, so only a superuser makes such a first write.

A version before B2251 (with --calvin) or before B2763 (without --calvin, and -e or -f) made such a collection with no catalog entry. A node of this version refuses to start with such a collection, until you start it once with KINEDB_ADOPT_IMPLICIT_TABLES=1. See Troubleshooting.

INSERT without an _id makes one. PUT '<id>' {...} writes the document at that key, and it replaces a document that is there. GET reads one document by its key. A TABLE refuses the JSON INSERT and PUT; see INSERT.

-- expect: OK
INSERT INTO documents {"name": "Alice", "age": 30};
-- expect: 1 rows
SELECT * FROM documents;
-- expect: OK
PUT 'doc1' {"name": "Bob", "title": "Engineer"} INTO documents;
-- expect: doc1
GET 'doc1' FROM documents;

A SELECT on a document collection answers two columns: _id and document. This is also true when the SELECT names fields. The document cell then holds the named fields as one JSON object, and the nesting stays. SELECT _id, email answers the row d1 and {"_id":"d1","email":"x"}, not a column email. A field that a document does not carry is absent from its object; it is not null. Read a field from the JSON of the document cell.

-- expect: OK
INSERT INTO documents {"_id": "doc3", "email": "x", "name": "Dave"};
-- expect: {"_id":"doc3","email":"x"}
SELECT _id, email FROM documents WHERE _id = 'doc3';
DELETE FROM documents WHERE _id = 'doc3';

A WHERE reads nested fields with dots. On an array field, = is true when one element of the array is equal to the value. Each condition finds its own element: tags = 'red' AND tags = 'blue' is true when the array holds both values, and NOT (tags = 'red') is true when the array holds no element equal to 'red'. Brackets address ONE element of an array by its position, and the first position is 0.

-- expect: OK
INSERT INTO documents {"_id": "doc2", "name": "Carol", "address": {"city": "Oslo"}, "tags": ["red", "blue"]};
-- expect: Carol
SELECT name FROM documents WHERE address.city = 'Oslo';
-- expect: Carol
SELECT name FROM documents WHERE tags = 'red';
-- expect: Carol
SELECT name FROM documents WHERE tags = 'red' AND tags = 'blue';
-- expect: Carol
SELECT name FROM documents WHERE tags[0] = 'red';

INSERT with an _id that exists replaces the whole document, exactly as PUT does, and the answer is the same 1 row(s) inserted. A field that the new document does not carry is gone. INSERT never refuses an _id that exists, and it never keeps two documents under one _id. Use UPDATE to change some fields and keep the others.

-- expect: OK
INSERT INTO documents {"_id": "doc2", "name": "Carol", "address": {"city": "Bergen"}};
-- expect: Bergen
SELECT address.city FROM documents WHERE _id = 'doc2';
-- expect: 0 rows
SELECT name FROM documents WHERE tags = 'red';
-- expect: 3
SELECT COUNT(*) FROM documents;

INSERT … IF NOT EXISTS is the strict form for a document. It refuses an _id that exists with duplicate key: '<_id>' already exists in '<collection>', and it writes nothing. A document without _id gets a new _id and is written. The refusal is final, so a client must not retry it. Inside a transaction, an _id that exists refuses the whole transaction, as for a table row.

-- expect: OK
INSERT INTO documents {"_id": "doc9", "name": "Grace"} IF NOT EXISTS;
-- error: duplicate key: 'doc1' already exists in 'documents'
INSERT INTO documents {"_id": "doc1", "name": "Mallory"} IF NOT EXISTS;
-- expect: Bob
GET 'doc1' FROM documents;
DELETE FROM documents WHERE _id = 'doc9';

One INSERT writes many documents when the bodies follow each other with a comma between them. Each body is written exactly as a single INSERT writes it: a body without _id gets a new _id, and a body with an _id that exists replaces that document. A refused body, for example a body that is not a JSON object or a value that a UNIQUE index holds, refuses the whole statement, and no document of it is written. The answer counts the documents. Use this form to load many documents. IF NOT EXISTS is not allowed on this form.

-- expect: 3 row(s) inserted
INSERT INTO documents {"_id": "m1", "n": 1}, {"_id": "m2", "n": 2}, {"_id": "m3", "n": 3};
-- expect: 3
SELECT COUNT(*) FROM documents WHERE n > 0;
DELETE FROM documents WHERE n > 0;

A document field can hold a value in MongoDB Extended JSON form. The field compares as the value that the form wraps. {"$date": {"$numberLong": "<ms>"}} is a timestamp. {"$numberLong": "..."} and {"$numberInt": "..."} are integers. {"$numberDouble": "..."} is a number. {"$oid": "..."} is text. kd-tool import writes each date in this form. WHERE and ORDER BY read the wrapped value. A WHERE compares a date against the number of microseconds after the epoch.

-- expect: OK
INSERT INTO documents {"_id": "e1", "name": "Launch", "when": {"$date": {"$numberLong": "1704067200000"}}};
-- expect: OK
INSERT INTO documents {"_id": "e2", "name": "Review", "when": {"$date": {"$numberLong": "1735689600000"}}};
-- expect: Review
SELECT name FROM documents WHERE when > 1704067200000000 ORDER BY when DESC LIMIT 1;

UPDATE and DELETE work on documents with any WHERE, the same predicate as SELECT (AND, OR, NOT and parentheses):

UPDATE documents SET age = 31 WHERE name = 'Alice';
-- expect: 31
SELECT age FROM documents WHERE name = 'Alice';
UPDATE documents SET age = 33 WHERE name = 'Alice' AND (age = 31 OR age > 40);
-- expect: 33
SELECT age FROM documents WHERE name = 'Alice';
DELETE FROM documents WHERE _id = 'doc2';
-- expect: 4
SELECT COUNT(*) FROM documents;

A SET path with brackets writes ONE element of an array. The other elements keep their values, and the array keeps its length. A path that names an array without a position is refused, because a write does not map over the elements. A position past the end of the array is refused too. A SET does not extend an array. Write the whole array, or PUT the whole document, to make the array longer.

-- expect: OK
INSERT INTO documents {"_id": "doc7", "lines": [{"sku": "A"}, {"sku": "B"}]};
UPDATE documents SET lines[0].sku = 'Z' WHERE _id = 'doc7';
-- expect: Z
SELECT lines[0].sku FROM documents WHERE _id = 'doc7';
-- expect: B
SELECT lines[1].sku FROM documents WHERE _id = 'doc7';
-- error: a write does not map over its elements
UPDATE documents SET lines.sku = 'Q' WHERE _id = 'doc7';
-- error: position 5 is past the end
UPDATE documents SET lines[5].sku = 'Q' WHERE _id = 'doc7';
DELETE FROM documents WHERE _id = 'doc7';
-- expect: 4
SELECT COUNT(*) FROM documents;

A SELECT path that continues after an array maps over the elements. The result goes in the place of the array: SELECT lines.sku gives {"lines":["A","B"]}. Two paths that continue after the SAME array, for example lines.sku and lines.qty, are refused, because one array place cannot hold both results. A path and the array itself (lines and lines.sku) are refused for the same reason. Select one of the paths in each query, or select the whole array.

-- expect: OK
INSERT INTO documents {"_id": "doc8", "lines": [{"sku": "A", "qty": 1}, {"sku": "B", "qty": 2}]};
-- expect: ["A","B"]
SELECT lines.sku FROM documents WHERE _id = 'doc8';
-- error: one result cannot hold both
SELECT lines.sku, lines.qty FROM documents WHERE _id = 'doc8';
DELETE FROM documents WHERE _id = 'doc8';

UPSERT writes one document per body, keyed by a field of the body and not by _id. The key fields come after ON. One match replaces the whole body and keeps the _id of the document it matched. No match inserts a new document. More than one match is refused, and then nothing is written. A key of two fields matches a document that carries both. An index on a key field, or one index over both key fields, speeds up the lookup. An upsert always finds a document that the cluster acknowledged before the upsert started, on any node. Two upserts of the same new key can run at the same time on two nodes, and both then insert.

UPSERT INTO documents ON (name) {"name": "Alice", "age": 32};
-- expect: 32
SELECT age FROM documents WHERE name = 'Alice';
-- expect: 4
SELECT COUNT(*) FROM documents;

A document collection takes an index too. The index names one field, or several fields in order. A WHERE that gives a value for the first fields of the index uses the index. A field that holds an array gets one index entry for each element.

A read over a document collection decodes only the top-level fields the statement names: the WHERE fields, the ORDER BY fields and the selected fields. SELECT * decodes the whole document, for the rows the statement returns.

An index on one field also serves ORDER BY <field> LIMIT n. The query walks the index in the order of the sort and reads only the documents of the page, not the whole collection. When the WHERE names its own index, the walk keeps only the documents that WHERE index found, so a sorted search over a common value costs the page and not every match. EXPLAIN shows the walk as index-ordered scan.

A LIMIT on an unsorted search bounds the index read too. The query reads the index entries of the value one at a time and stops at the last row of the page, so a search for a common value costs the page and not every entry.

A WHERE that gives a value for every field of a second index uses that index as well. The query reads the entries of each index together and keeps the documents that every index names, so it fetches the documents that match all the values and not the documents of the first value alone. EXPLAIN shows the second index as index intersection.

A WHERE _id = '<id>' reads the one document with that _id, and it uses no index, even when the WHERE names an indexed field too. EXPLAIN shows it as point lookup.

A WHERE _id IN ('<id>', …) reads the named documents. The query does one read for each different value, and it uses no index. A repeated value costs one read. The query checks the whole WHERE on each document it reads, so a second comparison still removes the documents that do not match. The rows come back in _id order, as they do from a scan. EXPLAIN shows the plan as id set lookup. A SELECT, an UPDATE and a DELETE all take it, and they take it before any index, because the statement already names the keys it wants.

An UPDATE and a DELETE select their documents through the same index, for a comparison that the top level of the WHERE joins with AND. The statement fetches each candidate document that the index names and checks the whole WHERE on it, so it changes the documents that a SELECT with that WHERE returns. An IN list on the first field of an index uses the index as well, for a SELECT, an UPDATE and a DELETE: the statement reads the index once for each value. DESCRIBE UPDATE and DESCRIBE DELETE (or EXPLAIN UPDATE and EXPLAIN DELETE) show that plan, and they run no write.

CREATE INDEX idx_documents_name_age ON documents (name, age);
-- expect: 32
SELECT age FROM documents WHERE name = 'Alice' AND age = 32;
-- expect: 32
SELECT age FROM documents WHERE name = 'Alice';
DROP INDEX idx_documents_name_age;
CREATE INDEX idx_documents_name ON documents (name);
-- expect: index scan
DESCRIBE UPDATE documents SET age = 33 WHERE name = 'Alice';
-- expect: index scan
DESCRIBE DELETE FROM documents WHERE name = 'Alice';
-- the index covers `name`; the whole `WHERE` is checked again on each candidate
-- expect: index scan
DESCRIBE UPDATE documents SET age = 33 WHERE name = 'Alice' AND age = 32;
-- an `IN` list on the indexed field reads the index once for each value
-- expect: index scan (in-list)
DESCRIBE SELECT age FROM documents WHERE name IN ('Alice', 'Bob');
-- expect: index scan (in-list)
DESCRIBE DELETE FROM documents WHERE name IN ('Alice', 'Bob');
-- a `WHERE` on the `_id` values reads them directly, with no index
-- expect: id set lookup
DESCRIBE DELETE FROM documents WHERE _id IN ('e1', 'e2');
-- a read takes the same plan as the write
-- expect: id set lookup
DESCRIBE SELECT age FROM documents WHERE _id IN ('e1', 'e2');
-- the `_id` values win over the index, and the whole `WHERE` is checked on each document
-- expect: id set lookup
DESCRIBE SELECT age FROM documents WHERE _id IN ('e1', 'e2') AND name = 'Alice';
-- expect: 32
SELECT age FROM documents WHERE name = 'Alice';
CREATE INDEX idx_documents_age ON documents (age);
-- expect: Alice
SELECT name FROM documents WHERE name = 'Alice' AND age = 32;
DROP INDEX idx_documents_name;
DROP INDEX idx_documents_age;

Key-value collections

A key-value collection holds one value per key.

-- expect: OK
CREATE COLLECTION cache TYPE KV;
-- expect: OK
PUT 'key1' 1000 INTO cache;
-- expect: OK
PUT 'key2' 2000 INTO cache;
-- expect: key1
GET 'key1' FROM cache;
-- expect: 1 row(s) deleted
DELETE 'key1' FROM cache;

The keyed DELETE '<key>' FROM <collection> counts the key only when it was there. A key that the collection does not hold answers count: 0, on a single node and on a cluster. The same rule applies to the keyed form on a document collection. On a document collection with an index, the keyed form also removes the index entries of the document, as DELETE … WHERE does.

The keyed form is for a KV or a DOCUMENT collection. A table refuses it: use DELETE FROM <table> WHERE <pk> = …, which keeps the indexes and the row clock correct. A ledger refuses it too, because a ledger is append-only.

-- expect: 0 row(s) deleted
DELETE 'key1' FROM cache;

A SELECT on a key-value collection gives the same two columns GET gives: key and value. The WHERE reads those two names, and no other name.

-- expect: key2
SELECT * FROM cache;
-- expect: 2000
SELECT * FROM cache WHERE key = 'key2';

SHOW COLUMNS and DESCRIBE report the same two columns. The key key is Text and it is the primary key. The value column reports Any, because one collection can hold a value of any type: an Int at one key and a Text at the next. Any is not a type that a CREATE TABLE accepts, so a client that copies a schema refuses it and does not guess a type.

-- expect: Any
SHOW COLUMNS FROM cache;

Files

A file is a row like any other row. The row key is the file name, and the value the database keeps is the file's MANIFEST: the size, the hash of the whole file, and the list of the content blocks the file is made of. The bytes live in the block store, not in the row.

There is no size limit. A file is cut into blocks of at most 128 KiB and each block travels as its own replication entry, so a large file costs more entries and never a larger one.

The boundaries follow the content, not a fixed offset. Two files that share a long run of bytes share the blocks of that run, and the database stores each block once. An insert near the front of a file therefore re-writes one block, not every block after it. The INSERT … FILE path, the Python client and the JavaScript client all cut a file the same way, so the same file stored by any of the three shares every block.

A block is stored once, by content. Writing the same file twice under two names writes two manifests and no new blocks.

A file write keeps the cells of a row that exists. In a table, a file can belong to a row that has other columns, for example a title or an owner. A file write (INSERT … FILE, put_file, or the embedded engine) on a key whose row exists keeps every cell of that row. It writes or replaces only the file. A key with no row gets a new row: the file name in the primary-key column, and NULL in every other column. A second file write on the same key replaces the file, and the cells stay. In a collection, the manifest IS the row, so a file write replaces the row. In the example, the SELECT still returns ana.

CREATE TABLE reports (name TEXT PRIMARY KEY, owner TEXT);
INSERT INTO reports (name, owner) VALUES ('q3.pdf', 'ana');
INSERT INTO reports FILE '/srv/in/q3.pdf' VALUES ('q3.pdf');
SELECT owner FROM reports WHERE name = 'q3.pdf';

A file write can set the fields of its row. Add WITH and a JSON object after VALUES ('<name>'). The client libraries take the same object as fields. In a table, each key names a column of the file row, and the value goes into that column as an INSERT puts it. The other cells of a row that exists stay. In a collection, the document is the manifest and the fields of the object. The file write refuses an unknown column, a key column with a value that is not the file name, and the keys name, path, size, hash, chunks, chunk_count and _id in a collection. An empty object is no object.

A row permission judges the row that a file write leaves. A user with only a MATCH or WHERE permission writes a file when the INSERT permission allows the row before the write (when one exists) and the row after it, with the fields of WITH. The check and the write are one step: when another write changes the row between them, the file write fails with a conflict and writes nothing, and the client can send it again. So under GRANT ALL ON owned_reports TO MATCH owner, the user ana writes INSERT INTO owned_reports FILE '/srv/in/q3.pdf' VALUES ('q3.pdf') WITH {"owner": "ana"}. A file write on the row of another user, or a write that leaves a row that ana may not write, gets permission denied.

In a BEGIN … COMMIT block, WITH on a table row that exists is refused: run that INSERT … FILE alone, or UPDATE the row in the block.

CREATE TABLE owned_reports (name TEXT PRIMARY KEY, owner TEXT);
GRANT ALL ON owned_reports TO MATCH owner;
INSERT INTO owned_reports FILE '/srv/in/q3.pdf' VALUES ('q3.pdf') WITH {"owner": "ana"};

In a document or KV collection, a row is a file only when it has the manifest shape. The manifest shape is a chunks array of 64-hex block hashes and a chunk_count that is its length. A file write makes this shape. Every other document is a row: SELECT FILE does not list it, getFile answers that there is no such file, and kd-tool backup dumps it as a row only. So a document of your application can have a chunk_count, size or hash field. Do not write a chunks array of block hashes into a document that is not a file.

A file needs a collection name and a file name. Every file write (INSERT … FILE, put_file, a file stream, the embedded engine) refuses an empty collection name or an empty file name. The error names the empty field, for example a file needs a file name: the file name is empty; nothing was written. Nothing is stored.

There are two ways in and two ways out.

INSERT … FILE reads a path on the SERVER. On a cluster the path must exist on the shard leader of the target collection, not on the node your client is connected to. SELECT FILE lists the files, and with INTO it writes one back to a path on the server.

CREATE TABLE documents (name TEXT PRIMARY KEY);
INSERT INTO documents FILE '/srv/in/report.pdf' VALUES ('report.pdf');
SELECT FILE FROM documents;
SELECT FILE FROM documents WHERE name = 'report.pdf' INTO '/srv/out/report.pdf';

A file and other rows commit together. INSERT … FILE can be a statement of a server transaction: one transaction() call, or a BEGIN … COMMIT block of the command line. The file and the other rows then commit in one transaction: the database never keeps the file without the rows, or the rows without the file. When the transaction fails (for example, on a conflict with another write), it writes no file row and no other row. A transaction writes a file row once: a second write of the same file row in the same transaction is refused.

CREATE TABLE photos (name TEXT PRIMARY KEY);
CREATE TABLE photo_links (photo TEXT PRIMARY KEY, project TEXT);
BEGIN;
INSERT INTO photos FILE '/srv/in/site-42.jpg' VALUES ('site-42.jpg');
INSERT INTO photo_links (photo, project) VALUES ('site-42.jpg', 'p-7');
COMMIT;

The client libraries send and receive the bytes themselves, so neither side needs a path on the server. Both libraries verify every block against its hash, and the whole file against the file hash, before get_file returns. See the Python and JavaScript client pages.

await sock.put_file("documents", "report.pdf", data)
data = await sock.get_file("documents", "report.pdf")
info = await sock.file_info("documents", "report.pdf")   # size, hash, chunks

await sock.putFile('documents', 'report.pdf', data);
const bytes = await sock.getFile('documents', 'report.pdf');
const info = await sock.fileInfo('documents', 'report.pdf');

The embedded engine stores a file in a transaction. In @kinedb/embedded, a file item of transaction() ({ putFile: [collection, name, data] }) stores the file together with the rows of the same call. When one item fails, the call stores neither the file nor the rows. It cuts the same blocks as the server and the client libraries.

A manifest names only blocks that its file uploaded. The server refuses a manifest that names a block this file did not send. A block counts when an upload of this file sent it, or when the file's current manifest already names it. The client libraries send every block before the manifest, so they never see this answer. Nothing was written when you see it:

InsertFile manifest names chunk <hash> that this file never uploaded; nothing was written

A client does not send a block that the shard already holds. The block can come from any file of the same table on the same shard. The client asks the server which block hashes the shard holds, sends only the other blocks, and then sends the manifest. The manifest apply checks every block again. When it refuses, the client sends the skipped blocks and the manifest once more. The client asks only when the login gave it the skip capability, and the server answers only a caller that may read the WHOLE collection. A caller with a row policy is told that nothing is held, so it sends every block. Only an upload by a caller that may read the whole collection makes its blocks count as held for a later upload. The metric files.chunk_bytes_received_total in SHOW METRICS counts the block bytes a node received. The metrics files.chunks_served_total and files.chunk_bytes_served_total count the blocks a node answered to a chunk read, and their bytes.

A file read obeys the row policy of the file. getFile, each block read and SELECT FILE check the caller's SELECT rights on the file before they answer. In a table, a file has a row whose key is the file name, and the policy judges that row, as a SELECT * and a sync do. In a collection, the policy judges the manifest, because the manifest is the row. A superuser and a caller with a SELECT grant on the whole collection read every file. A file that the policy hides gets the same answer as a file that does not exist, and SELECT FILE does not list it.

A DELETE of a file row removes the file. After the commit, getFile, fileInfo and SELECT FILE answer that there is no such file. This is true for every form of DELETE (a WHERE, a range, no WHERE) and inside a transaction. On a cluster, each node removes the file of its own shard. A transaction that fails keeps the file.

An upload that never sends its manifest expires. The server keeps a record of each block that an upload sent, until the manifest of the file arrives. When a client stops before the manifest, a sweep marks each record, and a later sweep deletes a record whose mark is older than the TTL. The GC can then reclaim the block. The sweep runs every hour (KINEDB_FILE_UPLOAD_SWEEP_INTERVAL_MS, 0 turns it off), and the TTL is 24 hours (KINEDB_FILE_UPLOAD_TTL_MS, at least 2 × the interval). A block that a client sends again gets a new record.

A node that is still joining the cluster passes a file write on. It holds no data of its own, so it sends each put_file_chunk and put_file frame, as it came, to the node that it joins. That node writes the file. A bulk load travels the same way. When the joining node knows no node to send it to, it answers:

passive joiner: no leader known yet and no --join target configured; retry shortly

Nothing was written when you see this, so sending the file again is safe. The server marks the answer as a temporary one, and both client libraries expose that as retryable on the error, so a retrying client waits and sends it again rather than giving up.

A file read is routed like a row read. get_file and file_info go to the node that serves the collection's shard right now. A node that holds a copy of the shard but is not current on it (it has no read lease from its leader yet, or it lost contact with the leader) passes the read on to a node that is, and never answers from its own copy. When no current node is reachable, the answer is a temporary one with retryable set:

read reached a node that does not currently serve shard(s) [...] (passive / no leader
contact / no read lease); retry shortly against a current member

A copy holds a read lease only while reads reach it. When no read reaches a copy for one lease window, its leader stops renewing the lease, so writes do not wait for that copy. The next read that reaches it asks for the lease again and waits a short time for it (about one to two heartbeats). Then the copy answers the read. If the lease does not come in that time, the copy passes the read on.

file not found is a final answer: it comes only from a node that is current on the shard, so a file the cluster has stored is never reported as missing.

Indexes

A secondary index speeds up a WHERE on its column. It speeds up a SELECT, and it speeds up the row selection of an UPDATE and a DELETE, on a table and on a document collection alike. A write reads the index for a comparison that the top level of the WHERE joins with AND: an equality on the leading columns of the index, and a range on the column after them. The write then reads each candidate row the index names and checks the whole WHERE again on it, so it reads the candidates and not the whole table, and it changes the rows that a SELECT with that WHERE returns.

A write reads the index for an IN list on the leading column of the index too, and for a MATCH on a column with a FULLTEXT index. The IN list reads the index once for each value. The MATCH reads the index once for each word and keeps the rows that hold every word. A phrase then checks the order of its words on each of these rows. A write takes the same plan as a SELECT with the same WHERE.

DESCRIBE SELECT (or EXPLAIN SELECT) shows the plan of a query, and DESCRIBE UPDATE and DESCRIBE DELETE show the plan of the row selection of a write. A DESCRIBE runs no statement: DESCRIBE UPDATE writes nothing and reads no row. A user who may not read a collection gets permission denied for the plan of a query on it. The plan of a write needs the SELECT permission OR the permission of the write itself: a user with UPDATE and no SELECT gets the plan of its UPDATE, and a user with neither gets the same permission denied as for the plan of a query. The plan of a join shows the row count of each table, so it needs a permission on the whole table: a MATCH or WHERE permission is not sufficient.

An index lives with the shards of its table: every shard of the table holds the index entries of its own rows. When a shard splits, its index entries split with it. When a node receives a shard, it receives the shard's index entries with it. CREATE INDEX builds the index on every shard of the table, on the node that leads that shard. SHOW ROOTS lists one row per shard for an index of a split table, named index#shard. In a cluster, the internal tables that hold the state of each shard (the transaction cursors, holds and index fences) are listed as one row per shard that the node hosts, named <table>#<shard>. Each row is a digest of the rows of that shard, so the nodes that host one shard show the same value. If the node cannot read such a table, each of its rows holds unreadable:<host>:<port>:<table>#<shard> instead of a digest, and the root compare of the cluster fails for that table.

An index belongs to the database of its table. CREATE INDEX, CREATE UNIQUE INDEX and DROP INDEX act on the table of the current database, which USE <database> selects. A qualified table name in CREATE INDEX … ON mydb.accounts names the database of the table (see Qualified table names). So two databases may each hold a table of the same name with an index of the same name, and the two indexes stay separate: each one indexes the rows of its own table only, and DROP INDEX in one database leaves the other index in place. SHOW INDEXES lists the indexes of the current database.

An index name is unique inside its database. Two tables of one database cannot both carry an index of the same name, and CREATE INDEX refuses the second one and names the table that holds the name. This is what lets DROP INDEX <name> take the name alone: the name selects one index in the database. Give each index its own name, or drop the first index before you use its name again.

CREATE INDEX IF NOT EXISTS does nothing when the same table already holds an index of that name, and answers OK. A name that another table holds is still refused. DROP INDEX IF EXISTS does nothing when no index has that name, and answers OK. Without IF EXISTS, a DROP INDEX of an absent name is refused with index not found. Two clients that send the same plain DROP INDEX at the same time get one OK and one index not found, on a single server and on a cluster.

Two clients can send the same CREATE INDEX at the same time, for example two app instances that run one schema file at start. Exactly one statement builds the index. The plain form of the other statement is refused with index already exists. The IF NOT EXISTS form waits for the first build: it answers OK when the index is ready, and an error that asks for a retry when the first build fails. This is true on a single server and on a cluster. On a single server, a write-only index that no build finishes (its rollback failed) is refused with index already exists, also with IF NOT EXISTS: run DROP INDEX <name>, then send the statement again.

A CREATE INDEX that fails during its build tries to remove its index before it answers. The answer tells you the result:

• An error that asks for a retry: the server removed the index. Send the same statement again, and it builds the index.
• An error that says the rollback could NOT be proposed: the server lost the lead of its metadata group, and the index stays write-only for a short time. This error does not ask for a retry. The new leader of the metadata group removes the index. SHOW INDEXES FROM <table> shows when it is gone; then send the statement again. DROP INDEX <name> removes it at once.
• An error that says the outcome is unknown: the server proposed the removal, but it cannot confirm it. Run SHOW INDEXES FROM <table>. If it lists the index as write-only, run DROP INDEX <name> (or wait until the leader of the metadata group removes it), then send the statement again. Do not use CREATE INDEX IF NOT EXISTS here: it can wait for the removal, and then ask for a retry again.

Since B2945 the leader of the metadata group removes each write-only index whose build no longer runs: a build on a server that stopped, or on a server that lost the lead.

Since B2933 a retryable answer comes only after the removal applied. Some failures that answered with a plain error before now answer that the outcome is unknown, when the server cannot confirm the removal.

Writes continue while CREATE INDEX builds the index. Before each shard scans its rows, it writes a fence into its log. A write that the shard orders after the fence, but that the server prepared before the index existed, holds no entry for the new index. The shard refuses that write whole, and nothing is written. The server prepares the write again against the new index and sends it again, up to 3 times, so the client normally sees only the OK. When the retries end, the client gets an error that asks for a retry (the text names [B2250_STALE_BUILD]). Send the statement again. Every row of the table is then in the index, on every replica.

On a single node, the build writes its entries in parts of about 192 KiB. Each part holds the engine only while it writes its entries, so a statement can wait for the write of a part. A part does not read the rows of the table again (B2456).

CREATE INDEX idx_owner ON mydb.accounts (owner);

CREATE INDEX idx_owner ON accounts (owner);
-- expect: 1 rows
SHOW INDEXES;
-- expect: 1 rows
SHOW INDEXES FROM accounts;
-- expect: index scan
DESCRIBE SELECT * FROM accounts WHERE owner = 'Alice';
-- expect: index scan
DESCRIBE UPDATE accounts SET balance = 0 WHERE owner = 'Alice';
-- expect: index scan
DESCRIBE DELETE FROM accounts WHERE owner = 'Alice';
-- an `IN` list reads the index once for each value, for a read and for a write
-- expect: index scan (in-list)
DESCRIBE SELECT * FROM accounts WHERE owner IN ('Alice', 'Bob');
-- expect: index scan (in-list)
DESCRIBE UPDATE accounts SET balance = 0 WHERE owner IN ('Alice', 'Bob');
DROP INDEX idx_owner;

An index takes a column of type TEXT, INT, TIMESTAMP, BOOL or FLOAT. On a FLOAT column the index holds numbers in numeric order, so a range and an ORDER BY read it in the order of the values. An INT value compares with the column as its number: price = 10 finds the row that holds 10.0. -0.0 and 0.0 are one value. An index never rounds a value: a write into an indexed FLOAT column of a value that is not an exact double (a text, a bool, an integer past 2^53) is refused, names the value, and writes nothing.

CREATE INDEX and CREATE UNIQUE INDEX refuse a column of another type (BYTES, BLOB, VECTOR), also on an empty table and also in an index of several columns. The refusal names the column and creates nothing. An ARRAY column takes an index of its own kind, with one entry for each distinct element: see Array columns (the paragraph Index).

-- expect: OK
CREATE TABLE raw_keys (id INT PRIMARY KEY, k BYTES, emb VECTOR(2));
-- error: an index cannot hold a
CREATE INDEX raw_keys_k ON raw_keys (k);
-- error: an index cannot hold a
CREATE UNIQUE INDEX raw_keys_emb ON raw_keys (emb);
-- expect: OK
INSERT INTO raw_keys (id, k, emb) VALUES (1, X'00ff', [1, 2]);
DROP TABLE raw_keys;

An index that a binary before B3151 created on such a column stays. A write of a value into that column fails; DROP INDEX removes the index.

CREATE INDEX idx_balance ON accounts (balance);
-- expect: index scan
DESCRIBE SELECT * FROM accounts WHERE balance > 100;
DROP INDEX idx_balance;

SHOW INDEXES answers nine columns: index_name, table_name, columns, unique, status, fix, progress, started_at and elapsed_s. status has three values:

• OK: the planner uses the index. fix is empty.
• WRITE-ONLY: the CREATE INDEX build has not ended, or it stopped. fix names the repeat of the same CREATE INDEX and the DROP INDEX, for example repeat CREATE INDEX idx_balance ON accounts (balance), or DROP INDEX idx_balance. An index from before B1354 gets DROP INDEX idx_balance, then CREATE INDEX … instead.
• UNUSABLE: the tree predates the B934 layout. fix is the DROP INDEX / CREATE INDEX pair that repairs it.

The status is derived from the catalog, never stored. Every use of an UNUSABLE index is an error that carries the same fix, never a silent full scan, and GET /health answers 503 while one remains. The repair is the ordinary DDL, on a live node.

The planner does not use a WRITE-ONLY index. Each write to the table also writes the index, and the planner starts to use the index when the build ends. The last three columns show a build that runs on the node that answers:

• progress: the phase of the build. scan rows=1200345 shards=1/4 while the build reads the table, parts 120/563 while it writes the entries. On a cluster the node that coordinates the build shows slices shards=2/5, and the leader of each shard adds its own scan, for example scan rows=8100 shards=0/1 (shard 7).
• started_at: the start of the build, RFC 3339 in UTC.
• elapsed_s: the seconds since the start.

The three columns are empty (elapsed_s is NULL) when no build of the index runs on the node that answers. A WRITE-ONLY index with empty progress columns on every node has no build: its build stopped. On a cluster, the same CREATE INDEX starts the build again; a CREATE INDEX over a build that runs is refused and names that build. DROP INDEX removes the index.

CREATE UNIQUE INDEX makes the index a constraint. Two rows cannot hold one value in the indexed column. A write that would give a second row an existing value is refused with duplicate key: '<value>' already exists in unique index '<index>' on '<table>', and nothing is written. The refusal is final, so a client must not retry it. A row never collides with itself, so a write that keeps the row's own value is accepted. A NULL never collides with another NULL, so any number of rows may leave the column empty. A composite unique index collides on the whole key only. CREATE UNIQUE INDEX over data that already holds a duplicate is refused, and no index is created. A FULLTEXT index cannot be UNIQUE.

The same rule applies to the writes of a transaction() call and of a stored function body, on a standalone server and on a cluster. Two rows of one batch with one value are refused. A refusal rejects the whole batch, and nothing is written. The check uses the final state of the whole batch, on a standalone server and on a cluster. So a value that the batch frees from another row is accepted: one batch can swap two values between two rows, or delete the holder of a value and insert the same value into another row.

CREATE UNIQUE INDEX idx_owner_unique ON accounts (owner);
-- error: duplicate key
INSERT INTO accounts (account_id, owner, balance, created_at)
VALUES (11, 'Alice', 1.0, 11000000);
-- expect: 0 rows
SELECT owner FROM accounts WHERE account_id = 11;
-- expect: OK
INSERT INTO accounts (account_id, owner, balance, created_at)
VALUES (11, 'Ivan', 1.0, 11000000);
DELETE FROM accounts WHERE account_id = 11;
DROP INDEX idx_owner_unique;

A document collection takes a unique index on a field, with the same rules. A document that does not carry the field never collides. A field that holds an array is checked one element at a time.

The rules hold across shards. Two rows on two different shards cannot hold one value either. On a table with more than one shard, a write that adds an index entry asks every shard whether it already holds the value, and one shard that says yes refuses the write. The refusal text is the same on one shard and on many. CREATE UNIQUE INDEX on a table with more than one shard reads every shard before it creates anything: each shard builds its own part, then the server merges the parts and refuses the statement when one value appears on two rows. It names up to three such values and creates nothing.

Because the check is a vote of every shard, a unique write on a sharded table is a global transaction over all the shards of the table. Writes of different values do not wait for each other: to write many rows fast, send the writes from several connections at the same time.

kd-tool import --skip-existing is refused when the target collection has a unique index AND more than one shard. Import without --skip-existing, or drop the index first.

Transactions

A transaction is ONE complete list of statements in ONE call. The server, and the embedded engine, run all of the statements together as one atomic write: all of them are written, or none. No transaction stays open between two calls, so no client can make the server wait for it.

A client library sends the list with transaction():

await ws.transaction([
  ['INSERT INTO accounts (account_id, owner, balance, created_at) VALUES ($1, $2, $3, $4)',
   [5, 'Eve', 500.0, 5000000]],
  ['UPDATE accounts SET balance = balance - $1 WHERE account_id = $2', [10.0, 1]],
], { token: 'order-77', retry: true });

• The JavaScript and Python clients have transaction(statements, {token, retry}) on the WebSocket and on POST /sql. A statement is a string, [sql] or [sql, params]. See JavaScript and Python.
• The embedded engines have transaction(items, {token}). An item can also be a file, so a file and a row commit together. See Embedded.
• Rust: WsClient::transaction on a server, Db::transaction in embedded mode.

BEGIN, COMMIT and ROLLBACK are not statements that a client sends alone. A WebSocket statement, POST /sql and the embedded sql() refuse each of them with this error, and apply nothing:

BEGIN, COMMIT and ROLLBACK are not statements that a client sends alone: send the complete transaction in one call with transaction(). To read and then write in one transaction, CALL a function. Nothing was applied.

The kinedb command line keeps the BEGIN … COMMIT form as text. In a .sql file, in -e and in the interactive shell, it sends a complete BEGIN … COMMIT block as ONE transaction. A BEGIN … ROLLBACK block is not sent. A BEGIN with no COMMIT or ROLLBACK is an error, and nothing is sent. In the interactive shell, the prompt changes to txn> after BEGIN. The shell keeps the statements on your computer and sends them at COMMIT. ROLLBACK drops them. The examples on this page use this form:

BEGIN;
INSERT INTO accounts (account_id, owner, balance, created_at)
VALUES (5, 'Eve', 500.0, 5000000);
COMMIT;
-- expect: 4
SELECT COUNT(*) FROM accounts;

A transaction is NOT an interactive transaction. It holds no lock, and it cannot read a row, decide, and then write:

• The answer is OK ({"type":"created"}), or the error. The answer does not give the number of rows that the transaction changed.
• A SELECT in the list gives no rows. Read before the transaction, or after it. For a read, a decision and a write in one atomic step, write a function and CALL it (see Functions and tickers).
• When one statement fails (for example, a syntax error or a table that does not exist), the answer is its error, and nothing of the transaction is written.
• To retry, send the whole transaction again. The retry option of the client does this for an error that has retryable set (for example the retry shortly answer of a node that is not current, below). A retry never sends a part of the transaction.
• Two transaction() calls on one connection are two separate transactions.
• A transaction runs in one database, on the branch main. A transaction on another branch is refused. Statements on a branch work alone.
• These statements cannot be in a transaction: BEGIN, COMMIT, ROLLBACK, USE, WATCH, UNWATCH, the user, role and grant statements (CREATE USER, GRANT, …) and a statement with a subquery. Send each of them alone.
• The server limits one transaction to 1,048,576 statements and to 64 MiB of SQL text. The write budget of the node can refuse a smaller transaction; the error then tells you to split it.

A write reads a current copy

An UPDATE and a DELETE read the rows they change, and a transaction reads the rows its statements start from. A node reads them from its own copy only when that copy is current (it holds a read lease, as for a SELECT). A node that is not current sends a one-shard write on to the leader of the shard, which reads its own current copy. So a write never answers row not found or a count of 0 for a row that the cluster acknowledged. A transaction over several shards cannot go to one leader. When the node is not current on one of them after a short wait, the transaction answers a temporary error, and retryable is set:

write translate reached a node that does not currently serve shard(s) [...] (passive / no
leader contact / no read lease); retry shortly against a current member

Nothing was written. Send the whole transaction again.

A write over several shards is acknowledged only after every current copy of each of those shards holds it, as for a one-shard write. So a SELECT on any node that answers from its own copy sees every write acknowledged before it, on one shard or on several.

The acknowledgement waits a limited time for each shard. When the leader of a shard has not applied its part of the write in that time, the write is still acknowledged as OK, because the write is committed. That leader then stops its read lease and each lease of its copies. No node answers a read of that shard from its own copy until the leader has applied the part. This is also true when a split moved the part to a new shard: the leader of the new shard waits in the same way. A read in that time waits a short time for the apply, then answers the temporary retry shortly error with retryable set. Send the read again.

A pushed offline change (a synced write) reads its row the same way. A node that is not current on the row's shard passes the push on to a current node, and never answers a conflict from its own copy. When no current node answers in time, the push fails with a temporary error, and every change stays pending on the device for the next push. A sync that brings changes down reads a current copy too. A node that is not current waits a short time to become current, and then answers. If it does not become current in that time, the sync fails with a temporary error, and the device keeps the rows it has.

A schema change is visible on the node that ran it

The rule: when a schema statement answers success, the node that the client is connected to has applied it. So the next statement on that connection reads the change. A CREATE TABLE followed by an INSERT into that table, or a CREATE FUNCTION followed by a CALL of it, does not answer table not found or function not found.

The cluster keeps the schema in one replicated log. Only the leader of that log writes to it. A node that is not the leader sends the statement on to the leader. The leader answers after its own apply. The node then waits until its own copy shows the change, and only then answers the client. A leader waits for its own apply before it answers.

The rule covers these statements:

• CREATE TABLE, CREATE COLLECTION, DROP TABLE, DROP COLLECTION and TRUNCATE.
• Every ALTER TABLE form.
• CREATE INDEX (the node waits until it can serve the index) and DROP INDEX.
• CREATE FUNCTION, CREATE OR REPLACE FUNCTION and DROP FUNCTION.
• CREATE DATABASE, DROP DATABASE, CREATE BRANCH and DROP BRANCH.
• The user, role and grant statements, ALTER USER … SET PASSWORD and ALTER CLUSTER ROTATE TOKEN KEY.
• SET CLUSTER <name> = … and SET CLUSTER QUORUM.
• CREATE TICKER and DROP TICKER.

The wait has a limit: 10 seconds on a node that sent the statement on, and a longer limit on the leader (60 seconds, and 90 seconds for CREATE INDEX by default). When the limit ends first, the statement answers an error that says the outcome is unknown, for example timeout waiting for local DDL apply of default.t after forward. The change is committed and can still apply. Do not send the statement again as it is: see A statement fails on a busy node.

The rule does not hold in these cases:

• A client on a different node. The answer does not wait for the other nodes. A client on a second node can read the old schema for a short time after the answer. When a write or a schema statement on that node names a table, a database or a branch that the node does not know yet, the node first catches up with the leader of the log and then checks the name one more time. A SELECT on that node is not guaranteed to see the change. To read your own schema change at once, use the connection that made it.
• A node outside the cluster configuration. A node that started to join, but that the cluster has not added yet, gets no copy of the log. It answers when the leader answers, and does not wait for its own copy. Its next statements go on to the leader, so the connection still reads the change. A node that the cluster added, but that does not vote yet, gets the log and waits, as every other node does.

Concurrent writes to the same row

A transaction does not lock the rows it writes. It checks them when it runs. UPDATE computes the new row from the stored row, and inside a transaction DELETE and an INSERT over an existing key also start from the stored row. At the commit the server checks that each such row is still the row that the statement read. When another client wrote that row first, the transaction writes nothing and answers:

calvin conflict: <n> ops rejected at apply (ReadValidated CAS failed on table '<table>': stored bytes do not match propose-time pre-image) — nothing applied, retry

This answer carries the retry flag, so a client with the retry option sends the whole transaction again. The retry reads the rows again.

A statement that writes a row which an earlier statement of the SAME transaction wrote starts from that earlier write. The check then uses the stored row from before the transaction, so the pair does not conflict with itself. For example, an INSERT of a document and an UPDATE … SET title = $1 of the same _id commit in one transaction. The stored row is the newest committed row, also when the server has not yet written it to disk. So a row that you committed a moment before does not cause a conflict.

A plain INSERT of a document writes the document at its _id. When two transactions insert the same new _id at the same time, both commit and the later one wins. The stored document is the whole body of one of them, never a mix. Use INSERT … IF NOT EXISTS to claim an _id.

The same check protects a single UPDATE statement, also one with a WHERE on another column. When two clients run UPDATE jobs SET state = 'processing', owner = 'a' WHERE state = 'new' at the same time, each row is written by one of them only, and a client whose statement meets a row that the other client wrote first gets the conflict. One statement is one transaction: it writes every row, or no row. The same is true when one row cannot take the change, for example SET n = n + 1 on a row whose n would overflow. The end of the answer tells you what it wrote:

• — nothing applied, retry: the statement wrote no row. The answer carries the retry flag. Send the statement again: the retry reads the rows again, so a row that another client took no longer matches.
• — <m> row(s) written, not retried: read the rows back: the statement wrote some rows and not others. A statement on one shard no longer gets this answer when a row changed or when a row cannot take the change. One case can still give it: a node that runs an older version. The answer does not carry the retry flag, because a statement such as SET n = n + 1 sent again would change those rows two times. Read the rows back, for example SELECT … WHERE owner = 'a', and decide.

A statement whose rows lie on more than one shard runs as ONE cross-shard transaction. It writes every row on every shard, or no row. Each shard checks its part before the transaction commits. When one shard refuses (a changed row, a split, a leader change, a table that a TRUNCATE or a DROP re-created), no shard writes a row. The answer then carries the retry flag, and a retry is safe. The node that runs the statement coordinates the transaction, also when the statement arrived forwarded from another node. When the node cannot learn the outcome of the transaction inside its wait budget, the answer says that the outcome of the cross-shard transaction is unknown and does not carry the retry flag: read the rows back before you send the statement again. A straddling statement meets the same write budget as a statement on one shard: above it, the answer is the batch of … exceeds this node's write-pipeline budget error. Split the statement.

A kd-tool import --skip-existing batch whose documents lie on more than one shard is one cross-shard transaction too. Each shard skips the documents whose _id it already holds and writes the others. A document whose _id comes again later in the same batch is skipped. The answer counts the written documents and the skipped documents. When one shard refuses, no shard writes a document, the answer carries the retry flag, and a retry is safe.

A kd-tool import --skip-existing batch on one shard is one transaction too. The shard skips the documents whose _id it already holds and writes the others, with the same counts. When one document is refused, for example because another document already holds its value in a unique index, the batch writes no document. The answer is that refusal.

A single INSERT, DELETE or PUT statement, and an INSERT of a new key in a transaction, do not read the stored row. When two of them write the same key, both succeed and the later write wins.

A blind UPDATE does not read the stored row either. An UPDATE is blind when all of these are true:

• its WHERE is only <primary key> = <value>;
• every SET gives a literal value, such as SET state = 'done', never a value computed from a column, such as SET n = n + 1;
• the table has no secondary index, no unique index and no access policy;
• nothing before it in the same transaction or function read that row.

A blind UPDATE writes only the columns that it sets. When two clients set different columns of one row at the same time, both succeed and the row keeps both values. When they set the same column, the later write wins. When another client deleted the row first, the UPDATE writes nothing and does not bring the row back: a single statement answers count: 0, and in a transaction the other statements are still written. Every other UPDATE keeps the check above.

Use these tools instead:

• INSERT … IF NOT EXISTS claims a key. When two clients insert the same key at the same time, exactly one row is written, and the other client gets duplicate key. Inside a transaction, an existing key refuses the whole transaction.
• SET <column> = <column> + <n> and TRANSFER change a number by an amount. Concurrent increments on one row all count, in every database and on every branch. The server adds the amounts at apply time, so the increments compose and no client gets a conflict. The increments also compose with an in-flight cross-shard transaction (for example a TRANSFER between two shards) that increments the same row (B2633). One exception: the server must check that the result fits in an INT in every order of the pending increments. When the sum of the pending increments could leave the INT range, the increment is refused with a retryable error ([B35_HOLD] … could overflow in some order of the pending deltas — retry shortly). An increment of a row that an in-flight cross-shard transaction SETS (not increments) is refused retryably too. Retry the statement. The column must be an integer column that is not the primary key and not part of an index, and the WHERE must name the primary key only. In a transaction, the statements keep their order for one row: when a later statement of the same transaction writes or reads that row, the increment is checked like any other UPDATE. So SET n = n + 1 and then SET n = 5 commits n = 5. An increment whose result does not fit in an INT refuses the statement with integer overflow, and the row keeps its value.
• The token of transaction() makes a retry safe after a lost connection. It does not detect a concurrent write.

A transaction can carry an idempotency token: the token option of transaction(), or COMMIT WITH TOKEN '<token>' at the end of a BEGIN … COMMIT block of the command line. A second transaction with a token that committed applies nothing and answers OK. A client that lost its connection asks GET TRANSACTION STATUS whether the commit happened, and it retries only when it did not. The token is stored in the same commit as the rows of the transaction. A transaction that fails stores no token: the status stays UNKNOWN, and a retry with the same token runs the transaction.

In a cluster (--calvin), the token is stored with a row that the transaction writes, in the shard of that row. A shard snapshot, a split and a merge carry the token with the shard, so a node that gets the shard later also replays the retry. DROP TABLE and TRUNCATE remove the tokens with the rows of the table. So after a TRUNCATE, or after a DROP TABLE and a new CREATE TABLE with the same name, a retry with an old token runs the transaction again. The same is true for the (client_id, seq) rows of a sync push: a sync retry after a TRUNCATE or a DROP TABLE applies again.

BEGIN;
INSERT INTO accounts (account_id, owner, balance, created_at)
VALUES (6, 'Frank', 600.0, 6000000);
COMMIT WITH TOKEN 'token-unique-001';
-- expect: OK
GET TRANSACTION STATUS 'token-unique-001';

TRANSFER moves an amount from one row to another in one atomic step. It needs a server connection:

TRANSFER 10 OF balance ON accounts FROM 1 TO 2;

Time travel and history

Every commit keeps the old data readable. AS OF reads a table at a commit (HEAD is the newest), and SHOW COMMITS lists the commits. SHOW COMMIT <hash> shows one commit.

HEAD~N and SHOW COMMITS count the commits of the session's branch. A commit counts when it changes what the branch reads: a table of the branch, a column of the branch, or the branch itself. A write on another branch, a write in another database and a system write (for example a new user) do not count. The first row of SHOW COMMITS is HEAD, the second row is HEAD~1, and so on. Before a branch exists, the branch reads its base main, so the commits of main count there. SHOW COMMIT COUNT is different: it counts every retained commit of the node, on all branches.

An AS OF read never writes a commit. The server holds a new write in a buffer first, and a buffered write is not a commit until the node flushes the buffer. Until then, the buffered writes of the branch are ONE pending step on top of the newest commit. AS OF HEAD reads that pending step, so it answers your last write. HEAD~1 is the newest commit before the pending step, HEAD~2 the commit before that, and so on. An AS OF time at or after the time of the last buffered write also reads the pending step. Several writes can be in one pending step, so HEAD~1 is not always the state before your last statement. An example: an INSERT and then an UPDATE in the buffer are one step, and HEAD~1 answers the state before the INSERT. SHOW COMMITS lists only the commits, never the pending step.

An AS OF time before the pending step reads a commit: the commit just before the OLDEST commit with a wall_clock after that time. With no such commit, it reads the newest commit. The wall_clock of the commits does not always grow from one commit to the next. For example, a flush of an old buffer can come after a newer commit and get an older time. Each commit holds all the rows of the commits before it. So the newest commit with a time at or before yours can hold rows that were written after your time, and AS OF never reads that commit. As a result, a row written at or before your time can be absent from the answer when it is in a commit after that oldest commit. A merge commit counts by its own wall_clock.

The commits belong to one node: each node writes its own commits, so a commit hash and HEAD~N name history on the node that answers. For this reason a node never passes an AS OF read on to another node. A node that holds a copy of the table but is not current on it (it has no read lease from its leader yet, or it lost contact with the leader) can be behind the last acknowledged write. That node refuses the read with a temporary answer, and retryable is set:

read reached a node that does not currently serve shard(s) [...] (passive / no leader
contact / no read lease); retry shortly against a current member

A copy that no read reached for one lease window has no lease either. The first AS OF read after a period with only writes asks for the lease and waits a short time for it. If the lease does not come in that time, the read gets this answer, and a retry is served.

A node can receive the data of a table after a commit: it joined the cluster, or the cluster moved a shard of the table to it. The history of that node holds no true rows of the table before the receive. So AS OF, SHOW DIFF and CREATE BRANCH … AS OF refuse such a target with NotFound, and never answer 0 rows:

the AS OF target is older than the commit where this node received the data of table t,
so the history of this node holds no true rows of t at that target; use a later target,
or read on a node that held t then

A node can also STOP holding the data of a table: the cluster removed it from the voters of a shard (a shed, a move), a split or a merge left it with no copy of a new shard, or a repair rebuilt a shard on other nodes. From that commit on, the history of that node holds stale rows of the table. AS OF, SHOW DIFF and CREATE BRANCH … AS OF refuse a target at or after that commit with NotFound while the node does not hold the data:

the AS OF target is at or after the commit where this node stopped holding the data of
table t, so the history of this node holds no true rows of t at that target; read on a node
that held the data then

When the node holds the data again (it receives a copy, or the cluster adds it to the voters again and it reads the missed log), a target before that commit refuses with the error above ("older than the commit where this node received the data").

A node that received a shard again (a rebalance, a return after a shed) keeps no older history of that table. The node keeps the refusal by the name of the table too, so it stays after a TRUNCATE, or a DROP TABLE and a CREATE TABLE of the same name. A restore from S3 onto a new data directory keeps no older history on that node either. A data directory of an older build keeps no history older than its first start on this build: that start sets the floor of every table at the commit before HEAD. A server data directory of a build before B3817 does the same once more at its first server start on this build. The check follows the first parent of each commit from HEAD. So on a node that received the data of the table, AS OF '<hash>' of a commit that is not on that line (for example the second parent of a merge) refuses too.

A node with --gc-retention keeps only the commits from HEAD down to its retention floor, on the same first-parent line. AS OF '<hash>' of a commit older than the floor, or of a commit that is not on that line, refuses with NotFound, also when the commit block is still on disk:

commit <hash> is not on the history of this node between HEAD and the retention floor
(beyond retention horizon, or not on the parents[0] chain); this node does not answer it
while a retention floor is set

-- expect: 5
SELECT COUNT(*) FROM accounts AS OF HEAD;
-- expect: hash
SHOW COMMITS;

SHOW COMMITS with clauses

SHOW COMMITS [SINCE <ref>] [UNTIL <ref>] [BEFORE <ref>] [TABLE <t>] [LIMIT n]

The clauses select a part of the SHOW COMMITS list. Each clause is optional, the clauses can come in any order, and each clause can occur only one time. The order of the rows stays newest first. With no clause, the answer is the full list.

Each <ref> takes every form of AS OF: HEAD, HEAD~N, a quoted full 64-character commit hash, a quoted date and time, or a number of epoch microseconds.

• A time compares with the wall_clock column. SINCE t keeps the commits at or after t, UNTIL t keeps the commits at or before t, and BEFORE t keeps the commits before t. A commit with no wall clock does not match a time.
• A commit (HEAD, HEAD~N or a hash) is a position in the list. SINCE c keeps c and the newer commits. UNTIL c keeps c and the older commits. BEFORE c keeps only the commits that are older than c. A commit that is not on the history of this node, between HEAD and the oldest retained commit, gives an error, not an empty answer.
• TABLE t keeps the commits that changed the table t (its rows or its indexes) of the request database and the session branch. A table that you cannot see gives the same error as a table that does not exist.
• LIMIT n gives at most n commit rows. When more commits match, the answer ends with one more row, as SHOW DIFF does. Its hash is the FULL hash of the first commit that the answer does not show, its tables is more, and its other cells are NULL. LIMIT 0 gives only the more row.

When the selected range reaches the retention floor, the answer ends with the floor row of SHOW COMMITS. When LIMIT cuts the answer before the floor, the more row comes instead.

To read the history page by page, give the next page the hash of the more row with UNTIL, or the full hash of the last row with BEFORE. The rows show only the first 12 characters of a hash: get the full hash from SHOW COMMIT <hash> or kinedb_commits.

SHOW COMMITS LIMIT 50;
SHOW COMMITS UNTIL '<hash of the more row>' LIMIT 50;
SHOW COMMITS TABLE accounts SINCE 1767225600000000;
SHOW COMMITS SINCE HEAD~20 UNTIL HEAD~10;

With the commit-history index, a page reads only the commits of the page, also deep in the history. A time range reads each index entry, but it reads only the commits in the range. Who may run it: the rule of SHOW COMMITS.

The commit history views

Two read-only system views give the commits as rows, so a plain SELECT can filter, sort, count and group them. A view has no stored rows, and a read of it writes no commit:

• kinedb_commits: seq, hash, parent, wall_clock, branch, history_floor, index_lag. One row for each commit of SHOW COMMITS.
• kinedb_commit_tables: commit_hash, table_name, table_id. One row for each table that such a commit changed. An index tree and a system tree are not listed.

The rows are the commits of the request database and the session branch, the same commits as SHOW COMMITS. seq is the position of the commit on this node: 0 is the oldest commit that the node keeps. A VACUUM with a retention floor removes older commits, so it changes the seq of each commit. hash and parent are the full 64-character hashes, and wall_clock is in epoch microseconds (NULL when the commit has no wall clock).

Each row of kinedb_commits also tells where the history ends. history_floor is the hash of the oldest commit that the node keeps when a retention floor cut the history, and NULL when the history starts at the first commit. index_lag is the number of commits that this read had to add to the commit-history index of the node (0 when the index was current).

A filter on seq (=, IN, BETWEEN, <, >) or on the hash (=, IN) reads only the index entries of those commits. A filter on wall_clock reads only the commits in the range.

SELECT seq, hash, wall_clock FROM kinedb_commits ORDER BY seq DESC LIMIT 5;
SELECT table_name, COUNT(*) AS n FROM kinedb_commit_tables GROUP BY table_name;

Who may read them: the rule of SHOW COMMITS. A database that you cannot see gives the answer of a database that does not exist, and table_name names only the tables that you can see. A guest cannot read the views. A table name of the request database is its bare name.

A JOIN can read a view, also a view joined with the other view (B3789):

SELECT a.id, c.seq, c.wall_clock
FROM audit a
JOIN kinedb_commits c ON c.hash = a.commit_hash;
SELECT c.seq, t.table_name
FROM kinedb_commits c
JOIN kinedb_commit_tables t ON t.commit_hash = c.hash
WHERE c.seq >= 100;

The join reads only the commits that it can need. A WHERE filter of the view side on seq, on the hash or on wall_clock selects the commits as for a SELECT of the view. An ON equality with the key of the view (hash or seq of kinedb_commits, commit_hash of kinedb_commit_tables) also selects the commits: the join reads only the commits of the values of the other side. When the other side is a table of 10 rows, the join reads 10 commits, not the full history. The view side of a LEFT JOIN or a RIGHT JOIN that keeps each row of the view is read in full. A LEFT JOIN from a table keeps each row of the table: a row whose hash is not in the history gets NULL in the columns of the view.

A join with a view runs only on the node that receives it, because the rows of a view are the history of that node. On a node that does not hold a copy of every joined table, the join is refused with this error. A re-send to the same node cannot succeed; send the join to a node that holds a copy of each table:

a JOIN with the system view kinedb_commits runs only on a node that holds a copy of every
joined table; this node does not hold shard <id> of <table>. The history of the view is the
history of this node.

A join with a view has the rules of each side. A table side needs the grants of the table, as for a SELECT of the table. A view side has the rule of SHOW COMMITS: a guest cannot run the join, a database that you cannot see gives the answer of a database that does not exist, and kinedb_commit_tables gives no row for a table that you cannot see.

The views are read-only. INSERT, UPDATE, DELETE, a schema statement, GRANT, AS OF and WATCH that name a view are refused. You cannot create a table with the name of a view. A table with that name from an older version keeps its name in its database: a SELECT reads the table, with the grants of the table, not the view. DROP TABLE removes the table, and then the name gives the view.

SHOW DIFF

SHOW DIFF OF <table> FROM <ref> TO <ref> [LIMIT n] shows the rows that changed between two commits of one table. The table is required. The right to read is a right on one table, and a diff of a whole database has no bound. The statement reads a table or a ledger. It refuses a document or KV collection and names its kind, for example docs is a DOCUMENT collection.

Each <ref> takes every form of AS OF:

• HEAD, or HEAD~N, which counts the commits of the session's branch as SHOW COMMITS does;
• a quoted commit hash. Use the full 64-character hash. The short hash of SHOW COMMITS does not work here. A quoted text of only hex digits with a length that is not 64 is refused with an error that asks for the full 64-character hash. AS OF gives the same error;
• a quoted date and time, or a number of epoch microseconds.

The statement gives one row for each key that is different at the two refs. The rows are in key order. A key with equal rows at both refs gives no row. The columns are all TEXT:

• key: the primary key of the row;
• change: added, removed or changed;
• old: the row at FROM, as column=value, column=value, or NULL when the row is absent;
• new: the row at TO, in the same form, or NULL when the row is absent.

A deleted row is an absent row. The old cells use the columns of the table at FROM, and the new cells use the columns of the table at TO. So a diff across an ADD COLUMN shows the new column in new only. For the same reason, a row that was written again with the same values after an ADD COLUMN can show as changed: its new cell has the new column.

The column=value, column=value form of old and new is a display text. A text value that holds , or = makes the form ambiguous. To read the exact values of a row, use SELECT … AS OF <ref> with the same ref.

SHOW DIFF OF accounts FROM HEAD~1 TO HEAD;

key | change  | old                                 | new
----+---------+-------------------------------------+-------------------------------------
1   | changed | account_id=1, owner=ann, balance=10 | account_id=1, owner=ann, balance=25
2   | removed | account_id=2, owner=bob, balance=7  | NULL
3   | added   | NULL                                | account_id=3, owner=cy, balance=0

The answer is never cut with no sign:

• With no LIMIT, the statement gives at most 1000 rows. When more than 1000 rows changed, it refuses the whole statement and gives no row: SHOW DIFF found more than 1000 changed rows: give LIMIT n, or use refs that are closer together.
• With LIMIT n, the statement gives the first n rows. When more rows changed, it adds ONE last row: change is more, old and new are NULL, and key is the key of the first row that is not shown. An explicit LIMIT asks for a part of the answer, so the statement gives that part, and the more row shows in the data that the answer is not complete. A diff of exactly n rows has no more row.
• LIMIT 0 follows the same rule: no change gives no row, and a change gives only the more row. So LIMIT 0 tells cheaply whether a change exists.

The maximum LIMIT is 100000. A larger LIMIT is refused. The memory that the statement uses stays bounded by the limit. The statement has no cursor. To read more rows, choose two refs that are closer together.

The time of the statement grows with the number of changed entries that it reads in the storage, not only with the LIMIT. An entry that gives no row also costs time: for example, a row that was deleted and written again with the same values, or a row that was added and then deleted between the two refs.

A ref is refused in the same cases as AS OF: a ref before the history of the branch, a commit that does not hold the table, and a commit below the retention floor that --gc-retention sets.

A ref from before the CREATE TABLE of the table gives permission denied to a user who has a grant, not a message that the table did not exist. A table that does not exist at a commit has no policy, so no grant covers it.

The statement needs the SELECT right on the whole table, now AND at both commits. A grant with only MATCH or WHERE is refused, and the refusal shows no row value. The guest user is refused. kinedb has no column masks, so only row policies apply. The statement runs on the read path only. A writer engine, or a statement inside a transaction, refuses it with an error that says it runs on the read path only.

On a cluster, a commit hash names the history of one node. The statement runs on the node that receives it and is never passed to another node. A node that does not host and serve every shard of the table refuses it with the same answer as AS OF: time-travel reads are not yet supported at N>RF. A standalone server answers. A table with several shards, also a table that split between the two commits, gives one answer in key order.

Branches

A branch is a named root of the whole database. It costs no copy.

-- expect: OK
CREATE BRANCH dev_branch;
-- expect: 2 rows
SHOW BRANCHES;
-- expect: OK
USE BRANCH dev_branch;
-- expect: OK
USE BRANCH main;
-- expect: OK
DROP BRANCH dev_branch;

A branch belongs to one database: CREATE BRANCH, SHOW BRANCHES and USE BRANCH act on the database of the session. A new connection starts on main. USE <db> also puts the session on main of the new database, also when that database has a branch of the same name as the old branch. To work on a branch of the new database, send USE BRANCH <name> after USE <db>. A refused USE <db> keeps the database and the branch of the session.

On a cluster, USE <db> and USE BRANCH <name> find a database or a branch that another node made, also on a node whose catalog is behind. Before the node answers database not found or branch not found, it waits until its catalog holds every acknowledged CREATE. When the node cannot do this check in time, the answer is a retryable error. Send the USE again.

SHOW BRANCHES lists the branches of a database only to a user who sees that database in SHOW DATABASES. For any other database it answers database not found: <db>, the same answer as for a database that does not exist. In a database that the user sees, SHOW BRANCHES lists main and each branch on which the user has a right. root sees every branch (see Rights on a branch).

A table name can also name the branch: mydb@dev_branch.accounts is the table accounts on the branch dev_branch of mydb, and the session stays where it is (see Qualified table names).

CREATE BRANCH of a present name is refused with branch already exists, and DROP BRANCH of an absent name is refused with branch not found. When two clients send the same statement at the same time, one gets OK and the other gets the refusal, on a single server and on a cluster.

A new branch holds every row that was acknowledged before CREATE BRANCH, in every table, also in a cluster where no single node holds every table.

A write on a branch changes only that branch. The FIRST write of a table on a branch makes the branch's own copy of that table, and the base keeps its rows. That first write applies like every later write: INSERT, UPDATE and DELETE answer their count, and the next read on the branch answers the new value.

That copy is a pointer, also for the indexes of the table: it copies no row and no index entry, so a branch of a large table costs no more than a branch of a small one. Only the rows and index entries that the branch changes take new storage. The branch's copy keeps every index that the table had when the branch was created, so a read on the branch uses the same indexes as on main, and a UNIQUE index refuses a duplicate on the branch. An index that main creates after CREATE BRANCH is not on the branch. CREATE INDEX and DROP INDEX on a branch are refused. An ALTER TABLE ... ALTER COLUMN ... TYPE on a branch is refused when the new type needs a rebuild of an index of the column, because the rebuild needs CREATE INDEX on the branch. Nothing changes on the branch or on main, and the same ALTER on main works. A TRUNCATE on a branch keeps the indexes of that branch table: each index starts empty, and a UNIQUE index refuses a duplicate after it.

The copy is a pointer for the files of the table too. After the first write on the branch, every file that the table held when the branch was created still reads on the branch (SELECT FILE, GetFile). A row DELETE on the branch removes the file of that row on the branch only, and a file that the branch stores is not on main.

A branch can hold a different schema than its base. CREATE TABLE, CREATE COLLECTION, ALTER TABLE, TRUNCATE, DROP TABLE and DROP COLLECTION on a branch change only that branch, in the embedded mode and over a server connection. The base keeps its tables, its columns and its rows. A row that the branch inherits reads NULL in a column that the branch added.

You cannot remove a base table on a branch. A base table is a table or a collection that the base commit of the branch holds: a table of main when the branch was created. The branch reads that table from its base commit, so a drop on the branch cannot remove it. DROP TABLE and DROP COLLECTION of a base table on a branch are refused, also after a write on the branch, and NOTHING is dropped. DROP TABLE IF EXISTS is refused the same way. A table that the branch created, or a table that MERGE BRANCH main INTO <branch> adopted, drops on the branch. To remove a base table, run the drop on main. The branch keeps its table until you drop the branch and create it again.

CREATE BRANCH drop_dev;
USE BRANCH drop_dev;
-- refused: you cannot remove a base table on a branch
DROP TABLE accounts;
USE BRANCH main;
DROP BRANCH drop_dev;

A branch sees the schema of each table as it was when the branch was created. An ALTER TABLE on main after CREATE BRANCH does not change the branch, also when the branch writes the table for the first time after that ALTER. So a write on the branch to a column that only main added is refused with unknown column, and an ALTER TABLE on the branch that adds the same column succeeds. A refused ALTER TABLE on a branch changes nothing: the branch gets no copy of the table. (Before B2630 the first write took the schema of main at the time of that write.) MERGE BRANCH main INTO <branch> gives a table that the branch did not write the schema of main (B2590).

CREATE BRANCH schema_dev;
USE BRANCH schema_dev;
ALTER TABLE accounts ADD COLUMN nickname TEXT;
CREATE TABLE drafts (id INT PRIMARY KEY, body TEXT);
USE BRANCH main;
-- main answers: unknown column: nickname
SELECT nickname FROM accounts;

An AS OF read on a branch answers the data AND the columns of that commit. A branch inherits the history of its base, so a commit older than the CREATE BRANCH is readable on the branch, and it answers what the base answers at that same commit. So AS OF the commit before an ALTER TABLE on the branch shows the columns the table had then, and AS OF a commit older than the branch itself shows the base's rows and the base's columns.

Rights on a branch

A branch has its own grants. The rights on a branch are SELECT, INSERT, UPDATE, DELETE and CONTROL. A holder is a user or a role:

GRANT SELECT, INSERT, UPDATE ON BRANCH dev TO alice;
GRANT SELECT ON BRANCH dev TO ROLE reviewers;
GRANT CONTROL ON BRANCH dev TO ROLE leads;
REVOKE INSERT ON BRANCH dev FROM alice;

GRANT … ON BRANCH and REVOKE … ON BRANCH need root or CONTROL on the branch. DROP BRANCH also needs root or CONTROL on the branch. Any other user gets permission denied. The cluster judges the right again when the statement applies, in the order of the log. main takes no branch grant: the rights on main are the grants of its tables. GRANT … ON BRANCH main is refused.

Today only root creates a branch. A branch that root creates has no grant, so give the rights with GRANT … ON BRANCH. The CREATE BRANCH grant (GRANT CREATE BRANCH [ON DATABASE <db>] TO … WITH <rights>) comes with a separate change (B3838). It is not available yet. With that grant, a user that is not root creates a branch, and the creator gets the rights of the WITH list on the new branch.

A hidden branch. A user with no right on a branch does not see the branch. SHOW BRANCHES does not list it. Every statement that names the branch answers as for an absent branch, for example branch not found: dev. This applies to USE BRANCH, DROP BRANCH, GRANT and REVOKE … ON BRANCH, MERGE BRANCH, a read, a write, AS OF, SHOW DIFF, SHOW COMMITS, WATCH and a file read. So the user cannot tell that the branch exists.

Reads. A read on a branch needs SELECT on the branch. The table grants of the branch then decide each row. A branch keeps the table grants that it had at the fork, so a later GRANT on a table of main does not reach the branch. The current grants of main also limit each row: a branch never shows a row that main hides from the user. So a REVOKE on main also narrows the reads on the branch.

Writes. A write on a branch needs the branch right of its operation: INSERT, UPDATE or DELETE. The table grants of the branch must also allow the write. A write right on a table of main gives no right on a branch.

WATCH and history. A WATCH on a branch follows the rule of a read. A notification shows only the rows that a SELECT of the same user on the branch shows. AS OF, SHOW DIFF and SHOW COMMITS on a branch need SELECT on the branch, as a read does.

After an upgrade. A branch that a version before B3840 made has no grant. After the upgrade, only root sees it. Another user gets branch not found: <name> until root gives a right on the branch:

GRANT SELECT ON BRANCH old_dev TO ROLE developers;

Merge branches

MERGE BRANCH <name> writes the changes of a branch into main. MERGE BRANCH <name> INTO main is the same statement. MERGE BRANCH main INTO <name> goes the other way: the branch takes the changes of main since the last merge with main, or since the fork. Both directions work on the embedded engine and on a server or cluster.

Both directions use the same rules. The merge compares each row with its ancestor. Before the first merge, the ancestor is the row that main had when the branch was created. After a merge in either direction, the ancestor of each merged key moves to the row that the merge read on the source side. So a round trip (the branch into main, main into the branch, the branch into main) gives no false conflict. The rules below name the branch as the source and main as the target. In the other direction, the two sides swap roles:

• A row that only the branch changed takes the value of the branch. An insert, an update and a delete on the branch all merge.
• A row that only main changed keeps the value of main.
• A row that both sides changed to the same value merges with no write.
• A row that both sides changed merges column by column under every policy. A column that only one side changed takes the value of that side. For example, the branch sets first and main sets name of one row, and the merged row keeps both values. Under LWW the merged row keeps the newer clock of the two sides. Under FUNCTION the merge does not call the function, because there is no conflict.
• A conflict is a column that both sides changed to different values, a row that one side deleted and the other side changed, or a row that both sides inserted with different values. The conflict policy of the table decides. Under REJECT, the default, the merge writes NOTHING and its error names each conflicting row by table and primary key. Under LWW, the row with the newer clock wins the whole row. Every row that the merge writes into an LWW table keeps the clock of its write on the branch, not the time of the merge. A delete in an LWW table has a clock too, and it competes as a row does: the newer clock wins. On equal clocks the row that is not deleted wins. The merge refuses a conflict under LWW, and names the row, when a row or a delete has no clock (it was written before SET CONFLICT POLICY LWW). Under FUNCTION <name> the function writes the merged row; a refusal of the function refuses the merge as REJECT does, with the reason of the function.
• Under LPW, a row that both sides changed merges column by column. A column that only one side changed takes the value of that side. A column that both sides changed to different values takes the value with the newer clock. Each column of the merged row keeps the clock of its write, not the time of the merge. The merge refuses, and names the row, when one side deleted the row and the other side changed it. It refuses, and names the row and the column, when both sides changed a column and one side has no clock for it. A delete that only the branch made merges, and it keeps the time of the delete on the branch.
• A DOCUMENT collection merges by _id, with the same rules for each document, except that a deleted document has no clock: a conflict with a delete refuses under LWW and LPW. Under LPW, a document that both sides changed merges property by property: the branch sets a.x and main sets a.y, and the merged document keeps both. The merge refuses, and names the document and the path, when one side removed a property that the other side changed, when a property has no clock, or when one path is a value on one side and an object on the other side.
• A COUNTER or COUNTER(FLOAT) cell that both sides changed merges as a sum: the old value, plus the amount of the branch, plus the amount of main. Two equal amounts count twice. The other cells of the row merge as above: a cell that only one side changed takes the value of that side. When no other cell has a conflict, the merge writes the row under every policy. Else the policy of the table decides the row, and the counter cells of its result are the sums. Under LPW a summed cell keeps the newer clock of the two sides. A delete on one side, or a row that both sides inserted, goes to the policy as before. The merge refuses, and names the row and the column, when a counter cell is NULL, when an integer sum does not fit in 64 bits, or when a floating-point sum is not a finite number.

The merge is one transaction: all of its rows apply, or none. It keeps the indexes of the target current. A merged row must not give a UNIQUE index of the target a second row with the same value: the merge then writes NOTHING and answers the duplicate key error of a direct write. Two rows that exchange their unique values in one merge are not a duplicate. The branch stays after the merge, and the session stays on its branch. You can change the branch again and merge it again: a row that only the branch changed since the last merge merges with no conflict.

On a cluster, the merge first waits until its node applies every metadata change that answered before the merge arrived. So the merge uses the current conflict policy, and a FUNCTION policy runs the current body of the function as its current run-as user. When the node cannot catch up before the deadline, the merge answers a retryable [metadata-floor] error and writes nothing. Run it again.

The merged schema is always the schema of the target. The schema of a table merges three ways too. A column keeps its identity through a rename or a type change, so the merge matches the columns that existed at the fork by that identity, and a new column by its name:

• A schema change on main only (ADD, DROP, RENAME or a type change) merges. A branch row reads NULL in a column that only main added.
• The same change on both sides merges. A new column of the same name and type on both sides is one column.
• A schema change on the source side only refuses, because the merge does not change the schema of the target. The error names each ALTER TABLE statement to run on the target first; then run the merge again. For MERGE BRANCH main INTO dev, the error says to run the same ALTER TABLE on dev. This applies only when dev has its own copy of the table. When dev never wrote the table, the merge ADOPTS the table of main (see below), because dev has no schema change of its own.
• Two changes that do not agree (two different renames, two types for one new column, a drop on one side and a rename on the other, one name for two columns) refuse as a schema conflict. The error shows both sides.
• A column that main dropped and the branch changed refuses: the branch value would be lost. The error names the column and the row.

-- expect: OK
CREATE TABLE stock (sku TEXT PRIMARY KEY, qty INT);
-- expect: OK
INSERT INTO stock (sku, qty) VALUES ('a', 1), ('b', 1);
-- expect: OK
CREATE BRANCH restock;
-- expect: OK
USE BRANCH restock;
-- expect: OK
UPDATE stock SET qty = 10 WHERE sku = 'a';
-- expect: OK
USE BRANCH main;
-- expect: OK
MERGE BRANCH restock;
-- expect: 10
SELECT qty FROM stock WHERE sku = 'a';

A table that the branch never wrote is copied for the branch only when the merge writes it. A merge that refuses copies nothing.

A table that main created after the fork is ADOPTED by MERGE BRANCH main INTO <name>. The branch gets the table of main as it is at the merge, with its rows and its schema. The copy is a pointer, as at CREATE BRANCH: it copies no row. From then on, the ancestor of the table is main at the adoption, so a later merge in either direction takes only the newer changes. When the branch created its own table of the same name, the merge refuses and names the table. The error says to run DROP TABLE <table> on the branch (its rows of that table are then lost) and to merge again. The same way out applies to an adopted table that main dropped and created again. A base table of the branch that main dropped and created again also refuses the merge. You cannot remove a base table on a branch, so the error says to drop the branch and create it again (the changes of the branch are then lost).

A table that the branch never wrote, and whose schema main changed after the fork (ADD, DROP, RENAME or a type change), is ADOPTED the same way by MERGE BRANCH main INTO <name>. The branch then reads the schema and the rows of main at the merge. No ALTER TABLE on the branch is necessary.

A KV collection merges by key, in both directions. A key that only one side changed takes that side. A key that both sides changed to two different values refuses under every policy, because a KV value has no clock to compare. The error names every such key. A ledger merges by the table rules, but it stays append-only: a merge takes a new key, and a key that both sides inserted with two different rows refuses by key under every policy.

Each refusal stops the whole merge. A refused merge changes NOTHING.

Who may merge. There is no separate merge right. root merges every branch. A user that is not root needs SELECT on a source branch and, on a target branch, the branch right of each write (see Rights on a branch). Each row that the merge writes also passes the normal write check of that user on the target, as a direct INSERT, UPDATE or DELETE does. Only these rows are checked, so a merge that only deletes rows needs DELETE and no INSERT. A row that changes a protected field needs CONTROL (see Users, roles and permissions). When the check refuses one row, the whole merge is refused with permission denied, and NOTHING is written. The check runs before the merge copies or adopts a table on the target, so a refused merge leaves no copy and no adopted table there. A merge from or into a hidden branch answers as for an absent branch.

-- alice holds SELECT, INSERT, UPDATE ON BRANCH dev and a write grant on the tables of main
MERGE BRANCH dev;
MERGE BRANCH main INTO dev;

The merge checks its rows before it writes them. When the merge changes between the check and the write, it checks the rows again, at most 3 times. Then it answers a retryable error, and NOTHING is written. Run the merge again.

MERGE BRANCH <b1> INTO <b2> also merges two branches that are not main. It uses the same rules: the merge compares each row with its ancestor, merges column by column, and lets the conflict policy decide a conflict. It merges only the tables that <b1> has its own copy of. A write on <b1> makes that copy, and so does a merge into <b1>. The merged schema is the schema of the target. A watch on the target gets the merge. A watch on the source and a watch on main get nothing.

For two branches, the ancestor of a key depends on their history. After a merge between the two branches, in either direction, the ancestor is the row that the merge read on the source side. Before their first merge, the ancestor is the one that both branches share with main. Sometimes the two branches see different ancestors with main. For example, they forked at different times, and main changed the row in between. Then the row has no common ancestor. It counts as changed on both sides, and the policy decides. This is also true for a row that neither branch changed. Under REJECT, the merge refuses and names the key. Under LWW, the newer row wins. To avoid this, run MERGE BRANCH main INTO <b1> and MERGE BRANCH main INTO <b2> first. Then merge <b1> into <b2>.

The ancestor of a merge between two branches counts only when the copies of both branches hold it. A DROP TABLE on one branch removes its copy. The next merge of that table then uses the ancestors with main again.

A COUNTER cell does not add up across a merge between two branches. A counter cell that both sides changed is a conflict, not a sum, in these cases:

• The key has no common ancestor.
• The key took part in a merge between two branches before. This is true in every later merge of that key: with main and with the other branch.

A counter cell that only one side changed merges as before. For a key with no common ancestor that never took part in a merge between two branches, the conflict policy decides.

For a key that took part in a merge between two branches, the merge refuses under every conflict policy, also LWW. It names the key and changes nothing. Every other key and table of that merge waits too.

After a key took part in a merge between two branches, its counter conflict stays. A later MERGE BRANCH main INTO a branch does not clear it. The conflict comes back in every merge in which both sides changed that cell. To get out of it:

1. Set the counter cell back to its ancestor value on ONE side. The error shows the values. On the target, the merge then takes the value of the source. On the source, the target keeps its own value. If the error says that the cell has no common ancestor, set the cell to one value on both sides.
2. Run the merge again.
3. If the total is not correct, set it by hand.

No step prevents this conflict after the first merge between two branches: that merge records the key, and a later change of the counter on both sides refuses. A merge of main into both branches before that first merge only prevents the "no common ancestor" conflict at the first merge itself. This strict rule is the known cost of the per-key ancestors. A merge base for each pair of branches (later work) removes it.

A branch that you drop and create again with the same name starts fresh. The merge does not use the old ancestors.

-- expect: OK
CREATE TABLE memos (id INT PRIMARY KEY, body TEXT);
-- expect: OK
CREATE BRANCH b1;
-- expect: OK
CREATE BRANCH b2;
-- expect: OK
USE BRANCH b1;
-- expect: OK
INSERT INTO memos (id, body) VALUES (1, 'from b1');
-- expect: OK
MERGE BRANCH b1 INTO b2;
-- expect: OK
USE BRANCH b2;
-- expect: from b1
SELECT body FROM memos WHERE id = 1;
-- expect: OK
USE BRANCH main;

To refresh a long-lived branch with the newer data of main, merge main into the branch. The branch keeps its own changes:

-- expect: OK
CREATE TABLE prices (sku TEXT PRIMARY KEY, price INT);
-- expect: OK
INSERT INTO prices (sku, price) VALUES ('a', 5), ('b', 7);
-- expect: OK
CREATE BRANCH dev;
-- expect: OK
UPDATE prices SET price = 6 WHERE sku = 'a';
-- expect: OK
USE BRANCH dev;
-- expect: OK
UPDATE prices SET price = 9 WHERE sku = 'b';
-- expect: OK
MERGE BRANCH main INTO dev;
-- expect: 6
SELECT price FROM prices WHERE sku = 'a';

A counter merge: the branch adds 2 and main adds 3 to 10.

-- expect: OK
CREATE TABLE likes (post INT PRIMARY KEY, n COUNTER);
-- expect: OK
INSERT INTO likes (post, n) VALUES (1, 10);
-- expect: OK
CREATE BRANCH campaign;
-- expect: OK
USE BRANCH campaign;
-- expect: OK
UPDATE likes SET n = n + 2 WHERE post = 1;
-- expect: OK
USE BRANCH main;
-- expect: OK
UPDATE likes SET n = n + 3 WHERE post = 1;
-- expect: OK
MERGE BRANCH campaign;
-- expect: 15
SELECT n FROM likes WHERE post = 1;

WATCH: live queries

WATCH <table> [FAST] [VERBOSE] [SINCE START | SINCE '<token>'] [LIVE] [WINDOW <n>] [WHERE <column> <operator> <value> | PREDICATE <id> [WHERE …] …] streams the changes of a table to the client, over the WebSocket of a server connection, until UNWATCH. The embedded engine refuses a local WATCH, ALTER WATCH and UNWATCH with an error (B2198): a watch on the embedded database is not available yet (B2196). The HTTP endpoint POST /sql refuses WATCH with the same error, because it has no stream to send the changes on.

WATCH accounts WHERE balance > 100;
UNWATCH accounts;

A watch is scoped to the session's database: USE sales; WATCH orders streams the changes of sales.orders, and so does WATCH sales.orders from any session. Two databases may hold a collection of the same name, and the watches stay apart: a watch on tenant_a.orders hears nothing for a write to tenant_b.orders or to default.orders, and its permissions are the permissions of tenant_a.orders.

A watch is also scoped to the session's branch: USE BRANCH dev; WATCH orders streams the changes of orders on dev. A write on main sends nothing to that watch, and a write on dev sends nothing to a watch on main. A merge writes on its target. So MERGE BRANCH dev sends the merged rows to the watches on main. MERGE BRANCH main INTO dev sends them to the watches on dev and sends nothing to the watches on main.

A notification honours the permissions of the subscriber, the same way a SELECT does. A user needs a SELECT permission on the table to subscribe. Every notification carries only the changed rows that the user may read: a row that a MATCH or a WHERE permission hides is left out, and a write that changes only hidden rows sends nothing, not even a count. The permissions are read at the time of each write, so a REVOKE takes effect at the next write.

On a cluster with more nodes than the replication factor, a node can hold no copy of a part (a shard) of the table. A watch on that node still receives every committed change of that part, but it receives them in a different way. The node asks a node that holds the part for the change about every 100 ms. One notification then carries the combined change since the last request, so five inserts can arrive as one notification with changed 5. A row that is inserted and deleted between two requests can arrive as a delete of that key only, or not at all. The permissions are read when the change arrives on the node. A watch whose permissions filter row by row cannot read a key that is already gone, so it receives the error below for such a row instead of a delete. When the node cannot compute an exact change, for example because no node that holds the part answers for 5 seconds, the watch receives an error that names the table and the part and says that changes may be missing. Then the watch ends: subscribe again, and read the table with a SELECT to get the current rows. A watch on a node that holds the part receives one notification for each write, as before. When a node stops holding a part (a rebalance moves it to another node), its watches continue without a gap: the node compares its own last copy of the part with the copy of a node that holds it, and sends the combined change. A row can then arrive a second time; a change is never missing.

What a notification carries

A plain WATCH sends a count: the changed field, and no data. Add VERBOSE to receive the changed records themselves.

A VERBOSE notification names its columns, and it names the same columns a SELECT * on that collection names. So a client reads a notification with the code it already uses for a query result.

Collection	Columns of a VERBOSE notification
A table	the columns of the table
TYPE DOCUMENT	_id and document, where document is the whole body as JSON text
TYPE KV	key and value

WATCH accounts VERBOSE;
UNWATCH accounts;

Every notification also names WHAT happened, in an operation field: insert, update or delete. Both kinds of notification carry it — the count-only one and the VERBOSE one.

The field is ABSENT when the server cannot say, and a client reads that as "not known" rather than as a kind of change. There are three such cases, and each one is a fact about the source rather than a limitation of the field: a notification replayed from a snapshot (the snapshot does not record which statement wrote each row), a notification forwarded from another node's push, and one batch that mixes kinds — a single notification carries a single word, so a mixed batch names none. The rows and the changed count are unaffected in all three.

A fourth word, rights, comes with no write at all. A GRANT ROLE, a REVOKE ROLE, a GRANT … ON or a REVOKE … ON can change the rows that a watcher may see. Then each open watch of that watcher gets ONE count-only notification with changed: 0 and the operation rights, also a VERBOSE watch. The notification carries no row. Read the table again to get the rows of the new rights. A watch whose SINCE fill is in progress gets no rights notification: the fill itself answers the rights change. A client of an older build reads the unknown word as "not known" (B3043).

Catching up after a disconnect

A plain WATCH starts at the moment it subscribes. A client that was disconnected never learns what changed while it was away. SINCE closes that gap.

Add SINCE START on the first subscription. The server sends the rows that match, then a token: the commit the client is now caught up to. Save it.

WATCH accounts VERBOSE SINCE START WHERE balance > 100;
UNWATCH accounts;

On reconnect, pass the saved token back. The server compares that commit with the current one and sends only what changed: the rows to insert or replace, and the rows to drop.

WATCH accounts VERBOSE SINCE '3bc5c3c40d9f4e2a8b7c1d6e5f0a9b8c7d6e5f4a3b2c1d0e9f8a7b6c5d4e3f2a' WHERE balance > 100;
UNWATCH accounts;

The token above is an example. Use the one the last fill gave you; a token the server no longer holds is not an error, it simply earns a full fill.

SINCE serves a table only. The fill carries typed columns, and a DOCUMENT or KV collection has none. So WATCH <collection> SINCE … is refused with an error that names the collection, and nothing is registered. A plain WATCH <collection> without SINCE works on a collection.

SINCE needs a node that holds a replica of every shard of the table. The fill reads the storage of the node you are connected to. In a cluster with more nodes than replicas, a node can hold no replica of a shard. That node refuses WATCH … SINCE with a retryable error that names the table and the shards, and nothing is registered. Connect to a node that holds the table, and subscribe again. A plain WATCH without SINCE works on every node.

The fill arrives as one or more watch_fill frames ahead of the live notifications. Each carries upserts, removes and a full flag; the last one carries the commit token. full is true when the frame replaces the client's whole set for this query, and false when the rows are changes to apply. A frame with more set means another follows.

The fill is paced (B2200). Each frame is one page of at most 2048 rows or 4 MiB, in key order. After a frame with more set, the server sends nothing more for the table until the client asks for the next page with the request WatchFillNext (0x29). A frame with more set carries a resume token. Store the rows and the token of the page together, then ask for the next page. After a cut, subscribe again with SINCE '<the stored token>': the fill continues after the last stored page. A page can have zero rows. A fill that gets no WatchFillNext within 120 s ends with a failure frame. The JavaScript, Python and embedded clients ask for each page for you.

Add WINDOW <n> after SINCE to let the server send up to n pages before the client asks (B3026). n is 1 or more, and WINDOW needs SINCE. The server caps n at 32. WINDOW 1 and a statement with no WINDOW are the same: window 1. The server sends one page, then waits for the plain WatchFillNext, as above. A client of window 1 sends no ack. If it sends one, the server counts it as a stale ack, and the fill stops until the idle timeout.

With n above 1, the client sends WatchFillNext with an ack slot. The slot holds the token of the oldest page that the client delivered. Each ack lets the server send one more page. The server refuses a plain WatchFillNext of such a fill with an error. A LIVE tail diff always has window 1. When a statement has WINDOW twice, the last one wins. The wire form of the request is in docs/protocol/wire-protocol.md, under WatchFillNext (0x29).

Choose n with the page size in mind. A page can have 4 MiB, so a window of 32 can hold up to 128 MiB of fill in flight to your client. The JavaScript client asks for a window of 16 and holds back its credit above 16 MiB. The sync of @kinedb/embedded (JavaScript and Python) asks for a window of 4 (B3033), so at most 16 MiB of fill is in flight to it. The native Rust SyncClient uses window 1.

At every window, a change of your rights between two pages of a fill ends the fill with a failure frame (retry: true). Subscribe again with the stored token. The stamp no longer matches (B2681), so the server answers it as a SINCE START fill with your new rights.

WATCH accounts SINCE START WINDOW 16;

A row arrives in removes when it leaves the visible set, whatever the reason: it was deleted, it stopped matching the WHERE, or a permission stopped admitting it. The client drops the row in all three cases.

Two behaviours are worth knowing before you build on this:

• The server may send a full fill instead of changes. It does so whenever it cannot compute an exact difference — the saved commit has passed out of the retention horizon, or the table's storage was reshaped since, or the token is from before the node you are connected to received the data of the table. full tells the client which it received, and a full fill is always safe to apply.
• A fill can fail, and the server tells you why. When the server cannot build the fill, it sends one failure frame with the reason instead of rows (watch_fill_error in the embedded client). The failure frame ends the fill and carries no token. The live subscription stays in place. Re-subscribe with SINCE START, or with SINCE '<the stored token>' when you stored the token of a page.
• A row may arrive twice. A change committed between the subscription and the end of the fill appears in the fill and again in the live stream. Applying a fill row is idempotent, so the result is the same. This is deliberate: the alternative ordering would leave that window in neither, which loses a change silently.

Staying current after the fill: LIVE

Add LIVE after the SINCE part, and the subscription keeps a tail. After the fill, the server sends each later change of the table as a diff from the token of the diff before, with the same upserts, removes and full as a fill, and a new token on its last frame. Every frame of a diff also names its base: the token the diff starts from. Apply a diff only when its base is the token your set is complete as of, and keep its new token.

WATCH accounts SINCE START LIVE WHERE balance > 100;
UNWATCH accounts;

• LIVE needs SINCE. A tail diffs from the token of the fill, so a LIVE alone is refused by name:

-- error: LIVE needs SINCE
WATCH accounts LIVE;

• A LIVE subscription gets no count and no row notification: the diffs replace them. Many commits close together arrive as one diff.
• A row that leaves the visible set arrives in removes, as in a fill: a delete, a row that stopped matching the WHERE, or a permission that stopped admitting it.
• A diff can fail, and then its frame carries the reason and no rows. When it fails on its first page, the server keeps its tail at the same base, and the next change of the table runs the diff from that base again. To catch up at once, subscribe again with SINCE '<your token>' LIVE.
• A diff comes in pages, as a fill does: ask for each next page with WatchFillNext. When a diff fails after its first page, the tail stops. Subscribe again with SINCE '<the stored token>' LIVE.
• A diff with any other base is a late frame of a subscription you replaced. Drop it.

The embedded client does all of this for you: db.sync(table) subscribes LIVE, applies each diff to the local table and calls onChange (see the embedded client).

A LIVE watch on a plain embedded connection (db.connect()) applies nothing. Each diff frame reaches the watch() callback of its table as watch_tail, with its base and the fields of a fill frame. A failed diff arrives as watch_tail_error, with its base and the reason in message.

A revoked permission is a special case. The fill reports a row as removed only when the row itself changed; revoking access without writing to the row does not by itself produce a removes entry. Re-subscribe with SINCE START to force a fresh, fully filtered set.

A set of predicates: PREDICATE and ALTER WATCH

A LIVE watch can hold a set of named predicates instead of one WHERE. A row is in the set of the watch when one predicate matches it (and your permissions admit it). Each row of a fill or a diff names the ids of the predicates that match it (upsert_predicates).

WATCH accounts SINCE START LIVE PREDICATE rich WHERE balance > 100 PREDICATE mine WHERE owner = 'ana';

• An id is an identifier or a quoted string. Two predicates with one id are refused.
• PREDICATE needs LIVE, and a statement with PREDICATE takes no bare WHERE.
• A PREDICATE <id> without WHERE matches every row.
• The bare WHERE of a watch is the set of one predicate with the id ''. Its frames carry no ids.

ALTER WATCH changes the set of the ONE live watch of your connection on the table:

ALTER WATCH accounts ADD PREDICATE big WHERE balance > 1000;
ALTER WATCH accounts DROP PREDICATE mine;

• The change arrives as ONE diff from your current token. An added predicate sends every row that it matches, also a row that did not change and a row that another predicate already matched. Each row comes with its full list of ids, so the ids name the new predicate. A dropped predicate removes the rows that only it matched. It sends no row that another predicate still matches: remove the dropped id from the ids that you hold.
• The statement answers watching when the diff was sent. If the diff fails, the statement answers an error and the watch keeps its old set.
• ALTER WATCH is refused by name when your connection holds no live watch on the table, when the watch is not LIVE, when it holds two or more live watches on the table, when an added id is in the set already, and when a dropped id is not in the set.
• The set can become empty. The watch then holds no rows and stays live.

The token does not carry the set. To resume after a disconnection, subscribe with SINCE '<your token>' LIVE and the set you held at that token, then send your ALTER WATCH changes again.

Limits and the gap answer: ALLOW GAP and MAX_RESUME_ROWS

The server limits the watches of one connection. The defaults are very large, so a normal application never meets them. An operator changes them with SET CLUSTER:

Setting	Default	What it limits
cluster.watch.max_predicates_per_client	100 000	The predicates of all the watches of ONE connection. A watch with a bare WHERE or with no WHERE counts 1.
cluster.watch.max_fill_rows	100 000 000	The changed rows of ONE fill: a SINCE '<token>' resume, a SINCE START fill, or the diff of an ALTER WATCH.

A WATCH or an ALTER WATCH … ADD PREDICATE above the predicate limit is refused. The error names the count, the limit and the setting, and it is not retryable. Nothing else of the connection changes.

Before the first frame of a fill, the server counts its changed rows. It stops at the limit plus one. A fill above the limit sends no row. What the watch gets then depends on ALLOW GAP:

• Without ALLOW GAP, one failure frame that names the limit and the setting. It is not retryable.
• With ALLOW GAP, one gap frame: a watch_fill frame with no rows and a gap field. Its token is the current commit of the server, and the watch continues live from that commit. The watch stays open.

The count of cluster.watch.max_fill_rows counts keys. After its first page it reads no row data and applies no rights, so it is fast. The rights of the caller never change the count: a row that the caller may not read still counts. A watch with MAX_RESUME_ROWS reads the rows, because that limit counts the rows that the caller gets. A stored row that the server cannot read fails the fill at its page. Rows before that page can already be on the client. The failure frame is retryable (B3048 tracks a terminal end for a row that never heals).

Under a MATCH grant, a SINCE START fill reads a one-column BTree index on the matched column when the table has one. With no usable index, the fill walks the table and counts every key, also the rows of other owners. So a user who can read few rows of a big table can get a gap. Create the index on the matched column to keep such a fill under the limit. The diff of an ALTER WATCH reads no index. A window of the embedded sync (sync_window, a forwarded ORDER BY … LIMIT query) whose fill or bound move gets a gap answers that gap, not an error. Its local rows are empty. After a gap of the fill, the table is gapped as of HEAD: call refill() (or sync with { full: true }) for a full fill. After a gap of the ALTER WATCH diff of a bound move, the table holds no token, and the next sync_window fills it from START. The same is true when an earlier live diff of the table gets a gap while the bound move waits for its ALTER WATCH: the call answers that gap, and the next sync_window fills the window from START.

WATCH accounts SINCE START LIVE ALLOW GAP MAX_RESUME_ROWS 10000 WHERE balance > 100;
UNWATCH accounts;

MAX_RESUME_ROWS <n> is the limit of the client: a fill that would send more than n rows gets a gap. It counts the rows that match the watch, not every changed row. It implies ALLOW GAP. It has no default. ALLOW GAP and MAX_RESUME_ROWS need SINCE: a watch without SINCE has no fill, so they are refused by name.

A gap means: the changes between the token of the request and the token of the gap are NOT in your set. The rows that you hold can be old, and rows can be missing. A row that the user lost in that time (a revoke, a row moved to another owner) can still be in your set. So a client that asks ALLOW GAP must not keep its rows on a gap: delete them and subscribe again with SINCE START (B3443). The embedded sync does this for you. The gap field says why:

reason	Why
server_limit	The fill walks more changed rows than cluster.watch.max_fill_rows.
limit	The fill sends more rows than your MAX_RESUME_ROWS.
retention	The server no longer holds the commit of your token (ALLOW GAP only).

The field also carries since (the token of the request), limit and changed_rows_at_least (the limit plus one; 0 for retention). A server_limit gap also carries path: the read that counted (B3233). table_walk is a walk of the table, index_lookup is a read of a BTree index or an array index (B3160, B3162), and token_diff is a diff from your token. The path is the path of the page in which the count passed the limit. changed_rows_at_least is the total of all pages. So a fill whose index read gives up names table_walk when the count passes the limit in that page or later. When both limits pass in one page, the gap is server_limit. A limit or retention gap has no path, and a gap from an older server has none. To get a complete set again, subscribe with SINCE START. A token beyond retention without ALLOW GAP keeps its old answer: a full fill, or a diff while the node still holds the old commit in its cache. Both give you every row.

The embedded client sends ALLOW GAP. A gap reaches the application as report.gap of db.sync() and as freshness().gap, with the state gapped (see the embedded client).

A rights change and the resume: RIGHTS

A SINCE '<token>' resume sends the rows that CHANGED since the token. A change of your rights changes no row: REVOKE ROLE, GRANT ROLE, or a GRANT or REVOKE on the table. So a diff cannot remove a row that you lost, or send a row that you gained.

Each fill frame carries a rights stamp (rights_stamp): a digest of your roles, your user and the policies of the table. Send it back with the resume:

WATCH accounts SINCE '<token>' RIGHTS '<stamp>' LIVE ALLOW GAP;

When the stamp differs from the stamp of the server now, the fill is FULL: you replace your set. The empty stamp RIGHTS '' never matches. A WATCH without RIGHTS keeps the diff. RIGHTS needs SINCE. The embedded client stores the stamp of each fill and sends it on each resume from a token.

A LIVE tail checks the rights too (B2682). The server keeps the stamp of the rows that it sent. When your rights change, the next tail diff is FULL (full is true): you replace your set. A GRANT ROLE, a REVOKE ROLE, and a GRANT or a REVOKE on the table (B2687) send that diff at once, also with no change of the table. A full tail diff runs under the limits of a fill (ALLOW GAP). A tail frame carries an empty rights_stamp: keep the stamp of the last fill.

Databases

A database holds its own tables and collections, and their indexes.

-- expect: OK
CREATE DATABASE mydb;
-- expect: 2 rows
SHOW DATABASES;
DROP DATABASE mydb;

CREATE DATABASE of a present name is refused with database already exists, and DROP DATABASE of an absent name is refused with database not found. When two clients send the same statement at the same time, one gets OK and the other gets the refusal, on a single server and on a cluster.

A user that is not a superuser needs a grant to create a database, and CONTROL on the database to drop it:

GRANT CREATE DATABASE TO ROLE tenants;
REVOKE CREATE DATABASE FROM ROLE tenants;

• CREATE DATABASE is global, so its grant takes no ON DATABASE and no WITH. A member of global_admins holds it.
• CREATE DATABASE <db> makes the role admins_<db> with the creator as its member. A member of admins_<db> or of global_admins holds CONTROL on <db>, so it may drop it (the admin roles).
• DROP DATABASE <db> drops admins_<db> in the same commit. Each user with <db> as its default database gets default again.
• A superuser or a member of global_admins sets the default database of a user. The database must exist:

ALTER USER 'ann' SET DEFAULT DATABASE shop;

SHOW DATABASES shows every database to a superuser. Any other user sees default and each database that has at least one table or collection that the user can see (the rule of SHOW TABLES). A database that the user cannot see does not show.

USE <database> switches the database of a session. It needs a server connection. A client or an agent names the database in the table name instead (mydb.accounts, see Qualified table names), so no statement depends on an earlier USE:

USE mydb;

USE needs a WebSocket session. POST /sql runs one statement per request and keeps no session, so it refuses USE and USE BRANCH. Over HTTP, write the qualified table name, or add ?db=<name> to the URL (POST /sql?db=mydb). A database that does not exist is refused with database not found: <name>: in ?db=, in the db of a client query and in a qualified table name. A database that the user cannot see in SHOW DATABASES gets the same refusal, in USE, in ?db=, in the db of a client query or a transaction, in a stream, in a WATCH and in a qualified table name. So the reply never tells that such a database exists.

POST /sql gives each refusal one of five HTTP status codes. A client must read the "type" of the JSON body too, because a refused statement gets 200 OK:

Refusal	HTTP status	Body
A refused statement: database not found in ?db=, USE, BEGIN, a parse error, a missing table	200 OK	{"type": "error", "message": ...}
A query parameter that is not one ?db=<name> with a value	400 Bad Request	{"type": "error", "message": ...}
A transaction body (application/vnd.kinedb.sql-txn) that does not decode	400 Bad Request	{"type": "error", "message": ...}
A body that is not UTF-8	400 Bad Request	the text Internal: invalid UTF-8; the node closes the connection
No Authorization header (client login required), or a token that is not valid	401 Unauthorized	{"type": "error", "message": ...}
A Content-Length over 64 MiB, the frame cap of the WebSocket too (B451)	413 Payload Too Large	a text with the length and the cap; the node closes the connection
A request line and headers of more than 64 KiB together (B451)	431 Request Header Fields Too Large	a text with the cap; the node closes the connection

A statement that does not parse gets its parse error with 200 OK, also with no login: a login does not make it parse.

The Python and the JavaScript client can set a default database (and branch) for a connection: database= / set_context in Python, database / setContext in JavaScript. The client sends the default with every statement, and it wins over USE. So a WebSocket session with a client default refuses USE and USE BRANCH with an error that names the client option.

A transaction runs in one database: the db of the transaction() call, else the database of the session (?db= on POST /sql). Every statement of the transaction is checked and written there, on every node the transaction reaches. USE cannot be a statement of a transaction. A CALL reads and writes in the database of the session too.

SHOW TABLES and SHOW COLLECTIONS list only the tables and collections of the database of the session. After USE mydb they list the entries of mydb, not those of default. They also list only the entries that you can use: an entry shows when a grant gives you SELECT, INSERT, UPDATE or DELETE on it, also a per-row grant (MATCH or WHERE). A user with no grant gets an empty list. A superuser sees every entry.

The other catalog statements use the same rule. DESCRIBE x, SHOW COLUMNS FROM x and SHOW INDEXES FROM x give table not found: x for an entry that you cannot use, the same answer as for a missing name. SHOW INDEXES with no name, SHOW TICKERS and the tables column of SHOW COMMITS leave out the entries that you cannot use. SHOW COMMITS also leaves out the names of other databases and of dropped tables. SHOW COMMITS and SHOW COMMIT <hash> never list a system collection, for example the audit events. A ticker is judged in the default database, because a ticker runs there. In the admin console, the schema tree of the query page loads the list again when you select a different database in the picker, or when you run USE <db> in the editor. The picker then shows that database. The same holds for a branch: the tree loads again when you select a branch in the branch picker, or when you run USE BRANCH <name> in the editor. The branch picker then shows that branch.

Qualified table names

A table name can name its database and its branch:

Name	The table
orders	orders in the database and on the branch of the session
app.orders	orders in the database app, on the branch main
app@dev.orders	orders in the database app, on the branch dev
[my app]@[dev].[orders]	each part can be [bracket] quoted

Use a qualified name, not USE, when you write a client or an agent. The target is then part of the statement. A session that loses its USE (a new connection, a reconnect) sends a bare name to default, and a qualified name still goes to its own database. A qualified name does not change the database or the branch of the session.

A qualified name stands where a table or collection name stands: in CREATE TABLE, INSERT, SELECT (also in a JOIN), UPDATE, DELETE, TRUNCATE TABLE, CREATE INDEX … ON, SHOW INDEXES FROM, SHOW COLUMNS FROM, DESCRIBE, EXPLAIN, WATCH and UNWATCH, among others. DESCRIBE app.orders shows the columns of orders in app. EXPLAIN SELECT * FROM app@dev.orders WHERE … shows the plan over the indexes of orders in app on the branch dev. A WATCH app.orders streams the changes of orders in app.

CREATE DATABASE app;
CREATE TABLE app.orders (id INT PRIMARY KEY, total INT);
INSERT INTO app.orders (id, total) VALUES (1, 100);
UPDATE app.orders SET total = 120 WHERE id = 1;
SELECT * FROM app.orders;

CREATE BRANCH acts on the database of the session, so make the branch from that database. Then name it in the table name:

USE app;
CREATE BRANCH dev;
USE default;
INSERT INTO app@dev.orders (id, total) VALUES (2, 50);
-- the row is on dev; app.orders on main does not hold it
SELECT * FROM app@dev.orders WHERE id = 2;

A server connection runs a qualified name of any database. The embedded mode runs every statement in default, on its current branch, so there a qualified name names that target:

-- expect: 5
SELECT COUNT(*) FROM default.accounts;

One statement reads and writes ONE database on ONE branch. All the table names of a statement, the bare names too, name the same target. A JOIN of orders (a bare name, in the session's default) with app.orders names two targets, and the answer is:

statement targets two databases/branches: 'default@main' and 'app@main'; one statement targets one (database, branch)

A transaction runs in one database (see Databases). Inside a transaction, a qualified name must name that database, on main. A different target answers transaction is pinned to database '<db>'; '<name>' targets '<db>@<branch>', and nothing of the transaction is written.

A database that does not exist answers database not found: <name>, and a branch that does not exist answers branch not found: <name>, the same answers as USE. A table that does not exist in the named database answers table not found: <name>, also when a table of that name exists in another database.

A name with three parts, a.b.c, reads as cluster.database.table. Linked clusters are not supported, so the name is refused. For a branch, write a@b.c:

`probe2.main.t2` reads as cluster.database.table; linked clusters are not supported. For a branch, write `probe2@main.t2`

Functions and tickers

A function is a script in kd, the scripting language of kinedb. Its parameters are variables in the body, and return gives the result. Every parameter and the result have a type, as in a stored procedure. Functions and tickers need a server.

CREATE FUNCTION add_one(x INT) RETURNS INT LANGUAGE kd AS $$ return x + 1; $$;
CALL add_one(41);
SHOW FUNCTIONS;
DROP FUNCTION add_one;

The signature

CREATE [OR REPLACE] FUNCTION [IF NOT EXISTS] name ( [param {, param}] )
    RETURNS ret LANGUAGE kd AS $$ … $$ [RUN AS 'user'] [WITH (timeout_ms = n)]
param := name type [NULL | NOT NULL]
ret   := type [NULL | NOT NULL]
       | TABLE ( column type [NULL | NOT NULL] {, …} )

• WITH (timeout_ms = n) sets the wall time of one call, in milliseconds, from 1 to 300000. Without it a call has 30000 ms (the limits of kd).

• The parentheses are necessary, also for a function with no parameters: ping().

• NOT NULL is the default. A parameter or a result can be null only when you write NULL after its type.

• The server refuses a parameter without a type and a function without RETURNS.

A parameter and the result take these types:

Type	The value
INT	a 64-bit signed integer
FLOAT	a 64-bit floating-point number
TEXT	a UTF-8 string
BOOL	true or false
TIMESTAMP	microseconds since the Unix epoch
VECTOR(n)	a list of n numbers
VECTOR	a list of numbers of any size, as a VECTOR column
ROW	a document: a JSON object. In the body it is a kd object.

The aliases of Data types are correct too, for example INTEGER or BOOLEAN. BLOB, BYTES and COUNTER are refused for a parameter, because a CALL cannot carry them. There is no ANY type and no VALUE type: the server refuses RETURNS VALUE.

function <f>: parameter <x> has no type: write a type, for example <x> INT
function <f> has no RETURNS type: write RETURNS <type> or RETURNS TABLE (...)

How CALL checks the arguments

CALL converts each argument to the type of its parameter. It uses the rule of an INSERT into a column of the same type, and no other rule. So a value that an INSERT refuses for an INT column, CALL refuses for an INT parameter.

• A missing argument is an error. When the parameter is NULL, a missing argument is null.
• An extra argument is an error.
• A null argument for a NOT NULL parameter is an error.
• After one of these errors, no kd code runs.

function <f>: argument <i> (<x>) must be <TYPE>, got <TYPE>
function <f>: argument <i> (<x> <TYPE>) is missing
function <f>: argument <i> (<x>) is NOT NULL, got null
function <f> takes <n> arguments, got <m>

CREATE FUNCTION greet(name TEXT, title TEXT NULL) RETURNS TEXT LANGUAGE kd AS $$
  if title == null { return "Hello " + name; }
  return "Hello " + title + " " + name;
$$;
CALL greet('Ada');
CALL greet('Ada', 'Dr.');
DROP FUNCTION greet;

The server checks the return value before the writes of the call commit. The check uses the same conversion rule as an argument. Thus a whole number such as 3 * 2.0 (kd gives 6) answers 6.0 for RETURNS FLOAT. A whole number above 2^53 fails. A value of the wrong type, or a null for a NOT NULL result, fails the call, and no write of the call commits:

function <f> must return <TYPE>, got <TYPE>; the writes of the call did not commit

A node that is behind never gives these errors from an old definition (B2938). Right after CREATE OR REPLACE FUNCTION on one node, another node can still hold the old definition. When that node is not confirmed current for the catalog, it does not answer an argument error, a return-type error or a parse error of the body. It also does not answer a Function execution error of the old body (B2946). It sends the call on to a current node, or it answers a temporary error with retryable set. A current node checks the call again with the new definition. The body writes nothing before its commit, so no write runs two times. On a current node a body that fails runs two times.

RETURNS TABLE (…) makes the CALL answer rows. The body returns an array of objects. The CALL gives the declared columns, and one row for each object of the array. A key that is not a declared column fails the call; a missing key is null. Write a column name that is a keyword in brackets, for example RETURNS TABLE (_id TEXT, [document] TEXT):

CREATE FUNCTION top_two() RETURNS TABLE (name TEXT, score INT NULL) LANGUAGE kd AS $$
  return [{name: "ada", score: 9}, {name: "bob", score: null}];
$$;
CALL top_two();
DROP FUNCTION top_two;

A function without types

A function that an older release stored has no types. The server keeps it, and SHOW FUNCTIONS shows it with typed = false. The server refuses its CALL:

function <f> has no parameter types: replace it with CREATE OR REPLACE FUNCTION

To repair it, read the body with SHOW CREATE FUNCTION, then send CREATE OR REPLACE FUNCTION with a type for each parameter and a RETURNS clause. The grants of the function stay. The Functions page of the web console marks such a function, and its Edit form writes the types.

Who may call a function

A CALL needs the INVOKE right on the function. root can call every function. A function with no grant is for root only, as a collection with no grant is. Give the right to ANYONE, AUTHENTICATED or ROLE <name>. ALL on a function means INVOKE. A function has no rows, so MATCH and WHERE are refused. For a user without the right, the CALL of a function that does not exist gets the same refusal as a denied CALL.

CREATE FUNCTION add_two(x INT) RETURNS INT LANGUAGE kd AS $$ return x + 2; $$;
GRANT INVOKE ON FUNCTION add_two TO AUTHENTICATED;
REVOKE INVOKE ON FUNCTION add_two FROM AUTHENTICATED;
GRANT INVOKE ON FUNCTION add_two TO ANYONE;
SHOW GRANTS ON FUNCTION add_two;
DROP FUNCTION add_two;

DROP FUNCTION removes the grants of the function. A new function with the same name starts with no grant.

A function has a second right, CONTROL. A user with CONTROL on a function may drop the function and may grant and revoke rights on it (INVOKE and CONTROL). ALL on a function does not give CONTROL. CONTROL does not let a user replace the function or set its run-as user: CREATE OR REPLACE FUNCTION keeps the rules below.

CREATE ROLE IF NOT EXISTS fn_admins;
CREATE FUNCTION add_one(x INT) RETURNS INT LANGUAGE kd AS $$ return x + 1; $$;
GRANT CONTROL ON FUNCTION add_one TO ROLE fn_admins;
REVOKE CONTROL ON FUNCTION add_one FROM ROLE fn_admins;
DROP FUNCTION add_one;

Upgrade note (B1376): before this release every user that logged in could call every function. Now a user that is not root needs GRANT INVOKE ON FUNCTION. Add the grant for each function that such users call.

By default the body runs with the rights of the caller: INVOKE does not give the body more rights. RUN AS <user>, the last clause, makes the body read and write with the rights of that user, for every caller, the guest included:

CREATE FUNCTION add_score(p TEXT) RETURNS INT LANGUAGE kd AS $$ insert("scores", {player: p}); return 1; $$ RUN AS 'web';
GRANT INVOKE ON FUNCTION add_score TO ANYONE;

• The run-as rule. The run-as user must be eligible: the home of the user is default, and the user is not a member of global_admins. So root and a global admin are never run-as users. The creator needs CONTROL on the run-as user. A new user holds CONTROL on himself, so RUN AS '<self>' works for an eligible user. A superuser holds CONTROL on every user, but the run-as user must be eligible for a superuser too. Any user that is not a superuser needs CREATE FUNCTION. CREATE FUNCTION with no RUN AS needs CREATE FUNCTION too (Who may create).
• A refused run-as user gives function '<f>' may not run as '<u>': its home is not 'default' or it is a global admin, or a function may run as '<u>' only for a holder of CONTROL on user '<u>'.
• The user must exist when you create the function. The server resolves the user again on every CALL, so a role you grant or revoke later applies to the next call. When the user no longer exists, the CALL fails and the body does not run. When the user is no longer eligible (a new home, or a member of global_admins now), the CALL fails with function '<f>' may not run as '<u>': its home is not 'default' or it is a global admin. A conflict function with a run-as user follows the same rule.
• DROP FUNCTION, GRANT and REVOKE on the function need a superuser, the owner or the run-as user of the function, or a user with CONTROL on the function. A GRANT or REVOKE of CONTROL needs a superuser or CONTROL.
• SHOW FUNCTIONS shows the run-as user in the run_as column. The column is NULL for a function that runs as its caller.
• The cluster judges these rules against the function as it is at the moment the statement applies, in the order of the log. When a superuser changes the run-as user of the function while your statement is in flight, your statement fails with the same permission denied error, and the change of the superuser stays.
• A node that has not yet applied the newest function changes never refuses a function statement from its old copy. This applies to CALL, SHOW CREATE FUNCTION, CREATE FUNCTION (also with IF NOT EXISTS or OR REPLACE), DROP FUNCTION, GRANT INVOKE and REVOKE INVOKE, and to a FUNCTION conflict policy of CREATE TABLE, CREATE COLLECTION and ALTER TABLE. It applies also when the node has not yet applied the newest users: a RUN AS user that the node does not know yet. The node sends the statement on to a current node, or it answers an error that you can retry.

SHOW FUNCTIONS gives one row per function with the columns name, language, run_as, params, param_types, returns and typed:

• params is a JSON array of the parameter names, in order, and [] for a function with no parameters.
• param_types is a JSON array with one object for each parameter, in order: {"name": "x", "type": "INT", "null": false}.
• returns is the RETURNS clause as text, for example INT, TEXT NULL or TABLE (name TEXT, score INT NULL).
• typed is false for a function without types, and true for every other function.

Every user that logged in may run SHOW FUNCTIONS; the guest may not. A superuser sees every function. Any other user sees only the functions that it may run (an INVOKE grant that applies to it) and the functions whose run-as user it is.

CREATE OR REPLACE FUNCTION changes a function in one step. When the function does not exist, the statement creates it. When it exists, the statement replaces the signature and the body together, and there is no moment in which the function does not exist. A CALL that started before the replace runs the old body to its end. The next CALL runs the new body, on every node.

CREATE OR REPLACE FUNCTION add_three(x INT) RETURNS INT LANGUAGE kd AS $$ return x + 3; $$;
GRANT INVOKE ON FUNCTION add_three TO AUTHENTICATED;
CREATE OR REPLACE FUNCTION add_three(x INT, y INT) RETURNS INT LANGUAGE kd AS $$ return x + y + 3; $$;
CALL add_three(1, 2);
DROP FUNCTION add_three;

• The grants of the function stay, and a ticker that calls the function keeps its schedule.
• A superuser may replace every function. Any other user may replace a function that runs as that user, a function that the user owns, or a function on which the user holds CONTROL. The new statement has no RUN AS, or a RUN AS user that the caller may name (the run-as rule above). So a replace never changes the run-as user to a user that the caller may not name.
• CREATE FUNCTION IF NOT EXISTS <name> does nothing when the function exists, and the statement succeeds. A plain CREATE FUNCTION of a name that exists fails with Function '<name>' already exists.
• SHOW FUNCTIONS does not show the body, because every caller may run it.

SHOW CREATE FUNCTION <name> shows the definition of one function. It gives one row with the columns name, params, run_as, source and statement. params is a JSON array of the parameter names. run_as is NULL for a function that runs as its caller. source is the body, byte for byte. statement is a CREATE OR REPLACE FUNCTION statement that re-creates the function. Run it, and the function gets the same typed parameters, RETURNS clause, body and run-as user. For a function without types, statement has no types and no RETURNS clause, so the server refuses it: add the types before you run it.

CREATE FUNCTION add_four(x INT) RETURNS INT LANGUAGE kd AS $$ return x + 4; $$;
SHOW CREATE FUNCTION add_four;
DROP FUNCTION add_four;

• A superuser may read every function. The run-as user of a function may read that function. Other users and the guest may not. A function with no RUN AS is for a superuser only.
• A user that may not read gets the same refusal for a missing function and for a function that exists. For a superuser, a missing function gives the error of DROP FUNCTION.
• A body that contains $$ gets a tagged quote in statement, for example $kd$ … $kd$.

The web console shows the functions on its Functions page (/ui/#/functions). The page lists each function with its typed signature, its language, its run-as user and its INVOKE grants. It marks a function without types with the refusal of its CALL. It creates, replaces and drops a function, and it grants and revokes INVOKE with the statements of this section. The page shows the statement before it sends it.

In the form, a parameter is a row with a name, a type and a NULL box. RETURNS is a type and a NULL box, or TABLE with its columns. The Edit button loads the body with SHOW CREATE FUNCTION, and you change the signature, the body and the run-as user. The page then sends CREATE OR REPLACE FUNCTION. An empty run-as user means that the body runs as its caller. A user that may not read the definition sees the refusal of the server.

A function on an offline device (planned, B2175)

This section is planned (B2175). It is not in main yet. The embedded database will run a function offline, so the device will receive the functions that the user may call.

• The code goes to the device. The device receives each function that the user has INVOKE on, with its body. So every user with INVOKE can read the body, also when SHOW CREATE FUNCTION refuses that user. GRANT INVOKE … TO ANYONE sends the body to every device.
• SERVER ONLY keeps a function on the server. The device does not receive a SERVER ONLY function. Mark each function that holds secret logic SERVER ONLY. An offline call to it waits, and the server runs it at sync.
• A revoke reaches an offline device late. The device can call the function until it syncs. The server refuses the call at sync.
• An offline call gives a preview. The preview sees only the data on the device, so it can be different from the result of the server. At sync, the server runs the call again, and the result of the server replaces the preview.
• In the SDK, call(name, args, { offline }) chooses preview, queue or refuse for one call.

A small API with a function

A site that reads as the guest can need one small write, for example one row in a queue. GRANT INSERT … TO ANYONE gives that write to every client, with any values. A server route of your own needs a password on your server. A function does the write with no route and no password: it runs as a user that may write, and ANYONE may call it.

The example: a site shows company reports, and its "Fetch latest reports" button adds one row to the queue cvrreportqueue. The user web never logs in. It exists to hold the right that the body needs, and nothing more:

CREATE TABLE cvrreportqueue (enhedsNummer INT PRIMARY KEY, cvr INT, name TEXT, attempts INT);
CREATE USER 'web' PASSWORD 'a-long-random-password';
CREATE ROLE queue_writer;
GRANT ROLE queue_writer TO 'web';
GRANT INSERT ON cvrreportqueue TO ROLE queue_writer;
CREATE FUNCTION queue_reports(enhedsNummer INT, cvr INT, name TEXT) RETURNS ROW LANGUAGE kd AS $$
  if enhedsNummer <= 0 {
    return {error: "enhedsNummer must be a positive integer"};
  }
  if cvr < 10000000 || cvr > 99999999 {
    return {error: "cvr must have 8 digits"};
  }
  if length(name) == 0 || length(name) > 200 {
    return {error: "name must have 1 to 200 characters"};
  }
  insert("cvrreportqueue", {enhedsNummer: enhedsNummer, cvr: cvr, name: name, attempts: 0});
  return {queued: enhedsNummer};
$$ RUN AS 'web';
GRANT INVOKE ON FUNCTION queue_reports TO ANYONE;

The browser sends the CALL with no login:

curl -s -X POST http://localhost:4820/sql \
  --data-binary "CALL queue_reports(4001364731, 26572517, 'IMERCO A/S')"

A table INSERT with a key that exists replaces the row, so a second click queues the same company again with attempts 0.

The queue can be a document collection. There insert() writes the object as a new document with a new _id, so a second click queues a second document. To keep one document for each company, the body writes the document with UPSERT … ON (<field>), and gives the document as a parameter. UPSERT replaces the document with the same enhedsNummer, so a second click keeps one document. INSERT is the only right that web needs, also for the replace. In place of the table and the function above:

CREATE COLLECTION cvrreportqueue TYPE DOCUMENT;
CREATE INDEX idx_cvrreportqueue_enhedsnummer ON cvrreportqueue (enhedsNummer);
GRANT INSERT ON cvrreportqueue TO ROLE queue_writer;
CREATE FUNCTION queue_reports(enhedsNummer INT, cvr INT, name TEXT) RETURNS ROW LANGUAGE kd AS $$
  // the same three checks as above
  query("UPSERT INTO cvrreportqueue ON (enhedsNummer) $1",
        [{enhedsNummer: enhedsNummer, cvr: cvr, name: name, attempts: 0}]);
  return {queued: enhedsNummer};
$$ RUN AS 'web';
GRANT INVOKE ON FUNCTION queue_reports TO ANYONE;

Do not build the document with to_string({…}) and join it into the SQL text. The parameter puts the object into the statement as one JSON document, so a ' or a " in name stays in the value (parameters).

RUN AS gives every caller the rights of web inside the body. So the body is the only guard, and a function that anyone may call must obey these rules:

• Check every argument. The signature checks the type, and NOT NULL refuses a null (How CALL checks the arguments). The body checks the range and the length. When a check fails, return a value that names the error, before the body writes.
• Write only what the function must. The body names the table and the columns. The caller gives values, never a table name, a column name or a WHERE.
• Never join an argument into SQL text. insert() and update() quote each value of their object. A where string and the SQL of query() run as they are, so a string argument there can change the statement. Give the argument as a parameter: query("DELETE FROM t WHERE name = $1", [name]) or, for a document collection, query("UPSERT INTO c ON (k) $1", [{k: key, name: name}]) (parameters).
• Give the run-as user only the rights of the body. Here web has INSERT on one table. A mistake in the body then cannot read or change anything else.

The CALL text that the browser sends needs no such care. The guest can send any statement that its ANYONE grants allow, with or without the page. This example adds only INVOKE on one function to those grants.

A ticker calls a function on a schedule. OVERLAP SKIP (the default) skips a run while the last run still works; OVERLAP WAIT waits for it. A tick calls the function with no arguments, so CREATE TICKER refuses a function that has a NOT NULL parameter, and a function without types. Each tick goes through the checks of a CALL.

CREATE FUNCTION ping() RETURNS INT LANGUAGE kd AS $$ return 1; $$;
GRANT INVOKE ON FUNCTION ping TO AUTHENTICATED;
CREATE TICKER ping_every_minute EVERY 60s CALL ping OVERLAP SKIP RUN AS 'web';
SHOW TICKERS;
DROP TICKER ping_every_minute;

The full form is CREATE TICKER <name> [ON <collection>] EVERY <duration> CALL <function> [OVERLAP SKIP | WAIT] [RUN AS <user>]. RUN AS, the last clause, makes each tick run as that user. It follows the run-as rule of a function: the user is eligible (home default, no member of global_admins), and the creator holds CONTROL on the user. With no RUN AS, the ticker runs as its creator, and the creator must be eligible. So root and a global admin create a ticker only with RUN AS. A refused run-as user gives ticker '<k>' may not run as '<u>': its home is not 'default' or it is a global admin.

In a cluster (--calvin), CREATE TICKER and DROP TICKER go through the metadata log, the same as CREATE FUNCTION. You can send them to any node. The statement answers after the change is applied on that node, and every node then has the same tickers in SHOW TICKERS. Only the metadata leader runs the ticks. After a leader change, the new leader runs every ticker.

A ticker runs as its RUN AS user, else as the user who created it. The server resolves that user again on every tick, so a role you grant the user later applies to the next tick.

A ticker does not run when the server cannot resolve an identity for it:

• The user is no longer eligible: its home is not default now, or it is a member of global_admins now. A ticker of root with no RUN AS, made before this rule, is in this case too. The server skips every tick and writes one warning.
• The creator no longer exists. The server skips every tick and writes one warning. It does not delete the ticker: the ticks start again if you create the user again with CREATE USER.
• The ticker records no creator, and the server requires client authentication (KINEDB_CLIENT_AUTH=required, the default). A ticker created before the run-as-creator rule has this shape. Create it again over an authenticated session to give it a creator.

A server with KINEDB_CLIENT_AUTH=permissive applies no policy to any client, so a ticker with no creator keeps running there.

Triggers

A trigger calls a function for each row that a write changes. A BEFORE trigger runs before the row is written. It can change the new row, write other rows or refuse the write. An AFTER trigger runs after the commit (AFTER triggers). Triggers need a server.

CREATE TRIGGER name BEFORE | AFTER event [OR event ...] ON table CALL function
DROP TRIGGER [IF EXISTS] name
SHOW TRIGGERS [ON table]
event := INSERT | UPDATE | DELETE

CREATE TABLE orders (id INT, v TEXT, PRIMARY KEY (id));
CREATE TABLE audit (id INT, what TEXT, PRIMARY KEY (id));
CREATE FUNCTION stamp(old_row ROW NULL, new_row ROW NULL) RETURNS ROW NULL LANGUAGE kd AS $$
  if new_row == null { return null; }
  if new_row.v == "bad" { refuse("v may not be bad"); }
  insert("audit", {id: new_row.id, what: "stamped"});
  return {v: new_row.v + "!"};
$$;
CREATE TRIGGER stamp_orders BEFORE INSERT OR UPDATE ON orders CALL stamp;
INSERT INTO orders (id, v) VALUES (1, 'one');
SELECT id, v FROM orders;
SHOW TRIGGERS ON orders;
DROP TRIGGER stamp_orders;

The INSERT writes one! into orders and one row into audit, in one commit.

• A trigger name is global, as a function name is. The table is a table of the current database.
• SHOW TRIGGERS gives the columns name, table, timing, events and function, sorted by name. It lists only the triggers of the current database, and only the triggers of a table that you can see.
• DROP TABLE makes each trigger of the table inert. A new table with the same name does not get the old trigger, and a new trigger can take its name.
• DROP FUNCTION is refused while a live trigger calls the function.

The trigger function

A trigger function has this signature, and no other:

(old_row ROW NULL, new_row ROW NULL) RETURNS ROW NULL

• old_row is the stored row. It is null for an INSERT.
• new_row is the row that the write will store. It is null for a DELETE.
• When the function returns an object, each field of the object sets that cell of the new row. Cells that the object does not name keep their value.
• When the function returns null, the new row does not change. For a DELETE the returned value has no effect.
• A field that is not a column of the table refuses the write. A change of the primary key refuses the write.
• refuse(reason) refuses the write. Any other error of the body refuses the write too.
• The writes of the body (insert(), update(), query()) commit in the same batch as the write. One refusal refuses the whole batch, and nothing changes.

the trigger refused the write: <reason>. NOTHING was changed.
function <f> cannot run as a trigger: a trigger function has the signature (old_row ROW NULL, new_row ROW NULL) RETURNS ROW NULL. NOTHING was changed.
a trigger on table <t> returned the unknown column <c>; NOTHING was changed
a trigger on table <t> may not change the primary key <c> of a row; NOTHING was changed

The server checks the signature when a write runs the trigger, not at CREATE TRIGGER. See kd for the body of a trigger function.

The order and the depth

The BEFORE triggers of one table and one event run in the order of their names. Each trigger gets the new row that the trigger before it returned.

A write of a trigger body to a table with a trigger runs that trigger too, one level deeper. The triggers of a client statement run at depth 1. A trigger at depth 9 refuses the whole write:

trigger <name> on table <t> would run at depth 9: the trigger depth limit is 8. A trigger that fires a trigger (or a cycle of triggers) went too deep. NOTHING was changed.

Triggers in a transaction and in a CALL

The statements of a transaction (BEGIN … COMMIT) and of a CALL body run their triggers in statement order. The trigger of statement N sees the writes of statements 1 to N-1. The whole block commits as one batch, or nothing commits.

A trigger body that reads a table that an earlier write of the batch changed must use a plain read. Other read forms are refused:

a trigger body reads table <t>, which an earlier write of this batch changed; such a read must be `SELECT <columns or *> FROM <t> [WHERE ...]`, with no ORDER BY, LIMIT, OFFSET, GROUP BY, aggregate or join. NOTHING was changed.

Who may create and run a trigger

CREATE TRIGGER needs three rights:

• the CREATE TRIGGER right in the database (Who may create),
• CONTROL on every row of the table,
• INVOKE on the function.

DROP TRIGGER needs the creator of the trigger or a superuser:

permission denied: CREATE TRIGGER <name> needs CONTROL on every row of table '<t>' for user '<u>'
permission denied: CREATE TRIGGER <name> needs the INVOKE right on function '<f>' for user '<u>'
permission denied: trigger '<name>' may be dropped only by its creator or a superuser for user '<u>'

A write that runs a trigger needs INVOKE on the function for the user that writes. The body runs as the run-as user of the function (RUN AS), else as the user that writes. The writes of a body with a run-as user need the rights of the run-as user and of the user that writes.

A retry after a concurrent change

An UPDATE or a DELETE reads its rows before the triggers run. When another write changes such a row before the commit, the server refuses the write with retryable set, and nothing changes. A retry runs the triggers again on the new row. In a cluster, a write that a node forwards to another node is refused with retryable set too: the trigger runs only on the node that received the write from the client.

AFTER triggers

An AFTER trigger runs after the commit of its write, not in the write. Each row that the write changes commits ONE event with the row, in the same commit. A refused write makes no event. The server runs the events of a shard one at a time, in the order of their commits, on the node that leads the shard.

• old_row and new_row are the committed rows: the stored row before the write and the row after it, after every BEFORE trigger. A plain INSERT over a stored row is an INSERT event, and its old_row is the row that it replaced.
• The replaced rows of the INSERT events of one commit have a cap on their sum (the Raft entry limit). Past the cap, an event gets old_row null and the global old_row_omitted true; the write is not refused. In every other case old_row_omitted is false.
• The function returns nothing that is used. The writes of its body commit as a new batch, as the user that wrote the row (or the RUN AS user of the function). A write of that batch runs the BEFORE triggers of its table in the batch, and the AFTER triggers of its table as new events, one level deeper. The depth limit 8 holds.
• The body reads the data of the time when it runs, which can be later than the commit.
• The writes of an event commit once: the body batch and the removal of the event commit together. When the node stops before that commit, the event stays, and the next leader of the shard runs the body again. So an effect outside the database can run more than once.
• An error of the body, or a refusal of its batch, ends the event: the server removes it and does not retry it. The node logs [B3831_AFTER_FAILED] and counts the event in the metric triggers.after_failed_total (SHOW METRICS).
• DROP TRIGGER before an event runs: the event does not run that trigger.
• An UPDATE that changes no cell writes no row, so it makes no event.
• TRUNCATE TABLE is refused on a table with an AFTER trigger until #3471 gives a deterministic carry of the pending events:

table <t> has an AFTER trigger; drop the trigger to truncate

What is not done yet

• Other write forms. INSERT FILE, a file write of the client protocol and MERGE BRANCH into a table with a live trigger are refused. A write on a branch and a write to a document collection with a trigger are refused too.
• No trigger runs for a sync push or a bulk load. kd-tool restore uses a bulk load, so it runs no trigger.
• A file cell. A fired UPDATE or DELETE of a row with a BLOB cell is refused.

The refusal of the forms that do not run a trigger:

triggers on table <t> do not run yet in this release; drop the trigger to write

Users, roles and permissions

These statements need a server.

CREATE USER 'alice' PASSWORD 'secret123';
ALTER USER 'alice' SET PASSWORD 'newsecret';
ALTER USER 'alice' SET PASSWORD 'newsecret2' KEEP SESSIONS;
ALTER USER 'alice' REVOKE SESSIONS;
SHOW USERS;
DROP USER 'alice';

CREATE USER refuses a name that exists. The error is user 'alice' already exists; use CREATE OR REPLACE USER 'alice' PASSWORD '…' to replace its password, or ALTER USER 'alice' SET PASSWORD '…'. So a provisioning script that runs two times does not change a live password. Three forms control what happens when the name exists:

Statement	The name does not exist	The name exists
CREATE USER 'alice' PASSWORD '…'	creates the user	an error; nothing changes
CREATE USER IF NOT EXISTS 'alice' PASSWORD '…'	creates the user	success; nothing changes
CREATE OR REPLACE USER 'alice' PASSWORD '…'	creates the user	replaces the password

CREATE OR REPLACE USER keeps the roles, the grants and the credentials of the user (how to drop the credentials). It also keeps the option values of the user, unless the statement has WITH. A replace that changes the password ends the open sessions of the user, and a replace with the same PASSWORD HASH keeps them. ALTER USER … SET PASSWORD is the statement for a deliberate rotation. It refuses a user that does not exist (user 'alice' not found). A new password ends the open sessions of the user, unless the statement has KEEP SESSIONS (what a password change does to the sessions). ALTER USER … REVOKE SESSIONS ends the sessions without a password change.

A user with no password

NO PASSWORD in place of PASSWORD '…' makes a user that has no password. All three forms of the table above take it, and so does WITH.

CREATE USER 'u_one' NO PASSWORD;
CREATE USER IF NOT EXISTS 'u_two' NO PASSWORD WITH SET_OWN_PASSWORD = OFF;
SHOW CREATE USER 'u_one';
DROP USER 'u_one';
DROP USER 'u_two';

No password logs in as such a user. A password login gets the answer of a wrong password. CREATE OR REPLACE USER '<u>' NO PASSWORD removes the password of a user and ends the open sessions of the user. ALTER USER '<u>' SET PASSWORD '…' gives the user a password again. SHOW CREATE USER writes CREATE USER '<u>' NO PASSWORD [WITH …] for such a user. The user root always has a password: NO PASSWORD for root is refused.

Change a password

An administrator (a user with the superuser right, such as root) can set the password of any user. A user that is not an administrator changes the own password, and the password of another user when a password right reaches that user (the password of another user). Use CURRENT_USER to name the user that runs the statement. A quoted 'CURRENT_USER' is a user name, not the keyword.

-- An administrator sets a password with no old password.
ALTER USER 'alice' SET PASSWORD 'newsecret';
-- A user changes the own password and gives the old password (RESET).
ALTER USER CURRENT_USER SET PASSWORD 'newsecret' REPLACE 'oldsecret';
-- A user sets the own password with no old password (SET).
ALTER USER CURRENT_USER SET PASSWORD 'newsecret';
-- Keep the open sessions.
ALTER USER CURRENT_USER SET PASSWORD 'newsecret' REPLACE 'oldsecret' KEEP SESSIONS;

Write REPLACE '<old>' before KEEP SESSIONS. The server checks the old password before it changes the password. A wrong old password gets the error permission denied: the old password is not correct for user '<name>'. SHOW AUDIT shows it as the action auth_ddl_refused with the reason bad_credentials and the user as the actor, so a monitor of failed logins also finds it. The server writes no password, new or old, in a log, in SHOW STATEMENTS or in an error.

Three options control what a user that is not an administrator can do. An administrator can always set any password and can always use KEEP SESSIONS.

Option	Default	What it allows
CHANGE_OWN_PASSWORD	on	The user changes the own password with REPLACE '<old>'.
SET_OWN_PASSWORD	off	The user sets the own password with no old password.
KEEP_SESSIONS_ON_PASSWORD_CHANGE	off	The user adds KEEP SESSIONS to a change of the own password.

A refused change gets permission denied: <OPTION> is off for user '<name>'. The options apply only to a change of the own password. A statement that names another user needs a password right on that user.

CHANGE_OWN_PASSWORD = OFF alone does not stop a change with no old password when SET_OWN_PASSWORD is on. To refuse every change of the own password, set both options OFF.

Each option has a value for one user and a value for the cluster. The value of the user overrides the value of the cluster. Only an administrator can change an option:

-- A value for one user: ON, OFF or DEFAULT. DEFAULT removes the value of the user.
ALTER USER 'alice' SET SET_OWN_PASSWORD = ON;
ALTER USER 'alice' SET CHANGE_OWN_PASSWORD = OFF;
ALTER USER 'alice' SET KEEP_SESSIONS_ON_PASSWORD_CHANGE = DEFAULT;
-- A value for the cluster: true or false.
SET CLUSTER cluster.auth.default_reset_password = true;
SET CLUSTER cluster.auth.default_set_password = false;
SET CLUSTER cluster.auth.keep_sessions_on_password_change = false;

The server checks the value of a user two times: when it accepts the statement, and again when the change applies on each node. It checks the value of the cluster only when it accepts the statement. Thus a SET CLUSTER that applies between these two points does not stop that one change. The reason: a node loads the last cluster value when it starts, so the cluster value at an earlier point of the log is not known on every node. To stop a change at once, set the value of the user.

A password change ends the open sessions of the user. When a user changes the own password, the server ends the other sessions and keeps the session that ran the statement. With KEEP SESSIONS, the server ends no session. A bearer token that ran the change works on every node after it, also for a next change of the own password; the next end of the sessions of the user (REVOKE SESSIONS, a password change by an administrator) ends it.

A user that is not an administrator changes the own password only from a live session. When an administrator ended the session first (REVOKE SESSIONS, or a new password), the change gets session expired or revoked: log in again and changes nothing. This is also true on a node that did not apply the end of the session yet.

The password of another user

Two rights on a user let a holder change the password of that user. Give them with a rule on one user or on the users of a database:

CREATE ROLE desk;
CREATE USER 'pat' PASSWORD 'pat-secret';
-- SET PASSWORD: set the password of pat, with or without the old password.
GRANT SET PASSWORD ON USER 'pat' TO ROLE desk;
-- RESET PASSWORD: change the password of each user of the home default,
-- only with the old password (REPLACE).
GRANT RESET PASSWORD ON USERS IN DATABASE default TO ROLE desk;
-- The short form of a rule on the users of a database.
GRANT RESET PASSWORD ON DATABASE default TO ROLE desk;
SHOW GRANTS;
REVOKE SET PASSWORD ON USER 'pat' FROM ROLE desk;
REVOKE RESET PASSWORD ON USERS IN DATABASE default FROM ROLE desk;
DROP USER 'pat';
DROP ROLE desk;

A holder of the role desk then runs ALTER USER 'pat' SET PASSWORD '<new>' (SET PASSWORD), or ALTER USER 'pat' SET PASSWORD '<new>' REPLACE '<old>' (RESET PASSWORD).

• Who holds the right on user u. A rule on u, a rule on the users of the home of u (ON USERS IN DATABASE <home>), a member of admins_<home>, and a member of global_admins. The home of u is the database of DEFAULT DATABASE, or default.
• SET PASSWORD sets the password with or without REPLACE '<old>'.
• RESET PASSWORD needs REPLACE '<old>', and the server checks the old password. A wrong old password counts in the limit of the user that runs the statement (the limit of failed password checks).
• A global admin is protected. Only a member of global_admins changes the password of a member of global_admins. A rule on such a user gives no password right. root is a member.
• KEEP SESSIONS on another user needs a member of global_admins. Every other change ends all the sessions of the user.
• CONTROL is not a password right. CONTROL on a user does not change the password.
• Who gives the rules. A member of global_admins, or a member of admins_<home> for a user of that home and for ON USERS IN DATABASE <home>. A holder of a password right does not give it to another holder.
• A user that the caller cannot see answers user '<name>' not found. A missing right answers permission denied: SET PASSWORD or RESET PASSWORD on user '<name>' is needed for user '<caller>'. A change with no REPLACE of a holder of RESET PASSWORD only answers permission denied: RESET PASSWORD on user '<name>' needs REPLACE '<old>' for user '<caller>'.
• The cluster judges the right again when the change applies, in the order of the log. When the right goes away while the change is in flight, the change fails, and the password stays.

The limit of failed password checks

Each node limits the failed password checks. The limit applies to a login with a password (WebSocket and POST /login), a login with an API secret, and REPLACE '<old>'. A login with a session token checks no password, and the limit does not apply to it.

The limit is a set of buckets of tokens. A failed check takes one token. When a bucket is empty, the node refuses the check and runs no password check. A bucket gets its tokens back at a fixed rate, so a refusal always ends. A login uses three buckets:

Bucket	Failed checks before a refusal	One check back each
The source address	20	6 s
The user and the source address	5	60 s
The user from all source addresses	20	30 s

REPLACE '<old>' uses one bucket for the user that runs the statement: 5 failed checks, and one check back each 60 s. The node that the client connects to checks it, before it sends the statement to another node.

A check holds its token while it runs. So many checks at the same time cannot run more password checks than a bucket has tokens. When a bucket has no free token, but checks that run now hold some of its tokens, a new check waits for one of them to end. A correct password gives its token back, and the waiting check then runs. A wrong password keeps its token, and the waiting check gets the refusal. So 8 logins of one user from one address at the same time all succeed with the correct password. A check waits at most 5 s, and at most 64 checks of a node wait at the same time. Past these limits, the check gets the refusal at once.

A refused check gets the error AUTH_THROTTLED: too many failed password checks; retry in <N> s, with retry true. POST /login answers 429 Too Many Requests with the header Retry-After: <N>. The answer is the same for a user that exists and for a user that does not exist. SHOW AUDIT shows the refusal with the reason throttled: the action login, api_secret_login or auth_ddl_refused. The count of each bucket is on its node. The node forgets it when it restarts.

A person who knows a user name can empty the bucket of that user from all source addresses. To make this less of a problem, the node remembers the source addresses that logged in as the user in the last 30 days, 8 for each user at most. The bucket of the user from all source addresses does not apply to such an address. Thus during such an attack the user still logs in from a usual address and with a session token. A new address of the user waits until the attack stops and the bucket gets its tokens back.

An IPv6 address counts by its /64 prefix. Behind a reverse proxy, every client has the address of the proxy. Set the trusted proxies (--trusted-proxy), so that the node takes the client address from X-Forwarded-For.

What REVOKE SESSIONS ends

ALTER USER … REVOKE SESSIONS ends the sessions of the user on every node of the cluster. The node that answers the statement is not important. Once the statement answers, the user does no more work through any session it held:

The client	What the server does
An HTTP bearer token	The next request gets 401, on every node.
An authenticated WebSocket connection	The server closes the connection at its next frame.
A WATCH subscription	The server sends it no more notifications.

A bearer token is signed, so it works on every node and after a restart of the nodes. To end ONE token and keep the other sessions of the user, send POST /logout with that token. REVOKE SESSIONS, a new password, DROP CREDENTIAL and two ALTER CLUSTER ROTATE TOKEN KEY also end a token that POST /token/refresh made (Refresh a token). A REVOKE ROLE and a DROP CREDENTIAL reach a bearer token at its next request: the server reads the roles and the credentials of the user at each request.

Known limits of the bearer token:

• A node that is behind accepts a token that a password change, REVOKE SESSIONS, DROP USER, DROP CREDENTIAL or a logout ended, until that node applies the change. The statement answers after the commit, and each node applies it a short time later. The metadata barrier does not make this time shorter: a node runs it only when its own check refuses a token, so that it never answers 401 for a token that it has not seen yet.
• A WebSocket connection that logged in with a token stays open after POST /logout of that token. REVOKE SESSIONS closes it.
• A copy of the stored key (it is in the replicated data of the cluster), together with the cluster key (cluster.key and the lock key of its node), can sign a token for any user, root too. Each such token lives at most 24 hours and 10 minutes, but the holder can sign a new token at any time. A copy of the stored key alone signs nothing. The repair: run ALTER CLUSTER ROTATE TOKEN KEY two times to end the tokens of the stolen key. Run it three times to also end every token that was minted between the first two runs. If the thief can also read the data directory, the WAL or the backups, a rotation does not help: keep the data directory and its backups as private as the root password. See run.md for the full list.

The server closes a revoked WebSocket connection. It does not answer an error on it. Write the client to reconnect and to authenticate again. This is the same path the client already takes after a network drop.

Open a new connection for a new session. A revoked connection does not authenticate again.

DROP USER ends the sessions of the user in the same way. A new user of the same name does not get these sessions: CREATE USER makes a new record, and a session of the old record stays ended. A logout of a token of the old record does not change the new record.

DROP USER also removes the user from every role that it is a member of. A new user of the same name starts with no role: grant the roles to it again.

Two statements that change the sessions of one user can run at the same time, for example a REVOKE SESSIONS and a password change. Then one of them applies, and the other gets the error the sessions of user '<u>' changed during the statement; run the statement again. That statement made no change. The error is retryable, so the client runs the statement again. When the statement that did not apply is a change of the own password, and the other statement ended the session of that user, the error is session expired or revoked: log in again. A retry does not help: log in again.

CREATE USER IF NOT EXISTS 'bob' PASSWORD 'secret123';
CREATE USER IF NOT EXISTS 'bob' PASSWORD 'other';
CREATE OR REPLACE USER 'bob' PASSWORD 'rotated';
DROP USER 'bob';

SHOW USERS lists every user with the roles it is a member of (columns user, roles; the roles sorted and comma-separated). It has three more columns: change_own_password, set_own_password and keep_sessions_on_password_change. Each shows the value of the user, ON or OFF, or NULL when the user has no value and the cluster value applies. The column roles_json holds the same roles as a sorted JSON array of strings, for example ["analysts","ops"]. A user with no role has []. Use roles_json when a role name can contain a comma. The column public_key holds the public half of the key pair of the user (see below), or NULL when the user has no key pair. The column home holds the home database of the user (default for a user with no home). The last column, rights_version, is a number. 0 tells that the server did not upgrade the user to the new admin rights yet: the user has no CONTROL on himself yet. 1 tells that the upgrade is done, or that this version created the user.

The key pair of a user. CREATE USER makes a P-256 key pair for a new user. The public half is in the user record: SHOW USERS shows it as the hex of the 65-byte point. The private half is not in the user record. Each node keeps it in a local file (user_keys in its data directory), locked by the cluster key. The file keeps no old copy. CREATE OR REPLACE USER of a user that exists keeps its key pair. DROP USER overwrites the private half on each node. A user that a node with no cluster key created has no key pair.

The key pair in a backup. SHOW CREATE USER <u> (superuser only) ends row 1 with KEY '<hex>': the private half, still locked by the cluster key. It never holds the cluster key. CREATE USER '<u>' PASSWORD HASH '<hash>' … KEY '<hex>' (superuser only) installs that key pair again. The node opens the locked bytes with its own cluster key, so the clause works only in a cluster with the same cluster key. A key that another cluster key locked, or a key of another user, is refused, and the error holds no byte of the key. CREATE OR REPLACE USER … KEY of a user that exists accepts only the key pair that the user holds.

SHOW CLUSTER KEY (superuser only) shows the cluster key pair of the node in one row: version (the key version) and fingerprint (64 lower hex digits, the SHA-256 of the public point). The KEY clause of a user holds the same fingerprint: bytes 5 to 36 of the locked bytes, counted from 0. The statement never shows a byte of the private key. A node with no cluster key pair, or a node in quarantine, gives zero rows. The statement has no ON ALL NODES form.

SHOW ROLES lists every role with its member users and its member roles (columns role, members, member_roles, members_json, principal). principal holds the principal id of the role: role#<16 hex digits>:<name> for a role that this version created, and role_<name> for a role that an older version created. In members and member_roles, each list is sorted and comma-separated. member_roles holds only the roles that are direct members of the role (a role inside a role). members_json holds the member users as a sorted JSON array of strings, for example ["alice","bob"]. An empty list is []. It comes from the same read as members. Each name is a JSON string, so a comma, a quote or a newline in a name stays exact. Use members_json when a user name can contain a comma. Then come the columns built_in and admin_of, the last ones. built_in is true for a role that the server makes itself: global_admins (with its fixed principal id) and each admins_<db> that CREATE DATABASE made. admin_of holds the database of such an admins_<db>, and NULL for every other role. A role with a built-in name and built_in = false (a role that a user made before this release) is a plain role: it gives no admin right. Both statements need root.

SHOW GRANTS lists the permissions of every collection in the current database and of every function. It answers one row per rule, with the columns collection, rights, grant, where and kind. rights holds the permission names, comma-separated. grant holds the target as you write it in GRANT: ANYONE, AUTHENTICATED, ROLE <name> or MATCH <field>. where holds the WHERE filter as JSON text, or NULL for the whole collection. kind is collection, function or create; for a function, collection holds the name of the function. A create row is a CREATE grant (Who may create): collection holds the kind (table) or the database and the kind (shop.table), and the default of the current database has a row too. A rule that names a user shows as USER <name>. A rule on a role has the kind role, and a rule on a user the kind user: collection holds the name of the role or the user. A rule on the users of a home (ON USERS IN DATABASE <db>) has the kind users, and collection holds the database. The rows of kind users come last. SHOW GRANTS ON <collection> lists the rules of one collection. SHOW GRANTS ON FUNCTION <name> lists the rules of one function. A function with no rule gives no rows. A missing function gives the error of DROP FUNCTION. A rule shows as ROLE <name> only when it names the principal id of the live role. A rule for a role that was dropped, or for an older role of the same name, shows as PRINCIPAL <id>. That rule matches no user. SHOW GRANTS lists the collection rules of the last commit, which the SELECT check uses. If the server cannot read that commit, SHOW GRANTS gives an error and no rows. SHOW GRANTS and SHOW GRANTS ON FUNCTION <name> need root. SHOW GRANTS ON <collection> also passes for a user that holds CONTROL on every row of the collection (no MATCH and no WHERE), the user that may GRANT and REVOKE on it. That user sees only the rules of that collection. A missing collection gives such a user the same refusal as a collection with no CONTROL.

The web console shows these rules on its Access page (/ui/#/access). The page lists the rules of one collection, of one role or of one user, and it grants, edits and revokes them with the statements of this section. For a user, it shows each rule that reaches the user and the path of that rule: a role, ANYONE, AUTHENTICATED or MATCH.

A permission goes to a target: ANYONE, AUTHENTICATED, ROLE <name>, or MATCH <field> (a rule that matches the user against a field of the row). A WHERE limits the permission to the matching rows. A user gets permissions through roles. A ROLE <name> target must name a role that exists. GRANT refuses an unknown role. Create the role first.

A MATCH or WHERE permission filters every read. SELECT COUNT(*) with no WHERE answers the number of rows the user can see, on a table and on a document collection.

A write cannot change a protected field: a field that a permission of the collection reads (a MATCH field or a field of a WHERE). Such a change could give the row to another user. Only a superuser and a user with the CONTROL permission on the row can change it. A write permission is not enough, also when it is on the whole collection. GRANT ALL does not give CONTROL. The statement is refused as a whole with permission denied. This applies to UPDATE, to an INSERT over a key that exists (on a table and on a document collection), to PUT and UPSERT over a document, and to TRANSFER. A SET that writes the same value again changes nothing and is allowed.

CONTROL works with MATCH and WHERE as every permission does, so it can apply to some rows only. The write needs its own permission too:

GRANT SELECT, UPDATE ON projects TO MATCH editors;
GRANT SELECT, UPDATE, CONTROL ON projects TO MATCH managers;
REVOKE CONTROL ON projects FROM MATCH managers;

CONTROL on every row of a collection also lets a user change the collection itself. A user with a CONTROL rule that has no MATCH and no WHERE (for example GRANT CONTROL ON projects TO ROLE owners) may run these statements on that collection:

• ALTER TABLE <name> …,
• DROP TABLE <name> and DROP COLLECTION <name>,
• GRANT … ON <name> and REVOKE … ON <name>, CONTROL included,
• ALTER COLLECTION <name> SET POLICY '…'.

A CONTROL on some rows only (a MATCH or a WHERE) does not give these statements, and neither does a write permission on the whole collection. TRUNCATE, CREATE INDEX and every other statement of a schema stay for a superuser. CREATE TABLE needs a CREATE grant (Who may create). The cluster judges the right again when the statement applies, in the order of the log. When a superuser takes the right away while the statement is in flight, the statement fails with permission denied, and nothing changes. Each such statement writes its audit row, with the user as the actor (see Audit trail).

GRANT CONTROL ON projects TO ROLE owners;
-- A member of owners:
GRANT SELECT ON projects TO ROLE readers;
ALTER TABLE projects ADD COLUMN note TEXT;
DROP TABLE projects;

Who may create

A CREATE grant says who may create a table, a function or a ticker, and which rights the creator gets on the new object:

GRANT CREATE TABLE ON DATABASE shop TO ROLE app_users WITH SELECT, INSERT;
REVOKE CREATE TABLE ON DATABASE shop FROM AUTHENTICATED;
GRANT CREATE FUNCTION TO ROLE devs;
GRANT CREATE TICKER TO ROLE devs;
REVOKE CREATE TICKER FROM ROLE devs;

• The holder is ANYONE, AUTHENTICATED or ROLE <name>. Only a superuser gives or takes a CREATE grant. GRANT CREATE ROLE lets the holder create a role (Rights on a role and on a user). GRANT CREATE DATABASE lets the holder create a database (Databases).
• CREATE TABLE covers CREATE COLLECTION too. ON DATABASE <db> limits the grant to one database; with no ON DATABASE the grant holds in every database. A function, a ticker and a role are global, so their grant takes no ON DATABASE.
• The default. Every database has CREATE TABLE ON DATABASE <db> TO AUTHENTICATED, also an old database and a new one. So each logged-in user may create a table. Revoke it to close a database: REVOKE CREATE TABLE ON DATABASE <db> FROM AUTHENTICATED. The revoke changes only that database. CREATE FUNCTION and CREATE TICKER have no default.
• The creator rights of a table. The creator gets one rule on the new table: the WITH set of the grant, or SELECT, INSERT, UPDATE, DELETE, CONTROL with no WITH. With CONTROL the creator may alter, drop and share the table. The rule names the user itself, not its name: a new user with the same name gets no right on the table. SHOW GRANTS shows the rule as USER <name>. When more than one grant matches the creator, the creator gets the rights of all of them. A CREATE TABLE … IF NOT EXISTS of a table that exists changes no right.
• The first write. A write to a collection that does not exist creates it (see INSERT). The write needs CREATE TABLE in the database, with the right of the write in the WITH set. The writer becomes the creator. If another user creates the collection at the same time, the write is judged as a write to the table of that user.
• A database you may create in shows in SHOW DATABASES, also when it has no table that you can read, and USE accepts it. With the default, every logged-in user sees every database. After the revoke of the default, a user that holds no grant and sees no table of the database gets database not found.
• Functions. A holder of CREATE FUNCTION creates a function that runs as the caller (no RUN AS) or as an eligible user on whom the holder has CONTROL (RUN AS '<self>' too, with the stored self rule). See the run-as rule in Who may call a function. The creator owns the function: the owner may drop it, replace it and grant or revoke INVOKE on it. The owner may not give CONTROL: that needs CONTROL on the function. The user of a CREATE OR REPLACE becomes the owner; a replace by a superuser leaves the function with no owner.
• Tickers. A holder of CREATE TICKER creates a ticker that runs as the holder, or as the user of RUN AS (the run-as rule of functions). Only its creator or a superuser drops it or creates it again. A ticker runs only while its creator exists: a new user with the same name does not run it.
• The guest never creates, also with a grant to ANYONE.
• The cluster judges the grant again when the statement applies, in the order of the log. When a superuser revokes the grant while the statement is in flight, the statement fails with permission denied, and nothing is created. DROP DATABASE removes the CREATE grants of the database, so a new database of the same name has the default again.

MATCH also works on an ARRAY(TEXT) column. The permission applies when one element names the user or one role of the user. A user element holds the user name or the user id. A role element holds the principal id of the role, the same rule as a MATCH column with one value: the element 'team' alone names no role. SHOW ROLES shows the principal id of each role in its last column, principal. A role that this version creates has the id role#<16 hex digits>:<name>, for example role#3f9a0c41d2e87b65:team. A role that an older version created keeps the id role_<name>. A WHERE of a permission on an array column is contains: the row matches when one element equals the value.

Each CREATE ROLE gives the role a new principal id. So after DROP ROLE team and CREATE ROLE team, a row that holds the id of the old role grants nothing to the new role. Write the new id into the rows that must grant to the new role.

CREATE TABLE docs (id INT PRIMARY KEY, body TEXT, readers ARRAY(TEXT));
SHOW ROLES;
INSERT INTO docs (id, body, readers) VALUES (1, 'plan', ARRAY['role#3f9a0c41d2e87b65:team', 'alice']);
GRANT SELECT ON docs TO MATCH readers;

A user with the role team (here with the principal id role#3f9a0c41d2e87b65:team) reads row 1. The user alice reads it too. A user that no element names reads no row.

A full sync (WATCH … SINCE START) under one such MATCH permission reads an index on the MATCH column when the table has one: a TEXT column with CREATE INDEX, or an ARRAY(TEXT) column with CREATE INDEX docs_readers ON docs (readers). The sync then reads the rows that name the user or a role of the user, not every row of the table. When those rows are 20 % of the table or more, the sync reads the whole table, because that is faster. Each row that the sync sends still passes the full permission check. In this version, SELECT and COUNT(*) do not use the index for the permission: they check the permission on every row that their WHERE reads.

A user with no permission for a write on the collection (for example, a user with SELECT only) gets permission denied for that write at once, before the server reads a row. The server refuses the write also when no row matches.

A user with the permission on the whole collection (no MATCH, no WHERE) writes with no row check, so the write costs the same as for root. For other users, the write checks each row it changes against their MATCH or WHERE permissions, and one row that fails refuses the whole statement. An UPDATE or a DELETE skips a row that the user may neither read nor change: the row stays as it is, the count does not include it, and the answer is the same as for a row that does not exist. So a write does not tell a user that a row it cannot read exists. A row that the user can read but not change still refuses the statement. An INSERT over a key that exists replaces the stored row, so the write checks the stored row and the new row, as for PUT. An UPSERT then finds the old document through the index on its key, as it does for root.

ANYONE includes the guest, a client that did not log in. The guest gets exactly the rights of GRANT … TO ANYONE. So GRANT SELECT … TO ANYONE makes a collection readable without a login, and GRANT INSERT … TO ANYONE lets the guest add rows. The guest can run SELECT, INSERT, UPSERT, UPDATE, DELETE and the EXPLAIN of each. A statement without an ANYONE grant for its right gets a refusal. An UPSERT needs only INSERT, so a guest with INSERT can also replace a row with the same key: add a WHERE to the grant to limit the rows. AUTHENTICATED, ROLE and MATCH never match the guest, so a collection with only such grants refuses every statement of the guest. The guest can CALL a function with GRANT INVOKE ON FUNCTION … TO ANYONE. The body runs as the guest, so each statement of the body needs its own ANYONE grant, unless the function has RUN AS: then the body runs as that user (a small API with a function). The guest cannot run DDL, GRANT, a transaction, TRUNCATE, TRANSFER, a key or file statement, AS OF, WATCH or SHOW, also not in a function body. cluster.auth.allow_guest = false turns the guest off.

CREATE ROLE analysts;
GRANT ROLE analysts TO 'alice';
GRANT SELECT ON documents TO ROLE analysts;
GRANT SELECT ON documents TO ANYONE WHERE _type = 'public';
GRANT ALL ON orders TO ROLE analysts;
SHOW ROLES;
SHOW GRANTS;
REVOKE SELECT ON documents FROM ROLE analysts;
REVOKE ALL ON orders FROM ROLE analysts;
REVOKE ROLE analysts FROM 'alice';
DROP ROLE analysts;

DROP ROLE refuses a role while a rule still names it: a rule on a table, a collection or a function (GRANT INVOKE). The error names each table and each function, for example role 'analysts' cannot be dropped: rules still name it: table orders; revoke them first (B2929). A table of another database shows as database.table, and a branch table adds on branch <b>. Revoke each rule, then drop the role. Else a new role with the same name would get the rules of the old role. Every node judges the drop in log order. So a GRANT that the cluster orders after the DROP ROLE is refused with role 'analysts' not found. A GRANT ROLE analysts TO 'alice' or a REVOKE ROLE analysts FROM 'alice' in that place gets the same error. A GRANT ROLE or a REVOKE ROLE that applied answers success, also when a later DROP USER, DROP ROLE or GRANT ROLE changed the membership first (B2979).

CREATE ROLE refuses a name that exists. The error is role 'analysts' already exists; use CREATE ROLE IF NOT EXISTS 'analysts' to accept an existing role. CREATE ROLE IF NOT EXISTS is the form for a script that runs again and again:

Statement	The name does not exist	The name exists
CREATE ROLE analysts	creates the role	an error; nothing changes
CREATE ROLE IF NOT EXISTS analysts	creates the role	success; nothing changes

Each CREATE ROLE gives the role a new principal id (the column principal of SHOW ROLES). A rule always names that id. So a role that you drop and create again never gets the rules of the old role: not on a live table, not in an AS OF read, not on a branch and not through a MATCH row.

CREATE ROLE <name> WITH PRINCIPAL '<id>' creates the role with a principal id that you give, for example the id of a dump. kd-tool restore uses it. It needs root. The id must name the same role: role#<16 hex digits>:<name> or role_<name>. It refuses a name that exists, as CREATE ROLE does.

Neither form changes the members or the grants of a role that exists. When two clients create the same role at the same time, the role is created once. The strict form of the second client gets the error, and IF NOT EXISTS gets success.

CREATE ROLE IF NOT EXISTS analysts;
CREATE ROLE IF NOT EXISTS analysts;
DROP ROLE analysts;

Rights on a role and on a user

A role has two rights: CONTROL and SELECT. A user has the right SELECT. A rule on the role or the user gives a right to a holder:

CREATE ROLE team;
CREATE ROLE team_admins;
GRANT CONTROL, SELECT ON ROLE team TO ROLE team_admins;
GRANT SELECT ON USER 'alice' TO ROLE team_admins;
GRANT CREATE ROLE TO ROLE team_admins;
SHOW GRANTS;
REVOKE SELECT ON USER 'alice' FROM ROLE team_admins;
REVOKE CONTROL, SELECT ON ROLE team FROM ROLE team_admins;
REVOKE CREATE ROLE FROM ROLE team_admins;
DROP ROLE team;
DROP ROLE team_admins;

• The holder is ANYONE, AUTHENTICATED or ROLE <name>. ON ROLE team TO ROLE x is the role form. ON role TO … with no name after role names the table role.
• CONTROL on a role lets the holder add a member to the role and remove a member, drop the role, and give or take CONTROL or SELECT on the role. A superuser or a holder of CONTROL on the role gives these rules.
• SELECT on a role lets the holder see the role in SHOW ROLES and add the role as a member of another role. CONTROL includes SELECT.
• SELECT on a user lets the holder see the user in SHOW USERS and add the user to a role. A user also takes CONTROL, SET PASSWORD and RESET PASSWORD (the password of another user). A member of global_admins, or a member of admins_<home> for a user of that home, gives or takes a rule on a user. A rule on a user with rights_version 0 in SHOW USERS is refused with the upgrade of user <u> is not applied yet; run the statement again. The server upgrades such a user by itself; run the statement again after the upgrade.
• Membership is SELECT. A member of a role (directly or through a role inside a role) counts as SELECT on that role. A user counts as SELECT on himself.
• To add a member. GRANT ROLE r TO <user> and GRANT ROLE r TO ROLE m need CONTROL on r and SELECT on the member. REVOKE ROLE r FROM … needs CONTROL on r only.
• Rights on a role do not nest. CONTROL on p gives no right on a role c that is a member of p. Membership nests: the members of c get the rights of p.
• CREATE ROLE. A holder of GRANT CREATE ROLE creates a role. The creator gets the WITH set of the grant on the new role, or CONTROL, SELECT with no WITH. The rule names the user itself: a new user with the same name gets no right on the role. SHOW GRANTS shows it as a row of kind role with the holder USER <name>.
• GRANT AUDIT, GRANT QUERY AS and CREATE ROLE … WITH PRINCIPAL stay superuser only. CONTROL on a role does not allow them.
• A role that you cannot see is a missing role. For a user that is not a superuser, each statement that names a role that the user cannot see answers role '<name>' not found, the same answer as for a role that does not exist: GRANT ROLE, REVOKE ROLE, DROP ROLE, GRANT … ON ROLE and GRANT … TO ROLE <name> on a table, a function or a role. A user that the caller cannot see answers user '<name>' not found.
• DROP ROLE needs CONTROL on the role. It also removes, in the same change, each rule that names the role on another role or a user, and each CREATE grant to the role. The rules on tables, collections and functions still refuse the drop (see above).
• The cluster judges each of these statements again when it applies, in the order of the log. When a superuser takes the right away while the statement is in flight, the statement fails, and nothing changes.
• SHOW GRANTS shows a rule on a role as a row of kind role, and a rule on a user as a row of kind user. The column collection holds the name of the role or the user.

SHOW ROLES and SHOW USERS show a superuser every row. Another user sees:

Statement	The rows
SHOW ROLES	each role that the user is a member of (directly or through a role inside a role), and each role that a rule gives the user SELECT or CONTROL on; a role that shows lists all its members
SHOW USERS	the user, and each user that a rule gives the user SELECT on; the roles and roles_json cells list only the roles that the user sees, and public_key is NULL except on the row of the user

The guest runs neither statement. No answer holds a password or a secret.

A role inside a role

A role can be a member of another role. GRANT ROLE <a> TO ROLE <b> makes the role b a member of the role a. Every member of b then gets the rights of a, also the members of a role inside b. REVOKE ROLE <a> FROM ROLE <b> removes the role b from a. The rights flow from a down to b, never from b up to a.

CREATE ROLE staff;
CREATE ROLE analysts;
CREATE ROLE interns;
GRANT SELECT ON documents TO ROLE staff;
GRANT ROLE staff TO ROLE analysts;
GRANT ROLE analysts TO ROLE interns;
GRANT ROLE interns TO 'alice';
SHOW ROLES;
REVOKE ROLE analysts FROM ROLE interns;
DROP ROLE interns;
DROP ROLE analysts;
REVOKE SELECT ON documents FROM ROLE staff;
DROP ROLE staff;

The user alice is a member of interns, interns is a member of analysts, and analysts is a member of staff. Thus alice reads documents until the REVOKE.

The rules:

• The two statements need CONTROL on the role a, and the grant also needs SELECT on the role b (Rights on a role and on a user). A superuser holds both. Both roles must exist.
• The word ROLE after TO or FROM starts the role form. GRANT ROLE a TO 'b' and GRANT ROLE a TO b name the user b. To name a user that has the name role, write the name in quotes: GRANT ROLE a TO 'role'. GRANT ROLE a TO role with no word after role also names the user role.
• A cycle is refused: a role cannot be a member of itself, and GRANT ROLE a TO ROLE b is refused when a is already a member of b, directly or through other roles. The error names the chain, for example GRANT ROLE a TO ROLE b makes a cycle: a is already a member of b through the chain a -> c -> b.
• The depth of a chain is the number of roles that a user passes through. For alice -> interns -> analysts -> staff the depth is 3. The setting cluster.auth.max_role_depth limits the depth (default 3, minimum 1, maximum 64). A grant that makes a longer chain is refused, and the error names the chain and the limit. A user that is a member of a role does not count.
• SET CLUSTER cluster.auth.max_role_depth = <n> changes the limit. A value below the longest chain that exists is refused, and the error lists the chains that are longer. Revoke a role from a role on each such chain first. A value above 64 is refused. The statement works on a single server and on a cluster. The audit trail records it with the action set_role_depth_limit.
• After the first SET CLUSTER cluster.auth.max_role_depth, the cluster keeps its stored limit across versions: a later server version with a different default does not change it.
• SHOW ROLES shows the direct member roles of each role in the column member_roles. The column members_json lists the member users only, never the member roles.
• DROP ROLE b also removes b from every role that it is a member of. A new role with the name b starts with no parent role.
• The AUDIT privilege and the QUERY AS privilege also pass through a role inside a role. After GRANT QUERY AS TO ROLE gateways and GRANT ROLE gateways TO ROLE b, every member of b can send the frame QueryAs.
• The audit trail records each statement with the action grant_role_to_role or revoke_role_from_role. The object is the role, and the grantee is the member role (ROLE <member_role>). The target is NULL, so SHOW AUDIT does not show the row to a user with the name of the member role.

SET CLUSTER cluster.auth.max_role_depth = 4;
SET CLUSTER cluster.auth.max_role_depth = 3;

When a role change reaches an open connection

A change of the roles of a user reaches the connections that user already holds open. The client does not reconnect, and it does not log in again. Once the statement answers, the next statement on an open connection uses the new roles.

The statement	What an open connection does next
GRANT ROLE <role> TO <user>	The user reads and writes what the role gives.
REVOKE ROLE <role> FROM <user>	The server refuses what only that role gave.
GRANT ROLE <role> TO ROLE <member_role>	Every member of the member role reads and writes what the role gives.
REVOKE ROLE <role> FROM ROLE <member_role>	The server refuses what only that role gave to the members of the member role.
DROP ROLE <role>	Every member of the role loses what the role gave through its parent roles. A rule that names the role directly blocks the drop.

A WATCH follows the same rule. After REVOKE ROLE, the server sends the connection no more notifications for a collection that only the lost role permitted. The client does nothing for this: the server applies the change when it delivers the next notification.

A GRANT ROLE adds the new rights to the open connection and to its WATCH subscriptions. The server keeps the connection open for a role change.

This holds on every node of the cluster. The node that answers the statement is not important.

ALTER COLLECTION <name> SET POLICY '<json array>' replaces the whole policy list of a collection:

ALTER COLLECTION documents SET POLICY '[]';

Credentials: public keys and API secrets

A credential is a public key or an API secret that kinedb keeps on a user. A user can hold many credentials. A client can log in with an API secret or with the private key of a public key, in place of the password. See Authentication.

Each credential has these fields:

Field	What it holds
type	public_key or api_secret.
id	The identifier of the credential. A public key: the SHA-256 fingerprint of the key, as 64 hex characters. An API secret: 32 hex characters that do not show the secret or its hash.
name	An optional name, unique inside one user.
created_at	The time the cluster added the credential (RFC 3339, UTC).
expire_at	An optional time (RFC 3339, UTC) after which the credential is not valid.

kinedb stores expire_at and lists it. After that time, SHOW CREDENTIALS lists the credential as expired, and a login with it is refused. The credential stays on the user until you drop it. EXPIRE AT must be after the time that the cluster adds the credential. An earlier time, or the same time, gets the error invalid EXPIRE AT: … is not after the creation time ….

CREATE USER IF NOT EXISTS 'carol' PASSWORD 'secret123';
ALTER USER 'carol' ADD PUBLIC KEY '04bd5ef0939d54e1af3ac5f7f48a35d41ff4ff8b355ce317c4fe53e7e1e130ddebad261e758073dc174262a2142c81f37737c4733b5a48e1d728e9333caf9b323b' NAME 'laptop' EXPIRE AT '2027-01-01T00:00:00Z';
ALTER USER 'carol' CREATE API SECRET NAME 'ci';
SHOW CREDENTIALS FOR 'carol';
ALTER USER 'carol' DROP CREDENTIAL NAME 'ci';
ALTER USER 'carol' DROP CREDENTIAL '783f15c2a92ea2c09f1ca2c8b2f40e4447c3c867109e0f0a3c76c3b2ea7ddd16';
DROP USER 'carol';

The statements:

Statement	What it does
ALTER USER <u> ADD PUBLIC KEY '<key>' [NAME '<n>'] [EXPIRE AT '<t>']	Adds a P-256 public key.
ALTER USER <u> CREATE API SECRET [NAME '<n>'] [EXPIRE AT '<t>']	Makes a new API secret and replies it one time.
ALTER USER <u> ADD API SECRET HASH '<64 hex>' [NAME '<n>'] [EXPIRE AT '<t>']	Adds an API secret from its SHA-256 hash (the restore form).
ALTER USER <u> DROP CREDENTIAL '<id>'	Drops the credential with this id.
ALTER USER <u> DROP CREDENTIAL NAME '<n>'	Drops the credential with this name.
SHOW CREDENTIALS FOR <u>	Lists the credentials of the user.

NAME and EXPIRE AT are optional. When you give both, write NAME first. <t> is an RFC 3339 timestamp, for example '2027-01-01T00:00:00Z'. Another text gets the error invalid EXPIRE AT: ….

A public key. ADD PUBLIC KEY accepts a P-256 (ECDSA, secp256r1) public key in one of these forms:

• PEM of the SubjectPublicKeyInfo, the text from -----BEGIN PUBLIC KEY----- to -----END PUBLIC KEY-----.
• Base64 of the SubjectPublicKeyInfo DER.
• Base64 of the raw 65-byte point (04 followed by X and Y).
• Hex of the raw 65-byte point, as in the example above.

kinedb checks that the point is on the curve. It refuses another text with an error that starts with invalid public key:. The id of the key is the SHA-256 fingerprint of the 65-byte point. So the same key gets the same id in each form, and a user cannot hold one key two times. These commands make a key pair and print the public key as PEM:

openssl ecparam -name prime256v1 -genkey -noout -out carol.key.pem
openssl ec -in carol.key.pem -pubout

Keep carol.key.pem with the client. kinedb never receives a private key, and no statement accepts one.

An API secret. CREATE API SECRET replies one row with the columns id and secret. The secret is the text kdsk_ followed by 43 base64url characters (32 random bytes):

id	secret
3f0c9a… (32 hex)	kdsk_…

The reply holds the secret one time only. Copy it at once. kinedb stores only the SHA-256 hash of the secret. No statement, log line, audit row or error shows the secret again. A lost secret cannot be found again: drop it and create a new one.

The node that receives CREATE API SECRET makes the secret. Only the hash goes to the other nodes, so the secret never crosses the link between the nodes.

ADD API SECRET HASH is the restore form, as PASSWORD HASH is for a password. It adds an API secret from the 64 hex characters of its SHA-256 hash, so a restore keeps a secret that clients already use. kinedb hides the hash from SHOW STATEMENTS and from the slow-query list (see the redaction rule under SHOW STATEMENTS below).

CREATE USER IF NOT EXISTS 'carol' PASSWORD 'secret123';
ALTER USER 'carol' ADD API SECRET HASH 'e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855' NAME 'restored';
DROP USER 'carol';

The credentials in SHOW CREATE USER. SHOW CREATE USER <u> (superuser only) replies one row per statement, in the column statement. The first row creates the user: CREATE USER '<u>' PASSWORD HASH '<hash>' [WITH …]. Then each credential of the user has one row, in the order of SHOW CREDENTIALS:

ALTER USER '<u>' ADD PUBLIC KEY '<hex of the point>' [NAME '<n>'] [EXPIRE AT '<t>']
ALTER USER '<u>' ADD API SECRET HASH '<64 hex>' [NAME '<n>'] [EXPIRE AT '<t>']

Run the rows in this order on another server to make the same user with the same credentials: the same id, name and expire_at. The row of an API secret holds its hash, never the secret, because kinedb stores only the hash. The server that runs the rows sets a new created_at. A credential that is expired on the clock of the server has no row, because kinedb refuses to add an expired credential.

The list. SHOW CREDENTIALS FOR <u> replies one row per credential, with the columns type, id, name, created_at, expire_at, expired, provider and subject. name and expire_at are NULL when the statement gave none. expired is true when expire_at is at or before the time of the node that answers. provider and subject are set for a federation link only (below), and NULL for a key and a secret. The list never shows a key, a secret or a hash.

A federation link. A federation link connects a user to an account at an identity provider (Identity providers). It is a credential of the type federation. It holds the provider and the subject: the stable id of the account at the provider, from the claim that the provider option SUBJECT CLAIM names (sub or oid). No login uses a link in this release.

ALTER USER 'carol' ADD FEDERATION PROVIDER corp SUBJECT '00u1a2b3c4d5e6f7g8h9' NAME 'work';
ALTER USER 'carol' DROP FEDERATION PROVIDER corp SUBJECT '00u1a2b3c4d5e6f7g8h9';

Statement	What it does
ALTER USER <u> ADD FEDERATION PROVIDER <p> SUBJECT '<s>' [NAME '<n>']	Adds a link to the account <s> at the provider <p>.
ALTER USER <u> DROP FEDERATION PROVIDER <p> SUBJECT '<s>'	Drops that link. DROP CREDENTIAL '<id>' drops it too.

• The id of a link is 32 hex characters that do not show the subject. The answer of an add or a drop names the id, never the subject.
• A subject is 1 to 255 visible ASCII characters. A subject with the shape of an email address is refused: a subject is the stable id of the account at the identity provider; it is never an email address. A stable subject that also has that shape cannot be linked.
• The provider must exist. A disabled provider takes a link.
• One pair of a provider and a subject belongs to ONE user. A second user gets the error provider <p>: this subject is linked to user '<u>'.
• A user holds at most 64 links, the number of providers that can exist. A 65th link gets the error user '<u>' has 64 federation links, the limit; drop a link first. Keys and API secrets do not count.
• A link has no EXPIRE AT: the identity provider owns the life of the account.
• SHOW CREATE USER writes a link as the row ALTER USER '<u>' ADD FEDERATION PROVIDER '<p>' SUBJECT '<s>' [NAME '<n>'].
• DROP USER removes the links of the user. DROP PROVIDER refuses a provider that has links.

A drop. DROP CREDENTIAL finds the credential by its id or, with NAME, by its name. A missing credential gets the error user 'carol' has no credential with id '…' or user 'carol' has no credential named '…'. A drop moves the same connection fence as REVOKE ROLE (when a role change reaches an open connection): each open connection checks its user again. A connection that logged in with the dropped API secret closes, and a token that POST /login gave for that secret is refused. A connection that logged in with a password, or with another secret, stays open.

A login with an API secret. A client logs in with the user name and the secret in place of the password: POST /login takes {"username": "carol", "api_secret": "kdsk_…"} and gives the same token as a password login. Each client library has a login call for a secret (in Rust, WsClient::authenticate_with_api_secret; in kd-tool, --api-secret-env <VAR> or --api-secret-file <path>). A wrong, unknown, dropped or expired secret, or the secret of another user, gets the one error invalid username or password. A secret with an EXPIRE AT that has passed cannot log in, and the token of a secret login ends at the latest at its EXPIRE AT.

A login with a key pair, and the end of a live session. These are on the page Authentication: the challenge, the signed bytes, the client calls and the end of a session at the EXPIRE AT of its credential.

Rules.

• Each statement of this section needs root. The guest cannot run them. A transaction refuses them.
• ADD PUBLIC KEY, CREATE API SECRET and ADD API SECRET HASH refuse a user that does not exist (user 'carol' not found).
• A second credential with the same id or the same name on one user gets an error, for example user 'carol' already has a credential named 'ci'.
• CREATE OR REPLACE USER keeps ALL the credentials of the user, as it keeps its roles. It replaces only the password. To remove the credentials, run SHOW CREDENTIALS FOR <u>, then run ALTER USER <u> DROP CREDENTIAL '<id>' for each id in the list. DROP USER removes the credentials with the user.
• A statement can go to any node. Every node lists the same credentials.

The web console shows the credentials of a user on its Users & roles page (/ui/#/users). Open the row of a user to see its Credentials panel. The panel lists the credentials with a Drop button on each row. The panel also lists the federation links of the user. A superuser adds a link with a form, and drops a link after a confirmation. Each form below has an optional name and an optional expiry time:

• Generate key pair makes a P-256 key pair in the browser. The browser sends only the public key (ADD PUBLIC KEY). The panel then offers the private key one time, as the download <user>-<name>.pk8.pem (PKCS #8 PEM). The private key never goes to the server.
• The browser makes a key pair only on HTTPS or on localhost. On another address the button is disabled, with the text "Key pair generation needs HTTPS or localhost; add a public key made elsewhere".
• Add public key adds a key that you paste, in one of the forms above.
• Create API secret shows the new secret one time, with a Copy button. After you close it, the console cannot show it again.

Identity providers (OpenID Connect)

An identity provider is an OpenID Connect server (for example Microsoft Entra ID, Google or Keycloak) that can sign in the users of kinedb. kinedb keeps a list of providers. Each node of a cluster has the same list. Only the superuser can run the statements of this section.

In this release, kinedb stores the providers and shows them, and it keeps the keys of each DISCOVERY provider current. The login through a provider uses them: see Log in with an identity provider.

CREATE PROVIDER corp MANUAL
  ISSUER 'https://login.example.com'
  AUTHORIZATION ENDPOINT 'https://login.example.com/authorize'
  TOKEN ENDPOINT 'https://login.example.com/token'
  KEYS ENDPOINT 'https://login.example.com/jwks'
  KEYS '{"keys":[{"kid":"key-1","kty":"EC","crv":"P-256","x":"…","y":"…"}]}'
  CLIENT ID 'kinedb'
  CLIENT SECRET 'the-secret-of-the-provider'
  REDIRECT URIS ('https://db.example.com/auth/callback')
  DISPLAY NAME 'Corp SSO';
SHOW PROVIDERS;
SHOW CREATE PROVIDER corp;
ALTER PROVIDER corp LOGIN PAGE ON HOSTNAMES ('db.example.com');
DROP PROVIDER corp;

The name. A provider name is 1 to 63 characters of A-Z, a-z, 0-9 and _. It does not start with a digit. The name is case-sensitive. You can write it bare or as a string literal.

CREATE PROVIDER <name> MANUAL …. The statement gives the endpoints and the keys of the provider. kinedb does not connect to the provider. These options are necessary:

Option	What it holds
ISSUER '<url>'	The issuer of the provider (the iss of its tokens).
AUTHORIZATION ENDPOINT '<url>'	Where the browser signs in.
TOKEN ENDPOINT '<url>'	Where kinedb gets the tokens.
KEYS ENDPOINT '<url>'	The jwks_uri of the provider.
KEYS '<JWKS JSON>'	The public keys of the provider: a JSON Web Key Set with one or more keys, each with a kid. At most 256 KiB.
CLIENT ID '<id>'	The client id of kinedb at the provider.

These options are optional. They come after MANUAL in any order. Each option can occur one time:

Option	Default	What it holds
END SESSION ENDPOINT '<url>'	none	Where the browser signs out of the provider.
REVOCATION ENDPOINT '<url>'	none	Where kinedb revokes a token.
TOKEN AUTH METHOD '<method>'	'client_secret_basic'	'client_secret_basic' or 'client_secret_post': how kinedb sends the client secret.
ALGORITHMS ('<alg>', …)	('RS256')	The signature algorithms of the ID tokens: RS256, PS256, ES256.
CLIENT SECRET '<secret>'	none	The client secret, 1 to 4096 bytes. With no client secret, kinedb is a public client (PKCE).
REDIRECT URIS ('<uri>', …)	empty	The callback addresses of kinedb.
HOSTNAMES ('<host>', …)	empty	The host names that use this provider.
DISPLAY NAME '<text>'	the name	The text of the sign-in button, at most 128 bytes.
ENABLED or DISABLED	ENABLED
LOGIN PAGE { ON | OFF }	OFF	Show the provider on the login page.
ROLES CLAIM '<claim>'	none	The token claim that holds the roles of a user.
UPDATE ROLES { ON | OFF }	OFF
REMOVE ROLES { ON | OFF }	OFF
MAP ROLES BY NAME { ON | OFF }	OFF
AUTO CREATE USERS { ON | OFF }	OFF	With ON, the first login of an account with no user makes the user, named by the email of the account in lower case, with no password and with the link of the account. Every account of the provider that signs in gets a user, so the provider must validate the emails. The cluster setting cluster.auth.auto_create_users_per_hour limits the users of one provider in one hour. See Login with a provider.
SUBJECT CLAIM { 'sub' | 'oid' }	'sub'	The token claim that names the account at the provider. sub is the stable id of OpenID Connect. oid is the object id of the user in an Entra ID tenant. No other value is accepted: an email address can go to another account later.

Each endpoint and the issuer must be an https:// URL. A redirect URI is an https:// URL, or an http:// URL with the host localhost, 127.0.0.1 or [::1]. A list has at most 32 items. A URL, a list item and the client id have at most 2048 bytes. A second copy of one option, or a value that breaks a rule, gets an error and stores nothing.

CREATE PROVIDER <name> DISCOVERY '<url>' …. kinedb reads the endpoints and the keys from the provider. <url> is the issuer URL (https://login.example.com) or the full discovery URL (https://login.example.com/.well-known/openid-configuration). The node that receives the statement gets the discovery document and then the key set (jwks_uri) one time. It takes the issuer, the endpoints, the token auth method and the ID-token algorithms from the document. The other nodes do not connect to the provider. CLIENT ID is necessary. The other options are the options of the table above, except the endpoints, KEYS, TOKEN AUTH METHOD and ALGORITHMS: the document gives them, and the statement refuses them.

CREATE PROVIDER corp DISCOVERY 'https://login.example.com' CLIENT ID 'kinedb'
  CLIENT SECRET 'the-secret-of-the-provider' LOGIN PAGE ON;

The fetch rules. A fetch that breaks a rule gets the error the discovery fetch of provider <name> failed: <reason>, and kinedb stores nothing. The reason names the host and the rule. It never holds text of the answer of the provider.

• Only https://. The certificate must be valid for the host of the URL (the Mozilla root certificates of webpki-roots).
• The URL holds no control byte, no space and no non-ASCII byte (bytes 0x00-0x20, 0x7f and above). Percent-encode such a byte.
• Only public addresses. kinedb resolves the host one time, checks EVERY address of the DNS answer, and connects only to a checked address. One address that is not public refuses the fetch: 0.0.0.0/8, 10.0.0.0/8, 100.64.0.0/10, 127.0.0.0/8, 169.254.0.0/16, 172.16.0.0/12, 192.0.0.0/24, 192.0.2.0/24, 192.168.0.0/16, 198.18.0.0/15, 198.51.100.0/24, 203.0.113.0/24, 224.0.0.0/4, 240.0.0.0/4; and for IPv6 ::/96 (with ::, ::1 and every IPv4-compatible address), fc00::/7, fe80::/10, ff00::/8, 2001:db8::/32 and 100::/64. An IPv4 address inside an IPv6 address (::ffff:0:0/96, 64:ff9b::/96, 2002::/16) gets the IPv4 rule. A provider behind such an address must use MANUAL.
• No redirect: any HTTP 3xx answer refuses the fetch. Each other status than 200 refuses it too.
• At most 262144 bytes (256 KiB) for each answer, the head and the body together.
• A connect has at most 5 seconds, and each request has at most 10 seconds in total. So one CREATE PROVIDER … DISCOVERY can take up to 20 seconds. The name lookup (DNS) is not in the 10 seconds.
• The document must name the issuer of the URL (OpenID Connect Discovery § 4.3), each endpoint must be https://, and the document must offer RS256, PS256 or ES256.

Each fetch writes one row of the audit trail on the node that took the statement, with the action provider_fetch and the result ok or refused (fetch_failed). The row names the provider. It holds no URL, no host and no error text.

SHOW PROVIDERS shows discovery in kind, the URL of the fetch in discovery_url and the time of the fetch in fetched_at. SHOW CREATE PROVIDER gives the DISCOVERY form; a run of it fetches again. A change of ALTER PROVIDER does not fetch; ALTER PROVIDER <name> REFRESH KEYS does (see the key refresh below).

The refresh interval. A DISCOVERY provider has a refresh interval for its keys. The default is 24 hours. Set another interval with REFRESH INTERVAL <duration> after the discovery URL, or change it with ALTER PROVIDER <name> REFRESH INTERVAL <duration>. ALTER PROVIDER <name> REFRESH INTERVAL DEFAULT sets the default again.

CREATE PROVIDER corp DISCOVERY 'https://login.example.com' REFRESH INTERVAL 6h
  CLIENT ID 'kinedb';
ALTER PROVIDER corp REFRESH INTERVAL 90m;
ALTER PROVIDER corp REFRESH INTERVAL DEFAULT;

• The duration has the grammar of CREATE TICKER … EVERY: a number with the unit ms, s, m or h, for example 24h or '90m'. A number with no unit is milliseconds, so give a unit.
• The interval is 5 minutes (5m) to 30 days (720h), in whole seconds. There is no d unit: write 30 days as 720h. Another value gets the error REFRESH INTERVAL is 5m to 720h (30 days); give a unit, for example 24h.
• A MANUAL provider has no refresh interval. CREATE PROVIDER … MANUAL refuses the clause, and ALTER PROVIDER refuses it for a MANUAL provider.
• SHOW PROVIDERS shows the interval in refresh_interval (for example 24h or 90m). SHOW CREATE PROVIDER shows the REFRESH INTERVAL clause when the interval is not the default.
• The columns last_failure_at and last_failure_reason of SHOW PROVIDERS show the last recorded failure of a refresh of the keys (see below). A MANUAL provider shows no value in the three columns.

The key refresh. kinedb keeps the keys of a DISCOVERY provider current:

• Only the metadata leader fetches. It checks the providers once a minute. When the keys of an enabled provider are as old as its interval, the leader gets the discovery document and then the key set from the jwks_uri of that document, with the fetch rules above. One refresh of one provider runs at a time.
• The new key list REPLACES the old list on every node: a removed key goes, a kept key stays, a new key comes. fetched_at shows the time of the last good refresh.
• The same keys in another order or with other white space are the same list: only fetched_at moves.
• A failure never changes the keys: the old keys stay. An empty key set is a failure. After a failure, the leader tries again each 5 minutes. A changed endpoint other than the keys URL does not change the provider: run DROP PROVIDER and CREATE PROVIDER to take it.
• A failure shows on every node in last_failure_at and last_failure_reason. A later good refresh keeps the last failure: a failure older than fetched_at is resolved. A repeat of the same reason is recorded at most once per hour. The reasons are address_refused, network, timeout, redirect, status (an HTTP status other than 200), too_large, bad_document, wrong_issuer (the document names another issuer than the stored issuer), bad_keys and empty_keys.
• A disabled provider is not refreshed in the background. A MANUAL provider is never refreshed.
• While the provider cannot be reached, the old keys stay in use. A key that the provider removed stays valid in kinedb until a refresh succeeds.

ALTER PROVIDER <name> REFRESH KEYS runs one refresh now and answers its result: provider <name>: keys changed, keys unchanged, refresh failed: <reason> or refresh skipped: the provider changed while the refresh ran (run it again). It takes no other change. On a follower, the statement goes to the metadata leader. A MANUAL provider refuses it.

ALTER PROVIDER corp REFRESH KEYS;

A changed key list writes one row of the audit trail with the action provider_keys_changed on every node; a recorded failure writes one row with the action provider_refresh_failed. An unchanged list writes no row. The row names the provider. It holds no key and no URL. Each failed fetch also writes the provider_fetch row with the result refused on the leader.

A cluster has at most 64 providers. A CREATE PROVIDER past that limit gets the error at most 64 providers can exist; drop a provider first. A dropped provider does not count, but kinedb keeps one small row for each name that was dropped. A name that the limit refused also keeps one small row, so that a restart replays the same refusal.

The client secret. kinedb never stores, logs or shows the client secret as you wrote it:

• The node that receives the statement seals the secret with AES-256-GCM before it sends the statement to another node. The key of the seal comes from the cluster key pair (The cluster key) and a random value. So the network between the nodes, the replicated log and the store hold only the sealed bytes.
• A node with no cluster key yet refuses CLIENT SECRET. Use a public client there.
• SHOW PROVIDERS shows set or none for the client secret. SHOW CREATE PROVIDER has no CLIENT SECRET clause; when the provider has a client secret, a second row says so.
• SHOW STATEMENTS, the slow-query list and the history of the web console show a CREATE PROVIDER or ALTER PROVIDER text as a fixed label, also when the text has no secret.
• A client secret that an older version sealed under the cluster secret does not open. A use of it answers the client secret of provider <name> was sealed by an older version under the cluster secret, and such a value refuses the start of the node (exit code 3, The cluster key). Set the client secret again with ALTER PROVIDER <name> CLIENT SECRET '…'. The other fields of a provider do not change.
• kinedb cannot rotate the key of the stored client secrets yet: B3821 (#6069).

ALTER PROVIDER <name> <change> …. The changes are DISPLAY NAME '<text>', ENABLED, DISABLED, LOGIN PAGE { ON | OFF }, CLIENT ID '<id>', CLIENT SECRET '<secret>', DROP CLIENT SECRET, REDIRECT URIS (…), HOSTNAMES (…), ROLES CLAIM '<claim>', DROP ROLES CLAIM, SUBJECT CLAIM { 'sub' | 'oid' }, the four role switches and AUTO CREATE USERS with ON or OFF, and REFRESH INTERVAL { <duration> | DEFAULT } (a DISCOVERY provider only). A list change replaces the whole list; () makes the list empty. ALTER PROVIDER does not change an endpoint, the issuer, the keys or the kind. To change them, run DROP PROVIDER and CREATE PROVIDER.

DROP PROVIDER with federation links. A provider that has one or more federation links cannot be dropped: the error is provider <name> has federation links; drop them first (SHOW CREDENTIALS FOR <user> lists the links of a user). Drop the links with ALTER USER … DROP FEDERATION PROVIDER …, then drop the provider. So a link never names a provider that does not exist, and a provider of the same name with another issuer cannot take the links of the old one.

CLIENT ID and SUBJECT CLAIM with federation links. On a provider that has links, ALTER PROVIDER refuses a new CLIENT ID or a new SUBJECT CLAIM: another client id or another claim can make a subject name another account. The error is provider <name> has federation links; a new CLIENT ID or SUBJECT CLAIM can make a subject name another account. Drop the links first, or end the statement with KEEP LINKS. End the statement with KEEP LINKS to confirm; the links stay. KEEP LINKS comes last, and only with a change of CLIENT ID or SUBJECT CLAIM.

ALTER PROVIDER corp CLIENT ID 'kinedb-2' KEEP LINKS;

A disabled provider. ALTER PROVIDER <name> DISABLED keeps the links of the provider, but no session of a link of that provider is live while it is disabled. ENABLED makes a new login with a link live again. A connection that the disable closed stays closed.

DROP PROVIDER [IF EXISTS] <name>. It removes the provider. With IF EXISTS, a missing name answers provider <name> does not exist; nothing changed.

SHOW PROVIDERS. One row for each provider, in the order of the names, with the columns name, kind, display_name, enabled, login_page, issuer, discovery_url, authorization_endpoint, token_endpoint, jwks_uri, end_session_endpoint, revocation_endpoint, token_auth_method, algorithms, key_ids, client_id, client_secret, redirect_uris, hostnames, roles_claim, update_roles, remove_roles, map_roles_by_name, auto_create_users, fetched_at, refresh_interval, last_failure_at, last_failure_reason and subject_claim (sub or oid). key_ids lists the kid of each key, not the keys. token_auth_method is none for a provider with no client secret.

SHOW CREATE PROVIDER <name>. One row with the column create_statement: the CREATE PROVIDER statement that makes the provider again, with no client secret.

SHOW CREATE PROVIDER <name> STORED. The restore form, for a backup. One row with two columns. create_statement holds CREATE PROVIDER '<name>' STORED '<text>': the stored definition of the provider with its id, its keys and the time of its fetch, and with no client secret. client_secret is set or none: the secret that the provider has. A run of the statement makes the same provider again with no network request, also on a cluster with another cluster key. The new provider has no client secret: set it with ALTER PROVIDER <name> CLIENT SECRET '…'. kd-tool backup uses this form (Backup).

The stored form. The receiving node runs CREATE PROVIDER <name> STORED '<text>' and ALTER PROVIDER <name> STORED '<text>', where the text holds the sealed client secret. A superuser can run these forms too; the same checks apply. A stored form opens only on a cluster with the same cluster key.

Each statement writes one row of the audit trail (create_provider, alter_provider, drop_provider) that names the provider. The row holds no client id, no URL and no secret.

The web console shows the providers on its Identity providers page (/ui/#/providers). Only a superuser sees the page. The page lists the providers and has a form to create a MANUAL provider or a DISCOVERY provider, and a form to alter or drop one. The form of a client secret takes the secret one time and never shows it again; the page shows only set or none. The fields that ALTER PROVIDER cannot change are read-only. The create form and the edit form show AUTO CREATE USERS as a switch, with a warning: the provider must validate the email addresses, and with the switch on every account of the provider that signs in gets a kinedb user. The page shows the last refresh and the last failure of the keys. Refresh keys now refreshes a DISCOVERY provider. Show SQL runs SHOW CREATE PROVIDER. Drop asks for a confirmation. The server runs each statement and gives each error; the console adds no rule.

Role mappings

A role mapping connects a value that the token of a provider holds (in its ROLES CLAIM, for example a group) to a kinedb role. At a login, the user gets the REAL kinedb role with the normal role grant: SHOW ROLES and the rights of the user show it. The login through a provider uses the mappings and calls the rule below (Log in with an identity provider).

ALTER PROVIDER corp ADD ROLE MAPPING 'kinedb-analysts' TO analyst;
ALTER PROVIDER corp DROP ROLE MAPPING 'kinedb-analysts' TO analyst;
SHOW ROLE MAPPINGS FOR PROVIDER corp;
SHOW MANAGED ROLES FOR PROVIDER corp;

• ADD ROLE MAPPING and DROP ROLE MAPPING stand alone: they take no other change of ALTER PROVIDER. Only the superuser runs them, as the other provider statements.
• The role must exist. A mapping names the role by its principal id: a role that is dropped and created again with the same name does not get the mapping. A provider has at most 256 mappings; a value is 1 to 1024 bytes.
• The roles that a provider manages. Each provider has a set of role names that it manages. A mapping adds its role to the set. With MAP ROLES BY NAME ON, a token value with no mapping gives the role of the same name, when that role exists; the first such grant adds the role to the set. SHOW MANAGED ROLES lists the set. A provider manages at most 512 roles.
• The rule at a login. With UPDATE ROLES OFF, a login changes no role. Else the user gets each role that the token gives (by a mapping, or by name). A matching mapping always grants its role, also a role with AUDIT or QUERY AS. With REMOVE ROLES ON, the user loses each role of the set that the token no longer gives. A role that is not in the set is never removed: a role that an admin granted by hand and that is not in the set stays.
• A role in the set belongs to federation. When REMOVE ROLES is on, a hand grant of a role of the set is removed at the next login of the user if the token does not give it.
• A dropped mapping removes the role from no user, and the role stays in the set. At the next login with REMOVE ROLES ON, the role goes when no other value of the token gives it.
• Two providers. A role is in the set of two providers only while REMOVE ROLES is off for both. So kinedb refuses a mapping to a role of another provider, and ALTER PROVIDER … REMOVE ROLES ON, when that rule would break: role <role> is managed by provider <name>, and REMOVE ROLES is on for one of them. A grant by name of such a role still gives the role, but it does not add the role to the set.
• DROP PROVIDER deletes the mappings and the set of the provider. The users keep their roles.
• SHOW CREATE PROVIDER shows one ALTER PROVIDER … ADD ROLE MAPPING row for each mapping. The restore form (STORED) holds the mappings and the set, so kd-tool restores them.
• Each ADD and DROP writes one row of the audit trail (create_role_mapping, drop_role_mapping) that names the provider and the role. No audit row, error text or log line holds the value of a mapping, and a statement view hides the two statements. The value of a token of a user is stored nowhere.

The detail of a provider on the Identity providers page of the web console has a role mapping section. It lists the mappings and the managed roles of the provider. It adds and drops a mapping with the two statements above.

Apps and return addresses

An app is a program that uses kinedb to sign in its users. A login ends with a return to a web address. kinedb returns only to an address that a pattern of the app allows, so an attacker link cannot send a login result to another site. Each node of a cluster has the same list. Only the superuser can run the statements of this section.

The login through a provider checks its return address against the list of an app: see Log in with an identity provider.

CREATE APP shop
  DISPLAY NAME 'The shop'
  RETURN ADDRESSES ('https://shop.example.com/*', 'http://localhost:*/callback');
SHOW APPS;
SHOW CREATE APP shop;
ALTER APP shop DISABLED;
ALTER APP 'kinedb-admin' RETURN ADDRESSES ('/ui/*', '/console/*');
SET CLUSTER cluster.auth.default_return_addresses = 'https://example.org/*';
DROP APP shop;

The name. An app name is 1 to 63 characters of A-Z, a-z, 0-9, _ and -. It starts with a letter or _. Write a name with a - as a string literal ('kinedb-admin').

The options. DISPLAY NAME '<text>' (the default is the name), ENABLED (the default) or DISABLED, and RETURN ADDRESSES ( '<pattern>' [, …] ) (the default is an empty list, which allows no address). ALTER APP takes the same options; a list replaces the whole list. A list holds at most 32 patterns. At most 64 apps exist.

The patterns. kinedb parses the address into its parts (the scheme, the host, the port and the path) and compares the parts. It never compares the raw text. These are the forms:

Pattern	Allows
*	any https address, and an http address of localhost, 127.0.0.1 or [::1]. Never javascript:, data: or another scheme.
https://shop.example.com/callback	only this address (a query is allowed and not compared)
https://shop.example.com/*	any path on this host and port
https://*.example.com/*	any path on a host of exactly ONE more label (a.example.com, not example.com and not a.b.example.com)
https://shop.example.com/t/*/cb	a * path segment is exactly one segment
https://shop.example.com:8443/cb	the port must be equal; no port is port 443 (http: 80)
http://localhost:*/cb	any port; :* is valid only for localhost, 127.0.0.1 and [::1]
com.example.shop:/cb	a custom scheme, written out in full, with a dot in it (RFC 8252)
/ui/*	a path on the site that started the login; only for the built-in app

A pattern is written in lower case and has a path. A * stands only for the whole leftmost host label, a whole path segment, or the whole pattern; at least two labels follow a host *. kinedb refuses an address with a user part (https://shop.example.com@evil.test/), a fragment (#), a space, a tab, a control character, a backslash, a non-ASCII character (write the xn-- form of a host), a % in the host, a trailing dot, an encoded ., / or \ in the path, a . or .. segment, or an IPv4 address in a short form (127.1). These rules are decisions of the agent that wrote the arc (the smallest match); the issue does not fix the forms.

The client secret. NEW CLIENT SECRET (on CREATE APP and ALTER APP) makes a secret of 32 random bytes, kdcs_ and base64url. The answer of the statement shows it ONE time, in the column client_secret; store it in the app at once. kinedb keeps only its SHA-256, so nobody can read the secret back: SHOW APPS shows set or none, and SHOW CREATE APP has no secret clause. ALTER APP <name> DROP CLIENT SECRET makes the app a public client again. The node that receives NEW CLIENT SECRET runs CLIENT SECRET HASH '<64 hex>'; a superuser can type that form too. The statement views redact both forms. The built-in app takes no secret. The route POST /token checks the secret (Use kinedb as the identity provider of your app).

The built-in app. kinedb-admin is the app of the web interface. It always exists, and nothing is stored until an ALTER APP 'kinedb-admin' changes it: SHOW APPS shows it with stored = false. Its default list is /ui/*: a path on the site that started the login. Its list holds relative patterns only, so it never returns to another site. You cannot create or drop it.

The DEFAULT list. A login that names no app uses the setting cluster.auth.default_return_addresses: the patterns, separated by spaces. It is empty at first, so it allows no address. SET CLUSTER changes it on every node; SHOW SETTINGS shows it. A relative pattern is refused here.

SHOW APPS shows one row for each app, the built-in app first: name, display_name, enabled, built_in, stored, return_addresses, holds_any (the list holds *) and client_secret. SHOW CREATE APP <name> gives the statement that makes the app again (the ALTER form for the built-in app).

Errors. A refused pattern is named by its position and the rule (pattern 2: …). The error never repeats the pattern.

Each statement writes one row of the audit trail (create_app, alter_app, drop_app) that names the app; a name with a - gives a row with no object. When the new list holds *, a second row return_list_holds_any names the app (or the setting of the DEFAULT list, whose change writes a set_cluster row).

The web console shows the apps on its Apps page (/ui/#/apps). Only a superuser sees the page. The page lists the apps and has forms to create, alter and drop an app. It shows a warning when a list holds *. The form of the built-in app gives the pattern of the folder of the page as an example, for example /admin/*. The Identity providers page (/ui/#/providers) shows a warning on each enabled provider with no HOSTNAMES list, on its row and in its edit form, next to the field Host names. The warning goes away after a save that sets a host list. The page shows a new client secret one time, when the statement makes it. A reload never shows it again.

ID token signing keys

kinedb signs ID tokens with one ES256 key set. See kinedb as an identity provider. Only a superuser may run the two statements. A transaction and a function body refuse them.

SET CLUSTER cluster.auth.issuer = 'https://auth.example.com';
SHOW ID TOKEN KEYS;

ALTER CLUSTER ROTATE ID TOKEN KEY;

The issuer. The setting cluster.auth.issuer holds the issuer URL. It is empty at first, and an empty value turns off the discovery document and the key document. The value is https, or http for localhost, 127.0.0.1 or [::1] only. It has no query, no fragment, no user information and no / at the end, and it has at most 2048 bytes. SET CLUSTER changes it on every node, and SHOW SETTINGS shows it. A SET CLUSTER with a bad value is refused.

SHOW ID TOKEN KEYS shows one row for each key: kid, state (active or retired), algorithm, created_at, retired_at and removable_at. The two last columns are null for the active key. The active key comes first. No column holds a private key. The statement makes no key.

ALTER CLUSTER ROTATE ID TOKEN KEY makes a new active key and retires the old one. When no key exists, it makes the first key. It also removes each retired key that is past removable_at (1 hour and 10 minutes after retired_at). Every node needs the cluster key: the statement is refused without it.

The audit trail records the actions create_id_token_key, rotate_id_token_key and remove_id_token_key. Each row names the kid, never a key.

Audit trail

The server records an audit trail: logins, refusals, user and role changes, grants, DDL, settings, branch merges and node starts. No row holds a password, a hash, a token or a statement text. No statement changes or deletes an audit row, also not as root. These statements need a server.

SHOW AUDIT;
SHOW AUDIT LIMIT 50;
SHOW AUDIT WHERE actor = 'alice' AND result = 'refused';
SHOW AUDIT WHERE action = 'login' AND time >= '2026-10-13T00:00:00Z' AND time < '2026-10-14T00:00:00Z';
SHOW AUDIT WHERE time >= 1760313600000000 LIMIT 10;
GRANT AUDIT TO 'alice';
GRANT AUDIT TO ROLE auditors;
REVOKE AUDIT FROM 'alice';
REVOKE AUDIT FROM ROLE auditors;

SHOW AUDIT answers the newest rows first. Without LIMIT, it answers at most 1000 rows. The highest LIMIT is 10000. A higher LIMIT gets an error that names the maximum.

A filter goes after WHERE. Join two or more filters with AND:

Filter	What it matches
actor = '<name>'	The user that did the action.
target = '<name>'	The user that the action changed, for example the user of ALTER USER.
action = '<action>'	The action, for example login, create_user, grant_audit.
result = 'ok' or result = 'refused'	The result of the action.
node = '<node>'	The node that recorded the row: the text of the node column.
time >= <time>	The time. The operators are >=, >, <= and <.

The value of actor, target, action, result and node is a string literal. The match is exact. A time is an integer of microseconds since the Unix epoch, or an RFC 3339 UTC time in quotes ('2026-10-13T12:00:00Z'). The server refuses a time with an offset such as +02:00.

The columns, in order:

Column	Value
time	The time of the event as RFC 3339 UTC text.
time_us	The same time in microseconds since the Unix epoch.
node	The node that recorded the event.
actor	The user name, system for the server itself, <unknown> or <many>.
action	The action in snake case, for example login, drop_table, merge_branch.
object	The object of the action, for example a table, a database or a role, or NULL.
branch	The branch of the action, or NULL.
target	The user that the action changed, or NULL.
grantee	For a grant: ROLE <name>, ANYONE, AUTHENTICATED or MATCH <field>. Else NULL.
rights	For a grant: the rights, comma-separated. Else NULL.
result	ok or refused.
reason	For a refusal: the reason, for example bad_credentials or permission_denied. Else NULL.
count	1 for one event. A higher number is one summary row for that number of events.
client_addr	The address of the client, or NULL.
client_name	The name that the client sent at its login, or NULL.
build	The build of the node, or NULL.

A burst of the same event from the same actor and client address gives a few rows one by one. After them, the server writes one summary row, with the number of the other events in count.

Who sees which rows. The server applies the grant filter before the WHERE filters:

The caller	The rows of SHOW AUDIT
A superuser (root)	All rows.
A user with the AUDIT privilege, direct or through a role	All rows.
Any other user	Only the rows where the user is the actor or the target.
The guest (no login)	None: the server answers an error.

A WHERE filter only makes the answer smaller. It never shows a row that the grant filter hides.

A grant or a revoke of a right gives a row with the target NULL: the actions grant, revoke, set_policy, grant_function and revoke_function. The role of the grant is in the grantee column (ROLE <name>). A user and a role can have the same name, so the row does not go to a user with the name of the role. The filter target = '<name>' does not match these rows. This applies also to the rows that an older server version wrote.

GRANT AUDIT TO <user> and GRANT AUDIT TO ROLE <role> give the AUDIT privilege. REVOKE AUDIT FROM <user> and REVOKE AUDIT FROM ROLE <role> take it back. These four statements need root. A revoke applies at once: the next SHOW AUDIT of that user shows only the rows of the user. The word ROLE after TO or FROM always starts the role form. To name a user that has the name role, write the name in quotes: GRANT AUDIT TO 'role'. The audit trail records each GRANT AUDIT and REVOKE AUDIT. A grant to a role also reaches the members of each role inside that role (a role inside a role).

The web console shows the audit trail on its Audit log page (/ui/#/audit). The page sends SHOW AUDIT with the filters that you set. It shows the error of the server, for example to the guest. The action filter offers the name of each action as a suggestion. You can also type a name, and the server matches it exactly.

GRANT QUERY AS

A web server can keep one shared connection and run each request as the user of that request. The frame QueryAs does this with a bearer token. The QUERY AS privilege lets a connection send that frame. See Run a query as a user of a token. These statements need a server.

GRANT QUERY AS TO 'webapp';
GRANT QUERY AS TO ROLE gateways;
REVOKE QUERY AS FROM 'webapp';
REVOKE QUERY AS FROM ROLE gateways;

The rules:

• No user has the privilege by default. The server refuses QueryAs until a grant exists.
• root also needs the grant. root can grant it to itself.
• The check uses the user of the connection and the roles of that user. A grant to the user, or to one of its roles, is enough. The roles of the user include each role that a role of the user is a member of (a role inside a role).
• A guest and a connection with no login never have the privilege.
• The four statements need root, as GRANT AUDIT does. The word ROLE after TO or FROM always starts the role form. To name a user role, write the name in quotes.
• The grant covers the token of every user. A grant for the token of one user only is not available yet (B2847).
• A grant or a revoke does not end the open sessions. The server reads the grant again at each QueryAs frame, so a revoke applies to the next frame.
• The audit trail records each GRANT QUERY AS and REVOKE QUERY AS, with the actions grant_query_as and revoke_query_as. The frame QueryAs writes the action query_as. Its actor is the user of the connection and its target is the user of the token. The node that serves the connection writes ONE such event for each pair of a connection and a token user. Because target names the token user, that user also sees the event in SHOW AUDIT. Example: SHOW AUDIT WHERE action = 'query_as' AND target = 'alice';

Settings and cluster operations

These statements are for operators, and most of them need a server.

SHOW SETTINGS;
SET CLUSTER cluster.slow_query_threshold_ms = 100;
SHOW CLUSTER STATUS;
SHOW CLUSTER QUORUM;
SET CLUSTER QUORUM = 1;
SHOW NODES;
SHOW PEERS;
SHOW RAFTGROUPS;
SHOW RAFTGROUP 0;
SHOW SHARDS;
SHOW PLACEMENT;
SHOW TABLE STATS;
SHOW METRICS;
SHOW SLOW QUERIES;
SHOW MEMORY;
SHOW DISK STATUS;
SHOW SYNC STATUS;
SHOW S3 STATUS;
SHOW ROOTS;
SHOW LEAVES OF accounts LIMIT 100;
CLUSTER VERIFY;
SPLIT SHARD 0 OF accounts AT 'm';
MERGE SHARDS 0 1 OF accounts;
ALTER CLUSTER EVICT NODE 'abc123';
ALTER CLUSTER READMIT NODE 'abc123';
ALTER CLUSTER ROTATE TOKEN KEY;
VACUUM;

For cluster.auth.issuer and the ID token key statements, see ID token signing keys.

The setting cluster.auth.web_password_login switches the password login of the web on and off. With false, the web login takes the password of root only:

SET CLUSTER cluster.auth.web_password_login = false CONFIRM;
SET CLUSTER cluster.auth.web_password_login = true;

The setting cluster.auth.auto_create_users_per_hour (default 50, 0 to 10000) limits the users that the first logins through one provider with AUTO CREATE USERS ON make in one hour. 0 makes no user:

SET CLUSTER cluster.auth.auto_create_users_per_hour = 20;

• Who may run these. root runs every statement. Another logged-in user runs the statements that show the catalog or the state of the server: SHOW SETTINGS, SHOW NODES, SHOW PEERS, SHOW PLACEMENT, SHOW RAFTGROUP <id>, SHOW METRICS, SHOW MEMORY, SHOW DISK STATUS, SHOW SYNC STATUS, SHOW S3 STATUS, SHOW CLUSTER QUORUM, SHOW COMMITS and SHOW COMMIT COUNT. The statements that show data derived from the rows of a table need more. SHOW TABLE STATS OF <table> and SHOW SHARDS OF <table> need a SELECT grant on the whole table (a MATCH or WHERE grant is not enough); SHOW TABLE STATS and SHOW SHARDS list only the tables the user has that grant on. SHOW COMMITS, SHOW COMMIT COUNT, SHOW TABLE STATS, SHOW SHARDS and SHOW BRANCHES read the database of the session. A user who does not see that database in SHOW DATABASES gets database not found: <db>, the same answer as for a database that does not exist. OF <table> names the table of the database of the session. Without OF, a user sees only the tables of the database of the session; root sees the tables of every database. SHOW DIFF OF <table> needs a SELECT grant on the whole table, now and at both commits. SHOW SLOW QUERIES and SHOW STATEMENTS (the SQL text of other sessions), KILL STATEMENT, SHOW RAFTGROUPS (split keys), SHOW ROOTS, SHOW CLUSTER STATUS, CLUSTER VERIFY, SHOW COMMIT <hash> (root hashes), SHOW LEAVES, VACUUM, SPLIT SHARD, MERGE SHARDS, SET CLUSTER and ALTER CLUSTER (also ALTER CLUSTER ROTATE TOKEN KEY) need root. The answer to a refused statement is permission denied: …; it names the user and the statement, never a row, a key or a count. <statement> ON ALL NODES needs the same right as the statement inside it.
• SET CLUSTER <setting> = <value> CONFIRM confirms a change that the server refuses otherwise. Only cluster.auth.web_password_login takes it: the server refuses false when no enabled provider is on the login page, because then only root can sign in on the web. The word on another setting is refused. See switch off the password login of the web.
• SHOW SETTINGS lists every setting with its value; SET CLUSTER <setting> = <value> changes a cluster setting on every node. See Run kinedb. A standalone server (no --calvin) applies SET CLUSTER on its own node, and the value stays after a restart. When that data directory later starts with --calvin and no --join, the node keeps the value. A start with --join refuses (exit 3), because a joiner takes the settings of the cluster: set KINEDB_DROP_LOCAL_CLUSTER_SETTINGS=1 for one start, then run the SET CLUSTER statements of the file b3279_dropped_cluster_settings of the data directory on the cluster. A standalone server refuses SET CLUSTER when its data directory was a node of a cluster of more than one node: the value would exist on that node only. The answer then starts with settings: B3279:. Run the SET CLUSTER on the cluster instead (see Troubleshooting). A boot. row shows the value that the node uses and, in source, what gave it: the flag (--webroot), the environment variable (KINEDB_WEB_ROOT), auto-detect, derived from --memory or default. boot.webroot shows the full path of the directory that the node serves, or nothing when the node serves no UI. A secret shows as (set): the --cluster-key value and the -e statement. boot.s3 shows the URL without its key and secret (s3://***@host/bucket).
• SHOW NODES, SHOW PEERS, SHOW RAFTGROUPS and SHOW SHARDS show the members, the Raft groups and the shards. CLUSTER VERIFY checks that the replicas agree. SHOW SHARDS takes leader_id and peers from the replica on THIS node. On a cluster with more nodes than the replication factor, a node holds no replica of some shards: for those shards, leader_id is empty and peers is 0. Reads of those shards still work, because the node forwards them to a replica (B1722).
• SHOW METRICS returns one row for each counter of this node. Rows that start with shard_<id>. belong to one shard. Each shard has seven rows for its Raft log: raft_log_entries and raft_log_bytes give the size of the log, and raft_log_droppable_bytes gives the part that compaction can remove. The shard compacts its log when raft_log_droppable_bytes is more than raft_log_compaction_trigger_bytes (0 means that this byte trigger is off). raft_log_batch_entries counts the log entries that hold a batch of writes, and raft_log_batch_bytes gives their part of raft_log_bytes. raft_log_unarmed_batch_entries counts the batches with data that calvin.live_batch_bytes does not count. SHOW MEMORY gives only the sum over all shards. Four rows show the block store of the node. io.block_puts_total counts the blocks that the node gave to the store. io.block_dedup_hits_total counts the blocks that the store did not write, because it held the same content. io.blocks_written_total counts the blocks that the store wrote. These three rows start at 0 when the node starts and only grow, and io.block_puts_total is the sum of the two others. io.blocks_stored is the number of block entries in the indexes of the store now. It decreases when a sweep removes dead blocks. storage.wal.rotate_deferred_total counts the wal.d rotations that waited, because the node was at a resource limit (for example the open-file limit) when it opened the next segment or the seal. The node writes on into the current segment and tries the rotation again at the next commit. A value that grows means that the node is at that limit.
• SHOW STATEMENTS lists the client statements that run on THIS node now, over the WebSocket and over HTTP POST /sql, one row each: id, user (NULL with no login; a name longer than 128 bytes can show cut), peer (the client address), started (milliseconds since the Unix epoch), elapsed_ms and sql (the first 256 characters). A transaction() call and an HTTP transaction body are listed too, as ONE row with the text <transaction of N statements>. The rows come in id order. An id is unique on the node while it runs, but it is not the start order: use started for the start order.
• SHOW STATEMENTS ON ALL NODES lists the running client statements of every node. Each row has a first column node: the host:port of the node that runs the statement. The rows of the node that answers come first, then the rows of each peer. The id of a row is unique only on its node. It needs the same right as SHOW STATEMENTS (superuser).
• A user statement shows no text. CREATE [OR REPLACE] USER and ALTER USER can carry a password, a password hash, an API secret hash or a public key. kinedb shows such a statement as a fixed label in SHOW STATEMENTS, in SHOW SLOW QUERIES and in the refusal of a statement inside transaction(). The rule redacts a text when the words CREATE USER, CREATE OR REPLACE USER or ALTER USER are next to each other at any position of the text, in any case. A word in brackets counts as the word, so ALTER [USER] … is redacted too. In these pairs, a string literal whose text is the word also counts, so ALTER "USER" … is redacted. The rule also redacts a text with the word PASSWORD or IDENTIFIED at any position: the password forms of other databases (ALTER ROLE … PASSWORD '…', SET PASSWORD FOR …, … IDENTIFIED BY '…') are redacted too. A column named password is that word, so a statement with it is redacted too. So the rule also applies after EXPLAIN or DESCRIBE, to every statement of a text with ;, and to a text that does not parse. The words inside a string literal or a -- comment do not count, except in a pair. A text with USER, PASSWORD or IDENTIFIED that the tokenizer refuses is redacted too. SHOW SLOW QUERIES also hides the error of a redacted text. A parse error never shows the content of a string literal: it says a string literal. Inside a user statement, a parse error names only the kind of a token that you typed (a word, a number, a bytes literal), never its text. The error of a text that does not tokenize and has USER, PASSWORD or IDENTIFIED does not show the text. The query console of the web UI keeps the same label in its history, and removes a secret that an older version kept there.
• KILL STATEMENT <id> stops the statement <id> of SHOW STATEMENTS on this node. The statement ends at its next scan step or at its next point read (for example a join that reads the other table by its primary key, or an IN list of keys), and its client gets the error statement stopped: killed. An id that no running statement has is an error.
• A statement also stops when its client closes the connection, over the WebSocket and over HTTP POST /sql: it ends with statement stopped: client closed, and the node does not finish its scan. A client that closes only its sending side after the request (a half-close, for example shutdown(SHUT_WR)) also counts as closed, and its statement stops.
• In a cluster, the stop also reaches the part of the statement that another node runs for it: a read or a write that the node sends to a shard leader or to a voter. That node ends its work at its next scan step. A read answers the stop at once. A write waits for the answer of the leader: when the leader already proposed it, the answer is its real result or outcome unknown, never statement stopped.
• The stop also reaches the exchange stream of a repartition join, also of an AS OF join: the voter that streams a slice of a shard ends its scan, and the client gets statement stopped: <reason> at once. A transaction that the node sends on to another node needs no stop on that node: its send is its commit (see below).
• A stopped write changes nothing. A read, an INSERT, UPDATE, DELETE, PUT or UPSERT finds all its rows before it writes any, so a stop can only land before the write. A statement that could leave part of its work behind if it stopped in the middle runs to its end, also after a close or a KILL: DDL (CREATE, ALTER, DROP, TRUNCATE), CALL, INSERT … FILE, VACUUM and the other statements in this section that change the cluster.
• A transaction (a transaction() call or an HTTP transaction body) stops as ONE statement. A stop before its commit ends it with statement stopped: <reason>, and nothing of it is written. From its commit to its answer, a stop does not end it: the transaction commits, and its answer is its real result. A transaction with a statement that runs to its end (for example a DDL) runs to its end as a whole. When the node sends a transaction on to another node, the send is its commit: a stop before the send ends it, and a later stop does not.
• SPLIT SHARD and MERGE SHARDS split and merge shards by hand. The cluster also splits a shard by itself when it grows.
• SPLIT SHARD <id> OF <table> AT <key> gets two child ids from the shard id and the key. When the cross-shard transaction ledger already has a row for one of these ids (an id that an earlier shard used), the statement proposes nothing and answers ONE error: SPLIT SHARD <id> refused: <reason>. Nothing was proposed. A different split key gives different child ids. The same key gives the same error. Use a different key. This case is very rare (B2339).
• The key of SPLIT SHARD must give each child at least one row. The leader of the shard checks the key against its rows before it proposes the split. A key outside the key range of the shard, or a key with no row key on one side (above the largest key, or below the smallest key), gets ONE error that is not retryable: SPLIT SHARD <id> OF <table> refused: <reason>. Nothing was proposed. Pick a split key with rows of the shard on each side. Use a key between two row keys of the shard (B921).
• Right after a leader change of the shard, SPLIT SHARD can answer the retryable error split deferred: a parent voter has not answered the new leader yet; retry shortly. The new leader does not know yet how far each voter is, and a split now would give the children fewer copies. The wait ends when each live voter answered, and after at most a few seconds (B2423). Retry the statement.
• SPLIT SHARD waits up to 30 s for the split to apply on the node that runs it. When a new leader of the shard replaced the split entry before it committed, the answer is the retryable error SPLIT NOT applied: … retry the SPLIT. The split did not happen; retry the statement. When the wait ends without a decision, the answer is an "outcome unknown" error (SPLIT timed out at log index … outcome unknown). The split can still apply. Look at SHOW SHARDS before you retry (B2859).
• When the apply refuses the split entry, SPLIT SHARD answers at once with an error that is not retryable: SPLIT NOT applied: the apply refused the SplitOp at log index … (term …): <reason>. The split did not happen, and a retry of the same statement gets the same refusal. An example of a reason: the split key leaves one child empty. Look at SHOW SHARDS, then pick a new split key (B3519).
• MERGE SHARDS <left> <right> OF <table> goes to the leader of the metadata group. It first SEALS the two shards: from the seal until the merge ends, each shard refuses every write (shard is merging, retryable) and serves no read. Then it proposes the merge. The statement runs ONE time. When a step fails (a seal, a shard that still owes work of a cross-shard transaction, the merge that does not apply in time), it aborts: the two shards stay, the seals go, and the answer is ONE error that names the cause. It does not retry. When the two shards still owe work of a cross-shard transaction before the seal, the statement first waits up to cluster.merge.drain_wait_ms (default 5000 ms, 0 = off) for that work to drain. During this wait and until the merge ends, a new cross-shard transaction that writes to one of the two shards gets a retryable hold refusal. When the work does not drain in time, the answer is the retryable refusal of before (B3119). When such work still stops the merge after the seal, the merge aborts and the answer is also retryable: nothing changed and the two shards are writable again (B3149). When the node that runs the statement stops in the middle, the leader of each sealed shard aborts the merge after 120 s and removes its seal. When a sealed shard loses the majority of its voters, the cluster first aborts the merge, then it rebuilds or removes that shard. It never rebuilds or removes a sealed shard while the merge is pending. Every voter of the two shards must run this version: an older node cannot read the seal entry. After the merge, a cross-shard transaction that a client planned on the old layout and that names a merged shard is refused (retryable, nothing applied). The client runs it again, and the new plan names the merged shard.
• ALTER CLUSTER EVICT NODE removes a node from the cluster, and READMIT lets it back. EVICT first puts the node on the denylist, then removes it from the voters. It answers the evicted row when the node is not a voter any more, also when the membership observer committed the removal first. On a node that is not the leader, EVICT and READMIT answer after that node applied the change.
• ALTER CLUSTER ROTATE TOKEN KEY puts a new random key in the key record of the bearer tokens. The record keeps the previous key. After one rotation, each live token works on every node, and a new login gets a token of the new key. After a second rotation, each token of the first key gets a 401, also after a restart. A node that is behind, or cut off from the cluster, accepts the dropped key until it applies the rotation. An open WebSocket connection of a dropped key ends at its next request. A live WATCH of that connection ends at its next push or frame. A token that was minted between the two rotations has the key of the middle generation. It stays valid until its expiry, and a third rotation ends it. Run the statement two times to end the tokens of a stolen key, and three times to also end the middle tokens. After the second rotation, each client of an old token gets a 401 and logs in again. Each rotation makes every open WebSocket connection read the users lock once. A rotation does not help when the thief can also read the data directory, the WAL or the backups: the new key is stored and replicated in the same way as the old key. Keep the data directory and its backups as private as the root password. The answer names the new generation: token key rotated: generation <n>. With no key record (no login yet), the answer is no token key exists; nothing to rotate, and the statement writes nothing. When two rotations run at the same time, one of them fails with another token key rotation ran at the same time; run the statement again. The node that gets the statement answers after it applied the rotation. The audit trail has one rotate_token_key row of the user, with no key in it. The row of a rotation that changed nothing (the one that failed) is a refused row, never an ok row. A single process (no metadata log) rotates the key too, with the same answers.
• VACUUM reclaims the space of dead blocks. It returns one row for each number of the sweep: dead_fraction_threshold and dead_share_target are the two rules it ran under, segments_compacted and segments_compacted_for_share say how many segments each rule took, and bytes_reclaimed against bytes_rewritten says what the sweep gave back and what it wrote to do it. KINEDB_VACUUM_DEAD_FRACTION and KINEDB_VACUUM_DEAD_SHARE set the two rules; see Run kinedb.