Considérations sur les performances de PostgreSQL

Les déclencheurs dans PostgreSQL sont des fonctions de rappel de base de données automatiquement invoquées ou exécutées en réponse à certains événements sur un tableau ou une vue particulière. Ils sont utilisés pour appliquer les règles de gestion, valider les données d'entrée et maintenir l'intégrité des données.

Utilisation

Les déclencheurs sont utilisés lorsque vous avez besoin d'exécuter automatiquement une fonction en réponse à des événements tels que les opérations `INSERT`, `UPDATE`, ou `DELETE` sur un tableau. Ils sont définis pour agir avant ou après ces événements.

sql
CREATE TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
ON table_name
[ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE function_name(arguments);
Propulsé par

Dans cette syntaxe, `CREATE TRIGGER` met en place un trigger appelé `nom_trigger` qui exécute `nom_de_fonction` lorsque les événements spécifiés se produisent sur `nom_de_table`.

Exemples

1. Déclencheur d'insertion de base

sql
CREATE OR REPLACE FUNCTION log_insert()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_table(action, time) VALUES ('INSERT', NOW());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER insert_audit
AFTER INSERT ON my_table
FOR EACH ROW EXECUTE PROCEDURE log_insert();
Propulsé par

Ce trigger enregistre chaque opération d'insertion sur `my_table` en ajoutant un enregistrement à `audit_table`.

2. Déclencheur de mise à jour avec logique conditionnelle

sql
CREATE OR REPLACE FUNCTION check_update()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.amount < 0 THEN
    RAISE EXCEPTION 'Amount cannot be negative';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_update_trigger
BEFORE UPDATE ON transactions
FOR EACH ROW EXECUTE PROCEDURE check_update();
Propulsé par

Ce déclencheur vérifie que la colonne `amount` du tableau `transactions` n'est pas négative avant les opérations de mise à jour.

3. Déclenchement des suppressions en cascade

sql
CREATE OR REPLACE FUNCTION cascade_delete()
RETURNS TRIGGER AS $$
BEGIN
  DELETE FROM child_table WHERE parent_id = OLD.id;
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER cascading_delete_trigger
AFTER DELETE ON parent_table
FOR EACH ROW EXECUTE PROCEDURE cascade_delete();
Propulsé par

Ce trigger permet de s'assurer que lorsqu'une ligne est supprimée de `parent_table`, les lignes correspondantes dans `child_table` sont également supprimées.

Conseils et bonnes pratiques

• Logique de déclenchement des limites. Veillez à ce que les fonctions de déclenchement soient simples afin de minimiser les surcoûts liés aux performances et d'éviter les logiques commerciales complexes.
• Utilisez les déclencheurs au niveau des lignes de manière sélective. Optez pour des déclencheurs au niveau des déclarations lorsque cela est possible afin de réduire le nombre d'exécutions. Les déclencheurs au niveau des lignes sont utiles lorsque les opérations doivent être effectuées sur chaque ligne concernée, tandis que les déclencheurs au niveau des instructions peuvent réduire les frais généraux en s'exécutant une fois par opération.
• Soyez prudent avec les déclencheurs récursifs. Veillez à ce que les déclencheurs ne s'appellent pas eux-mêmes sans le vouloir, ce qui entraînerait des boucles infinies.
• Effectuez un test approfondi. Testez de manière approfondie les déclencheurs dans un environnement de développement afin d'évaluer leur impact sur les performances.
• Déclencheurs de documents. Documentez clairement l'objectif et la logique de chaque déclencheur afin de pouvoir vous y référer ultérieurement et d'assurer la maintenance.
• Tenez compte des limites de la transaction. Soyez attentif aux limites des transactions pour vous assurer que les déclencheurs se comportent comme prévu dans les transactions multi-déclarations.
• Contrôler les performances. Gardez un œil sur l'impact des déclencheurs sur les performances d'un système réel et diagnostiquez tout problème à l'aide des outils de journalisation et de surveillance de PostgreSQL.

Gestion des déclencheurs

• Pour désactiver temporairement un déclencheur, utilisez la commande `ALTER TABLE ... Commande DISABLE TRIGGER`.
• Pour supprimer définitivement un déclencheur, utilisez la commande `DROP TRIGGER`.