Configuración de Disparadores de Eventos en PostgreSQL

Introducción

En este laboratorio, aprenderá cómo configurar un disparador de eventos (event trigger) en PostgreSQL. El objetivo es crear una función que registre eventos de la base de datos, específicamente la creación de tablas, en una tabla de registro dedicada (event_log).

Creará una función de disparador de eventos llamada log_ddl_event que inserta detalles del evento en la tabla event_log. Luego, configurará un disparador de eventos DDL (DDL event trigger) para ejecutar esta función cada vez que se cree una nueva tabla. Finalmente, probará el disparador creando una nueva tabla y verificando que el evento se registre correctamente, y luego eliminará el disparador de eventos.

Skills Graph

Crear la Tabla de Registro de Eventos y la Función del Disparador

En este paso, creará la tabla event_log y la función log_ddl_event. La tabla event_log almacenará los detalles de los eventos de la base de datos, y la función log_ddl_event se ejecutará cuando ocurra un evento específico.

Primero, conéctese a la base de datos PostgreSQL como el usuario postgres:

sudo -u postgres psql

Ahora, cree la tabla event_log ejecutando el siguiente comando SQL:

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

Este comando crea una tabla llamada event_log con columnas para almacenar la hora del evento (event_time), el tipo de evento (event_type), el nombre del esquema (schema_name), el nombre del objeto (object_name) y la etiqueta del comando (command_tag).

A continuación, cree la función de disparador de eventos log_ddl_event. Ejecute el siguiente comando SQL:

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;

Este comando crea una función llamada log_ddl_event. Analicemos lo que hace esta función:

• CREATE OR REPLACE FUNCTION log_ddl_event(): Crea o reemplaza una función llamada log_ddl_event.
• RETURNS event_trigger: Especifica que la función es una función de disparador de eventos (event trigger function).
• AS $$ ... $$ LANGUAGE plpgsql: Define el cuerpo de la función utilizando el lenguaje plpgsql.
• INSERT INTO event_log ...: Inserta una nueva fila en la tabla event_log con los detalles del evento.
• NOW(): Obtiene la marca de tiempo actual (current timestamp).
• TG_EVENT: Una variable especial que contiene el tipo de evento que activó la función (por ejemplo, CREATE TABLE).
• TG_TABLE_SCHEMA: Una variable especial que contiene el nombre del esquema del objeto que activó la función.
• TG_TABLE_NAME: Una variable especial que contiene el nombre del objeto que activó la función.
• TG_TAG: Una variable especial que contiene la etiqueta del comando (command tag) del evento (por ejemplo, CREATE TABLE).

Después de ejecutar los comandos anteriores, debería ver CREATE TABLE y CREATE FUNCTION en la salida, lo que indica que la tabla y la función se han creado correctamente.

Crear el Disparador de Eventos DDL

En este paso, creará un disparador de eventos DDL (DDL event trigger) que ejecuta la función log_ddl_event cada vez que se crea una nueva tabla.

Ejecute el siguiente comando SQL en el shell psql:

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

Este comando crea un disparador de eventos llamado log_table_creation. Analicemos lo que hace este disparador:

• CREATE EVENT TRIGGER log_table_creation: Crea un disparador de eventos llamado log_table_creation.
• ON ddl_command_end: Especifica que el disparador se activará al final de un comando DDL.
• WHEN TAG IS 'CREATE TABLE': Especifica que el disparador solo se activará cuando la etiqueta del comando (command tag) sea CREATE TABLE.
• EXECUTE PROCEDURE log_ddl_event(): Especifica que la función log_ddl_event debe ejecutarse cuando se active el disparador.

Después de ejecutar el comando anterior, debería ver CREATE EVENT TRIGGER en la salida.

Probar el Disparador

Ahora, probemos el disparador de eventos creando una nueva tabla y verificando que el evento se registre en la tabla event_log.

Cree una nueva tabla llamada test_table ejecutando el siguiente comando SQL:

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

Este comando crea una tabla llamada test_table con una columna id y una columna name. Debería ver CREATE TABLE en la salida.

A continuación, verifique que el disparador de eventos haya registrado el evento de creación de la tabla en la tabla event_log. Ejecute el siguiente comando SQL:

SELECT * FROM event_log;

Este comando selecciona todas las filas de la tabla event_log. Debería ver una fila con los detalles del evento CREATE TABLE para test_table. La salida debería ser similar a esta:

       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)

event_time será la hora en que creó la tabla. event_type será ddl_command_end. schema_name será public (o el esquema en el que creó la tabla). object_name será test_table. command_tag será CREATE TABLE.

Eliminar el Disparador y las Tablas

En este paso, eliminará el disparador de eventos y las tablas que creó.

Primero, elimine el disparador de eventos log_table_creation ejecutando el siguiente comando SQL:

DROP EVENT TRIGGER log_table_creation;

Debería ver DROP EVENT TRIGGER en la salida.

A continuación, elimine la tabla test_table y la tabla event_log ejecutando los siguientes comandos SQL:

DROP TABLE test_table;
DROP TABLE event_log;

Finalmente, elimine la función log_ddl_event:

DROP FUNCTION log_ddl_event();

Esto completa el laboratorio. Ha creado con éxito una función de disparador de eventos, configurado un disparador de eventos DDL (DDL event trigger), probado el disparador y eliminado el disparador.

Salga del shell psql escribiendo \q y presionando Enter.

Resumen

En este laboratorio, aprendió cómo crear un disparador de eventos (event trigger) en PostgreSQL. Comenzó creando una función de disparador de eventos, log_ddl_event, que registra los detalles de los eventos de la base de datos en la tabla event_log. Luego, configuró un disparador de eventos DDL (DDL event trigger) para ejecutar esta función cada vez que se crea una nueva tabla. Finalmente, probó el disparador creando una nueva tabla y verificando que el evento se registrara correctamente, y luego eliminó el disparador de eventos.