2026-06-01 · claude-opus-4-6

Monthly Summary: Add WHERE Clause Support to REFRESH MATERIALIZED VIEW — May 2026

Overview

This month saw significant technical maturation of the patch to add REFRESH MATERIALIZED VIEW mv WHERE <predicate> syntax to PostgreSQL. The proposal addresses a fundamental scalability limitation: full MV refreshes are O(N) when only O(delta) work is needed. The patch evolved from initial design discussions through concrete bug fixes and resolution of key architectural questions, culminating in a v3 implementation with formal correctness guarantees.

Architecture

The feature touches grammar/parser, rewrite/optimizer, executor/SPI, locking/concurrency, and catalog/index subsystems. It provides two refresh paths:

direct_mod path (FOR UPDATE + single CTE with upsert/anti-join delete): Uses ROW EXCLUSIVE lock, allowing concurrent readers AND writers
match_merge path (diff-based, reusing existing concurrent refresh infrastructure): Uses EXCLUSIVE lock, allowing readers but blocking writers

Both paths require a unique index on the MV for conflict resolution, row identification, and anti-join stale-row detection. Volatile functions are forbidden in the WHERE clause to ensure deterministic predicate evaluation.

Key Developments This Month

Deadlock Prevention

The deadlock concern from overlapping concurrent refreshes was resolved via deterministic lock ordering. The SELECT ... FOR UPDATE step now includes ORDER BY on unique key columns, and the upsert source is similarly ordered — a textbook total-ordering approach to deadlock prevention.

Scope Drift Bug Fix

A consistency bug was identified and fixed: when a row's non-key columns change such that it newly matches the WHERE predicate, but an old row with the same key exists in the MV (outside the predicate's match set), the match_merge path would fail with a unique constraint violation. Both paths now use INSERT ... ON CONFLICT DO UPDATE (or DO NOTHING for no non-key columns), resolving collisions in-place.

CONCURRENTLY Semantics Resolved

The semantic inversion issue was decisively resolved by remapping terminology:

CONCURRENTLY → direct_mod path (ROW EXCLUSIVE, most permissive — readers and writers allowed)
Non-concurrent → match_merge path (EXCLUSIVE, readers allowed, writers blocked)

This restores the historical meaning of CONCURRENTLY as "less restrictive locking."

ON CONFLICT Index Bug Fix

Dharin Shah identified a bug where indnatts (including INCLUDE columns) was used instead of indnkeyatts when building ON CONFLICT target lists, causing failures for indexes with INCLUDE columns.

Formal Correctness Statement

The author provided a detailed correctness argument for the direct_mod path under READ COMMITTED:

FOR UPDATE locks held to transaction end bridge the locking SELECT and CTE
Overlapping refreshes block for the full transaction duration
The CTE executes as a single statement, providing atomic before/after visibility to readers
Key collisions serialized by ON CONFLICT + unique index with last-writer-wins semantics

Performance Results

Benchmarks validate the O(delta) design goal:

Single-row refreshes: ~43-45% of baseline TPS (constant overhead regardless of MV size)
Batch=50 with 4+ clients: matches or exceeds full refresh due to reduced lock contention
Performance is independent of total MV size, comparable to hand-maintained trigger tables

Open Issues

Isolation level coverage: Correctness analysis only covers READ COMMITTED; REPEATABLE READ and SERIALIZABLE behavior unanalyzed
Predicate push-down reliability: Performance depends on optimizer pushing WHERE to base tables; complex views (aggregation, CTEs, window functions) may not benefit
Committer engagement: No committer has responded despite the feature's broad scope
IVM interaction: Relationship to ongoing incremental view maintenance work undiscussed
SQL standard deviation: Non-standard extension with no discussion of alternative syntax approaches

2026-06-01 · claude-opus-4-6

Incremental Update: Security Escalation Bug and Maintenance State Leak

Two significant bugs are reported by a new reviewer, one of which is a privilege escalation vulnerability affecting the core security model of the patch.

1. Privilege Escalation via WHERE Clause Functions (Security Bug)

The Problem: The WHERE predicate in REFRESH MATERIALIZED VIEW mv WHERE <predicate> is executed with the owner's privileges, not the invoker's. This is because the predicate is concatenated into the same SPI query as the view definition (e.g., SELECT * FROM (<view definition>) mv WHERE (<predicate>)), and SPI executes the entire statement under one security context — the owner's.

Since PostgreSQL 16's MAINTAIN privilege allows non-owners to refresh materialized views, a low-privilege user with MAINTAIN + write access to any schema can:

Create a function in their schema that performs arbitrary operations (reads/writes)
Mark it STABLE (bypassing the existing volatile function restriction)
Use it in the WHERE clause of REFRESH, which executes it as the MV owner

This is a classic confused-deputy attack — the MV owner's privileges are exploited to perform actions the invoker couldn't do directly.

Proposed Fix (Adam's response): A two-tier permission model based on function leakproofness:

Leakproof predicate functions only → allow any user with MAINTAIN privilege (status quo for simple predicates with built-in operators)
Non-leakproof functions in predicate → require ownership or superuser

Rationale: A leakproof function executed in owner context cannot leak data or perform side effects, so no escalation occurs. Non-leakproof functions could do arbitrary things, so only the owner (who is already in the same trust domain) may use them.

Assessment: This is a reasonable mitigation but has implications:

The existing restriction on VOLATILE functions is necessary but insufficient — STABLE/IMMUTABLE functions can still have side effects via PL/pgSQL
The "leakproof" annotation is a trust assertion by superusers; it's not automatically verifiable
Built-in operators (=, <, >, etc.) are leakproof, so the common case (WHERE id = 5, WHERE updated_at > now() - interval '1 hour') remains accessible to MAINTAIN holders

2. Maintenance State Leak on Error (Correctness Bug)

The Problem: If an error occurs during refresh_by_direct_modification between the calls to OpenMatViewIncrementalMaintenance() and CloseMatViewIncrementalMaintenance(), the matview_maintenance_depth counter is left incremented for the remainder of the session. This counter controls whether direct DML (INSERT/DELETE/UPDATE) is permitted on materialized views. With the counter stuck above zero, the MV becomes a writable table for the rest of the session.

Demonstration: After a failed partial refresh (e.g., due to a unique constraint violation from base table changes), plain DELETE FROM mv and INSERT INTO mv succeed — completely breaking the MV's read-only invariant.

Fix: Wrap the direct-modification path in PG_TRY/PG_CATCH to ensure CloseMatViewIncrementalMaintenance() is called even on error. The author notes the match_merge path already handles this correctly.

Technical Significance

The privilege escalation bug is architecturally important because it reveals a fundamental tension in the design: the WHERE clause necessarily runs in the same execution context as the view definition query, but its content is controlled by a potentially less-privileged user. This is a novel attack surface that doesn't exist in full REFRESH (where there's no user-supplied expression executed in owner context). The proposed leakproof-based mitigation adds complexity to the permission model but preserves usability for the common case.

[Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW

Latest Update