PostgreSQL Event Trigger Setup

Introduction

In this lab, you will learn how to set up an event trigger in PostgreSQL. Event triggers capture Data Definition Language (DDL) events, such as creating, altering, or dropping database objects.

You will create a dedicated log table and a special function to record these events. Then, you will create an event trigger that fires after a CREATE TABLE command, logging the details of the new table. This hands-on exercise will guide you through creating the necessary components, testing the trigger's functionality, and cleaning up the database objects afterward.

Create a Log Table and a Trigger Function

In this step, you will create the necessary database objects for logging DDL events: a table to store the logs and a function that the trigger will execute.

First, open a terminal and connect to the PostgreSQL database using the psql interactive shell. You will perform the subsequent SQL operations within this shell.

sudo -u postgres psql

You should now see the PostgreSQL prompt, which looks like postgres=#.

Next, create a table named event_log to store the details of each DDL event. This table will have columns for the event time, type, schema, object name, and the command that was executed.

Execute the following SQL command in the psql shell:

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

Upon successful execution, PostgreSQL will respond with:

CREATE TABLE

Now, create the function that will be triggered by the DDL event. This function, log_ddl_event, will insert a new record into the event_log table.

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;

Let's break down this function:

• RETURNS event_trigger: Specifies that this is a special type of function designed to be used with event triggers.
• AS $$ ... $$ LANGUAGE plpgsql: Defines the function body using the plpgsql procedural language.
• INSERT INTO event_log ...: This is the core action, which inserts a new row into our log table.
• now(): A standard function that returns the current timestamp.
• tg_event, tg_tag: Special variables available inside a trigger function that provide context about the event, such as ddl_command_end and CREATE TABLE.
• pg_event_trigger_ddl_commands(): A function that returns a set of rows describing the DDL commands that were executed. We use it to get the schema_name and objid (object identifier) of the object being created.

After running the command, you will see the following confirmation:

CREATE FUNCTION

You have now successfully set up the foundational components for your event trigger.

Create the DDL Event Trigger

With the log table and function in place, you can now create the event trigger itself. This trigger will monitor for specific DDL commands and execute your logging function when they occur.

In the same psql shell, execute the following command to create an event trigger named log_table_creation:

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

Let's examine the components of this statement:

• CREATE EVENT TRIGGER log_table_creation: This names your new event trigger.
• ON ddl_command_end: This specifies the timing of the trigger. It will fire after a DDL command has finished executing.
• WHEN TAG IN ('CREATE TABLE'): This is a filter condition. The trigger will only activate if the command tag matches CREATE TABLE. You can add other tags like ALTER TABLE or DROP TABLE here as well.
• EXECUTE FUNCTION log_ddl_event(): This specifies which function to run when the trigger's conditions are met.

After executing the command, PostgreSQL will confirm its creation:

CREATE EVENT TRIGGER

Your event trigger is now active and will log every new table creation.

Test the Event Trigger

Now it's time to test if the event trigger works as expected. You will create a new table and then check the event_log to see if the creation event was recorded.

First, create a simple table named employees:

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

The CREATE TABLE command will execute normally, and you will see the standard confirmation:

CREATE TABLE

Behind the scenes, this action should have fired your log_table_creation trigger. To verify this, query the event_log table to see its contents:

SELECT schema_name, object_name, command_tag FROM event_log;

The output will show multiple records. This is because creating a table with a serial PRIMARY KEY also implicitly creates a sequence and a primary key constraint. The event trigger captures all related DDL commands, resulting in multiple entries in the 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)

This result confirms that your event trigger is functioning correctly and logging the table creation along with its dependent objects.

Clean Up the Database Objects

It is good practice to remove database objects that are no longer needed. In this final step, you will drop the event trigger, the tables, and the function you created during this lab.

First, remove the event trigger:

DROP EVENT TRIGGER log_table_creation;

You will see the confirmation message:

DROP EVENT TRIGGER

Next, drop the two tables you created, employees and event_log:

DROP TABLE employees;
DROP TABLE event_log;

PostgreSQL will confirm each action:

DROP TABLE
DROP TABLE

Finally, remove the trigger function:

DROP FUNCTION log_ddl_event();

You will see the final confirmation:

DROP FUNCTION

You have now successfully cleaned up all the objects created in this lab. To exit the psql shell and return to the regular terminal prompt, type \q and press Enter.

\q

Summary

In this lab, you have learned how to implement event triggers in PostgreSQL to monitor DDL activities. You created a log table to store event data and a PL/pgSQL function to populate it. You then defined an event trigger that specifically captures CREATE TABLE events, linking it to your logging function. By testing the trigger and verifying the log entry, you gained practical experience in how these components work together. Finally, you practiced good database management by cleaning up the trigger, tables, and function you created.