PostgreSQL 源码解读（217）- A Faster, Lightweight Trigger Function in C

本节介绍了如何使用C语言实现性能更高的轻量级触发器.
主要内容翻译自 A Faster, Lightweight Trigger Function in C for PostgreSQL

一、实现步骤

已在CentOs7.x上使用源码安装了PG,当前用户为PG实例的owner(pg12),已配置好环境变量,可以运行pg_config命令

[pg12@localhost demo_plus]$ whoamipg12[pg12@localhost ~]$ cat .bashrc# .bashrc# Source global definitionsif [ -f /etc/bashrc ]; then    . /etc/bashrcfi# Uncomment the following line if you don't like systemctl's auto-paging feature:# export SYSTEMD_PAGER=# User specific aliases and functionsexport PATH=/appdb/xdb/pg12beta1/bin:$PATHexport 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 #include #include "postgres.h"#include "utils/rel.h"#include "executor/spi.h"#include "commands/trigger.h"#include "utils/fmgrprotos.h"#ifdef PG_MODULE_MAGICPG_MODULE_MAGIC;#endifextern Datum demo_trig(PG_FUNCTION_ARGS);PG_FUNCTION_INFO_V1(demo_trig);Datumdemo_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 = trgrEXTENSION = trgrDATA = trgr--0.0.1.sql#PG_CONFIG = pg_config#PGXS := $(shell $(PG_CONFIG) --pgxs)#include $(PGXS)#prefix=/appdb/xdb/pg12beta1/ifdef USE_PGXSPG_CONFIG = pg_configPGXS := $(shell $(PG_CONFIG) --pgxs)include $(PGXS)elsesubdir = contrib/demo_trgrtop_builddir = ../..include $(top_builddir)/src/Makefile.globalinclude $(top_srcdir)/contrib/contrib-global.mkendif

control文件

[pg12@localhost demo_trgr]$ cat trgr.control comment = 'Simple number add function'default_version = '0.0.1'relocatable = truemodule_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]$ makemake -C ../../src/backend generated-headersmake[1]: Entering directory `/home/pg12/source/postgresql-12beta3/src/backend'make -C catalog distprep generated-header-symlinksmake[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-symlinksmake[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.Pogcc -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 installmake -C ../../src/backend generated-headersmake[1]: Entering directory `/home/pg12/source/postgresql-12beta3/src/backend'make -C catalog distprep generated-header-symlinksmake[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-symlinksmake[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 timestamppg12@testdb(# );CREATE TABLETime: 93.441 ms[local]:5432 pg12@testdb=# create extension trgr;CREATE EXTENSIONTime: 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 FUNCTIONTime: 2.937 ms[local]:5432 pg12@testdb=# CREATE TRIGGER trigger_demotrgrpg12@testdb-#  BEFORE INSERT OR UPDATE ON public.t_demo_trig pg12@testdb-# FOR EACH ROW EXECUTE PROCEDURE public.demo_trig();CREATE TRIGGERTime: 31.037 ms

性能对比,C实现 VS plpgsql实现

[local]:5432 pg12@testdb=# drop trigger trigger_demotrgr on t_demo_trig;DROP TRIGGERTime: 58.935 ms[local]:5432 pg12@testdb=# insert into t_demo_trig(id) select x from generate_series(1,1000000) as x;INSERT 0 1000000Time: 5063.936 ms (00:05.064)[local]:5432 pg12@testdb=# drop trigger trigger_demotrgr on t_demo_trig;DROP TRIGGERTime: 58.935 ms[local]:5432 pg12@testdb=# CREATE OR REPLACE FUNCTION demo_trig_plpgsql()pg12@testdb-#   RETURNS TRIGGER AS $$pg12@testdb$#   BEGINpg12@testdb$#      if  (TG_OP = 'UPDATE') thenpg12@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 FUNCTIONTime: 60.053 ms[local]:5432 pg12@testdb=# [local]:5432 pg12@testdb=# CREATE TRIGGER trigger_demotrgrpg12@testdb-#  BEFORE INSERT OR UPDATE ON public.t_demo_trig pg12@testdb-# FOR EACH ROW EXECUTE PROCEDURE public.demo_trig_plpgsql();CREATE TRIGGERTime: 0.938 ms[local]:5432 pg12@testdb=# insert into t_demo_trig(id) select x from generate_series(1,1000000) as x;INSERT 0 1000000Time: 8716.367 ms (00:08.716)

5063ms(C函数) vs 8716ms(plpgsql)

二、参考资料

A Faster, Lightweight Trigger Function in C for PostgreSQL