Skip to content

building.md

Latest commit

 

History

History
433 lines (341 loc) · 19.5 KB

building.md

File metadata and controls

433 lines (341 loc) · 19.5 KB
Raw

Building and running OpenIVM

OpenIVM currently requires DuckDB v1.5.4 and must be built from source. The build below produces both a DuckDB command-line program with OpenIVM built in and a loadable extension.

You do not need to replace your existing DuckDB installation. Use the executable from this build for OpenIVM, and open an existing database with it as described below.

1. Install the build tools

You need Git, CMake (>= 3.5), Make, Python 3, and a C++ compiler. Ninja is recommended. The commands below are for macOS and Linux; run shell commands in Terminal, and SQL in the DuckDB prompt after launching it.

macOS (including Apple Silicon)

Install Apple's command-line developer tools, then install the remaining tools with Homebrew if you use it:

xcode-select --install
brew install cmake ninja python

Ubuntu / Debian

sudo apt-get update
sudo apt-get install build-essential git cmake ninja-build python3

2. Clone the source and its dependencies

git clone --recurse-submodules https://github.com/ila/openivm.git
cd openivm

For an existing clone that is missing dependencies:

git submodule update --init --recursive

Use the submodule revisions recorded in the repository. They include the matching DuckDB source and LPTS SQL compiler; installing DuckDB separately does not provide these build dependencies. Do not update the submodules to their latest branches when following this guide.

3. Compile

Run the commands for your platform, replacing /path/to/openivm with your clone's location.

macOS

cd /path/to/openivm
CMAKE_BUILD_PARALLEL_LEVEL=4 GEN=ninja make

Linux

cd /path/to/openivm
GEN=ninja make

On Linux you can also set CMAKE_BUILD_PARALLEL_LEVEL=4 to limit build concurrency.

This builds DuckDB as well as OpenIVM, so the first build can take a while. The four build workers used in the macOS command are a conservative starting point; lower the number if compilation runs out of memory. You can rerun the same command after an interrupted build.

The main outputs are:

File Purpose
build/release/duckdb DuckDB CLI with OpenIVM built in; use this for the steps below
build/release/extension/openivm/openivm.duckdb_extension Loadable extension for a matching DuckDB build and platform

Without Ninja, use CMAKE_BUILD_PARALLEL_LEVEL=4 make in a fresh checkout. Keep the same generator when reusing a build directory.

4. Launch OpenIVM and check the version

From the repository root:

./build/release/duckdb openivm_demo.duckdb

This opens or creates a persistent database in the current directory. Calling just duckdb may start a different installation. At the DuckDB prompt, run:

SELECT version(); -- expected: v1.5.4
SELECT extension_name, loaded
FROM duckdb_extensions()
WHERE extension_name = 'openivm'; -- expected: openivm | true

OpenIVM is already loaded in this executable; no INSTALL or LOAD is needed. Use .quit to exit the CLI.

5. Try an incremental refresh

In a fresh demo database, paste the following SQL into the DuckDB prompt:

CREATE TABLE sales (region VARCHAR, product VARCHAR, amount INT);
INSERT INTO sales VALUES ('US', 'Widget', 100), ('EU', 'Gadget', 200);

CREATE MATERIALIZED VIEW regional_totals AS
    SELECT region, SUM(amount) AS total, COUNT(*) AS cnt
    FROM sales GROUP BY region;

-- Batch changes before refreshing once.
INSERT INTO sales VALUES ('US', 'Bolt', 50), ('JP', 'Gear', 300);
UPDATE sales SET amount = 110 WHERE product = 'Widget';
DELETE FROM sales WHERE product = 'Gadget';
PRAGMA refresh('regional_totals');

SELECT * FROM regional_totals ORDER BY region;
-- JP | 300 | 1
-- US | 160 | 2

-- Both checks must return zero rows, including when duplicates are present.
SELECT * FROM regional_totals
EXCEPT ALL
SELECT region, SUM(amount), COUNT(*) FROM sales GROUP BY region;

SELECT region, SUM(amount), COUNT(*) FROM sales GROUP BY region
EXCEPT ALL
SELECT * FROM regional_totals;

The same example is included in docs/build/demo.sql. From the repository root, run it directly (no copying or saving SQL is needed):

