Waiting for 9.4 – PL/Perl: Add event trigger support

On 11th of December, Peter Eisentraut committed patch:

PL/Perl: Add event trigger support
 
From: Dimitri Fontaine <dimitri@2ndQuadrant.fr>

In PostgreSQL 9.3 we got EVENT triggers.

To remind – these are triggers that get executed, not on change to table, but on specific event – related to general schema. Creation of new object. Altering state of the object. Dropping it.

Let's see how that works. First I need to have the language:

$ CREATE extension plperlu;
CREATE EXTENSION

Now, I can write some simple, testing trigger:

$ CREATE FUNCTION event_test_pl() RETURNS event_trigger AS $$
>> USE DATA::Dumper;
>> elog( NOTICE, Dumper($_TD) );
>> $$ LANGUAGE plperlu;
CREATE FUNCTION
 
$ CREATE event TRIGGER test_before ON ddl_command_start EXECUTE PROCEDURE event_test_pl();
CREATE EVENT TRIGGER
 
$ CREATE event TRIGGER test_after ON ddl_command_end EXECUTE PROCEDURE event_test_pl();
CREATE EVENT TRIGGER

And now for the test:

$ CREATE TABLE z (i int4);
NOTICE:  $VAR1 = {
          'tag' => 'CREATE TABLE',
          'event' => 'ddl_command_start'
        };
 
CONTEXT:  PL/Perl FUNCTION "event_test_pl"
NOTICE:  $VAR1 = {
          'tag' => 'CREATE TABLE',
          'event' => 'ddl_command_end'
        };
 
CONTEXT:  PL/Perl FUNCTION "event_test_pl"
CREATE TABLE

and dropping:

$ DROP TABLE z;
NOTICE:  $VAR1 = {
          'tag' => 'DROP TABLE',
          'event' => 'ddl_command_start'
        };
 
CONTEXT:  PL/Perl FUNCTION "event_test_pl"
NOTICE:  $VAR1 = {
          'tag' => 'DROP TABLE',
          'event' => 'ddl_command_end'
        };
 
CONTEXT:  PL/Perl FUNCTION "event_test_pl"
DROP TABLE

OK. Works. Just like with PL/pgSQL triggers – there is not enough information passed to trigger function to be able to do something really cool, but that's just the beginning.

Of course even now we can detect what is added/dropped, by simply checking catalogs state in “ddl_command_start" and “ddl_command_end" triggers, and comparing results. Since PL/Perl has %_SHARED, it shouldn't be all that problematic:

CREATE OR REPLACE FUNCTION event_test_pl() RETURNS event_trigger AS $$
USE DATA::Dumper;
my $rv     = spi_exec_query("SELECT relname FROM pg_class where relkind = 'r'");
my $tables = [ map { $_->{'relname'} } @{ $rv->{'rows'} } ];
 
IF ( $_TD->{'event'} eq 'ddl_command_start' ) {
    $_SHARED{'ddl_before'} = $tables;
    RETURN 1;
}
 
my $before_as_hash = {};
$before_as_hash->{$_} = 1 FOR @{ $_SHARED{'ddl_before'} };
my $after_as_hash = {};
$after_as_hash->{$_} = 1 FOR @{ $tables };
 
FOR my $table ( @{ $_SHARED{'ddl_before'} } ) {
    elog( NOTICE, 'Dropped table: ' . $table ) unless $after_as_hash->{ $table };
}
FOR my $table ( @{ $tables } ) {
    elog( NOTICE, 'Created table: ' . $table ) unless $before_as_hash->{ $table };
}
$$ LANGUAGE plperlu;

This trigger can detect table creation/dropping:

$ CREATE TABLE depesz_test (whatever text);
NOTICE:  Created TABLE: depesz_test
CONTEXT:  PL/Perl FUNCTION "event_test_pl"
CREATE TABLE
 
$ DROP TABLE depesz_test ;
NOTICE:  Dropped TABLE: depesz_test
CONTEXT:  PL/Perl FUNCTION "event_test_pl"
DROP TABLE

Of course it's not beautiful, but it works. And, in time, we'll get more features.