Plain-language companion: v0.2.3.md

v0.2.3 — Non-Determinism, CDC/Mode Gaps & Operational Polish

Status: Released (2026-03-09).

Goal: Close a small set of high-leverage correctness and operational gaps that do not need to wait for the larger v0.3.0 parallel refresh, security, and partitioning work. This milestone tightens refresh-mode behavior, makes CDC transitions easier to observe, and removes one silent correctness hazard in DIFFERENTIAL mode.

Non-Deterministic Function Handling

In plain terms: Functions like random(), gen_random_uuid(), and clock_timestamp() return a different value every time they’re called. In DIFFERENTIAL mode, pg_trickle computes what changed between the old and new result — but if a function changes on every call, the “change” is meaningless and produces phantom rows. This detects such functions at stream-table creation time and rejects them in DIFFERENTIAL mode (they still work fine in FULL or IMMEDIATE mode).

Status: Done. Volatility lookup, OpTree enforcement, E2E coverage, and documentation are complete.

Volatile functions (random(), gen_random_uuid(), clock_timestamp()) break delta computation in DIFFERENTIAL mode — values change on each evaluation, causing phantom changes and corrupted row identity hashes. This is a silent correctness gap.

Item Description Effort Ref
ND1 Volatility lookup via pg_proc.provolatile + recursive Expr scanner Done PLAN_NON_DETERMINISM.md §Part 1
ND2 OpTree volatility walker + enforcement policy (reject volatile in DIFFERENTIAL, warn for stable) Done PLAN_NON_DETERMINISM.md §Part 2
ND3 E2E tests (volatile rejected, stable warned, immutable allowed, nested volatile in WHERE) Done PLAN_NON_DETERMINISM.md §E2E Tests
ND4 Documentation (SQL_REFERENCE.md, DVM_OPERATORS.md) Done PLAN_NON_DETERMINISM.md §Files

Non-determinism subtotal: ~4–6 hours

CDC / Refresh Mode Interaction Gaps ✅

In plain terms: pg_trickle has four CDC modes (trigger, WAL, auto, per-table override) and four refresh modes (FULL, DIFFERENTIAL, IMMEDIATE, AUTO). Not every combination makes sense, and some had silent bugs. This fixed six specific gaps: stale change buffers not being flushed after FULL refreshes (so they got replayed again on the next tick), a missing error for the IMMEDIATE + WAL combination, a new pgt_cdc_status monitoring view, per-table CDC mode overrides, and a guard against refreshing stream tables that haven’t been populated yet.

Six gaps between the four CDC modes and four refresh modes — missing validations, resource leaks, and observability holes. Phased from quick wins (pure Rust) to a larger feature (per-table cdc_mode override).

Item Description Effort Ref
G6 Defensive is_populated + empty-frontier check in execute_differential_refresh() Done PLAN_CDC_MODE_REFRESH_MODE_GAPS.md §G6
G2 Validate IMMEDIATE + cdc_mode='wal' — global-GUC path logs INFO; explicit per-table override is rejected with a clear error Done PLAN_CDC_MODE_REFRESH_MODE_GAPS.md §G2
G3 Advance WAL replication slot after FULL refresh; flush change buffers Done PLAN_CDC_MODE_REFRESH_MODE_GAPS.md §G3
G4 Flush change buffers after AUTO→FULL adaptive fallback (prevents ping-pong) Done PLAN_CDC_MODE_REFRESH_MODE_GAPS.md §G4
G5 pgtrickle.pgt_cdc_status view + NOTIFY on CDC transitions Done PLAN_CDC_MODE_REFRESH_MODE_GAPS.md §G5
G1 Per-table cdc_mode override (SQL API, catalog, dbt, migration) Done PLAN_CDC_MODE_REFRESH_MODE_GAPS.md §G1

CDC/refresh mode gaps subtotal: ✅ Complete

Progress: G6 is now implemented in v0.2.3: the low-level differential executor rejects unpopulated stream tables and missing frontiers before it can scan from 0/0, while the public manual-refresh path continues to fall back to FULL for initialize => false stream tables.

