PostgreSQL的触发器

详细了解PostgreSQL中触发器的管理与使用

概览

  • 触发器行为概述
  • 触发器的分类
  • 触发器的功能
  • 触发器的种类
  • 触发器的触发
  • 触发器的创建
  • 触发器的修改
  • 触发器的查询
  • 触发器的性能

触发器概述

触发器行为概述:英文中文

触发器分类

触发时机:BEFORE, AFTER, INSTEAD

触发事件:INSERT, UPDATE, DELETE,TRUNCATE

触发范围:语句级,行级

内部创建:用于约束的触发器,用户定义的触发器

触发模式:origin|local(O), replica(R),disable(D)

触发器操作

触发器的操作通过SQL DDL语句进行,包括CREATE|ALTER|DROP TRIGGER,以及ALTER TABLE ENABLE|DISABLE TRIGGER进行。注意PostgreSQL内部的约束是通过触发器实现的。

创建

CREATE TRIGGER 可以用于创建触发器。

CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

event包括
    INSERT
    UPDATE [ OF column_name [, ... ] ]
    DELETE
    TRUNCATE

删除

DROP TRIGGER 用于移除触发器。

DROP TRIGGER [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]

修改

ALTER TRIGGER 用于修改触发器定义,注意这里只能修改触发器名,以及其依赖的扩展。

ALTER TRIGGER name ON table_name RENAME TO new_name
ALTER TRIGGER name ON table_name DEPENDS ON EXTENSION extension_name

启用禁用触发器,修改触发模式是通过ALTER TABLE的子句实现的。

ALTER TABLE 包含了一系列触发器修改的子句:

ALTER TABLE tbl ENABLE TRIGGER tgname; -- 设置触发模式为O (本地连接写入触发,默认)
ALTER TABLE tbl ENABLE REPLICA TRIGGER tgname; -- 设置触发模式为R (复制连接写入触发)
ALTER TABLE tbl ENABLE ALWAYS TRIGGER tgname; -- 设置触发模式为A (总是触发)
ALTER TABLE tbl DISABLE TRIGGER tgname; -- 设置触发模式为D (禁用)

注意这里在ENABLEDISABLE触发器时,可以指定用USER替换具体的触发器名称,这样可以只禁用用户显式创建的触发器,不会把系统用于维持约束的触发器也禁用了。

ALTER TABLE tbl_name DISABLE TRIGGER USER; -- 禁用所有用户定义的触发器,系统触发器不变  
ALTER TABLE tbl_name DISABLE TRIGGER ALL;  -- 禁用所有触发器
ALTER TABLE tbl_name ENABLE TRIGGER USER;  -- 启用所有用户定义的触发器
ALTER TABLE tbl_name ENABLE TRIGGER ALL;   -- 启用所有触发器

查询

获取表上的触发器

最简单的方式当然是psql的\d+ tablename。但这种方式只会列出用户创建的触发器,不会列出与表上约束相关联的触发器。直接查询系统目录pg_trigger,并通过tgrelid用表名过滤

SELECT * FROM pg_trigger WHERE tgrelid = 'tbl_name'::RegClass;

获取触发器定义

pg_get_triggerdef(trigger_oid oid)函数可以给出触发器的定义。

该函数输入参数为触发器OID,返回创建触发器的SQL DDL语句。

SELECT pg_get_triggerdef(oid) FROM pg_trigger; -- WHERE xxx

触发器视图

pg_trigger (中文) 提供了系统中触发器的目录

名称 类型 引用 描述
oid oid 触发器对象标识,系统隐藏列
tgrelid oid pg_class.oid 触发器所在的表 oid
tgname name 触发器名,表级命名空间内不重名
tgfoid oid pg_proc.oid 触发器所调用的函数
tgtype int2 触发器类型,触发条件,详见注释
tgenabled char 触发模式,详见下。O|R|A|D
tgisinternal bool 如果是内部用于约束的触发器则为真
tgconstrrelid oid pg_class.oid 参照完整性约束中被引用的表,无则为0
tgconstrindid oid pg_class.oid 支持约束的相关索引,没有则为0
tgconstraint oid pg_constraint.oid 与触发器相关的约束对象
tgdeferrable bool DEFERRED则为真
tginitdeferred bool INITIALLY DEFERRED则为真
tgnargs int2 传入触发器函数的字符串参数个数
tgattr int2vector pg_attribute.attnum 如果是列级更新触发器,这里存储列号,否则为空数组。
tgargs bytea 传递给触发器的参数字符串,C风格零结尾字符串
tgqual pg_node_tree 触发器WHEN条件的内部表示
tgoldtable name OLD TABLEREFERENCING列名称,无则为空
tgnewtable name NEW TABLEREFERENCING列名称,无则为空

触发器类型

触发器类型tgtype包含了触发器触发条件相关信息:BEFORE|AFTER|INSTEAD OF, INSERT|UPDATE|DELETE|TRUNCATE

TRIGGER_TYPE_ROW         (1 << 0)  // [0] 0:语句级 	1:行级
TRIGGER_TYPE_BEFORE      (1 << 1)  // [1] 0:AFTER 	1:BEFORE
TRIGGER_TYPE_INSERT      (1 << 2)  // [2] 1: INSERT
TRIGGER_TYPE_DELETE      (1 << 3)  // [3] 1: DELETE
TRIGGER_TYPE_UPDATE      (1 << 4)  // [4] 1: UPDATE
TRIGGER_TYPE_TRUNCATE    (1 << 5)  // [5] 1: TRUNCATE
TRIGGER_TYPE_INSTEAD     (1 << 6)  // [6] 1: INSTEAD OF 

触发器模式

触发器tgenabled字段控制触发器的工作模式,参数session_replication_role 可以用于配置触发器的触发模式。该参数可以在会话层级更改,可能的取值包括:origin(default),replica,local

(D)isable触发器永远不会被触发,(A)lways触发器在任何情况下触发, (O)rigin触发器会在origin|local模式触发(默认),而 (R)eplica触发器replica模式触发。R触发器主要用于逻辑复制,例如pglogical的复制连接就会将会话参数session_replication_role设置为replica,而R触发器只会在该连接进行的变更上触发。

ALTER TABLE tbl ENABLE TRIGGER tgname; -- 设置触发模式为O (本地连接写入触发,默认)
ALTER TABLE tbl ENABLE REPLICA TRIGGER tgname; -- 设置触发模式为R (复制连接写入触发)
ALTER TABLE tbl ENABLE ALWAYS TRIGGER tgname; -- 设置触发模式为A (始终触发)
ALTER TABLE tbl DISABLE TRIGGER tgname; -- 设置触发模式为D (禁用)

information_schema中还有两个触发器相关的视图:information_schema.triggers, information_schema.triggered_update_columns,表过不提。

触发器FAQ

触发器可以建在哪些类型的表上?

普通表(分区表主表,分区表分区表,继承表父表,继承表子表),视图,外部表。

触发器的类型限制

  • 视图上不允许建立BEFOREAFTER触发器(不论是行级还是语句级)
  • 视图上只能建立INSTEAD OF触发器,INSERTEAD OF触发器也只能建立在视图上,且只有行级,不存在语句级INSTEAD OF触发器。
  • INSTEAD OF` 触发器只能定义在视图上,并且只能使用行级触发器,不能使用语句级触发器。

触发器与锁

在表上创建触发器会先尝试获取表级的Share Row Exclusive Lock。这种锁会阻止底层表的数据变更,且自斥。因此创建触发器会阻塞对表的写入。

触发器与COPY的关系

COPY只是消除了数据解析打包的开销,实际写入表中时仍然会触发触发器,就像INSERT一样。