觸發器 • PostgreSQL 的藝術

當「每幾分鐘 refresh 一次」的快取政策不夠用時，常見做法是改採事件驅動處理。包括 PostgreSQL 在內的多數 SQL 系統都提供這種機制：觸發器（trigger）。

Trigger 可以註冊一個程序（procedure），在指定事件發生時、於指定時機執行。時機可為 before、after 或 instead of；事件可為 insert、update、delete 或 truncate。完整細節見 PostgreSQL 文件的 CREATE TRIGGER 與 PL/pgSQL trigger procedures 兩頁。
PostgreSQL 的 trigger 多以 PL/pgSQL（SQL 程序語言）撰寫；預設編譯也內建 PL/Tcl、PL/Perl、PL/Python 與 C 語言函式。核心之外還有 PL/Java、PL/v8（V8 引擎驅動的 JavaScript）、PL/XSLT 等擴充，更多語言見 PostgreSQL wiki 的 PL Matrix。

Trigger 不能用純 SQL 撰寫，必須寫成儲存程序（stored procedure）才能使用 PostgreSQL 的觸發器能力。

交易式事件驅動處理#

Trigger 會在支援的事件每次提交時呼叫註冊的程序，且程序的執行永遠是交易的一部分：程序在執行期失敗，整個交易就中止。

經典範例：每當 tweet.activity 有相關 insert 時，更新每日的 rts/favs 計數：

begin;

create table twcache.daily_counters
 (
   day     date not null primary key,
   rts     bigint,
   de_rts  bigint,
   favs    bigint,
   de_favs bigint
 );

create or replace function twcache.tg_update_daily_counters ()
 returns trigger
 language plpgsql
as $$
declare
begin
      update twcache.daily_counters
         set rts = case when NEW.action = 'rt'
                         then rts + 1
                         else rts
                    end,
             de_rts = case when NEW.action = 'de-rt'
                         then de_rts + 1
                         else de_rts
                    end,
             favs = case when NEW.action = 'fav'
                          then favs + 1
                          else favs
                     end,
             de_favs = case when NEW.action = 'de-fav'
                          then de_favs + 1
                          else de_favs
                      end
        where daily_counters.day = current_date;

  if NOT FOUND
  then
       insert into twcache.daily_counters(day, rts, de_rts, favs, de_favs)
            select current_date,
                   case when NEW.action = 'rt'
                         then 1 else 0
                     end,
                   case when NEW.action = 'de-rt'
                         then 1 else 0
                    end,
                   case when NEW.action = 'fav'
                         then 1 else 0
                    end,
                   case when NEW.action = 'de-fav'
                         then 1 else 0
                    end;
  end if;

  RETURN NULL;
end;
$$;

CREATE TRIGGER update_daily_counters
         AFTER INSERT
            ON tweet.activity
      FOR EACH ROW
       EXECUTE PROCEDURE twcache.tg_update_daily_counters();

insert into tweet.activity(messageid, action)
     values (7, 'rt'),
            (7, 'fav'),
            (7, 'de-fav'),
            (8, 'rt'),
            (8, 'rt'),
            (8, 'rt'),
            (8, 'de-rt'),
            (8, 'rt');

select day, rts, de_rts, favs, de_favs
  from twcache.daily_counters;

rollback;

執行結果：

BEGIN
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
INSERT 0 8
    day     │ rts │ de_rts │ favs │ de_favs
════════════╪═════╪════════╪══════╪═════════
 2017-09-21 │   5 │      1 │    1 │       1
(1 row)

ROLLBACK

腳本刻意以 ROLLBACK 結尾——因為我們並不真的要裝這個 trigger，同時這也是在 psql 互動式開發的好方法：把整段包在交易裡反覆修正 bug 與語法錯誤直到全部通過。否則腳本一部分成功、一部分失敗，你只能複製貼上到處補救，而且因為前幾次「部分成功」已經改變了條件，你永遠無法確定整份腳本能從頭乾淨地跑完。

問題在於：每筆 tweet.activity 的 insert 都被這個 trigger 轉成對單一資料列的 update，而且一整天都是同一列。

