cockroachdb / cockroachdb/cockroach

sql: skip eager evaluation of VIRTUAL computed columns at write time when not needed

Open
#172,798 0 comments 0 reactions 0 assignees View on GitHub
C-enhancement T-sql-foundations
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.