本節簡單介紹瞭如何實現擴充套件函式.
主要內容翻譯自[PostgreSQL: Simple C extension Development for a Novice User (and Performance Advantages)](https://www.percona.com/blog/2019/07/31/postgresql-simple-c-extension-development-for-a-novice-user/)

### 一、實現步驟
假設我們已在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
```

建立原始碼目錄
```
[pg12@localhost source]$ mkdir -p extension/demo_plus
[pg12@localhost source]$ cd extension/demo_plus/
```
使用C作為extension的實現語言,參照PG extension Makefile檔案格式編寫Makefile檔案
```
[pg12@localhost demo_plus]$ cat Makefile 
MODULES = demo_plus
EXTENSION = demo_plus
DATA = demo_plus--0.0.1.sql
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
```
MODULES:擴充套件模組,待Build的Module
EXTENSION:副檔名稱,可與Modules不同
DATA:安裝指令碼
0.0.1:版本

下面建立extension control檔案
```
[pg12@localhost demo_plus]$ cat demo_plus.control 
comment = 'Simple number add function'
default_version = '0.0.1'
relocatable = true
module_pathname = '$libdir/demo_plus'
```
comment:註釋
default_version:預設版本
relocatable:是否重新載入
module_pathname:所在路徑

接下來是實現檔案demo_plus.c
```
[pg12@localhost demo_plus]$ cat demo_plus.c
#include "postgres.h"
#include "fmgr.h"

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(demo_plus);

Datum
demo_plus(PG_FUNCTION_ARGS)
{
int32 arg1 = PG_GETARG_INT32(0);
int32 arg2 = PG_GETARG_INT32(1);

PG_RETURN_INT32(arg1 + arg2);
}

```
邏輯很簡單,輸入兩個數,返回兩個數相加的結果.make編譯
```
[pg12@localhost demo_plus]$ ls -l
total 12
-rw-rw-r--. 1 pg12 pg12 220 Aug  8 11:21 demo_plus.c
-rw-rw-r--. 1 pg12 pg12 123 Aug  8 11:18 demo_plus.control
-rw-rw-r--. 1 pg12 pg12 146 Aug  8 11:18 Makefile

[pg12@localhost demo_plus]$ make
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/appdb/xdb/pg12beta1/include/postgresql/server -I/appdb/xdb/pg12beta1/include/postgresql/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o demo_plus.o demo_plus.c -MMD -MP -MF .deps/demo_plus.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 demo_plus.o -L/appdb/xdb/pg12beta1/lib   -Wl,--as-needed -Wl,-rpath,'/appdb/xdb/pg12beta1/lib',--enable-new-dtags  -shared -o demo_plus.so

```

最後一步,建立sql檔案用於生成函式
```
[pg12@localhost demo_plus]$ cat demo_plus-0.0.1.sql 
CREATE OR REPLACE FUNCTION
demofunc1(int,int) RETURNS int AS 'MODULE_PATHNAME','demo_plus'
LANGUAGE C STRICT;

[pg12@localhost demo_plus]$ 
```

執行make install安裝
```
[pg12@localhost demo_plus]$ sudo make install
make -C ../../src/backend generated-headers
make[1]: Entering directory `/data/source/postgresql-12beta1/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/data/source/postgresql-12beta1/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 `/data/source/postgresql-12beta1/src/backend/catalog'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/data/source/postgresql-12beta1/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 `/data/source/postgresql-12beta1/src/backend/utils'
make[1]: Leaving directory `/data/source/postgresql-12beta1/src/backend'
/usr/bin/mkdir -p '/appdb/xdb/pg12beta1/share/postgresql/extension'
/usr/bin/mkdir -p '/appdb/xdb/pg12beta1/share/postgresql/extension'
/usr/bin/mkdir -p '/appdb/xdb/pg12beta1/lib/postgresql'
/usr/bin/install -c -m 644 ./demo_plus.control '/appdb/xdb/pg12beta1/share/postgresql/extension/'
/usr/bin/install -c -m 644 ./demo_plus--0.0.1.sql  '/appdb/xdb/pg12beta1/share/postgresql/extension/'
/usr/bin/install -c -m 755  demo_plus.so '/appdb/xdb/pg12beta1/lib/postgresql/'
```

建立extension,執行函式
```
testdb=# create extension demo_plus;
CREATE EXTENSION
```

*PL/pgSQL vs C Extension*
C Extension的效能接近於原生內建函式(+),而PL/pgSQL的執行時間則是原生的2倍+
```
CREATE FUNCTION plpgsql_demo_plus(a integer, b integer)
 RETURNS integer
as $$ 
BEGIN
  return a+b;
END;
$$ LANGUAGE plpgsql;

[xdb@localhost ~]$ time psql -c "select plpgsql_demo_plus(floor(random() * (100-1+1) + 1)::int,x) from generate_series(1,1000000) as x" -p 5433 -d testdb > out.txt

real	0m3.753s
user	0m0.924s
sys	0m0.658s
[xdb@localhost ~]$ time psql -c "select demo_plus(floor(random() * (100-1+1) + 1)::int,x) from generate_series(1,1000000) as x" -p 5433 -d testdb > out.txt

real	0m1.685s
user	0m1.005s
sys	0m0.093s
[xdb@localhost ~]$ time psql -c "select floor(random() * (100-1+1) + 1)::int+x from generate_series(1,1000000) as x" -p 5433 -d testdb > out.txt

real	0m1.666s
user	0m0.992s
sys	0m0.067s
```

### 二、配置檔案說明
**Makefile**
MODULES
    list of shared-library objects to be built from source files with same stem (do not include library suffixes in this list)
MODULE_big
    a shared library to build from multiple source files (list object files in OBJS)
PROGRAM
    an executable program to build (list object files in OBJS)
EXTENSION
    extension name(s); for each name you must provide an extension.control file, which will be installed into prefix/share/extension
MODULEDIR
    subdirectory of prefix/share into which DATA and DOCS files should be installed (if not set, default is extension if EXTENSION is set, or contrib if not)
DATA
    random files to install into prefix/share/$MODULEDIR
DATA_built
    random files to install into prefix/share/$MODULEDIR, which need to be built first
DATA_TSEARCH
    random files to install under prefix/share/tsearch_data
DOCS
    random files to install under prefix/doc/$MODULEDIR
HEADERS
HEADERS_built
    Files to (optionally build and) install under prefix/include/server/$MODULEDIR/$MODULE_big.
    Unlike DATA_built, files in HEADERS_built are not removed by the clean target; if you want them removed, also add them to EXTRA_CLEAN or add your own rules to do it.
HEADERS_$MODULE
HEADERS_built_$MODULE
    Files to install (after building if specified) under prefix/include/server/$MODULEDIR/$MODULE, where $MODULE must be a module name used in MODULES or MODULE_big.
    Unlike DATA_built, files in HEADERS_built_$MODULE are not removed by the clean target; if you want them removed, also add them to EXTRA_CLEAN or add your own rules to do it.
    It is legal to use both variables for the same module, or any combination, unless you have two module names in the MODULES list that differ only by the presence of a prefix built_, which would cause ambiguity. In that (hopefully unlikely) case, you should use only the HEADERS_built_$MODULE variables.
SCRIPTS
    script files (not binaries) to install into prefix/bin
SCRIPTS_built
    script files (not binaries) to install into prefix/bin, which need to be built first
REGRESS
    list of regression test cases (without suffix), see below
REGRESS_OPTS
    additional switches to pass to pg_regress
ISOLATION
    list of isolation test cases, see below for more details
ISOLATION_OPTS
    additional switches to pass to pg_isolation_regress
TAP_TESTS
    switch defining if TAP tests need to be run, see below
NO_INSTALLCHECK
    don't define an installcheck target, useful e.g. if tests require special configuration, or don't use pg_regress
EXTRA_CLEAN
    extra files to remove in make clean
PG_CPPFLAGS
    will be prepended to CPPFLAGS
PG_CFLAGS
    will be appended to CFLAGS
PG_CXXFLAGS
    will be appended to CXXFLAGS
PG_LDFLAGS
    will be prepended to LDFLAGS
PG_LIBS
    will be added to PROGRAM link line
SHLIB_LINK
    will be added to MODULE_big link line
PG_CONFIG
    path to pg_config program for the PostgreSQL installation to build against (typically just pg_config to use the first one in your PATH)

**control file**
A control file can set the following parameters:
directory (string)
    The directory containing the extension's SQL script file(s). Unless an absolute path is given, the name is relative to the installation's SHAREDIR directory. The default behavior is equivalent to specifying directory = 'extension'.
default_version (string)
    The default version of the extension (the one that will be installed if no version is specified in CREATE EXTENSION). Although this can be omitted, that will result in CREATE EXTENSION failing if no VERSION option appears, so you generally don't want to do that.
comment (string)
    A comment (any string) about the extension. The comment is applied when initially creating an extension, but not during extension updates (since that might override user-added comments). Alternatively, the extension's comment can be set by writing a COMMENT command in the script file.
encoding (string)
    The character set encoding used by the script file(s). This should be specified if the script files contain any non-ASCII characters. Otherwise the files will be assumed to be in the database encoding.
module_pathname (string)
    The value of this parameter will be substituted for each occurrence of MODULE_PATHNAME in the script file(s). If it is not set, no substitution is made. Typically, this is set to $libdir/shared_library_name and then MODULE_PATHNAME is used in CREATE FUNCTION commands for C-language functions, so that the script files do not need to hard-wire the name of the shared library.
requires (string)
    A list of names of extensions that this extension depends on, for example requires = 'foo, bar'. Those extensions must be installed before this one can be installed.
superuser (boolean)
    If this parameter is true (which is the default), only superusers can create the extension or update it to a new version. If it is set to false, just the privileges required to execute the commands in the installation or update script are required.
relocatable (boolean)
    An extension is relocatable if it is possible to move its contained objects into a different schema after initial creation of the extension. The default is false, i.e. the extension is not relocatable. See Section 37.17.3 for more information.
schema (string)
    This parameter can only be set for non-relocatable extensions. It forces the extension to be loaded into exactly the named schema and not any other. The schema parameter is consulted only when initially creating an extension, not during extension updates. See Section 37.17.3 for more information.

### 三、參考資料
[PostgreSQL: Simple C extension Development for a Novice User (and Performance Advantages)](https://www.percona.com/blog/2019/07/31/postgresql-simple-c-extension-development-for-a-novice-user/)
[Packaging Related Objects into an Extension](https://www.postgresql.org/docs/12/extend-extensions.html)
[Extension Building Infrastructure](https://www.postgresql.org/docs/12/extend-pgxs.html)

PostgreSQL 原始碼解讀（216）- 實現簡單的擴充套件函式

相關文章