Plain-language companion: v0.2.2.md

v0.2.2 — OFFSET, AUTO Mode, ALTER QUERY, Edge Cases & CDC Hardening

Status: Released (2026-03-08).

This milestone shipped paged TopK OFFSET support, AUTO-by-default refresh selection, ALTER QUERY, the remaining upgrade-tooling work, edge-case and WAL CDC hardening, IMMEDIATE-mode parity fixes, and the outstanding documentation sweep.

ORDER BY + LIMIT + OFFSET (Paged TopK) — Finalization ✅

In plain terms: Extends TopK to support OFFSET — so you can define a stream table as “rows 11–20 of the top-20 best-selling products” (page 2 of a ranked list). Useful for paginated leaderboards, ranked feeds, or any use case where you want a specific window into a sorted result.

Core implementation is complete (parser, catalog, refresh path, docs, 9 E2E tests). The topk_offset catalog column shipped in v0.2.1 and is exercised by the paged TopK feature here.

Item Description Status Ref
OS1 9 OFFSET E2E tests in e2e_topk_tests.rs ✅ Done PLAN_OFFSET_SUPPORT.md §Step 6
OS2 sql/pg_trickle--0.2.1--0.2.2.sql — function signature updates (no schema DDL needed) ✅ Done PLAN_OFFSET_SUPPORT.md §Step 2

AUTO Refresh Mode ✅

In plain terms: Changes the default from “always try differential (incremental) refresh” to a smart automatic selection: use differential when the query supports it, fall back to a full re-scan when it doesn’t. New stream tables also get a calculated schedule interval instead of a hardcoded 1-minute default.

Item Description Status Ref
AM1 RefreshMode::Auto — uses DIFFERENTIAL when supported, falls back to FULL ✅ Done PLAN_REFRESH_MODE_DEFAULT.md
AM2 create_stream_table default changed from 'DIFFERENTIAL' to 'AUTO' ✅ Done —
AM3 create_stream_table schedule default changed from '1m' to 'calculated' ✅ Done —

ALTER QUERY ✅

In plain terms: Lets you change the SQL query of an existing stream table without dropping and recreating it. pg_trickle inspects the old and new queries, determines what type of change was made (added a column, dropped a column, or fundamentally incompatible change), and performs the most minimal migration possible — updating in place where it can, rebuilding only when it must.

Item Description Status Ref
AQ1 alter_stream_table(query => ...) — validate, classify schema change, migrate storage ✅ Done PLAN_ALTER_QUERY.md
AQ2 Schema classification: same, compatible (ADD/DROP COLUMN), incompatible (full rebuild) ✅ Done —
AQ3 ALTER-aware cycle detection (check_for_cycles_alter) ✅ Done —
AQ4 CDC dependency migration (add/remove triggers for changed sources) ✅ Done —
AQ5 SQL Reference & CHANGELOG documentation ✅ Done —

Upgrade Tooling ✅

In plain terms: If the compiled extension library (.so file) is a different version than the SQL objects in the database, the scheduler now warns loudly at startup instead of failing in confusing ways later. Also adds FAQ entries and cross-links for common upgrade questions.

Item Description Status Ref
UG1 Version mismatch check — scheduler warns if .so version ≠ SQL version ✅ Done PLAN_UPGRADE_MIGRATIONS.md §5.2
UG2 FAQ upgrade section — 3 new entries with UPGRADING.md cross-links ✅ Done PLAN_UPGRADE_MIGRATIONS.md §5.4
UG3 CI and local upgrade automation now target 0.2.2 (upgrade-check, upgrade-image defaults, upgrade E2E env) ✅ Done PLAN_UPGRADE_MIGRATIONS.md

IMMEDIATE Mode Parity ✅

In plain terms: Closes two remaining SQL patterns that worked in DIFFERENTIAL mode but not in IMMEDIATE mode. Recursive CTEs (queries that reference themselves to compute e.g. graph reachability or org-chart hierarchies) now work in IMMEDIATE mode with a configurable depth guard. TopK (ORDER BY + LIMIT) queries also get a dedicated fast micro-refresh path in IMMEDIATE mode.

Close the gap between DIFFERENTIAL and IMMEDIATE mode SQL coverage for the two remaining high-risk patterns — recursive CTEs and TopK queries.

Item Description Effort Ref
IM1 Validate recursive CTE semi-naive in IMMEDIATE mode; add stack-depth guard for deeply recursive defining queries 2–3d PLAN_EDGE_CASES_TIVM_IMPL_ORDER.md Stage 6 §5.1 | ✅ Done — check_for_delete_changes handles TransitionTable; generate_change_buffer_from uses NEW transition table in IMMEDIATE mode; ivm_recursive_max_depth GUC (default 100) injects __pgt_depth counter into semi-naive SQL
IM2 TopK in IMMEDIATE mode: statement-level micro-refresh + ivm_topk_max_limit GUC 2–3d PLAN_EDGE_CASES_TIVM_IMPL_ORDER.md Stage 6 §5.2 | ✅ Done — apply_topk_micro_refresh() in ivm.rs; GUC threshold check in api.rs; 10 E2E tests (basic, insert, delete, update, aggregate, offset, multi-DML, threshold rejection, mode switch)

