Paging and history

Two clauses that both name a position: one in the order a read produced, one in the store's own history.

Paging with AFTER

The table this page pages through, and the one edit that gives it a history:

DEFINE TABLE users (name string, city string);
DEFINE INDEX by_city ON users FIELDS city;
CREATE users:1 = { name: 'ada', city: 'london' };
CREATE users:2 = { name: 'bob' };
CREATE users:3 = { name: 'cyd' };
CREATE users = { name: 'dee' };

UPDATE users:1 SET city = 'york';

START n LIMIT n works and is the obvious thing:

SELECT * FROM users ORDER BY name START 2 LIMIT 2;

3: { name: 'cyd' }
4: { name: 'dee' }
(2 record(s), via scan)

It is also the thing that goes wrong quietly. START 2 means produce the answer, then throw the first two away, so page 500 does five hundred pages of work; and if a record is inserted between two requests, everything shifts by one and the reader sees a record twice or never sees it at all.

AFTER takes the record you last saw instead of a count:

SELECT * FROM users AFTER users:2 LIMIT 2;

3: { name: 'cyd' }
4: { name: 'dee' }
(2 record(s), via scan)

The anchor is a record, not a value — the read already knows what order it is in, so naming the record is enough to say where in that order to resume. It follows the order you asked for, including a reversed one:

SELECT * FROM users ORDER BY name DESC AFTER users:3 LIMIT 2;

2: { name: 'bob' }
1: { city: 'york', name: 'ada' }
(2 record(s), via scan)
note: this page was reached by reading the records rather than seeking to the
anchor, so it cost what the read costs and not what the page costs

That note is the subject of the next section; the answer is the same with or without it.

Because the position is a record rather than an offset, an insert elsewhere in the table does not move it. A page taken this way is stable in a way a counted one is not.

And it says when it could not seek

The point of a cursor is that the database can jump to it. Sometimes it cannot — the order asked for is not one it can seek along — and then it walks, which costs what the whole read costs:

SELECT * FROM users ORDER BY name AFTER users:2 LIMIT 2;

3: { name: 'cyd' }
4: { name: 'dee' }
(2 record(s), via scan)
note: this page was reached by reading the records rather than seeking to the
anchor, so it cost what the read costs and not what the page costs

The same answer either way — this is a cost note, not a correctness one. It is worth reading, because a paging loop that walks is the shape that works perfectly in development and is quadratic in production.

Reading the store as it stood

SELECT * FROM users:1 VERSION 5;

1: { city: 'london', name: 'ada' }
(1 record(s), via record)

SELECT * FROM users:1 VERSION 9;

1: { city: 'york', name: 'ada' }
(1 record(s), via record)

One record, two answers, both true — of different moments.

What changed, rather than what it looked like

VERSION answers what did this record hold at sequence 5. The other half of the question — what has happened to it at all — is a different statement:

INFO FOR HISTORY OF users:1;

{"events": [{"at": "4", "change": "written", "value": {"city": "york", "name": "ada"}},
            {"at": "1", "change": "written", "value": {"city": "london", "name": "ada"}}],
 "complete": true, "walked": "4"}

Newest first, one entry per write, and change is written or removed. A written entry carries the value the record became.

at is not a number to hand to VERSION. It is the write's position in this database's log — the number a replica resumes from — and it counts only this database's commits. VERSION takes the store's own record version, which counts every commit the store made, definitions and other databases included. That is why the two writes above sit at at: 1 and at: 4 while the reads that see them are VERSION 5 and VERSION 9. Use the history for what happened, in what order; a version to read at comes from the store, not from at — the committed tail a refused VERSION names is one, and INFO FOR VERSIONS OF gives a record's current one.

It is deliberately not created / updated / deleted. The log carries what a record became and not what stood there before, so calling the first write a creation would mean consulting the current state to label the past. A wrong label is worse than a missing one: you can tell a first write from a later one by looking at what precedes it, and you cannot recover from being told the wrong thing.

Nothing is written to answer this. Every commit already records the address of what it changed and what that became — a history is a reading of that. There is no event log to enable, nothing added to the commit path, and no way for the answer to disagree with what was committed.

