DBSP for DuckDB - API Reference

August 2, 2026 · View on GitHub

Complete reference for all DBSP extension functions.

Table of Contents

SQL DDL Syntax

The primary interface. Sources are tracked automatically and views keep themselves current (auto-sync is on by default).

CREATE MATERIALIZED VIEW

CREATE MATERIALIZED VIEW name AS SELECT ...;

Creates an incrementally maintained view. Equivalent to dbsp_create_view('name', 'SELECT ...'). Internally routed through the dbsp_create_materialized_view table function (registered for the parser extension; not intended for direct use).

CREATE OR REPLACE MATERIALIZED VIEW

CREATE OR REPLACE MATERIALIZED VIEW name AS SELECT ...;

Plain CREATE when name doesn't exist yet; when it does, changes its definition and rebuilds only name and its transitive dependents — equivalent to dbsp_replace_view('name', 'SELECT ...'). See dbsp_replace_view below for the rebuild semantics.

REFRESH MATERIALIZED VIEW

REFRESH MATERIALIZED VIEW name;

Accepted for compatibility — a no-op, since views refresh automatically.

DROP MATERIALIZED VIEW

DuckDB parses DROP MATERIALIZED VIEW natively, which bypasses the extension's parser hook — use the function form instead:

SELECT dbsp_drop('name');           -- or dbsp_drop_cascade('name')

Table Tracking

Usually unnecessary: creating a view auto-tracks its source tables and loads their current state, and auto-sync (on by default) keeps views current on every commit. These functions serve manual workflows: pre-tracking, bulk loads with auto-sync off, and the notify API.

dbsp_track(table_name)

Track a DuckDB table for automatic change detection.

SELECT * FROM dbsp_track('orders');

Parameters:

  • table_name (VARCHAR): Name of the table to track

Returns:

  • result (VARCHAR): Confirmation message with column count

Example:

CREATE TABLE orders (id INT, customer VARCHAR, amount DECIMAL);
SELECT * FROM dbsp_track('orders');
-- Returns: "Tracking table: orders (3 columns)"

Notes:

  • Automatically detects table schema
  • Loads current table contents into tracking state
  • Required before creating views on the table

dbsp_sync(table_name)

Synchronize a tracked table with its current DuckDB state. Detects and propagates any changes made outside of DBSP notifications.

SELECT * FROM dbsp_sync('orders');

Parameters:

  • table_name (VARCHAR, optional): Table to sync. If omitted, syncs all tracked tables.

Returns:

  • result (VARCHAR): Confirmation message

Examples:

-- Sync specific table
SELECT * FROM dbsp_sync('orders');

-- Sync all tracked tables
SELECT * FROM dbsp_sync();

Notes:

  • Use after INSERT/UPDATE/DELETE statements
  • Computes delta between tracked state and actual table
  • Propagates changes to all dependent views

dbsp_tables()

List all tables currently being tracked for CDC.

SELECT * FROM dbsp_tables();

Returns:

ColumnTypeDescription
table_nameVARCHARName of tracked table
columnsBIGINTNumber of columns

Example:

SELECT * FROM dbsp_tables();
-- table_name | columns
-- orders     | 3
-- products   | 5

View Management

dbsp_create_view(name, sql)

Create an incrementally maintained materialized view using SQL syntax.

SELECT * FROM dbsp_create_view('view_name', 'SELECT ... FROM ...');

Parameters:

  • name (VARCHAR): Unique name for the view
  • sql (VARCHAR): SQL SELECT statement defining the view

Returns:

  • result (VARCHAR): Confirmation with source tables

Supported SQL:

-- Filter
SELECT * FROM dbsp_create_view('high_value',
    'SELECT * FROM orders WHERE amount > 100');

-- Projection
SELECT * FROM dbsp_create_view('order_amounts',
    'SELECT id, amount FROM orders');

-- Aggregation
SELECT * FROM dbsp_create_view('totals',
    'SELECT customer, SUM(amount) FROM orders GROUP BY customer');

