PostgreSQL Event Trigger Einrichtung

Einführung

In diesem Lab lernen Sie, wie Sie einen PostgreSQL Event Trigger (Ereignis-Trigger) einrichten. Ziel ist es, eine Funktion zu erstellen, die Datenbankereignisse, insbesondere die Tabellenerstellung, in eine dedizierte Log-Tabelle protokolliert.

Sie erstellen eine Event-Trigger-Funktion namens log_ddl_event, die Ereignisdetails in die Tabelle event_log einfügt. Anschließend richten Sie einen DDL Event Trigger ein, um diese Funktion auszuführen, wenn eine neue Tabelle erstellt wird. Abschließend testen Sie den Trigger, indem Sie eine neue Tabelle erstellen und überprüfen, ob das Ereignis korrekt protokolliert wurde, und löschen dann den Event Trigger.

Skills Graph

Erstellen der Event-Log-Tabelle und der Trigger-Funktion

In diesem Schritt erstellen Sie die Tabelle event_log und die Funktion log_ddl_event. Die Tabelle event_log speichert die Details der Datenbankereignisse, und die Funktion log_ddl_event wird ausgeführt, wenn ein bestimmtes Ereignis eintritt.

Stellen Sie zunächst als Benutzer postgres eine Verbindung zur PostgreSQL-Datenbank her:

sudo -u postgres psql

Erstellen Sie nun die Tabelle event_log, indem Sie den folgenden SQL-Befehl ausführen:

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

Dieser Befehl erstellt eine Tabelle namens event_log mit Spalten zum Speichern der Ereigniszeit (event_time), des Ereignistyps (event_type), des Schemanamens (schema_name), des Objektnamens (object_name) und des Befehls-Tags (command_tag).

Erstellen Sie als Nächstes die Event-Trigger-Funktion log_ddl_event. Führen Sie den folgenden SQL-Befehl aus:

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)
    VALUES (NOW(), TG_EVENT, TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_TAG);
END;
$$ LANGUAGE plpgsql;

Dieser Befehl erstellt eine Funktion namens log_ddl_event. Lassen Sie uns aufschlüsseln, was diese Funktion tut:

• CREATE OR REPLACE FUNCTION log_ddl_event(): Erstellt oder ersetzt eine Funktion namens log_ddl_event.
• RETURNS event_trigger: Gibt an, dass die Funktion eine Event-Trigger-Funktion (Ereignis-Trigger-Funktion) ist.
• AS $$ ... $$ LANGUAGE plpgsql: Definiert den Funktionskörper mit der Sprache plpgsql.
• INSERT INTO event_log ...: Fügt eine neue Zeile mit den Ereignisdetails in die Tabelle event_log ein.
• NOW(): Ruft den aktuellen Zeitstempel ab.
• TG_EVENT: Eine spezielle Variable, die den Typ des Ereignisses enthält, das die Funktion ausgelöst hat (z. B. CREATE TABLE).
• TG_TABLE_SCHEMA: Eine spezielle Variable, die den Schemanamen des Objekts enthält, das die Funktion ausgelöst hat.
• TG_TABLE_NAME: Eine spezielle Variable, die den Namen des Objekts enthält, das die Funktion ausgelöst hat.
• TG_TAG: Eine spezielle Variable, die das Command Tag (Befehls-Tag) des Ereignisses enthält (z. B. CREATE TABLE).

Nach dem Ausführen der obigen Befehle sollten Sie CREATE TABLE und CREATE FUNCTION in der Ausgabe sehen, was darauf hinweist, dass die Tabelle und die Funktion erfolgreich erstellt wurden.

Erstellen des DDL Event Triggers

In diesem Schritt erstellen Sie einen DDL Event Trigger (Ereignis-Trigger), der die Funktion log_ddl_event ausführt, wenn eine neue Tabelle erstellt wird.

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

CREATE EVENT TRIGGER log_table_creation
ON ddl_command_end
WHEN TAG IS 'CREATE TABLE'
EXECUTE PROCEDURE log_ddl_event();

Dieser Befehl erstellt einen Event Trigger namens log_table_creation. Lassen Sie uns aufschlüsseln, was dieser Trigger tut:

