Alpha

These pages are built from main, turborel 0.0.0. Anything may change from one build to the next: SQL, API, protocol and files alike.

Kinds of Plug-in

Besides functions, aggregates, table functions, procedures, metrics, embedding models, authenticators, backup targets and partitioning strategies (Plug-ins), an extension may bring a full-text ranking, a vector quantizer, a collation, a system table, an XQuery function, a data type, a storage engine, an index method, the database's credentials, a sink of its events and a language of full-text search. Each is the in-process extension point of its kind, served by the program over protocol 1.

Every call crosses a pipe and costs a round trip, so the database asks a batch at a time and never asks inside a comparison, a page read or a commit's critical section. What it needs there it works out alone, from what one call gave it before: a codebook, a sort key, a type's key. Each section below gives a kind's declaration, its operations' payloads, what it costs, and an example run with the example plug-in, whose manifest declares every object shown.

CREATE EXTENSION EXAMPLE;
OK

Rankings

RANKING <identifier>

A ranking scores full-text matches for RANK and a top-k search, named by an index's FULLTEXT.RANKING. Kind 10 has two operations.

corpus   := documents u64 | average_length f64 | k1 f64 | b f64
term     := documents u64 | most u32
WEIGHTS  corpus | count u32 | (term | frequency u32 | length u32)...   -> count u32 | f64...
BOUNDS   corpus | count u32 | term...                                  -> count u32 | f64...

WEIGHTS gives the weight of many (term, document) pairs in one call, and BOUNDS each term's greatest weight, so a top-k search asks once a block of candidates. A weight that is not a number fails the statement.

CREATE TABLE DOCS (ID INTEGER NOT NULL UNIQUE, BODY VARCHAR(200));
INSERT INTO DOCS VALUES (1, 'a dog'), (2, 'a cat'), (3, 'dog eats dog dog');
COMMIT;
CREATE INDEX DOCS_BODY ON DOCS (BODY) USING FULLTEXT WITH (FULLTEXT.RANKING = COUNTED);
SELECT ID FROM DOCS WHERE CONTAINS(BODY, '"dog"') = 1 ORDER BY RANK(BODY, '"dog"') DESC;
OK
OK
OK
OK
           ID
--------------
            3
            1
(2 rows)

Quantizers

QUANTIZER <identifier>

A quantizer compresses the vectors a vector index searches by, named by the index's QUANTIZER option. Kind 11 has one operation.

TRAIN  dims u32 | code_bytes u32 | seed u64 | count u32 | (f32s)...
       -> k u32 | count u32 | width u32... | (f32s)... one a subspace, k centroids of its width

TRAIN turns a sample into a product codebook, k centroids in each of the subspaces its widths split the dimensions into. The database keeps the codebook and encodes, decodes and measures by it alone, so a search makes no call. The index trains when it is built, or when maintenance finds TRAIN_AT rows indexed.

CREATE TABLE V (K INTEGER PRIMARY KEY, E VECTOR(2));
INSERT INTO V VALUES (1, '[1, 1]'), (2, '[2, 5]'), (3, '[8, 1]'), (4, '[9, 9]');
COMMIT;
CREATE INDEX V_E ON V (E) USING DISKANN WITH (DISKANN.METRIC = EUCLIDEAN, DISKANN.QUANTIZER = FIRSTS, DISKANN.PQ_BYTES = 1);
SELECT K FROM V ORDER BY VECTOR_DISTANCE(E, '[8, 2]', EUCLIDEAN) FETCH APPROX FIRST 1 ROWS ONLY;
OK
OK
OK
OK
            K
--------------
            3
(1 row)

Collations

COLLATION <identifier> VERSION <character string literal>

A collation orders and compares character strings, named by COLLATE as a built-in one is. Kind 12 has one operation.

KEYS  pad u8 (0 PAD SPACE, 1 NO PAD) | count u32 | str...   -> count u32 | bytes...

