postgresql插件应用及开发实验

一、体验 pg_vector

1. 拉取 pg_vector docker 镜像

使用以下命令拉取 pg_vector 的 Docker 镜像：

docker pull swr.cn-north-4.myhuaweicloud.com/ddn-k8s/docker.io/pgvector/pgvector:pg17

2. 修改镜像名字

将拉取的镜像名字修改为 pg_vector:pg17：

docker tag sha256:206f8e0dc14e9a9cdf58fef9976f8ca5d8e06101a6c6fe47313724dc8571d12b pg_vector:pg17

3. 删除原长名镜像

删除原本的长名镜像：

docker rmi swr.cn-north-4.myhuaweicloud.com/ddn-k8s/docker.io/pgvector/pgvector:pg17

4. 创建 pg_vector 容器

使用以下命令创建 pg_vector 容器：

docker run --name pgv \
--restart=always \
-e POSTGRES_USER=pgvector \
-e POSTGRES_PASSWORD=123456 \
-v /srv/tlw/pgvectordata:/var/lib/postgresql/data \
-p 54333:5432 \
-d pg_vector:pg17

注意：/srv/tlw/pgvectordata 是挂载的本地目录，可以根据需要修改。

5. 进入容器

使用以下命令进入容器：

docker exec -it pgv bash

6. 使用 psql 连接数据库

使用 psql 连接数据库，注意用户是 pgvector 不是 postgres：

psql -U pgvector

7. 创建测试数据库并安装 pg_vector 插件

在 psql 中执行以下 SQL 语句：

CREATE DATABASE vector;
\c vector
CREATE EXTENSION vector;
\dx

其中，\dx 用于查看插件是否安装成功。

二、编写 PostgreSQL 插件Hello

0. 插件简介

一个最基本的插件至少需要以下文件：

plugin/
├── Makefile
├── Your-Plugin-Name.control
└── Your-Plugin-Name--1.0.sql

其中

• Makefile构建脚本，用于编译插件代码并安装到 PostgreSQL 目录通过 make 命令编译插件为共享库文件（.so），并安装到 PostgreSQL 的插件目录。make这个命令就像是批处理的gcc，用于编译链接各种文件，makefile就是指导make编译的文件。
• .control插件元数据文件，包含插件基本信息，用于告诉 PostgreSQL 关于插件的版本、路径、依赖项等信息。
• .sql插件安装脚本，包含创建扩展、函数、类型等数据库对象的 SQL 语句，是连接咱们插件和PG数据库的桥梁。

一般的话Makefile和.control文件都不需要太大修改，都是有模板的，大多只需要改一改插件名字就ok🤭。

我们写的第一个示例插件hello的文件树如下：

hello/
├── Makefile
├── hello.c
├── hello.control
└── hello--1.0.sql

多出来的hello.c就是一个调用外部c程序进行编写的c代码，可以直接被make编译进去，但是区别于一般的c语言代码，PG作为一个用C语言构建的数据库系统，有它自己独特的C语言代码风格（本质上是定义了许许多多的特定类型的结构体<struct>）。其中这个程序也可以链接外部的c语言程序包，引用的时候需要在makefile中写明（起码得链接起来才能编译成功是吧,,ԾㅂԾ,,)。

接下来让我们创建一个pg插件，用于在控制台输出一个含有hello的字符串。

1. 创建插件目录

在容器内创建插件目录：（也可以在本地挂载的数据库文件的目录创建子文件目录）

docker exec -it pgv bash -c "mkdir /var/lib/postgresql/data/myplugin"
docker exec -it pgv bash -c "mkdir /var/lib/postgresql/data/myplugin/hello"
cd /srv/tlw/pgvectordata/myplugin/hello

2. 编写插件文件

hello.control

# hello.control
comment = 'A simple PostgreSQL plugin'
default_version = '1.0'
module_pathname = '$libdir/hello'
relocatable = true

