Description
In the legacy schema changer, most ALTER operations unconditionally create a TYPEDESC SCHEMA CHANGE job for all user-defined types (UDTs) in the table, in order to maintain back references. This has the side effect of bumping the version of each UDT—even when nothing about the type has actually changed.
While this behavior isn't inherently problematic, it becomes an issue in multi-region databases where every table includes a crdb_region column backed by the crdb_internal_region enum. As a result, any ALTER on such tables will repeatedly bump the version of that enum.
This frequent versioning has exposed a caching-related bug in the query engine (see #132105). While this change doesn’t fix the underlying bug, it may help reduce how often it's triggered.
To Reproduce
- Start the demo:
$ cockroach demo --nodes=6 --demo-locality=region=us-east1,zone=us-east1-a:region=us-east1,zone=us-east1-b:region=us-central1,zone=us-central1-a:region=us-central1,zone=us-central1-b:region=us-west1,zone=us-west1-a:region=us-west1,zone=us-west1-b --no-example-database
- Execute the following the
cockroach sqlshell:
> CREATE DATABASE bank PRIMARY REGION "us-east1" REGIONS "us-east1", "us-central1", "us-west1" SURVIVE REGION FAILURE;
CREATE DATABASE
> use bank;
SET
> select crdb_internal.pb_to_json('descriptor', descriptor) -> 'type' -> 'version' from system.descriptor where id = 'crdb_internal_region'::REGTYPE::INT - 100000;
?column?
------------
"1"
(1 row)
> create table t1 (c1 int) locality regional by row;
CREATE TABLE
> select crdb_internal.pb_to_json('descriptor', descriptor) -> 'type' -> 'version' from system.descriptor where id = 'crdb_internal_region'::REGTYPE::INT - 100000;
?column?
------------
"2"
(1 row)
-- add column via declarative schema changer
> alter table t1 add column c2 int;
ALTER TABLE
> select crdb_internal.pb_to_json('descriptor', descriptor) -> 'type' -> 'version' from system.descriptor where id = 'crdb_internal_region'::REGTYPE::INT - 100000;
?column?
------------
"2"
(1 row)
-- add column via legacy schema changer
> begin;
BEGIN
> set local autocommit_before_ddl = false;
SET
> alter table t1 add column c3 int;
ALTER TABLE
> commit;
COMMIT
> select crdb_internal.pb_to_json('descriptor', descriptor) -> 'type' -> 'version' from system.descriptor where id = 'crdb_internal_region'::REGTYPE::INT - 100000;
?column?
------------
"3"
(1 row)
Jira issue: CRDB-49320