PostgreSQL Event Trigger Einrichtung

Einführung

In diesem Lab lernen Sie, wie Sie einen Event-Trigger in PostgreSQL einrichten. Event-Trigger erfassen Data Definition Language (DDL)-Ereignisse, wie das Erstellen, Ändern oder Löschen von Datenbankobjekten.

Sie erstellen eine dedizierte Log-Tabelle und eine spezielle Funktion, um diese Ereignisse aufzuzeichnen. Anschließend erstellen Sie einen Event-Trigger, der nach einem CREATE TABLE-Befehl ausgelöst wird und die Details der neuen Tabelle protokolliert. Diese praktische Übung führt Sie durch die Erstellung der notwendigen Komponenten, das Testen der Trigger-Funktionalität und die anschließende Bereinigung der Datenbankobjekte.

Log-Tabelle und Trigger-Funktion erstellen

In diesem Schritt erstellen Sie die notwendigen Datenbankobjekte für die Protokollierung von DDL-Ereignissen: eine Tabelle zur Speicherung der Protokolle und eine Funktion, die der Trigger ausführt.

Öffnen Sie zunächst ein Terminal und verbinden Sie sich mit der PostgreSQL-Datenbank über die interaktive Shell psql. Die nachfolgenden SQL-Operationen führen Sie innerhalb dieser Shell durch.

sudo -u postgres psql

Sie sollten nun die PostgreSQL-Eingabeaufforderung sehen, die wie postgres=# aussieht.

Erstellen Sie als Nächstes eine Tabelle namens event_log, um die Details jedes DDL-Ereignisses zu speichern. Diese Tabelle wird Spalten für die Ereigniszeit, den Ereignistyp, das Schema, den Objektnamen und den ausgeführten Befehl enthalten.

Führen Sie den folgenden SQL-Befehl in der psql-Shell aus:

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

Nach erfolgreicher Ausführung antwortet PostgreSQL mit:

CREATE TABLE

Erstellen Sie nun die Funktion, die durch das DDL-Ereignis ausgelöst wird. Diese Funktion, log_ddl_event, fügt einen neuen Datensatz in die Tabelle event_log ein.

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;

Lassen Sie uns diese Funktion aufschlüsseln:

• RETURNS event_trigger: Gibt an, dass es sich um eine spezielle Art von Funktion handelt, die für die Verwendung mit Event-Triggern konzipiert ist.
• AS $$ ... $$ LANGUAGE plpgsql: Definiert den Funktionskörper unter Verwendung der prozeduralen Sprache plpgsql.
• INSERT INTO event_log ...: Dies ist die Kernaktion, die eine neue Zeile in unsere Protokolltabelle einfügt.
• now(): Eine Standardfunktion, die den aktuellen Zeitstempel zurückgibt.
• tg_event, tg_tag: Spezielle Variablen, die innerhalb einer Trigger-Funktion verfügbar sind und Kontext über das Ereignis liefern, wie z. B. ddl_command_end und CREATE TABLE.
• pg_event_trigger_ddl_commands(): Eine Funktion, die eine Menge von Zeilen zurückgibt, die die ausgeführten DDL-Befehle beschreiben. Wir verwenden sie, um den schema_name und die objid (Objektidentifikator) des erstellten Objekts zu erhalten.

Nachdem Sie den Befehl ausgeführt haben, sehen Sie die folgende Bestätigung:

CREATE FUNCTION

Sie haben nun die grundlegenden Komponenten für Ihren Event-Trigger erfolgreich eingerichtet.

DDL Event Trigger erstellen

Nachdem die Log-Tabelle und die Funktion eingerichtet sind, können Sie nun den Event-Trigger selbst erstellen. Dieser Trigger überwacht spezifische DDL-Befehle und führt Ihre Logging-Funktion aus, wenn diese auftreten.

Führen Sie in derselben psql-Shell den folgenden Befehl aus, um einen Event-Trigger namens log_table_creation zu erstellen:

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

Betrachten wir die Komponenten dieser Anweisung:

