system-table-change

# System Table Schema Change Checklist When adding, removing, or modifying columns or indexes on a system table, multiple files and golden test artifacts must be updated in lockstep. Missing any of these causes test failures that can be confusing to debug. ## 1. Schema Definition Update the table's schema string and descriptor literal in: - **`pkg/sql/catalog/systemschema/system.go`** - The `CREATE TABLE` schema string (e.g., `StatementDiagnosticsRequestsTableSchema`) - The descriptor literal (e.g., `StatementDiagnosticsRequestsTable`): columns, column IDs, family column names/IDs, index store column names/IDs, `NextColumnID` - `NextColumnID` must be max(column IDs) + 1 - When adding/removing columns, update both `ColumnNames` and `ColumnIDs` in the family descriptor - If the column should be stored in a secondary index, update both the schema string's `STORING` clause and the descriptor literal's `StoreColumnNames`/`StoreColumnIDs` ## 2. New Table Registration (skip if modifying existing table) If creating a brand new system table, register it in these additional files: - **`pkg/sql/sem/catconstants/constants.go`** - Add a SystemTableName constant for the new table - **`pkg/sql/catalog/catprivilege/system.go`** - Register privileges (read-write, read-only, etc.) for the table - **`pkg/sql/catalog/bootstrap/metadata.go`** - Add the table descriptor to the bootstrap metadata - **`pkg/backup/system_schema.go`** - Define backup/restore behavior for the table - **`pkg/cli/zip_table_registry.go`** - Decide whether the table should be included in `cockroach debug zip` output and register it if so ## 3. Version Gate - **`pkg/clusterversion/cockroach_versions.go`** - Add a new version constant (e.g., `V26_2_MyChange`) - Add the version mapping in `versionTable` (must use even `Internal` values, incrementing by 2) - **`pkg/sql/catalog/systemschema/system.go`** - Update `SystemDatabaseSchemaBootstrapVersion` to your new version constant ## 4. Migration - **`pkg/upgrade/upgrades/`** - Create a migration file (e.g., `v26_2_my_change.go`). For new tables, this uses `CREATE TABLE`. For existing tables, this uses `ALTER TABLE` / `CREATE INDEX`. - Create a migration test file (e.g., `v26_2_my_change_test.go`) with the old descriptor and validation - Register the migration in `upgrades.go` - Add the old table descriptor constructor to `schema_changes.go` if needed (existing tables only) - Update `helpers_test.go` if adding new helper functions - Run `./dev gen bazel` if adding new files ## 5. Golden Files (must regenerate, not manually edit) These files contain serialized representations of the schema and must be regenerated after schema changes. Update hashes first, then run tests with `--rewrite`. ### Bootstrap test data - **`pkg/sql/catalog/bootstrap/testdata/testdata`** - Contains `system hash=<sha256>` and `tenant hash=<sha256>` followed by KV data - **Update pattern**: Run the test once, grep for "Unexpected hash" in the output to get both new hashes, update both hashes in the file, then re-run with `--rewrite` to regenerate the KV data - Test: `./dev test pkg/sql/catalog/bootstrap -f TestInitialValuesToString -v --rewrite` ### Bootstrap schema golden files - **`pkg/sql/catalog/systemschema_test/testdata/bootstrap_system`** - **`pkg/sql/catalog/systemschema_test/testdata/bootstrap_tenant`** - Contain SQL schema output and JSON descriptor blobs - Test: `./dev test pkg/sql/catalog/systemschema_test -f TestValidateSystemSchemaAfterBootStrap -v --rewrite` ### Logic test golden files - **`pkg/sql/logictest/testdata/logic_test/pg_catalog`** — pg_catalog column metadata - **`pkg/sql/logictest/testdata/logic_test/crdb_internal_catalog`** — internal catalog metadata - Test: `./dev testlogic --files=pg_catalog --rewrite` - Test: `./dev testlogic --files=crdb_internal_catalog --rewrite` ### Initial bootstrap keys and catalog cache test data - **`pkg/sql/tests/testdata/initial_keys`** — initial bootstrap keys - Test: `./dev test pkg/sql/tests -f TestInitialKeys -v --rewrite` - **`pkg/sql/catalog/internal/catkv/testdata/testdata_app`** — catalog cache test data for app tenant - **`pkg/sql/catalog/internal/catkv/testdata/testdata_system`** — catalog cache test data for system tenant - Test: `./dev test pkg/sql/catalog/internal/catkv -v --rewrite` ### Additional golden files for new tables When adding a new system table, these additional golden files may need regeneration: - **`pkg/sql/logictest/testdata/logic_test/information_schema`** — information_schema metadata - Test: `./dev testlogic --files=information_schema --rewrite` - **`pkg/sql/logictest/testdata/logic_test/system`** — system table tests - Test: `./dev testlogic --files=system --rewrite` - **`pkg/cli/testdata/doctor/test_examine_cluster*`** — cluster doctor examination test data - Test: `./dev test pkg/cli -f TestDoctor -v --rewrite` - **`pkg/ccl/spanconfigccl/spanconfigreconcilerccl/testdata/`** — span config reconciler test data - Test: `./dev test pkg/ccl/spanconfigccl/spanconfigreconcilerccl -v --rewrite` ### Previous-release bootstrap data (only if changing an existing release's schema) - **`pkg/sql/catalog/bootstrap/data/{version}_system.keys`** - **`pkg/sql/catalog/bootstrap/data/{version}_system.sha256`** - **`pkg/sql/catalog/bootstrap/data/{version}_tenant.keys`** - **`pkg/sql/catalog/bootstrap/data/{version}_tenant.sha256`** - These are for hardcoded previous release versions, NOT the current `Latest` - Only need updating if you're modifying a released schema (rare) ## 6. Documentation Adding a version constant changes the `version` cluster setting's default value, which is reflected in generated settings docs. Regenerate with: ```bash ./dev gen docs ``` ## 7. Runtime Version Gating If the schema change adds a column/index used at runtime, gate usage on the version: ```go if settings.Version.IsActive(ctx, clusterversion.V26_2_MyChange) { // Use the new column/index } ``` This ensures mixed-version clusters work during rolling upgrades. ## 8. Verification Re-run the tests from Section 5 without `--rewrite` to confirm everything passes. Also run your migration test: ```bash ./dev test pkg/upgrade/upgrades -f TestMyMigration -v ``` ## 9. Rebase Conflicts Golden files frequently conflict during rebases because multiple PRs change system tables concurrently. The resolution pattern: 1. Accept either side of the conflict (content doesn't matter) 2. Regenerate by running the tests with `--rewrite` 3. Update hashes in `bootstrap/testdata/testdata` (run test, grep "Unexpected hash", update, re-run with `--rewrite`) Never try to manually merge golden file content — always regenerate.