ALTER TABLE ALTER COLUMN TYPE doesn't work for some corner cases

[SergiiShapoval]

Describe the problem

Hi, migrating now several tables in our system to use TEXT type instead of UUID and can't update type. Error response says it is related to an existing index related to a column in ALTER, but all related indexes are already dropped.

To Reproduce

1. Set up CockroachDB cluster 21.1.7 I am using docker-compose for this:

   
   version: "3.7"

volumes: cockroachdb_data:

services:

cockroachdb: image: cockroachdb/cockroach:v21.1.7 command: ["shell", "/cockroach/cockroach-init/init.sh"] ports:

• "26257:26257"
• "26280:8080" volumes:
• cockroachdb_data:/cockroach/cockroach-data
• ./init/cockroachdb:/cockroach/cockroach-init

  in `./init/cockroachdb` I have:

  !/bin/bash

./cockroach start-single-node --insecure & \ sleep 3; \ ./cockroach sql --insecure --execute="CREATE DATABASE IF NOT EXISTS cockroach_test;";

./cockroach sql --insecure --execute="SET CLUSTER SETTING sql.defaults.interleaved_tables.enabled = 'true';"; \ wait

2. Send SQL ... 

CREATE TABLE test ( account_id UUID, id text NOT NULL, status text DEFAULT 'CREATED' NOT NULL, role text DEFAULT '' NOT NULL, deleted bool DEFAULT FALSE NOT NULL, create_time TIMESTAMPTZ DEFAULT now() NOT NULL, last_update_time TIMESTAMPTZ DEFAULT now() NOT NULL, PRIMARY key (account_id, id), UNIQUE (id) ); CREATE INDEX test_status_account_id_create_time_idx ON test (status, account_id, create_time);

ALTER TABLE test DROP CONSTRAINT "primary" CASCADE, ADD CONSTRAINT temp_primary PRIMARY KEY (id);

DROP INDEX IF EXISTS test_status_account_id_create_time_idx CASCADE;

SET enable_experimental_alter_column_type_general=TRUE; ALTER TABLE test ALTER COLUMN account_id TYPE TEXT;

3. See error

[0A000] ERROR: unimplemented: ALTER COLUMN TYPE requiring rewrite of on-disk data is currently not supported for columns that are part of an index Hint: You have attempted to use a feature that is not yet implemented. See: https://go.crdb.dev/issue-v/47636/v21.1

**Expected behavior**
Type updated as all related indexes are dropped.

**Additional data / screenshots**
-

**Environment:**
 - CockroachDB version 21.1.7
 - Server OS: Linux
 - Client app: JDBC
 - 
**Additional context**
Can't do a migration

Jira issue: CRDB-26889
Epic CRDB-25314

[blathers-crl[bot]]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-schema (found keywords: ALTER TABLE)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[SergiiShapoval]

cc @bobvawter @rohany , @RichardJCai, @ajwerner , @chengxiong-ruan , @ecwall , do you know who can help on this?

is there any way to overcome this issue?

[SergiiShapoval]

found in https://www.cockroachlabs.com/blog/alter-column-type/

how to overcome this with

ALTER TABLE test ADD COLUMN account_id_string STRING AS (account_id::string) STORED;
ALTER TABLE test ALTER COLUMN account_id_string DROP STORED;
ALTER TABLE test DROP COLUMN account_id;
ALTER TABLE test RENAME COLUMN account_id_string to account_id ;

still, this looks riskier than ALTER COLUMN TYPE

[Xiang-Gu]

The unique index UNIQUE(id), which uses column account_id, is still there. Try dropping that unique index before altering the column type?

[SergiiShapoval]

The unique index UNIQUE(id), which uses column account_id, is still there. Try dropping that unique index before altering the column type?

@Xiang-Gu , UNIQUE(id) should be using column id, no? id and account_id are separate columns...

[ajwerner]

This now seems to work. @rafiss maybe close it?