Configuration de déclencheurs d'événements PostgreSQL

Introduction

Dans ce laboratoire, vous apprendrez comment configurer un déclencheur d'événements (event trigger) dans PostgreSQL. Les déclencheurs d'événements capturent les événements de langage de définition de données (DDL), tels que la création, la modification ou la suppression d'objets de base de données.

Vous créerez une table de journalisation dédiée et une fonction spéciale pour enregistrer ces événements. Ensuite, vous créerez un déclencheur d'événements qui se déclenchera après une commande CREATE TABLE, enregistrant les détails de la nouvelle table. Cet exercice pratique vous guidera à travers la création des composants nécessaires, le test de la fonctionnalité du déclencheur et le nettoyage des objets de base de données par la suite.

Créer une table de journalisation et une fonction de déclenchement

Dans cette étape, vous allez créer les objets de base de données nécessaires à la journalisation des événements DDL : une table pour stocker les journaux et une fonction que le déclencheur exécutera.

Tout d'abord, ouvrez un terminal et connectez-vous à la base de données PostgreSQL en utilisant le shell interactif psql. Vous effectuerez les opérations SQL suivantes dans ce shell.

sudo -u postgres psql

Vous devriez maintenant voir l'invite PostgreSQL, qui ressemble à postgres=#.

Ensuite, créez une table nommée event_log pour stocker les détails de chaque événement DDL. Cette table aura des colonnes pour l'heure de l'événement, le type, le schéma, le nom de l'objet et la commande qui a été exécutée.

Exécutez la commande SQL suivante dans le shell psql :

CREATE TABLE event_log (
    event_time timestamp with time zone,
    event_type text,
    schema_name text,
    object_name text,
    command_tag text
);

Après une exécution réussie, PostgreSQL répondra par :

CREATE TABLE

Maintenant, créez la fonction qui sera déclenchée par l'événement DDL. Cette fonction, log_ddl_event, insérera un nouvel enregistrement dans la table event_log.

CREATE OR REPLACE FUNCTION log_ddl_event()
RETURNS event_trigger
AS $$
BEGIN
    INSERT INTO event_log (event_time, event_type, schema_name, object_name, command_tag)
    SELECT now(), tg_event, ddl_command.schema_name, ddl_command.objid::regclass, tg_tag
    FROM pg_event_trigger_ddl_commands() AS ddl_command;
END;
$$ LANGUAGE plpgsql;

Décomposons cette fonction :

