PolarDB：temporal_tables（時間表）

使用方法

安裝外掛程式

CREATE EXTENSION temporal_tables;

建立系統時間段時間表

temporal_tables外掛程式使用一個通用的觸發器函數來維護系統時間段時間表的行為：

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

該函數必須在系統時間段時間表上的INSERT、UPDATE或DELETE之前觸發，需要指定以下參數：

樣本

1. 準備基礎資料，建立employees表：

   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. 建立一張包含表中歸檔行的時間表，最簡單的建立方法是使用LIKE語句：

   CREATE TABLE employees_history (LIKE employees);

   說明

   時間表不必與原表結構相同。例如，您希望歸檔原行的某些列，同時忽略其他列，或者時間表可能包含一些在原表中不必要的有用資訊。時間表的必須滿足的兩個要求如下：

   • 時間表必須包含與原表中同名且資料類型相同的系統時間段列。

   • 如果時間表和原表都包含希望歸檔的列，則該列的資料類型在這兩個表中必須相同。

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日插入到employees表中：

   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. 查詢employees表和其時間表中的資料。

   • employees表：

     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. 於2007年2月27日在更新employees表中如下資料：

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

2. 查詢employees表和其時間表中的資料。

   • employees表：

     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表的記錄：

          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同時對錶employees執行語句：

在T1和T2插入之後，系統時間段時間表employees包含以下資料：

時間表employees_history為空白。在T4時，觸發器必須將行的sys_period列的開始設定為T1，並將以下行插入時間表：

然而，T2>T1，因此該行無法插入。在這種情況下，T4時的更新會以SQL STATE 22000失敗。為了避免這種失敗，您可以將觸發器的"adjust"參數設定為true。然後在T4時，sys_period列的開始時間設定為T2加上一個微小的時間間隔（通常為1 微秒）。在這個調整和事務A完成之後，employees表如下所示：

時間表employees_history包含以下資料：

刪除資料

從系統時間段時間表中刪除資料時，觸發器會將行添加到關聯的時間表中。

樣本

1. 於2012年12月24日從employees表中刪除如下資料：

   DELETE FROM employees WHERE name = 'Helmholtz Watson';

2. 查詢employees表和其時間表中的資料。

   • employees表：

     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表的記錄：

            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。