Configuración de Disparadores de Eventos en PostgreSQL

Introducción

En este laboratorio, aprenderá a configurar un disparador de eventos (event trigger) en PostgreSQL. Los disparadores de eventos capturan eventos de Lenguaje de Definición de Datos (DDL), como la creación, alteración o eliminación de objetos de base de datos.

Creará una tabla de registro dedicada y una función especial para registrar estos eventos. Luego, creará un disparador de eventos que se activa después de un comando CREATE TABLE, registrando los detalles de la nueva tabla. Este ejercicio práctico lo guiará a través de la creación de los componentes necesarios, la prueba de la funcionalidad del disparador y la limpieza de los objetos de la base de datos posteriormente.

Crear una Tabla de Registro y una Función de Disparador

En este paso, creará los objetos de base de datos necesarios para registrar eventos DDL: una tabla para almacenar los registros y una función que ejecutará el disparador.

Primero, abra una terminal y conéctese a la base de datos PostgreSQL utilizando el shell interactivo psql. Realizará las operaciones SQL subsiguientes dentro de este shell.

sudo -u postgres psql

Ahora debería ver el prompt de PostgreSQL, que se parece a postgres=#.

A continuación, cree una tabla llamada event_log para almacenar los detalles de cada evento DDL. Esta tabla tendrá columnas para la hora del evento, el tipo, el esquema, el nombre del objeto y el comando que se ejecutó.

Ejecute el siguiente comando SQL en el shell psql:

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

Tras una ejecución exitosa, PostgreSQL responderá con:

CREATE TABLE

Ahora, cree la función que será disparada por el evento DDL. Esta función, log_ddl_event, insertará un nuevo registro en la tabla 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;

Analicemos esta función:

• RETURNS event_trigger: Especifica que este es un tipo especial de función diseñada para ser utilizada con disparadores de eventos.
• AS $$ ... $$ LANGUAGE plpgsql: Define el cuerpo de la función utilizando el lenguaje procedural plpgsql.
• INSERT INTO event_log ...: Esta es la acción principal, que inserta una nueva fila en nuestra tabla de registro.
• now(): Una función estándar que devuelve la marca de tiempo actual.
• tg_event, tg_tag: Variables especiales disponibles dentro de una función de disparador que proporcionan contexto sobre el evento, como ddl_command_end y CREATE TABLE.
• pg_event_trigger_ddl_commands(): Una función que devuelve un conjunto de filas que describen los comandos DDL que se ejecutaron. La utilizamos para obtener el schema_name y objid (identificador de objeto) del objeto que se está creando.

Después de ejecutar el comando, verá la siguiente confirmación:

CREATE FUNCTION

Ahora ha configurado con éxito los componentes fundamentales para su disparador de eventos.

Crear el Disparador de Eventos DDL

Con la tabla de registro y la función en su lugar, ahora puede crear el disparador de eventos en sí. Este disparador supervisará comandos DDL específicos y ejecutará su función de registro cuando ocurran.

En el mismo shell psql, ejecute el siguiente comando para crear un disparador de eventos llamado log_table_creation:

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

Examinemos los componentes de esta declaración:

• CREATE EVENT TRIGGER log_table_creation: Esto nombra su nuevo disparador de eventos.
• ON ddl_command_end: Esto especifica el momento de activación del disparador. Se activará después de que un comando DDL haya terminado de ejecutarse.
• WHEN TAG IN ('CREATE TABLE'): Esta es una condición de filtro. El disparador solo se activará si la etiqueta del comando coincide con CREATE TABLE. También puede agregar otras etiquetas como ALTER TABLE o DROP TABLE aquí.
• EXECUTE FUNCTION log_ddl_event(): Esto especifica qué función ejecutar cuando se cumplen las condiciones del disparador.

Después de ejecutar el comando, PostgreSQL confirmará su creación:

CREATE EVENT TRIGGER

Su disparador de eventos ahora está activo y registrará cada nueva creación de tabla.

Probar el Disparador de Eventos

Ahora es el momento de probar si el disparador de eventos funciona como se espera. Creará una nueva tabla y luego verificará event_log para ver si se registró el evento de creación.

Primero, cree una tabla simple llamada employees:

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

El comando CREATE TABLE se ejecutará normalmente y verá la confirmación estándar:

CREATE TABLE

Detrás de escena, esta acción debería haber activado su disparador log_table_creation. Para verificar esto, consulte la tabla event_log para ver su contenido:

SELECT schema_name, object_name, command_tag FROM event_log;

La salida mostrará varios registros. Esto se debe a que la creación de una tabla con serial PRIMARY KEY también crea implícitamente una secuencia y una restricción de clave primaria. El disparador de eventos captura todos los comandos DDL relacionados, lo que resulta en múltiples entradas en el registro.

 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)

Este resultado confirma que su disparador de eventos está funcionando correctamente y registrando la creación de la tabla junto con sus objetos dependientes.

Limpiar los Objetos de la Base de Datos

Es una buena práctica eliminar los objetos de la base de datos que ya no se necesitan. En este paso final, eliminará el disparador de eventos, las tablas y la función que creó durante este laboratorio.

Primero, elimine el disparador de eventos:

DROP EVENT TRIGGER log_table_creation;

Verá el mensaje de confirmación:

DROP EVENT TRIGGER

A continuación, elimine las dos tablas que creó, employees y event_log:

DROP TABLE employees;
DROP TABLE event_log;

PostgreSQL confirmará cada acción:

DROP TABLE
DROP TABLE

Finalmente, elimine la función del disparador:

DROP FUNCTION log_ddl_event();

Verá la confirmación final:

DROP FUNCTION

Ahora ha limpiado con éxito todos los objetos creados en este laboratorio. Para salir del shell psql y volver al prompt de la terminal normal, escriba \q y presione Enter.

\q

Resumen

En este laboratorio, ha aprendido a implementar disparadores de eventos en PostgreSQL para monitorear actividades DDL. Creó una tabla de registro para almacenar datos de eventos y una función PL/pgSQL para poblarla. Luego definió un disparador de eventos que captura específicamente eventos CREATE TABLE, vinculándolo a su función de registro. Al probar el disparador y verificar la entrada de registro, adquirió experiencia práctica sobre cómo funcionan estos componentes juntos. Finalmente, practicó una buena gestión de bases de datos limpiando el disparador, las tablas y la función que creó.