mkdir -p openivm_generated_sql
./build/release/duckdb -bail < docs/build/demo.sql

With no database filename, each run uses a fresh in-memory database. To keep the results, pass a new database filename before <, for example ./build/release/duckdb -bail openivm_demo.duckdb < docs/build/demo.sql.

In a fresh CLI session launched from the repository root, you can also run the script from the DuckDB prompt, after creating the output directory in your terminal:

.read docs/build/demo.sql

Inspect the generated SQL

SQL file output is opt-in. The included docs/build/demo.sql sets openivm_files_path; the SQL pasted above does not. No default output directory is selected. Create a directory in your terminal, then configure it in the DuckDB session before creating or refreshing a view:

mkdir -p /absolute/path/to/openivm_sql
SET openivm_files_path = '/absolute/path/to/openivm_sql';
PRAGMA openivm_files('regional_totals');

The pragma reports the absolute path, existence, and status of each reference file, plus the process working directory. It does not generate files. If output is disabled, it explains how to enable it. Relative paths are relative to the working directory of the DuckDB process, not the attached database file. A file that exists may come from an earlier compilation; existence does not prove freshness. The setting applies to the current session and must be set again after reopening. An unwritable or missing directory now produces an error with the affected path.

After running the included demo from the repository root, inspect its files from your terminal:

cat openivm_generated_sql/openivm_system_tables.sql
cat openivm_generated_sql/openivm_compiled_queries_*.sql
cat openivm_generated_sql/openivm_upsert_queries_*.sql

The wildcards include all views in that directory. For one view, use the exact paths returned by PRAGMA openivm_files('regional_totals'); filenames use an internal key derived from the view's catalog, schema, and name.

These contain system-table DDL, view setup SQL, and the SQL compiled for the refresh, respectively. The refresh file is overwritten when a new refresh is compiled and contains that refresh's catalog names and delta cutoff timestamps. It is an inspection artifact, not the demo's input script. The optional openivm_initial_load_explain_<internal_key>.txt contains the initial-load plan. A refresh that skips empty deltas may not compile a new file. Setting the path after CREATE does not retroactively create the setup files; do not replace an existing MV just to obtain an inspection file.

Archived compiled SQL

Enable automatic archival before creating or refreshing a view:

SET openivm_archive_compiled_sql = true;  -- default: false
SET openivm_compiled_sql_retention = 10;  -- latest distinct versions per view and operation

OpenIVM then archives the programs it runs for CREATE MATERIALIZED VIEW and refresh in two tables next to the view's openivm_views metadata, independently of openivm_files_path. Neither setting creates SQL artifact files. For DuckLake views the default is the native frontend database's main schema. With custom metadata placement, the archive follows the selected catalog and schema; qualify the tables below, for example as control.openivm.openivm_compiled_programs and control.openivm.openivm_compiled_statements.

SELECT view_catalog, view_schema, view_sql_name, operation, version,
       statement_count, last_outcome, committed_count
FROM openivm_compiled_programs ORDER BY view_name, operation, version;

SELECT s.stmt_order, s.sql
FROM openivm_compiled_statements s
JOIN openivm_compiled_programs p USING (view_name, operation, version)
WHERE p.view_catalog = current_database() AND p.view_schema = 'main'
  AND p.view_sql_name = 'regional_totals' AND p.operation = 'refresh'
  AND p.version = (SELECT max(version) FROM openivm_compiled_programs
                   WHERE view_name = p.view_name AND operation = 'refresh')
