本節介紹瞭如何使用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/)</time.h></stdio.h>

PostgreSQL 原始碼解讀（217）- A Faster, Lightweight Trigger Function in C

相關文章