-- Distinct
SELECT * FROM dbsp_create_view('unique_customers',
    'SELECT DISTINCT customer FROM orders');

-- Join
SELECT * FROM dbsp_create_view('order_details',
    'SELECT * FROM orders JOIN products ON orders.product_id = products.id');

-- Cascading (view on view)
SELECT * FROM dbsp_create_view('vip_totals',
    'SELECT * FROM totals WHERE SUM > 1000');

Notes:

  • Source tables are automatically tracked if not already
  • Views can reference other views (cascading)
  • Circular dependencies are detected and rejected

dbsp_replace_view(name, sql)

Change an existing materialized view's definition in place, rebuilding only that view and its transitive dependents — everything upstream (its sources, and any unrelated view) is left untouched, and every dependent keeps its own original DDL.

SELECT * FROM dbsp_replace_view('view_name', 'SELECT ... FROM ...');

Equivalent to CREATE OR REPLACE MATERIALIZED VIEW view_name AS SELECT ... (the DDL form routes here automatically when view_name already exists).

Parameters:

  • name (VARCHAR): Name of an existing view
  • sql (VARCHAR): New SQL SELECT statement for the view

Returns:

  • result (VARCHAR): Confirmation with source tables

Example:

SELECT * FROM dbsp_create_view('lvl1', 'SELECT k, v * 2 AS d FROM base');
SELECT * FROM dbsp_create_view('lvl2', 'SELECT k, d + 1 AS e FROM lvl1');

-- Redefine lvl1; lvl2's own DDL is untouched, only its values change
SELECT * FROM dbsp_replace_view('lvl1', 'SELECT k, v * 3 AS d FROM base');

Notes:

  • Fails with DBSP-E206 if name doesn't exist — use dbsp_create_view for a new view instead.
  • No transactional rollback (v1): if a dependent fails to recreate mid rebuild, the remaining dependents are still attempted with their saved DDL, and the DBSP-E304 error reports every failure plus which views are still queryable afterward.
  • Cost is proportional to the changed view's subtree — sources and sibling views outside it never repopulate.

dbsp_use_planner(enable)

Deprecated no-op since Phase C5. The planner frontend is the only frontend — the bespoke SQL parser it used to toggle against was deleted. The function stays callable so existing scripts don't break; it always reports ENABLED. dbsp_create_view translates view SQL through DuckDB's own binder/planner, covering scan/filter/projection (arbitrary expressions), GROUP BY aggregation (multiple aggregates, expression keys, HAVING, global aggregates, exact DECIMAL SUM, DISTINCT and FILTER modifiers, ROLLUP/CUBE/GROUPING SETS with GROUPING(), ordered STRING_AGG/ARRAY_AGG, holistic MEDIAN/QUANTILE_CONT/QUANTILE_DISC/ MODE/MAD), inner and outer joins (LEFT/RIGHT/FULL, equi + residual predicates), cross joins, IN/NOT IN/EXISTS/scalar subqueries (correlated included), DISTINCT, DISTINCT ON, UNION/INTERSECT/EXCEPT (ALL and DISTINCT), window functions (expressions auto-projected; ROWS/RANGE/GROUPS frames including constant bounded frames like ROWS BETWEEN 11 PRECEDING AND CURRENT ROW, LAG/LEAD with a constant non-default offset like LAG(v, 12), and NTILE with a constant bucket count like NTILE(4)), non-recursive CTEs, WITH RECURSIVE (deletions included), and ORDER BY/LIMIT/OFFSET (constant or percentage). The few remaining gaps (USING KEY recursion, expression LIMIT, approximate statistics like approx_quantile, unordered string_agg, NTH_VALUE's N — even as a literal constant, unlike NTILE's bucket count — see TODO.md) fail with a DBSP-E110 error naming the construct.

SELECT * FROM dbsp_use_planner();       -- Always: ENABLED

Parameters:

  • enable (BOOLEAN, optional): Enable or disable. Omit to query status.

