Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 5A503119C295 for ; Thu, 8 Mar 2012 18:24:53 -0400 (AST) Received: from acidenitrix.villemain.org ([91.121.90.165]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1S5llQ-0004r8-54 for pgsql-hackers@postgresql.org; Thu, 08 Mar 2012 22:24:52 +0000 Received: from 89-158-241-83.rev.dartybox.com ([89.158.241.83] helo=DimIMac.local) by acidenitrix.villemain.org with esmtpsa (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:32) (Exim 4.69) (envelope-from ) id 1S5ll7-00073f-Gt; Thu, 08 Mar 2012 23:24:30 +0100 Received: by DimIMac.local (Postfix, from userid 501) id 1FA593CA2E39; Thu, 8 Mar 2012 23:24:22 +0100 (CET) From: Dimitri Fontaine To: Thom Brown Cc: Dimitri Fontaine , pgsql-hackers@postgresql.org, Andres Freund , Tom Lane Subject: Re: Command Triggers, patch v11 Organization: 2ndQuadrant References: <201203041650.39826.andres@anarazel.de> User-Mail-Address: dimitri@2ndQuadrant.fr Date: Thu, 08 Mar 2012 23:24:22 +0100 In-Reply-To: (Thom Brown's message of "Tue, 6 Mar 2012 23:25:55 +0000") Message-ID: User-Agent: Gnus/5.110018 (No Gnus v0.18) Emacs/24.0.94 (darwin) MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=-=-=" X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201203/540 X-Sequence-Number: 204422 --=-=-= Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hi, Thom Brown writes: > The message returned by creating a command trigger after create index > is still problematic: Fixed. I'm attaching an incremental patch here, the github branch is updated too. > CREATE VIEW doesn't return schema: Fixed, and as an added bonus I fixed the CREATE SEQUENCE oddity about that too. > No specific triggers fire when altering a conversion: Couldn't reproduce, works here, added tests. > No specific triggers fire when altering the properties of a function: Fixed. > No specific triggers fire when altering a sequence: Couldn't reproduce, added tests. > No specific triggers when altering a view: Same again. > The object name shown in specific triggers when dropping aggregates > shows the schema name: > Same for collations: > Dropping functions shows the object name as the schema name: > Same with dropping operators: > ...and operator family: > =E2=80=A6 and the same for dropping text search > configuration/dictionary/parser/template. Fixed in the attached (all of those where located at exactly the same place, by the way, that's one fix). > When dropping domains, the name of the domain includes the schema name: I'm using format_type_be(objectId) so that int4 is integer and int8 bigint etc, but that's adding the schemaname. I didn't have time to look for another API that wouldn't add the schemaname nor to add one myself, will do that soon. > I hadn't previously tested triggers against CLUSTER, REINDEX, VACUUM > and LOAD, but have tested them now. Cool=C2=A0:) > When creating a trigger on REINDEX, I get the following message: Fixed. > 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. Fixed. > VACUUM doesn't fire a specific command trigger: I though it was better this way, I changed my mind and completed the code. > REINDEX on a table seems to show no schema name but an object name for > specific triggers: Still on the TODO. > When REINDEXing an index rather than a table, the table's details are > shown in the trigger. Is this expected?: Yeah well. Will see about how much damage needs to be done in the current APIs, running out of steam for tonight's batch. > REINDEXing the whole database doesn't fire specific command triggers: We don't want to because REINDEX DATABASE is managing transactions on its own, same limitation as with AFTER VACUUM and all. Will have a look at what it takes to document that properly. > Documentation: Fixed. Thom Brown writes: > I've also since found that if I issue a VACUUM, CLUSTER or REINDEX on > a read-only standby, the BEFORE ANY COMMAND trigger fires. I don't > think any trigger should fire on a read-only standby. Well I'm not sold on that myself (think pl/untrusted that would reach out to the OS and do whatever is needed there). You can even set the session_replication_role GUC to replica and only have the replica command triggers fired. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support --=-=-= Content-Type: text/x-patch Content-Disposition: attachment; filename=more-cmd-trigger-fixes.patch *** a/doc/src/sgml/ref/create_command_trigger.sgml --- b/doc/src/sgml/ref/create_command_trigger.sgml *************** *** 41,48 **** CREATE COMMAND TRIGGER name { BEFOR ALTER LANGUAGE ALTER OPERATOR ALTER OPERATOR CLASS - ALTER OPERATOR CLASS - ALTER OPERATOR FAMILY ALTER OPERATOR FAMILY ALTER SCHEMA ALTER SEQUENCE --- 41,46 ---- *************** *** 137,146 **** CREATE COMMAND TRIGGER name { BEFOR ! Note that objects dropped by effect of DROP CASCADE ! will not provoque firing a command trigger, only the top-level command ! for the main object will fire a command trigger. That also applis to ! other dependencies following, as in DROP OWNED BY. --- 135,145 ---- ! Note that objects dropped by the effect of DROP ! CASCADE will not result in a command trigger firing, only the ! top-level command for the main object will fire a command trigger. That ! also applies to other dependencies following, as in DROP OWNED ! BY. *************** *** 192,198 **** CREATE COMMAND TRIGGER name { BEFOR you are connected to. ! Commands that exercize their own transaction control are only supported in BEFORE command triggers, that's the case for VACUUM, CLUSTER CREATE INDEX CONCURRENTLY, and REINDEX --- 191,197 ---- you are connected to. ! Commands that exercise their own transaction control are only supported in BEFORE command triggers, that's the case for VACUUM, CLUSTER CREATE INDEX CONCURRENTLY, and REINDEX *************** *** 215,220 **** CREATE COMMAND TRIGGER name { BEFOR --- 214,224 ---- not to be able to take over control from a superuser. + 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. + Triggers on ANY command support more commands than just this list, and will only provide the command tag argument as NOT NULL. Supporting more *************** *** 244,257 **** CREATE COMMAND TRIGGER name { BEFOR tag, objectid (can be null in case of a BEFORE CREATE or an AFTER DROP command trigger timing), schemaname (can be null for objects not ! living in a schema, and for sequences due to an implementation limit) ! and object name (can be null for any command ! triggers). ! ! ! The command CREATE SEQUENCE lacks support for ! the schemaname command trigger argument, it ! provides NULL in all cases. --- 248,255 ---- tag, objectid (can be null in case of a BEFORE CREATE or an AFTER DROP command trigger timing), schemaname (can be null for objects not ! living in a schema) and object name (can be null ! for any command triggers). *** a/src/backend/commands/cmdtrigger.c --- b/src/backend/commands/cmdtrigger.c *************** *** 185,197 **** CreateCmdTrigger(CreateCmdTrigStmt *stmt, const char *queryString) ereport(WARNING, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("AFTER CREATE INDEX CONCURRENTLY triggers are not supported"), ! errdetail("The command trigger will not get fired."))); if (strcmp(stmt->command, "REINDEX") == 0) ereport(WARNING, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("REINDEX DATABASE is not supported"), ! errdetail("The command trigger will not get fired."))); if (funcrettype != VOIDOID) ereport(ERROR, --- 185,197 ---- ereport(WARNING, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("AFTER CREATE INDEX CONCURRENTLY triggers are not supported"), ! errdetail("The command trigger will not fire on concurrently-created indexes."))); if (strcmp(stmt->command, "REINDEX") == 0) ereport(WARNING, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("REINDEX DATABASE is not supported"), ! errdetail("The command trigger will not fire on REINDEX DATABASE."))); if (funcrettype != VOIDOID) ereport(ERROR, *** a/src/backend/commands/dropcmds.c --- b/src/backend/commands/dropcmds.c *************** *** 316,323 **** get_object_name(CommandContext cmd, ObjectType objtype, case OBJECT_FOREIGN_SERVER: case OBJECT_OPCLASS: case OBJECT_OPFAMILY: ! cmd->objectname = strVal(linitial(objname)); break; default: elog(ERROR, "unexpected object type (%d)", (int)objtype); break; --- 316,331 ---- case OBJECT_FOREIGN_SERVER: case OBJECT_OPCLASS: case OBJECT_OPFAMILY: ! { ! int len = list_length(objname); ! if (len == 1) ! cmd->objectname = strVal(linitial(objname)); ! else if (len == 2) ! cmd->objectname = strVal(lsecond(objname)); ! else ! elog(ERROR, "unexpected name list length (%d)", len); break; + } default: elog(ERROR, "unexpected object type (%d)", (int)objtype); break; *** a/src/backend/commands/functioncmds.c --- b/src/backend/commands/functioncmds.c *************** *** 1324,1329 **** AlterFunction(AlterFunctionStmt *stmt) --- 1324,1330 ---- List *set_items = NIL; DefElem *cost_item = NULL; DefElem *rows_item = NULL; + CommandContextData cmd; rel = heap_open(ProcedureRelationId, RowExclusiveLock); *************** *** 1433,1444 **** AlterFunction(AlterFunctionStmt *stmt) --- 1434,1461 ---- repl_val, repl_null, repl_repl); } + /* Call BEFORE ALTER FUNCTION command triggers */ + InitCommandContext(&cmd, (Node *)stmt, false); + + if (CommandFiresTriggers(&cmd)) + { + cmd.objectId = InvalidOid; + cmd.objectname = pstrdup(NameStr(procForm->proname)); + cmd.schemaname = get_namespace_name(procForm->pronamespace); + + ExecBeforeCommandTriggers(&cmd); + } + /* Do the update */ simple_heap_update(rel, &tup->t_self, tup); CatalogUpdateIndexes(rel, tup); heap_close(rel, NoLock); heap_freetuple(tup); + + /* Call AFTER ALTER FUNCTION command triggers */ + if (CommandFiresAfterTriggers(&cmd)) + ExecAfterCommandTriggers(&cmd); } /* *** a/src/backend/commands/sequence.c --- b/src/backend/commands/sequence.c *************** *** 213,233 **** DefineSequence(CreateSeqStmt *seq) stmt->tablespacename = NULL; stmt->if_not_exists = false; ! /* ! * Call BEFORE CREATE SEQUENCE triggers ! */ InitCommandContext(&cmd, (Node *)seq, false); ! if (CommandFiresTriggers(&cmd)) ! { ! cmd.objectId = InvalidOid; ! cmd.objectname = NameStr(name); ! cmd.schemaname = NULL; /* can't publish it easily enough here */ ! ! ExecBeforeCommandTriggers(&cmd); ! } ! ! seqoid = DefineRelation(stmt, RELKIND_SEQUENCE, seq->ownerId); Assert(seqoid != InvalidOid); rel = heap_open(seqoid, AccessExclusiveLock); --- 213,222 ---- stmt->tablespacename = NULL; stmt->if_not_exists = false; ! /* Prepare BEFORE CREATE SEQUENCE triggers */ InitCommandContext(&cmd, (Node *)seq, false); ! seqoid = DefineRelation(stmt, RELKIND_SEQUENCE, seq->ownerId, &cmd); Assert(seqoid != InvalidOid); rel = heap_open(seqoid, AccessExclusiveLock); *** a/src/backend/commands/tablecmds.c --- b/src/backend/commands/tablecmds.c *************** *** 415,421 **** static void RangeVarCallbackForAlterRelation(const RangeVar *rv, Oid relid, * ---------------------------------------------------------------- */ Oid ! DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId) { char relname[NAMEDATALEN]; Oid namespaceId; --- 415,421 ---- * ---------------------------------------------------------------- */ Oid ! DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId, CommandContext cmd) { char relname[NAMEDATALEN]; Oid namespaceId; *************** *** 608,613 **** DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId) --- 608,623 ---- } } + /* Exec BEFORE CREATE view|sequence|table|type command triggers */ + if (CommandFiresTriggers(cmd)) + { + cmd->objectId = InvalidOid; + cmd->objectname = pstrdup(relname); + cmd->schemaname = get_namespace_name(namespaceId); + + ExecBeforeCommandTriggers(cmd); + } + /* * Create the relation. Inherited defaults and constraints are passed in * for immediate handling --- since they don't need parsing, they can be *************** *** 670,675 **** DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId) --- 680,686 ---- */ relation_close(rel, NoLock); + /* AFTER command triggers are fired from well outside this function */ return relationId; } *** a/src/backend/commands/typecmds.c --- b/src/backend/commands/typecmds.c *************** *** 2110,2130 **** DefineCompositeType(RangeVar *typevar, List *coldeflist, } /* - * Call BEFORE CREATE (composite) TYPE triggers - */ - if (CommandFiresTriggers(cmd)) - { - cmd->objectId = InvalidOid; - cmd->objectname = createStmt->relation->relname; - cmd->schemaname = get_namespace_name(typeNamespace); - - ExecBeforeCommandTriggers(cmd); - } - - /* * Finally create the relation. This also creates the type. */ ! relid = DefineRelation(createStmt, RELKIND_COMPOSITE_TYPE, InvalidOid); Assert(relid != InvalidOid); /* Call AFTER CREATE (composite) TYPE triggers */ --- 2110,2118 ---- } /* * Finally create the relation. This also creates the type. */ ! relid = DefineRelation(createStmt, RELKIND_COMPOSITE_TYPE, InvalidOid, cmd); Assert(relid != InvalidOid); /* Call AFTER CREATE (composite) TYPE triggers */ *** a/src/backend/commands/vacuum.c --- b/src/backend/commands/vacuum.c *************** *** 123,130 **** vacuum(VacuumStmt *vacstmt, Oid relid, bool do_toast, else in_outer_xact = IsInTransactionChain(isTopLevel); ! /* CAll BEFORE VACUUM command triggers */ ! if (!IsAutoVacuumWorkerProcess() && vacstmt->relation != NULL) { CommandContextData cmd; InitCommandContext(&cmd, (Node *)vacstmt, false); --- 123,130 ---- else in_outer_xact = IsInTransactionChain(isTopLevel); ! /* Call BEFORE VACUUM command triggers */ ! if (!IsAutoVacuumWorkerProcess()) { CommandContextData cmd; InitCommandContext(&cmd, (Node *)vacstmt, false); *************** *** 132,140 **** vacuum(VacuumStmt *vacstmt, Oid relid, bool do_toast, if (CommandFiresTriggers(&cmd)) { cmd.objectId = relid; - cmd.objectname = vacstmt->relation->relname; - cmd.schemaname = vacstmt->relation->schemaname; ExecBeforeCommandTriggers(&cmd); } } --- 132,143 ---- if (CommandFiresTriggers(&cmd)) { cmd.objectId = relid; + if (vacstmt->relation != NULL) + { + cmd.objectname = vacstmt->relation->relname; + cmd.schemaname = vacstmt->relation->schemaname; + } ExecBeforeCommandTriggers(&cmd); } } *** a/src/backend/commands/view.c --- b/src/backend/commands/view.c *************** *** 169,186 **** DefineVirtualRelation(RangeVar *relation, List *tlist, bool replace, lockmode = replace ? AccessExclusiveLock : NoLock; (void) RangeVarGetAndCheckCreationNamespace(relation, lockmode, &viewOid); - /* - * Call BEFORE CREATE VIEW triggers - */ - if (CommandFiresTriggers(cmd)) - { - cmd->objectId = InvalidOid; - cmd->objectname = relation->relname; - cmd->schemaname = relation->schemaname; - - ExecBeforeCommandTriggers(cmd); - } - if (OidIsValid(viewOid) && replace) { Relation rel; --- 169,174 ---- *************** *** 208,213 **** DefineVirtualRelation(RangeVar *relation, List *tlist, bool replace, --- 196,211 ---- */ Assert(relation->relpersistence == rel->rd_rel->relpersistence); + /* Call BEFORE CREATE VIEW triggers */ + if (CommandFiresTriggers(cmd)) + { + cmd->objectId = viewOid; + cmd->objectname = RelationGetRelationName(rel); + cmd->schemaname = get_namespace_name(RelationGetNamespace(rel)); + + ExecBeforeCommandTriggers(cmd); + } + /* * Create a tuple descriptor to compare against the existing view, and * verify that the old column list is an initial prefix of the new *************** *** 282,288 **** DefineVirtualRelation(RangeVar *relation, List *tlist, bool replace, * existing view, so we don't need more code to complain if "replace" * is false). */ ! relid = DefineRelation(createStmt, RELKIND_VIEW, InvalidOid); Assert(relid != InvalidOid); return relid; } --- 280,286 ---- * existing view, so we don't need more code to complain if "replace" * is false). */ ! relid = DefineRelation(createStmt, RELKIND_VIEW, InvalidOid, cmd); Assert(relid != InvalidOid); return relid; } *** a/src/backend/tcop/utility.c --- b/src/backend/tcop/utility.c *************** *** 691,706 **** standard_ProcessUtility(Node *parsetree, */ InitCommandContext(&cmd, parsetree, false); - if (CommandFiresTriggers(&cmd)) - { - cmd.objectId = InvalidOid; - cmd.objectname = stmt->relation->relname; - cmd.schemaname = get_namespace_name( - RangeVarGetCreationNamespace(stmt->relation)); - - ExecBeforeCommandTriggers(&cmd); - } - /* Run parse analysis ... */ stmts = transformCreateStmt(stmt, queryString); --- 691,696 ---- *************** *** 717,723 **** standard_ProcessUtility(Node *parsetree, /* Create the table itself */ relOid = DefineRelation((CreateStmt *) stmt, RELKIND_RELATION, ! InvalidOid); /* * Let AlterTableCreateToastTable decide if this one --- 707,714 ---- /* Create the table itself */ relOid = DefineRelation((CreateStmt *) stmt, RELKIND_RELATION, ! InvalidOid, ! &cmd); /* * Let AlterTableCreateToastTable decide if this one *************** *** 741,747 **** standard_ProcessUtility(Node *parsetree, /* Create the table itself */ relOid = DefineRelation((CreateStmt *) stmt, RELKIND_FOREIGN_TABLE, ! InvalidOid); CreateForeignTable((CreateForeignTableStmt *) stmt, relOid); } --- 732,739 ---- /* Create the table itself */ relOid = DefineRelation((CreateStmt *) stmt, RELKIND_FOREIGN_TABLE, ! InvalidOid, ! &cmd); CreateForeignTable((CreateForeignTableStmt *) stmt, relOid); } *** a/src/include/commands/tablecmds.h --- b/src/include/commands/tablecmds.h *************** *** 21,27 **** #include "utils/relcache.h" ! extern Oid DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId); extern void RemoveRelations(DropStmt *drop); --- 21,28 ---- #include "utils/relcache.h" ! extern Oid DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId, ! CommandContext cmd); extern void RemoveRelations(DropStmt *drop); *** a/src/test/regress/expected/cmdtriggers.out --- b/src/test/regress/expected/cmdtriggers.out *************** *** 26,31 **** alter command trigger snitch_before set disable; --- 26,32 ---- alter command trigger snitch_before set enable; alter command trigger snitch_after_ rename to snitch_after; create command trigger snitch_create_table after create table execute procedure snitch(); + create command trigger snitch_create_seq after create sequence execute procedure snitch(); create command trigger snitch_create_view after create view execute procedure snitch(); create command trigger snitch_alter_table after alter table execute procedure snitch(); create command trigger snitch_alter_seq after alter sequence execute procedure snitch(); *************** *** 54,59 **** create command trigger snitch_alter_domain before alter domain execute procedure --- 55,70 ---- create command trigger snitch_create_type before create type execute procedure snitch(); create command trigger snitch_alter_type before alter type execute procedure snitch(); create command trigger snitch_before_alter_function before alter function execute procedure snitch(); + create command trigger snitch_alter_conversion before alter conversion execute procedure snitch(); + create command trigger snitch_drop_agg before drop aggregate execute procedure snitch(); + create command trigger snitch_before_drop_tsconf before drop text search configuration execute procedure snitch(); + create command trigger snitch_before_drop_tsdict before drop text search dictionary execute procedure snitch(); + create command trigger snitch_before_drop_tsparser before drop text search parser execute procedure snitch(); + create command trigger snitch_before_drop_tstmpl before drop text search template execute procedure snitch(); + create command trigger snitch_vacuum before vacuum execute procedure snitch(); + create command trigger snitch_reindex before reindex execute procedure snitch(); + WARNING: REINDEX DATABASE is not supported + DETAIL: The command trigger will not fire on REINDEX DATABASE. create schema cmd; NOTICE: snitch: BEFORE any CREATE SCHEMA NOTICE: snitch: BEFORE CREATE SCHEMA cmd *************** *** 63,72 **** NOTICE: snitch: BEFORE any CREATE SCHEMA --- 74,85 ---- NOTICE: snitch: BEFORE CREATE SCHEMA cmd2 NOTICE: snitch: AFTER any CREATE SCHEMA create role regbob; + ERROR: role "regbob" already exists create table cmd.foo(id bigserial primary key); NOTICE: snitch: BEFORE any CREATE TABLE NOTICE: CREATE TABLE will create implicit sequence "foo_id_seq" for serial column "foo.id" NOTICE: snitch: BEFORE any CREATE SEQUENCE + NOTICE: snitch: AFTER CREATE SEQUENCE cmd foo_id_seq NOTICE: snitch: AFTER any CREATE SEQUENCE NOTICE: snitch: BEFORE any CREATE INDEX NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo" *************** *** 166,171 **** NOTICE: snitch: AFTER ALTER TABLE cmd test --- 179,185 ---- NOTICE: snitch: AFTER any ALTER TABLE create sequence test_seq_; NOTICE: snitch: BEFORE any CREATE SEQUENCE + NOTICE: snitch: AFTER CREATE SEQUENCE public test_seq_ NOTICE: snitch: AFTER any CREATE SEQUENCE alter sequence test_seq_ owner to regbob; NOTICE: snitch: BEFORE any ALTER SEQUENCE *************** *** 217,223 **** NOTICE: snitch: AFTER ALTER SEQUENCE cmd test_seq NOTICE: snitch: AFTER any ALTER SEQUENCE create view view_test as select id, things from cmd.test; NOTICE: snitch: BEFORE any CREATE VIEW ! NOTICE: snitch: AFTER CREATE VIEW view_test NOTICE: snitch: AFTER any CREATE VIEW alter view view_test owner to regbob; NOTICE: snitch: BEFORE any ALTER VIEW --- 231,237 ---- NOTICE: snitch: AFTER any ALTER SEQUENCE create view view_test as select id, things from cmd.test; NOTICE: snitch: BEFORE any CREATE VIEW ! NOTICE: snitch: AFTER CREATE VIEW public view_test NOTICE: snitch: AFTER any CREATE VIEW alter view view_test owner to regbob; NOTICE: snitch: BEFORE any ALTER VIEW *************** *** 243,250 **** cluster cmd.foo using foo_pkey; --- 257,270 ---- NOTICE: snitch: BEFORE any CLUSTER vacuum cmd.foo; NOTICE: snitch: BEFORE any VACUUM + NOTICE: snitch: BEFORE VACUUM cmd foo vacuum; NOTICE: snitch: BEFORE any VACUUM + NOTICE: snitch: BEFORE VACUUM + reindex table cmd.foo; + NOTICE: snitch: BEFORE any REINDEX + NOTICE: snitch: BEFORE REINDEX cmd + NOTICE: snitch: AFTER any REINDEX set session_replication_role to replica; create table cmd.bar(); reset session_replication_role; *************** *** 254,278 **** NOTICE: snitch: AFTER any CREATE INDEX drop index cmd.idx_foo; NOTICE: snitch: BEFORE any DROP INDEX NOTICE: snitch: AFTER any DROP INDEX ! create function cmd.fun(int) returns text language sql as $$ select t from cmd.foo where id = $1; $$; NOTICE: snitch: BEFORE any CREATE FUNCTION ! NOTICE: snitch: AFTER CREATE FUNCTION cmd fun NOTICE: snitch: AFTER any CREATE FUNCTION ! alter function cmd.fun(int) strict; NOTICE: snitch: BEFORE any ALTER FUNCTION NOTICE: snitch: AFTER any ALTER FUNCTION ! alter function cmd.fun(int) rename to notfun; NOTICE: snitch: BEFORE any ALTER FUNCTION ! NOTICE: snitch: BEFORE ALTER FUNCTION cmd fun NOTICE: snitch: AFTER ALTER FUNCTION cmd notfun NOTICE: snitch: AFTER any ALTER FUNCTION ! alter function cmd.notfun(int) set schema public; NOTICE: snitch: BEFORE any ALTER FUNCTION NOTICE: snitch: BEFORE ALTER FUNCTION cmd notfun ! NOTICE: snitch: AFTER ALTER FUNCTION public notfun NOTICE: snitch: AFTER any ALTER FUNCTION ! drop function public.notfun(int); NOTICE: snitch: BEFORE any DROP FUNCTION NOTICE: snitch: AFTER any DROP FUNCTION create function cmd.plus1(int) returns bigint language sql --- 274,310 ---- drop index cmd.idx_foo; NOTICE: snitch: BEFORE any DROP INDEX NOTICE: snitch: AFTER any DROP INDEX ! create function fun(int) returns text language sql as $$ select t from cmd.foo where id = $1; $$; NOTICE: snitch: BEFORE any CREATE FUNCTION ! NOTICE: snitch: AFTER CREATE FUNCTION public fun NOTICE: snitch: AFTER any CREATE FUNCTION ! alter function fun(int) strict; NOTICE: snitch: BEFORE any ALTER FUNCTION + NOTICE: snitch: BEFORE ALTER FUNCTION public fun + NOTICE: snitch: AFTER ALTER FUNCTION public fun NOTICE: snitch: AFTER any ALTER FUNCTION ! alter function fun(int) rename to notfun; NOTICE: snitch: BEFORE any ALTER FUNCTION ! NOTICE: snitch: BEFORE ALTER FUNCTION public fun ! NOTICE: snitch: AFTER ALTER FUNCTION public notfun ! NOTICE: snitch: AFTER any ALTER FUNCTION ! alter function notfun(int) set schema cmd; ! NOTICE: snitch: BEFORE any ALTER FUNCTION ! NOTICE: snitch: BEFORE ALTER FUNCTION public notfun NOTICE: snitch: AFTER ALTER FUNCTION cmd notfun NOTICE: snitch: AFTER any ALTER FUNCTION ! alter function cmd.notfun(int) owner to regbob; NOTICE: snitch: BEFORE any ALTER FUNCTION NOTICE: snitch: BEFORE ALTER FUNCTION cmd notfun ! NOTICE: snitch: AFTER ALTER FUNCTION cmd notfun ! NOTICE: snitch: AFTER any ALTER FUNCTION ! alter function cmd.notfun(int) cost 77; ! NOTICE: snitch: BEFORE any ALTER FUNCTION ! NOTICE: snitch: BEFORE ALTER FUNCTION cmd notfun ! NOTICE: snitch: AFTER ALTER FUNCTION cmd notfun NOTICE: snitch: AFTER any ALTER FUNCTION ! drop function cmd.notfun(int); NOTICE: snitch: BEFORE any DROP FUNCTION NOTICE: snitch: AFTER any DROP FUNCTION create function cmd.plus1(int) returns bigint language sql *************** *** 306,311 **** NOTICE: snitch: BEFORE any ALTER AGGREGATE --- 338,344 ---- NOTICE: snitch: AFTER any ALTER AGGREGATE drop aggregate public.avg(float8); NOTICE: snitch: BEFORE any DROP AGGREGATE + NOTICE: snitch: BEFORE DROP AGGREGATE public avg NOTICE: snitch: AFTER any DROP AGGREGATE create collation cmd.french (LOCALE = 'fr_FR'); NOTICE: snitch: BEFORE any CREATE COLLATION *************** *** 423,430 **** NOTICE: snitch: BEFORE ALTER TRIGGER cmd footg NOTICE: snitch: AFTER any ALTER TRIGGER drop trigger foo_trigger on cmd.foo; NOTICE: snitch: BEFORE any DROP TRIGGER ! NOTICE: snitch: BEFORE DROP TRIGGER cmd ! NOTICE: snitch: AFTER any DROP TRIGGER create text search configuration test (parser = "default"); NOTICE: snitch: BEFORE any CREATE TEXT SEARCH CONFIGURATION NOTICE: snitch: AFTER CREATE TEXT SEARCH CONFIGURATION public test --- 456,478 ---- NOTICE: snitch: AFTER any ALTER TRIGGER drop trigger foo_trigger on cmd.foo; NOTICE: snitch: BEFORE any DROP TRIGGER ! ERROR: unexpected name list length (3) ! create conversion test for 'utf8' to 'sjis' from utf8_to_sjis; ! NOTICE: snitch: BEFORE any CREATE CONVERSION ! NOTICE: snitch: AFTER any CREATE CONVERSION ! create default conversion test2 for 'utf8' to 'sjis' from utf8_to_sjis; ! NOTICE: snitch: BEFORE any CREATE CONVERSION ! NOTICE: snitch: AFTER any CREATE CONVERSION ! alter conversion test2 rename to test3; ! NOTICE: snitch: BEFORE any ALTER CONVERSION ! NOTICE: snitch: BEFORE ALTER CONVERSION public test2 ! NOTICE: snitch: AFTER any ALTER CONVERSION ! drop conversion test3; ! NOTICE: snitch: BEFORE any DROP CONVERSION ! NOTICE: snitch: AFTER any DROP CONVERSION ! drop conversion test; ! NOTICE: snitch: BEFORE any DROP CONVERSION ! NOTICE: snitch: AFTER any DROP CONVERSION create text search configuration test (parser = "default"); NOTICE: snitch: BEFORE any CREATE TEXT SEARCH CONFIGURATION NOTICE: snitch: AFTER CREATE TEXT SEARCH CONFIGURATION public test *************** *** 453,458 **** create text search template test_template ( --- 501,522 ---- NOTICE: snitch: BEFORE any CREATE TEXT SEARCH TEMPLATE NOTICE: snitch: AFTER CREATE TEXT SEARCH TEMPLATE public test_template NOTICE: snitch: AFTER any CREATE TEXT SEARCH TEMPLATE + drop text search configuration test; + NOTICE: snitch: BEFORE any DROP TEXT SEARCH CONFIGURATION + NOTICE: snitch: BEFORE DROP TEXT SEARCH CONFIGURATION public test + NOTICE: snitch: AFTER any DROP TEXT SEARCH CONFIGURATION + drop text search dictionary test_stem; + NOTICE: snitch: BEFORE any DROP TEXT SEARCH DICTIONARY + NOTICE: snitch: BEFORE DROP TEXT SEARCH DICTIONARY public test_stem + NOTICE: snitch: AFTER any DROP TEXT SEARCH DICTIONARY + drop text search parser test_parser; + NOTICE: snitch: BEFORE any DROP TEXT SEARCH PARSER + NOTICE: snitch: BEFORE DROP TEXT SEARCH PARSER public test_parser + NOTICE: snitch: AFTER any DROP TEXT SEARCH PARSER + drop text search template test_template; + NOTICE: snitch: BEFORE any DROP TEXT SEARCH TEMPLATE + NOTICE: snitch: BEFORE DROP TEXT SEARCH TEMPLATE public test_template + NOTICE: snitch: AFTER any DROP TEXT SEARCH TEMPLATE create function cmd.testcast(text) returns int4 language plpgsql as $$begin return 4::int4;end;$$; NOTICE: snitch: BEFORE any CREATE FUNCTION NOTICE: snitch: AFTER CREATE FUNCTION cmd testcast *************** *** 489,498 **** NOTICE: drop cascades to type cmd2.us_postal_code NOTICE: snitch: AFTER any DROP SCHEMA drop role regbob; NOTICE: snitch: BEFORE any DROP ROLE ! NOTICE: snitch: AFTER any DROP ROLE drop command trigger snitch_before; drop command trigger snitch_after; drop command trigger snitch_create_table; drop command trigger snitch_create_view; drop command trigger snitch_alter_table; drop command trigger snitch_alter_seq; --- 553,564 ---- NOTICE: snitch: AFTER any DROP SCHEMA drop role regbob; NOTICE: snitch: BEFORE any DROP ROLE ! ERROR: role "regbob" cannot be dropped because some objects depend on it ! DETAIL: 3 objects in database foo drop command trigger snitch_before; drop command trigger snitch_after; drop command trigger snitch_create_table; + drop command trigger snitch_create_seq; drop command trigger snitch_create_view; drop command trigger snitch_alter_table; drop command trigger snitch_alter_seq; *************** *** 521,523 **** drop command trigger snitch_alter_domain; --- 587,597 ---- drop command trigger snitch_create_type; drop command trigger snitch_alter_type; drop command trigger snitch_before_alter_function; + drop command trigger snitch_alter_conversion; + drop command trigger snitch_drop_agg; + drop command trigger snitch_before_drop_tsconf; + drop command trigger snitch_before_drop_tsdict; + drop command trigger snitch_before_drop_tsparser; + drop command trigger snitch_before_drop_tstmpl; + drop command trigger snitch_vacuum; + drop command trigger snitch_reindex; *** a/src/test/regress/sql/cmdtriggers.sql --- b/src/test/regress/sql/cmdtriggers.sql *************** *** 30,35 **** alter command trigger snitch_before set enable; --- 30,36 ---- alter command trigger snitch_after_ rename to snitch_after; create command trigger snitch_create_table after create table execute procedure snitch(); + create command trigger snitch_create_seq after create sequence execute procedure snitch(); create command trigger snitch_create_view after create view execute procedure snitch(); create command trigger snitch_alter_table after alter table execute procedure snitch(); create command trigger snitch_alter_seq after alter sequence execute procedure snitch(); *************** *** 59,64 **** create command trigger snitch_alter_domain before alter domain execute procedure --- 60,75 ---- create command trigger snitch_create_type before create type execute procedure snitch(); create command trigger snitch_alter_type before alter type execute procedure snitch(); create command trigger snitch_before_alter_function before alter function execute procedure snitch(); + create command trigger snitch_alter_conversion before alter conversion execute procedure snitch(); + create command trigger snitch_drop_agg before drop aggregate execute procedure snitch(); + + create command trigger snitch_before_drop_tsconf before drop text search configuration execute procedure snitch(); + create command trigger snitch_before_drop_tsdict before drop text search dictionary execute procedure snitch(); + create command trigger snitch_before_drop_tsparser before drop text search parser execute procedure snitch(); + create command trigger snitch_before_drop_tstmpl before drop text search template execute procedure snitch(); + + create command trigger snitch_vacuum before vacuum execute procedure snitch(); + create command trigger snitch_reindex before reindex execute procedure snitch(); create schema cmd; create schema cmd2; *************** *** 113,118 **** alter view cmd.view_test2 alter column id drop default; --- 124,130 ---- cluster cmd.foo using foo_pkey; vacuum cmd.foo; vacuum; + reindex table cmd.foo; set session_replication_role to replica; create table cmd.bar(); *************** *** 121,133 **** reset session_replication_role; create index idx_foo on cmd.foo(t); drop index cmd.idx_foo; ! create function cmd.fun(int) returns text language sql as $$ select t from cmd.foo where id = $1; $$; ! alter function cmd.fun(int) strict; ! alter function cmd.fun(int) rename to notfun; ! alter function cmd.notfun(int) set schema public; ! drop function public.notfun(int); create function cmd.plus1(int) returns bigint language sql as $$ select $1::bigint + 1; $$; --- 133,147 ---- create index idx_foo on cmd.foo(t); drop index cmd.idx_foo; ! create function fun(int) returns text language sql as $$ select t from cmd.foo where id = $1; $$; ! alter function fun(int) strict; ! alter function fun(int) rename to notfun; ! alter function notfun(int) set schema cmd; ! alter function cmd.notfun(int) owner to regbob; ! alter function cmd.notfun(int) cost 77; ! drop function cmd.notfun(int); create function cmd.plus1(int) returns bigint language sql as $$ select $1::bigint + 1; $$; *************** *** 185,190 **** create trigger footg before update on cmd.foo for each row execute procedure cmd --- 199,210 ---- alter trigger footg on cmd.foo rename to foo_trigger; drop trigger foo_trigger on cmd.foo; + create conversion test for 'utf8' to 'sjis' from utf8_to_sjis; + create default conversion test2 for 'utf8' to 'sjis' from utf8_to_sjis; + alter conversion test2 rename to test3; + drop conversion test3; + drop conversion test; + create text search configuration test (parser = "default"); create text search dictionary test_stem ( *************** *** 205,210 **** create text search template test_template ( --- 225,235 ---- lexize = dsimple_lexize ); + drop text search configuration test; + drop text search dictionary test_stem; + drop text search parser test_parser; + drop text search template test_template; + create function cmd.testcast(text) returns int4 language plpgsql as $$begin return 4::int4;end;$$; create cast (text as int4) with function cmd.testcast(text) as assignment; *************** *** 218,223 **** drop command trigger snitch_before; --- 243,249 ---- drop command trigger snitch_after; drop command trigger snitch_create_table; + drop command trigger snitch_create_seq; drop command trigger snitch_create_view; drop command trigger snitch_alter_table; drop command trigger snitch_alter_seq; *************** *** 247,249 **** drop command trigger snitch_alter_domain; --- 273,285 ---- drop command trigger snitch_create_type; drop command trigger snitch_alter_type; drop command trigger snitch_before_alter_function; + drop command trigger snitch_alter_conversion; + drop command trigger snitch_drop_agg; + + drop command trigger snitch_before_drop_tsconf; + drop command trigger snitch_before_drop_tsdict; + drop command trigger snitch_before_drop_tsparser; + drop command trigger snitch_before_drop_tstmpl; + + drop command trigger snitch_vacuum; + drop command trigger snitch_reindex; --=-=-=--