循序渐进丨openGauss / MogDB 数据库编写自定义extension

MogDB 2024-11-26

270

本文介绍如何在 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文件帮助编译。

在本文中，我定义了一个名为ysla的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)

关于作者

阎书利，云和恩墨数据库技术顾问，PostgreSQL ACE，《快速掌握 PostgreSQL 版本新特性》一书副主编，中国PG分会认证讲师，PGfans 2021年度MVP，Gauss松鼠会2021年度优秀会员，拥有PGCM、OCP(MySQL)等十多项数据库认证，目前主要从事于 PostgreSQL、openGauss、MogDB 的运维以及数据库迁移改造工作。

END

MogDB 是云和恩墨基于 openGauss 开源内核进行增强提升，推出的一款安稳易用的企业级关系型数据库。其具备金融级高可用和全密态计算的极致安全、面向多核处理器的极致性能、AI自诊断调优的极致智能能力，能够满足从核心交易到复杂计算的企业级业务需求。

访问官网了解更多：www.mogdb.io

产品兼容适配申请：partner@enmotech.com

加微信进入交流群：Roger_database

文章转载自MogDB，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。