• RETURNS event_trigger : Spécifie qu'il s'agit d'un type spécial de fonction conçu pour être utilisé avec des déclencheurs d'événements.
• AS $$ ... $$ LANGUAGE plpgsql : Définit le corps de la fonction en utilisant le langage procédural plpgsql.
• INSERT INTO event_log ... : C'est l'action principale, qui insère une nouvelle ligne dans notre table de journalisation.
• now() : Une fonction standard qui renvoie l'horodatage actuel.
• tg_event, tg_tag : Variables spéciales disponibles à l'intérieur d'une fonction de déclenchement qui fournissent le contexte de l'événement, comme ddl_command_end et CREATE TABLE.
• pg_event_trigger_ddl_commands() : Une fonction qui renvoie un ensemble de lignes décrivant les commandes DDL qui ont été exécutées. Nous l'utilisons pour obtenir le schema_name et l'objid (identifiant de l'objet) de l'objet en cours de création.

Après avoir exécuté la commande, vous verrez la confirmation suivante :

CREATE FUNCTION

Vous avez maintenant configuré avec succès les composants fondamentaux de votre déclencheur d'événements.

Créer le déclencheur d'événements DDL

Avec la table de journalisation et la fonction en place, vous pouvez maintenant créer le déclencheur d'événements lui-même. Ce déclencheur surveillera les commandes DDL spécifiques et exécutera votre fonction de journalisation lorsqu'elles se produiront.

Dans le même shell psql, exécutez la commande suivante pour créer un déclencheur d'événements nommé log_table_creation :

CREATE EVENT TRIGGER log_table_creation
ON ddl_command_end
WHEN TAG IN ('CREATE TABLE')
EXECUTE FUNCTION log_ddl_event();

Examinons les composants de cette instruction :

• CREATE EVENT TRIGGER log_table_creation : Ceci nomme votre nouveau déclencheur d'événements.
• ON ddl_command_end : Ceci spécifie le moment du déclenchement. Il se déclenchera après l'exécution d'une commande DDL.
• WHEN TAG IN ('CREATE TABLE') : Ceci est une condition de filtrage. Le déclencheur ne s'activera que si le tag de la commande correspond à CREATE TABLE. Vous pouvez également ajouter d'autres tags comme ALTER TABLE ou DROP TABLE ici.
• EXECUTE FUNCTION log_ddl_event() : Ceci spécifie quelle fonction exécuter lorsque les conditions du déclencheur sont remplies.

Après avoir exécuté la commande, PostgreSQL confirmera sa création :

CREATE EVENT TRIGGER

Votre déclencheur d'événements est maintenant actif et enregistrera chaque nouvelle création de table.

Tester le déclencheur d'événements

Il est maintenant temps de tester si le déclencheur d'événements fonctionne comme prévu. Vous allez créer une nouvelle table, puis vérifier la table event_log pour voir si l'événement de création a été enregistré.

Tout d'abord, créez une table simple nommée employees :

CREATE TABLE employees (
    id serial PRIMARY KEY,
    name text NOT NULL
);

La commande CREATE TABLE s'exécutera normalement, et vous verrez la confirmation standard :

CREATE TABLE

En coulisses, cette action aurait dû déclencher votre déclencheur log_table_creation. Pour vérifier cela, interrogez la table event_log pour voir son contenu :

SELECT schema_name, object_name, command_tag FROM event_log;

La sortie affichera plusieurs enregistrements. C'est parce que la création d'une table avec une serial PRIMARY KEY crée également implicitement une séquence et une contrainte de clé primaire. Le déclencheur d'événements capture toutes les commandes DDL associées, ce qui entraîne plusieurs entrées dans le journal.

 schema_name |   object_name    | command_tag
-------------+------------------+--------------
 public      | employees_id_seq | CREATE TABLE
 public      | employees        | CREATE TABLE
 public      | employees_pkey   | CREATE TABLE
 public      | employees_id_seq | CREATE TABLE
(4 rows)

Ce résultat confirme que votre déclencheur d'événements fonctionne correctement et enregistre la création de la table ainsi que ses objets dépendants.

Nettoyer les objets de la base de données

Il est de bonne pratique de supprimer les objets de base de données qui ne sont plus nécessaires. Dans cette dernière étape, vous allez supprimer le déclencheur d'événements, les tables et la fonction que vous avez créés lors de ce laboratoire.

Tout d'abord, supprimez le déclencheur d'événements :

DROP EVENT TRIGGER log_table_creation;

Vous verrez le message de confirmation :

DROP EVENT TRIGGER

Ensuite, supprimez les deux tables que vous avez créées, employees et event_log :

DROP TABLE employees;
DROP TABLE event_log;

PostgreSQL confirmera chaque action :

DROP TABLE
DROP TABLE

Enfin, supprimez la fonction de déclenchement :

DROP FUNCTION log_ddl_event();

Vous verrez la confirmation finale :

DROP FUNCTION

Vous avez maintenant nettoyé avec succès tous les objets créés dans ce laboratoire. Pour quitter le shell psql et revenir à l'invite du terminal normal, tapez \q et appuyez sur Entrée.

\q

Résumé

Dans ce laboratoire, vous avez appris à implémenter des déclencheurs d'événements dans PostgreSQL pour surveiller les activités DDL. Vous avez créé une table de journalisation pour stocker les données d'événements et une fonction PL/pgSQL pour la peupler. Vous avez ensuite défini un déclencheur d'événements qui capture spécifiquement les événements CREATE TABLE, en le reliant à votre fonction de journalisation. En testant le déclencheur et en vérifiant l'entrée du journal, vous avez acquis une expérience pratique du fonctionnement conjoint de ces composants. Enfin, vous avez pratiqué une bonne gestion de base de données en nettoyant le déclencheur, les tables et la fonction que vous avez créés.