PostgreSQL Event Trigger Setup

Introduction

In this lab, you will learn how to set up a PostgreSQL event trigger. The goal is to create a function that logs database events, specifically table creation, to a dedicated log table.

You will create an event trigger function named log_ddl_event that inserts event details into the event_log table. Then, you'll set up a DDL event trigger to execute this function whenever a new table is created. Finally, you'll test the trigger by creating a new table and verifying that the event is logged correctly, and then drop the event trigger.

Skills Graph

Create the Event Log Table and Trigger Function

In this step, you will create the event_log table and the log_ddl_event function. The event_log table will store the details of the database events, and the log_ddl_event function will be executed when a specific event occurs.

First, connect to the PostgreSQL database as the postgres user:

sudo -u postgres psql

Now, create the event_log table by executing the following SQL command:

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

This command creates a table named event_log with columns to store the event time, event type, schema name, object name, and command tag.

Next, create the event trigger function log_ddl_event. Execute the following SQL command:

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;

This command creates a function named log_ddl_event. Let's break down what this function does:

• CREATE OR REPLACE FUNCTION log_ddl_event(): Creates or replaces a function named log_ddl_event.
• RETURNS event_trigger: Specifies that the function is an event trigger function.
• AS $$ ... $$ LANGUAGE plpgsql: Defines the function body using the plpgsql language.
• INSERT INTO event_log ...: Inserts a new row into the event_log table with the event details.
• NOW(): Gets the current timestamp.
• TG_EVENT: A special variable containing the type of event that triggered the function (e.g., CREATE TABLE).
• TG_TABLE_SCHEMA: A special variable containing the schema name of the object that triggered the function.
• TG_TABLE_NAME: A special variable containing the name of the object that triggered the function.
• TG_TAG: A special variable containing the command tag of the event (e.g., CREATE TABLE).

After executing the above commands, you should see CREATE TABLE and CREATE FUNCTION in the output, indicating that the table and function have been created successfully.

Create the DDL Event Trigger

In this step, you will create a DDL event trigger that executes the log_ddl_event function whenever a new table is created.

Execute the following SQL command in the psql shell:

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

This command creates an event trigger named log_table_creation. Let's break down what this trigger does:

• CREATE EVENT TRIGGER log_table_creation: Creates an event trigger named log_table_creation.
• ON ddl_command_end: Specifies that the trigger will fire at the end of a DDL command.
• WHEN TAG IS 'CREATE TABLE': Specifies that the trigger will only fire when the command tag is CREATE TABLE.
• EXECUTE PROCEDURE log_ddl_event(): Specifies that the log_ddl_event function should be executed when the trigger fires.

After executing the above command, you should see CREATE EVENT TRIGGER in the output.

Test the Trigger

Now, let's test the event trigger by creating a new table and verifying that the event is logged in the event_log table.

Create a new table named test_table by executing the following SQL command:

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

This command creates a table named test_table with an id column and a name column. You should see CREATE TABLE in the output.

Next, verify that the event trigger has logged the table creation event to the event_log table. Execute the following SQL command:

SELECT * FROM event_log;

This command selects all rows from the event_log table. You should see a row with the details of the CREATE TABLE event for test_table. The output should look similar to this:

       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)

The event_time will be the time when you created the table. The event_type will be ddl_command_end. The schema_name will be public (or the schema you created the table in). The object_name will be test_table. The command_tag will be CREATE TABLE.

Drop the Trigger and Tables

In this step, you will drop the event trigger and the tables you created.

First, drop the event trigger log_table_creation by executing the following SQL command:

DROP EVENT TRIGGER log_table_creation;

You should see DROP EVENT TRIGGER in the output.

Next, drop the test_table and the event_log table by executing the following SQL commands:

DROP TABLE test_table;
DROP TABLE event_log;

Finally, drop the log_ddl_event function:

DROP FUNCTION log_ddl_event();

This completes the lab. You have successfully created an event trigger function, set up a DDL event trigger, tested the trigger, and dropped the trigger.

Exit the psql shell by typing \q and pressing Enter.

Summary

In this lab, you learned how to create an event trigger in PostgreSQL. You started by creating an event trigger function, log_ddl_event, which logs details of database events into the event_log table. You then set up a DDL event trigger to execute this function whenever a new table is created. Finally, you tested the trigger by creating a new table and verifying that the event was logged correctly, and then dropped the event trigger.