Read complete before believing the oldest entry. The answer is produced by walking the log backwards from the newest record, which is cheap for something written recently and expensive for something last touched long ago — and nobody can tell which they are asking for in advance. So the walk is bounded, and complete: false means it stopped at that bound and older entries may exist below it. walked reports how many log records were read, because a history of three events that cost two thousand records is worth knowing about.

complete is about the log, never about the answer's length. An answer cut short because fifty entries is all the statement returns still reports complete: true, since the walk did reach the beginning.

There is a second reason it reads false. On a node with a retained-record window, the log no longer begins at the beginning, and a walk that reaches the horizon has read everything there is rather than everything there was — so it reports complete: false even where the walk was never bounded by its own limit. The two causes are not distinguished in the answer, and neither one is a failure: both say older entries may exist below this and this node cannot see them. See Bounding the log.

It is not INFO FOR VERSIONS OF. That one is a conflict report: it answers whether a record is contested right now, and on a single-leader range that is one version however many times the record has been written. This answers what it became.

A version is a sequence, not a time

VERSION takes the sequence number the store assigns to each commit. It does not take a timestamp, and that is not a missing convenience. No stored record carries a wall clock, and two commits inside the same millisecond are ordered by sequence alone — so no timestamp could name a point between them. A sequence can.

Ask for one that has not happened and the refusal tells you where the store is:

SELECT * FROM users VERSION 99;

Refused — VersionInTheFuture.

error: sequence 99 is ahead of the committed tail 9

The schema is history too

SELECT * FROM users VERSION 1;

Refused — Unknown.

error: no database named "site"

The name in that refusal is whichever database you are in — it is being reported absent, not misspelt. The catalog lives in the same versioned keyspace as the data, so a read at an earlier point sees the definitions that existed then. A table created at sequence 3 does not exist at sequence 2. This is the honest behaviour rather than a limitation: a historical read that resolved names against today's schema would be answering with a mixture of two moments.

A historical read and the indexes

SELECT * FROM users WHERE city = 'york';                 -- via index
SELECT * FROM users WHERE city = 'london' VERSION 5;     -- via index

The second read asks for london as the store stood at sequence 5, before users:1 moved to york — and by_city still finds it.

An index entry records that a record matches as of the last write, and the write that changes a value removes the entry for the old one. Read from an earlier point, that gives two opposite wrong answers from one cause: a record that has since changed is missing, and one that has since started matching appears, failing the very WHERE clause that selected it. Until 0.34.0-beta that is why a read at an earlier version always scanned.

From 0.34.0-beta the write that removes an entry of a plain index keeps what it said, stamped with the version that removed it. A historical read takes the entries the index holds now together with the ones removed after its version, reads each candidate as it stood, and tests the condition again — so the index changes what the read costs and never what it answers. USING INDEX therefore holds on the read above:

SELECT * FROM users WHERE city = 'london' USING INDEX by_city VERSION 5;

1: { city: 'london', name: 'ada' }
(1 record(s), via index)

Two limits, both decided rather than guessed:

• How far back. A serving node removes the kept entries no open transaction can still need. A read at a version older than that falls back to the scan, and USING INDEX on it is refused with IndexNotUsed.
• Which indexes. Only a plain index keeps them. A UNIQUE, full-text, spatial, containment or vector index is not used at an earlier version, and USING INDEX naming one is refused.

A store written by an older build keeps entries from the moment it holds format 8 — created by 0.34.0-beta, or after ALTER STORE FINALIZE FORMAT; before that its historical reads scan as they did.

VERSION does not go inside a transaction

BEGIN;
  SELECT * FROM users VERSION 5;
COMMIT;

Refused — VersionInsideTransaction.

error: `VERSION` cannot be used inside a transaction — a transaction already
reads at one point in history

A transaction is already a fixed point in history; naming a second one inside it would mean two answers in one transaction that disagree about when they are.

How far back it goes

As far as reclamation has left. Removing old versions is what stops history costing space forever, and the store records the oldest sequence it can still answer exactly. Below that, a read is refused rather than answered from what happens to remain — because "there was no such record then" and "the version that said so has been removed" are different facts, and an answer that cannot tell them apart is worse than a refusal.