DuckDB Compatibility¶

RockLake aims to be a transparent, drop-in replacement for DuckLake's built-in PostgreSQL and SQLite catalog backends. From DuckDB's perspective, talking to RockLake should be indistinguishable from talking to a PostgreSQL instance running the DuckLake schema. This page documents the complete SQL compatibility matrix, version support, known differences, and the testing methodology that ensures compatibility across DuckDB releases.

Compatibility Philosophy¶

DuckDB's ducklake extension emits a specific, bounded set of SQL statements to manage catalog metadata. RockLake does not implement general-purpose SQL — it implements exactly the SQL patterns that DuckDB's ducklake extension produces. This is the "bounded SQL" design: rather than building a full SQL engine, RockLake recognizes and handles only the specific statement patterns it will encounter.

This approach has important implications:

• If DuckDB's ducklake extension emits a new SQL pattern (in a new version), RockLake must be updated to recognize it
• Arbitrary SQL from custom clients will be rejected unless it matches a known pattern
• The compatibility surface is finite and testable

Compatibility Matrix¶

Schema Operations¶

Table Operations¶

Column Type Support¶

RockLake stores column types as strings matching DuckDB's type system:

Data File Operations¶

Transaction Operations¶

View and Macro Operations¶

Sequence Operations¶

Inlined Insert Operations¶

DuckDB Version Compatibility¶

Supported Versions¶

Version Detection¶

RockLake detects the client's DuckDB version from the startup message parameters and adjusts its behavior accordingly. Older versions may emit slightly different SQL patterns for the same operations, and RockLake handles these variations transparently.

Wire Corpus Testing¶

Compatibility is verified through a wire corpus — a collection of recorded PostgreSQL wire protocol messages captured from each supported DuckDB version. The corpus records the exact SQL that DuckDB emits for every catalog operation, and the test suite replays these messages against RockLake to verify correct handling.

tests/golden/wire-corpus/
├── duckdb-1.5.2/
│   ├── create-schema.pgwire
│   ├── create-table.pgwire
│   ├── insert-file.pgwire
│   └── ...
├── duckdb-1.5.3/
│   ├── create-schema.pgwire
│   ├── create-table.pgwire
│   └── ...

When a new DuckDB version is released, the corpus is extended with recordings from that version. If the new version emits new SQL patterns, RockLake's bounded SQL dispatcher is updated to recognize them.

Known Differences¶

Transaction Isolation Level¶

Practical impact: None for typical workloads. The single-writer model means write conflicts cannot occur — there is only one writer. Readers always see a consistent snapshot.

Error Messages¶

RockLake returns standard SQLSTATE error codes identical to what PostgreSQL would return. However, the human-readable error messages differ:

-- PostgreSQL:
ERROR: relation "ducklake_schemas" does not exist

-- RockLake:
ERROR: Table not found in catalog: ducklake_schemas

Well-behaved clients (including DuckDB) use SQLSTATE codes for error handling, not message text. If your custom tooling parses error messages as strings, it may need adjustment.

Session Variables¶

RockLake acknowledges but ignores most PostgreSQL session variables:

System Catalog Queries¶

DuckDB's ducklake extension does not query PostgreSQL system catalogs (pg_catalog, information_schema). If custom clients send such queries, RockLake returns empty results or an error depending on the specific table.

COPY Protocol¶

The PostgreSQL COPY protocol (bulk data transfer) is not supported. DuckDB does not use it for ducklake operations — all data transfers happen through standard query/response messages.

Bounded SQL: What's Recognized¶

RockLake's SQL dispatcher classifies incoming statements into categories. Here are examples of recognized patterns:

Catalog Reads (SELECT)¶

-- List schemas
SELECT schema_id, schema_name FROM ducklake_schemas WHERE database_id = $1 AND (end_snapshot_id IS NULL OR end_snapshot_id > $2)

-- List tables in schema
SELECT table_id, table_name, table_uuid FROM ducklake_tables WHERE schema_id = $1 AND (end_snapshot_id IS NULL OR end_snapshot_id > $2)

-- List columns for table
SELECT column_id, column_name, data_type, ordinal_position, is_nullable FROM ducklake_columns WHERE table_id = $1 AND (end_snapshot_id IS NULL OR end_snapshot_id > $2)

-- List data files
SELECT file_id, file_path, file_format, row_count, file_size_bytes FROM ducklake_data_files WHERE table_id = $1 AND (end_snapshot_id IS NULL OR end_snapshot_id > $2)

Catalog Writes (INSERT/UPDATE)¶

-- Create schema
INSERT INTO ducklake_schemas (schema_name, database_id, created_snapshot_id) VALUES ($1, $2, $3) RETURNING schema_id

-- Create table
INSERT INTO ducklake_tables (table_name, schema_id, table_uuid, created_snapshot_id) VALUES ($1, $2, $3, $4) RETURNING table_id

-- Drop table (soft delete)
UPDATE ducklake_tables SET end_snapshot_id = $1 WHERE table_id = $2

Unrecognized SQL¶

If RockLake receives SQL it does not recognize, it returns:

ERROR: Unsupported SQL statement
SQLSTATE: 42601
DETAIL: The bounded SQL dispatcher does not recognize this statement pattern.
HINT: RockLake only handles catalog SQL emitted by DuckDB's ducklake extension.

Testing Your Setup¶

After connecting DuckDB to RockLake, verify compatibility:

-- Create a test schema and table
CREATE SCHEMA test_compat;
CREATE TABLE test_compat.verify (
    id BIGINT,
    name VARCHAR,
    amount DECIMAL(18,4),
    created_at TIMESTAMP,
    tags VARCHAR[],
    metadata JSON
);

-- Verify all types were stored correctly
DESCRIBE test_compat.verify;

-- Insert and verify data round-trip
INSERT INTO test_compat.verify VALUES (1, 'test', 99.99, '2024-01-01', ['a','b'], '{"key": "value"}');
SELECT * FROM test_compat.verify;

-- Test time travel
CREATE TABLE test_compat.versioned (id INTEGER, value VARCHAR);
INSERT INTO test_compat.versioned VALUES (1, 'first');
INSERT INTO test_compat.versioned VALUES (2, 'second');
-- Query at older snapshot should show fewer rows

-- Clean up
DROP SCHEMA test_compat CASCADE;

Further Reading¶

• DuckDB — Connection setup and usage guide
• Architecture: SQL Dispatcher — How bounded SQL classification works
• Architecture: PG Wire Protocol — Wire protocol implementation details
• Internals: Wire Corpus — Testing methodology