KEYS gives the sort keys of many strings at once, and the database compares keys as bytes. A sort, a grouping or an index of a column of the collation asks once a batch of values, and no comparison crosses the pipe. Its VERSION is checked as a built-in collation's is, so an index made by an older version is refused until it is rebuilt. LIKE, TRIM, an XQuery collation and a key made character by character need the characters themselves, which a sort key does not give, so a plug-in collation is refused there with SQLCODE -103.

CREATE TABLE PEOPLE (N VARCHAR(20) COLLATE FOLDED);
INSERT INTO PEOPLE VALUES ('b'), ('A'), ('a'), ('B');
SELECT N FROM PEOPLE ORDER BY N, N COLLATE UCS_BASIC;
SELECT N FROM PEOPLE WHERE N LIKE 'a%';
OK
OK
 N
----------------------
 A
 a
 B
 b
(4 rows)
turborel: SQLCODE -103 (SQLSTATE 2A000): LIKE compares no strings by the plug-in collation FOLDED, whose keys are calls made a batch at a time

System tables

SYSTEM TABLE <identifier> ( <column name> <data type>, ... )

A system table is read in INFORMATION_SCHEMA as the built-in ones are. Kind 13 has one operation.

ROWS  user str | owner u8 | from u64 | max u32   -> rows | more u8

ROWS is asked as a statement reads the table, with the statement's user and whether it is the database's owner, and answers a page of at most max rows, BATCH of them (1,024), after the from given before, and whether more follow. At most 1,000,000 rows are taken, each value checked against its column.

SELECT COUNT(*), MAX(N) FROM INFORMATION_SCHEMA.EXAMPLE_FACTS WHERE NAME = 'ROW';
                      |
----------------------+--------------
                 2500 |         2500
(1 row)

XQuery functions

XQUERY FUNCTION <character string literal> ( <data type>, ... ) RETURNS <data type>

An XQuery function is called in XMLQUERY by its expanded name, written in the manifest as its namespace's URI in braces before its local name. Its parameters and result are atomic, of the types that map to xs:string, xs:integer, xs:decimal, xs:double and xs:boolean, each optional. Kind 14 has one operation, CALL, of one row of arguments to one value, as a function's is, so each call in a query is a round trip.

VALUES (XMLCAST(XMLQUERY('declare namespace f = "http://example.com/fn"; f:twice(21)' RETURNING SEQUENCE BY REF EMPTY ON EMPTY) AS INTEGER));

--------------
           42
(1 row)

Types

TYPE <identifier> [ ORDERED ]

A type is a column's type, named as a built-in one is. Kind 15 has two operations.

INPUT    count u32 | str...     -> count u32 | made...
RECEIVE  count u32 | bytes...   -> count u32 | made...
made:    key bytes | binary bytes | text str

INPUT makes values of their text forms, a cast from a character string, and RECEIVE of their binary forms, a cast from a binary string, a batch at a time. Each value is the three byte strings the program made, which the database stores: a key compared as bytes, the binary form and the text form. It compares, groups, hashes and indexes by the key, and casts to character by the text form and to binary by the binary form, so nothing after a value is made calls the program. A type declared ORDERED may be ordered and range-indexed, one not only compared for equality. It has no arithmetic, no cast to or from another type, no type modifier and no collation.

CREATE TABLE P (K INTEGER, X PAIR);
INSERT INTO P VALUES (1, CAST('(2,1)' AS PAIR)), (2, CAST('(1,5)' AS PAIR)), (3, CAST('(-1, 0)' AS PAIR));
SELECT K, CAST(X AS VARCHAR(20)) FROM P ORDER BY X;
OK
OK
            K |
--------------+----------------------
            3 | (-1,0)
            2 | (1,5)
            1 | (2,1)
(3 rows)

Storage engines

STORAGE ENGINE <identifier> [ BATCH <unsigned integer> ]

A storage engine keeps the rows of each table made USING it. Kind 16 has seven operations, each of a table's locator, which CREATE gives.

