保存済み生成カラムの概要：書き込み時計算と整合性 - Hologres

生成カラムは、同一テーブル内の他のカラムから計算された値を持つ特殊なカラムです。生成カラムには「STORED 生成カラム」と「仮想生成列」の 2 種類があります。Hologres V3.1 以降では、STORED 生成カラムがサポートされています。このタイプのカラムは、データの書き込みまたは更新時に自動的に計算され、実際のストレージ領域を消費します。仮想生成列は現時点ではサポートされていません。本トピックでは、Hologres における STORED 生成カラムの使用方法について説明します。

シナリオ

必須フィールドに対する計算処理を自動化し、手動での計算ロジック実装を不要にします。
人為的ミスや不具合のあるコードロジックによる不整合を防ぎ、データ整合性を確保します。
クエリ性能を最適化します。高頻度で実行されるクエリにおいて、STORED 生成カラムからの読み取りは、通常のカラムからの読み取りと同等の効率性を発揮します。
共通かつ固定されたデータ変換操作において、SQL の複雑さを低減することで、ビジネスロジックを簡素化します。

ご自身のビジネス要件に応じて生成カラムを適切に活用することで、開発効率が大幅に向上し、データ信頼性も確保されます。

構文

GENERATED ALWAYS AS 句を使用して生成列を宣言し、STORED キーワードを追加してそれが格納生成列であることを指定します。

生成カラムを含むテーブルを作成します。

CREATE TABLE generated_col_t (
    [...,]
    col1 INT,
    col2 INT GENERATED ALWAYS AS (col1 + 1) STORED
);

生成カラムを含む論理パーティションテーブルを作成し、その生成カラムをパーティションキーとして使用します。

CREATE TABLE generated_col_logical_part (
    a TEXT,
    b INT,
    ts TIMESTAMP NOT NULL,
    d TIMESTAMP GENERATED ALWAYS AS (date_trunc('day', ts)) STORED NOT NULL
)
LOGICAL PARTITION BY LIST(d);

注意事項

CREATE TABLE を使用する場合
- 生成カラムの定義には、IMMUTABLE な関数または式のみがサポートされます。CURRENT_DATE や RANDOM のような非 IMMUTABLE 関数は使用できません。
- 生成カラムを定義する際、その式は他の生成カラムを参照できません。また、そのカラムに対して default キーワードを指定することもできません。
- パーティションテーブルを作成する場合、論理パーティションテーブルのパーティションキーとして生成カラムを設定できますが、物理パーティションテーブルのパーティションキーとして生成カラムを設定することはできません。ただし、パーティションテーブル内の任意の通常カラムを生成カラムとして定義することは可能です。
- CREATE FOREIGN TABLE を使用して外部テーブルを作成する際には、生成カラムを定義できません。
- 生成カラムを、プライマリキー、分散キー、セグメントキー、クラスタインデックス、ビットマップ索引、辞書エンコーディングカラムなど、Hologres の各種インデックスとして設定できます。
ALTER TABLE を使用する場合
- 生成カラムに対して ADD 操作はサポートされていません。
- 生成カラムを削除できますが、削除前にそのカラムが参照しているすべてのカラムを保持する必要があります。
- 生成列またはそれが参照する列のデータの型は変更できません。変更するには、REBUILD 機能を使用します。詳細については、「REBUILD」をご参照ください。
- 生成カラムの名前を変更できます。
DML または DQL を使用する場合
- 生成カラムを含むテーブルへのデータ書き込みまたは更新では、生成カラムを省略するか、default キーワードを使用できます。生成カラムに直接値を割り当てることはできません。
- データ更新時、生成カラムまたはその参照先カラムのいずれかが分散キーとして設定されている場合、それらのカラムを更新することはできません。
- テーブルのプライマリキーに生成カラムが含まれる場合、Fixed Plan を使用したデータ更新をサポートするためには、その生成カラムが参照するすべてのカラムもプライマリキーに含める必要があります。
- Fixed Plan を使用した部分カラム更新を行う場合、生成カラムが複数の通常カラムを参照しているとき、その参照先カラムの一部のみを更新することはできません。
- その他の操作（HQE エンジンによる読み取り／書き込み、Fixed Plan による読み取り／書き込み、Copy 操作など）は、生成カラムを含むテーブルをサポートしています。
その他の操作
- CREATE TABLE LIKE を使用する場合、生成カラムを含むソーステーブルからテーブルを作成できます。ただし、生成カラムの属性を保持するには、hg_experimental_enable_create_table_like_properties パラメーターを有効化する必要があります。
- CREATE TABLE AS を使用する場合、生成カラムを含むソーステーブルはサポートされていません。
- 生成列を含むテーブルのテーブルパラメーターを変更するには、REBUILD 構文を使用します。これには、テーブルを別のテーブルグループに移行することも含まれます。詳細については、「REBUILD」をご参照ください。テーブルのテーブルグループを移行する場合、HG_MOVE_TABLE_TO_TABLE_GROUP 構文はサポートされていません。
- 生成カラムを含むテーブルに対して INSERT OVERWRITE 操作を実行するには、Hologres V3.1 でサポートされるネイティブな INSERT OVERWRITE 構文をご利用ください。hg_insert_overwrite 構文は、すでに非推奨となっています。構文の詳細については、「INSERT OVERWRITE」をご参照ください。

