Hi,
Thom Brown <[email protected]> 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:
> … 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 :)
> 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 <[email protected]> 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
*** a/doc/src/sgml/ref/create_command_trigger.sgml
--- b/doc/src/sgml/ref/create_command_trigger.sgml
***************
*** 41,48 **** CREATE COMMAND TRIGGER <replaceable class="PARAMETER">name</replaceable> { 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 <replaceable class="PARAMETER">name</replaceable> { BEFOR
</para>
<para>
! Note that objects dropped by effect of <literal>DROP CASCADE</literal>
! 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 <literal>DROP OWNED BY</literal>.
</para>
<para>
--- 135,145 ----
</para>
<para>
! Note that objects dropped by the effect of <literal>DROP
! CASCADE</literal> 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 <literal>DROP OWNED
! BY</literal>.
</para>
<para>
***************
*** 192,198 **** CREATE COMMAND TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFOR
you are connected to.
</para>
<para>
! Commands that exercize their own transaction control are only
supported in <literal>BEFORE</literal> command triggers, that's the
case for <literal>VACUUM</literal>, <literal>CLUSTER</literal>
<literal>CREATE INDEX CONCURRENTLY</literal>, and <literal>REINDEX
--- 191,197 ----
you are connected to.
</para>
<para>
! Commands that exercise their own transaction control are only
supported in <literal>BEFORE</literal> command triggers, that's the
case for <literal>VACUUM</literal>, <literal>CLUSTER</literal>
<literal>CREATE INDEX CONCURRENTLY</literal>, and <literal>REINDEX
***************
*** 215,220 **** CREATE COMMAND TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFOR
--- 214,224 ----
not to be able to take over control from a superuser.
</para>
<para>
+ 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 <literal>ANY</literal> command support more commands than
just this list, and will only provide the <literal>command
tag</literal> argument as <literal>NOT NULL</literal>. Supporting more
***************
*** 244,257 **** CREATE COMMAND TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFOR
tag</literal>, <literal>objectid</literal> (can be null in case of a
BEFORE CREATE or an AFTER DROP command trigger
timing), <literal>schemaname</literal> (can be null for objects not
! living in a schema, and for sequences due to an implementation limit)
! and <literal>object name</literal> (can be null for any command
! triggers).
! </para>
! <para>
! The command <literal>CREATE SEQUENCE</literal> lacks support for
! the <literal>schemaname</literal> command trigger argument, it
! provides <literal>NULL</literal> in all cases.
</para>
</listitem>
</varlistentry>
--- 248,255 ----
tag</literal>, <literal>objectid</literal> (can be null in case of a
BEFORE CREATE or an AFTER DROP command trigger
timing), <literal>schemaname</literal> (can be null for objects not
! living in a schema) and <literal>object name</literal> (can be null
! for any command triggers).
</para>
</listitem>
</varlistentry>
*** 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 <NULL> cmd
***************
*** 63,72 **** NOTICE: snitch: BEFORE any CREATE SCHEMA
--- 74,85 ----
NOTICE: snitch: BEFORE CREATE SCHEMA <NULL> 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 <NULL> 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 <NULL> <NULL>
+ reindex table cmd.foo;
+ NOTICE: snitch: BEFORE any REINDEX
+ NOTICE: snitch: BEFORE REINDEX <NULL> 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 <NULL> 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;
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
