Skip to content

v0.5

Choose a tag to compare

View all tags
@markhammond markhammond released this 17 Oct 14:16
· 24 commits to main since this release
v0.5
6b7ef1b

ChalkQL v0.5

This release adds standing views. A standing view keeps a statement's answer current as one row per key, and a reader drains it from a cursor, receiving only what changed. A view recomputes its statement at each update or, as an experiment, maintains its answer from the rows that changed. Views read in-process tables and databases, run under an access policy with every update audited, and move to a better plan as their tables grow, without pausing their readers.

Planning comes off the refresh path. A refresh can leave statistics and plans to the background, PlanAsync and PlanNow() say when new plans take effect, and an observer hears every planning decision. A table's replacement is built off the refresh lock. SQL gains rollups, as-of joins that seek, WITH computed once, CURRENT_TIME, and dates cast to text. Paths now hold a parent's visibility by default, and statistical columns are guarded more tightly. Some of this changes results or refuses policies that 0.4 accepted; Changed lists every such change.

Targets .NET 10. A JDK 21 or newer is needed only to run the planner locally. Assemblies and namespaces retain the Chalk.* names.

NuGet packages:

Package Licence
ChalkQL NuGet Elastic License 2.0 engine, planner client, catalogue, execution, entitlements and standing views
ChalkQL.Sources NuGet Elastic License 2.0 POCO, Akade.IndexedSet, ADO.NET, DuckDB and source conformance implementations

Highlights

Standing views

A view is made from a statement and the result columns that key it. Map turns each result row into the host's own unmanaged key and row, which the view keeps:

await using var view = await engine.MaterializeAsync(sql, new ViewOptions<CellKey, Cell>
{
    Key = ["row_key", "column_key"],   // the result's key columns
    LocalJoinMaxRows = StandingViews.DefaultLocalJoinMaxRows,
    Map = Cells.Map,                   // (in ViewRow row, out CellKey key, out Cell cell)
    RowHash = Cells.Hash,              // optional: summed over the live rows as the checksum
    Parameters = new { venue = "LMAX" },
});

await view.UpdateAsync();                         // computes, at the engine's latest epoch
var outcome = view.Drain(ref cursor, sink, 4096); // copies what changed since the cursor
  • Drains send each changed key once, with its latest row, then commit the view's version and checksum. A drain hands rows to the sink by reference and allocates nothing. A reader whose backlog is as large as the view is reset with the whole result. ResetWhen sets that threshold, as a fraction of the view's rows.
  • Updates compute only when asked, at one epoch, read whole. ReachedAsync(version) waits for a version and brings the view to it.
  • A view of a prepared statement runs that statement's plan. A run at the view's epoch then gives the view's rows bit for bit, so a host can check a view by its checksum.
  • Inputs say how they change. A POCO table may be declared AppendOnly, AsOf or Keyed, and a refresh takes Upsert, Delete and Advance beside Replace and Append. An engine may name a clock (ChalkEngineOptions.Clock), which a refresh moves with AdvanceClock. Every query on that engine reads the clock as of the refresh its tables are read at.
  • Views over databases read their databases when the engine is refreshed (DatabaseReads.WhenRefreshed). Every view states a local-join cap, LocalJoinMaxRows: the most rows a cross-source join may fetch to join locally.
  • Views under an access policy. One statement, prepared for a shape, serves each principal's view through ViewOptions.Context. A refresh that withdraws a grant takes the rows out at the next update. Every update is audited, and the audit event carries the epoch's Version.
  • Hosts see what a view costs. StateBudget bounds what a view holds, StateBytes estimates it, and a Tracker hears each update's time, allocation and changes.

Incremental maintenance, experimental

With ViewMaintenance.Incremental, an update computes only what changed since the last. It maintains:

  • filters and projections;
  • inner, left, right, semi and anti joins;
  • aggregates, their FILTERs included;
  • ORDER BY … LIMIT, in every form the planner writes;
  • filters over a window of the clock, re-reading only the rows that cross the window's edges;
  • as-of joins, index lookups pinned to keys, and joins that seek a governed table by key.