• CREATE EVENT TRIGGER log_table_creation: Erstellt einen Event Trigger namens log_table_creation.
• ON ddl_command_end: Gibt an, dass der Trigger am Ende eines DDL-Befehls ausgelöst wird.
• WHEN TAG IS 'CREATE TABLE': Gibt an, dass der Trigger nur ausgelöst wird, wenn das Command Tag (Befehls-Tag) CREATE TABLE ist.
• EXECUTE PROCEDURE log_ddl_event(): Gibt an, dass die Funktion log_ddl_event ausgeführt werden soll, wenn der Trigger ausgelöst wird.

Nach dem Ausführen des obigen Befehls sollten Sie CREATE EVENT TRIGGER in der Ausgabe sehen.

Testen des Triggers

Lassen Sie uns nun den Event Trigger (Ereignis-Trigger) testen, indem wir eine neue Tabelle erstellen und überprüfen, ob das Ereignis in der Tabelle event_log protokolliert wird.

Erstellen Sie eine neue Tabelle namens test_table, indem Sie den folgenden SQL-Befehl ausführen:

CREATE TABLE test_table (
    id serial PRIMARY KEY,
    name text
);

Dieser Befehl erstellt eine Tabelle namens test_table mit einer Spalte id und einer Spalte name. Sie sollten CREATE TABLE in der Ausgabe sehen.

Überprüfen Sie als Nächstes, ob der Event Trigger das Tabellenerstellungsereignis in der Tabelle event_log protokolliert hat. Führen Sie den folgenden SQL-Befehl aus:

SELECT * FROM event_log;

Dieser Befehl wählt alle Zeilen aus der Tabelle event_log aus. Sie sollten eine Zeile mit den Details des CREATE TABLE-Ereignisses für test_table sehen. Die Ausgabe sollte in etwa so aussehen:

       event_time        | event_type  | schema_name | object_name | command_tag
-----------------------+-------------+-------------+-------------+-------------
 2023-10-27 10:00:00+00 | ddl_command_end | public      | test_table  | CREATE TABLE
(1 row)

Die event_time ist die Zeit, zu der Sie die Tabelle erstellt haben. Der event_type ist ddl_command_end. Der schema_name ist public (oder das Schema, in dem Sie die Tabelle erstellt haben). Der object_name ist test_table. Der command_tag ist CREATE TABLE.

Löschen des Triggers und der Tabellen

In diesem Schritt löschen Sie den Event Trigger (Ereignis-Trigger) und die von Ihnen erstellten Tabellen.

Löschen Sie zuerst den Event Trigger log_table_creation, indem Sie den folgenden SQL-Befehl ausführen:

DROP EVENT TRIGGER log_table_creation;

Sie sollten DROP EVENT TRIGGER in der Ausgabe sehen.

Löschen Sie als Nächstes die Tabelle test_table und die Tabelle event_log, indem Sie die folgenden SQL-Befehle ausführen:

DROP TABLE test_table;
DROP TABLE event_log;

Löschen Sie abschließend die Funktion log_ddl_event:

DROP FUNCTION log_ddl_event();

Damit ist das Lab (die Übung) abgeschlossen. Sie haben erfolgreich eine Event-Trigger-Funktion erstellt, einen DDL Event Trigger eingerichtet, den Trigger getestet und den Trigger gelöscht.

Verlassen Sie die psql-Shell, indem Sie \q eingeben und die Eingabetaste drücken.

Zusammenfassung

In diesem Lab (dieser Übung) haben Sie gelernt, wie man einen Event Trigger (Ereignis-Trigger) in PostgreSQL erstellt. Sie begannen mit der Erstellung einer Event-Trigger-Funktion, log_ddl_event, die Details von Datenbankereignissen in die Tabelle event_log protokolliert. Anschließend haben Sie einen DDL Event Trigger eingerichtet, um diese Funktion auszuführen, wenn eine neue Tabelle erstellt wird. Abschließend haben Sie den Trigger getestet, indem Sie eine neue Tabelle erstellt und überprüft haben, ob das Ereignis korrekt protokolliert wurde, und dann den Event Trigger gelöscht.