Zenn
株式会社コンサイズPublicationへの投稿
2️⃣

CockroachDBでデータ型の変更(ALTER COLUMN TYPE)ができないときに読む記事

miyamo2
2024/12/15に公開
SQL
Database
CockroachDB
NewSQL
tech

⚠WARNING⚠

TL;DR

  • ALTER COLUMN TYPEはPreviewのためSESSION変数を書き換える必要がある
    • SET enable_experimental_alter_column_type_general = true;
  • ALTER COLUMN TYPEは他のALTER TABLEサブフォームと一緒に実行できない
    • e.g. ALTER TABLE t ALTER COLUMN x TYPE STRING, ALTER COLUMN x SET NOT NULL;
  • ALTER COLUMN TYPEは明示的なトランザクション内で実行することもできない

経緯

timestamp型で定義したカラムをtimestampzに変更するためのSQLを書いていました

ALTER TABLE foo
    ALTER COLUMN created_at SET DATA TYPE TIMESTAMP WITH TIME ZONE USING created_at AT TIME ZONE 'UTC',
    ALTER COLUMN updated_at SET DATA TYPE TIMESTAMP WITH TIME ZONE USING updated_at AT TIME ZONE 'UTC';

CockroachDB CloudのSQL Shellで試し打ちをしてみたところ
以下の怒られが発生してしまいました

[XCEXF] ERROR: ALTER COLUMN TYPE from timestampt to timestamptz is only supported experimentally
See: https://go.crdb.dev/issue-v/49329/v24.2
--
you can enable alter column type general support by running `SET enable_experimental_alter_column_type_general = true`

https://github.com/cockroachdb/cockroach/issues/49329?version=v24.2

原因1: SESSION変数の設定

https://www.cockroachlabs.com/docs/v24.3/alter-table#alter-column-data-types

Support for altering column data types is in preview, with certain limitations. To enable column type altering, set the enable_experimental_alter_column_type_general session variable to true.

データ型の変更はまだPreview機能でいくつかの制限付き
データ型を変更したい場合はenable_experimental_alter_column_type_generalをtrueにしてね

とのこと

実際に設定されている値を見てみると無効になっていることがわかります

SHOW SESSION enable_experimental_alter_column_type_general;
enable_experimental_alter_column_type_general
1 off

早速下記のクエリで機能を有効化していきます

SET enable_experimental_alter_column_type_general = true;

Serverless版を使っている人向けに書くと
SQL Shellで実行するとエラーになるので
cockroach sqlやGUIクライアントなどで実行する必要があります

私はGoLandのDatabase Tools and SQLプラグインを使いました
SQL ShellでSETを実行してエラーが出ている

原因2: もう一つ以上のサブフォームと組み合わせられない

先ほどSESSION変数を書き換えたので気を取り直して再実行していきます

ALTER TABLE foo
    ALTER COLUMN created_at SET DATA TYPE TIMESTAMP WITH TIME ZONE USING created_at AT TIME ZONE 'UTC',
    ALTER COLUMN updated_at SET DATA TYPE TIMESTAMP WITH TIME ZONE USING updated_at AT TIME ZONE 'UTC';

すると今度は別の怒られが

[0A000] ERROR: unimplemented: ALTER COLUMN TYPE cannot be used in combination with other ALTER TABLE commands
You have attempted to use a feature that is not yet implemented.
See: https://go.crdb.dev/issue-v/49351/v24.2

https://go.crdb.dev/issue-v/49351/v24.2

Similarly, ALTER COLUMN TYPE cannot be used in combination with other ALTER TABLE statements since this runs the statements inside a transaction.

Example:
ALTER TABLE t ALTER COLUMN x TYPE STRING, ALTER COLUMN x SET NOT NULL;

データ型を変更する場合はそれ単独でALTER TABLEを実行する必要があるとのこと

created_at、updated_atのALTER COLUMN TYPEを個別で変更するように修正して実行

ALTER TABLE foo
    ALTER COLUMN created_at SET DATA TYPE TIMESTAMP WITH TIME ZONE USING created_at AT TIME ZONE 'UTC';

ALTER TABLE foo
    ALTER COLUMN updated_at SET DATA TYPE TIMESTAMP WITH TIME ZONE USING updated_at AT TIME ZONE 'UTC';

無事通りました

ALTER COLUMN TYPE changes are finalized asynchronously; further schema changes on this table may be restricted until the job completes; some writes to the altered column may be rejected until the schema change is finalized
completed in 575 ms

私はgooseでマイグレーションをしているので
このSQLをgooseのマイグレーションファイルに落とし込んでいきます

原因3: トランザクション内で実行できない

SET enable_experimental_alter_column_type_general = true;を
ベタ書きしている是非はさておき

gooseのマイグレーションファイルにこれまでの歩みを反映させました

-- +goose Up
SET enable_experimental_alter_column_type_general = true;

ALTER TABLE foo
    ALTER COLUMN created_at SET DATA TYPE TIMESTAMP WITH TIME ZONE USING created_at AT TIME ZONE 'UTC';

ALTER TABLE foo
    ALTER COLUMN updated_at SET DATA TYPE TIMESTAMP WITH TIME ZONE USING updated_at AT TIME ZONE 'UTC';

-- +goose Down
SET enable_experimental_alter_column_type_general = true;

ALTER TABLE foo
    ALTER COLUMN created_at SET DATA TYPE TIMESTAMP WITHOUT TIME ZONE;

ALTER TABLE foo
    ALTER COLUMN updated_at SET DATA TYPE TIMESTAMP WITHOUT TIME ZONE;

goose upで実行してみるとまたしても別の怒られが発生
トランザクション内では実行できないそうです

ERROR: unimplemented: ALTER COLUMN TYPE is not supported inside a transaction (SQLSTATE 0A000)

原因2に戻ってみると
Issueタイトルにも「sql: alter column type in transaction not supported」とあります

ALTER COLUMN TYPE cannot be used in combination with other ALTER TABLE statements since this runs the statements inside a transaction.

原因2の根本的な原因は2つ以上のサブフォームを含むALTER TABLEで内部的にトランザクションを使っていることに起因しているっぽいです

-- +goose NO TRANSACTIONディレクティブを追記して再実行

-- +goose NO TRANSACTION
-- +goose Up
SET enable_experimental_alter_column_type_general = true;

ALTER TABLE foo
    ALTER COLUMN created_at SET DATA TYPE TIMESTAMP WITH TIME ZONE USING created_at AT TIME ZONE 'UTC';

ALTER TABLE foo
    ALTER COLUMN updated_at SET DATA TYPE TIMESTAMP WITH TIME ZONE USING updated_at AT TIME ZONE 'UTC';

-- +goose Down
SET enable_experimental_alter_column_type_general = true;

ALTER TABLE foo
    ALTER COLUMN created_at SET DATA TYPE TIMESTAMP WITHOUT TIME ZONE;

ALTER TABLE foo
    ALTER COLUMN updated_at SET DATA TYPE TIMESTAMP WITHOUT TIME ZONE;

今度こそ完遂しました

OK   19700101000001_alter_timestamp_with_tz.sql (2.61s)
goose: successfully migrated database to version: 19700101000001

おわりに

実はALTER COLUMN TYPEに対する制限は下記のページに一覧化されています
また、"該当カラムにCHECK制約が掛かっている場合" etc
私が今回遭遇したエラー以外にも制限もあるため詳しくはこちらをご参照ください
https://www.cockroachlabs.com/docs/stable/known-limitations#alter-column-limitations

株式会社コンサイズ
株式会社コンサイズPublication

Discussion