rqlite substitutes time values before replicating SQL

Replicating SQL text does not make a call to the current time repeatable. Different nodes can execute the same statement at different moments, and replay can occur later still. rqlite 8.33.0, published on November 8, adds rewriting for recognised date and time expressions so the statement carries a chosen time value into execution.

The release changelog describes time and date functions becoming deterministic. The implementation supplies the necessary qualification. It recognises particular function calls and replaces particular arguments that mean now. It does not make every SQL expression involving time deterministic, and it does not synchronize the machines' clocks.

I value rqlite's statement replication because it provides a relatively small distributed relational system. That design also makes nondeterministic expressions a visible engineering concern. Choosing a value before replication is a direct response to the problem, provided the forms the rewriter recognises are understood precisely.

Choosing the input before SQLite evaluates it

The SQL processor at 8.33.0 parses each statement and walks its syntax tree. It already recognises RETURNING and can replace random-number calls. The new implementation adds a separate flag controlling time rewriting and a clock function on the rewriter. That clock defaults to Go's current-time function.

For a recognised call, the code constructs a numeric Julian-day literal from the selected time. It substitutes that literal for a recognised now argument and serializes the modified statement. SQLite then receives a function call with a concrete input. The function still performs its date or time conversion, but it no longer needs to choose its current-time input independently at each execution.

For example, this insertion contains a form that the new code recognises. The application asks SQLite for a formatted datetime while making the current-time input explicit in the SQL text.

INSERT INTO events(id, created_at)
VALUES (1, datetime('now'));

The rewriter changes the argument supplied to datetime. It does not replace the whole insert with a row image or bypass SQLite's expression evaluation. That division is useful because the SQL function and any retained modifiers still belong to SQLite, while rqlite chooses the nondeterministic input before the statement reaches replicated execution.

This is also why replicating a concrete parameter supplied by the application is conceptually different from replicating an instruction to consult local time. A concrete value can remain the same when the statement is repeated. An instruction that reads the environment may produce another answer. The new code turns supported instances of the latter into the former.

Function arguments have different positions

The first group of recognised names includes date, time, datetime, julianday and unixepoch. For these calls, the rewriter looks at the first argument when one is present. If the argument is a string literal or identifier whose value matches now without regard to case, the code replaces it with the chosen numeric literal.

strftime has another argument layout because its first argument is the format. The implementation therefore checks its second argument for now. A treatment that blindly replaced the first argument of every time-related function would damage the format expression. The syntax visitor handles this as a separate case.

timediff receives another branch because it takes two time values. The code checks both arguments and can replace either recognised now expression. This shows the value of inspecting the parsed function's arguments instead of performing a global string replacement over SQL text. The same word can have different meaning depending on its position and syntactic type.

The processor tests include calls to the supported functions and check the rewritten numeric shape. They also include a time call with a fixed date, which retains that argument. A date example keeps its start-of-month modifier while replacing the now input. The test demonstrates that the transformation can preserve SQLite's remaining conversion instructions.

Random-number rewriting remains a separate decision in the visitor. A call to random can become a numeric literal when random rewriting is enabled, while ordering terms receive special treatment. The time-rewrite option does not replace that existing policy. A statement can need both transformations and still require its RETURNING clause to set the row-producing execution flag.

The processor creates a new rewriter for each statement in the request. Clock sampling is therefore not a request-wide timestamp assignment for a batch either. An application that requires several stored columns or statements to share exactly one timestamp can supply that concrete value itself. The implementation's per-statement construction and per-call sampling do not establish that equality on the application's behalf. This follows from the processor loop and does not depend on any later release behaviour.

The numerical representation has its own details

The conversion helper decomposes the selected Go time into calendar fields and a fractional day. It calculates a Julian day and formats the result as a numeric literal. The code's formatting uses six digits after the decimal point. Consequently, the emitted representation has a defined precision determined by that formatting choice.

The helper also reads the calendar fields of the time value passed to it. The default rewriter clock is time.Now, and the shown conversion does not first call UTC on the value. That is a detail worth distinguishing from the general goal of repeatability. Replicating one chosen literal makes it repeatable across executions, while the interpretation of the chosen instant is another property of the conversion.

The visitor computes its Julian-day value when visiting each function call. A comment says the value should be the same for the duration of the statement, but the code invokes the clock inside the visit method. Multiple calls in one statement can therefore sample the clock separately. This inference follows from the placement of rw.nowFn() in the function-call branch.

Those details limit a claim that the release establishes one exact statement timestamp for every possible expression. The source shows recognised substitutions with a particular conversion and sampling strategy, applied before the database evaluates the modified SQL text. That is useful functionality, but describing its actual mechanism gives application authors a firmer basis for choosing their SQL forms.

The parser defines the recognised scope

Zero-argument calls do not enter the branches that require an explicit argument. The now recogniser checks string literals and identifiers, not a parameter value discovered later during binding. There is also no general case in this visitor that expands every schema default or function body containing a time expression. The transformation operates on the syntax tree of the submitted statement.

Parse failures are another boundary. The processor continues past a statement when its own parser cannot parse it. It does not reject all SQL that it cannot transform. A statement may therefore reach SQLite without the rewriting an application expected. SQLite accepting syntax and rqlite recognising that syntax for transformation are different conditions.

The HTTP query parameter implementation also exposes norwtime, which disables time rewriting. The HTTP service passes the corresponding flag into SQL processing. This makes rewriting a request policy as well as a parser capability. Disabling processing or requesting no time rewrite changes what the server can establish before execution.

For local reads that do not enter the replicated log, the service has no reason to apply the same preprocessing merely for replay. Its query handler conditionally processes SQL for the strong path. The purpose of the new feature is clearest where a chosen time value becomes part of a statement that may execute through replication.

Repeatability is the immediate benefit

The useful guarantee of a successful substitution is that subsequent execution sees a concrete numeric argument. That directly addresses nodes consulting their own current time for the recognised expression. It leaves the SQL function responsible for formatting and modifiers, and it leaves unsupported expressions outside the transformation's scope.

My preference is to express timestamps explicitly when their exact meaning is part of an application's data model. The new rewriter makes common now expressions more convenient within rqlite's replication model, but its implementation still determines which expressions receive that treatment. In this release, function recognition, argument position and parser success jointly decide whether a time value becomes part of the replicated SQL.