Can an event trigger capture DDL events for tablespace objects?

Event triggers are global to a particular database and are capable of capturing DDL events occured in the database. In Redrock Postgres, tablespaces are database-level objects. Event triggers also support DDL commands for tablespace objects. For details, see Event Trigger Firing Matrix.

In the example below, we create an event trigger that captures DDL events that occur in the database at the end of a command.

CREATE OR REPLACE FUNCTION ddl_trigger_func()
  RETURNS event_trigger AS $$
DECLARE
  ddl record;
BEGIN
  FOR ddl IN SELECT * FROM pg_event_trigger_ddl_commands()
  LOOP
    RAISE NOTICE 'ddl command: %',
      pg_ddl_command_deparse(ddl.command);
  END LOOP;
END $$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER ddl_event_trigger
    ON ddl_command_end
    EXECUTE PROCEDURE ddl_trigger_func();

Let’s create, change, and delete a tablespace dbspace.

CREATE TABLESPACE dbspace LOCATION '/data/dbs';

ALTER TABLESPACE dbspace OWNER TO david;

DROP TABLESPACE dbspace;

In the example above, we will see that when creating, changing, and deleting the tablespace dbspace, the event trigger captures the corresponding DDL event.