【openGauss/MogDB编写自定义extension】

之前写过一篇文章，PostgreSQL编写自定义extension。PostgreSQL编写自定义extension - 墨天轮

openGauss是基于PostgreSQL发展而来，虽然有很多扩展不能直接兼容，但是也可以经过适配来进行使用,而且除此之外，openGauss把一些比较好用的插件的功能直接内置到了数据库里，提供给我们很多的便利。

下面我将介绍如何在openGauss/MogDB上编写自定义extension（实现自定义C函数），并介绍几个容易遇到的坑和解决方法，供参考。

和PostgreSQL类似，编写自定义extension比较关键的四个文件如下

omm2@ubuntu-linux-22-04-desktop:~/test_gauss_extension$ ls
Makefile  ysla--1.0.sql  ysla.c  ysla.control

其中

一个格式为extension_name.control的控制文件，它告诉openGauss关于您的扩展程序的一些基础知识
一个扩展程序的SQL脚本文件，格式为extension--version.sql
一个c实现的extension_name.c文件，可以根据功能需求编写
一个Makefile文件帮助编译

在本文中，我定义了一个名为ysal的extension，比较简单，扩展包含一个表和一个函数，仅仅用一个函数验证extension的功能是否正常。（简单用C函数实现一个减法函数，输入两个值，然后用两个值做减法）

一、文件准备

在使用openGauss的初始系统用户下建立一个test_gauss_extension目录，开始编写如下四个文件

omm2@ubuntu-linux-22-04-desktop:~/test_gauss_extension$ pwd
/home/omm2/test_gauss_extension

omm2@ubuntu-linux-22-04-desktop:~/test_gauss_extension$ ls
Makefile  ysla--1.0.sql  ysla.c  ysla.control

1.编写ysla–1.0.sql文件

/* ysla--1.0.sql */

--complain if script is sourced in psql rather than via ALTER EXTENSION
\echo Use "CREATE EXTENSION ysla" to load this file. \quit
CREATE TABLE tab_ysla(oid integer,namespace_oid integer,name text,time timestamp);        /* 创建一个表 */

CREATE FUNCTION sub_ab(int,int)           /* 创建一个函数 */
RETURNS int
AS '$libdir/ysla.so'
LANGUAGE C STRICT;

2.编写ysla.control文件

# ysl extension

comment = ' xmaster '
default_version = '1.0'
#module_pathname = 'ysla.so'
relocatable = true

3.编写Makefile文件

# contrib/ysl/Makefile

#MODULES = ysla
EXTENSION =ysla
DATA = ysla--1.0.sql
MODULES = ysla

exclude_option = -fPIE
override CPPFLAGS := $(filter-out $(exclude_option),$(CPPFLAGS))

PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

4.编写ysla.c文件

#include "postgres.h"
#include "fmgr.h"