ORDER BY s.stmt_order;
  • Identity. view_name is the internal key also used by openivm_views; view_catalog, view_schema and view_sql_name give the qualified MV name. operation is create or refresh. compilation_id identifies the operation that first stored a version; last_compilation_id is the latest operation that ran the same program. For refreshes outside an explicit transaction this is the refresh_id in openivm_refresh_profile when profiling is enabled; refreshes inside an explicit transaction are not profiled.
  • Versions. A program is the complete statement list, in execution order. A new version is stored only when that list differs from the view's latest stored version for the same operation. The comparison is exact: delta cutoffs, snapshot IDs and catalog references are not normalized away. A refresh inside an explicit transaction, for example, stamps its own timestamp, and DuckLake refreshes embed snapshot IDs, so these usually store a new version. Refreshes skipped because deltas are empty compile nothing and store nothing. If a DuckLake refresh rolls back its first delete attempt and retries with ranked deletion, only the retried program that committed is stored. For snapshot-published DuckLake views, the executed publication statement is stored between the data statements and the watermark update. Statements OpenIVM derives while it runs, such as DuckLake delta-table definitions and pinned snapshot publications, are stored as the SQL that actually ran. Cleanup of consumed delta rows after a refresh commits is housekeeping and is not part of the stored program.
  • Outcomes. last_outcome describes the latest operation that ran the version: committed (its transaction committed), attempted (it failed and rolled back, so nothing was committed) or unknown (a cross-catalog refresh failed after its first statements ran outside a transaction, or COMMIT itself failed, so some effects may have committed). This includes a DuckLake refresh whose data statements roll back, because its in-progress marker in the metadata location has already committed. committed_count counts committed runs. Compile-only calls such as openivm_compile_with_facts are never archived, so a stored program has always been executed.
  • Transactions. Native CREATE and refresh write the archive in their own transaction, so it is visible only if they commit. A failed or rolled-back native operation in an explicit transaction leaves no row. An autocommit refresh that fails is recorded as attempted (or unknown) in a separate metadata transaction. DuckLake data cannot share a transaction with native metadata, so cross-catalog operations write the archive on separate connections: refreshes in one metadata transaction after they commit (or fail), even inside an explicit transaction, and DuckLake CREATE with its native metadata.
  • Archive failures. OpenIVM does not report success if the archive cannot be written. For native operations the archive write is part of the transaction, so a failed write rolls back the CREATE or refresh with an error. If a cross-catalog refresh or DuckLake CREATE OR REPLACE commits but its archive write fails, it returns an error that says it committed. If recording a failed refresh also fails, both errors are reported.
  • Retention. openivm_compiled_sql_retention must be at least 1 and defaults to 10. Each archived operation retains the newest N distinct versions for that view and operation (create or refresh), deleting older program and statement rows in the same transaction. All outcomes count toward the limit. Set it to 1 to keep only the latest program of each kind. Identical programs reuse their version and still apply the current limit; empty-delta skips do not prune. Lowering the limit takes effect on the next archived operation for each view and operation, not immediately across the database. Pruning rolls back with transactional archive writes. Version numbers continue increasing after pruning.
  • Disabling and cleanup. SET openivm_archive_compiled_sql = false stops recording and pruning without deleting existing history or disabling file export. Enabling it later records future operations, not earlier ones. History survives DROP VIEW and CREATE OR REPLACE; a recreated view continues the same version sequence. This is a per-view history bound, not a total byte limit or expiration policy for dropped views. To remove history manually, delete matching (view_name, operation, version) rows from both tables in one transaction. Empty archive tables may exist even when recording is disabled. Reapply the settings after reopening the database; use global settings for daemon refreshes.

Archived SQL is for inspection and auditing. Later refreshes always compile against current state; do not run an archived program to refresh a view.

Inspect or save compiled SQL without files

The compiler also returns SQL as rows. This works without openivm_files_path:

SELECT stmt_order, stmt_kind, sql
FROM openivm_compile_with_facts(
    'regional_totals', '{"target_dialect":"duckdb","compile_only":true}')
ORDER BY stmt_order;

CREATE TABLE saved_refresh_sql AS
SELECT * FROM openivm_compile_with_facts(
    'regional_totals', '{"target_dialect":"duckdb","compile_only":true}');

This compiles a refresh without executing it or consuming the pending changes. The saved table is an ordinary user table, not automatically maintained OpenIVM metadata, and compile-only calls do not add to the compiled SQL archive. The generated program depends on current state, including delta cutoffs and snapshot IDs. Compile again for a later refresh; do not treat an old saved program as a reusable refresh procedure. Use PRAGMA refresh for normal execution.

Use the prompt or a SQL file rather than putting creation and refresh together in one -c argument: refresh metadata can be resolved before the preceding view creation has executed.

6. Use an existing DuckDB database

Start with a backup or test copy, and close other processes that have the database open for writing. You can open the file directly:

./build/release/duckdb /absolute/path/to/analytics.duckdb

Or launch ./build/release/duckdb and attach it at the SQL prompt:

ATTACH '/absolute/path/to/analytics.duckdb' AS analytics;
USE analytics;
SHOW TABLES;

Replace the path with an existing file: DuckDB otherwise creates a new database. USE analytics makes it the default catalog for subsequent unqualified table and view names. You can now create materialized views over its tables and refresh them as in the example above. You do not have to export and reimport compatible files.

OpenIVM runs inside this DuckDB process; a separate metadata server is not required. For this workflow, keep the source tables and materialized views in the same writable database. OpenIVM creates its metadata, backing tables, and delta tables there. A READ_ONLY attachment is useful for inspecting data, but cannot support this in-place maintenance workflow.

For ordinary DuckDB tables, perform subsequent inserts, updates, and deletes through connections with OpenIVM loaded so it can record the changes. Writes made through another DuckDB installation without OpenIVM are not automatically captured. DuckLake uses a different snapshot-based change-tracking path; see DuckLake integration.

Databases created by other DuckDB versions

The extension version requirement and the database file format are separate:

  • OpenIVM must run with its matching DuckDB v1.5.4 build. A locally compiled extension cannot be loaded into an arbitrary DuckDB version.
  • DuckDB supports reading older database formats, with backward compatibility established from v0.10. A database does not need to have been created with v1.5.4 to be usable here.
  • Reading a file written by a newer DuckDB release depends on the storage features used; forward compatibility is not guaranteed. ATTACH does not convert an incompatible format.

See DuckDB's storage compatibility documentation. Files created with DuckDB v1.1.2 and v1.2.1 were tested with the v1.5.4 OpenIVM build, both opened directly and attached. Each case passed MV creation, mixed-DML refresh, and another refresh after closing and reopening the process. Results were checked against full recomputation with EXCEPT ALL in both directions. These checks do not cover every file version, extension-defined type, or stored SQL definition.

If the file is incompatible, open it with a DuckDB version that can read it, export the required tables to Parquet, and import them into a new database using the OpenIVM build. Recreate views and other schema objects as needed. Keep the original file until the imported data and queries have been verified.

Loading the extension into another DuckDB client

This extension will not load into any other DuckDB version. It is compiled for v1.5.4; v1.1.2 and v1.2.1 clients were both tested and rejected it with a version mismatch, even with unsigned extensions enabled. This is separate from opening older database files using the v1.5.4 executable.

The built CLI is the simplest starting point. For a separate compatible DuckDB v1.5.4 CLI, enable unsigned local extensions when launching it:

duckdb -unsigned /absolute/path/to/test.duckdb

Then load the compiled file by its full path:

LOAD '/absolute/path/to/openivm/build/release/extension/openivm/openivm.duckdb_extension';

The DuckDB version, operating system, architecture, and build platform must match. -unsigned allows loading your local build; it does not bypass version or platform compatibility. Likewise, a Python client uses its own DuckDB library: building the CLI does not update an installed Python package. Hosted services also have their own extension-loading policies; a successful local build does not establish MotherDuck deployment support.

Troubleshooting

Symptom What to check
LOAD 'openivm' cannot find the extension Use ./build/release/duckdb, or load the compiled extension by its full path in a matching client.
Extension version/platform mismatch Check SELECT version() and use the DuckDB executable produced by this build.
Unsigned extension error Launch a compatible external CLI with -unsigned before loading the local extension.
Missing DuckDB/LPTS source or build files Run git submodule update --init --recursive from the repository root.
Compiler killed / out of memory Rerun with CMAKE_BUILD_PARALLEL_LEVEL=2 GEN=ninja make.
Database lock error Close the other process using the file, or work with a separate test copy.
Incompatible database storage version Follow the file compatibility guidance above; rebuilding OpenIVM does not convert the database.

When reporting a problem, include your OS/architecture, SELECT version() output, git rev-parse HEAD, the command you ran, and the complete error message.

Development

For a debug build, use GEN=ninja make debug; its outputs are under build/debug/. Run make format-fix after C++ edits. See testing for the test suite, UPDATING.md for maintainer instructions on changing the pinned DuckDB version, and pipelines for refresh orchestration.