CREATE OR REPLACE MATERIALIZED VIEW — Technical Analysis

Core Problem

Materialized views in PostgreSQL occupy an awkward middle ground in the object taxonomy: they carry a query definition like a view, but they are physically backed by a heap relation like a table. This dual nature is reflected in the code paths — CREATE MATERIALIZED VIEW is implemented on top of the CTAS (CREATE TABLE AS) machinery in ExecCreateTableAs/create_ctas_internal, not the view-definition path in DefineViewRelation.

Operationally, this asymmetry bites users whenever a matview's defining query needs to evolve (new columns, changed expressions, new underlying logic). Unlike CREATE OR REPLACE VIEW, which performs in-place swap of the query tree while preserving the pg_class OID and dependent objects, matviews must be dropped and recreated. Any dependent view, matview, or foreign key must be torn down and rebuilt — a real operational hazard that the proponent, Erik Wienhold, had already automated in production with procedures that stash and restore dependent view definitions.

The feature request is therefore to close a completeness gap: make CREATE MATERIALIZED VIEW symmetric with CREATE VIEW with respect to OR REPLACE, so that schema evolution does not cascade through the dependency graph.

Proposed Solution and Implementation Approach

The patch series extends create_ctas_internal rather than introducing a parallel command path. Concretely:

• Column compatibility rules mirror views: new columns may be appended; existing columns may have their SELECT-list expressions rewritten; renaming and type changes are forbidden; column removal is forbidden. The patch reuses checkViewColumns with a new is_matview flag to tailor error messages. Erik flagged uncertainty about this flag — whether to branch error messages or factor out a matview-specific helper.
• Physical properties are replaced too: access method, tablespace, and storage parameters specified in the new command overwrite those of the existing relation. This required wiring into AlterTableInternal / ATPrepSetTableSpace.
• WITH NO DATA semantics: leaves the replaced matview unscannable, identical to a freshly created unpopulated matview.
• New WITH OLD DATA clause (patch 0003): an escape hatch that preserves the existing heap contents during replacement. Motivated by the observation that REFRESH MATERIALIZED VIEW CONCURRENTLY requires a populated matview — so you cannot replace-with-no-data and then concurrently refresh. WITH OLD DATA keeps the table scannable (with NULLs for newly appended columns) so that a subsequent concurrent refresh is possible, avoiding the AccessExclusiveLock cost of a full inline repopulation.
• IF NOT EXISTS deprecation (patch 0002): proposed on the grounds that OR REPLACE subsumes the common use case and that matviews are a PostgreSQL extension (no SQL-standard compatibility constraint).

Key Design Tensions

1. The deprecation of IF NOT EXISTS — rejected

This was the most contentious design choice and was shot down decisively. Aleksander Alekseev, Daniel Gustafsson, and ultimately Tom Lane pushed back. Tom's statement is authoritative: "the standard of proof to remove features is amazingly high." He further argued the semantic point that since matviews are table-like in their physical implementation, and tables have IF NOT EXISTS, matviews should too. Historical precedent supports this: the WITH OIDS removal and the COMMENT ON RULE removal each took >10 years from deprecation to removal. Michael Paquier reinforced the point by noting that COPY still carries two legacy grammars (pre-7.3 and pre-9.0) because grammar back-compat is cheap. Erik dropped patch 0002 in v7.

2. The contract of CREATE OR REPLACE — Tom Lane's critical correction

Tom identified a semantic bug in v6: the implementation was preserving the old object's tablespace/access method/storage parameters when the new command omitted them. This violates the fundamental invariant of CREATE OR REPLACE:

"C.O.R. … will either fail, or produce exactly the same object definition that you would have gotten from plain CREATE with no conflicting object."

In other words, CREATE OR REPLACE must be indistinguishable from DROP + CREATE in terms of the resulting object's properties; the only thing that should survive is the OID (and thus dependencies). v7 corrects this — unspecified properties now revert to defaults, which required the slightly awkward dance of passing an empty-string sentinel through ATPrepSetTableSpace to mean "default tablespace."

This is a subtle but load-bearing correctness point. If unspecified properties silently inherited, then the command's meaning would depend on prior state — exactly the kind of hidden statefulness that makes schema migrations unreproducible.

3. WITH OLD DATA when there is no old data

Tom flagged that silently treating WITH OLD DATA as WITH DATA in the plain-CREATE case is the same class of sin as inheriting tablespace: it makes the command's meaning state-dependent. v7-0003 raises an error in ExecCreateTableAs if WITH OLD DATA is specified without an existing matview to replace.

4. Locking cost

Saïd noted that the replace operation blocks reads. Erik confirmed this is inherent: AccessExclusiveLock is required, matching CREATE OR REPLACE VIEW's behavior. The WITH OLD DATA + concurrent-refresh pattern is the workaround for the data-volume case, but the DDL itself cannot avoid the exclusive lock because column layout and relation metadata are changing.

Architectural Observations

Michael Paquier's review remark cuts to the heart of the implementation: "matviews are much closer to physical relations than views. This is trying to make matviews behave more consistently with views." The patch is therefore a user-facing consistency fix layered on top of a backend that treats matviews as tables. It does not — and cannot without major surgery — make matview replacement as cheap as view replacement, because the underlying heap must be rewritten (or preserved via WITH OLD DATA). This is a grammar/DDL-surface feature, not a storage-engine feature.

The reuse of create_ctas_internal (rather than factoring out a new helper) is pragmatic but creates a branching code path inside what was previously a single-purpose function. Whether this becomes a maintenance hazard depends on how create_ctas_internal evolves.

Outstanding Limitations (from reviewer testing)

Soumya Murali's review identified that the patch cannot:

1. Replace with WITH NO DATA when the new query has fewer columns (rejected by the no-drop-columns rule).
2. Handle dependent matviews gracefully — the dependency is enforced indirectly through column-mismatch errors rather than through the dependency machinery.
3. Drop or retype existing columns.

Items (1) and (3) are intentional and mirror CREATE OR REPLACE VIEW. Item (2) is a genuine UX wart — the error message is confusing because it surfaces a downstream constraint as if it were an upstream one. This likely warrants a better error path that inspects pg_depend and produces a "cannot replace because dependent object X requires column Y" message, analogous to what regular view replacement does.

Status

As of the last message in the thread (May 2026), the patch has been through the Patch Review Workshop (Paul Jungwirth and Yan Haibo), applies and passes tests, and has independent functional verification from Soumya Murali. Tom Lane's correctness objections have been addressed in v7. No committer has yet claimed the patch for commit.