PostgreSQL Antipatterns : modifier les données en contournant un déclencheur

Tôt ou tard, beaucoup sont confrontés à la nécessité de réparer massivement quelque chose dans les enregistrements de table. j'ai déjà dis moi comment faire mieux, et comment - il vaut mieux ne pas le faire. Aujourd'hui, je vais parler du deuxième aspect de la mise à jour de masse - à propos des déclencheurs.

Par exemple, sur une table dans laquelle vous devez réparer quelque chose, un déclencheur maléfique se bloque ON UPDATE, transférant toutes les modifications à certains agrégats. Et vous devez tout mettre à jour (initialiser un nouveau champ, par exemple) avec tant de soin que ces agrégats ne sont pas affectés.

Désactivons simplement les déclencheurs !

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

En fait, c'est tout - tout est suspendu.

Parce que ALTER TABLE impose Accès exclusif- un verrou sous lequel personne ne court en parallèle, même un simple SELECT, ne pourra rien lire du tableau. C'est-à-dire que jusqu'à la fin de cette transaction, tous ceux qui veulent même « simplement lire » attendront. Et on s'en souvient UPDATE nous avons longtemps...

Éteignons-le rapidement, puis rallumons-le rapidement !

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

UPDATE ...;

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

Ici la situation est déjà meilleure, le temps d'attente est bien moindre. Mais seuls deux problèmes gâchent toute la beauté :

• ALTER TABLE attend lui-même toutes les autres opérations sur la table, y compris les longues SELECT
• Pendant que la gâchette est éteinte "survoler" tout changement dans le tableau, pas même le nôtre. Et il n'entrera pas dans les agrégats, bien qu'il le devrait. Inquiéter!

Gestion des variables de session

Ainsi, dans la version précédente, nous sommes tombés sur un point fondamental - nous devons en quelque sorte apprendre au déclencheur à distinguer "nos" changements dans le tableau de "pas les nôtres". "Les nôtres" sont ignorés tels quels, mais sur "pas les nôtres", ils sont déclenchés. Pour cela, vous pouvez utiliser variable de session.

session_replication_role

Lire Manuel:

Le mécanisme de déclenchement est également affecté par la variable de configuration session_replication_role. Activés sans instructions supplémentaires (par défaut), les déclencheurs se déclenchent lorsque le rôle de réplication est "origine" (par défaut) ou "local". Déclencheurs activés en spécifiant ENABLE REPLICA, ne fonctionnera que si mode de session en cours - "réplica", et déclencheurs activés en spécifiant ENABLE ALWAYS, fonctionnera quel que soit le mode de réplication actuel.

Je soulignerai surtout que le réglage ne s'applique pas à tout-tout à la fois, comme ALTER TABLE, mais uniquement à notre connexion spéciale séparée. Au total, pour qu'aucun déclencheur d'application ne fonctionne :

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

Condition à l'intérieur de la gâchette

Mais l'option ci-dessus fonctionne pour tous les déclencheurs à la fois (ou vous devez "alterner" les déclencheurs à l'avance que vous ne souhaitez pas désactiver). Et si nous avons besoin "désactiver" un déclencheur spécifique?

Cela nous aidera variable de session "utilisateur":

Les noms des paramètres d'extension sont écrits comme suit : le nom de l'extension suivi d'un point, puis le nom du paramètre lui-même, similaire aux noms d'objet complets dans SQL. Par exemple : plpgsql.variable_conflict.
Étant donné que les options hors système peuvent être définies dans des processus qui ne chargent pas le module d'extension approprié, PostgreSQL accepte valeurs pour tous les noms à deux composants.

Tout d'abord, nous finalisons le déclencheur, quelque chose comme ceci :

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;
...

Soit dit en passant, cela peut être fait "à but lucratif", sans bloquer, par CREATE OR REPLACE pour la fonction de déclenchement. Et ensuite, dans la connexion spéciale, nous armons "notre" variable :

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

Connaissez-vous d'autres moyens ? Partagez dans les commentaires.

Source: habr.com