Postgresql - plpgsql - Event Trigger 讲解

PostgreSQL还提供了事件触发器。事件触发器是特定数据库的全局触发器，能够捕获DDL事件。

与常规触发器一样，事件触发器可以用任何包含事件触发器支持的过程语言编写，也可以用C语言编写，但不能用纯SQL编写。

 

1. Overview of Event Trigger Behavior

每当与事件相关联的事件发生在定义它的数据库中时，事件触发器就会激发。目前，支持的事件只有是 ddl_command_start、ddl_command_end、table_rewrite和sql_drop。对其他事件的支持可以在将来的版本中添加。

DDL命令启动事件发生在执行create、alter、drop、security label、comment、grant或revoke命令之前。在触发事件触发器之前，不检查受影响的对象是否存在。但是，作为例外，针对共享对象（数据库、角色和表空间）的DDL命令或针对事件触发器本身的命令不会发生此事件。事件触发器机制不支持这些对象类型。ddl_command_start 也发生在执行select into命令之前，因为这相当于CREATE TABLE AS。

DDL命令结束事件发生在同一组命令执行之后。要获得有关发生的DDL操作的更多详细信息，请使用DDL命令结束事件触发器代码中的set返回函数pg_event_trigger_ddl_commands()。注意，触发器在操作发生后（但在事务提交之前）触发，因此系统目录可以读取为已更改。

对于删除数据库对象的任何操作，SQL删除事件发生在DDL命令结束事件触发器之前。要列出已删除的对象，请使用SQL删除事件触发器代码中的set返回函数pg_event_trigger_dropped_objects()。注意，触发器是在对象从系统目录中删除后执行的，因此无法再查找它们。

表重写事件发生在通过命令alter table和alter type的某些操作重写表之前。虽然其他控制语句可用于重写表，如cluster和vacuum，但表重写事件不会被它们触发。

无法在中止的事务中执行事件触发器（与其他函数一样）。因此，如果DDL命令因错误而失败，则不会执行任何相关的 ddl_command_end 触发器。相反，如果ddl_command_start触发器因错误而失败，则不会再触发事件触发器，也不会尝试执行命令本身。类似地，如果ddl_command_end 触发器因错误而失败，DDL语句的效果将回滚，就像在包含事务中止的任何其他情况下一样。

事件触发器是使用命令create event trigger创建的。要创建事件触发器，必须首先创建具有特殊返回类型事件触发器的函数。此函数不需要（也可能不需要）返回值；返回类型仅充当将作为事件触发器调用函数的信号。

如果为特定事件定义了多个事件触发器，则它们将按触发器名称的字母顺序触发。

触发器定义还可以指定when条件，例如，只能为用户希望截获的特定命令触发DDL命令启动触发器。这种触发器的常见用途是限制用户可以执行的DDL操作的范围。

 

 

2. Event Trigger Firing Matrix

lists all commands for which event triggers are supported.

