On 6 March 2012 21:18, Thom Brown <t...@linux.com> wrote: > On 6 March 2012 21:04, Dimitri Fontaine <dimi...@2ndquadrant.fr> wrote: >>>> [CASCADE will not run the command triggers for cascaded objects] >>> If these are all expected, does it in any way compromise the >>> effectiveness of DDL triggers in major use-cases? >> >> I don't think so. When replicating the replica will certainly drop the >> same set of dependent objects, for example. Auditing is another story. >> Do we want to try having cascaded object support in drop commands? > > I wasn't sure if auditing was one of the rationale behind the feature > or not. If it is, it presents a major problem. How does the replica > know that the objects were dropped? > > Thanks for the updated patch and the quick turnaround time. I'll give > it another review.
Okay, here's the update: The message returned by creating a command trigger after create index is still problematic: thom@test=# CREATE COMMAND TRIGGER cmd_trg_after_create_index AFTER CREATE INDEX EXECUTE PROCEDURE cmd_trg_info(); WARNING: AFTER CREATE INDEX CONCURRENTLY triggers are not supported DETAIL: The command trigger will not get fired. CREATE COMMAND TRIGGER The detail suggests that even though the command trigger has been requested, it won't be fired. Might I suggest: WARNING: AFTER CREATE INDEX CONCURRENTLY triggers are not supported DETAIL: The command trigger will not fire on concurrently-created indexes. CREATE VIEW doesn't return schema: thom@test=# CREATE VIEW view_test AS SELECT id, stuff FROM public.test; NOTICE: Command trigger on any: tg_when='BEFORE' cmd_tag='CREATE VIEW' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' NOTICE: Command trigger: tg_when='BEFORE' cmd_tag='CREATE VIEW' objectid=<NULL> schemaname='<NULL>' objectname='view_test' NOTICE: Command trigger: tg_when='AFTER' cmd_tag='CREATE VIEW' objectid=16606 schemaname='<NULL>' objectname='view_test' NOTICE: Command trigger on any: tg_when='AFTER' cmd_tag='CREATE VIEW' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' CREATE VIEW No specific triggers fire when altering a conversion: thom@test=# ALTER CONVERSION test9 RENAME TO test8; NOTICE: Command trigger on any: tg_when='BEFORE' cmd_tag='ALTER CONVERSION' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' NOTICE: Command trigger on any: tg_when='AFTER' cmd_tag='ALTER CONVERSION' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' ALTER CONVERSION Note: I hadn’t previously tested this, but I don’t think it was listed as supported until now. No specific triggers fire when altering the properties of a function: thom@test=# ALTER FUNCTION test.testfunc2() COST 77; NOTICE: Command trigger on any: tg_when='BEFORE' cmd_tag='ALTER FUNCTION' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' NOTICE: Command trigger on any: tg_when='AFTER' cmd_tag='ALTER FUNCTION' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' ALTER FUNCTION No specific triggers fire when altering a sequence: thom@test=# ALTER SEQUENCE test_seq2 OWNER TO test; NOTICE: Command trigger on any: tg_when='BEFORE' cmd_tag='ALTER SEQUENCE' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' NOTICE: Command trigger on any: tg_when='AFTER' cmd_tag='ALTER SEQUENCE' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' ALTER SEQUENCE thom@test=# ALTER SEQUENCE test_seq2 RESTART WITH 4; NOTICE: Command trigger on any: tg_when='BEFORE' cmd_tag='ALTER SEQUENCE' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' NOTICE: Command trigger on any: tg_when='AFTER' cmd_tag='ALTER SEQUENCE' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' ALTER SEQUENCE No specific triggers when altering a view: thom@test=# ALTER VIEW view_test2 SET SCHEMA testnew; NOTICE: Command trigger on any: tg_when='BEFORE' cmd_tag='ALTER VIEW' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' NOTICE: Command trigger on any: tg_when='AFTER' cmd_tag='ALTER VIEW' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' ALTER VIEW thom@test=# ALTER VIEW testnew.view_test2 ALTER COLUMN id SET DEFAULT 9; NOTICE: Command trigger on any: tg_when='BEFORE' cmd_tag='ALTER VIEW' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' NOTICE: Command trigger on any: tg_when='AFTER' cmd_tag='ALTER VIEW' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' ALTER VIEW The object name shown in specific triggers when dropping aggregates shows the schema name: thom@test=# DROP AGGREGATE testnew.avgtest2(bigint); NOTICE: Command trigger on any: tg_when='BEFORE' cmd_tag='DROP AGGREGATE' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' NOTICE: Command trigger: tg_when='BEFORE' cmd_tag='DROP AGGREGATE' objectid=16539 schemaname='testnew' objectname='testnew' NOTICE: Command trigger: tg_when='AFTER' cmd_tag='DROP AGGREGATE' objectid=<NULL> schemaname='testnew' objectname='testnew' NOTICE: Command trigger on any: tg_when='AFTER' cmd_tag='DROP AGGREGATE' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' DROP AGGREGATE Same for collations: thom@test=# DROP COLLATION testnew.en_gb_test; NOTICE: Command trigger on any: tg_when='BEFORE' cmd_tag='DROP COLLATION' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' NOTICE: Command trigger: tg_when='BEFORE' cmd_tag='DROP COLLATION' objectid=16542 schemaname='testnew' objectname='testnew' NOTICE: Command trigger: tg_when='AFTER' cmd_tag='DROP COLLATION' objectid=<NULL> schemaname='testnew' objectname='testnew' NOTICE: Command trigger on any: tg_when='AFTER' cmd_tag='DROP COLLATION' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' DROP COLLATION When dropping domains, the name of the domain includes the schema name: thom@test=# DROP DOMAIN testnew.us_postal_code; NOTICE: Command trigger on any: tg_when='BEFORE' cmd_tag='DROP DOMAIN' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' NOTICE: Command trigger: tg_when='BEFORE' cmd_tag='DROP DOMAIN' objectid=16546 schemaname='testnew' objectname='testnew.us_postal_code' NOTICE: Command trigger: tg_when='AFTER' cmd_tag='DROP DOMAIN' objectid=<NULL> schemaname='testnew' objectname='testnew.us_postal_code' NOTICE: Command trigger on any: tg_when='AFTER' cmd_tag='DROP DOMAIN' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' DROP DOMAIN Dropping functions shows the object name as the schema name: thom@test=# DROP FUNCTION testnew.testfunc2(); NOTICE: Command trigger on any: tg_when='BEFORE' cmd_tag='DROP FUNCTION' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' NOTICE: Command trigger: tg_when='BEFORE' cmd_tag='DROP FUNCTION' objectid=16557 schemaname='testnew' objectname='testnew' NOTICE: Command trigger: tg_when='AFTER' cmd_tag='DROP FUNCTION' objectid=<NULL> schemaname='testnew' objectname='testnew' NOTICE: Command trigger on any: tg_when='AFTER' cmd_tag='DROP FUNCTION' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' DROP FUNCTION Same with dropping operators: thom@test=# DROP OPERATOR testnew.<<|| (int2, int2); NOTICE: Command trigger on any: tg_when='BEFORE' cmd_tag='DROP OPERATOR' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' NOTICE: Command trigger: tg_when='BEFORE' cmd_tag='DROP OPERATOR' objectid=16566 schemaname='testnew' objectname='testnew' NOTICE: Command trigger: tg_when='AFTER' cmd_tag='DROP OPERATOR' objectid=<NULL> schemaname='testnew' objectname='testnew' NOTICE: Command trigger on any: tg_when='AFTER' cmd_tag='DROP OPERATOR' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' DROP OPERATOR ...and operator family: thom@test=# DROP OPERATOR FAMILY testnew.test_ops2 USING btree; NOTICE: Command trigger on any: tg_when='BEFORE' cmd_tag='DROP OPERATOR FAMILY' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' NOTICE: Command trigger: tg_when='BEFORE' cmd_tag='DROP OPERATOR FAMILY' objectid=16571 schemaname='testnew' objectname='testnew' NOTICE: Command trigger: tg_when='AFTER' cmd_tag='DROP OPERATOR FAMILY' objectid=<NULL> schemaname='testnew' objectname='testnew' NOTICE: Command trigger on any: tg_when='AFTER' cmd_tag='DROP OPERATOR FAMILY' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' DROP OPERATOR FAMILY … and the same for dropping text search configuration/dictionary/parser/template. I hadn't previously tested triggers against CLUSTER, REINDEX, VACUUM and LOAD, but have tested them now. When creating a trigger on REINDEX, I get the following message: thom@test=# CREATE COMMAND TRIGGER cmd_trg_before_reindex BEFORE REINDEX EXECUTE PROCEDURE cmd_trg_info(); WARNING: REINDEX DATABASE is not supported DETAIL: The command trigger will not get fired. My problem with this is the same as what I reported previously for CREATE INDEX, in that it suggests the DDL itself isn't supported. But more importantly: Creating AFTER CLUSTER command triggers produce an error (as expected since it's not supported), but AFTER REINDEX only produces a warning. These should be the same, probably both an error. VACUUM doesn't fire a specific command trigger: thom@test=# VACUUM; NOTICE: Command trigger on any: tg_when='BEFORE' cmd_tag='VACUUM' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' VACUUM REINDEX on a table seems to show no schema name but an object name for specific triggers: thom@test=# REINDEX TABLE testnew.test9; NOTICE: Command trigger on any: tg_when='BEFORE' cmd_tag='REINDEX' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' NOTICE: Command trigger: tg_when='BEFORE' cmd_tag='REINDEX' objectid=16558 schemaname='<NULL>' objectname='testnew' NOTICE: Command trigger: tg_when='AFTER' cmd_tag='REINDEX' objectid=16558 schemaname='<NULL>' objectname='testnew' NOTICE: Command trigger on any: tg_when='AFTER' cmd_tag='REINDEX' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' REINDEX When REINDEXing an index rather than a table, the table's details are shown in the trigger. Is this expected?: thom@test=# REINDEX INDEX testnew.idx_test_2 ; NOTICE: Command trigger on any: tg_when='BEFORE' cmd_tag='REINDEX' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' NOTICE: Command trigger: tg_when='BEFORE' cmd_tag='REINDEX' objectid=16565 schemaname='testnew' objectname='test9' NOTICE: Command trigger: tg_when='AFTER' cmd_tag='REINDEX' objectid=16565 schemaname='testnew' objectname='test9' NOTICE: Command trigger on any: tg_when='AFTER' cmd_tag='REINDEX' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' REINDEX REINDEXing the whole database doesn't fire specific command triggers: thom@test=# REINDEX DATABASE test; NOTICE: Command trigger on any: tg_when='BEFORE' cmd_tag='REINDEX' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' NOTICE: table "pg_catalog.pg_class" was reindexed <snip> NOTICE: table "information_schema.sql_features" was reindexed NOTICE: Command trigger on any: tg_when='AFTER' cmd_tag='REINDEX' objectid=<NULL> schemaname='<NULL>' objectname='<NULL>' REINDEX The same happens for the REINDEX SYSTEM syntax. Documentation: I previously noted text which needed correcting that doesn’t seem to have fixed yet. The email I reported those in misleadingly began with “Right, hopefully this should be my last piece of list spam...”. The only one which I feel no longer needs fixing is the first thing I reported due to the fact that command triggers can now only been applied against one command at a time. Also disregard the comments in that email for ALTER and DROP. You appear to have added support for ALTER CAST, but there isn’t any such DDL. OPERATOR CLASS and OPERATOR FAMILY are listed twice. “Note that objects dropped by effect of DROP CASCADE will not provoque firing a command trigger” should probably be: “Note that objects dropped by the effect of DROP CASCADE will not result in a command trigger firing.” “That also applis to other dependencies following, as in DROP OWNED BY.” should be: “That also applies to other dependencies following, as in DROP OWNED BY.” Another typo (which was in the email I referred to earlier) is s/exercize/exercise/. “Triggers on ANY command support more commands than just this list, and will only provide the command tag argument as NOT NULL.” should be: “Triggers on ANY command support more commands than just this list, and will provide NULL values for every argument except for the argument that determines whether the trigger was before or after the command event, and the command tag.” This will no doubt need changing to refer to the new trigger variables if you introduce them. -- Thom -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