Incremental maintenance carries the diagnostic CHALK003, which a project acknowledges with <NoWarn>$(NoWarn);CHALK003</NoWarn>. Its behaviour and API may change in any release, so check what a maintained view gives against its statement before relying on it. A view that recomputes carries no such mark.

A maintained update keeps what it can from one update to the next: its staging, its operators' iterations, its result's keys, and the memory its changes are written in. In a pivot over as-of joins under a rollup, a steady update allocates about 8.5 KB, and the view's own share is within a few bytes of its statement's.

Plans that keep up with the data

  • Statistics follow drift. A POCO table computes its statistics again once a tenth of its rows have moved, and sends the planner its numbers then. A prepared statement becomes stale when a table it reads has moved tenfold. PreparedQuery.LogicalDigest names a statement however its plan moves.
  • Planning off the refresh. DeferPlanning() leaves a refresh's statistics and plans to one background run. Its plans swap in at the next refresh, never underneath one.
    • PlanAsync() plans now, and completes once the new plans are in effect, with true when a plan changed. Await it where nothing is served yet, as after seeding at startup.
    • PlanNow() asks for the same run inside a refresh, ordered with it.
  • A plan asked for again costs nothing when it would not change. The planner answers that the plan is the one the caller holds, and sends no plan to parse, check or compile.
  • A view swaps plans without a stall.
    • The planner prices a new plan against the one the view runs, and holds it back unless it saves PlanImprovementMargin.
    • RefreshQueryPlan lets the host veto asking for one.
    • Under ViewPlanSwap.LowJitter, the view builds the new plan beside the one it runs, starting as soon as the statement takes it in, and swaps it in once built.
  • An observer. ChalkEngineOptions.PlanningObserver hears each statement prepared, judged and taken in, and each background run.

Refreshes off the lock

  • PrepareReplaceAsync builds a table's replacement off the refresh lock, and a later refresh commits it. The commit adds the rows appended since its cut, and holds the lock about as long as an append: 0.3 ms against 22 ms for a Replace, in one host.
  • A replacement can take the host's array over without a copy: PrepareReplaceAsync(table, rows, count, through). PocoTableBuilder<T>.ArrayPool(pool) names the pool a table's row arrays come from and go back to. With both, a compaction and the appends after it allocate no row arrays.
  • A refresh costs what it changes.
    • A keyed table finds its upserts and deletes by key.
    • An append extends a table's indexes by the rows it appends.
    • A refresh publishes only what it moved, and validates only the tables whose numbers moved.
  • RefreshAsync<TState>(state, plan) lets the plan be a static lambda that captures nothing.

SQL

  • GROUPING SETS, ROLLUP, CUBE and GROUPING().
  • As-of joins seek their right side through an index. They take a condition on one input as a filter on that input, accept a row-value equality in ON, and match on a computed column.
  • A WITH block is computed once, however many times it is read.
  • Time: CURRENT_TIME and LOCALTIME; the difference of two timestamps; a number times an interval.
  • Functions and arithmetic: LEAST and GREATEST; a DECIMAL product or quotient rescales rather than failing.
  • Dates to text: a DATE or TIMESTAMP casts to VARCHAR, written as DuckDB writes it.
  • Computed once per execution: an expression of constants, and a range bound, which then serves any ordered or clustered index.

Changed

Exceptions

A ChalkQL exception raised while a query runs now reaches the host. Previously, exceptions would be wrapped and so catch (ArenaBudgetExceededException) never fired. The same holds for SourceTimeoutException, SourceExecutionException, SourceContractException, InvalidUtf8Exception, StalePlanException, BatchLifetimeException and UnsupportedFeatureException.

// 0.4
catch (ExecutionException e) when (e.InnerException is ArenaBudgetExceededException) { … }

