Configuração de Gatilho de Evento PostgreSQL

Introdução

Neste laboratório, você aprenderá como configurar um gatilho de evento (event trigger) no PostgreSQL. Gatilhos de evento capturam eventos de Linguagem de Definição de Dados (DDL), como a criação, alteração ou exclusão de objetos de banco de dados.

Você criará uma tabela de log dedicada e uma função especial para registrar esses eventos. Em seguida, criará um gatilho de evento que é acionado após um comando CREATE TABLE, registrando os detalhes da nova tabela. Este exercício prático irá guiá-lo através da criação dos componentes necessários, teste da funcionalidade do gatilho e limpeza dos objetos de banco de dados posteriormente.

Criar uma Tabela de Log e uma Função de Gatilho

Nesta etapa, você criará os objetos de banco de dados necessários para registrar eventos DDL: uma tabela para armazenar os logs e uma função que o gatilho executará.

Primeiro, abra um terminal e conecte-se ao banco de dados PostgreSQL usando o shell interativo psql. Você realizará as operações SQL subsequentes dentro deste shell.

sudo -u postgres psql

Você deverá ver o prompt do PostgreSQL, que se parece com postgres=#.

Em seguida, crie uma tabela chamada event_log para armazenar os detalhes de cada evento DDL. Esta tabela terá colunas para o horário do evento, tipo, esquema, nome do objeto e o comando que foi executado.

Execute o seguinte comando SQL no shell psql:

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

Após a execução bem-sucedida, o PostgreSQL responderá com:

CREATE TABLE

Agora, crie a função que será acionada pelo evento DDL. Esta função, log_ddl_event, inserirá um novo registro na tabela 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;

Vamos detalhar esta função:

RETURNS event_trigger: Especifica que este é um tipo especial de função projetada para ser usada com gatilhos de evento.
AS $$ ... $$ LANGUAGE plpgsql: Define o corpo da função usando a linguagem procedural plpgsql.
INSERT INTO event_log ...: Esta é a ação principal, que insere uma nova linha em nossa tabela de log.
now(): Uma função padrão que retorna o timestamp atual.
tg_event, tg_tag: Variáveis especiais disponíveis dentro de uma função de gatilho que fornecem contexto sobre o evento, como ddl_command_end e CREATE TABLE.
pg_event_trigger_ddl_commands(): Uma função que retorna um conjunto de linhas descrevendo os comandos DDL que foram executados. Usamos para obter o schema_name e objid (identificador do objeto) do objeto que está sendo criado.

Após executar o comando, você verá a seguinte confirmação:

CREATE FUNCTION

Você agora configurou com sucesso os componentes fundamentais para o seu gatilho de evento.

Criar o Gatilho de Evento DDL

Com a tabela de log e a função configuradas, você pode agora criar o próprio gatilho de evento. Este gatilho monitorará comandos DDL específicos e executará sua função de log quando eles ocorrerem.

No mesmo shell psql, execute o seguinte comando para criar um gatilho de evento chamado log_table_creation:

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

Vamos examinar os componentes desta instrução:

CREATE EVENT TRIGGER log_table_creation: Isso nomeia seu novo gatilho de evento.
ON ddl_command_end: Isso especifica o momento do gatilho. Ele será acionado após a conclusão da execução de um comando DDL.
WHEN TAG IN ('CREATE TABLE'): Esta é uma condição de filtro. O gatilho só será ativado se a tag do comando corresponder a CREATE TABLE. Você também pode adicionar outras tags como ALTER TABLE ou DROP TABLE aqui.
EXECUTE FUNCTION log_ddl_event(): Isso especifica qual função executar quando as condições do gatilho forem atendidas.

Após executar o comando, o PostgreSQL confirmará sua criação:

CREATE EVENT TRIGGER

Seu gatilho de evento agora está ativo e registrará cada nova criação de tabela.

Testar o Gatilho de Evento

Agora é hora de testar se o gatilho de evento funciona como esperado. Você criará uma nova tabela e, em seguida, verificará a event_log para ver se o evento de criação foi registrado.

Primeiro, crie uma tabela simples chamada employees:

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

O comando CREATE TABLE será executado normalmente, e você verá a confirmação padrão:

CREATE TABLE

Nos bastidores, esta ação deve ter acionado seu gatilho log_table_creation. Para verificar isso, consulte a tabela event_log para ver seu conteúdo:

SELECT schema_name, object_name, command_tag FROM event_log;

A saída mostrará vários registros. Isso ocorre porque a criação de uma tabela com serial PRIMARY KEY também cria implicitamente uma sequência e uma restrição de chave primária. O gatilho de evento captura todos os comandos DDL relacionados, resultando em várias entradas no log.

 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 seu gatilho de evento está funcionando corretamente e registrando a criação da tabela juntamente com seus objetos dependentes.

Limpar os Objetos do Banco de Dados

É uma boa prática remover objetos de banco de dados que não são mais necessários. Nesta etapa final, você removerá o gatilho de evento, as tabelas e a função que criou durante este laboratório.

Primeiro, remova o gatilho de evento:

DROP EVENT TRIGGER log_table_creation;

Você verá a mensagem de confirmação:

DROP EVENT TRIGGER

Em seguida, remova as duas tabelas que você criou, employees e event_log:

DROP TABLE employees;
DROP TABLE event_log;

O PostgreSQL confirmará cada ação:

DROP TABLE
DROP TABLE

Finalmente, remova a função do gatilho:

DROP FUNCTION log_ddl_event();

Você verá a confirmação final:

DROP FUNCTION

Agora você limpou com sucesso todos os objetos criados neste laboratório. Para sair do shell psql e retornar ao prompt do terminal regular, digite \q e pressione Enter.

\q

Resumo

Neste laboratório, você aprendeu a implementar gatilhos de evento no PostgreSQL para monitorar atividades DDL. Você criou uma tabela de log para armazenar dados de eventos e uma função PL/pgSQL para preenchê-la. Em seguida, definiu um gatilho de evento que captura especificamente eventos CREATE TABLE, vinculando-o à sua função de log. Ao testar o gatilho e verificar a entrada de log, você ganhou experiência prática em como esses componentes funcionam juntos. Finalmente, você praticou uma boa gestão de banco de dados limpando o gatilho, as tabelas e a função que você criou.