# v0.24.0 — Join Correctness and Durability Hardening

> **Full technical details:** [v0.24.0.md-full.md](v0.24.0.md-full.md)

**Status: ✅ Released** | **Scope: Large** (~8–9 weeks)

> The final resolution of the EC-01 join correctness bug, a two-phase
> commit that eliminates crash-recovery data loss, configurable change
> buffer durability, TOAST-aware change detection, and automatic cleanup
> of refresh history.

---

## What problem does this solve?

Despite multiple partial fixes since v0.12.0, the EC-01 phantom row bug
in multi-table LEFT/RIGHT/FULL JOINs was not fully resolved for all
scenarios. Additionally, a crash between the change buffer TRUNCATE and the
frontier update could cause changes to be replayed on restart. For deployments
where the change buffer absolutely must survive a crash, there was no
configuration option.

---

## EC-01: Complete Join Correctness Fix

The root cause of EC-01 was that Part 1b of the join delta algorithm (which
handles the case "left side changed, right side unchanged") was computing its
row identity hash differently from Part 1a (which handles "right side
changed, left side unchanged"). When both parts ran, they should cancel each
other out to zero net change for unchanged rows — but different hashes meant
they did not cancel.

The fix ensures both parts use the same hash algorithm (left-side primary key
only). Cross-cycle phantom rows — rows from previous refresh cycles that
accumulated over time — are cleaned up by the PH-D1 phantom cleanup pass.

TPC-H Q15 is now re-enabled in IMMEDIATE mode. A 5,000-iteration property
test verifies that no phantom accumulation occurs across any combination of
INSERT, UPDATE, and DELETE sequences.

*In plain terms:* LEFT JOINs, RIGHT JOINs, and FULL OUTER JOINs are now
permanently correct in differential mode under all combinations of changes.

---

## Two-Phase Frontier Commit

The "frontier" is the watermark recording how far into the change history
a stream table has been refreshed. Previously, the frontier was updated
*after* the change buffer was truncated. A crash between truncation and
frontier update meant changes would be replayed from the wrong position on
restart.

The two-phase commit writes a *tentative* frontier before truncation and
*finalises* it after the MERGE commits. On startup after a crash, any
unfinished tentative frontier is reconciled. This eliminates the crash
recovery window.

---

## Configurable Change Buffer Durability

The `pg_trickle.change_buffer_durability` GUC provides three options:

- **`unlogged`** (default) — fastest, but change buffer is empty after a
  crash (changes are recaptured from source tables)
- **`logged`** — WAL-logged change buffers, survive crash with no data loss
- **`sync`** — logged plus synchronous commit for maximum durability

Deployments with strict recovery requirements can now choose the right
tradeoff between performance and durability.

---

## TOAST-Aware CDC Hashing

PostgreSQL uses **TOAST** (The Oversized-Attribute Storage Technique) to
store large values — long text, large JSON documents, binary data — out of
line. The row-identity hash used by change capture was not correctly
including TOAST column data, which meant that an UPDATE changing only a
TOAST column (e.g. updating a JSONB column) could be silently missed.

The fix includes the size and a hash of TOAST column values in the row
identity hash, ensuring that all column changes are captured regardless
of their storage mechanism.

*In plain terms:* if your source tables use large JSON, XML, or binary
columns, changes to those columns are now reliably detected.

---

## Refresh History Retention

`pg_trickle.refresh_history_retention_days` (default 7) configures how long
rows are kept in `pgt_refresh_history`. The background worker automatically
prunes rows older than the retention period in small batches during idle
ticks, preventing the history table from growing without bound.

---

## Frozen Stream Table Detector

The `df_frozen_stream_tables` self-monitoring view flags any stream table
that has not refreshed within 5× its configured refresh interval while its
source tables are showing active change capture. This detects stuck or broken
stream tables that are silently falling behind, and emits a `pgtrickle_alert`
notification.

---

## Scope

v0.24.0 closes the last known correctness gap (EC-01 join phantoms), adds a
durability guarantee against crash-recovery data loss (two-phase frontier),
and provides the operational tools (retention, frozen detector) to keep
pg_trickle healthy over months of production operation.