https://www.percona.com/blog/wp-content/uploads/2023/03/lucas.speyer_a_postgresql_texture_like_an_elephant_cb75dd38-d342-444a-b3a3-c9b8a348a816-150×150.png
PostgreSQL provides a powerful mechanism for implementing event-driven actions using triggers. Triggers on Data Definition Language (DDL) events are a powerful feature of PostgreSQL that allows you to perform additional actions in response to changes to the database schema. DDL events include operations such as CREATE, ALTER, and DROP statements on tables, indexes, and other database objects. In this blog post, we will explore using triggers on DDL events in PostgreSQL to implement custom logic and automate database management tasks.
Creating event-based triggers
To create an event-based trigger in PostgreSQL, first, create a trigger function that defines the logic to be executed when the trigger fires. The trigger function can be written in PL/SQL or PL/Python, or any language supported by PostgreSQL.
Trigger function can be created in the same way as we create any user-defined function except that it returns event_trigger variable unlike returning the normal datatypes:
CREATE OR REPLACE FUNCTION
RETURNS event_trigger AS
$$
DECLARE
-- Declare variables if needed
BEGIN
-- Function body
-- Perform desired actions when the trigger fires
END;
$$
LANGUAGE plpgsql;
Once the trigger function is created, the trigger can be created that is associated with a specific event. Unlike normal triggers (which are executed for INSERT, UPDATE, and DELETE kinds of DML operations) that are created on specific tables, event-based triggers are created for DDL events and not on a particular table.
CREATE EVENT TRIGGER trigger_name
[ ON event_trigger_event ]
[ WHEN filter_condition ]
EXECUTE FUNCTION trigger_function_name();
In this syntax, event_trigger_event can be any of the following events, which are described in more detail in PostgreSQL documentation.
- ddl_command_start,
- ddl_command_end,
- sql_drop and
- table_rewrite
This syntax will be clearer after seeing the example in the next sections.
Using triggers on DDL events
Triggers on DDL events can be used for a wide range of purposes and database management tasks. Here are some examples of how to use DDL triggers:
- Log schema changes: One can use DDL triggers to log all schema changes, providing an audit trail of who made the changes and when.
- Automate database management tasks: One can use DDL triggers to automate routine database management tasks, such as creating indexes or updating views.
- Enforce naming conventions: One could use a DDL trigger to enforce naming conventions for tables and columns, ensuring that all objects are named consistently.
Let’s create a few triggers that help us understand all the above usages of event triggers.
Before creating the trigger, let’s create the table which will log all the DDL statements:
CREATE TABLE ddl_log (
id integer PRIMARY KEY,
username TEXT,
object_tag TEXT,
ddl_command TEXT,
timestamp TIMESTAMP
);
CREATE SEQUENCE ddl_log_seq;
Let’s create the event trigger function, which will insert the data into the above table:
CREATE OR REPLACE FUNCTION log_ddl_changes()
RETURNS event_trigger AS $$
BEGIN
INSERT INTO ddl_log
(
id,
username,
object_tag,
ddl_command,
Timestamp
)
VALUES
(
nextval('ddl_log_seq'),
current_user,
tg_tag,
current_query(),
current_timestamp
);
END;
$$ LANGUAGE plpgsql;
Let’s finally create the trigger, which will call the trigger function created above:
CREATE EVENT TRIGGER log_ddl_trigger
ON ddl_command_end
EXECUTE FUNCTION log_ddl_changes();
Let’s create a test table and check if we get the entry in the ddl_log table or not:
demo=# create table test (t1 numeric primary key);
CREATE TABLE
demo=# select * from ddl_log;
id | username | object_tag | ddl_command | timestamp
----+----------+--------------+---------------------------------------------+----------------------------
1 | postgres | CREATE TABLE | create table test (t1 numeric primary key); | 2023-06-02 15:24:54.067929
(1 row)
demo=# drop table test;
DROP TABLE
demo=#
demo=# select * from ddl_log;
id | username | object_tag | ddl_command | timestamp
----+----------+--------------+---------------------------------------------+----------------------------
1 | postgres | CREATE TABLE | create table test (t1 numeric primary key); | 2023-06-02 15:24:54.067929
2 | postgres | DROP TABLE | drop table test; | 2023-06-02 15:25:14.590444
(2 rows)
In this way, schema changes can be logged using the above event trigger code.
Even though it is not a rule of thumb, in my experience, it has been seen that mostly foreign key columns are used for the joining condition in queries. If we have indexes on such columns, it automizes the index creation on foreign key columns. In many applications, there are some naming conventions for table names. Let’s see an example that throws an error if the table name does not start with ‘tbl_’. Similar code can be developed for any object. Let’s check the code which will help in achieving this use case.
Common Trigger function for naming conventions and index creation — this code has been custom developed through my experience working in PostgreSQL and researching online.
CREATE OR REPLACE FUNCTION chk_tblnm_crt_indx()
RETURNS event_trigger AS
$$
DECLARE
obj record;
col record;
table_name text;
column_name text;
BEGIN
FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() WHERE command_tag = 'CREATE TABLE'
LOOP
-- Check if the table name starts with tbl_
table_name := obj.objid::regclass;
IF table_name NOT LIKE 'tbl_%' THEN
RAISE EXCEPTION 'Table name must start with tbl_';
END IF;
-- Check if there is any foreign key then create index
FOR col IN
SELECT a.attname AS column_name
FROM pg_constraint AS c
CROSS JOIN LATERAL unnest(c.conkey) WITH ORDINALITY AS k(attnum, n)
JOIN pg_attribute AS a
ON k.attnum = a.attnum AND c.conrelid = a.attrelid
WHERE c.contype = 'f' AND c.conrelid = obj.objid::regclass
LOOP
EXECUTE format('CREATE INDEX idx_%s_%s ON %s (%s)', table_name,col.column_name, table_name, col.column_name);
RAISE NOTICE 'INDEX idx_%_% ON % (%) has been created on foreign key column', table_name,col.column_name, table_name, col.column_name;
END LOOP;
END LOOP;
END;
$$ LANGUAGE plpgsql;
Let’s finally create the trigger which will call this event trigger function:
CREATE EVENT TRIGGER chk_tblnm_crt_indx_trigger
ON ddl_command_end
EXECUTE FUNCTION chk_tblnm_crt_indx();
Let’s create a table that does not start with ‘tbl_’ and check how it gives an error:
demo=# create table dept (dept_id numeric primary key, dept_name varchar);
ERROR: Table name must start with tbl_
CONTEXT: PL/pgSQL function chk_tblnm_crt_indx() line 21 at RAISE
demo=#
demo=# create table tbl_dept (dept_id numeric primary key, dept_name varchar);
CREATE TABLE
Now, let’s create another table that references the tbl_dept table to check if an index is created automatically for a foreign key column or not.
demo=# create table tbl_emp(emp_id numeric primary key, emp_name varchar, dept_id numeric references tbl_dept(dept_id));
NOTICE: INDEX idx_tbl_emp_dept_id ON tbl_emp (dept_id) has been created on foreign key column
CREATE TABLE
demo=#
demo=# di idx_tbl_emp_dept_id
List of relations
Schema | Name | Type | Owner | Table
--------+---------------------+-------+----------+---------
public | idx_tbl_emp_dept_id | index | postgres | tbl_emp
(1 row)
As per the output of di, we can see that index has been created automatically on the foreign key column.
Conclusion
Event-based triggers are a powerful feature of PostgreSQL that allows the implementation of complex business logic and helps automate database operations. By creating triggers that are associated with specific events, one can execute custom logic automatically when the event occurs, enabling one to perform additional actions and enforce business rules. With event-based triggers, one can build more robust and automated database systems that can help improve the efficiency of the data.
On the other hand, these are good for non-production environments as it might be an overhead in the production environment if the logic is too complex. In my personal opinion, if any table is populated from the application, triggers should not be created on them; such constraints should be implemented from the application side to reduce database load. At the same time, it could act as a boon for the development (or non-production) environment to follow best practices and recommendations like who did what changes, whether proper naming conventions are used or not, and similar industry standards. In my experience, I have extensively used them for audit purposes on development environments to track the changes done by a huge team of hundreds of people.
Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community, in a single distribution, designed and tested to work together.
Download Percona Distribution for PostgreSQL Today!
Percona Database Performance Blog
