Event triggers in PostgreSQL are really useful. They help the database respond to things that happen at the database level. This can be things like creating tables or altering objects. It can also include things such as dropping objects or when users log in.
Event triggers are different from triggers. Regular triggers work with the data in tables. Event triggers work with things like creating tables and other events that happen in the database.
People often use event triggers to keep track of changes to the schema. They also use them to log what is happening in the database. They use them to make sure the rules are being followed.
We can see the default value of event_trigger in Postgres like this.
show event_triggers ;
Result:
event_triggers
----------------
on
(1 row)
Check the metadata from pg_settings.
select * from pg_settings where name = 'event_triggers';
Result:
-[ RECORD 1 ]---+----------------------------------------------------------------------
name | event_triggers
setting | on
unit |
category | Client Connection Defaults / Statement Behavior
short_desc | Enables event triggers.
extra_desc | When enabled, event triggers will fire for all applicable statements.
context | superuser
vartype | bool
source | default
min_val |
max_val |
enumvals |
boot_val | on
reset_val | on
sourcefile |
sourceline |
pending_restart | f
Create a function named log_ddl and make it return an event trigger like this.
CREATE OR REPLACE FUNCTION log_ddl()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'DDL Event Fired';
END;
$$;
Check the metadata of this created function.
\df+ log_ddl
Result :
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Leakproof? | Access privileges | Language | Internal name | Description
--------+---------+------------------+---------------------+------+------------+----------+----------+----------+------------+-------------------+----------+---------------+-------------
public | log_ddl | event_trigger | | func | volatile | unsafe | postgres | invoker | no | | plpgsql | |
(1 row)
Now create an event trigger based on the ddl_command_end and execute the function we created named log_ddl.
CREATE EVENT TRIGGER ddl_logger
ON ddl_command_end
EXECUTE FUNCTION log_ddl();
In PostgreSQL, we have a catalogue named pg_event_trigger to see the metadata of the created event triggers.
select * from pg_event_trigger ;
Result:
oid | evtname | evtevent | evtowner | evtfoid | evtenabled | evttags
-------+------------+-----------------+----------+---------+------------+---------
73432 | ddl_logger | ddl_command_end | 10 | 73431 | O |
(1 row)
Now create a table and check whether the logs from the event trigger are showing or not.
CREATE TABLE emp(id int);
Result:
NOTICE: DDL Event Fired
CREATE TABLE
Now we can see that the message from the event trigger is showing after the table creation.
Now execute an ALTER command and check the result.
ALTER TABLE emp
ADD COLUMN name text;
Result :
NOTICE: DDL Event Fired
ALTER TABLE
Now create an index to check the log message.
CREATE INDEX idx_emp
ON emp(id);
Result :
NOTICE: DDL Event Fired
CREATE INDEX
Now try to create a view.
CREATE VIEW emp_view AS
SELECT * FROM emp;
Result:
NOTICE: DDL Event Fired
CREATE VIEW
Create a schema and check the log message.
CREATE SCHEMA demo;
Result:
NOTICE: DDL Event Fired
CREATE SCHEMA
Create a sequence and check again.
CREATE SEQUENCE seq1;
Result:
NOTICE: DDL Event Fired
CREATE SEQUENCE
Create a function and check the result.
CREATE FUNCTION hello()
RETURNS int
LANGUAGE sql
AS $$
SELECT 1;
$$;
Result:
NOTICE: DDL Event Fired
CREATE FUNCTION
Now drop the table and check the result.
DROP TABLE emp cascade;
Result:
NOTICE: drop cascades to view emp_view
NOTICE: DDL Event Fired
DROP TABLE
Now, based on the above execution, the log messages from the event triggers are properly showing after each command execution, like create table, alter table, create view, drop view, etc.
Now, temporarily set off to the event triggers.
SET event_triggers = off;
Check whether the value of the event triggers is properly changed or not.
SHOW event_triggers;
Result:
event_triggers
----------------
off
(1 row)
Now try to create a table and observe its result.
CREATE TABLE test(id int);
Result :
CREATE TABLE
Now we can see the event trigger is not working.
Now try the alter command.
ALTER TABLE test
ADD COLUMN age int;
Result :
ALTER TABLE
Now check the metadata from pg_event_trigger.
SELECT * FROM pg_event_trigger;
Result :
oid | evtname | evtevent | evtowner | evtfoid | evtenabled | evttags
-------+------------+-----------------+----------+---------+------------+---------
73432 | ddl_logger | ddl_command_end | 10 | 73431 | O |
(1 row)
We can see the metadata of the event trigger by the name of the event trigger, the event trigger type, and the status of the trigger, which means it is disabled or enabled.
We can easily disable the event trigger by using the ALTER command with the disable option.
ALTER EVENT TRIGGER ddl_logger
DISABLE;
Now check the metadata again and see the active status of the trigger.
SELECT *
FROM pg_event_trigger;
Result :
oid | evtname | evtevent | evtowner | evtfoid | evtenabled | evttags
-------+------------+-----------------+----------+---------+------------+---------
73432 | ddl_logger | ddl_command_end | 10 | 73431 | D |
(1 row)
Now our Postgres has nearly 5 types of predefined events. When you create an event trigger and press the tab key, you can see the names of the predefined events.
CREATE EVENT TRIGGER my_trigge ON
Result:
DDL_COMMAND_END DDL_COMMAND_START LOGIN SQL_DROP TABLE_REWRITE
Ddl_command_start
Purpose:
This occurs before a DDL command executes.
Ddl_command_end
Purpose:
This occurs after a DDL command succeeds.
Login
Purpose:
This occurs after a successful database login.
Sql_drop
Purpose:
This occurs when SQL objects are being dropped.
Table_rewrite
Purpose:
This occurs before PostgreSQL rewrites a table.
Now try to use each type of predefined event in postgres. At first, let’s try the ddl_commad_start.
CREATE OR REPLACE FUNCTION log_ddl_start()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'DDL command is about to start';
END;
$$;
CREATE EVENT TRIGGER ddl_start_trigger
ON ddl_command_start
EXECUTE FUNCTION log_ddl_start();
Now try to create a table and check its result.
CREATE TABLE test1(id int);
Result:
NOTICE: DDL command is about to start
Now create a function with a different log message and use the event named ddl_command_end.
CREATE OR REPLACE FUNCTION log_ddl_end()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'DDL command completed successfully';
END;
$$;
CREATE EVENT TRIGGER ddl_end_trigger
ON ddl_command_end
EXECUTE FUNCTION log_ddl_end();
Result:
NOTICE: DDL command is about to start
CREATE FUNCTION
CREATE EVENT TRIGGER
Now execute an alter operation and check its result.
ALTER TABLE test1 ADD COLUMN name text;
Result:
NOTICE: DDL command is about to start
NOTICE: DDL command completed successfully
ALTER TABLE
Now we can see the log message from the event trigger.
Now create another function with a different log message and use the event named sql_drop.
CREATE OR REPLACE FUNCTION log_drop()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'An object is being dropped';
END;
$$;
CREATE EVENT TRIGGER drop_trigger
ON sql_drop
EXECUTE FUNCTION log_drop();
Result:
NOTICE: DDL command is about to start
NOTICE: DDL command completed successfully
CREATE FUNCTION
CREATE EVENT TRIGGER
Now try a drop command and check its result.
DROP TABLE test1;
Result:
NOTICE: DDL command is about to start
NOTICE: An object is being dropped
NOTICE: DDL command completed successfully
DROP TABLE
At last, let’s check the table_rewrite event.
Create the function with a log message.
CREATE OR REPLACE FUNCTION log_rewrite()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'Table rewrite is happening';
END;
$$;
Create the event trigger.
CREATE EVENT TRIGGER rewrite_trigger
ON table_rewrite
EXECUTE FUNCTION log_rewrite();
NOTICE: DDL command is about to start
NOTICE: DDL command completed successfully
CREATE FUNCTION
CREATE EVENT TRIGGER
Now create a sample table and insert values.
CREATE TABLE emp(
id int,
salary int
);
INSERT INTO emp
SELECT g, g*1000
FROM generate_series(1,1000) g;
Result:
NOTICE: DDL command is about to start
NOTICE: DDL command completed successfully
CREATE TABLE
INSERT 0 1000
Now try an ALTER command for changing the data type.
ALTER TABLE emp
ALTER COLUMN salary TYPE bigint;
Result:
NOTICE: DDL command is about to start
NOTICE: Table rewrite is happening
NOTICE: DDL command completed successfully
ALTER TABLE
Now we can see the log message related to the table_rewrite event.
At last, create the event trigger based on login.
Create the function.
CREATE OR REPLACE FUNCTION log_login()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'User % has logged in', session_user;
END;
$$;
Create the event trigger by using the option login.
CREATE EVENT TRIGGER login_trigger
ON login
EXECUTE FUNCTION log_login();
Result:
NOTICE: DDL command is about to start
NOTICE: DDL command completed successfully
CREATE FUNCTION
CREATE EVENT TRIGGER
Now log in to postgres.
psql -U postgres
Result:
NOTICE: User postgres has logged in
psql (18.4 (Ubuntu 18.4-1.pgdg22.04+1))
Type "help" for help.
Now we can see the log message.
Now check all the trigger metadata from the catalogue named pg_event_trigger.
SELECT evtname,
evtevent,
evtenabled
FROM pg_event_trigger;
Result:
evtname | evtevent | evtenabled
-------------------+-------------------+------------
ddl_logger | ddl_command_end | D
ddl_start_trigger | ddl_command_start | O
ddl_end_trigger | ddl_command_end | O
drop_trigger | sql_drop | O
rewrite_trigger | table_rewrite | O
login_trigger | login | O
(6 rows)
Event triggers are a useful thing. They let you do custom things when certain database events happen. You can attach functions to events like ddl_command_start, ddl_command_end, sql_drop, table_rewrite, and login. This way, you can keep an eye on changes to the schema, record things that happen, or apply your own rules without changing the application code. If you know when each event happens, it is easier to pick the trigger for what you need. This helps you make database administration and auditing solutions. Event triggers are important for database administration and auditing solutions.