July 29th, 2012 by depesz | Tags: , , , , , | 5 comments »
Did it help? If yes - maybe you can help me? Donate BTC to 19zPa5diT2LZqGtTi8f8bfApLn8rw9zBHx

On 20th of July, Robert Haas committed patch:

Make new event trigger facility actually do something.
 
Commit 3855968f328918b6cd1401dd11d109d471a54d40 added syntax, pg_dump,
psql support, and documentation, but the triggers didn't actually fire.
With this commit, they now do.  This is still a pretty basic facility
overall because event triggers do not get a whole lot of information
about what the user is trying to do unless you write them in C; and
there's still no option to fire them anywhere except at the very
beginning of the execution sequence, but it's better than nothing,
and a good building block for future work.
 
Along the way, add a regression test for ALTER LARGE OBJECT, since
testing of event triggers reveals that we haven't got one.
 
Dimitri Fontaine and Robert Haas

This was preceded (two days earlier) by commit, also by Robert Haas, which stated:

Syntax support and documentation for event triggers.
 
They don't actually do anything yet; that will get fixed in a
follow-on commit.  But this gets the basic infrastructure in place,
including CREATE/ALTER/DROP EVENT TRIGGER; support for COMMENT,
SECURITY LABEL, and ALTER EXTENSION .. ADD/DROP EVENT TRIGGER;
pg_dump and psql support; and documentation for the anticipated
initial feature set.
 
Dimitri Fontaine, with review and a bunch of additional hacking by me.
Thom Brown extensively reviewed earlier versions of this patch set,
but there's not a whole lot of that code left in this commit, as it
turns out.

What are Event Triggers? These are triggers that run not on INSERT/UPDATE/DELETE/TRUNCATE – i.e. on DML queries, but rather on CREATE/ALTER/DROP commands – that is DDL.

So, how it works?

Commit log says:

...event triggers do not get a whole lot of information about what the user is trying to do unless you write them in C...

Well, sorry, but I will not write in C just for the benefit of the “Waiting for" series (my C skills are generally very, very limited), so I can only show these limited functions.

Docs show this function as example implementation:

CREATE OR REPLACE FUNCTION snitch() RETURNS event_trigger AS $$
BEGIN
    RAISE NOTICE 'snitch: % %', tg_event, tg_tag;
END;
$$ LANGUAGE plpgsql;
 
CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE PROCEDURE snitch();

These two variables – tg_event and tg_tag are the only things that are accessible to PL/pgSQL triggers now. So, what does it show? I created this trigger in my database, and then ran some DDLs:

$ create table z (i int4);
NOTICE:  snitch: ddl_command_start CREATE TABLE
CREATE TABLE
 
$ alter table z add column j int8;
NOTICE:  snitch: ddl_command_start ALTER TABLE
ALTER TABLE
 
$ create index q on z(j);
NOTICE:  snitch: ddl_command_start CREATE INDEX
CREATE INDEX
 
$ drop table z;
NOTICE:  snitch: ddl_command_start DROP TABLE
DROP TABLE

You might have noticed that I used “ddl_command_start" in CREATE EVENT TRIGGER statement, and it also was showed.

Currently – there are no other cases that could exist, but in future, I can imagine “ddl_command_end" added.

One important note – you cannot (currently) create triggers for DDL for shared elements (databases, tablespaces, roles).

As you can see – functionality, as accessible from PL/pgSQL, is now limited. But – you can get full functionality by writing your trigger in C (which will be very interesting options for Slony and similar tools).

All in all – its a great addition, missed by many, and long overdue. And since 9.3 development cycle started very recently – I have high hopes of functionality extensions that might be committed before 9.3 will be released.

  1. 5 comments

  2. # Thomas
    Dec 5, 2012

    Does this restriction also mean that you cannot access e.g. the name of the table that was created?

  3. Dec 5, 2012

    @Thomas: tables are not shared. So you can. Not in pl/PgSQL, though.

  4. Sep 16, 2013

    So far as I understand new feature, it cannot be fired only when one particular event occurs, e.g. ALTER FUNCTION only? It’s always fired on every DDL event?
    It could be used for sure as the basis of a version control system for db. But would be useful for any other purposes?
    Maybe I’m asking too many questions ;p But would like to know if anybody tested the performance of using those triggers?

  5. Sep 16, 2013

    @Boria:
    I don’t think such tests were made for two reasons:
    1. 9.3 has been released very recently
    2. ddl triggers are not that important performance-wise. Do you really have that many ddl commands running that performance of the triggers matters?

  6. Sep 18, 2013

    just curiosity. Wanted to know extra overhead, especially covered by partitioning mechanism.

Leave a comment