Re: Command Triggers, v16

On 15 March 2012 18:13, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr> wrote:
> Hi,
>
> I guess it's time to start a new thread here. Please find attached
> version 16 of the command trigger patch, with augmented documentation
> and “magic variable” support (TG_WHEN, TG_OBJECTID and such).
>
> The current version of the patch only supports PLpgSQL, I need to add
> support for the other languages in core but I though Thom would like to
> be able to play with a new patch before I finish plpython, plperl and
> pltcl support.
>
> This patch also includes edits following latest reviews from both Thom,
> Andres and Robert, in particular ANY command triggers are now called
> from the same place as specific command triggers and receive the same
> parameters.

Good to see that ANY COMMAND triggers contain everything the specific
triggers have. I've completed a complete re-run of all my testing.

Note: incremental patch attached for the following section...

-----START----

The docs have an excessive opening <varlistentry> tag. The docs also
list ALTER CAST as an option, which it isn't. There's an old version
of a paragraph included, immediately followed by its revised version.
It begins with "Triggers on ANY command...".

The example given for the abort_any_command function has a typo. The
RAISE statement should have a comma after the closing single quote
instead of %.

In doc/src/sgml/plpgsql.sgml:

“The command trigger function return's value is not used.”
should be
“The command trigger function’s return value is not used.”

“This example trigger just raise a...”
should be
“This example trigger just raises a...”

The example procedure isn't called correctly in the create command
trigger statement below it. It refers to it at "any_snitch", but the
function is just named "snitch". Also the style is inconsistent with
the other trigger functions further up the page, such as putting the
function language last, showing the return type on the same line as
the CREATE FUNCTION line and using upper-case lettering for keywords.

I don’t understand how functions can return a type of “command
trigger”. This certainly works, but I’ve never seen a type consisting
of more than one word. Could you explain this for me? This is also
at odds with the error message in src/backend/commands/cmdtrigger.c:

errmsg("function \"%s\" must return type \"trigger\"",

Should be “command trigger” as a regular trigger can’t be used on
command triggers.

----END----

At this moment in time, CTAS is still outstanding. Is the plan to try
to get that in for this release, or as an enhancement in 9.3?

I don’t know if this was a problem before that I didn’t spot
(probably), but triggers for both ANY COMMAND and ALTER FOREIGN TABLE
show a command tag of ALTER TABLE for ALTER FOREIGN TABLE statements
where the column is renamed:

thom(at)test=# ALTER FOREIGN TABLE test.dict2 RENAME COLUMN word TO words;
NOTICE: Command trigger on any: tg_when='BEFORE' cmd_tag='ALTER
TABLE' objectid=16569 schemaname='test' objectname='dict2'
NOTICE: Command trigger: tg_when='BEFORE' cmd_tag='ALTER TABLE'
objectid=16569 schemaname='test' objectname='dict2'
NOTICE: Command trigger: tg_when='AFTER' cmd_tag='ALTER TABLE'
objectid=16569 schemaname='test' objectname='dict2'
NOTICE: Command trigger on any: tg_when='AFTER' cmd_tag='ALTER TABLE'
objectid=16569 schemaname='test' objectname='dict2'
ALTER TABLE

I don’t think this is the fault of the trigger code because it
actually says ALTER TABLE at the bottom, suggesting it’s something
already present. This isn’t the case when adding or dropping columns.
Any comments?

Altering the properties of a function (such as cost, security definer,
whether it’s stable etc) doesn’t report the function’s OID:

thom(at)test=# ALTER FUNCTION test.testfunc2() COST 77;
NOTICE: Command trigger on any: tg_when='BEFORE' cmd_tag='ALTER
FUNCTION' objectid=<NULL> schemaname='test' objectname='testfunc2'
NOTICE: Command trigger: tg_when='BEFORE' cmd_tag='ALTER FUNCTION'
objectid=<NULL> schemaname='test' objectname='testfunc2'
NOTICE: Command trigger: tg_when='AFTER' cmd_tag='ALTER FUNCTION'
objectid=<NULL> schemaname='test' objectname='testfunc2'
NOTICE: Command trigger on any: tg_when='AFTER' cmd_tag='ALTER
FUNCTION' objectid=<NULL> schemaname='test' objectname='testfunc2'
ALTER FUNCTION

I get a garbage objectname for AFTER ANY COMMAND triggers on ALTER
TEXT SEARCH DICTIONARY when changing its options. It doesn’t show it
in the below example because I can’t get it displaying in plain text,
but where the objectname is blank is where I’m seeing unicode a square
containing “0074” 63 times in a row:

thom(at)test=# ALTER TEXT SEARCH DICTIONARY testnew.test_stem2 (
StopWords = dutch );
NOTICE: Command trigger on any: tg_when='BEFORE' cmd_tag='ALTER TEXT
SEARCH DICTIONARY' objectid=16617 schemaname='testnew'
objectname='test_stem2'
NOTICE: Command trigger: tg_when='BEFORE' cmd_tag='ALTER TEXT SEARCH
DICTIONARY' objectid=16617 schemaname='testnew'
objectname='test_stem2'
NOTICE: Command trigger: tg_when='AFTER' cmd_tag='ALTER TEXT SEARCH
DICTIONARY' objectid=16617 schemaname='testnew'
objectname='test_stem2'
NOTICE: Command trigger on any: tg_when='AFTER' cmd_tag='ALTER TEXT
SEARCH DICTIONARY' objectid=16617 schemaname='testnew'
objectname=' '
ALTER TEXT SEARCH DICTIONARY

