本节介绍了如何使用C语言实现性能更高的轻量级触发器. 主要内容翻译自[A Faster, Lightweight Trigger Function in C for PostgreSQL](https://www.percona.com/blog/2019/08/15/faster-lightweight-trigger-function-in-c-for-postgresql/) ### 一、实现步骤 已在CentOs7.x上使用源码安装了PG,当前用户为PG实例的owner(pg12),已配置好环境变量,可以运行pg_config命令 ``` [pg12@localhost demo_plus]$ whoami pg12 [pg12@localhost ~]$ cat .bashrc # .bashrc # Source global definitions if [ -f /etc/bashrc ]; then . /etc/bashrc fi # Uncomment the following line if you don't like systemctl's auto-paging feature: # export SYSTEMD_PAGER= # User specific aliases and functions export PATH=/appdb/xdb/pg12beta1/bin:$PATH export PGDATA=/data/pgsql/pg12db1 [pg12@localhost ~]$ pg_config --pgxs /appdb/xdb/pg12beta1/lib/postgresql/pgxs/src/makefiles/pgxs.mk ``` 本例的场景是希望在update/insert的时候记录更新时间/插入时间,通常我们会使用plpgsql实现此需求,但这次改用C语言实现. 数据表脚本如下: ``` CREATE TABLE t_demo_trig( id int, insert_ts timestamp, update_ts timestamp ); ``` 下面是C实现源文件 ``` #include <stdio.h> #include <time.h> #include "postgres.h" #include "utils/rel.h" #include "executor/spi.h" #include "commands/trigger.h" #include "utils/fmgrprotos.h" #ifdef PG_MODULE_MAGIC PG_MODULE_MAGIC; #endif extern Datum demo_trig(PG_FUNCTION_ARGS); PG_FUNCTION_INFO_V1(demo_trig); Datum demo_trig(PG_FUNCTION_ARGS) { //从函数调用上下文中获取触发器(TriggerData结构体) TriggerData *trigdata = (TriggerData *) fcinfo->context; //TupleDesc tupdesc; HeapTuple tuple;//更新前的元组 HeapTuple rettuple;//更新后的元组 int attnum = 0;//属性编号 Datum datumVal;//数据值(Datum其实是一个普通指针) //Get the structure of the tuple in the table. //tupdesc = trigdata->tg_relation->rd_att; //Make sure that the function is called from a trigger if (!CALLED_AS_TRIGGER(fcinfo)) elog(ERROR, "are you sure you are calling from trigger manager?"); //If the trigger is part of an UPDATE event if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))//UPDATE操作 { //attnum = SPI_fnumber(tupdesc,"update_ts"); attnum = 3; tuple = trigdata->tg_newtuple; } //If the trigger is part of INSERT event else//插入操作 { //attnum = SPI_fnumber(tupdesc,"insert_ts"); attnum = 2; tuple = trigdata->tg_trigtuple; } //Get the current timestamp using "now" //调用函数now(),获取当前时间 datumVal = DirectFunctionCall3(timestamp_in, CStringGetDatum("now"), ObjectIdGetDatum(InvalidOid), Int32GetDatum(-1)); //Connect to Server and modify the tuple //使用SPI连接到数据库,并执行更新 SPI_connect(); rettuple = SPI_modifytuple(trigdata->tg_relation, tuple, 1, &attnum, &datumVal, NULL); if (rettuple == NULL) { if (SPI_result == SPI_ERROR_ARGUMENT || SPI_result == SPI_ERROR_NOATTRIBUTE) elog(ERROR, "SPI_result failed! SPI_ERROR_ARGUMENT or SPI_ERROR_NOATTRIBUTE"); elog(ERROR, "SPI_modifytuple failed!"); } //收尾工作 SPI_finish(); /* don't forget say Bye to SPI mgr */ //返回更新后的元组 return PointerGetDatum(rettuple); } ``` Makefile文件 ``` [pg12@localhost demo_trgr]$ cat Makefile MODULES = trgr EXTENSION = trgr DATA = trgr--0.0.1.sql #PG_CONFIG = pg_config #PGXS := $(shell $(PG_CONFIG) --pgxs) #include $(PGXS) #prefix=/appdb/xdb/pg12beta1/ ifdef USE_PGXS PG_CONFIG = pg_config PGXS := $(shell $(PG_CONFIG) --pgxs) include $(PGXS) else subdir = contrib/demo_trgr top_builddir = ../.. include $(top_builddir)/src/Makefile.global include $(top_srcdir)/contrib/contrib-global.mk endif ``` control文件 ``` [pg12@localhost demo_trgr]$ cat trgr.control comment = 'Simple number add function' default_version = '0.0.1' relocatable = true module_pathname = '/appdb/pg12/pg12beta3/lib/postgresql/trgr.so' ``` sql安装文件 ``` [pg12@localhost demo_trgr]$ cat trgr--0.0.1.sql CREATE OR REPLACE FUNCTION demop_trig() RETURNS trigger AS 'MODULE_PATHNAME','demo_trig' LANGUAGE C STRICT; ``` 编译&安装 ``` [pg12@localhost demo_trgr]$ make make -C ../../src/backend generated-headers make[1]: Entering directory `/home/pg12/source/postgresql-12beta3/src/backend' make -C catalog distprep generated-header-symlinks make[2]: Entering directory `/home/pg12/source/postgresql-12beta3/src/backend/catalog' make[2]: Nothing to be done for `distprep'. make[2]: Nothing to be done for `generated-header-symlinks'. make[2]: Leaving directory `/home/pg12/source/postgresql-12beta3/src/backend/catalog' make -C utils distprep generated-header-symlinks make[2]: Entering directory `/home/pg12/source/postgresql-12beta3/src/backend/utils' make[2]: Nothing to be done for `distprep'. make[2]: Nothing to be done for `generated-header-symlinks'. make[2]: Leaving directory `/home/pg12/source/postgresql-12beta3/src/backend/utils' make[1]: Leaving directory `/home/pg12/source/postgresql-12beta3/src/backend' gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -fPIC -I. -I. -I../../src/include -D_GNU_SOURCE -I/usr/include/libxml2 -c -o trgr.o trgr.c -MMD -MP -MF .deps/trgr.Po gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -fPIC trgr.o -L../../src/port -L../../src/common -Wl,--as-needed -Wl,-rpath,'/appdb/pg12/pg12beta3/lib',--enable-new-dtags -shared -o trgr.so [pg12@localhost demo_trgr]$ make install make -C ../../src/backend generated-headers make[1]: Entering directory `/home/pg12/source/postgresql-12beta3/src/backend' make -C catalog distprep generated-header-symlinks make[2]: Entering directory `/home/pg12/source/postgresql-12beta3/src/backend/catalog' make[2]: Nothing to be done for `distprep'. make[2]: Nothing to be done for `generated-header-symlinks'. make[2]: Leaving directory `/home/pg12/source/postgresql-12beta3/src/backend/catalog' make -C utils distprep generated-header-symlinks make[2]: Entering directory `/home/pg12/source/postgresql-12beta3/src/backend/utils' make[2]: Nothing to be done for `distprep'. make[2]: Nothing to be done for `generated-header-symlinks'. make[2]: Leaving directory `/home/pg12/source/postgresql-12beta3/src/backend/utils' make[1]: Leaving directory `/home/pg12/source/postgresql-12beta3/src/backend' /bin/mkdir -p '/appdb/pg12/pg12beta3/share/postgresql/extension' /bin/mkdir -p '/appdb/pg12/pg12beta3/share/postgresql/extension' /bin/mkdir -p '/appdb/pg12/pg12beta3/lib/postgresql' /bin/install -c -m 644 ./trgr.control '/appdb/pg12/pg12beta3/share/postgresql/extension/' /bin/install -c -m 644 ./trgr--0.0.1.sql '/appdb/pg12/pg12beta3/share/postgresql/extension/' /bin/install -c -m 755 trgr.so '/appdb/pg12/pg12beta3/lib/postgresql/' [pg12@localhost demo_trgr]$ ``` 创建触发器 ``` [local]:5432 pg12@testdb=# CREATE TABLE t_demo_trig( pg12@testdb(# id int, pg12@testdb(# insert_ts timestamp, pg12@testdb(# update_ts timestamp pg12@testdb(# ); CREATE TABLE Time: 93.441 ms [local]:5432 pg12@testdb=# create extension trgr; CREATE EXTENSION Time: 1.403 ms [local]:5432 pg12@testdb=# CREATE FUNCTION demo_trig() RETURNS trigger pg12@testdb-# AS '/appdb/pg12/pg12beta3/lib/postgresql/trgr.so' pg12@testdb-# LANGUAGE C; CREATE FUNCTION Time: 2.937 ms [local]:5432 pg12@testdb=# CREATE TRIGGER trigger_demotrgr pg12@testdb-# BEFORE INSERT OR UPDATE ON public.t_demo_trig pg12@testdb-# FOR EACH ROW EXECUTE PROCEDURE public.demo_trig(); CREATE TRIGGER Time: 31.037 ms ``` 性能对比,C实现 VS plpgsql实现 ``` [local]:5432 pg12@testdb=# drop trigger trigger_demotrgr on t_demo_trig; DROP TRIGGER Time: 58.935 ms [local]:5432 pg12@testdb=# insert into t_demo_trig(id) select x from generate_series(1,1000000) as x; INSERT 0 1000000 Time: 5063.936 ms (00:05.064) [local]:5432 pg12@testdb=# drop trigger trigger_demotrgr on t_demo_trig; DROP TRIGGER Time: 58.935 ms [local]:5432 pg12@testdb=# CREATE OR REPLACE FUNCTION demo_trig_plpgsql() pg12@testdb-# RETURNS TRIGGER AS $$ pg12@testdb$# BEGIN pg12@testdb$# if (TG_OP = 'UPDATE') then pg12@testdb$# NEW.update_ts = now(); pg12@testdb$# else pg12@testdb$# NEW.insert_ts = now(); pg12@testdb$# end if; pg12@testdb$# RETURN NEW; pg12@testdb$# END; pg12@testdb$# $$ language 'plpgsql'; CREATE FUNCTION Time: 60.053 ms [local]:5432 pg12@testdb=# [local]:5432 pg12@testdb=# CREATE TRIGGER trigger_demotrgr pg12@testdb-# BEFORE INSERT OR UPDATE ON public.t_demo_trig pg12@testdb-# FOR EACH ROW EXECUTE PROCEDURE public.demo_trig_plpgsql(); CREATE TRIGGER Time: 0.938 ms [local]:5432 pg12@testdb=# insert into t_demo_trig(id) select x from generate_series(1,1000000) as x; INSERT 0 1000000 Time: 8716.367 ms (00:08.716) ``` 5063ms(C函数) vs 8716ms(plpgsql) ### 二、参考资料 [A Faster, Lightweight Trigger Function in C for PostgreSQL](https://www.percona.com/blog/2019/08/15/faster-lightweight-trigger-function-in-c-for-postgresql/)