// 0.5
catch (ArenaBudgetExceededException e) { … }

Temporal

  • The engine's clock. On an engine that names a clock, every query reads it, a view or not. A query that reads the time before any refresh sets the clock is refused with ClockRequiredException. An engine without a clock reads the wall clock, as before.
  • Current time, once per execution. CURRENT_TIMESTAMP, LOCALTIMESTAMP and CURRENT_DATE are computed once per execution.
  • Text read as a timestamp is read as DuckDB and PostgreSQL read it, on any machine.
    • A zone or an offset in the text names an instant. A TIMESTAMP is then the wall clock as written, with the zone dropped. Text that writes no zone is read as written.
    • 0.4 read zoned text in the machine's time zone.
    • A cast from text to a date, a time or a timestamp is no longer sent to a source.
  • Zone-less timestamps read back. A zone-less TIMESTAMP read through ValueAt, ToRows() or a composite field is a DateTime of kind Unspecified, as documented. 0.4 returned a DateTimeOffset at the machine's offset, which moved the instant on any machine away from UTC.

Statistics and plans

  • Statistics are kept until rows drift. A POCO table keeps its column statistics until a tenth of its rows have moved. Statistics(level, refreshWhenGrownBy: 0) computes them at every refresh, as 0.4 did.
    • The planner is sent a row count once it is a tenth away from the one it holds.
    • The catalog a host reads keeps exact counts.
  • Drift makes a statement stale. A prepared statement is stale (IsStale) when a table it reads has moved tenfold in rows since it was planned, as after a change of shape.
  • PlanDigest names the plan that runs, and moves when statistics change it. Use LogicalDigest for a statement's identity.
  • Plain plan text no longer carries the search's sampled costs, which varied from run to run. PrepareOptions.VerbosePlanText brings them back.
  • Parameters beside intervals. A parameter beside an interval takes its own declared type, not the interval's.

Entitlements and tenancy

  • Parent visibility along paths. An Inherited or Related path now reaches a principal only in a role every table it steps down to admits, and carries those tables' scope tests. 0.4 took a parent's value of a kind as a marking, and read none of the parent's visibility.
    • ExemptFromParentVisibility(roles), declared on the path, keeps the marking for the roles it names.
    • Where the default leaves a role no way to a table's rows, the policy is refused, naming the role, the table, the parent and the clause to add.
    • RequireExplicitParentVisibility() makes every path name its choice, with ExemptFromParentVisibility or BoundByParentVisibility.
    • Results change where a parent's rule leaves a role out.
  • Through a parent. A table entitled Through a parent is exactly as visible as its parent. A rule of its own that would narrow that is refused with InvalidPolicy: one that leaves out a role the parent admits, or holds in fewer scopes.
  • Disclosure behind an aggregate. A disclosure companion behind GROUP BY carries each group's least disclosure. It no longer reads PER_ROW on every grouped row where every row is full.
  • Group-size floors count the rows each guarded call reports on, its FILTER included.
    • A call filtered on a statistical column is floored over the rows its filter keeps. Below the floor the call is NULL, while its group and its other aggregates stand.
    • An aggregate argument computed from a statistical column is refused.
    • So is a unique-key pin inside a FILTER or an argument.
  • A principal may own nothing. TenancyPrincipal.User is nullable. A principal that owns no rows no longer keeps owner tests in its plans.
  • Population-only rules. A population-only rule for one role no longer refuses every statement prepared for a shape that projects the column.
  • Explanations report a group-size floor only to the principals it applies to, and fold a branch that a resource owner and an owner rule both state into one.
  • Audit events carry the version of the epoch they report (EntitlementsAuditEvent.Version).

In-process sources

A table declared as a log (AppendOnly or AsOf) refuses a refresh that brings a row older than its frontier, less its lateness. The refusal names the row, and nothing in the refresh is applied. CatalogVerificationException names the table, the declaration, the row and what to do.