這一個 trigger 就徹底扼殺了我們模型的並行與擴展性——但因為這種 trigger 太好寫，實務上到處都看得到。

Trigger 與計數器反模式#

這個 trigger 的行為本身也寫錯了：它的 insert-or-update（即 upsert）寫法為並行問題留了門。想想新的一天開始時：

順利劇本：當天第一個交易 update 找不到列 → insert 第一筆；第二個交易 update 找到列，跳過 insert。
出事劇本（不常發生，但也絕非不會發生——並行 bug 最愛躲在光天化日之下）：
1. 第一個交易 update，找不到列；
2. 第二個交易也 update，一樣找不到列（第一個交易的 insert 還沒發生）；
3. 第二個交易 insert 當天第一筆；
4. 第一個交易接著 insert——主鍵衝突錯誤，因為那筆已經被插入了。

經典解法記載於 PostgreSQL 文件的 A PL/pgSQL Trigger Procedure For Maintaining A Summary Table：在迴圈裡輪流嘗試 update 與 insert 直到其中一個成功，過程中忽略 UNIQUE_VIOLATION 例外，作為「別的交易搶先 insert」時的退路。而從 PostgreSQL 9.5 起，insert into 支援 on conflict 子句，有好得多的解法。

修正行為#

靠 PostgreSQL 的 trigger 以事件驅動方式維護快取很容易，但把 insert 轉成對單一列有競爭的 update 永遠不是好主意——這是經典反模式。以下是用 on conflict 修正前述 trigger 的現代寫法，這次改為每則訊息的計數：

begin;

create table twcache.counters
 (
   messageid   bigint not null references tweet.message(messageid),
   rts         bigint,
   favs        bigint,

   unique(messageid)
 );

create or replace function twcache.tg_update_counters ()
 returns trigger
 language plpgsql
as $$
declare
begin
   insert into twcache.counters(messageid, rts, favs)
         select NEW.messageid,
                case when NEW.action = 'rt' then 1 else 0 end,
                case when NEW.action = 'fav' then 1 else 0 end
   on conflict (messageid)
      do update
            set rts = case when NEW.action = 'rt'
                           then counters.rts + 1

                                 when NEW.action = 'de-rt'
                                 then counters.rts - 1

                                 else counters.rts
                          end,

                   favs = case when NEW.action = 'fav'
                               then counters.favs + 1

                                  when NEW.action = 'de-fav'
                                  then counters.favs - 1

                              else counters.favs
                          end
            where counters.messageid = NEW.messageid;

  RETURN NULL;
end;
$$;

CREATE TRIGGER update_counters
         AFTER INSERT
            ON tweet.activity
      FOR EACH ROW
       EXECUTE PROCEDURE twcache.tg_update_counters();

insert into tweet.activity(messageid, action)
     values (7, 'rt'),
            (7, 'fav'),
            (7, 'de-fav'),
            (8, 'rt'),
            (8, 'rt'),
            (8, 'rt'),
            (8, 'de-rt'),
            (8, 'rt');

select messageid, rts, favs
  from twcache.counters;

rollback;

用 psql -f 或互動式 \i <path/to/file.sql> 執行的結果：

BEGIN
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
INSERT 0 8
 messageid │ rts │ favs
═══════════╪═════╪══════
         7 │   1 │    0
         8 │   3 │    0
(2 rows)

ROLLBACK

檔案結尾仍是 ROLLBACK——這個 trigger 只是示範，我們並不真的想安裝它：它會把每筆對 tweet.activity 的 insert 轉成對 twcache.counters 同一 messageid 的 update，等於推翻我們先前為推文活動擴展性所做的所有建模努力。上一章已經證明這種做法無法滿足我們的擴展需求。

事件觸發器（Event Triggers）#

事件觸發器是只有 PostgreSQL 支援的另一類 trigger，可對原始碼整合的任何事件掛上觸發器，目前主要提供給 DDL 指令使用。本書不涵蓋此主題，可參考 PostgreSQL 文件中的 A Table Rewrite Event Trigger Example。