Returns:

  • result (VARCHAR): Confirmation or current status

dbsp_create_view(name, table, type, spec)

Alternative syntax for simple views without SQL parsing.

SELECT * FROM dbsp_create_view('name', 'table', 'type', 'spec');

Parameters:

  • name (VARCHAR): View name
  • table (VARCHAR): Source table
  • type (VARCHAR): View type (filter, aggregate, distinct)
  • spec (VARCHAR): Type-specific specification

Type Specifications:

TypeSpec FormatExample
filtercolumn op valueamount > 100
aggregategroup_col AGG value_colcustomer SUM amount
distinct(empty string)``

Examples:

-- Filter view
SELECT * FROM dbsp_create_view('expensive', 'orders', 'filter', 'amount > 100');

-- Aggregate view
SELECT * FROM dbsp_create_view('totals', 'orders', 'aggregate', 'customer SUM amount');

-- Distinct view
SELECT * FROM dbsp_create_view('unique', 'orders', 'distinct', '');

dbsp_views()

List all materialized views with statistics.

SELECT * FROM dbsp_views();

Returns:

ColumnTypeDescription
view_nameVARCHARName of the view
sqlVARCHARSQL definition
rowsBIGINTCurrent row count
versionBIGINTUpdate version number

For a view still pending lazy restore (see dbsp_lazy_restore below), rows is read straight from the checkpoint's stashed sink blob (its length-prefix, no decode) rather than the view's live state — exact, since "pending" means no delta has touched it, but this call never triggers the view's actual restore. dbsp_views() enumerates every view, so realizing here to compute a row count would eagerly decode everything on the first call, defeating the point of lazy restore.

dbsp_drop(view_name)

Alias: dbsp_drop_view(view_name) (identical scalar function).

Drop a materialized view.

SELECT dbsp_drop('view_name');

Parameters:

  • view_name (VARCHAR): Name of view to drop

Returns:

  • VARCHAR: "Dropped" or error message

Notes:

  • Fails if other views depend on this view
  • Use dbsp_drop_cascade to force drop with dependents

dbsp_drop_cascade(view_name)

Alias: dbsp_drop_view_cascade(view_name).

Drop a view and all views that depend on it.

SELECT dbsp_drop_cascade('view_name');

Parameters:

  • view_name (VARCHAR): Name of view to drop

Returns:

  • VARCHAR: Confirmation with count of dropped views

Example:

SELECT dbsp_drop_cascade('totals');
-- Returns: "Dropped totals (and 2 dependent views)"

dbsp_deps(view_name)

Show dependencies for a view.

SELECT * FROM dbsp_deps('view_name');

Returns:

ColumnTypeDescription
nameVARCHARRelated view/table name
relationshipVARCHARdepends_on or depended_by

Example:

SELECT * FROM dbsp_deps('vip_totals');
-- name    | relationship
-- totals  | depends_on
-- orders  | depends_on

Querying

dbsp_query(view_name)

Query a materialized view. Returns current contents instantly without recomputation.

SELECT * FROM dbsp_query('view_name');

Parameters:

  • view_name (VARCHAR): Name of view to query

Returns:

  • Table with columns matching the view's schema

Example:

SELECT * FROM dbsp_query('customer_totals');
-- customer | SUM
-- Alice    | 300
-- Bob      | 200

Notes:

  • Results reflect all synced changes
  • Query is O(result_size), not O(source_size)
  • Column names derived from SQL or auto-generated (col0, col1, ...)

dbsp_changes(view_name)

Rows added or removed by the view's most recent sync, as the view's columns plus a signed BIGINT weight (+n insert, -n delete).

SELECT * FROM dbsp_changes('customer_totals');

Parameters:

  • view_name (VARCHAR): Name of the view

Returns:

  • The view's columns, plus weight (BIGINT)