CREATE      table str | columns: count u32 | name str...      -> locator bytes
OPEN        locator bytes                                     -> found u8 | next u64 | rows u64 | durable u64
SCAN        locator bytes | after u64 | max u32               -> count u32 | (id u64 | row)... | more u8
GET         locator bytes | count u32 | id u64...             -> count u32 | (found u8 | row if found)...
APPLY       locator bytes | count u32 | (id u64 | change)...  -> count u32 | before...
CHECKPOINT  locator bytes | through u64                       -> (nothing)
DROP        locator bytes                                     -> (nothing)
change, before: u8 0 (no row) | u8 1 and row

The database keeps the transactions. Its table locks isolate them, a writer holding its table to its end, so the program never sees two at once. A transaction's writes stay in the database until it commits. Then the rows it changes are read (GET) and its writes applied (APPLY), BATCH rows a call (1,024 if not written), each call all or none. A call that fails puts every row back and fails the commit. A read merges the program's rows, a page of BATCH rows a SCAN in ascending order of id, or a GET by id, with the transaction's own writes.

The program keeps the position it is durable through, given at a CHECKPOINT, and OPEN gives it back. Recovery applies the log's changes from there, each an APPLY of one row, so APPLY must be idempotent. A process of the extension that stops while a transaction writes a table fails that transaction at commit with SQLSTATE 40001, to run again, so a program restarted never commits half of one.

What a row costs is its share of a call. A commit costs one GET of before-images and one APPLY per BATCH rows written. A scan costs one SCAN per BATCH rows, and a point read one round trip, a GET of its id. A larger BATCH spreads each call over more rows, at the cost of a larger message.

CREATE TABLE T (K INTEGER, V VARCHAR(20)) USING FILES;
INSERT INTO T VALUES (1, 'one'), (2, 'two'), (3, 'three');
COMMIT;
SELECT K, V FROM T ORDER BY K;
OK
OK
OK
            K | V
--------------+----------------------
            1 | one
            2 | two
            3 | three
(3 rows)

Index methods

INDEX METHOD <identifier> SUPPORTS ( EQUALITY | RANGE | NEAREST, ... ) [ BATCH <unsigned integer> ]

An index method keeps each index made USING it, and serves the searches its SUPPORTS names: EQUALITY keys equal to one given, RANGE comparisons, prefixes, ranges and order, NEAREST the nearest entries by a vector query's metric, an approximate search. Kind 17 has eight operations.

CREATE      columns: count u32 | type str...                  -> locator bytes
OPEN        locator bytes                                     -> found u8 | clean u8
WRITE       locator bytes | count u32 | (insert u8 | id u64 | key)...   -> (nothing)
SEARCH      locator bytes | probe | descending u8 | cursor bytes | max u32
                                                              -> entries | cursor bytes
ROWS        locator bytes | key                               -> count u32 | id u64...
NEAREST     locator bytes | column u32 | value | metric str | first u32 | descending u8
                                                              -> count u32 | (distance f64 | id u64 | key)...
CHECKPOINT  locator bytes                                     -> (nothing)
DROP        locator bytes                                     -> (nothing)
key:        count u32 | value...
entries:    count u32 | (id u64 | key)...
probe:      u8 0 every entry
            u8 1 | op u8 | value                       the first column compared
            u8 2 | equal: count u32 | value... | u8 0, or u8 1 | op u8 | value
            u8 3 | equal: count u32 | value... | low op u8 | value | high op u8 | value
op:         0 =, 1 <>, 2 <, 3 >, 4 <=, 5 >=

Inserts and deletes, a rollback's included, are given BATCH at a time (WRITE), before any search and at a checkpoint. SEARCH gives a page of entries in key order, which the database checks, and the cursor to go on from. The index is a list of candidates. Each row is read again as the reader's transaction sees it and kept only if it still has the key, so the entries of a transaction not committed are no one else's answers. OPEN says whether the program has the index and whether it is clean, as it is after a CHECKPOINT with no write since. One not found, or not clean as a crash leaves it, is rebuilt from its table as the database opens.

