⛓️

GCP Cloud Spannerのチェック制約を試す

2022/12/31に公開

Cloud Spanner

tech

はじめに

所謂伝統的なRDBMS(MySQL, PostgreSQL, Oracle etc...)ではCHECKやTRIGGER等によりデータベースレベルでの制約をかけることができます。例えばスケジュール的なデータ構造で開始時刻＞終了時刻なデータを書き込もうとするとエラーを起こすようなものが代表例でしょうか。

これまでのSpannerにはこの手の機能が無かったためアプリケーションレベルでほとんどの制約をかけなければならず複雑で脆弱な実装となりがちでした。万一制約ロジックにバグがあると何のエラーにもならずデータが挿入されてしまい、その場合アプリケーションがどのような振る舞いをするのかは実装次第であり予測が付きません。実装者としては想像するだけで恐ろしい話です。
（実際お仕事中に何度か事故を経験しています……）

しかしいつの間にかSpannerにも制約機能が入っていたので内容と動作を確認してみたいと思います。

使い方

以下のような構文が基本となります。

CONSTRAINT <name> CHECK <condition>

テーブル作成時

CREATE TABLE Users (
    UserID STRING(MAX) NOT NULL,
    Age INT64 NOT NULL,
    CONSTRAINT AgeGTEZero CHECK(Age >= 0)
) PRIMARY KEY (UserID);

追加

ALTER TABLE Users ADD CONSTRAINT AgeGTEZero CHECK(Age >= 0);

削除

ALTER TABLE Users DROP CONSTRAINT AgeGTEZero;

テーブル作成後に制約を追加した場合の挙動

制約の追加前に既に違反するデータが存在する場合はエラーとなります。
DDL発行時に検証の処理が走るため巨大なテーブルに制約を追加する場合相応の時間がかかるでしょう。ただインデックスと違い書き込み処理は無いので恐らく負荷はそこまで発生しないとは思います（未検証）。

INSERT INTO Users (UserID, Age) VALUES ('a', -1);

spanner-cli -p $SPANNER_PROJECT_ID -i $SPANNER_INSTANCE_ID -d $SPANNER_DATABASE_ID -e "ALTER TABLE Users ADD CONSTRAINT AgeGTEZero CHECK(Age >= 0);"
ERROR: rpc error: code = FailedPrecondition desc = Check constraint data validation error: Check constraint `AgeGTEZero` on table `Users` is violated for key: (a).

設定されている制約の一覧

INFORMATION_SCHEMAから存在はわかりますが具体的な制約内容は確認できないようです。常識的な運用管理をしていればマイグレーションクエリーの履歴は保存されているので然程問題は無いでしょう。

SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_NAME = 'AgeGTEZero' AND CONSTRAINT_TYPE = 'CHECK';

$ spanner-cli -p $SPANNER_PROJECT_ID -i $SPANNER_INSTANCE_ID -d $SPANNER_DATABASE_ID -e "SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = 'AgeGTEZero' AND CONSTRAINT_TYPE = 'CHECK';"
CONSTRAINT_NAME TABLE_NAME      CONSTRAINT_TYPE
AgeGTEZero      Users   CHECK

動作確認

例1: 数値制約

ユーザーの年齢がマイナス値となることを防いでみます。

--Schema
CREATE TABLE Users (
    UserID STRING(MAX) NOT NULL,
    Age INT64 NOT NULL,
    CONSTRAINT AgeGTEZero CHECK(Age >= 0)
) PRIMARY KEY (UserID);

--DML
INSERT INTO Users (UserID, Age) VALUES ('ok', 0);
INSERT INTO Users (UserID, Age) VALUES ('ng', -1);

$ spanner-cli -p $SPANNER_PROJECT_ID -i $SPANNER_INSTANCE_ID -d $SPANNER_DATABASE_ID -e "INSERT INTO Users (UserID, Age) VALUES ('ok', 0);"
$ 
$ 
$ spanner-cli -p $SPANNER_PROJECT_ID -i $SPANNER_INSTANCE_ID -d $SPANNER_DATABASE_ID -e "INSERT INTO Users (UserID, Age) VALUES ('ng', -1);"
ERROR: spanner: code = "OutOfRange", desc = "Check constraint `Users`.`AgeGTEZero` is violated for key (ng)"
$ 
$ 
$ spanner-cli -p $SPANNER_PROJECT_ID -i $SPANNER_INSTANCE_ID -d $SPANNER_DATABASE_ID -e "SELECT * FROM Users;"
UserID  Age
ok      0

当然ですが違反が発生するUPDATEも弾かれます。

UPDATE Users SET AGE = -1 WHERE UserID = 'ok';

$ spanner-cli -p $SPANNER_PROJECT_ID -i $SPANNER_INSTANCE_ID -d $SPANNER_DATABASE_ID -e "UPDATE Users SET AGE = -1 WHERE UserID = 'ok';"
ERROR: spanner: code = "OutOfRange", desc = "Check constraint `Users`.`AgeGTEZero` is violated for key (ok)"

例2: 時刻制約

StartTime >= EndTime とならないようにしてみましょう。

--Schema
CREATE TABLE Schedules (
    ScheduleID STRING(MAX) NOT NULL,
    StartTime TIMESTAMP NOT NULL,
    EndTime TIMESTAMP NOT NULL,
    CONSTRAINT StartBeforeEnd CHECK(StartTime < EndTime),
) PRIMARY KEY (ScheduleID);

