cockroachdb / cockroachdb/cockroach

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

Open
#164,273 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

Materialized views are table-like descriptors that store query results as physical data. This is the most complex CREATE VIEW variant to implement in the declarative schema changer.

## Describe the solution you'd like

- Remove the `NotImplementedErrorf` panic for `n.Materialized` in `create_view.go`
- Set `IsMaterialized: true` on the View element
- Handle table-like aspects: primary key allocation via `AllocateIDs`, column IDs
- Descriptor state: created in `ADD` state, transitions to `PUBLIC` after backfill
- `WITH NO DATA`: set `RefreshViewRequired = true`; `WITH DATA`: backfill at creation
- `AS OF SYSTEM TIME` support for creation timestamp
- Multi-region: apply `GLOBAL` locality and zone config
- Update `createViewChecks` in `process.go`
- Add scbuild, scplan, and end-to-end logic tests

## Code References

- [create_view.go:23](https://github.com/cockroachdb/cockroach/blob/7f85419e8fa/pkg/sql/schemachanger/scbuild/internal/scbuildstmt/create_view.go#L23) — `NotImplementedErrorf` panic for materialized views
- [create_view.go:269-291](https://github.com/cockroachdb/cockroach/blob/7f85419e8fa/pkg/sql/create_view.go#L269-L291) — legacy materialized view logic
- [elements.proto](https://github.com/cockroachdb/cockroach/blob/7f85419e8fa/pkg/sql/schemachanger/scpb/elements.proto) — View element has `IsMaterialized` field

Epic: CRDB-31466

Jira issue: CRDB-60728

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.