Fixed

  • Disclosure along a path. A column rule for a role held along a table's own path no longer applies to every row a reader sees. In 0.4, where the role's grants folded to a constant on the path, the rule's verdict was written into every row, so a column could be disclosed on rows the rule did not cover.
  • List parameters. A statement with a list parameter is checked against its entitlements report at every list length, not only the one it was prepared at.
  • One epoch per execution. An execution reads every table at one epoch. A refresh that committed while a join ran could give it one table as it was and another as it became.
  • Disclosure through a union. A disclosure companion keeps its column's label behind a UNION ALL, and is no longer dropped behind a barrier when the column is FULL.
  • Paths through parents.
    • A table entitled Through a parent that is entitled only along paths is no longer refused as "through an unrestricted parent".
    • An index on a grant table's grantee column no longer trips the taint check of a path through it.
  • As-of joins over an entitled table plan, where 0.4 raised a planner internal error.
  • Long errors. An error longer than 64 KB no longer takes down the connection to the planner, and every statement in flight with it.
  • Akade range indexes over a temporal member serve lookups.
  • Interval arithmetic is no longer sent to SQLite, which has no interval type.
  • Strings computed once. A string computed once for an execution no longer comes out empty in the vectorised executor.
  • Declared parameter types hold when the statement is validated.

Known limits

  • Incremental maintenance is experimental under CHALK003, and may change in any release.
  • As-of joins: an expression on one side of a MATCH_CONDITION is refused, and so is a SQL-bodied table function's parameter in an as-of join's ON.
  • Through a parent:
    • a child's column rule for a role the parent holds only along a path is not read;
    • through a parent itself entitled through a grandparent, a child's column rules read no role.
  • Shared statements: a view over a shared statement (ViewOptions.Context) updates at two to five times the cost of a view over a statement folded for one principal, and holds about a sixth more state.
  • Maintained MIN and MAX keep a map, a sorted set and three arrays for each group.
  • SQLite INTEGER is read as a 32-bit integer, so declare a column that holds larger values BIGINT.
  • SQLite under a policy: a statement's parameterised filter and its GROUP BY are not pushed to SQLite, so the statement reads every row the policy admits.
  • StateBytes is an estimate made from counts. It leaves out the plan and the buffers a view's operators work in.
  • Unchanged from 0.4:
    • ChalkQL reports the first rule a statement breaks.
    • No index serves ILIKE.
    • A tenancy policy that declares no combinations is limited to five scopes on a table.
    • Composite types are one level deep.
    • DECIMAL values wider than 28 digits need a Tier 2 kernel in host functions.
    • CHALK001 still marks the experimental arena APIs.
    • ChalkQL remains read-only, and some correlated and LATERAL query shapes are refused.

On the wire

  • Planning.
    • A plan request may name the plan its caller holds (known_plan_digest), and ask the planner to price plans (price_plans, cheaper_by).
    • A response may say the plan is unchanged (plan_unchanged) or held back (plan_held), and carries plan_cost, known_plan_cost and logical_digest.
    • PlannerOptions.verbose_plan_text adds the search's sampled costs to plan text.
  • IR.
    • Shared plans (SharedPlan, SharedRef) carry a WITH block computed once.
    • AsOfLookupJoin seeks an as-of join's right side through an index.
    • A fetch carries the order it keeps, and GROUPING joins the aggregate functions.
    • The IR version does not move.

buf breaking against v0.4 passes, but the two versions do not work together. A remotely deployed planner must use the 0.5 planner JAR. PlannerArtifact.CopyToAsync exports the artefact matching the installed client.

Full Changelog: v0.4...v0.5

Licence and attribution

ChalkQL is source available under the Elastic License 2.0 (ELv2).

Apache Calcite and its JVM dependencies are bundled into the planner artefact distributed with ChalkQL. Their licences and attribution are recorded in THIRD-PARTY-NOTICES.txt.

Assets 2
Loading