PLAN_SQLANCER.md — Differential Semantic Proving via SQLancer

Status: Proposed Date: 2026-03-17 Driver: Open Relates to: Priority 1-1 of PLAN_TEST_EVALS_FINAL_REPORT.md

1. Motivation

pg_trickle is a Differential View Maintenance (DVM) engine that takes standard PostgreSQL view definitions and incrementally maintains them. A fundamental requirement of a DVM engine is that the incrementally maintained stream table must always strictly equal the exact output of the base query executed natively by PostgreSQL in a single snapshot.

Currently, we test this invariant using static, human-written queries (like the TPC-H benchmarks). However, SQL is vastly expansive. Our dvm::parser and delta-join logic are vulnerable to edge cases involving: * Three-valued logic (NULL comparisons). * Nested aggregations, correlated subqueries, and window functions. * Complex casting, JSONB traversal, and array coercions inside the projection layer.

Solution: Integrate SQLancer (Structured Query Language Analyzer). SQLancer is a rigorous fuzzer that dynamically generates valid, insanely complex schemas and queries to test database engines. By turning SQLancer’s oracles on pg_trickle, we can algorithmically search for parser panics, DAG generation failures, and subtle logic bugs in our dataflow operators.

2. Goals & Expected ROI

  • Zero-Panic Guarantee in the Parser: Expose our Rust dvm::parser and DAG builder to thousands of syntactically valid but convoluted SQL patterns. Ensure that if we do not support a feature (like a specific window sub-clause), we return a clean, informative PgTrickleError rather than crashing the Postgres backend.
  • Semantic Equivalence Proving: Use SQLancer’s mathematical test oracles, such as NoREC (Non-Optimizing Reference Engine Construction) or TLP (Ternary Logic Partitioning), to prove that our DVM Rust operators accurately mirror PostgreSQL’s C execution engine edge-cases (especially around outer joins and null handling).
  • Automated Bug Minimization: When a bug is found randomly by SQLancer, it provides exact, reproducible SQL seeds to generate a fast, new regression test for proptest.

3. Implementation Steps

SQLancer is written in Java and executed as a standalone external process against a running database.

Step 1: Establish the Fuzzing Environment

  1. Add a just sqlancer command that spins up a targeted E2E base Postgres container with the pg_trickle extension installed and shared memory properly allocated.
  2. Install SQLancer in an isolated Docker container configured to specifically test the PostgreSQL dialect.

Step 2: The Crash-Test Oracle (Parser Fuzzing)

The easiest immediate win is purely defensive—verifying that creating stream tables doesn’t crash Postgres. 1. Configure SQLancer to run against standard PostgreSQL, creating randomized base tables and views. 2. Write an intermediary Python or Rust script that acts as a proxy: whenever SQLancer attempts to execute a SELECT ... query to verify an oracle, our script intercepts the query, prepends SELECT pgtrickle.create_stream_table('temp_out', $$ ... $$, 'calculated', 'DIFFERENTIAL');, and throws it at the database. 3. Assert: The query must either succeed or fail with a known pg_trickle error code. If the database connection drops (meaning the background worker or SPI panicked and killed the backend), the test fails, capturing the seed.

Step 3: The Equivalence Oracle (Logic Validation)

Once the parser is robust, we must prove the data output is correct. This is effectively NoREC targeted at our operators. 1. Allow SQLancer to generate its chaotic schema and base data. 2. For every query Q generated by SQLancer, execute Q natively on PostgreSQL to get the expected_result. 3. Simultaneously, wrap Q in create_stream_table(), perform a pgtrickle.refresh_stream_table(), and query the underlying stream table to get the pg_trickle_result. 4. Assert: Use multiset symmetric difference checking. expected_result must exactly equal pg_trickle_result. If they differ, our DVM operators (like diff-joins or diff-aggregates) have a logical bug.

Step 4: Stateful DML Fuzzing

SQLancer allows for interleaving random DDL/DML. We can use this to break our DAG update ordering. 1. Create a stream table over the random SQLancer tables. 2. Let SQLancer run thousands of mixed INSERT/UPDATE/DELETE statements against the base tables. 3. Call pgtrickle.refresh(). 4. Compare the stream table against the native materialized view of the same query.

4. Risks & Considerations

  • Feature Parity Noise: SQLancer generates incredibly complex standard Postgres SQL. pg_trickle intentionally does not yet support all of Postgres (e.g., specific lateral join shapes or certain window configurations). The fuzzer will hit a lot of ERROR: feature not supported by pg_trickle. We must configure SQLancer’s Postgres dialect generator (or our parsing proxy) to ignore these expected exits so they do not flag as test failures.
  • Execution Time: Deep fuzzing takes hours. This cannot be part of the standard just test-all CI blocking suite.
  • Infrastructure Layout: SQLancer should run as a nightly scheduled GitHub Action (a “Chaos specific” pipeline) that alerts the team in the morning if a random seed broke the build, rather than running synchronously on PRs.

5. Definition of Done

  • A docker-compose or just workflow exists that spins up Postgres + pg_trickle + SQLancer.
  • A nightly GitHub Action runs SQLancer against main for 1 hour, verifying parser stability (no panics).
  • “Expected” unsupported features are cleanly ignored.