IMMEDIATE parity subtotal: ✅ Complete (IM1 + IM2)

Edge Case Hardening ✅

In plain terms: Three targeted fixes for uncommon-but-real scenarios: a cap on CUBE/ROLLUP combinatorial explosion (which can generate thousands of grouping variants from a single query and crash the database); automatic recovery when CDC gets stuck in a “transitioning” state after a database restart; and polling-based change detection for foreign tables (tables in external databases) that can’t use triggers or WAL.

Self-contained items from Stage 7 of the edge-cases/TIVM implementation plan.

Item Description Effort Ref
EC1 pg_trickle.max_grouping_set_branches GUC — cap CUBE/ROLLUP branch-count explosion 4h PLAN_EDGE_CASES.md EC-02 | ✅ Done — GUC in config.rs (default 64, range 1–65536); parser.rs rejects when branch count exceeds limit; 3 E2E tests (rejection, within-limit, raised limit)
EC2 Post-restart CDC TRANSITIONING health check — detect stuck CDC transitions after crash or restart 1d PLAN_EDGE_CASES.md EC-20 | ✅ Done — check_cdc_transition_health() in scheduler.rs; detects missing replication slots; rolls back to TRIGGER mode
EC3 Foreign table support: polling-based change detection via periodic re-execution 2–3d PLAN_EDGE_CASES.md EC-05 | ✅ Done — pg_trickle.foreign_table_polling GUC; setup_foreign_table_polling() creates snapshot table; poll_foreign_table_changes() uses EXCEPT ALL deltas; 3 E2E tests (rejection, FULL mode, polling correctness)

Edge-case hardening subtotal: ✅ Complete (EC1 + EC2 + EC3)

Documentation Sweep

In plain terms: Filled three documentation gaps: what happens to an in-flight refresh if you run DDL (ALTER TABLE, DROP INDEX) at the same time; limitations when using pg_trickle on standby replicas; and a PgBouncer configuration guide explaining the session-mode requirement and incompatible settings.

Remaining documentation gaps identified in Stage 7 of the gap analysis.

Item Description Effort Status Ref
DS1 DDL-during-refresh behaviour: document safe patterns and races 2h ✅ Done PLAN_EDGE_CASES.md EC-17
DS2 Replication/standby limitations: document in FAQ and Architecture 3h ✅ Done PLAN_EDGE_CASES.md EC-21/22/23
DS3 PgBouncer configuration guide: session-mode requirements and known incompatibilities 2h ✅ Done PLAN_EDGE_CASES.md EC-28

Documentation sweep subtotal: ✅ Complete

WAL CDC Hardening

In plain terms: WAL (Write-Ahead Log) mode tracks changes by reading PostgreSQL’s internal replication stream rather than using row-level triggers — which is more efficient and works across concurrent sessions. This work added a complete E2E test suite for WAL mode, hardened the automatic fallback from WAL to trigger mode when WAL isn’t available, and promoted cdc_mode = 'auto' (try WAL first, fall back to triggers) as the default.

WAL decoder F2–F3 fixes (keyless pk_hash, old_* columns for UPDATE) landed in v0.1.3.

Item Description Effort Status Ref
W1 WAL mode E2E test suite (parallel to trigger suite) 8–12h ✅ Done PLAN_HYBRID_CDC.md
W2 WAL→trigger automatic fallback hardening 4–6h ✅ Done PLAN_HYBRID_CDC.md
W3 Promote pg_trickle.cdc_mode = 'auto' to default ~1h ✅ Done PLAN_HYBRID_CDC.md

WAL CDC subtotal: ~13–19 hours

Exit criteria: - [x] ORDER BY + LIMIT + OFFSET defining queries accepted, refreshed, and E2E-tested - [x] sql/pg_trickle--0.2.1--0.2.2.sql exists (column pre-provisioned in 0.2.1; function signature updates) - [x] Upgrade completeness check passes for 0.2.1→0.2.2 - [x] CI and local upgrade-E2E defaults target 0.2.2 - [x] Version check fires at scheduler startup if .so/SQL versions diverge - [x] IMMEDIATE mode: recursive CTE semi-naive validated; ivm_recursive_max_depth depth guard added - [x] IMMEDIATE mode: TopK micro-refresh fully tested end-to-end (10 E2E tests) - [x] max_grouping_set_branches GUC guards CUBE/ROLLUP explosion (3 E2E tests) - [x] Post-restart CDC TRANSITIONING health check in place - [x] Foreign table polling-based CDC implemented (3 E2E tests) - [x] DDL-during-refresh and standby/replication limitations documented - [x] WAL CDC mode passes full E2E suite - [x] E2E tests pass (just build-e2e-image && just test-e2e)