Re: Event Triggers: adding information

On 22 January 2013 14:47, Thom Brown <thom(at)linux(dot)com> wrote:
> On 22 January 2013 14:45, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr> wrote:
>> Thom Brown <thom(at)linux(dot)com> writes:
>>> Ah, in that case, the docs are wrong:
>>> http://www.postgresql.org/docs/devel/static/sql-createeventtrigger.html
>>
>> Oh. It's missing the comma and applying the AND at the wrong level,
>> here's a fix:
>>
>> diff --git a/doc/src/sgml/ref/create_event_trigger.sgml b/doc/src/sgml/ref/create_event_trigger.sgml
>> index 040df11..3088ffa 100644
>> --- a/doc/src/sgml/ref/create_event_trigger.sgml
>> +++ b/doc/src/sgml/ref/create_event_trigger.sgml
>> @@ -23,7 +23,7 @@ PostgreSQL documentation
>> <synopsis>
>> CREATE EVENT TRIGGER <replaceable class="PARAMETER">name</replaceable>
>> ON <replaceable class="PARAMETER">event</replaceable>
>> - [ WHEN <replaceable class="PARAMETER">filter_variable</replaceable> IN (filter_value [ AND ... ] ) ]
>> + [ WHEN <replaceable class="PARAMETER">filter_variable</replaceable> IN (filter_value [, ... ]) [ AND ... ] ]
>> EXECUTE PROCEDURE <replaceable class="PARAMETER">function_name</replaceable>()
>> </synopsis>
>> </refsynopsisdiv>
>
> Okay, that makes sense now. :)

ALTER INDEX is missing from the event trigger matrix.

When renaming a column on a foreign table, tg_tag reports 'ALTER
TABLE' instead of 'ALTER FOREIGN TABLE'. It doesn't do this for any
other ALTER FOREIGN TABLE operation, including altering, adding or
dropping a column.

DROP OWNED BY doesn't fire any type of event trigger. Is this right?

And also I can't get ddl_command_end to do anything:

test=# CREATE OR REPLACE FUNCTION public.cmd_trg_info()
test-# RETURNS event_trigger
test-# LANGUAGE plpgsql
test-# AS $function$
test$# BEGIN
test$# RAISE NOTICE E'Command trigger: tg_event=\'%\' tg_tag=\'%\'',
tg_event, tg_tag;
test$# END; $function$;
CREATE FUNCTION
test=# CREATE EVENT TRIGGER cmd_trg_before_create_table ON
ddl_command_end WHEN TAG IN ('CREATE TABLE') EXECUTE PROCEDURE
cmd_trg_info();
CREATE EVENT TRIGGER
test=# CREATE TABLE test4 AS SELECT 1::int id, ''::text test;
SELECT 1
test=#

Tried this with every possible command and the corresponding trigger
never fires. Was this actually tested or am I missing something? :S

Also, I'm assuming the 'ANY COMMAND' special tag is supposed to be removed?

postgres=# CREATE EVENT TRIGGER cmd_trg_before_any_command ON
ddl_command_start WHEN TAG IN ('ANY COMMAND') EXECUTE PROCEDURE
cmd_trg_info_any();
ERROR: filter value "ANY COMMAND" not recognized for filter variable "tag"

... but it lets me use it for ddl_command_end triggers:

postgres=# CREATE EVENT TRIGGER cmd_trg_after_any_command ON
ddl_command_end WHEN TAG IN ('ANY COMMAND') EXECUTE PROCEDURE
cmd_trg_info();
CREATE EVENT TRIGGER

This doesn't actually fire anyway due to the issue I mentioned above
where ddl_command_end doesn't appear to be functional.

However, I *can* get ddl_command_end to run on any command by omitting
the WHEN clause, but this is the only scenario where I can get a
trigger firing when using ddl_command_end:

postgres=# CREATE EVENT TRIGGER cmd_trg_after_any_command_all ON
ddl_command_end EXECUTE PROCEDURE cmd_trg_info();
CREATE EVENT TRIGGER
postgres=# CREATE TABLE moooo2(id serial);
NOTICE: Command trigger: tg_event='ddl_command_end' tg_tag='CREATE TABLE'
CREATE TABLE

--
Thom