Specific command triggers on ALTER VIEW don’t work at all:

thom(at)test=# ALTER VIEW view_test OWNER TO test;
NOTICE: Command trigger on any: tg_when='BEFORE' cmd_tag='ALTER VIEW'
objectid=16625 schemaname='public' objectname='view_test'
NOTICE: Command trigger on any: tg_when='AFTER' cmd_tag='ALTER VIEW'
objectid=16625 schemaname='public' objectname='view_test'
ALTER VIEW

Command triggers that fire for CREATE RULE show a schema, but DROP
RULE doesn’t. Which is it?:

thom(at)test=# CREATE RULE notify_test AS ON UPDATE TO seq_table DO ALSO
NOTIFY test; -- support for testing DROP RULE
NOTICE: Command trigger on any: tg_when='BEFORE' cmd_tag='CREATE
RULE' objectid=<NULL> schemaname='public' objectname='notify_test'
NOTICE: Command trigger: tg_when='BEFORE' cmd_tag='CREATE RULE'
objectid=<NULL> schemaname='public' objectname='notify_test'
NOTICE: Command trigger: tg_when='AFTER' cmd_tag='CREATE RULE'
objectid=16706 schemaname='public' objectname='notify_test'
NOTICE: Command trigger on any: tg_when='AFTER' cmd_tag='CREATE RULE'
objectid=16706 schemaname='public' objectname='notify_test'
CREATE RULE
thom(at)test=# DROP RULE notify_test ON seq_table;
NOTICE: Command trigger on any: tg_when='BEFORE' cmd_tag='DROP RULE'
objectid=16706 schemaname='<NULL>' objectname='notify_test'
NOTICE: Command trigger: tg_when='BEFORE' cmd_tag='DROP RULE'
objectid=16706 schemaname='<NULL>' objectname='notify_test'
NOTICE: Command trigger: tg_when='AFTER' cmd_tag='DROP RULE'
objectid=<NULL> schemaname='<NULL>' objectname='notify_test'
NOTICE: Command trigger on any: tg_when='AFTER' cmd_tag='DROP RULE'
objectid=<NULL> schemaname='<NULL>' objectname='notify_test'
DROP RULE

This same behaviour exists for DROP TRIGGER.

Regards

Thom