cockroachdb / cockroachdb/cockroach

sql/schemachanger: implement CREATE VIEW in the declarative schema changer

Open
#164,233 0 comments 0 reactions 0 assignees View on GitHub
A-schema-changer-impl C-enhancement T-sql-foundations
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

## Summary

Implement support for regular (non-materialized, non-temporary) CREATE VIEW in the declarative schema changer. DROP VIEW is already implemented in the DSC. This adds CREATE VIEW support so that views can be created through the declarative path.

## Scope

- Regular views only (non-materialized, non-temporary).
- No CREATE OR REPLACE VIEW.
- Enabled by default (`on: true`) in `supportedStatements`.

## Implementation Plan

### New elements
- `ViewQuery` element in `elements.proto` to carry the view query text and forward references (parallels `FunctionBody`).

### New operations
- `CreateViewDescriptor` — creates a minimal table descriptor marked as a view.
- `SetViewQuery` — sets the `ViewQuery` on the view descriptor.
- `UpdateViewBackReferencesInRelations` / `UpdateViewBackReferencesInTypes` / `UpdateViewBackReferencesInRoutines` — manages back-references.

### Build phase
- New `create_view.go` build handler that resolves the target schema, builds a reference provider via the optimizer, validates cross-database references, and emits all needed elements (View, ViewQuery, Namespace, SchemaChild, Column, ColumnName, ColumnType, Owner, UserPrivileges).

### ReferenceProvider extension
- Extend `ReferenceProvider` to handle `*memo.CreateViewExpr` in `reference_provider.go`, extracting the view query text, result columns, and dependencies.

### OpGen rules
- Replace the `NotImplemented` stub in `opgen_view.go` with proper `CreateViewDescriptor` + `MarkDescriptorAsPublic` transitions.
- New `opgen_view_query.go` for `ViewQuery` element transitions.

### Dependency rules
- `View:DESCRIPTOR_ADDED` precedes `ViewQuery:PUBLIC`.
- `ViewQuery:PUBLIC` precedes `View:PUBLIC`.

### Execution layer
- New `view.go` in `scmutationexec` implementing `CreateViewDescriptor`, `SetViewQuery`, and the back-reference update ops.

### Decomposition
- Emit `ViewQuery` element during decomposition of existing views for round-trip correctness.

## Key Files

### New Files
- `pkg/sql/schemachanger/scbuild/internal/scbuildstmt/create_view.go`
- `pkg/sql/schemachanger/scplan/internal/opgen/opgen_view_query.go`
- `pkg/sql/schemachanger/scexec/scmutationexec/view.go`

### Modified Files
- `pkg/sql/schemachanger/scpb/elements.proto`
- `pkg/sql/schemachanger/scop/immediate_mutation.go`
- `pkg/sql/reference_provider.go`
- `pkg/sql/schemachanger/scbuild/internal/scbuildstmt/dependencies.go`
- `pkg/sql/schemachanger/scbuild/builder_state.go`
- `pkg/sql/schemachanger/scbuild/internal/scbuildstmt/process.go`
- `pkg/sql/schemachanger/scplan/internal/opgen/opgen_view.go`
- `pkg/sql/schemachanger/scdecomp/decomp.go`

Epic: CRDB-31466

Jira issue: CRDB-60703

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.