COMMIT;
CREATE INDEX T_K ON T (K) USING SORTED;
SELECT V FROM T WHERE K BETWEEN 2 AND 3 ORDER BY K DESC;
OK
OK
 V
----------------------
 three
 two
(2 rows)

Credentials and sinks of events

CREDENTIALS <identifier>
DIAGNOSTICS <identifier> [ BATCH <unsigned integer> ]

Credentials make each user's verifier, in place of those the host gives, as CREATE USER ... PASSWORD and ALTER USER run. At most one extension brings them. A sink of events is told each event the database emits, as a sink the host gives is. The events are queued, at most 4,096, the rest counted, and sent BATCH at a time (256 if not written) by a thread of their own, so emitting never waits for a pipe.

CREDENTIALS 1 verifier  user str | password str               -> bytes
DIAGNOSTICS 1 events    count u32 | (level str | kind str | text str)... | dropped u64  -> (nothing)
LANGUAGE <identifier> SERVES ( SEGMENT | STEM | STOP, ... ) [ LIKE <identifier> ] [ BATCH <unsigned integer> ]

A language serves full-text search under its own name, wherever a built-in language is named: an index's FULLTEXT.LANGUAGE, FULLTEXT(text, language) and a pattern's language specification. SERVES says what the program does. SEGMENT cuts text into words, STEM stems a word and STOP gives the stop words. A part it does not serve is that of the built-in language written after LIKE, NONE if none is. So a program may cut Chinese or Japanese into dictionary words and keep no stemmer. Its name may not be a built-in language's, as the built-in languages win. Kind 39 has three operations.

span     := offset u32 | length u32, in characters
SEGMENT  count u32 | str...   -> count u32 | (count u32 | span...)...
STEM     count u32 | str...   -> count u32 | str...
STOP     (nothing)            -> count u32 | str...

A document read is one SEGMENT call, and its words not stemmed before one STEM call more, a call carrying BATCH documents or words at most (256 if not written). The words of the last 32 short documents are kept, so an index that reads a document for its key and for its postings asks once, and a stem is kept once answered. The stop words are one STOP call, made as the extension is created. A pattern's words are cut by the same program. A document is held whole while the program cuts it. A language is named in the whole server, as the first extension that serves it names it, and at most 21 are served at once. An index naming one keeps its extension from being dropped. The example plug-in's TOYZH cuts by a dictionary of a dozen words, so 数据库 is one word and 数据 inside it is not found, where CHINESE's bigrams would find it.

CREATE TABLE ZH (ID INTEGER NOT NULL UNIQUE, BODY VARCHAR(200));
COMMIT;
CREATE INDEX ZH_BODY ON ZH (BODY) USING FULLTEXT WITH (FULLTEXT.LANGUAGE = TOYZH);
INSERT INTO ZH VALUES (1, '我们喜欢数据库'), (2, '全文搜索引擎'), (4, '数据');
SELECT ID FROM ZH WHERE CONTAINS(BODY, '"数据"') = 1;
SELECT ID FROM ZH WHERE CONTAINS(BODY, '"全文搜索"') = 1;
OK
OK
OK
OK
           ID
--------------
            4
(1 row)
           ID
--------------
            2
(1 row)

The points a plug-in may not serve

Ten extension points that a program embedding the database may fill have no kind of plug-in, as a call over a pipe cannot be made where they are used. An encoding transforms each page as it is read and written, and a hash function checksums it, both before the catalog that holds the extensions is open. The clock is read in a commit's critical section, and a keyring's keys must never leave the process. The log and its storage are written by every commit and read first by recovery, and catalog storage holds the extensions themselves. Spill relieves the memory budget when memory is short, a served connection belongs to the server beneath every database, and the cryptography that checks a package's signature cannot come from the package it checks.