Postgresql扩展Sql-添加插件

我们都知道 PostgreSQL 提供了丰富数据库内核编程的接口,允许开发者以插件的形式把功能融入数据库内核。PostgreSQL 提供了一个插件管理模块,用于管理用户创建的插件。 {插件编写可以参照共享库编写;}

PostgreSQL 支持使用PL/pgSQL语言或者原生的C语言开发扩展。PL/pgSQL开发简单,然而性能上较原生的C语言要逊色不少。有不少人已经做过相关的性能测试,这里就不再重复说明。我们开发的扩展的目的是为了增强生产的 PostgreSQL,自然要选择性能更好的C语言。



基本命令

创建

1
2
3
4
5
6
7
create extension exten_name;

CREATE EXTENSION [ IF NOT EXISTS ] extension_name
[ WITH ] [ SCHEMA schema_name ]
[ VERSION version ]
[ FROM old_version ]
[ CASCADE ]

删除

1
2
3
drop extension postgres_fdw cascade;

DROP EXTENSION [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

管理视图pg_extension

1
2
3
4
5
6
shell=# select * from pg_extension;
extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
----------+----------+--------------+----------------+------------+-----------+--------------
plpgsql | 10 | 11 | f | 1.0 | |
oraftops | 10 | 8001 | f | 1.0 | |
(2 rows)

插件编写

通常一个 PostgreSQL 内核插件包括下面的部分

  1. 包含功能的逻辑的动态库,即 so 文件。
  2. 描述插件信息的的控制文件,即 control 文件。
  3. 一组文件用于创建、更新和删除插件,这是一组按照版本命名的 SQL 文本文件。

新建插件

为了进行create extension命令加载扩展,必须存在以下两个文件:

  • extension_name.control 控制文件,声明该扩展的基础信息。
  • extension--version.sql 加载扩展所需要执行的SQL文件。
1
2
3
4
4 -rw-rw-r--. 1 Pg Pg    475 Aug  7 14:56 file_fdw--1.0.sql
4 -rw-rw-r--. 1 Pg Pg 155 Aug 7 14:56 file_fdw.control
4 -rw-rw-r--. 1 Pg Pg 467 Aug 7 14:56 Makefile
4 -rw-rw-r--. 1 Pg Pg 382 Aug 30 12:42 rock_hello.c

代码如下:

rock_hello.control
1
2
3
4
5
# rock_hello extension
comment = 'foreign-data wrapper for flat file access'
default_version = '1.0'
module_pathname = '$libdir/rock_hello'
relocatable = true
rock_hello--1.0.sql
1
2
3
4
5
6
7
8
9
10
/* contrib/rock_hello/rock_hello--1.0.sql */

-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "create EXTENSION rock_hello" to load this file. \quit

CREATE FUNCTION say_hello()
RETURNS text
AS 'MODULE_PATHNAME'
--AS '$libdir/rock_hello'
LANGUAGE C STRICT;
rock_hello.c
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#include "postgres.h"
#include "fmgr.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PG_FUNCTION_INFO_V1(say_hello);

Datum say_hello(PG_FUNCTION_ARGS)
{
char* arg = PG_GETARG_CSTRING(0);

PG_RETURN_CSTRING("say_hello!");
}

以上代码中.h 数据类型与函数 查看上一篇,Pg添加共享库。

Makefile
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# contrib/rock_hello/Makefile
MODULES = rock_hello

## 扩展名称;
EXTENSION = rock_hello

## 扩展安装的SQL文件;
DATA = rock_hello--1.0.sql

## 扩展描述;
PGFILEDESC = "rock_hello - foreign data wrapper for files"

### 以下为Pg构建扩展相关命令;
ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS) ## 环境变量参数加载;
else
subdir = contrib/rock_hello
top_builddir = ../..
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/contrib/contrib-global.mk
endif
编译并加载
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
[Pg@yfslcentos71 rock_hello]$ make
[Pg@yfslcentos71 rock_hello]$ sudo make install
[Pg@yfslcentos71 rock_hello]$ psql
Pg=# create extension rock_hello;
CREATE EXTENSION
Pg=#
Pg=#
Pg=# select * from pg_extension;
extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
------------+----------+--------------+----------------+------------+-----------+--------------
plpgsql | 10 | 11 | f | 1.0 | |
oraftops | 10 | 8001 | f | 1.0 | |
rock_hello | 10 | 2200 | t | 1.0 | |
(3 rows)
Pg=# select say_hello();
say_hello
-----------
ay_hello!
(1 row)

插件更新

