cockroachdb / cockroachdb/cockroach
sql: skip eager evaluation of VIRTUAL computed columns at write time when not needed
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
**Describe the problem**
Follow-up to https://github.com/cockroachdb/cockroach/issues/171976 (skip validation when adding a virtual column).
Today, INSERT/UPDATE/UPSERT always synthesize and evaluate every virtual computed column, even though the value is never stored. This happens in addSynthesizedComputedCols (`pkg/sql/opt/optbuilder/mutation_builder.go`), which treats virtual and stored computed columns identically. PostgreSQL 18 computes virtual generated columns only on read; CRDB should do the same when the value isn't actually needed at write time.
Goal: when the setting is off, skip synthesizing a virtual computed column on write unless it is required. Otherwise leave it to be evaluated lazily on read (the read-time projection already exists in `optbuilder/select.go`).
**Important Note**
Unlike Postgres 18, CRDB allows referencing virtual columns in an index. In such cases, we'll need to eagerly evaluate the column at insert time.
Jira issue: CRDB-66065
Epic CRDB-65516
Contributor guide
Research direction
Start in pkg/sql/opt/optbuilder/mutation_builder.go, especially addSynthesizedComputedCols, and compare it with the read-time projection in optbuilder/select.go. Trace when virtual computed columns are required at write time, including virtual columns referenced by indexes. Done means unnecessary virtual columns are evaluated lazily while required index values remain eagerly evaluated, with the setting-off behavior covered by relevant tests.
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
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 52/100