> **Plain-language companion:** [v0.15.0.md](v0.15.0.md)

## v0.15.0 — External Test Suites & Integration

**Status: Released (2026-04-03).** All 20 roadmap items complete.

**Goal:** Validate correctness against independent query corpora and ship the
dbt integration as a formal release.

### External Test Suite Integration

> **In plain terms:** pg_trickle's own tests were written by the pg_trickle
> team, which means they can have the same blind spots as the code. This
> adds validation against three independent public benchmarks: PostgreSQL's
> own SQL conformance suite (sqllogictest), the Join Order Benchmark (a
> realistic analytical query workload), and Nexmark (a streaming data
> benchmark). If pg_trickle produces a different answer than PostgreSQL does
> on the same query, these external suites will catch it.

Validate correctness against independent query corpora beyond TPC-H.

> ➡️ **TS1 and TS2 pulled forward to v0.11.0.** Delivering one of TS1 or TS2 is an
> exit criterion for 0.11.0. TS3 (Nexmark) remains in 0.15.0. If TS1/TS2 slip
> from 0.11.0, they land here.

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| ~~TS1~~ | ~~sqllogictest: run PostgreSQL sqllogic suite through pg_trickle DIFFERENTIAL mode~~ ➡️ Pulled to v0.11.0 | 2–3d | [PLAN_TESTING_GAPS.md](plans/testing/PLAN_TESTING_GAPS.md) §J |
| ~~TS2~~ | ~~JOB (Join Order Benchmark): correctness baseline and refresh latency profiling~~ ➡️ Pulled to v0.11.0 | 1–2d | [PLAN_TESTING_GAPS.md](plans/testing/PLAN_TESTING_GAPS.md) §J |
| TS3 | Nexmark streaming benchmark: sustained high-frequency DML correctness | 1–2d | [PLAN_TESTING_GAPS.md](plans/testing/PLAN_TESTING_GAPS.md) §J |

> **External test suites subtotal: ~1–2 days (TS3 only; TS1/TS2 in v0.11.0)** -- ✅ TS3 complete

### Documentation Review

> **In plain terms:** A full documentation review polishes everything so the
> product is ready to be announced to the wider PostgreSQL community.

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| I2 | Complete documentation review & polish | 4--6h | [docs/](docs/) |

> **Documentation subtotal: ✅ Done**

### Bulk Create API (G15-BC)

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| G15-BC | ~~**`bulk_create(definitions JSONB)`** — create multiple stream tables and their CDC triggers in a single transaction. Useful for dbt/CI pipelines that manage many STs programmatically.~~ ✅ Done | ~2–3d | [plans/performance/REPORT_OVERALL_STATUS.md §15](plans/performance/REPORT_OVERALL_STATUS.md) |

> **G15-BC subtotal: ✅ Completed**

### Parser Modularization (G13-PRF) -- ✅ Done

> **In plain terms:** At ~21,000 lines, `parser.rs` was too large to maintain
> safely. Split into 5 sub-modules by concern -- zero behavior change.

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| G13-PRF | ~~**Modularize `src/dvm/parser.rs`.**~~ ✅ Done. Split into `mod.rs`, `types.rs`, `validation.rs`, `rewrites.rs`, `sublinks.rs`. Added `// SAFETY:` comments to all ~750 `unsafe` blocks (~676 newly documented). | ~3–4wk | [plans/performance/REPORT_OVERALL_STATUS.md §13](plans/performance/REPORT_OVERALL_STATUS.md) |

> **G13-PRF subtotal: ✅ Completed**

### Watermark Hold-Back Mode (WM-7) -- ✅ Done

> **In plain terms:** The watermark gating system (shipped in v0.7.0) lets
> ETL producers signal their progress. Hold-back mode adds stuck detection:
> when a watermark is not advanced within a configurable timeout, downstream
> stream tables are paused and operators are notified.

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| WM-7 | **Watermark hold-back mode.** `watermark_holdback_timeout` GUC detects stuck watermarks; pauses downstream gated STs; emits `pgtrickle_alert` NOTIFY with `watermark_stuck` event; auto-resumes with `watermark_resumed` event when watermark advances. | ✅ Done | [PLAN_WATERMARK_GATING.md §4.1](plans/sql/PLAN_WATERMARK_GATING.md) |