Progress: G1 and G2 are now complete: create_stream_table() and alter_stream_table() accept an optional per-table cdc_mode override, the requested value is stored in pgt_stream_tables.requested_cdc_mode, dbt forwards the setting, and shared-source WAL transition eligibility is now resolved conservatively from all dependent deferred stream tables. The cluster-wide pg_trickle.cdc_mode = 'wal' path still logs INFO for refresh_mode = 'IMMEDIATE', while explicit per-table cdc_mode => 'wal' requests are rejected for IMMEDIATE mode with a clear error.

Progress: G3 and G4 are now implemented in v0.2.3: advance_slot_to_current() in wal_decoder.rs advances WAL slots after each FULL refresh; the shared post_full_refresh_cleanup() helper in refresh.rs advances all WAL/TRANSITIONING slots and flushes change buffers, called from scheduler.rs after every Full/Reinitialize execution and from the adaptive fallback path. This prevents change-buffer ping-pong on bulk-loaded tables.

Progress: G5 is now implemented in v0.2.3: the pgtrickle.pgt_cdc_status convenience view has been added, and a cdc_modes text-array column surfaces per-source CDC modes in pgtrickle.pg_stat_stream_tables. NOTIFY on CDC transitions (TRIGGER → TRANSITIONING → WAL) was already implemented via emit_cdc_transition_notify() in wal_decoder.rs.

Progress: The SQL upgrade path for these CDC and monitoring changes is in place via sql/pg_trickle--0.2.2--0.2.3.sql, which adds requested_cdc_mode, updates the create_stream_table / alter_stream_table signatures, recreates pgtrickle.pg_stat_stream_tables, and adds pgtrickle.pgt_cdc_status for ALTER EXTENSION ... UPDATE users.

Operational

In plain terms: Four housekeeping improvements: clean up prepared statements when the database catalog changes (prevents stale caches after DDL); make WAL slot lag alert thresholds configurable rather than hardcoded; simplify a confusing GUC setting (user_triggers) with a deprecated alias; and add a pg_trickle_dump tool that exports all stream table definitions to a replayable SQL file — useful as a backup before running an upgrade.

Item Description Effort Ref
O1 Prepared statement cleanup on cache invalidation Done GAP_SQL_PHASE_7.md G4.4
O2 Slot lag alerting thresholds configurable (slot_lag_warning_threshold_mb, slot_lag_critical_threshold_mb) Done PLAN_HYBRID_CDC.md §6.2
O3 Simplify pg_trickle.user_triggers GUC (canonical auto / off, deprecated on alias) Done PLAN_FEATURE_CLEANUP.md C5
O4 pg_trickle_dump: SQL export tool for manual backup before upgrade Done PLAN_UPGRADE_MIGRATIONS.md §5.3

Operational subtotal: Done

Progress: All four operational items are now shipped in v0.2.3. Warning-level and critical WAL slot lag thresholds are configurable, prepared __pgt_merge_* statements are cleaned up on shared cache invalidation, pg_trickle.user_triggers is simplified to canonical auto / off semantics with a deprecated on alias, and pg_trickle_dump provides a replayable SQL export for upgrade backups.

v0.2.3 total: ~45–66 hours

Exit criteria: - [x] Volatile functions rejected in DIFFERENTIAL mode; stable functions warned - [x] DIFFERENTIAL on unpopulated ST returns error (G6) - [x] IMMEDIATE + explicit cdc_mode='wal' rejected with clear error (G2) - [x] WAL slot advanced after FULL refresh; change buffers flushed (G3) - [x] Adaptive fallback flushes change buffers; no ping-pong cycles (G4) - [x] pgtrickle.pgt_cdc_status view available; NOTIFY on CDC transitions (G5) - [x] Prepared statement cache cleanup works after invalidation - [x] Per-table cdc_mode override functional in SQL API and dbt adapter (G1) - [x] Extension upgrade path tested (0.2.2 → 0.2.3)