# v0.70.0 — Scheduler, Validator & Security Hardening

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

## What's New

v0.70.0 targets the remaining HIGH- and high-impact MEDIUM-severity findings
from Assessment 13 that relate to the scheduler, the DVM validator, performance
hot paths, and security consistency. The headline items are closing the LATERAL
raw-SQL validation bypass and batching the monitor's per-source SPI fan-out,
both of which have been flagged since the DuckLake and fused-refresh arcs
introduced new code paths.

### COR-002: LATERAL Raw SQL Through Volatility and Support Validators (COR-002)

LATERAL functions (`OpTree::LateralFunction`) and LATERAL subqueries
(`OpTree::LateralSubquery`) store their bodies as raw SQL strings (`func_sql`,
`subquery_sql`). The validation walkers only recurse into the outer child node,
so any volatile expression (`random()`, `clock_timestamp()`, `now()`) or
unsupported SQL construct hidden inside a LATERAL body bypassed both
`pg_trickle.volatile_function_policy = 'reject'` and the DVM support validator.

Fix: `tree_collect_volatility()` and `check_ivm_support_inner()` now parse the
stored LATERAL SQL body through the existing parser and volatility registry
before classifying the node. If the LATERAL body contains function calls that
cannot be classified as immutable, the worst-case volatility is propagated.
Unsupported constructs inside LATERAL bodies now raise `UnsupportedFeature`
rather than passing silently.

### PERF-001: Batched Monitor Buffer Health Checks (PERF-001)

`check_slot_health_and_alert()` previously issued one `SELECT count(*)` SPI
query per tracked change buffer per scheduler loop. At high source counts this
produced a proportional SPI storm.

Fix: the function now builds a single `UNION ALL` query covering all tracked
buffers in one round-trip, then dispatches a warning per buffer that exceeds the
configured alert threshold. When all buffer counts are below threshold, the
function completes with one query regardless of source count. A benchmark with
1,000 and 10,000 tracked sources is added to the scheduler bench suite.

### PERF-002: Batched Fused Eligibility Metadata Loads (PERF-002)

The fused-refresh eligibility loop previously loaded dependency rows, source
metadata, watermarks, and buffer counts per node per source in separate SPI
calls. For wide DAGs this multiplied eligibility cost by nodes × source count.

Fix: dependency rows and source buffer metadata are preloaded once per DAG
tick in two batch queries. `StreamTableMeta` and frontier inputs are cached
across the fused eligibility pass and invalidated only on schema-change
notification.

### PERF-003: History Prune GUC Wired + `start_time` Index (PERF-003)

`pg_trickle.history_prune_interval_seconds` has been registered since v0.55.0
but the scheduler used a hard-coded 24-hour interval. The prune query also
filtered on `start_time` without a leading `start_time` index, causing a
full-table scan.

Fix: the scheduler now reads `pg_trickle_history_prune_interval_seconds()` to
determine the prune cadence. A new migration adds
`CREATE INDEX CONCURRENTLY pgt_refresh_history_start_time_idx ON
pgtrickle.pgt_refresh_history (start_time, refresh_id)`. An integration test
verifies that setting the GUC to 1 second causes pruning within two scheduler
cycles.

### PERF-004: `delta_work_mem_cap_mb` Conservative Default (PERF-004)

`pg_trickle.delta_work_mem_cap_mb` defaulted to `0` (disabled). In a
parallel-refresh deployment, multiple workers could each raise `work_mem`
without a cluster-level guard.

Fix: the default is changed to `256` MB. A preflight check at startup emits a
`WARNING` when planner-aggressive mode is active and the cap is zero. The docs
are updated to recommend setting the cap to `num_parallel_workers * work_mem`.

### SCAL-002: Launcher Database Polling Optimization (SCAL-002)

The launcher scanned all connectable databases and `pg_stat_activity` every 10
seconds regardless of how many databases have pg_trickle installed.

Fix: the launcher maintains a shared-memory set of databases where
`pg_trickle` is installed, populated by DDL event trigger hooks on
`CREATE EXTENSION` and `DROP EXTENSION`. The steady-state poll interval
increases to 60 seconds when no extension install/drop event has been
signalled. The fast 10-second interval is restored automatically when the
event-trigger fires.

### SEC-001: Publication API Name Parser Unified (SEC-001)

The publication module had a private `parse_qualified_name()` that defaulted
unqualified names to `public` and split on the first dot. The common helper
in `src/api/helpers.rs` defaults to `current_schema()` and returns a `Result`.
The inconsistency caused surprising behavior for users in non-public schemas.

Fix: the local `parse_qualified_name()` in `publication.rs` is removed and
replaced with a call to the shared helper. Tests are added for `SET search_path`
scenarios, quoted identifiers, and names containing dots.

### OBS-002: History Prune Failure Visibility (OBS-002)

The history pruner collapsed SPI errors to zero deleted rows with no logging
or metric. Repeated prune failures were completely invisible.

Fix: SPI errors in the prune loop now emit `pgrx::warning!` with the error
message. A new shared-memory counter `pg_trickle_history_prune_errors_total`
is incremented on each failure and exposed via Prometheus. A new
`pgtrickle.history_prune_status()` SQL function returns the last prune
timestamp, rows deleted, and last error string.

### TEST-001: LATERAL Volatile-Function Rejection Tests (TEST-001)

New E2E tests in `e2e_error_tests.rs` and `e2e_lateral_tests.rs`:
- `test_lateral_srf_volatile_rejected_differential`: `LATERAL (SELECT random())`
  under `volatile_function_policy = reject` raises `UnsupportedFeature`.
- `test_lateral_subquery_volatile_rejected_differential`: volatile expression
  inside a `LEFT JOIN LATERAL` subquery is rejected.
- `test_lateral_srf_immutable_allowed_differential`: immutable LATERAL SRF
  passes validation and produces correct results.

### TEST-002: `cache_stats()` E2E Coverage (TEST-002)

New test `test_cache_stats_shape_and_monotonicity`:
1. Calls `pgtrickle.cache_stats()` and asserts all expected columns are present
   with non-negative values.
2. Warms the template cache with a differential refresh.
3. Re-calls `cache_stats()` and asserts `template_hits + template_misses` is
   strictly greater than before.

## Breaking Changes

- `pg_trickle.delta_work_mem_cap_mb` default changes from `0` (disabled) to
  `256`. Deployments where the old "no limit" behaviour was intentional must set
  `pg_trickle.delta_work_mem_cap_mb = 0` explicitly in `postgresql.conf`.
- LATERAL bodies containing volatile functions that previously passed validation
  silently now raise `UnsupportedFeature`. Queries that relied on this
  undocumented behaviour must switch to `volatile_function_policy = 'allow'`
  or restructure the LATERAL to avoid volatile calls.
