Plain-language companion: v0.2.0.md

v0.2.0 — TopK, Diamond Consistency & Transactional IVM

Status: Released (2026-03-04).

The 51-item SQL_GAPS_7 correctness plan was completed in v0.1.x. v0.2.0 delivers three major feature additions.

Tier Items Status
0 — Critical F1–F3, F5–F6 ✅ Done in v0.1.1–v0.1.3
1 — Verification F8–F10, F12 ✅ Done in v0.1.2–v0.1.3
2 — Robustness F13, F15–F16 ✅ Done in v0.1.2–v0.1.3
3 — Test coverage F17–F26 (62 E2E tests) ✅ Done in v0.1.2–v0.1.3
4 — Operational hardening F27–F39 ✅ Done in v0.1.3
4 — Upgrade migrations F40 ✅ Done in v0.2.1
5 — Nice-to-have F41–F51 ✅ Done in v0.1.3

TPC-H baseline: 22/22 queries pass deterministic correctness checks across multiple mutation cycles (just test-tpch, SF=0.01).

Queries are derived from the TPC-H Benchmark specification; results are not comparable to published TPC results. TPC Benchmark™ is a trademark of TPC.

ORDER BY / LIMIT / OFFSET — TopK Support ✅

In plain terms: Stream tables can now be defined with ORDER BY ... LIMIT N — for example “keep the top 10 best-selling products”. When the underlying data changes, only the top-N slot is updated incrementally rather than recomputing the entire sorted list from scratch every tick.

ORDER BY ... LIMIT N defining queries are accepted and refreshed correctly. All 9 plan items (TK1–TK9) implemented, including 5 TPC-H queries with ORDER BY restored (Q2, Q3, Q10, Q18, Q21).

Item Description Status
TK1 E2E tests for FETCH FIRST / FETCH NEXT rejection ✅ Done
TK2 OFFSET without ORDER BY warning in subqueries ✅ Done
TK3 detect_topk_pattern() + TopKInfo struct in parser.rs ✅ Done
TK4 Catalog columns: pgt_topk_limit, pgt_topk_order_by ✅ Done
TK5 TopK-aware refresh path (scoped recomputation via MERGE) ✅ Done
TK6 DVM pipeline bypass for TopK tables in api.rs ✅ Done
TK7 E2E + unit tests (e2e_topk_tests.rs, 18 tests) ✅ Done
TK8 Documentation (SQL Reference, FAQ, CHANGELOG) ✅ Done
TK9 TPC-H: restored ORDER BY + LIMIT in Q2, Q3, Q10, Q18, Q21 ✅ Done

See PLAN_ORDER_BY_LIMIT_OFFSET.md.

Diamond Dependency Consistency ✅

In plain terms: A “diamond” is when two stream tables share the same source (A → B, A → C) and a third (D) reads from both B and C. Without special handling, updating A could refresh B before C, leaving D briefly in an inconsistent state where it sees new-B but old-C. This groups B and C into an atomic refresh unit so D always sees them change together in a single step.

Atomic refresh groups eliminate the inconsistency window in diamond DAGs (A→B→D, A→C→D). All 8 plan items (D1–D8) implemented.

Item Description Status
D1 Data structures (Diamond, ConsistencyGroup) in dag.rs ✅ Done
D2 Diamond detection algorithm in dag.rs ✅ Done
D3 Consistency group computation in dag.rs ✅ Done
D4 Catalog columns + GUCs (diamond_consistency, diamond_schedule_policy) ✅ Done
D5 Scheduler wiring with SAVEPOINT loop ✅ Done
D6 Monitoring function pgtrickle.diamond_groups() ✅ Done
D7 E2E test suite (tests/e2e_diamond_tests.rs) ✅ Done
D8 Documentation (SQL_REFERENCE.md, CONFIGURATION.md, ARCHITECTURE.md) ✅ Done

See PLAN_DIAMOND_DEPENDENCY_CONSISTENCY.md.

Transactional IVM — IMMEDIATE Mode ✅

In plain terms: Normally stream tables refresh on a schedule (every N seconds). IMMEDIATE mode updates the stream table inside the same database transaction as the source table change — so by the time your INSERT/UPDATE/ DELETE commits, the stream table is already up to date. Zero lag, at the cost of a slightly slower write.

New IMMEDIATE refresh mode that updates stream tables within the same transaction as base table DML, using statement-level AFTER triggers with transition tables. Phase 1 (core engine) and Phase 3 (extended SQL support) are complete. Phase 2 (pg_ivm compatibility layer) is postponed. Phase 4 (performance optimizations) has partial completion (delta SQL template caching).

Item Description Status
TI1 RefreshMode::Immediate enum, catalog CHECK, API validation ✅ Done
TI2 Statement-level IVM trigger functions with transition tables ✅ Done
TI3 DeltaSource::TransitionTable — Scan operator dual-path ✅ Done
TI4 Delta application (DELETE + INSERT ON CONFLICT) ✅ Done
TI5 Advisory lock-based concurrency (IvmLockMode) ✅ Done
TI6 TRUNCATE handling (full refresh of stream table) ✅ Done
TI7 alter_stream_table mode switching (DIFFERENTIAL↔IMMEDIATE, FULL↔IMMEDIATE) ✅ Done
TI8 Query restriction validation (validate_immediate_mode_support) ✅ Done
TI9 Delta SQL template caching (thread-local IVM_DELTA_CACHE) ✅ Done
TI10 Window functions, LATERAL, scalar subqueries in IMMEDIATE mode ✅ Done
TI11 Cascading IMMEDIATE stream tables (ST_A → ST_B) ✅ Done
TI12 29 E2E tests + 8 unit tests ✅ Done
TI13 Documentation (SQL Reference, Architecture, FAQ, CHANGELOG) ✅ Done

Remaining performance optimizations (ENR-based transition table access, aggregate fast-path, C-level trigger functions, prepared statement reuse) are tracked under post-1.0 A2.

See PLAN_TRANSACTIONAL_IVM.md.

Exit criteria: - [x] ORDER BY ... LIMIT N (TopK) defining queries accepted and refreshed correctly - [x] TPC-H queries Q2, Q3, Q10, Q18, Q21 pass with original LIMIT restored - [x] Diamond dependency consistency (D1–D8) implemented and E2E-tested - [x] IMMEDIATE refresh mode: INSERT/UPDATE/DELETE on base table updates stream table within the same transaction - [x] Window functions, LATERAL, scalar subqueries work in IMMEDIATE mode - [x] Cascading IMMEDIATE stream tables (ST_A → ST_B) propagate correctly - [x] Concurrent transaction tests pass