PostgreSQL アンチパターン: トリガーをバイパスしてデータを変更する

遅かれ早かれ、多くの人がテーブル レコード内の何かを大規模に修正する必要に直面することになります。 私はすでに持っています より良くする方法を教えてください、そしてどのように - それをしない方が良いです。 今日は一括アップデートの XNUMX 番目の側面についてお話します。 トリガーについて.

たとえば、何かを修正する必要があるテーブルで、邪悪なトリガーがハングします。 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;

ここでは状況はすでに良くなり、待ち時間ははるかに短くなります。 しかし、たった XNUMX つの問題だけがすべての美しさを台無しにしてしまいます。

• ALTER TABLE それ自体は、テーブル上の他のすべての操作 (長い操作を含む) を待機します。 SELECT
• トリガーがオフの状態では、 あらゆる変化を「飛ばして」 テーブルには、私たちのテーブルにもありません。 そして、それは集合体に入るはずですが、入りません。 問題！

セッション変数の管理

したがって、前のバージョンでは、基本的な点に遭遇しました。テーブル内の「私たちの」変更と「私たち以外の」変更を区別するために、何らかの方法でトリガーを教える必要があります。 「Ours」はそのままスキップされますが、「not ours」ではトリガーされます。 このために使用できます セッション変数.

session_replication_role

読む マニュアル:

トリガーメカニズムは構成変数にも影響されます session_replication_role。 追加の指示なしで有効にすると (デフォルト)、レプリケーション ロールが「オリジン」(デフォルト) または「ローカル」の場合にトリガーが起動します。 を指定することで有効になるトリガー ENABLE REPLICA、次の場合にのみ機能します。 現在のセッションモード - 「レプリカ」、および指定することでトリガーが有効になります ENABLE ALWAYS、現在のレプリケーション モードに関係なく機能します。

この設定は一度にすべてを適用するものではないことを特に強調しておきます。 ALTER TABLE、ただし、私たちの別の特別な接続にのみ。 合計すると、アプリケーション トリガーが機能しないようになります。

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

トリガー内部の状態

ただし、上記のオプションはすべてのトリガーに対して一度に機能します (または、無効にしたくないトリガーを事前に「代替」する必要があります)。 そして必要であれば 特定のトリガーを「オフ」にする?

これは私たちにとって役立ちます 「ユーザー」セッション変数:

拡張パラメータ名は次のように記述されます。SQL の完全なオブジェクト名と同様に、拡張名の後にドット、パラメータ名自体が続きます。 例: plpgsql.variable_conflict。
適切な拡張モジュールをロードしないプロセスではシステム外のオプションが設定される可能性があるため、PostgreSQL は XNUMX つのコンポーネントを含む名前の値.

まず、次のようにトリガーを完成させます。

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 = ''; -- вернули в исходное состояние

他の方法を知っていますか? コメントで共有してください。

出所： habr.com