PolarDB:temporal_tables

基本的な使い方

Install the extension

CREATE EXTENSION temporal_tables;

システム期間時間テーブルの作成

temporal_tables拡張機能は、共通のトリガー関数を使用して、システム期間の一時テーブルを管理します。

versioning(<system_period_column_name>, <history_table_name>, <adjust>)

この関数は、システム期間の時間テーブルでINSERT、UPDATE、またはDELETE操作を実行する前に起動する必要があります。 次のstableは、関数のパラメータを説明します。

例

1. 基本データを準備し、従業員という名前のテーブルを作成します。

   CREATE TABLE employees
   (
     name text NOT NULL PRIMARY KEY,
     department text,
     salary numeric(20, 2)
   );

2. システム期間列をemployeesテーブルに追加して、テーブルをシステム期間の一時テーブルに変更します。

   ALTER TABLE employees ADD COLUMN sys_period tstzrange NOT NULL;

3. employeesテーブルのアーカイブされた行を含む履歴テーブルを作成します。 テーブルを作成する最も簡単な方法は、LIKE句を使用することです。

   CREATE TABLE employees_history (LIKE employees);

   説明

   履歴テーブルは、元のテーブルと同じ構造を必要としません。 たとえば、元の行の一部の列をアーカイブして他の列を無視したり、履歴テーブルに元のテーブルでは不要な有用な情報が含まれている場合があります。 履歴テーブルは、次の要件を満たす必要があります。

   • 履歴テーブルには、元のテーブルと同じ名前とデータ型を持つシステム期間列が含まれている必要があります。

   • 履歴テーブルと元のテーブルに列が含まれている場合、列のデータ型は2つのテーブルで同じでなければなりません。

4. employeesテーブルにトリガーを作成し、トリガーを履歴テーブルに関連付けます。

   CREATE TRIGGER versioning_trigger
   BEFORE INSERT OR UPDATE OR DELETE ON employees
   FOR EACH ROW EXECUTE PROCEDURE versioning('sys_period',
                                             'employees_history',
                                             true);

データの挿入

通常のテーブルにデータを挿入するのと同様の方法で、システム期間の時間テーブルにデータを挿入できます。

例

1. 次のデータは、2006年8月8日に従業員テーブルに挿入されます。

   INSERT INTO employees (name, department, salary)
   VALUES ('Bernard Marx', 'Hatchery and Conditioning Centre', 10000);
   INSERT INTO employees (name, department, salary)
   VALUES ('Lenina Crowne', 'Hatchery and Conditioning Centre', 7000);
   INSERT INTO employees (name, department, salary)
   VALUES ('Helmholtz Watson', 'College of Emotional Engineering', 18500);

2. 従業員テーブルとその履歴テーブルのデータを照会します。

   • 従業員テーブル:

     SELECT * FROM employees;

     結果：

            name       |            department            |  salary  |         sys_period          
     ------------------+----------------------------------+----------+-----------------------------
      Bernard Marx     | Hatchery and Conditioning Centre | 10000.00 | ["2006-08-08 00:00:00+00",)
      Lenina Crowne    | Hatchery and Conditioning Centre |  7000.00 | ["2006-08-08 00:00:00+00",)
      Helmholtz Watson | College of Emotional Engineering | 18500.00 | ["2006-08-08 00:00:00+00",)
     (3 rows)

   • employees_historyテーブル:

     SELECT * FROM employees_history;

     次の結果が返されます。 履歴テーブルは空です。

      name | department | salary | sys_period 
     ------+------------+--------+------------
     (0 rows)

sys_period列の開始時刻は、行がテーブルに書き込まれる時刻を表します。 トリガーは、現在のトランザクションで最初のデータ変更ステートメントが実行された時刻を示すCURRENT_TIMESTAMP値を使用して値を生成します。

データの更新

システム期間時間テーブルの行の列の値を更新すると、トリガーは古い行のコピーを関連付けられた履歴テーブルに挿入します。 1つのトランザクションが同じ行を複数回更新する場合、1つの履歴行のみが生成されます。

例

1. 以下のデータは、2007年2月27日に従業員テーブルで更新されました。

   UPDATE employees SET salary = 11200 WHERE name = 'Bernard Marx';

2. 従業員テーブルとその履歴テーブルのデータを照会します。

   • 従業員テーブル:

     SELECT * FROM employees;

     結果：

            name       |            department            |  salary  |         sys_period          
     ------------------+----------------------------------+----------+-----------------------------
      Lenina Crowne    | Hatchery and Conditioning Centre |  7000.00 | ["2006-08-08 00:00:00+00",)
      Helmholtz Watson | College of Emotional Engineering | 18500.00 | ["2006-08-08 00:00:00+00",)
      Bernard Marx     | Hatchery and Conditioning Centre | 11200.00 | ["2007-02-27 00:00:00+00",)
     (3 rows)

   • employees_historyテーブル:

     SELECT * FROM employees_history;

     次の結果が返されます。 employees_historyテーブルのレコードが更新されます。

          name     |            department            |  salary  |                     sys_period                      
     --------------+----------------------------------+----------+-----------------------------------------------------
      Bernard Marx | Hatchery and Conditioning Centre | 10000.00 | ["2006-08-08 00:00:00+00","2007-02-27 00:00:00+00")
     (1 row)