Semantics:

  • Single-generation buffer: holds the delta of the most recent sync step that touched the view, and is overwritten by the next one — consume between syncs.
  • While a group survives an aggregate update, the step contains an (old row, -1), (new row, +1) pair, so one read yields both old and new values.
  • One dbsp_notify_insert/dbsp_notify_delete call is one step: a delete+insert notify pair leaves only the insert step's delta. Read between the two notifies if both halves are needed.
  • Empty result when the view exists but no sync has touched it; error when the view does not exist.
  • Empty right after CREATE MATERIALIZED VIEW, even over a populated table: the initial-population delta is dropped at create (bounded-RAM Phase 1a) — only post-create commits populate the buffer.
  • Reading does NOT drain the buffer: an untouched view keeps serving the same delta (its create-time initial replay, at worst) until the next sync that touches it. Use dbsp_delta_generations() to tell fresh buffers from stale ones.

dbsp_view_state()

Per-view resident circuit-state bytes, approximated by class.

SELECT * FROM dbsp_view_state();

Returns:

  • view_name (VARCHAR) — plus one synthetic row __shared_arrangements for manager-owned shared join arrangements
  • result_bytes, arrangement_bytes, window_bytes, recursion_bytes, other_bytes, total_bytes (BIGINT)

Semantics:

  • Estimates calibrated for trend, not exact heap bytes; H6 payload-shared rows are counted once per scan (attribution goes to the first view scanned; totals are what reconcile with the process footprint).
  • On macOS compare against footprint(1)/phys_footprint, not ps rss — the compressor hides most cold circuit state from RSS (measured: a DAG reporting 2.5GB RSS had a 23GB physical footprint).
  • other_bytes includes per-node output buffers and delta buffers.

dbsp_delta_generations()

Per-view provenance for the dbsp_changes buffer: the commit generation at which each view's delta buffer was last rewritten.

SELECT * FROM dbsp_delta_generations();

Returns:

  • view_name (VARCHAR)
  • generation (BIGINT): commit sequence at the buffer's last rewrite (create-time initial replay or a propagation step); 0 when the buffer was never written (e.g. a restored, still-pending view).

Semantics:

  • Generations only advance when a sync rewrites the view's buffer — reads (dbsp_changes, dbsp_query) never move them.
  • Change-feed pattern: snapshot MAX(generation) as a baseline after setup; after each commit batch, harvest dbsp_changes only from views whose generation moved past the baseline, then advance the baseline. Without the filter, a consumer re-reads stale buffers of untouched views on every batch.

Persistence

dbsp_save()

Save all view definitions to the _dbsp_views table in the default catalog — inside the database file, so copies and backups carry the views. dbsp_save('view_name', 'table_name') saves one view to a named table (identifier-validated).

SELECT * FROM dbsp_save();

dbsp_save(filepath)

Save all view definitions to a JSON file.

SELECT * FROM dbsp_save('views.json');

Parameters:

  • filepath (VARCHAR): Path to output JSON file, relative to the working directory (absolute paths and path traversal are rejected)

Returns:

  • VARCHAR: Confirmation message

JSON Format:

{
  "tracked_tables": ["orders", "products"],
  "views": [
    {
      "name": "totals",
      "sql": "SELECT customer, SUM(amount) FROM orders GROUP BY customer",
      "sources": ["orders"],
      "created_at": 1699900000000
    }
  ]
}

dbsp_load()

Load view definitions from the _dbsp_views table (see dbsp_save()). Missing _dbsp_views is not an error — there is simply nothing to load. dbsp_load('table_name') loads from a named table (identifier-validated; a missing named table IS an error). Loading rebuilds view state from current table data.

SELECT * FROM dbsp_load();

dbsp_load(filepath, 'json')

Load view definitions from a JSON file. The explicit 'json' format argument is required — a bare one-argument form is interpreted as a DuckDB table name, not a file.

SELECT * FROM dbsp_load('views.json', 'json');

Parameters:

  • filepath (VARCHAR): Path to JSON file, relative to the working directory (absolute paths and path traversal are rejected)
  • format (VARCHAR): Must be 'json'