1. comment = 'A simple PostgreSQL plugin'

• 功能：这行代码为插件添加了一段描述性的注释信息。此注释会在使用 \dx 命令查看数据库中所有扩展信息时显示出来，方便数据库管理员和开发者快速了解该插件的用途。
• 示例：当你在 psql 中执行 \dx 命令时，就能看到关于这个插件的简短描述。

2. default_version = '1.0'

• 功能：这行代码指定了插件的默认版本号。在创建插件时，如果没有特别指定版本，PostgreSQL 会使用这个默认版本来创建插件实例。版本号有助于管理插件的更新和维护。
• 示例：当你执行 CREATE EXTENSION hello; 语句时，PostgreSQL 会使用 1.0 版本来创建 hello 插件。

3. module_pathname = '$libdir/hello'

• 功能：这行代码定义了插件共享库文件的路径。$libdir 是 PostgreSQL 的一个变量，代表数据库安装的共享库目录。在实际运行时，PostgreSQL 会将 $libdir 替换为具体的目录路径，然后去该路径下查找名为 hello 的共享库文件（通常是 .so 文件）。
• 示例：假设 $libdir 实际对应 /usr/local/pgsql/lib，那么 PostgreSQL 会去 /usr/local/pgsql/lib/hello.so 查找插件的共享库文件。

4.relocatable = true

• 功能：这行代码表明该插件是可重定位的。可重定位的插件意味着它可以在不同的数据库集群之间迁移，而不需要对插件的代码或配置进行修改。这在进行数据库迁移或者在不同环境中部署插件时非常有用。
• 示例：当你需要将包含该插件的数据库从一个服务器迁移到另一个服务器时，如果 relocatable 设置为 true，插件可以顺利迁移并正常工作。

hello--1.0.sql

-- hello--1.0.sql
CREATE OR REPLACE FUNCTION hello()
RETURNS text
AS 'hello', 'hello'
LANGUAGE C STRICT IMMUTABLE;

1. CREATE OR REPLACE FUNCTION hello()
   • 创建或替换一个名为 hello 的函数，该函数没有输入参数 (())。
2. RETURNS text
   • 声明该函数的返回类型为 text（即字符串）。
3. AS 'hello', 'hello'
   • 第一个 'hello'：指定包含该函数的共享库文件名（如 hello.so 或 hello.dll）。
   • 第二个 'hello'：指定共享库中实际的 C 函数名。
   • 示例：如果 C 函数在代码中定义为 pg_hello，则此处应为 'hello', 'pg_hello'。
4. LANGUAGE C
   • 声明该函数是用 C 语言编写的（而非 SQL 或 PL/pgSQL）。
5. STRICT
   • 表示如果函数的输入参数为 NULL，函数将直接返回 NULL，而不会被执行。
6. IMMUTABLE
   • 表示该函数是“不可变的”，即对于相同的输入参数，返回值始终相同，且不会修改数据库状态。这允许查询优化器对函数调用进行缓存或预计算。

hello.c

#include "postgres.h"
#include "fmgr.h"
#include "utils/builtins.h" // 添加此头文件
#include <string.h>

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(hello);

Datum
hello(PG_FUNCTION_ARGS)
{
    text *result = cstring_to_text("Hello from C code!");
    PG_RETURN_TEXT_P(result);
}

• #include "postgres.h"：
  • 这是 PostgreSQL 开发中最基础且核心的头文件，它包含了大量用于构建 PostgreSQL 扩展的基础定义、宏和类型声明。这些定义涵盖了内存管理、错误处理、数据类型、锁机制等多个方面，为后续的开发提供了基本的环境和工具。
• #include "fmgr.h"：
  • 该头文件提供了与函数管理器（Function Manager）相关的功能。函数管理器负责管理和调用 PostgreSQL 中的用户自定义函数，此头文件中定义了函数调用所需的各种数据结构和函数原型，是开发自定义函数必不可少的。