更新の競合と時間調整

複数のトランザクションが同じ行を更新すると、更新の競合が発生します。 たとえば、トランザクションAとBは、従業員テーブルのステートメントを同時に実行します。

T1とT2にデータが挿入された後、employeesテーブルには次のデータが含まれます。

employee_historyテーブルは空です。 T4では、トリガーは行のsys_period列の開始値をT1に設定し、次の行を履歴テーブルに挿入する必要があります。

しかし、T2はT1より大きい。 したがって、行を挿入することはできません。 この場合、T4での更新は失敗し、SQL状態22000のエラーメッセージが返されます。 このような失敗を回避するには、トリガーの調整パラメーターをtrueに設定します。 次に、T4において、sys_period列の開始時間をT2に短い時間間隔 (通常1マイクロ秒) を加えたものに設定する。 調整とトランザクションAの完了後、従業員テーブルには次のデータが含まれます。

employees_historyテーブルには、次のデータが含まれます。

データの削除

システム期間の一時テーブルから行を削除すると、その行が関連付けられた履歴テーブルに追加されます。

例

1. 2012年12月24日に従業員テーブルから次のデータが削除されました。

   DELETE FROM employees WHERE name = 'Helmholtz Watson';

2. 従業員テーブルとその履歴テーブルのデータを照会します。

   • 従業員テーブル:

     SELECT * FROM employees;

     結果：

          name      |            department            |  salary  |         sys_period          
     ---------------+----------------------------------+----------+-----------------------------
      Lenina Crowne | Hatchery and Conditioning Centre |  7000.00 | ["2006-08-08 00:00:00+00",)
      Bernard Marx  | Hatchery and Conditioning Centre | 11200.00 | ["2007-02-27 00:00:00+00",)
     (2 rows)

   • employees_historyテーブル:

     SELECT * FROM employees_history;

     次の結果が返されます。 employees_historyテーブルのレコードが更新されます。

            name       |            department            |  salary  |                     sys_period                      
     ------------------+----------------------------------+----------+-----------------------------------------------------
      Bernard Marx     | Hatchery and Conditioning Centre | 10000.00 | ["2006-08-08 00:00:00+00","2007-02-27 00:00:00+00")
      Helmholtz Watson | College of Emotional Engineering | 18500.00 | ["2006-08-08 00:00:00+00","2012-12-24 00:00:00+00")
     (2 rows)

高度な使用法

CURRENT_TIMESTAMP値を使用する代わりに、バージョン管理トリガーのカスタムシステム時間を指定できます。 これは、独自のタイムスタンプを記録した既存のシステムからデータウェアハウスを作成するのに役立ちます。

SELECT set_system_time('1985-08-08 06:42:00+08');

カスタム設定を削除してデフォルトのシステム時間を復元するには、引数としてNULLを使用して関数を呼び出します。

SELECT set_system_time(NULL);

set_system_time関数が後でアボートされるトランザクション内で呼び出されると、すべての変更が元に戻されます。 トランザクションがコミットされた場合、変更はセッションの終了まで持続します。

継承を使用したシステム期間時間テーブルの作成

上記の例では、LIKE句を使用して履歴テーブルを作成しています。 場合によっては、継承を使用してシステム期間の一時テーブルを作成できます。 例：

CREATE TABLE employees_history
(
  name text NOT NULL,
  department text,
  salary numeric(20, 2),
  sys_period tstzrange NOT NULL
);

継承を使用してシステム期間の時間テーブルを作成します。

CREATE TABLE employees (PRIMARY KEY(name)) INHERITS (employees_history);

履歴テーブルの維持

履歴テーブルは常に成長しており、ストレージの消費量が増えています。 次の方法を使用して、履歴テーブルから古いデータを削除できます。

• 履歴テーブルから古いデータを削除する:

  • 履歴テーブルから古いデータを定期的に削除します。

  • パーティション分割を使用し、履歴テーブルから古いパーティションを切り離します。

  • 次のルールを使用して、古い行を剪定します。

    • 特定の年齢より古い行を剪定します。

    • 行の最新のNバージョンのみを保持します。

    • 対応する行がシステム期間時間テーブルから削除されると、行を剪定します。

    • 指定されたビジネスルールを満たす行を剪定します。

• 履歴テーブルに別のテーブルスペースを設定して、テーブルをより安価なストレージに移動することもできます。 詳細については、「コールドデータ階層ストレージ」をご参照ください。

データ監査にシステム期間の一時テーブルを使用する

データ監査には、システム期間の一時テーブルを使用できます。 たとえば、次のトリガーを追加して、現在の行を変更または削除したユーザーを保存できます。

CREATE FUNCTION employees_modify()
RETURNS TRIGGER AS $$
BEGIN
  NEW.user_modified = SESSION_USER;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER employees_modify
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW EXECUTE PROCEDURE employees_modify();

CREATE FUNCTION employees_delete()
RETURNS TRIGGER AS $$
BEGIN
  NEW.user_deleted = SESSION_USER;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER employees_delete
BEFORE INSERT ON employees_history
FOR EACH ROW EXECUTE PROCEDURE employees_delete();

関連ドキュメント

temporal_tablesの詳細については、「temporal_tables」をご参照ください。