Shulou(Shulou.com)06/01 Report--

This section describes how to use C language to implement lightweight triggers with higher performance.

The main content is translated from A Faster, Lightweight Trigger Function in C for PostgreSQL

I. implementation steps

PG has been installed on CentOs7.x using source code, and the current user is owner (pg12) of the PG instance. The environment variable has been configured and you can run the pg_config command.

[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

The scenario in this example is to record the update time / insertion time during update/insert. Usually we use plpgsql to implement this requirement, but this time we use C language instead.

The datasheet script is as follows:

CREATE TABLE t_demo_trig (id int, insert_ts timestamp, update_ts timestamp)

The following is the C implementation source file

# 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) {/ / get trigger (TriggerData structure) TriggerData * trigdata = (TriggerData *) fcinfo- > context; / / TupleDesc tupdesc; HeapTuple tuple;// tuple before update int attnum = 0 HeapTuple rettuple;// / attribute number Datum datumVal after update / / data value (Datum is actually a normal pointer) / / 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 operation {/ / attnum = SPI_fnumber (tupdesc, "update_ts"); attnum = 3 Tuple = trigdata- > tg_newtuple;} / / If the trigger is part of INSERT event else// insert operation {/ / attnum = SPI_fnumber (tupdesc, "insert_ts"); attnum = 2; tuple = trigdata- > tg_trigtuple } / / Get the current timestamp using "now" / / call the function now () to get the current time datumVal = DirectFunctionCall3 (timestamp_in, CStringGetDatum ("now"), ObjectIdGetDatum (InvalidOid), Int32GetDatum (- 1)); / / Connect to Server and modify the tuple / / Connect to the database using SPI and update 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! ");} / / wrap-up work SPI_finish (); / * don't forget say Bye to SPI mgr * / / return the updated tuple return PointerGetDatum (rettuple);}

Makefile file

[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 file

[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 installation files

[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

Compile & install

[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-Wendif-labels-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-O-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] $

Create trigger

[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

Performance comparison, C implementation of VS plpgsql implementation

[local]: 5432 pg12@testdb=# drop trigger trigger_demotrgr on tours demographics trig.DROP TRIGGERTime: 58.935 ms [local]: 5432 pg12@testdb=# insert into t_demo_trig (id) select x from generate_series (1Magne 1000000) as x insert 0 1000000Time: 5063.936 ms (00 ms 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 (1meme 1000000) as x insert 0 1000000Time: 8716.367 ms (00VO8.716)

5063ms (C function) vs 8716ms (plpgsql)

II. Reference materials

A Faster, Lightweight Trigger Function in C for PostgreSQL

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.