• #include "utils/builtins.h"：
  • 它包含了 PostgreSQL 内置函数的定义和相关工具。在开发扩展时，可能会用到这些内置函数，例如字符串处理、日期处理等函数。同时，该头文件也提供了一些常用的实用工具，方便开发者进行数据处理和转换。
• #include <string.h>：
  • 这是标准 C 库的头文件，提供了一系列字符串处理函数，如 strcpy、strlen 等。在开发过程中，可能需要对字符串进行操作，因此包含这个头文件是很常见的。
• PG_MODULE_MAGIC
  • 这是一个宏定义，其作用是确保加载的模块与当前运行的 PostgreSQL 服务器版本兼容。当 PostgreSQL 加载扩展模块时，会检查这个魔法值，如果不匹配，就会拒绝加载该模块，从而避免因版本不兼容导致的问题。
• PG_FUNCTION_INFO_V1(hello);
  • 这是一个宏，用于向 PostgreSQL 函数管理器注册名为 hello 的函数。V1 表示该函数遵循 PostgreSQL 的版本 1 函数调用约定。注册函数后，PostgreSQL 就能识别并调用这个函数。
• Datum：
  • 这是 PostgreSQL 中用于表示函数返回值的通用数据类型。由于 PostgreSQL 支持多种数据类型，Datum 可以存储任意类型的数据，是一种通用的容器。
• PG_FUNCTION_ARGS：
  • 这是一个宏，用于获取函数调用时传递的参数。在这个简单的例子中，函数没有参数，但在实际开发中，可能需要使用这个宏来获取和处理传入的参数。
• cstring_to_text("Hello from C code!")：
  • 这是一个函数调用，将 C 风格的字符串 "Hello from C code!" 转换为 PostgreSQL 的 text 类型。text 是 PostgreSQL 中用于存储变长字符串的数据类型。
• PG_RETURN_TEXT_P(result)：
  • 这是一个宏，用于将 text 类型的结果返回给 PostgreSQL。它会处理返回值的类型转换和内存管理，确保结果能正确地返回给调用者。

Makefile

# 插件名称
EXTENSION = hello
# 插件版本
DATA = hello--1.0.sql
# 编译生成的目标文件
MODULE_big = hello
OBJS = hello.o

# 获取pg_config命令路径
PG_CONFIG = pg_config
# 获取PGXS路径
PGXS := $(shell $(PG_CONFIG) --pgxs)
# 引入PGXS
include $(PGXS)             

EXTENSION = hello

• 作用：指定插件的名称。在 PostgreSQL 中，插件通常也被称为扩展（extension），这个变量的值会作为插件在数据库中的标识名称。当你在数据库中使用 CREATE EXTENSION 或 DROP EXTENSION 等命令时，就会用到这个名称。
• 示例：你可以使用 CREATE EXTENSION hello; 来安装这个名为 hello 的插件。

DATA = hello--1.0.sql

• 作用：指定插件的数据文件，通常是一个 SQL 脚本文件。这个文件包含了创建插件所需的数据库对象（如函数、表、视图等）的 SQL 语句。在安装插件时，PostgreSQL 会执行这个文件中的 SQL 语句来完成插件的初始化。
• 示例：hello--1.0.sql 可能包含创建函数、表等对象的语句，像创建一个简单的函数 CREATE FUNCTION hello() RETURNS text AS $$ SELECT 'Hello!'; $$ LANGUAGE sql;。

MODULE_big = hello

• 作用：指定插件的共享库文件名（不包含扩展名）。在编译完成后，会生成一个共享库文件（通常是 .so 文件，在 Linux 系统下），这个文件包含了插件的 C 代码实现。MODULE_big 变量的值决定了这个共享库文件的名称。
• 示例：编译完成后会生成 hello.so 文件，这个文件会被安装到 PostgreSQL 的共享库目录中。