例

生成カラムを含むテーブルを作成します。

CREATE TABLE generated_col_t (
    id INT PRIMARY KEY,
    col1 INT,
    col2 INT GENERATED ALWAYS AS (col1 + 1) STORED
);

データをインポートします。
- 生成カラム以外のすべてのカラムにデータをインポートできます。以下の例をご参照ください。
```
INSERT INTO generated_col_t VALUES (1, 1);
INSERT INTO generated_col_t(id, col1) VALUES (2, 2);
```
  SELECT * FROM generated_col_t; クエリの実行結果は以下のとおりです。
```
id	col1	col2
1	1	2
2	2	3
```
- データインポート時に、生成カラムに対して default キーワードを使用できます。以下の例をご参照ください。
```
INSERT INTO generated_col_t VALUES (3, 3, default);
INSERT INTO generated_col_t(id, col1, col2) VALUES (4, 4, default);
```
  SELECT * FROM generated_col_t; クエリの実行結果は以下のとおりです。
```
id	col1	col2
4	4	5
2	2	3
3	3	4
1	1	2
```
- 生成カラムに対して値を明示的に指定してデータをインポートすることはできません。以下の例をご参照ください。
```
INSERT INTO generated_col_t VALUES (5, 5, 6);
INSERT INTO generated_col_t(id, col1, col2) VALUES (6, 6, 7);
```
  以下のような結果が返されます。
データを更新します。
- 生成カラム以外のカラムのデータを更新できます。以下の例をご参照ください。
```
UPDATE generated_col_t SET col1 = 2 WHERE id = 1;
```
  SELECT * FROM generated_col_t; クエリの実行結果は以下のとおりです。
```
id	col1	col2
2	2	3
3	3	4
4	4	5
1	2	3     -- このカラムのデータが変更されました
```
- データ更新時に、生成カラムに対して default キーワードを使用できます。以下の例をご参照ください。
```
UPDATE generated_col_t SET col1 = 3, col2 = default WHERE id = 2;
```
  SELECT * FROM generated_col_t; クエリの実行結果は以下のとおりです。
```
id	col1	col2
3	3	4
2	3	4    -- このカラムのデータが変更されました
4	4	5
1	2	3
```
- 生成カラムに対して値を明示的に指定してデータを更新することはできません。以下の例をご参照ください。
```
UPDATE generated_col_t SET col2 = 4 WHERE id = 3;
```
  以下のような結果が返されます。

特定のパラメーター型に対して関数が IMMUTABLE であるかどうかを確認するには、以下の SQL クエリをご利用ください。たとえば、TO_CHAR 関数は、入力が TIMESTAMP WITH TIME ZONE 型の場合にのみ IMMUTABLE となります。そのため、生成カラム内でこの関数を使用する際には、パラメーター型が一致していることを確認してください。

SELECT n.nspname AS "Schema",
  p.proname AS "Name",
  pg_catalog.pg_get_function_result(p.oid) AS "Result data type",
  pg_catalog.pg_get_function_arguments(p.oid) AS "Argument data types",
 CASE p.prokind
  WHEN 'a' THEN 'agg'
  WHEN 'w' THEN 'window'
  WHEN 'p' THEN 'proc'
  ELSE 'func'
 END AS "Type",
 CASE
  WHEN p.provolatile = 'i' THEN 'immutable'
  WHEN p.provolatile = 's' THEN 'stable'
  WHEN p.provolatile = 'v' THEN 'volatile'
 END AS "Volatility",
 CASE
  WHEN p.proparallel = 'r' THEN 'restricted'
  WHEN p.proparallel = 's' THEN 'safe'
  WHEN p.proparallel = 'u' THEN 'unsafe'
 END AS "Parallel",
 pg_catalog.pg_get_userbyid(p.proowner) AS "Owner",
 CASE WHEN prosecdef THEN 'definer' ELSE 'invoker' END AS "Security",
 pg_catalog.array_to_string(p.proacl, E'\n') AS "Access privileges",
 l.lanname AS "Language",
 p.prosrc AS "Source code",
 pg_catalog.obj_description(p.oid, 'pg_proc') AS "Description"
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
     LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang
-- Target function
WHERE p.proname OPERATOR(pg_catalog.~) '^(TO_CHAR)$' COLLATE pg_catalog.default
  AND pg_catalog.pg_function_is_visible(p.oid)
ORDER BY 1, 2, 4;