一个触发器是一种声明，告诉数据库应该在执行特定的 *** 作的时候执行特定的函数。触发器可以定义在一个 INSERT, UPDATE, DELETE 命令之前或者之后执行，要么是对每行执行一次，要么是对每条 SQL 语句执行一次。如果发生触发器事件，那么将在合适的时刻调用触发器函数以处理该事件。触发器函数必须在创建触发器之前，作为一个没有参数并且返回 trigger 类型的函数定义。触发器函数通过特殊的 TriggerData 结构接收其输入，而不是用普通的函数参数方式.
注意:
一.按语句触发的触发器应该总是返回 NULL.
二.如果必要，按行触发的触发器函数可以给调用它的执行者返回一行数据(一个类型为 HeapTuple 的数值)，那些在 *** 作之前触发的触发器有以下选择
1. 它可以返回 NULL 以忽略对当前行的 *** 作。这就指示执行器不要执行调用该触
发器的行级别 *** 作(对特定行的插入或者更改)。
2.只用于 INSERT 和 UPDATE 行触发器：返回的行将成为被插入的行或者是成为
将要更新的行。这样就允许触发器函数修改将要被插入或者更新的行。
一个无意导致任何这类行为的在 *** 作之前触发的行级触发器必须仔细返回那个被当作新行传进来的行。也就是说，对于 INSERT 和 UPDATE 触发器而言，是 NEW 行，对于 DELETE触发器而言，是 OLD 行。
三. 对于在 *** 作之后触发的行级触发器，其返回值会被忽略，因此可以回NULL。
下面通过具体的例子来说明在postgresql中触发器的建立和使用(老规矩先写代码然后讲解)
#include <postgres.h>
#include <executor/spi.h>
#include <funcapi.h>
#include <commands/trigger.h>
#include <fmgr.h>
extern Datum pg_trigf(PG_FUNCTION_ARGS)
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC
#endif
PG_FUNCTION_INFO_V1(pg_trigf)
Datum
pg_trigf(PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *)fcinfo->context
HeapTuple rettuple = NULL
int ret
int proc/* to store the value of SPI_processed (actual row number)*/
/* to be sure this function will be called by trigger */
if (!(CALLED_AS_TRIGGER(fcinfo))) {
elog(ERROR, "trigf: not called by triggermanager")
}
/* should be fired by statement */
if (TRIGGER_FIRED_FOR_ROW(trigdata->tg_event)) {
elog(ERROR, "cannot process row events")
}
/* should be fired before event */
if (TRIGGER_FIRED_AFTER(trigdata->tg_event)) {
elog(ERROR, "must be fired before event")
}
/* connect spi manager */
if ((ret = SPI_connect()) <0) {
elog(INFO, "SPI_connect failed: SPI_connectreturned: %d", ret)
return PointerGetDatum(rettuple)
}
/* check the permanent table name(perm_user) existsor not*/
ret = SPI_exec("SELECT tablename FROM pg_tables WHERE tablename LIKE'perm!_user' ESCAPE '!'", 1)
proc = SPI_processed
if (ret != SPI_OK_SELECT) {
elog(INFO, "SPI_exec execute error: user table.")
SPI_finish()
return PointerGetDatum(rettuple)
}
/* create the permanent table(perm_user) if it does not exist */
if (proc <1) {
/* create permanent table: perm_user */
ret = SPI_exec("CREATE TABLE perm_user ASSELECT * FROM tbl_user", 0)
if (ret != SPI_OK_SELINTO ) {
elog(INFO, "SPI_execexecute error: fail to create perm_user")
SPI_finish()
returnPointerGetDatum(rettuple)
}
/* set attribute to perm_user */
ret = SPI_exec("ALTER TABLE perm_user ADD PRIMARYKEY (user_name)", 0)
if (ret != SPI_OK_SELINTO) {
elog(INFO, "SPI_execexecute error: fail to add primary key to perm_user")
SPI_finish()
returnPointerGetDatum(rettuple)
}
/* set attribute to perm_user*/
ret = SPI_exec("ALTER TABLE perm_user ALTERuser_passwd SET NOT NULL", 0)
if (ret != SPI_OK_SELINTO) {
elog(INFO, "SPI_execexecute error: fail to set attribute to password.")
SPI_finish()
returnPointerGetDatum(rettuple)
}
}
.....
/* check the permanent table name(perm_member) exists or not */
ret = SPI_exec("SELECT tablename FROM pg_tables WHERE tablename LIKE'perm!_member' ESCAPE '!'", 1)
proc = SPI_processed
if (ret != SPI_OK_SELECT) {
elog(INFO, "SPI_exec execute errortbl_member")
SPI_finish()
return PointerGetDatum(rettuple)
}
/* create the permanent table(perm_member) if it does not exist */
if (proc <1) {
/* create permanent table: perm_member */
ret = SPI_exec("CREATE TABLE perm_member ASSELECT * FROM tbl_member", 0)
if (ret != SPI_OK_SELINTO) {
elog(INFO, "SPI_execexecute error")
SPI_finish()
returnPointerGetDatum(rettuple)
}
/* set attribute to perm_member */
ret = SPI_exec("ALTER TABLE perm_member ADDCONSTRAINT user_fk FOREIGN KEY (user_name) REFERENCES perm_user(user_name) ONDELETE CASCADE ON UPDATE CASCADE", 0)
if (ret != SPI_OK_UTILITY) {
elog(INFO, "SPI_execexecute error: fail to set attribute to user_name.")
SPI_finish()
returnPointerGetDatum(rettuple)
}
/* set attribute to perm_member */
ret = SPI_exec("ALTER TABLE perm_member ADDCONSTRAINT group_fk FOREIGN KEY (grp_name) REFERENCES perm_group(grp_name) ONDELETE CASCADE ON UPDATE CASCADE", 0)
if (ret != SPI_OK_UTILITY) {
elog(INFO, "SPI_execexecute error: fail to set attribute to grp_name.")
SPI_finish()
return PointerGetDatum(rettuple)
}
/* add primary key to perm_member */
ret = SPI_exec("ALTER TABLE perm_member ADDPRIMARY KEY (user_name, grp_name)", 0)
if (ret != SPI_OK_UTILITY) {
elog(INFO, "SPI_execexecute error: fail to add primary key to perm_member.")
SPI_finish()
returnPointerGetDatum(rettuple)
}
}
/*close connect with SPI manager */
SPI_finish()
/* return back must be NULL*/
return PointerGetDatum(rettuple)
}
这个函数写法与postgresql服务端函数的写法很相似, 但是不完全相同.具体需要注意的地方是:
1. 需要多添加头文件:#include <commands/trigger.h>
2. 这个函数的返回值一定是trigger类型的.
3. 函数的开始最好确认这个函数是供触发器调用的并且明确一下自己要写的触发器的类型是什么,然后做一下判断,以免别的语句也触发的触发器.
二. 接下来的事情是编译:
gcc -fpic -c trigger.c-I/usr/local/postgreSQL/include/postgresql/server
gcc -shared -o trigger.so trigger.o
如果不明白可以参考手册(说句题外话,手册的作用实在是太大了,在手册中也提供了一例子).
三. 在数据库中创建函数和触发器:
/* create a trigger used to write memory and configmemory */
CREATE OR REPLACE FUNCTION pg_trigf() RETURNS trigger
AS 'filename'
LANGUAGE C IMMUTABLESTRICT
CREATE TRIGGER tbuser BEFORE INSERT OR UPDATE OR DELETE
ON tbl_user FOR EACH STATEMENT
EXECUTE PROCEDURE pg_trigf()
CREATE TRIGGER tbgroup BEFORE INSERT OR UPDATE OR DELETE
创建的触发器是语句触发器,这个和手册上的不一样, 手册上的是行触发器.
然后在数据库中使用SQL语句就可以看到触发器的效果了.
PostgreSQL传统的分区方法，使用约束来区分不同的分区存储数据（配置constraint_exclusion = partition），执行选择/删除/更新时执行计划根据约束和查询条件排除不需要的查询的分区表。调用COPY或插入数据时使用插入或规则，将数据插入对应的分区表。传统的做法，无论是查询还是插入，对性能的影响都较长。pg_pathman与传统的继承分区表做法有一个不同的地方，分区的定义放置在一张元数据表中，表的信息会缓存在内存中，同时使用HOOK来实现关系的替换，所以效率非常高。目前支持两种分区模式，范围和哈希，其中范围使用二进制搜索查找对应的分区，哈希使用哈希搜索查找对应的分区。
pg_pathman装备介绍
1. pg_pathman用到的hook
pg_pathman使用ProcessUtility_hook钩子来处理分区表的COPY查询。
RuntimeAppend（重写Append计划节点）
RuntimeMergeAppend（重写MergeAppend计划节点）
PartitionFilter（INSERT触发器的直接替代）
2. pg_pathman特性
目前支持range，hash分区。
支持自动分区管理（通过函数接口创建分区，自动将主表数据迁移到分区表），或手工分区管理（通过函数实现，将现有的表绑定到分区表，或者从分区表分割） 。
支持的分区分区类型包括int，float，date，以及其他常用类型，包括自定义的域。
通过CUSTOM SCAN实现了有效的分区表JOIN，子查询过滤分区。
使用RuntimeAppend和RuntimeMergeAppend自定义计划节点实现了动态分区选择。
PartitionFilter HOOK，实现就地插入，代替传统的插入触发器或插入规则。
支持自动添加分区。目前仅支持范围分区表。
支持从/到直接读取或写入分区表的复制，提高效率。
支持分区分区的更新，需要添加替换，如果不需要更新分区分区，则不建议添加此转换器，会产生一定的性能影响。
允许用户自定义定义函数，在创建分区时会自动触发。
非插入式创建分区表，以及后台自动将主表数据迁移到分区表，非插入式。
支持FDW，通过配置参数pg_pathman.insert_into_fdw =（禁用| postgres | any_fdw）支持postgres_fdw或任意fdw(外部分区)
支持GUC参数配置，注意通过使用了HOOK，如果其他插件也使用了相同的HOOK，需要将pg_pathman放在后面注册，如pg_stat_statements。
shared_preload_libraries ='pg_stat_statements，pg_pathman'
3. pg_pathman为什么高效
插入优化，使用PartitionFilter替换关系，替换初始化的方式。效率提高非常明显。
查询优化，分区定义加载在内存中，使用二进制搜索和哈希搜索对应范围与哈希分区表，使用RuntimeAppend和RuntimeMerge附加自定义计划节点以在运行时选择分区；
同时运行时过滤，支持子查询。传统的约束法不支持子查询过滤。
pg_pathman装备使用
1. 安装部署
--下载安装包：
https://github.com/postgrespro/pg_pathman
--pg_pathman安装：
unzip pg_pathman-master.zip
cd pg_pathman-master
make USE_PGXS=1
make USE_PGXS=1 install
--修改参数：
alter system set shared_preload_libraries=pg_stat_statements,pg_pathman,telepg_monitor
注意pg_pathman需写在pg_stat_statements之后
--重启实例：
$pg_ctl restart -m fast
--创建pg_pathman扩展：
\c ksl
postgres=# create extension pg_pathman
CREATE EXTENSION
ksl=# \dx
List of installed extensions
Name | Version | Schema | Description
-------------+---------+------------+-------------------------------------------------------
pageinspect | 1.7 | public | inspect the contents of database pages at a low level
pg_pathman | 1.5 | public | Partitioning tool for PostgreSQL
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(3 rows)
2. RANGE分区实战举例
创建需要分区的主表：
postgres=# create table part_test(id int, info text,
crt_time timestamp not null)-- 分区列必须有not null约束
CREATE TABLE
插入一批测试数据，模拟已经有数据了的主表：
postgres=# insert into part_test select id,md5(random()::text),clock_timestamp() + (id||' hour')::interval from generate_series(1,10000) t(id)
INSERT 0 10000
postgres=# select * from part_test limit 10
id | info | crt_time
----+----------------------------------+----------------------------
1 | 36fe1adedaa5b848caec4941f87d443a | 20XX-10-25 10:27:13.206713
2 | c7d7358e196a9180efb4d0a10269c889 | 20XX-10-25 11:27:13.206893
3 | 005bdb063550579333264b895df5b75e | 20XX-10-25 12:27:13.206904
4 | 6c900a0fc50c6e4da1ae95447c89dd55 | 20XX-10-25 13:27:13.20691
5 | 857214d8999348ed3cb0469b520dc8e5 | 20XX-10-25 14:27:13.206916
6 | 4495875013e96e625afbf2698124ef5b | 20XX-10-25 15:27:13.206921
7 | 82488cf7e44f87d9b879c70a9ed407d4 | 20XX-10-25 16:27:13.20693
8 | a0b92547c8f17f79814dfbb12b8694a0 | 20XX-10-25 17:27:13.206936
9 | 2ca09e0b85042b476fc235e75326b41b | 20XX-10-25 18:27:13.206942
10 | 7eb762e1ef7dca65faf413f236dff93d | 20XX-10-25 19:27:13.206947
(10 rows)
注意:
分区列必须有not null约束
分区个数必须能覆盖已有的所有记录.
创建分区，每个分区包含1个月的跨度数据：
postgres=#select create_range_partitions('part_test'::regclass, -- 主表OID
'crt_time', -- 分区列名
'2020-11-05 00:00:00'::timestamp, -- 开始值
interval '1 month', -- 间隔；interval 类型，用于时间分区表
24, -- 分多少个区
false) -- 不迁移数据
NOTICE: sequence "part_test_seq" does not exist, skipping
create_range_partitions
-------------------------
24
(1 row)
由于不迁移数据，所以数据还在主表：
postgres=# select count(*) from only part_test
count
-------
10000
(1 row)
使用非堵塞式的迁移接口：
postgres=# select partition_table_concurrently('part_test'::regclass,
10000,
1.0)
NOTICE: worker started, you can stop it with the following command: select stop_concurrent_part_task('part_test')
partition_table_concurrently
------------------------------
(1 row)
--查看后台的数据迁移任务：
select * from pathman_concurrent_part_tasks
迁移结束后，主表数据已经没有了，全部在分区中：
postgres=# select count(*) from only part_test
count
-------
0
(1 row)
数据迁移完成后，建议禁用主表，这样执行计划就不会出现主表了。
postgres=# select set_enable_parent('part_test'::regclass, false)
set_enable_parent
-------------------
(1 row)
postgres=# explain select * from part_test where crt_time = '2020-11-05 00:00:00'::timestamp
QUERY PLAN
-------------------------------------------------------------------------------
Append (cost=0.00..16.18 rows=1 width=45)
->Seq Scan on part_test_1 (cost=0.00..16.18 rows=1 width=45)
Filter: (crt_time = '2020-11-05 00:00:00'::timestamp without time zone)
(3 rows)
建议：
分区列必须有not null约束；
分区个数必须能覆盖已有的所有记录；
建议使用非堵塞式迁移接口；
建议数据迁移完成后，禁用主表。
1700w数据大概迁移了一个多小时，如果表有索引可以先删除索引，数据迁移完成后再建索引，因为在创建分区的时候，所有的分区表都会单独创建索引，这也是不能保证全局唯一的原因。
--禁止自动扩展分区：
select set_auto('part_test'::regclass, false)
insert into ksl.part_test values (1,'test','2023-01-01'::timestamp)
--向后添加分区：
select append_range_partition('part_test'::regclass)
--向前添加分区：
select prepend_range_partition('part_test'::regclass)
--停止迁移任务：
select top_concurrent_part_task('part_test'::regclass)
-- 查看后台的数据迁移任务：
select * from pathman_concurrent_part_tasks
-- 分裂范围分区,数据会自动迁移到另一个分区：
select split_range_partition(
'part_test_1'::regclass, -- 分区oid
'2020-11-25 00:00:00'::timestamp, -- 分裂值
'part_test_1_2')-- 分区表名
--合并范围分区,目前仅支持范围分区。
--指定两个需要合并分区，必须为相邻分区：
select merge_range_partitions('part_test_1'::regclass, 'part_test_1_2'::regclass)
--不相邻的分区合并会报错
select merge_range_partitions('part_test_2'::regclass, 'part_test_12'::regclass)
ERROR: partitions "part_test_2" and "part_test_12" are not adjacent
--合并后，会删掉其中一个分区表。
--删除单个范围分区：
drop_range_partition(
partition TEXT, -- 分区名称
delete_data BOOLEAN DEFAULT TRUE) -- 是否删除分区数据，如果false，表示分区数据迁移到主表。
--删除分区， 数据迁移到主表：
select drop_range_partition('part_test_1',false)
--删除分区，分区数据也删除，不迁移到主表：
select drop_range_partition('part_test_3',true)
--删除所有分区，并且指定是否要将数据迁移到主表：
drop_partitions(parent REGCLASS,delete_data BOOLEAN DEFAULT FALSE)
-- 删除所有分区表，并将数据迁移到主表
select drop_partitions('part_test'::regclass, false)
--绑定分区(已有的表加入分区表)。
--将已有的表，绑定到已有的某个分区主表。
--已有的表与主表要保持一致的结构，包括dropped columns。 (查看pg_attribute的一致性)
--如果设置了回调函数，会触发。
--绑定分区时，自动创建继承关系，自动创建约束。
attach_range_partition(
relation REGCLASS, -- 主表OID
partition REGCLASS, -- 分区表OID
start_value ANYELEMENT, -- 起始值
end_value ANYELEMENT) -- 结束值
create table part_test_1 (like part_test including all)
select attach_range_partition('part_test'::regclass, 'part_test_1'::regclass, '2020-11-05 00:00:00'::timestamp, '2020-12-05 00:00:00'::timestamp)
--解绑分区(将分区变成普通表)。
--将分区从主表的继承关系中删除, 不删数据，删除继承关系，删除约束。
detach_range_partition(partition REGCLASS) -- 指定分区名，转换为普通表
select detach_range_partition('part_test_2')
-- 更新触发器。
--如果分区字段要被更新，需要创建更新触发器，否则不需要。
create_range_update_trigger(parent REGCLASS)
ksl=>select * from part_test_3 limit 10
id | info | crt_time
------+----------------------------------+----------------------------
1450 | d16ae9fa14aabb821df6692beef610e6 | 2021-01-05 00:33:46.657077
1451 | b88247d2cb9acb9e98ba472f575f180c | 2021-01-05 01:33:46.657081
1452 | 344c48262f105e8622099b24d9ed7d8a | 2021-01-05 02:33:46.657086
1453 | bd6e36744447ab70a1624134de9dbde0 | 2021-01-05 03:33:46.65709
1454 | 3d8c3470df5dcbb1e5ad68974fabf11a | 2021-01-05 04:33:46.657094
1455 | 71664d8dcdad66ef2ccd0464cc61279b | 2021-01-05 05:33:46.657098
1456 | 7f0da1bec230ad34741081a5da79b995 | 2021-01-05 06:33:46.657102
1457 | 34045bcda2117d5643a54c29febd51b6 | 2021-01-05 07:33:46.657107
1458 | 103a593f0be11898153cf58d5ca576be | 2021-01-05 08:33:46.657111
1459 | 16e4d2340014ddfeb195c141c0395474 | 2021-01-05 09:33:46.657117
(10 rows)
--创建更新触发器前，如果更新分区字段后的值跨分区了，会报约束错误。
ksl=>update part_test set crt_time='2021-11-05 00:33:46.657077' where id=1450
ERROR: new row for relation "part_test_3" violates check constraint "pathman_part_test_3_check"
DETAIL: Failing row contains (1450, d16ae9fa14aabb821df6692beef610e6, 2021-11-05 00:33:46.657077).
--创建更新触发器后，正常。
--永久禁止分区表pg_pathman插件。
--可以针对单个分区主表禁用pg_pathman：
select disable_pathman_for('part_test')
禁用pg_pathman后，继承关系和约束不会变化，只是pg_pathman不介入custom scan 执行计划。
禁用pg_pathman后的执行计划：
postgres=# explain select * from part_test where crt_time='2020-11-25 00:00:00'::timestamp
QUERY PLAN
-------------------------------------------------------------------------------
Append (cost=0.00..16.00 rows=2 width=45)
->Seq Scan on part_test (cost=0.00..0.00 rows=1 width=45)
Filter: (crt_time = '2020-11-25 00:00:00'::timestamp without time zone)
-> Seq Scan on part_test_1 (cost=0.00..16.00 rows=1 width=45)
Filter: (crt_time = '2020-11-25 00:00:00'::timestamp without time zone)
(5 rows)
disable_pathman_for没有可逆 *** 作，请慎用。
--全局禁止pg_pathman
与禁用单个分区主表不同，全局禁止只需要调整参数即可，不需要修改pg_pathman的元数据，同时它是可逆 *** 作。
pg_pathman.enable
$ vi $PGDATA/postgresql.conf
pg_pathman.enable = off
$ pg_ctl reload
总 结：
如果在建初始分区时，需要设置分区表的表空间，可以设置会话或事务的参数
setlocal default_tablespace='tablespacename'
disable_pathman_for函数没有可逆 *** 作，请慎用。
不建议关闭pg_pathman.enable
不建议开启自动扩展范围分区，一个错误的分区值可能导致创建很多分区。
推荐使用set_enable_parent禁用主表。
由于pg_pathman使用了customscan接口，所以只支持9.5以及以上版本。
传统哈希分区需要输入分区键值的约束条件，才能正确选择分区。pg_pathman只要输入键值即可。
真的很好！我们公司在用它！首先它是面向对象的关系数据库，表继承解决了我们产品分类库的问题，这个在主流数据库中很难见得。其次，9.1版开始windows下的Postgresql搭配Server效率提高了不少啊！且函数、触发器的plpgsql语言非常好，况且还远远不是唯一的选择。再次，它的PostGIS模块是海量开源GIS数据的默认引擎，支持许多有用的计算，以及空间索引，经过我亲自测试，100GB的 Planet.OSM全球数据跑的很稳定。
最后，他的安装、卸载要比Oracle等简单的多，windows xp home上都跑的飞快（需要对表空间的权限用额外的工具修改）。
安装注意：
如果是Linux没有什么要注意的，直接 apt 或者yum即可。
中文windows下， 安装时的Locale 要选择"C"，而不是默认（Zh-cn)，否则可能安装失败。
创建表空间时，要手工在资源管理器里创建文件夹，而后把资源管理器的“工具”--》文件夹选项里的“查看”-->使用简单文件夹共享 勾掉（不选），而后右键单击新建的文件夹，打开属性，在"安全"页面添加用户postgres为全权限访问，否则表空间创建失败。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）