有时候,我们需要做插件的 BUGFIX ,或定制一些功能。这就用到了插件更新功能。

  • 首先,我们需要升级插件的小版本

    修改控制文件 .control, 增加一个小版本,如果当前版本是 1.0,则文件中版本号修改成 1.1

  • 添加新版本的的 DDL SQL文件 ==> 全部DDL.sql

    添加新版本的DDL SQL 文件*–1.1.sql, 用于从零创建该插件。

    该 SQL 文件应该包括该插件的所有对象的 DDL。

  • 添加用户老版本升级到新版本的DDL SQL文件 ==> 升级操作DDL.sql

    创建 *1.0–-1.1.sql,用于从版本 1.0升级到 1.1

    该 SQL 文件只包含 1.1版本中新创建的对象。用户的升级操作会调用该 SQL 文件,从而避免了完全重新创建。

  • 修改源码添加新的功能,编译并安装到指定目录。 C源码编写

  • 使用 SQL 升级小版本

    1
    alter extension postgres_fdw update;

如果成功更新,我们能从视图中看到对应的小版本号被更新了。

1
2
3
4
5
postgres=# select * from pg_extension ;
extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
--------------+----------+--------------+----------------+------------+-----------+--------------
rock_hello | 10 | 2200 | t | 1.1 | |
(2 rows)

使用 PostgreSQL 的插件管理功能,用户很容开发和维护需要的插件。

新增pg_say_name函数:

rock_hello.control
1
2
3
4
5
# rock_hello extension
comment = 'foreign-data wrapper for flat file access'
default_version = '1.1' ## 更改版本号
module_pathname = '$libdir/rock_hello'
relocatable = true
rock_hello--1.1.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
/* contrib/rock_hello/rock_hello--1.1.sql */

-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "create EXTENSION rock_hello" to load this file. \quit

--- 以前DDL
CREATE FUNCTION say_hello()
RETURNS text
AS 'MODULE_PATHNAME'
--AS '$libdir/rock_hello'
LANGUAGE C STRICT;

--- 新增处理函数;
CREATE FUNCTION pg_say_name(text)
RETURNS text
AS 'MODULE_PATHNAME'
--AS '$libdir/rock_hello'
LANGUAGE C STRICT;
rock_hello--1.0--1.1.sql 升级DDL
1
2
3
4
5
6
7
8
9
10
/* contrib/rock_hello/rock_hello--1.0--1.1.sql */

-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "alter EXTENSION rock_hello update to 1.1" to load this file. \quit
-- 新增Function;
CREATE FUNCTION pg_say_name(text)
RETURNS text
AS 'MODULE_PATHNAME'
--AS '$libdir/rock_hello'
LANGUAGE C STRICT;
Makefile
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# contrib/rock_hello/Makefile
MODULES = rock_hello

## 扩展名称;
EXTENSION = rock_hello

## 扩展安装的SQL文件;
DATA = rock_hello--1.1.sql rock_hello--1.0--1.1.sql

## 扩展描述;
PGFILEDESC = "rock_hello - foreign data wrapper for files"

### 以下为Pg构建扩展相关命令;
ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
else
subdir = contrib/rock_hello
top_builddir = ../..
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/contrib/contrib-global.mk
endif
编译并加载
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
[Pg@yfslcentos71 rock_hello]$ make
[Pg@yfslcentos71 rock_hello]$ sudo make install
[Pg@yfslcentos71 rock_hello]$ psql
Pg=# alter extension rock_hello update;
ALTER EXTENSION
Pg=#
Pg=# select * from pg_extension;
extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
------------+----------+--------------+----------------+------------+-----------+--------------
plpgsql | 10 | 11 | f | 1.0 | |
oraftops | 10 | 8001 | f | 1.0 | |
rock_hello | 10 | 2200 | t | 1.1 | |
(3 rows)

Pg=#
Pg=# select pg_say_name('nihao');
pg_say_name
-------------
nihao
(1 row)