Notes:

  • Recreates views in creation order (handles dependencies)
  • Re-syncs with current table data
  • Continues loading if individual views fail

Returns:

  • VARCHAR: Confirmation with view count

Manual CDC

For cases where dbsp_sync() is not suitable, you can manually notify of changes.

dbsp_notify_insert(table, values...)

Notify DBSP of a row insertion.

SELECT * FROM dbsp_notify_insert('orders', 1, 'Alice', 100.00);

Parameters:

  • table (VARCHAR): Table name
  • values... (ANY): Column values in order

Returns:

  • VARCHAR: Confirmation message

dbsp_notify_delete(table, values...)

Notify DBSP of a row deletion.

SELECT * FROM dbsp_notify_delete('orders', 1, 'Alice', 100.00);

Parameters:

  • table (VARCHAR): Table name
  • values... (ANY): Column values identifying the row

Returns:

  • VARCHAR: Confirmation message

Runtime Modes

dbsp_auto_sync(enable)

Toggle automatic change capture — ON by default: views update on every transaction commit without calling dbsp_sync.

Most plain SQL writes commit in O(delta) via captured deltas:

  • INSERT — explicit transactions containing only INSERTs (G2, captured from transaction-local storage), and autocommit INSERTs from a VALUES list or a deterministic SELECT (evaluated with the INSERT's own casts; partial column lists take their declared DEFAULTs; ~1.0 ms at 1M rows). SELECT sources must be repeatable: no LIMIT/SAMPLE, no table functions, no window functions, no CTEs.

  • Upserts (INSERT ... ON CONFLICT (cols) DO UPDATE SET excluded.-qualified / DO NOTHING) — captured via a LEFT JOIN probe of committed state; unqualified target columns in SET, conditional DO ... WHERE, OR REPLACE, and implicit conflict targets fall back.

  • UPDATE / DELETE — explicit-transaction and autocommit statements (write capture: one internal SELECT reads the old images and computes the new ones before the statement runs; a commit guard — interleaved- commit check, signed COUNT(*), rowid re-verification — validates the captured delta against committed storage). Subquery predicates and DELETE ... USING (rewritten to a correlated EXISTS probe) capture too, when the statement sees pure committed state: autocommit, or an explicit transaction before its first write. A single-row UPDATE on a 1M-row table syncs in ~1.5 ms vs ~2.4 s for scan-and-diff.

  • Any other UPDATE or DELETE — a plan tee (optimizer extension) observes the exact rows the statement processed, covering everything the pre-image capture declines: UPDATE ... FROM, prepared parameters, volatile expressions, post-write subqueries, USING over transaction-local state, indexed-column UPDATEs, repeated writes to one table in a transaction.

Everything else uses scan-and-diff scoped to the tables the transaction touched: multi-match UPDATE ... FROM (two new images for one row — ambiguous, the tee detects it and steps aside), CTEs/RETURNING on non-teeable shapes, non-deterministic expressions (random(), now()), UPDATEs of indexed or LIST-typed columns, multi-statement strings, and any transaction that writes the same table twice. If any statement in a transaction is un-capturable, the whole transaction falls back — captured and scanned deltas never mix for one commit, and guard failures fall back loudly (capture_guard_fallbacks counter). Correctness never depends on capture; the design is in docs/DESIGN_WRITE_CAPTURE.md.

Turn auto-sync off for bulk loads (each autocommit INSERT pays a scoped scan) and run one dbsp_sync() afterwards.

SELECT * FROM dbsp_auto_sync(true);   -- Enable
SELECT * FROM dbsp_auto_sync(false);  -- Disable
SELECT * FROM dbsp_auto_sync();       -- Query status

dbsp_autopersist(enable)

Toggle auto-persist — ON by default: a database that used DBSP views reopens with them alive, no explicit dbsp_save()/dbsp_load() calls.

  • Auto-save: on a clean connection close (the last user connection to an instance), if autopersist is on and any view exists, the extension saves view definitions (dbsp_save()) and a circuit-state checkpoint (save_checkpoint()) automatically.
  • Auto-load: the first DBSP entry point that has a ClientContext (dbsp_query, dbsp_views, dbsp_track, CREATE MATERIALIZED VIEW, dbsp_sync, dbsp_changes) loads persisted state — checkpoint fast path included — if the view registry in this session is still empty. Fires at most once per session and never after a view has already been created here; a missing _dbsp_views table is not an error (there is simply nothing to load).

The two-call dbsp_save()/dbsp_load() contract still works exactly as before and stays useful for named/JSON-file forms and manual snapshots; auto-persist just means the default zero-arg round trip is no longer something a caller has to remember.

Turn auto-persist off for bulk-load workflows the same way as auto-sync — dbsp_autopersist(false) before, (true) after — to skip the save/load overhead entirely.

SELECT * FROM dbsp_autopersist(true);   -- Enable (default)
SELECT * FROM dbsp_autopersist(false);  -- Disable
SELECT * FROM dbsp_autopersist();       -- Query status

dbsp_autopersist_interval(n)

Piggyback a circuit-state checkpoint every n commits (counted across dbsp_sync-driven propagation), so a crash between clean shutdowns loses at most n commits of operator state. Default 0 (off) — the underlying data is never at risk either way (base tables are DuckDB-durable, and a stale checkpoint fails its watermark check and falls back to a full rebuild on load).

SELECT * FROM dbsp_autopersist_interval(100);  -- checkpoint every 100 commits
SELECT * FROM dbsp_autopersist_interval(0);    -- off
SELECT * FROM dbsp_autopersist_interval();     -- query the current interval

dbsp_lazy_restore(enable)

Toggle lazy per-view checkpoint restore (D-lazy) — ON by default: a dbsp_load() circuit-state checkpoint fast path cold-creates every covered view but decodes each one's node/sink blobs on first need instead of eagerly, so reopen returns without paying every view's blob-decode cost. The first dbsp_query(), incoming delta, or other read of a still-pending view's live state decodes it transparently — the query or delta still returns/applies the exact same result, just with the decode cost deferred to when it's actually needed. A checkpointed view never touched between reopen and the next dbsp_save() is re-saved verbatim (no decode at all).

One deliberate exception to "decodes transparently on first read": dbsp_views() reports a pending view's row count from the checkpoint's stashed metadata without decoding it (see dbsp_views() above) — it enumerates every view, so realizing there would eagerly decode everything on the first call.

dbsp_load()'s report string counts views left pending separately from "sources deferred" (D3c table baselines) and "from checkpoint":

Loaded views from _dbsp_views (43 views, 43 from checkpoint, 0 sources
deferred, 43 pending lazy restore)

Turn lazy restore off for bulk benchmarks or when debugging a restore issue and you want every view's state visibly live immediately after dbsp_load():

SELECT * FROM dbsp_lazy_restore(true);   -- Enable (default)
SELECT * FROM dbsp_lazy_restore(false);  -- Disable (eager, pre-D-lazy behavior)
SELECT * FROM dbsp_lazy_restore();       -- Query status

dbsp_stats()

Sync-path observability counters, for monitoring which ingestion path serves a workload:

SELECT * FROM dbsp_stats();
-- metric                  | value
-- captured_delta_syncs    | 1042   -- O(Δ) captured applies (per table)
-- scan_syncs              | 3      -- scan-and-diff fallbacks
-- capture_guard_fallbacks | 0      -- guard-rejected captures (loud)
-- commit_seq              | 1045   -- monotonic baseline mutations
-- tracked_tables          | 4

dbsp_parallel(enable)

Toggle parallel execution (default off): multi-table syncs scan on threads, views at the same dependency level update concurrently during propagation, and large residual-free inner-join probe passes split across cores.

SELECT * FROM dbsp_parallel(true);

dbsp_mv_tables(enable)

Disk-backed MV results (bounded-RAM Phase 1b, default off): every registered view mirrors its result into a __mv_<view> table in the same database — backfilled on enable and at view creation, then kept in sync with one internal transaction per propagation pass. __dbsp_mv_meta(view_name, commit_seq) records the watermark of each table's last write. Backing tables are ordinary tables: durable across reopen and readable without any DBSP state. Disabling stops mirroring and leaves the tables stale.

SELECT * FROM dbsp_mv_tables(true);

Per-commit apply is DELETE (retractions) + INSERT (additions) via a staged temp table; any delta weight beyond ±1 rebuilds the table from its own current rows + the delta.

Table-backed reads (Phase 1c): once a view's table is written, the sink stops integrating — the RAM result is dropped and the _mv table IS the result. dbsp_query, create-time replay of view sources, and shared- arrangement backfill all stream from the table; checkpoints save operator state only (rows are already durable). Enable is idempotent (table functions can be bound twice per statement — a second backfill would run after the results were dropped). Disabling schedules a full view rebuild so sinks reintegrate in RAM.

dbsp_realize(view_name)

Realize one pending (lazy-restored) view's circuit state now instead of on first touch. Returns realized (whether it WAS pending) and state_bytes (the view's resident state after realization) — sum the latter to warm within a RAM budget and leave the tail lazy.

