cockroachdb / cockroachdb/cockroach

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

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

When a view with the same name already exists and `OR REPLACE` is specified, the declarative schema changer should update the existing view's query and columns rather than creating a new descriptor. This should follow the pattern established by `CREATE OR REPLACE FUNCTION` (`replaceFunction` in `create_function.go`).

## Describe the solution you'd like

- Remove the `NotImplementedErrorf` panic for `n.Replace` in `create_view.go`
- Resolve the existing view by name and verify it is indeed a view
- Check that the user has DROP privilege on the existing view
- Validate column compatibility (same prefix of columns — names, types, nullability) per `verifyReplacingViewColumns`
- Update the `ViewQuery` element with the new query and dependencies; remove stale back-references
- Update `createViewChecks` in `process.go` to allow replace
- Add scbuild, scplan, and end-to-end logic tests

## Code References

- [create_view.go:29](https://github.com/cockroachdb/cockroach/blob/7f85419e8fa/pkg/sql/schemachanger/scbuild/internal/scbuildstmt/create_view.go#L29) — `NotImplementedErrorf` panic for `n.Replace`
- [create_view.go:724](https://github.com/cockroachdb/cockroach/blob/7f85419e8fa/pkg/sql/create_view.go#L724) — legacy `replaceViewDesc` implementation
- [create_view.go:886](https://github.com/cockroachdb/cockroach/blob/7f85419e8fa/pkg/sql/create_view.go#L886) — `verifyReplacingViewColumns`
- [create_function.go:228](https://github.com/cockroachdb/cockroach/blob/7f85419e8fa/pkg/sql/schemachanger/scbuild/internal/scbuildstmt/create_function.go#L228) — `replaceFunction` pattern to follow

Epic: CRDB-31466

Jira issue: CRDB-60726

Contributor guide

Open the contributing guide

Research direction

Start in pkg/sql/schemachanger/scbuild/internal/scbuildstmt/create_view.go at the n.Replace panic, and compare the replacement flow with replaceFunction in create_function.go. Read the legacy replaceViewDesc and verifyReplacingViewColumns in pkg/sql/create_view.go, then inspect createViewChecks in process.go. Done means replacement updates the existing view query, columns, dependencies, and privileges, with scbuild, scplan, and end-to-end tests covering the behavior.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
databases
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.