Command Tag	ddl_command_start	ddl_command_end	sql_drop	table_rewrite	Notes
ALTER AGGREGATE	X	X	-	-
ALTER COLLATION	X	X	-	-
ALTER CONVERSION	X	X	-	-
ALTER DOMAIN	X	X	-	-
ALTER EXTENSION	X	X	-	-
ALTER FOREIGN DATA WRAPPER	X	X	-	-
ALTER FOREIGN TABLE	X	X	X	-
ALTER FUNCTION	X	X	-	-
ALTER LANGUAGE	X	X	-	-
ALTER OPERATOR	X	X	-	-
ALTER OPERATOR CLASS	X	X	-	-
ALTER OPERATOR FAMILY	X	X	-	-
ALTER POLICY	X	X	-	-
ALTER SCHEMA	X	X	-	-
ALTER SEQUENCE	X	X	-	-
ALTER SERVER	X	X	-	-
ALTER TABLE	X	X	X	X
ALTER TEXT SEARCH CONFIGURATION	X	X	-	-
ALTER TEXT SEARCH DICTIONARY	X	X	-	-
ALTER TEXT SEARCH PARSER	X	X	-	-
ALTER TEXT SEARCH TEMPLATE	X	X	-	-
ALTER TRIGGER	X	X	-	-
ALTER TYPE	X	X	-	X
ALTER USER MAPPING	X	X	-	-
ALTER VIEW	X	X	-	-
CREATE AGGREGATE	X	X	-	-
COMMENT	X	X	-	-	Only for local objects
CREATE CAST	X	X	-	-
CREATE COLLATION	X	X	-	-
CREATE CONVERSION	X	X	-	-
CREATE DOMAIN	X	X	-	-
CREATE EXTENSION	X	X	-	-
CREATE FOREIGN DATA WRAPPER	X	X	-	-
CREATE FOREIGN TABLE	X	X	-	-
CREATE FUNCTION	X	X	-	-
CREATE INDEX	X	X	-	-
CREATE LANGUAGE	X	X	-	-
CREATE OPERATOR	X	X	-	-
CREATE OPERATOR CLASS	X	X	-	-
CREATE OPERATOR FAMILY	X	X	-	-
CREATE POLICY	X	X	-	-
CREATE RULE	X	X	-	-
CREATE SCHEMA	X	X	-	-
CREATE SEQUENCE	X	X	-	-
CREATE SERVER	X	X	-	-
CREATE STATISTICS	X	X	-	-
CREATE TABLE	X	X	-	-
CREATE TABLE AS	X	X	-	-
CREATE TEXT SEARCH CONFIGURATION	X	X	-	-
CREATE TEXT SEARCH DICTIONARY	X	X	-	-
CREATE TEXT SEARCH PARSER	X	X	-	-
CREATE TEXT SEARCH TEMPLATE	X	X	-	-
CREATE TRIGGER	X	X	-	-
CREATE TYPE	X	X	-	-
CREATE USER MAPPING	X	X	-	-
CREATE VIEW	X	X	-	-
DROP AGGREGATE	X	X	X	-
DROP CAST	X	X	X	-
DROP COLLATION	X	X	X	-
DROP CONVERSION	X	X	X	-
DROP DOMAIN	X	X	X	-
DROP EXTENSION	X	X	X	-
DROP FOREIGN DATA WRAPPER	X	X	X	-
DROP FOREIGN TABLE	X	X	X	-
DROP FUNCTION	X	X	X	-
DROP INDEX	X	X	X	-
DROP LANGUAGE	X	X	X	-
DROP OPERATOR	X	X	X	-
DROP OPERATOR CLASS	X	X	X	-
DROP OPERATOR FAMILY	X	X	X	-
DROP OWNED	X	X	X	-
DROP POLICY	X	X	X	-
DROP RULE	X	X	X	-
DROP SCHEMA	X	X	X	-
DROP SEQUENCE	X	X	X	-
DROP SERVER	X	X	X	-
DROP STATISTICS	X	X	X	-
DROP TABLE	X	X	X	-
DROP TEXT SEARCH CONFIGURATION	X	X	X	-
DROP TEXT SEARCH DICTIONARY	X	X	X	-
DROP TEXT SEARCH PARSER	X	X	X	-
DROP TEXT SEARCH TEMPLATE	X	X	X	-
DROP TRIGGER	X	X	X	-
DROP TYPE	X	X	X	-
DROP USER MAPPING	X	X	X	-
DROP VIEW	X	X	X	-
GRANT	X	X	-	-	Only for local objects
IMPORT FOREIGN SCHEMA	X	X	-	-
REVOKE	X	X	-	-	Only for local objects
SECURITY LABEL	X	X	-	-	Only for local objects
SELECT INTO	X	X	-	-

 

 

3. event trigger functions

pg_event_trigger_ddl_commands returns a list of DDL commands executed by each user action, when invoked in a function attached to a ddl_command_end event trigger. If called in any other context, an error is raised. pg_event_trigger_ddl_commands returns one row for each base command executed; some commands that are a single SQL sentence may return more than one row. This function returns the following columns:

Name	Type	Description
classid	oid	OID of catalog the object belongs in
objid	oid	OID of the object itself
objsubid	integer	Sub-object ID (e.g. attribute number for a column)
command_tag	text	Command tag
object_type	text	Type of the object
schema_name	text	Name of the schema the object belongs in, if any; otherwise NULL. No quoting is applied.
object_identity	text	Text rendering of the object identity, schema-qualified. Each identifier included in the identity is quoted if necessary.
in_extension	bool	True if the command is part of an extension script
command	pg_ddl_command	A complete representation of the command, in internal format. This cannot be output directly, but it can be passed to other functions to obtain different pieces of information about the command.

 

 

pg_event_trigger_dropped_objects returns a list of all objects dropped by the command in whose sql_drop event it is called. If called in any other context, pg_event_trigger_dropped_objects raises an error. pg_event_trigger_dropped_objects returns the following columns:

Name	Type	Description
classid	oid	OID of catalog the object belonged in
objid	oid	OID of the object itself
objsubid	integer	Sub-object ID (e.g. attribute number for a column)
original	bool	True if this was one of the root object(s) of the deletion
normal	bool	True if there was a normal dependency relationship in the dependency graph leading to this object
is_temporary	bool	True if this was a temporary object
object_type	text	Type of the object
schema_name	text	Name of the schema the object belonged in, if any; otherwise NULL. No quoting is applied.
object_name	text	Name of the object, if the combination of schema and name can be used as a unique identifier for the object; otherwise NULL. No quoting is applied, and name is never schema-qualified.
object_identity	text	Text rendering of the object identity, schema-qualified. Each identifier included in the identity is quoted if necessary.
address_names	text[]	An array that, together with object_type and address_args, can be used by the pg_get_object_address() function to recreate the object address in a remote server containing an identically named object of the same kind
address_args	text[]	Complement for address_names

 

Table Rewrite information

Name	Return Type	Description
pg_event_trigger_table_rewrite_oid()	Oid	The OID of the table about to be rewritten.
pg_event_trigger_table_rewrite_reason()	int	The reason code(s) explaining the reason for rewriting. The exact meaning of the codes is release dependent.