PostgreSQL Antipatterns : modification des données en contournant le trigger

Tôt ou tard, beaucoup se retrouvent confrontés à la nécessité de corriger massivement des enregistrements dans une table. J'ai déjà expliqué comment le faire au mieux, et comment ne pas le faire. Aujourd'hui, je vais parler du deuxième aspect de la mise à jour massive — le déclenchement des triggers.

Par exemple, sur la table où vous devez apporter des modifications, il y a un trigger malveillant ON UPDATE, qui transfère tous les changements vers certains agrégats. Et vous devez tout mettre à jour (initialiser un nouveau champ, par exemple) de manière à ne pas toucher à ces agrégats.

Désactivons simplement les triggers !

BEGIN;
  ALTER TABLE ... DISABLE TRIGGER ...;
  UPDATE ...; -- ici pendant longtemps
  ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;

En fait, c'est tout — tout est déjà suspendu.

Parce que ALTER TABLE impose uneverrouillage AccessExclusive, sous lequel personne ne peut lire quoi que ce soit de la table, même la plus simple SELECT, rien de la table ne pourra être lu. C'est-à-dire que tant que cette transaction n'est pas terminée, tous ceux qui souhaitent même « juste lire » devront attendre. Et nous nous souvenons que UPDATE nous avons une très looooong…

Alors désactivons rapidement, puis réactivons 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 considérablement réduit. Mais deux problèmes viennent gâcher toute la beauté :

• ALTER TABLE il attend toutes les autres opérations sur la table, y compris les longues SELECT
• Tant que le trigger est désactivé, tout changement dans la table, même pas le nôtre, passera « à côté ». Et il ne parviendra pas aux agrégats, alors qu'il le devrait. Quel désastre !

Gestion des variables de session

Ainsi, dans le précédent scénario, nous avons rencontré un point fondamental — il faut apprendre au trigger à distinguer les « changements » dans la table de ceux qui ne le sont pas. Les « nôtres » doivent passer tels quels, et pour les « non-nôtres », il doit se déclencher. Pour cela, nous pouvons utiliser des variables de session.

session_replication_role

Nous lisons manuel:

Le mécanisme de déclenchement des triggers est également influencé par la variable de configuration session_replication_role. Les triggers activés sans indications supplémentaires (par défaut) se déclencheront lorsque le rôle de réplication est « origin » (par défaut) ou « local ». Les triggers activés avec ENABLE REPLICA, se déclencheront uniquement si le mode de session actuel est « replica », et les triggers activés avec ENABLE ALWAYS, se déclencheront indépendamment du mode de réplication actuel.

Je souligne particulièrement que ce réglage ne concerne pas tous-en-même-temps, comme ALTER TABLE, mais seulement à notre connexion spéciale distincte. En résumé, pour éviter que des déclencheurs applicatifs ne s'activent :

SET session_replication_role = replica; -- désactiver les déclencheurs
UPDATE ...;
SET session_replication_role = DEFAULT; -- revenir à l'état initial

Condition à l'intérieur du déclencheur

Mais l'option ci-dessus fonctionne pour tous les déclencheurs à la fois (ou il faut "modifier" à l'avance les déclencheurs que l'on ne souhaite pas désactiver). Et si nous devons "désactiver" un déclencheur spécifique?

Cela nous aidera à "variable" de session personnalisée:

Les noms des paramètres d'extension sont écrits de la manière suivante : nom de l'extension, point, puis le nom propre du paramètre, semblable aux noms complets des objets en SQL. Par exemple : plpgsql.variable_conflict.
Comme les paramètres non système peuvent être définis dans des processus ne chargeant pas le module d'extension correspondant, PostgreSQL accepte des valeurs pour tous les noms à deux composants.

D'abord, nous modifions le déclencheur, à peu près comme ça :

BEGIN
    -- le processus de conversion peut faire tout
    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;
...

À propos, cela peut être fait "en direct", sans blocages, via CREATE OR REPLACE pour la fonction de déclencheur. Et ensuite, dans la connexion spéciale, nous définissons notre variable :

SET mycfg.my_table_convert_process = 'TRUE';
UPDATE ...;
SET mycfg.my_table_convert_process = ''; -- revenir à l'état initial

Connaissez-vous d'autres méthodes ? Partagez-les dans les commentaires.

Source : habr.com