主持人 > 博客 > 管理 > PostgreSQL 反模式：繞過觸發器更改數據

PostgreSQL 反模式：繞過觸發器更改數據

遲早，許多人都需要大量修復表記錄中的某些內容。 我已經 告訴我如何做得更好，以及如何 - 最好不要這樣做。 今天要說的是海量更新的第二個方面—— 關於觸發器.

例如，在您需要修復某些東西的桌子上，一個邪惡的觸發器掛起 ON UPDATE，將所有更改轉移到某些聚合。 並且您需要非常小心地更新所有內容（例如初始化一個新字段），以免這些聚合受到影響。

讓我們禁用觸發器！

BEGIN;
  ALTER TABLE ... DISABLE TRIGGER ...;
  UPDATE ...; -- тут долго-долго
  ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;

其實，僅此而已—— 一切都掛了.

因為 ALTER TABLE 施加 訪問獨家- 一個沒有人並行運行的鎖，即使是一個簡單的鎖 SELECT, 將無法從表中讀取任何內容。 也就是說，在這個交易結束之前，每個想要“只讀”的人都會等待。 我們記得 UPDATE 我們有很長的...

讓我們快速關閉它，然後快速打開它！

BEGIN;
  ALTER TABLE ... DISABLE TRIGGER ...;
COMMIT;

UPDATE ...;

BEGIN;
  ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;

這裡的情況已經好了很多，等待的時間也少了很多。 但只有兩個問題破壞了所有的美麗：

  • ALTER TABLE 本身等待表上的所有其他操作，包括長操作 SELECT
  • 當觸發器關閉時 “飛過”任何變化 在桌子上，甚至不是我們的。 它不會進入聚合，儘管它應該。 麻煩！

管理會話變量

因此，在之前的版本中，我們偶然發現了一個基本點——我們需要以某種方式教會觸發器區分錶中“我們的”更改和“不是我們的”更改。 “我們的”按原樣被跳過，但在“不是我們的”時它們被觸發。 為此，您可以使用 會話變量.

會話複製角色

閱讀 手動的:

觸發機制也受配置變量的影響 會話複製角色. 在沒有額外說明的情況下啟用（默認），當複制角色是“源”（默認）或“本地”時，觸發器將觸發。 通過指定啟用觸發器 ENABLE REPLICA, 只有當 當前會話模式 - “副本”，並通過指定啟用觸發器 ENABLE ALWAYS, 無論當前的複制模式如何，都將起作用。

我要特別強調的是，該設置不適用於 all-all at once，因為 ALTER TABLE，但僅限於我們單獨的特殊連接。 總的來說，沒有應用程序觸發器工作：

SET session_replication_role = replica; -- выключили триггеры
UPDATE ...;
SET session_replication_role = DEFAULT; -- вернули в исходное состояние

觸發條件

但是上面的選項一次適用於所有觸發器（或者你需要提前“交替”你不想禁用的觸發器）。 如果我們需要 “關閉”一個特定的觸發器?

這將幫助我們 “用戶”會話變量:

擴展參數名的寫法如下：擴展名後跟一個點，然後是參數名本身，類似於 SQL 中的完整對象名。 例如：plpgsql.variable_conflict。
因為可以在不加載適當擴展模塊的進程中設置系統外選項，PostgreSQL 接受 具有兩個組件的任何名稱的值.

首先，我們完成觸發器，如下所示：

BEGIN
    -- процессу конвертации можно делать все
    IF current_setting('mycfg.my_table_convert_process') = 'TRUE' THEN
        IF TG_OP IN ('INSERT', 'UPDATE') THEN
            RETURN NEW;
        ELSE
            RETURN OLD;
        END IF;
    END IF;
...

順便說一句，這可以通過 CREATE OR REPLACE 用於觸發功能。 然後在特殊連接中我們設置“我們的”變量：


SET mycfg.my_table_convert_process = 'TRUE';
UPDATE ...;
SET mycfg.my_table_convert_process = ''; -- вернули в исходное состояние

你知道其他方法嗎？ 在評論中分享。

來源： www.habr.com

添加評論