> **WM-7 subtotal: ✅ Done**

### Delta Cost Estimation (PH-E1) — ✅ Done

> **In plain terms:** Before executing the MERGE, runs a capped COUNT on the
> delta subquery to estimate output cardinality. If the count exceeds
> `pg_trickle.max_delta_estimate_rows`, emits a NOTICE and falls back to FULL
> refresh to prevent OOM or excessive temp-file spills.

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| PH-E1 | **Delta cost estimation.** Capped `SELECT count(*) FROM (delta LIMIT N+1)` before MERGE execution. `max_delta_estimate_rows` GUC (default: 0 = disabled). Falls back to FULL + NOTICE when exceeded. | — | [PLAN_PERFORMANCE_PART_9.md §Phase E](plans/performance/PLAN_PERFORMANCE_PART_9.md) |

> **PH-E1 subtotal: ✅ Complete**

### dbt Hub Publication (I3) — ✅ Done

> **In plain terms:** `dbt-pgtrickle` is now prepared for dbt Hub publication.
> The `dbt_project.yml` is version-synced (0.15.0), README documents both
> git and Hub install methods, and a submission guide documents the hubcap
> PR process. Actual Hub listing requires creating a standalone `grove/dbt-pgtrickle`
> repository and submitting a PR to `dbt-labs/hubcap`.

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| I3 | Prepared `dbt-pgtrickle` for [dbt Hub](https://hub.getdbt.com/) publication. Version synced to 0.15.0, README updated with Hub install snippet, submission guide written. Hub listing pending separate repo creation + hubcap PR. | 2–4h | [dbt-pgtrickle/](dbt-pgtrickle/) · [docs/integrations/dbt-hub-submission.md](docs/integrations/dbt-hub-submission.md) |

> **I3 subtotal: ~2–4 hours** — ✅ Complete

### Hash-Join Planner Hints (PH-D2) — ✅ Done

> **In plain terms:** Added `pg_trickle.merge_join_strategy` GUC that lets
> operators manually override the join strategy used during MERGE. Values:
> `auto` (default heuristic), `hash_join`, `nested_loop`, `merge_join`.
> The existing delta-size heuristics remain the default (`auto`).

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| PH-D2 | **Hash-join planner hints.** Added `merge_join_strategy` GUC with manual override for join strategy during MERGE. `auto` preserves existing delta-size heuristics; `hash_join`/`nested_loop`/`merge_join` force specific strategies. | 3–5d | [PLAN_PERFORMANCE_PART_9.md §Phase D](plans/performance/PLAN_PERFORMANCE_PART_9.md) |

> **PH-D2 subtotal: ~3–5 days** — ✅ Complete

### Shared-Memory Template Cache Research Spike (G14-SHC-SPIKE)

> **In plain terms:** Every new database connection that triggers a refresh
> pays a 15–50ms cold-start cost to regenerate the MERGE SQL template. With
> PgBouncer in transaction mode, this happens on every refresh cycle. This
> milestone scopes a research spike only: write an RFC, build a prototype,
> measure whether DSM-based caching eliminates the cold-start. Full
> implementation stays in v0.16.0.

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| G14-SHC-SPIKE | **Shared-memory template cache research spike.** Write an RFC for DSM + lwlock-based MERGE SQL template caching. Build a prototype benchmark to validate cold-start elimination. Full implementation deferred to v0.16.0. | 2–3d | [plans/performance/REPORT_OVERALL_STATUS.md §14](plans/performance/REPORT_OVERALL_STATUS.md) |

> **G14-SHC-SPIKE subtotal: ~2–3 days** -- ✅ RFC complete (plans/performance/RFC_SHARED_TEMPLATE_CACHE.md)

### TRUNCATE Capture for Trigger-Mode CDC (TRUNC-1)

> **In plain terms:** WAL-mode CDC detects TRUNCATE on source tables and
> marks downstream stream tables for reinitialization. But trigger-mode CDC
> has no TRUNCATE handler — a `TRUNCATE` silently leaves the stream table
> stale. Adding a DDL event trigger that catches TRUNCATE and flags affected
> STs closes this correctness gap.

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| TRUNC-1 | ~~**TRUNCATE capture for trigger-mode CDC.** Add a DDL event trigger or statement-level trigger that detects TRUNCATE on source tables in trigger CDC mode and marks downstream STs for `needs_reinit`.~~ ✅ Done — CDC TRUNCATE triggers write `action='T'` marker; refresh engine detects and falls back to FULL. | 4–6h | [plans/adrs/PLAN_ADRS.md](plans/adrs/PLAN_ADRS.md) ADR-070 |

> **TRUNC-1 subtotal: ✅ Completed**

### Volatile Function Policy GUC (VOL-1)

> **In plain terms:** Volatile functions (`random()`, `clock_timestamp()`,
> etc.) are correctly rejected at stream table creation time in DIFFERENTIAL
> and IMMEDIATE modes. But there’s no way for users to override this — some
> want volatile functions in FULL mode. Adding a `volatile_function_policy`
> GUC with `reject`/`warn`/`allow` modes gives operators control.

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| VOL-1 | ~~**`pg_trickle.volatile_function_policy` GUC.** Add a GUC with values `reject` (default), `warn`, `allow` to control volatile function handling. `reject` preserves current behavior; `warn` emits WARNING but allows creation; `allow` silently permits (user accepts correctness risk).~~ ✅ Done | 3–5h | [plans/sql/PLAN_NON_DETERMINISM.md](plans/sql/PLAN_NON_DETERMINISM.md) |

> **VOL-1 subtotal: ✅ Completed**

### Spill-Aware Refresh (PH-E2)

> **In plain terms:** After PH-E1 adds pre-flight cost estimation, PH-E2
> adds post-flight monitoring: track `temp_bytes` from `pg_stat_statements`
> after each refresh cycle and auto-adjust if spill is excessive.

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| PH-E2 | ~~**Spill-aware refresh.** Monitor `temp_bytes` from `pg_stat_statements` after each refresh cycle. If spill exceeds threshold 3 consecutive times, automatically increase `per-ST work_mem` override or switch to FULL. Expose in `explain_st()` as `spill_history`.~~ ✅ Done | 1–2 wk | [PLAN_PERFORMANCE_PART_9.md §Phase E](plans/performance/PLAN_PERFORMANCE_PART_9.md) |

> **PH-E2 subtotal: ✅ Completed**

### ORM Integration Guides (E5)

> **In plain terms:** Documentation showing how popular ORMs (SQLAlchemy,
> Django, etc.) interact with stream tables — model definitions, migrations,
> and freshness checks. Documentation-only work.

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| E5 | ORM integrations guide (SQLAlchemy, Django, etc.) | 8–12h | [PLAN_ECO_SYSTEM.md §5](plans/ecosystem/PLAN_ECO_SYSTEM.md) |

> **E5 subtotal: ✅ Done**

### Flyway / Liquibase Migration Support (E4)

> **In plain terms:** Documentation showing how standard migration frameworks
> interact with stream tables — CREATE/ALTER/DROP patterns, handling CDC
> triggers across schema migrations. Documentation-only work.

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| E4 | Flyway / Liquibase migration support | 8–12h | [PLAN_ECO_SYSTEM.md §5](plans/ecosystem/PLAN_ECO_SYSTEM.md) |

> **E4 subtotal: ✅ Done**

### JOIN Key Change + DELETE Correctness Fix (EC-01) — ✅ Done (pre-existing)

> **In plain terms:** The phantom-row-after-DELETE bug was fixed in v0.14.0
> via the R₀ pre-change snapshot strategy. Part 1 of the JOIN delta is split
> into 1a (inserts ⋈ R₁) + 1b (deletes ⋈ R₀), ensuring DELETE deltas always
> find the old join partner. The fix was extended to all join depths via the
> EC-01B-1 per-leaf CTE strategy, and regression tests (EC-01B-2) cover
> TPC-H Q07, Q08, Q09.

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| EC-01 | **R₀ pre-change snapshot for JOIN key change + DELETE.** Part 1 split into 1a (inserts ⋈ R₁) + 1b (deletes ⋈ R₀). Applied to INNER/LEFT/FULL JOIN. Closes G1.1. | — | [GAP_SQL_PHASE_7.md §G1.1](plans/sql/GAP_SQL_PHASE_7.md) |

> **EC-01 subtotal: ✅ Complete (implemented in v0.14.0)**

### Multi-Level ST-on-ST Testing (STST-3)

> **In plain terms:** FIX-STST-DIFF (v0.14.0) fixed 2-level
> stream-table-on-stream-table DIFFERENTIAL refresh. Some 3-level cascade
> tests exist, but systematic coverage for 3+ level chains — including
> mixed refresh modes, concurrent DML at multiple levels, and DELETE/UPDATE
> propagation through deep chains — is missing. This adds a dedicated test
> matrix to prevent regressions as cascade depth increases.

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| STST-3 | **Multi-level ST-on-ST test matrix (3+ levels).** Systematic coverage: 3-level and 4-level chains, INSERT/UPDATE/DELETE propagation, mixed DIFFERENTIAL/FULL modes, concurrent DML at multiple levels, correctness comparison against materialized-view baseline. | 3–5d | [e2e_cascade_regression_tests.rs](tests/e2e_cascade_regression_tests.rs) |

> **STST-3 subtotal: ✅ Done**

### Circular Dependencies + IMMEDIATE Mode (CIRC-IMM)

> **In plain terms:** Circular dependencies are rejected at creation time
> (EC-30), but the interaction between near-circular topologies (e.g.
> diamond dependencies with IMMEDIATE triggers on both sides) and IMMEDIATE
> mode is untested territory. This adds targeted testing and, if needed,
> hardening to ensure IMMEDIATE mode doesn't deadlock or produce incorrect
> results on complex dependency graphs. **Conditional P1 — can slip to
> v0.16.0 if no issues surface during other IMMEDIATE-mode work.**

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| CIRC-IMM | **Circular-dependency + IMMEDIATE mode hardening.** Test: diamond deps with IMMEDIATE triggers, near-circular topologies, lock ordering under concurrent DML. Add deadlock detection / timeout guard if issues found. | 3–5d | [PLAN_EDGE_CASES.md §EC-30](plans/PLAN_EDGE_CASES.md) · [PLAN_CIRCULAR_REFERENCES.md](plans/sql/PLAN_CIRCULAR_REFERENCES.md) |

> **CIRC-IMM subtotal: ✅ Done**

### Cross-Session MERGE Cache Staleness Fix (G8.1)

> **In plain terms:** When session A alters a stream table's defining query,
> session B's cached MERGE SQL template remains stale until B encounters a
> refresh error or reconnects. Adding a catalog version counter that is
> bumped on every ALTER QUERY and checked before each refresh closes this
> race window.

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| G8.1 | ~~**Cross-session MERGE cache invalidation.** Add a `catalog_version` counter to `pgt_stream_tables`, bump on ALTER QUERY / DROP / reinit. Before each refresh, compare cached version to catalog; regenerate template on mismatch.~~ ✅ Done — existing `CACHE_GENERATION` counter + `defining_query_hash` provides cross-session + per-ST invalidation without a schema change. | 4–6h | — |

> **G8.1 subtotal: ✅ Completed**

### `explain_st()` Enhancements (EXPL-ENH) — ✅ Done

> **In plain terms:** Small quality-of-life improvements to the diagnostic
> function: refresh timing statistics, partition source info, and a dependency-graph
> visualization snippet in DOT format.

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| EXPL-ENH | **`explain_st()` enhancements.** Added: (a) refresh timing stats (min/max/avg/latest duration from last 20 refreshes), (b) source partition info for partitioned tables, (c) dependency sub-graph visualization in DOT format. | 4–8h | [PLAN_FEATURE_CLEANUP.md](plans/PLAN_FEATURE_CLEANUP.md) |

> **EXPL-ENH subtotal: ~4–8 hours** — ✅ Complete

### CNPG Operator Hardening (R4)

> **In plain terms:** Kubernetes-native improvements for the CloudNativePG
> integration: adopt K8s 1.33+ native ImageVolume (replacing the init-container
> workaround), add liveness/readiness probe integration for pg_trickle health,
> and test failover behavior with stream tables.

| Item | Description | Effort | Ref |
|------|-------------|--------|-----|
| R4 | **CNPG operator hardening.** Adopt K8s 1.33+ native ImageVolume, add pg_trickle health to CNPG liveness/readiness probes, test primary→replica failover with active stream tables. | 4–6h | [PLAN_CLOUDNATIVEPG.md](plans/ecosystem/PLAN_CLOUDNATIVEPG.md) |

> **R4 subtotal: ~4–6 hours** -- ✅ Complete

> **v0.15.0 total: ~52–90h + ~2–3d bulk create + ~3–5d planner hints + ~2–3d cache spike + ~3–4wk parser + ~1–2wk watermark + ~2–4wk delta cost/spill + ~2–3d EC-01 + ~3–5d ST-on-ST + ~3–5d CIRC-IMM**

**Exit criteria:**
- [x] At least one external test corpus (sqllogictest, JOB, or Nexmark) passes
- [x] Complete documentation review done
- [x] G15-BC: `pgtrickle.bulk_create(definitions JSONB)` creates all STs and CDC triggers atomically; tested with 10+ definitions in a single call
- [x] G13-PRF: `parser.rs` split into 5 sub-modules; zero behavior change; all existing tests pass
- [x] WM-7: Stuck watermarks detected and downstream STs paused; `watermark_stuck` alert emitted; auto-resume on watermark advance
- [x] PH-E1: Delta cost estimation via capped COUNT on delta subquery; `max_delta_estimate_rows` GUC; FULL downgrade + NOTICE when threshold exceeded
- [x] PH-E2: Spill-aware auto-adjustment triggers after 3 consecutive spills; `spill_info` exposed in `explain_st()`
- [x] PH-D2: `merge_join_strategy` GUC with manual override (`auto`/`hash_join`/`nested_loop`/`merge_join`)
- [x] G14-SHC-SPIKE: RFC written; prototype benchmark validates or invalidates DSM-based approach
- [x] I2: Complete documentation review done -- CONFIGURATION.md GUCs documented (40+), SQL_REFERENCE.md gaps filled, FAQ refs fixed
- [x] TRUNC-1: TRUNCATE on trigger-mode CDC source marks downstream STs for reinit; tested end-to-end
- [x] VOL-1: `volatile_function_policy` GUC controls volatile function handling; `reject`/`warn`/`allow` modes tested
- [x] I3: `dbt-pgtrickle` prepared for dbt Hub; submission guide written; Hub listing pending separate repo + hubcap PR
- [x] E4: Flyway / Liquibase integration guide published in `docs/integrations/flyway-liquibase.md`
- [x] E5: ORM integration guides (SQLAlchemy, Django) published in `docs/integrations/orm.md`
- [x] EC-01: R₀ pre-change snapshot ensures DELETE deltas find old join partners; unit + TPC-H regression tests confirm correctness
- [x] STST-3: 3-level and 4-level ST-on-ST chains tested with INSERT/UPDATE/DELETE propagation; mixed modes covered
- [x] CIRC-IMM: Diamond + near-circular IMMEDIATE topologies tested; no deadlocks or incorrect results
- [x] G8.1: Cross-session MERGE cache invalidation via catalog version counter; tested with concurrent ALTER QUERY + refresh
- [x] EXPL-ENH: `explain_st()` shows refresh timing stats, source partition info, and dependency sub-graph (DOT format)
- [x] R4: CNPG operator hardening — ImageVolume, health probes, failover tested
- [x] G13-PRF: `parser.rs` split into 5 sub-modules; all ~750 `unsafe` blocks have `// SAFETY:` comments; zero behavior change; all existing tests pass
- [x] Extension upgrade path tested (`0.14.0 → 0.15.0`)
- [x] `just check-version-sync` passes


---