编译过程分析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
[Pg@yfslcentos71 rock_hello]$ make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -fPIC -I. -I. -I../../src/include -D_GNU_SOURCE -c -o rock_hello.o rock_hello.c
rock_hello.c: In function ‘say_hello’:
rock_hello.c:15:11: warning: unused variable ‘arg’ [-Wunused-variable]
char* arg = PG_GETARG_CSTRING(0);
^
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -fPIC -L../../src/port -L../../src/common -Wl,--as-needed -Wl,-rpath,'/usr/local/postgresql/lib',--enable-new-dtags -shared -o rock_hello.so rock_hello.o
[Pg@yfslcentos71 rock_hello]$ make install
/usr/bin/mkdir -p '/usr/local/postgresql/share/extension'
/usr/bin/mkdir -p '/usr/local/postgresql/share/extension'
/usr/bin/mkdir -p '/usr/local/postgresql/lib'
/usr/bin/install -c -m 644 ./rock_hello.control '/usr/local/postgresql/share/extension/'
/usr/bin/install: cannot create regular file ‘/usr/local/postgresql/share/extension/rock_hello.control’: Permission denied
make: *** [install] Error 1
[Pg@yfslcentos71 rock_hello]$
[Pg@yfslcentos71 rock_hello]$ sudo make install
[sudo] password for Pg:
/usr/bin/mkdir -p '/usr/local/postgresql/share/extension'
/usr/bin/mkdir -p '/usr/local/postgresql/share/extension'
/usr/bin/mkdir -p '/usr/local/postgresql/lib'
/usr/bin/install -c -m 644 ./rock_hello.control '/usr/local/postgresql/share/extension/'
/usr/bin/install -c -m 644 ./rock_hello--1.0.sql '/usr/local/postgresql/share/extension/'
/usr/bin/install -c -m 755 rock_hello.so '/usr/local/postgresql/lib/'

我们发现make过程中添加了 -fPIC -shared, 实际此处是编译成共享库的过程。

1
2
3
4
5
Pg=# select proname,prolang, prorettype,proargtypes, prosrc,probin from pg_proc where probin like '%rock_hello%';
proname | prolang | prorettype | proargtypes | prosrc | probin
-------------+---------+------------+-------------+-------------+--------------------
say_hello | 13 | 25 | | say_hello | $libdir/rock_hello
pg_say_name | 13 | 25 | 25 | pg_say_name | $libdir/rock_hello

测试 make installcheck (未完)

Makefile

1
2
3
4
5
## list of regression test cases;
REGRESS = rock_hello

## extra files to remove in make clean
EXTRA_CLEAN = sql/rock_hello.sql expected/rock_hello.out

make installcheck 会调用psql执行每一个测试脚本,并将结果输出与相应的预期输出进行比较。

  • 测试脚本必须在sql目录中出现。
  • expected目录放置一个包含预期输出的文件。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
[Pg@yfslcentos71 rock_hello]$ make installcheck
make -C ../../src/test/regress pg_regress
make[1]: Entering directory `/home/Pg/hgdb-core/src/test/regress'
make -C ../../../src/port all
make[2]: Entering directory `/home/Pg/hgdb-core/src/port'
make -C ../backend submake-errcodes
make[3]: Entering directory `/home/Pg/hgdb-core/src/backend'
make[3]: Nothing to be done for `submake-errcodes'.
make[3]: Leaving directory `/home/Pg/hgdb-core/src/backend'
make[2]: Leaving directory `/home/Pg/hgdb-core/src/port'
make -C ../../../src/common all
make[2]: Entering directory `/home/Pg/hgdb-core/src/common'
make -C ../backend submake-errcodes
make[3]: Entering directory `/home/Pg/hgdb-core/src/backend'
make[3]: Nothing to be done for `submake-errcodes'.
make[3]: Leaving directory `/home/Pg/hgdb-core/src/backend'
make[2]: Leaving directory `/home/Pg/hgdb-core/src/common'
make[1]: Leaving directory `/home/Pg/hgdb-core/src/test/regress'
../../src/test/regress/pg_regress --inputdir=. --bindir='/usr/local/postgresql/bin' --dbname=contrib_regression rock_hello
(using postmaster on Unix socket, default port)
============== dropping database "contrib_regression" ==============
NOTICE: database "contrib_regression" does not exist, skipping
DROP DATABASE
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
============== dropping extension "oraftops" ==============

补充

Makefile变量设置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
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)

The following variables can also be set:

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

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

EXTRA_CLEAN
extra files to remove in make clean

PG_CPPFLAGS
will be added to CPPFLAGS

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)
  • 插件是通过动态库形式引入到内核中。和内核在同一个进程中运行,且没有内存保护,影响内核的稳定性。开发中需要特别注意内存的使用。不要造成内存泄露或越界写。建议使用 PostgreSQL 的内存管理机制,插件中也能使用。
  • 内核中被标记成PGDLLIMPORT 的全局变量都能在插件中直接使用,这些通常是一些 GUC 参数。
  • 内核中非 static 的函数也能在插件中使用,只需要先 extern 它们。
  • 我们可以实现 _PG_init 用于实现一些初始化工作,该函数在连接建立后只会被执行一次。
  • 我们可以在 _PG_init 中使用函数 DefineCustom*Variable 定义对应插件相关的 GUC 参数,他们可以用于开启和关闭该插件的一些功能。
  • 插件的参数需要以插件名开头且加上点,例如 oss_fdw.enable_parallel_read。

rock_hello.tar.gz 下载

欣赏此文? 求鼓励,求支持!