OBJS = hello.o

• 作用：指定编译插件所需的目标文件列表。在编译过程中，源文件（如 .c 文件）会被编译成目标文件（.o 文件），然后这些目标文件会被链接成共享库文件。这里的 hello.o 就是由 hello.c 源文件编译生成的目标文件。

PG_CONFIG = pg_config

• 作用：指定 pg_config 命令的路径。pg_config 是 PostgreSQL 提供的一个工具，用于获取 PostgreSQL 的各种配置信息，如安装路径、编译选项等。通过这个变量，可以方便地调用 pg_config 命令。

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

• 作用：获取 PostgreSQL 扩展构建系统（PGXS）的路径。$(shell ...) 是 Makefile 中的一个函数，用于执行 shell 命令并获取其输出结果。$(PG_CONFIG) --pgxs 会调用 pg_config 命令并传递 --pgxs 参数，从而获取 PGXS 的路径。PGXS 是一个用于构建 PostgreSQL 扩展的 Makefile 框架，它提供了一系列的规则和变量，方便开发者编译和安装插件。

include $(PGXS)

• 作用：引入 PGXS 框架。通过 include 指令，将 PGXS 的 Makefile 内容包含到当前的 Makefile 中，这样就可以使用 PGXS 提供的规则和变量来完成插件的编译和安装。例如，PGXS 会提供 all、install 等目标，分别用于编译和安装插件。

3. 更换 apt 源

由于我们拉下来的docker image是Debian系统的，默认的apt源在国外下载缓慢，所以进入容器后，更换 apt 源：

cat <<EOT > /etc/apt/sources.list
# 清华源
deb http://mirrors.tuna.tsinghua.edu.cn/debian/ bookworm main contrib non-free
# 安全更新
deb http://mirrors.tuna.tsinghua.edu.cn/debian-security/ bookworm-security main contrib non-free
# 更新与回溯
deb http://mirrors.tuna.tsinghua.edu.cn/debian/ bookworm-updates main contrib non-free
deb http://mirrors.tuna.tsinghua.edu.cn/debian/ bookworm-backports main contrib non-free
EOT

有兴趣可以拆解一下这个cat操作，这些操作在这种没有安装任何文本编辑辅助程序的系统环境下，将文字流写入了容器内部的文件里。

4. 安装编译工具

在容器内执行以下命令安装 PostgreSQL 编译所需的工具：

apt-get update
apt-get install -y postgresql-server-dev-17 build-essential git

5. 编译并安装插件

进入容器内的 hello 目录，执行以下命令编译并安装插件：

了解一下为什么从gcc变成了make？批处理程序产生的目的是什么？是为了符合项目开发？

通过make我们还可以继续了解make的进阶版cmake是什么>

make
make install

6. 使用插件

正常安装后，使用 psql 连接数据库：

psql -U pgvector

在 psql 中执行以下 SQL 语句：

CREATE EXTENSION hello;
\df
SELECT * FROM hello();

可以看看psql --help看看\d开头的命令都是干嘛的?\df和\dx是否有联系?

我们最终需要实现：

在test数据库中创建该插件，函数hello()可用，且hello在插件列表内能够查询到。

函数hello必须返回包含"hello"的字符串（不区分大小写）

三、 自行尝试 编写字符统计函数char_count

抽象函数表述

• 函数体：char_count(TEXT,CHAR)
• 输入：两个形参类型为TEXT和CHAR，TEXT是一个任意长度的字符串，CHAR是一个单个的字符。
• 功能：用于统计CHAR在TEXT中出现的次数。
• 输出：一个整数CHAR出现的次数。

示例

比如'This is Simple string for test'中有1个f，0个h和4个i，那么
SELECT char_count('This is Simple string for test','i')的结果为4
SELECT char_count('This is Simple string for test','f')的结果为1
SELECT char_count('This is Simple string for test','h')的结果为0