Inspecting Command Tags and Events in event triggersWhile preparing an example for a course of mine about event triggers, I thought I’ve never proposed a catch-all event trigger debugging use case. So, here it is.
Event Triggers are a powerful mechanism that PostgreSQL provides to react to database schema changes, like table or column addition and deletion, object creation, and so on. The official documentatio already presents a couple of example about dropping objects or rewriting tables, so my little example is about more common commands. I create the following function:
It is quite simple to understand what it does: every time the function is triggered, it asks or the tuples out of the special function
CREATE OR REPLACE FUNCTION f_event_trigger_demo() RETURNS EVENT_TRIGGER AS $code$ DECLARE event_tuple record; BEGIN RAISE INFO 'Event trigger function called '; FOR event_tuple IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP RAISE INFO 'TAG [%] COMMAND [%]', event_tuple.command_tag, event_tuple.object_type; END LOOP; END $code$ LANGUAGE plpgsql;
pg_event_trigger_ddl_commands(), that provides one tuple for every single command executed. Why multiple tuples? Because you could execute one command that explodes into different sub-commands.
Than, simply, the function does print the command tag and the object type.
Usually command tags are uppercase, while object types are lowercase.
The trigger can be created as follows:
It is now simple enough to test the trigger:
testdb=# create event trigger tr_demo on ddl_command_end execute function f_event_trigger_demo();
You can compare the output of the trigger function with the event trigger firing matrix to get an idea of what you can “catch”.
testdb=# create table foo(); INFO: Event trigger function called INFO: TAG [CREATE TABLE] COMMAND [table] CREATE TABLE testdb=# alter table foo add column i int default 0; INFO: Event trigger function called INFO: TAG [ALTER TABLE] COMMAND [table] ALTER TABLE testdb=# create index idx_foo on foo(i); INFO: Event trigger function called INFO: TAG [CREATE INDEX] COMMAND [index] CREATE INDEX testdb=# ALTER TABLE foo RENAME TO baz; INFO: Event trigger function called INFO: TAG [ALTER TABLE] COMMAND [table] ALTER TABLE
One last note: why have I attached the trigger to the
ddl_command_end? Having a look at the event trigger firing matrix it looks like you can attach the trigger to either the
ddl_command_endwith the very same result, but the fact is that the function
pg_event_trigger_ddl_command()works only on the end side of an event. The reason, as already explained, is that only approaching the end the system kows what a command has been exploded into.