--DML
INSERT INTO Schedules (
    ScheduleID,
    StartTime,
    Endtime,
) VALUES (
    'ok',
    TIMESTAMP('2022-01-10 00:00:00'),
    TIMESTAMP('2022-01-11 00:00:00'),
);
INSERT INTO Schedules (
    ScheduleID,
    StartTime,
    Endtime,
) VALUES (
    'ng',
    TIMESTAMP('2022-01-10 00:00:00'),
    TIMESTAMP('2022-01-09 00:00:00'),
);

$ spanner-cli -p $SPANNER_PROJECT_ID -i $SPANNER_INSTANCE_ID -d $SPANNER_DATABASE_ID -e "INSERT INTO Schedules (ScheduleID, StartTime, Endtime) VALUES ('ok', TIMESTAMP('2022-01-10 00:00:00'), TIMESTAMP('2022-01-11 00:00:00'));"
$ 
$ 
$ spanner-cli -p $SPANNER_PROJECT_ID -i $SPANNER_INSTANCE_ID -d $SPANNER_DATABASE_ID -e "INSERT INTO Schedules (ScheduleID, StartTime, Endtime) VALUES ('ok', TIMESTAMP('2022-01-10 00:00:00'), TIMESTAMP('2022-01-09 00:00:00'));"
ERROR: spanner: code = "OutOfRange", desc = "Check constraint `Schedules`.`StartBeforeEnd` is violated for key (ok)"
$ 
$ 
$ spanner-cli -p $SPANNER_PROJECT_ID -i $SPANNER_INSTANCE_ID -d $SPANNER_DATABASE_ID -e "SELECT * FROM Schedules"
ScheduleID      StartTime       EndTime
ok      2022-01-10T08:00:00Z    2022-01-11T08:00:00Z

注意点

条件の変更はできない

同名の制約をUPDATE的に置き換えることはできないので一度DROPし再度ADDする必要があります。

`allow_commit_timestamp = true` のカラムには使用できない

CREATE TABLE Schedules (
    ScheduleID STRING(MAX) NOT NULL,
    StartTime TIMESTAMP NOT NULL OPTIONS ( allow_commit_timestamp = true ),
    EndTime TIMESTAMP NOT NULL OPTIONS ( allow_commit_timestamp = true ),
    CONSTRAINT StartBeforeEnd CHECK(StartTime < EndTime),
) PRIMARY KEY (ScheduleID);

ERROR: (gcloud.spanner.databases.create) INVALID_ARGUMENT: Error parsing expression 'StartTime < EndTime' from check constraint 'StartBeforeEnd' in table 'Schedules': Column `EndTime` has option commit_timestamp, which is not supported in check constraint.

非決定性関数は条件に含められない

CURRENT_TIMESTAMP()等の実行時にSpanner側で値が決定するような関数は使用できません。
このため「未来に開始する」というような制約をかけることは難しそうです。

ALTER TABLE Schedules ADD CONSTRAINT InvalidConstraint CHECK(StartTime >= CURRENT_TIMESTAMP());

$ spanner-cli -p $SPANNER_PROJECT_ID -i $SPANNER_INSTANCE_ID -d $SPANNER_DATABASE_ID -e "ALTER TABLE Schedules ADD CONSTRAINT InvalidConstraint CHECK(StartTime >= CURRENT_TIMESTAMP());"
ERROR: rpc error: code = InvalidArgument desc = Error parsing expression 'StartTime >= CURRENT_TIMESTAMP()' from check constraint 'InvalidConstraint' in table 'Schedules': Expression is non-deterministic due to the use of non-determinstic function `CURRENT_TIMESTAMP`. Expression of check constraints must yield the same value for the same dependent column values. Non-deterministic functions inside the expressions are not allowed.

判定結果がNULLの場合は許可される

ドキュメントによるとCHECKの結果がNULLの場合はデータ変更が許可されるようです。

If the expression evaluates to TRUE or NULL, the data change is allowed by the check constraint.

が、明示的にNULLを指定して制約をかけようとした場合はエラーとなります。

ALTER TABLE Users ADD CONSTRAINT AgeNullConstraint CHECK(NULL);

$ spanner-cli -p $SPANNER_PROJECT_ID -i $SPANNER_INSTANCE_ID -d $SPANNER_DATABASE_ID -e "ALTER TABLE Users ADD CONSTRAINT AgeNullConstraint CHECK(NULL);"
ERROR: rpc error: code = FailedPrecondition desc = Check constraint `Users`.`AgeNullConstraint` does not use any non generated column. Expression: 'NULL'. A check constraint expression requires at least one non-generated column as dependency.

サブクエリーは使用できない

サブクエリーは条件に含められないため、例えばある区間に存在できるデータが一つだけという例えば会議室の予約のような制約をかけるすることは恐らくできません。

単一レコード内での制約として利用することが前提のようです。

終わりに

個人的には上記のような「ある区間に存在できるデータが一つだけ」のような複雑な要件を使う機会が多いためもう少し柔軟な機能が欲しかった感はありますが、これだけでも比較的原始的なデータ破壊については防止でき十分有用なので今後積極的に使っていこうと思います。

実用する場合はどこまでをアプリケーションで担保しどこまでをSpanner側で担保するか明確にした上でチームで認識合わせをしないと漏れやコードが複雑化するといった問題が発生しそうなのでそこは注意する必要があるでしょう。