PG_MODULE_MAGIC;
extern "C" Datum sub_ab(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(sub_ab);

Datum
sub_ab(PG_FUNCTION_ARGS)
{
          int32 arg_a = PG_GETARG_INT32(0);
          int32 arg_b = PG_GETARG_INT32(1);

          PG_RETURN_INT32(arg_a - arg_b);
}

二、编译及插件使用

1.编译插件

omm2@ubuntu-linux-22-04-desktop:~/test_gauss_extension$ ls
Makefile  ysla--1.0.sql  ysla.c  ysla.control

1.1 执行make编译

omm2@ubuntu-linux-22-04-desktop:~/test_gauss_extension$ make
g++ -std=c++11 -D_GLIBCXX_USE_CXX11_ABI=0 -fsigned-char -DSTREAMPLAN -DPGXC -march=armv8-a+crc+lse -O2 -g3  -D__USE_NUMA -D__ARM_LSE -Wall -Wpointer-arith -Wno-write-strings -fnon-call-exceptions -fno-common -freg-struct-return -pipe -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -DENABLE_GSTRACE -fno-aggressive-loop-optimizations -Wno-attributes -fno-omit-frame-pointer -fno-expensive-optimizations -Wno-unused-but-set-variable -fPIE -I. -I. -I/opt/openGauss5.0/soft/include/postgresql/server -I/opt/openGauss5.0/soft/include/postgresql/internal -I/opt/openGauss5.0/soft/lib/postgresql/pgxs/src/makefiles/../../src/lib/gstrace  -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/unixodbc/include -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/libobs/comm/include -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/libcgroup/comm/include -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/openssl/comm/include -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/boost/comm/include -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/llvm/comm/include -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/kerberos/comm/include -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/cjson/comm/include -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/numactl/comm/include -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/zlib1.2.11/comm/include -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/lz4/comm/include -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/zstd/include -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/libcurl/comm/include -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/component/dcf/include -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/masstree/comm/include -DOPENEULER_MAJOR  -c -o ysla.o ysla.c
g++ -std=c++11 -D_GLIBCXX_USE_CXX11_ABI=0 -fsigned-char -DSTREAMPLAN -DPGXC -march=armv8-a+crc+lse -O2 -g3  -D__USE_NUMA -D__ARM_LSE -Wall -Wpointer-arith -Wno-write-strings -fnon-call-exceptions -fno-common -freg-struct-return -pipe -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -DENABLE_GSTRACE -fno-aggressive-loop-optimizations -Wno-attributes -fno-omit-frame-pointer -fno-expensive-optimizations -Wno-unused-but-set-variable -fstack-protector -Wl,-z,relro,-z,now -Wl,-z,noexecstack -std=c++14 -pthread  -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -fpic -L/opt/openGauss5.0/soft/lib -pthread -L/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/zlib1.2.11/comm/lib -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/zlib1.2.11/comm/include -L/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/zstd/lib -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/zstd/include -L/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/platform/Huawei_Secure_C/comm/lib -L/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/openssl/comm/lib -L/usr1/build/workspace/openGauss-third_party_binarylibs/buildtools/libstd/gcc7.3.0/comm/lib -L/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/libcgroup/comm/lib -L -L/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/unixodbc/lib -L/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/libobs/comm/lib -L/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/kerberos/comm/lib -L/opt/openGauss5.0/soft/lib/postgresql/pgxs/src/makefiles/../../src/gstrace//common -L/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/numactl/comm/lib -L/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/libcurl/comm/lib -L/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/masstree/comm/lib -L/usr1/gauss_jenkins/jenkins/workspace/openGauss/thirdparty/dependency/libxml2/install_comm/lib -L/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/libxml2/comm/lib  -L/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/jemalloc/release/lib  -shared -o ysla.so ysla.o
rm ysla.o
omm2@ubuntu-linux-22-04-desktop:~/test_gauss_extension$ ls
Makefile  ysla--1.0.sql  ysla.c  ysla.control  ysla.so

1.2 执行make insall安装插件

omm2@ubuntu-linux-22-04-desktop:~/test_gauss_extension$ ls
Makefile  ysla--1.0.sql  ysla.c  ysla.control  ysla.so
omm2@ubuntu-linux-22-04-desktop:~/test_gauss_extension$ make install
/usr/bin/mkdir -p '/opt/openGauss5.0/soft/share/postgresql/extension'
/usr/bin/mkdir -p '/opt/openGauss5.0/soft/share/postgresql/extension'
/usr/bin/mkdir -p '/opt/openGauss5.0/soft/lib/postgresql'
/bin/sh /opt/openGauss5.0/soft/lib/postgresql/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./ysla.control '/opt/openGauss5.0/soft/share/postgresql/extension/'
/bin/sh /opt/openGauss5.0/soft/lib/postgresql/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./ysla--1.0.sql  '/opt/openGauss5.0/soft/share/postgresql/extension/'
/bin/sh /opt/openGauss5.0/soft/lib/postgresql/pgxs/src/makefiles/../../config/install-sh -c -m 755  ysla.so '/opt/openGauss5.0/soft/lib/postgresql/'

make install后会自动根据环境变量拷贝文件到对应的目录

omm2@ubuntu-linux-22-04-desktop:~/test_gauss_extension$ ll /opt/openGauss5.0/soft/share/postgresql/extension/ysla*
-rw-r--r-- 1 omm2 omm2 435 Jan 23 17:19 /opt/openGauss5.0/soft/share/postgresql/extension/ysla--1.0.sql
-rw-r--r-- 1 omm2 omm2 155 Jan 23 17:19 /opt/openGauss5.0/soft/share/postgresql/extension/ysla.control
omm2@ubuntu-linux-22-04-desktop:~/test_gauss_extension$ ll /opt/openGauss5.0/soft/lib/postgresql/ysla*
-rwxr-xr-x 1 omm2 omm2 244152 Jan 23 17:19 /opt/openGauss5.0/soft/lib/postgresql/ysla.so*

2.使用插件

创建ysla的extension然后验证功能，功能正常。

omm2@ubuntu-linux-22-04-desktop:~/test_gauss_extension$ gsql -r
gsql ((openGauss 5.0.0 build a07d57c3) compiled at 2023-03-29 03:36:31 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

openGauss=# create extension ysla ;
CREATE EXTENSION
openGauss=# \dx
                               List of installed extensions
      Name       | Version |   Schema   |                   Description
-----------------+---------+------------+--------------------------------------------------
 dist_fdw        | 1.0     | pg_catalog | foreign-data wrapper for distfs access
 file_fdw        | 1.0     | pg_catalog | foreign-data wrapper for flat file access
 hstore          | 1.1     | pg_catalog | data type for storing sets of (key, value) pairs
 log_fdw         | 1.0     | pg_catalog | Foreign Data Wrapper for accessing logging data
 mot_fdw         | 1.0     | pg_catalog | foreign-data wrapper for MOT access
 plpgsql         | 1.0     | pg_catalog | PL/pgSQL procedural language
 security_plugin | 1.0     | pg_catalog | provides security functionality
 ysla            | 1.0     | public     |  xmaster
(8 rows)

openGauss=# \df sub_ab
                                           List of functions
 Schema |  Name  | Result data type | Argument data types |  Type  | fencedmode | propackage | prokind
--------+--------+------------------+---------------------+--------+------------+------------+---------
 public | sub_ab | integer          | integer, integer    | normal | f          | f          | f
(1 row)

openGauss=# select sub_ab(667,1);
 sub_ab
--------
    666
(1 row)

三、相关问题

1.$libdir/proc_srclib/ 前缀问题

对于编译的时候，enable_default_cfunc_libpath这个参数开启的话，会自动在前面加一个 libdir/proc_srclib/ 前缀，libdir 代表是pg_config --pkglibdir的值 。所以要让so文件拷贝使用绝对路径，就要把这个参数关掉，否则要使用相对路径。

omm2@ubuntu-linux-22-04-desktop:~/test_gauss_extension$ pg_config --pkglibdir
/opt/openGauss5.0/soft/lib/postgresql

2.Makefile增加参数

Makefile文件里增加如下两条，修复 gcc 编译时候的参数，去掉 -fPIE。

exclude_option = -fPIE
override CPPFLAGS := $(filter-out $(exclude_option),$(CPPFLAGS))

否则可能会有例如这种报错

ERROR:  could not find function "fun_ysl" in file "/opt/mogdb/app/lib/postgresql/ysl.so"

3.extern “C”

C 语言中并没有重载和类这些特性，编译出的符号与 C+ +不同，例如 print(int i)，不会被编译为_print_int，而是直接编译为_print 等。因此如果直接在 C+ +中调用 C 的函数会失败，加上 extern “C”，指示编译器这部分代码按 C 语言来进行编译，而不是 C++。

例如：

extern "C" Datum sub_ab(PG_FUNCTION_ARGS);

4.函数的定义需要注意，有些关键字PostgreSQL支持，但是openGauss不支持

例如openGauss的函数不支持PARALLEL语法等，有些opengauss不支持的不可以使用，这个需要注意。

5.关于不能drop extension问题

验证过插件后发现不能drop掉插件，有如下的报错

openGauss=# drop extension ysla;
ERROR:  EXTENSION is not yet supported.

去翻了一下openGauss的源码，发现如果support_extended_features不开启的话，这种自定义的插件是不能删除的。

开启这个参数后，再删除extension，发现可以正常移除了。

openGauss=# show support_extended_features;
 support_extended_features
---------------------------
 on
(1 row)

openGauss=# create extension ysla ;
CREATE EXTENSION
openGauss=# \dx ysla
     List of installed extensions
 Name | Version | Schema | Description
------+---------+--------+-------------
 ysla | 1.0     | public |  xmaster
(1 row)

openGauss=# drop extension ysla;
DROP EXTENSION
openGauss=# \dx ysla
     List of installed extensions
 Name | Version | Schema | Description
------+---------+--------+-------------
(0 rows)