sql/schemachanger: support altering column type to an enum type #132936

spilchen · 2024-10-18T17:13:35Z

The work to add ALTER COLUMN TYPE in the declarative schema changer currently has a block when the new column type is an enum (see handleGeneralColumnConversion in alter_table_alter_column_type.go). This task is to remove that check and make the operation work.

Here is a simple repro:

SET enable_experimental_alter_column_type_general = true;
CREATE TYPE greeting AS ENUM ('hello', 'howdy', 'hi') ;
create table t1 (c1 string);
insert into t1 values ('hello');
alter table t1 alter column c1 set data type greeting using c1::greeting;

When you remove the check added to handleGeneralColumnConversion, the schema changer hits the following error:

(XX000) internal error: executing declarative schema change PostCommitPhase stage 2 of 15 with 1 BackfillType op (rollback=false) for ALTER TABLE: cannot resolve types in DistSQL by name

Jira issue: CRDB-43360

Epic CRDB-25314

The text was updated successfully, but these errors were encountered:

… ALTER TYPE Previously, attempts to alter a column type using an enum were blocked due to failures during backfill, which would result in the error 'cannot resolve types in DistSQL by name.' This change resolves that issue by ensuring that all type names are resolved when populating the USING expression for the alter Epic: CRDB-25314 Closes cockroachdb#132936 Release note: none

@100117

… ALTER TYPE Previously, attempts to alter a column's type to an enum were blocked due to failures during the backfill process, resulting in the error: "cannot resolve types in DistSQL by name." This change addresses that issue by ensuring all type names are fully resolved when populating the USING expression for the alteration. Additionally, I've corrected an error message for column type conversions involving an enum type without a USING clause. The previous message included a hint with an unhydrated type name, which was confusing for users. The hint used to look like this: ``` HINT: You might need to specify "USING: x::@100117". ``` This fix now provides a more meaningful hint by displaying the correct type name. Epic: CRDB-25314 Closes cockroachdb#132936 Release note: none

132147: sql: use memo metadata to add routines and UDTs to statement bundles r=DrewKimball a=DrewKimball #### opt: replace "udf" with "routine" in the metadata This commit changes the naming of some methods and data structures used in query plan metadata to refer to "routines" instead of "udfs". This clarifies that stored procedures are also valid targets for metadata tracking. Epic: None Release note: None #### sql: use memo metadata to add routines to statement bundles This commit updates the statement bundle logic to take advantage of the information stored in the query plan metadata so that only relevant routines are shown in `schema.sql` for a statement bundle. In addition, stored procedures are now tracked in the metadata in addition to UDFs. This has no impact on query-plan caching, since we currently do not cache plans that invoke stored procedures. Fixes #132142 Fixes #104976 Release note (bug fix): Fixed a bug that prevented the create statement for a routine from being shown in a statement bundle. This happened when the routine was created on a schema other than `public`. The bug has existed since v23.1. #### sql: use memo metadata to add UDTs to statement bundle This commit modifies the statement bundle collection logic to use the query plan metadata to determine which types to display in `schema.sql`. This ensures that only types which are referenced by the query are shown. Informs #104976 Release note: None 133365: roachtest: various minor perturbation cleanups r=kvoli a=andrewbaptist Various minor cleanups to the perturbation tests. These changes don't impact anything functional but make it slightly easier to write new perturbations. 133573: kv: redirect requests to raft leader who holds leader lease r=nvanbenschoten a=nvanbenschoten Informs #132762. This commit updates the lease status logic to redirect requests to the raft leader who holds a leader lease when evaluating a request on a follower. Previously, the follower would return a NotLeaseHolderError, but it would not include the lease in the response, so the client would not be immediately redirected to the leader. Instead, it would continue trying each replica in order, eventually throwing a `sending to all replicas failed` error. Furthermore, since no lease was included, request proxying would never be used. This explains why the `failover/partial/lease-gateway` test was failing to recover. With this change, the test now observes recovery in in 7.650s. | test | lease=epoch (ms) | lease=expiration (ms) | lease=leader (ms) | parity with expiration | |:---------------------------------------------|-----------------:|----------------------:|------------------:|:----------------------:| | failover/partial/lease-gateway | 8,589 | 19,327 | 7,650 | ✔ | **Key _(comparing leader vs. expiration)_**: ✔ = parity ❌ = minor regression ❌❌ = major regression ❌❌❌ = unavailability To further demonstrate that this change works as expected, the commit also ports over two unit test that exercise request proxying to run with leader leases. Without this change, they fail. With it, they pass. ---- Release note: None 133691: sql/schemachanger: Support enum types for DSC impl of ALTER COLUMN … ALTER TYPE r=spilchen a=spilchen Previously, attempts to alter a column's type to an enum were blocked due to failures during the backfill process, resulting in the error: "cannot resolve types in DistSQL by name." This change addresses that issue by ensuring all type names are fully resolved when populating the USING expression for the alteration. Additionally, I've corrected an error message for column type conversions involving an enum type without a USING clause. The previous message included a hint with an unhydrated type name, which was confusing for users. The hint used to look like this: ``` HINT: You might need to specify "USING: x::`@100117".` ``` This fix now provides a more meaningful hint by displaying the correct type name. Epic: CRDB-25314 Closes #132936 Release note: none Co-authored-by: Drew Kimball <drewk@cockroachlabs.com> Co-authored-by: Andrew Baptist <baptist@cockroachlabs.com> Co-authored-by: Nathan VanBenschoten <nvanbenschoten@gmail.com> Co-authored-by: Matt Spilchen <matt.spilchen@cockroachlabs.com>

spilchen added C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) A-schema-changes T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions) labels Oct 18, 2024

spilchen self-assigned this Oct 18, 2024

spilchen mentioned this issue Oct 29, 2024

sql/schemachanger: Support enum types for DSC impl of ALTER COLUMN … ALTER TYPE #133691

Merged

craig bot closed this as completed in 2d6711d Oct 29, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

sql/schemachanger: support altering column type to an enum type #132936

sql/schemachanger: support altering column type to an enum type #132936

spilchen commented Oct 18, 2024 •

edited by exalate-issue-sync bot

Loading

sql/schemachanger: support altering column type to an enum type #132936

sql/schemachanger: support altering column type to an enum type #132936

Comments

spilchen commented Oct 18, 2024 • edited by exalate-issue-sync bot Loading

spilchen commented Oct 18, 2024 •

edited by exalate-issue-sync bot

Loading