SELECT * FROM dbsp_realize('customer_totals');

dbsp_wait_teardown()

Wait (bounded, ~30s max) for detached close-time teardown threads. Call from a throwaway connection after closing your last real connection and before process exit — exiting during the detached teardown segfaults in static destructors. Pair with an explicit dbsp_save() before the final close so the teardown has no SQL left to run.

SELECT * FROM dbsp_wait_teardown();

dbsp_spill(enable)

Toggle disk-backed state (default off): tracked-table baselines, join indexes (shared and local), top-K sort windows, and oversized holistic aggregate groups move to disk record logs; RAM keeps compact digest indexes and hot-bucket caches. Trades sync CPU (row serialization) for bounded memory. Live state migrates both directions on toggle.

SELECT * FROM dbsp_spill(true);

Error Handling

duckDBSP uses a structured error code system (DBSP-Exxx) that provides:

  • Error codes in format DBSP-E{category}{number} (e.g., DBSP-E101)
  • Clear descriptions of what went wrong
  • SQL highlighting with position markers (^) showing exactly where the error occurred
  • Workarounds suggesting alternative approaches for unsupported features
  • Documentation links to detailed error explanations

Error Categories

CategoryDescriptionExamples
E1xxUnsupported SQLE110 (plan operator not supported)
E2xxValidation errors (invalid input)E201 (Invalid identifier)
E3xxRuntime errors (execution failures)E301 (View update failed)
E4xxResource errors (limits exceeded)E401 (Too many views)
E5xxPersistence errors (I/O failures)E501 (Load failed)

Example Error

SELECT * FROM dbsp_create_view('high_orders',
    'SELECT customer, SUM(amt) FROM orders GROUP BY customer HAVING SUM(amt) > 1000');

-- Error:
-- DBSP-E101: HAVING clause in GROUP BY
--
-- SQL:
-- SELECT customer, SUM(amt) FROM orders GROUP BY customer HAVING SUM(amt) > 1000
--                                                          ^
--
-- Workaround:
-- Use a nested view: create a view with GROUP BY, then create another view
-- with WHERE clause to filter the aggregated results. Tracked in TODO #3.
--
-- Documentation: docs/errors/E1xx/DBSP-E101.md

Legacy Error Messages

Some operations still return simple error strings:

SELECT * FROM dbsp_query('nonexistent');
-- Throws: InvalidInputException("View not found: nonexistent")

Getting Help

For complete error documentation and troubleshooting:

Type Mapping

DuckDB TypeDBSP Internal Type
INTEGER, BIGINT, SMALLINT, TINYINTint64_t
DOUBLE, FLOAT, REALdouble
VARCHAR, TEXTstring
BOOLEANbool
DATE, TIMESTAMP(converted to string)