Inspecting Command Tags and Events in event triggers
While 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:
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:
testdb=# create event trigger tr_demo on ddl_command_end execute function f_event_trigger_demo();
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_start
or ddl_command_end
with 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.