• CREATE EVENT TRIGGER log_table_creation: Dies benennt Ihren neuen Event-Trigger.
• ON ddl_command_end: Dies gibt den Zeitpunkt des Triggers an. Er wird ausgelöst, nachdem ein DDL-Befehl die Ausführung beendet hat.
• WHEN TAG IN ('CREATE TABLE'): Dies ist eine Filterbedingung. Der Trigger wird nur aktiviert, wenn das Befehls-Tag mit CREATE TABLE übereinstimmt. Sie können hier auch andere Tags wie ALTER TABLE oder DROP TABLE hinzufügen.
• EXECUTE FUNCTION log_ddl_event(): Dies gibt an, welche Funktion ausgeführt werden soll, wenn die Bedingungen des Triggers erfüllt sind.

Nach Ausführung des Befehls bestätigt PostgreSQL dessen Erstellung:

CREATE EVENT TRIGGER

Ihr Event-Trigger ist nun aktiv und wird jede neue Tabellenerstellung protokollieren.

Event Trigger testen

Nun ist es an der Zeit zu testen, ob der Event-Trigger wie erwartet funktioniert. Sie werden eine neue Tabelle erstellen und dann die event_log überprüfen, um zu sehen, ob das Erstellungsereignis aufgezeichnet wurde.

Erstellen Sie zunächst eine einfache Tabelle namens employees:

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

Der Befehl CREATE TABLE wird normal ausgeführt, und Sie sehen die Standardbestätigung:

CREATE TABLE

Im Hintergrund sollte diese Aktion Ihren Trigger log_table_creation ausgelöst haben. Um dies zu überprüfen, fragen Sie die Tabelle event_log ab, um deren Inhalt anzuzeigen:

SELECT schema_name, object_name, command_tag FROM event_log;

Die Ausgabe zeigt mehrere Datensätze. Dies liegt daran, dass die Erstellung einer Tabelle mit einem serial PRIMARY KEY auch implizit eine Sequenz und eine Primärschlüsselbeschränkung erstellt. Der Event-Trigger erfasst alle zugehörigen DDL-Befehle, was zu mehreren Einträgen im Log führt.

 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)

Dieses Ergebnis bestätigt, dass Ihr Event-Trigger korrekt funktioniert und die Tabellenerstellung zusammen mit ihren abhängigen Objekten protokolliert.

Datenbankobjekte bereinigen

Es ist gute Praxis, nicht mehr benötigte Datenbankobjekte zu entfernen. In diesem letzten Schritt werden Sie den Event-Trigger, die Tabellen und die Funktion, die Sie während dieses Labs erstellt haben, löschen.

Entfernen Sie zuerst den Event-Trigger:

DROP EVENT TRIGGER log_table_creation;

Sie sehen die Bestätigungsnachricht:

DROP EVENT TRIGGER

Löschen Sie als Nächstes die beiden von Ihnen erstellten Tabellen, employees und event_log:

DROP TABLE employees;
DROP TABLE event_log;

PostgreSQL bestätigt jede Aktion:

DROP TABLE
DROP TABLE

Entfernen Sie schließlich die Trigger-Funktion:

DROP FUNCTION log_ddl_event();

Sie sehen die endgültige Bestätigung:

DROP FUNCTION

Sie haben nun alle in diesem Lab erstellten Objekte erfolgreich bereinigt. Um die psql-Shell zu verlassen und zur normalen Terminalaufforderung zurückzukehren, geben Sie \q ein und drücken Sie Enter.

\q

Zusammenfassung

In diesem Lab haben Sie gelernt, wie man Event-Trigger in PostgreSQL implementiert, um DDL-Aktivitäten zu überwachen. Sie haben eine Log-Tabelle zur Speicherung von Event-Daten und eine PL/pgSQL-Funktion zu deren Befüllung erstellt. Anschließend haben Sie einen Event-Trigger definiert, der speziell CREATE TABLE-Ereignisse erfasst und ihn mit Ihrer Logging-Funktion verknüpft. Durch das Testen des Triggers und die Überprüfung des Log-Eintrags haben Sie praktische Erfahrung damit gesammelt, wie diese Komponenten zusammenarbeiten. Abschließend haben Sie gutes Datenbankmanagement praktiziert, indem Sie den Trigger, die Tabellen und die Funktion, die Sie erstellt haben, bereinigt haben.