postgresql udf开发

目录

介绍

接到需求，要在greenplum上开发udaf（聚合函数）。Greenplum基于postgresql开发，所以可以参考postgresql的资料开发udf

UDF(User-Defined-Function) 一进一出。

UDAF(User-Defined Aggregation Funcation) 聚集函数，多进一出。Count/max/min

UDTF(User-Defined Table-Generating Functions) 一进多出。

为了稳妥起见，先开发了只满足需求基本要求的udf，然后逐步改成udaf。

开发环境：Greenplum 6.8.1，其基于postgresql 9.4。
开发语言：c语言

udf开发所需要的头文件一般在${GPHOME}/include/postgresql/server/下（该路径可以通过运行pg_config --includedir-server得到），不过其中的很多.h文件中的api没有注释，所以可以下载postgresql源代码（或许也可以下载Greenplum源代码）并从其中.c文件中寻找对应的api的实现和接口注释

动态加载

postgresql的c语言扩展被编译成动态链接库，postgresql根据需要可在运行中加载该库。

动态加载对象文件在首次使用之后将一直滞留在内存中。 在同一个会话中的下一次调用将只需查找符号表的很小开销。 如果你想强制重新加载(比如重新编译之后)， 可以重新开始一个新的会话。

重新编译后，一定记得要重新开个新的会话，不要问我为什么要强调这个（呜呜呜）。

greenplum和postgresql对udf的加载是用dlsym那一套实现的，相关代码在gpdb/src/backend/utils/fmgr/dfmgr.c中，其对外暴露的api在fmgr.h为

/*
 * Routines in dfmgr.c
 */
extern char *Dynamic_library_path;

extern PGFunction load_external_function(char *filename, char *funcname,
					   bool signalNotFound, void **filehandle);
extern PGFunction lookup_external_function(void *filehandle, char *funcname);
extern void load_file(const char *filename, bool restricted);
extern void **find_rendezvous_variable(const char *varName)

基本数据类型

如int等，基本的sql type和c type的对应关系，在官方的文档中就有。

另外基本类型可以有下面三种内部形态(格式)之一：

  • 传递数值，定长
  • 传递引用，定长
  • 传递引用，变长

不过当用新版本的函数调用约定的话，其实我们一般不太用关心这些内部形态

函数参数和返回

函数的声明有版本0（普通c语言风格声明）和版本1（postgresql宏封装风格声明），一般选用后者。

一般使用PG_GETARG_xxx(n)宏获得第n个参数（编号从0开始），其内部先获得索引为n的参数，其类型是Datum，然后转化成该调用的宏对应的类型。
而返回值时会调用PG_RETURN_xxx返回，其先将传入的数据转化成Datum，然后再return

#define PG_GETARG_DATEADT(n)	 DatumGetDateADT(PG_GETARG_DATUM(n))
#define PG_RETURN_DATEADT(x)	 return DateADTGetDatum(x)

另外

PG_GETARG_xxx_COPY() 保证返回一个指定参数的副本，该副本是可以安全地写入的。普通的宏有时候会返回一个指向物理存储在表中的某值的指针， 因此我们不能写入该指针。用PG_GETARG_xxx_COPY()宏保证获取一个可写的结果。

当函数想直接返回函数接收到的参数时，要使用PG_GETARG_xxx_COPY来获取参数。这里以HeapTupleHeader为例：

PG_RETURN_HEAPTUPLEHEADER(PG_GETARG_HEAPTUPLEHEADER_COPY(0));

我曾经在聚合函数中错用PG_GETARG_HEAPTUPLEHEADER宏，然后运行时就遇到数据库出错的情况，报错信息也没有提供有价值的方向，然后费了挺长时间才找到问题根源。

聚合函数

在PostgreSQL里的聚合是依据状态值和状态转换函数定义的。也就是说，聚合操作使用一个随着每个输入行被处理而变化的状态值。要定义一个新的聚合函数，就要选择表示状态值的数据类型、状态初始值、状态转换函数。该状态转换函接受之前的状态值和聚合的输入值作为当前行，并返回一个新的状态值。

还可以声明一个最终处理函数，用于对付期望的聚合结果不同于需要保留在状态值中数据的情况。该最终处理函数接受最后的状态值并返回任何想要的作为聚合的结果。一般而言，状态和最终函数只是普通函数，也可以用在聚合的外面（实际上，创建特殊的只能作为聚合的一部分调用的转换函数通常对性能有帮助）。

sfunc( internal-state, next-data-values ) ---> next-internal-state

ffunc( internal-state ) ---> aggregate-value

CREATE AGGREGATE

另外还有用于窗口函数的Moving-Aggregate模式，和顺序集聚合（其可以接受传给最终处理函数的直接参数，另外好像只有顺序集函数才能接受直接参数）如求集合中的中位数。

组合类型（Composite Type）

CREATE TYPE

因为我要开发的聚合函数比较复杂，中间状态值要用组合类型表示，所以又涉及到组合类型。其类似于c语言中的结构体吧，就是对应于数据表的多列。在c语言udf中对应于HeapTupleHeader。对其简单封装，就是HeapTuple类型（感觉postgresql在这一块有些混乱）

typedef struct HeapTupleData
{
	uint32		t_len;			/* length of *t_data */
	ItemPointerData t_self;		/* SelfItemPointer */
	HeapTupleHeader t_data;		/* -> tuple header and data */
} HeapTupleData;

typedef HeapTupleData *HeapTuple;

组合类型有分为匿名组合类型和具名组合类型。我是用的后者。

函数接收组合类型

函数接收到组合类型参数时，可以使用PG_GETARG_HEAPTUPLEHEADER(n)得到heaptupleheader，并配合GetAttributeByNum或者GetAttributeByNum获取其中的参数，再配合DatumGetxxx将参数从Datum格式转换成xxx格式。

GetAttributeByName是PostgreSQL系统函数， 用来返回当前记录的字段。它有三个参数：类型为HeapTupleHeader的传入函数的参数、你想要的字段名称、 一个确定字段是否为 NULL 的返回参数。GetAttributeByName函数返回一个 Datum值，你可以用对应的DatumGetXXX()宏把它转换成合适的数据类型。 请注意，如果设置了NULL标志，那么返回值是无意义的，在准备对结果做任何处理之前， 总是要先检查NULL标志。还有一个GetAttributeByNum用字段编号而不是字段名选取目标字段。

Datum GetAttributeByNum(HeapTupleHeader tuple,AttrNumber attrno,bool *isNull) // 函数根据索引获取相应参数，其中attrno为位置索引，最小为1（索引0被当做无效值，不要问我怎么知道的）

Datum GetAttributeByName(HeapTupleHeader tuple, const char *attname, bool *isNull) // 函数根据名称获取相应参数。其内部会遍历参数列表以找到attname对应的参数

其实还有个

extern void heap_deform_tuple(HeapTuple tuple, TupleDesc tupleDesc,
				  Datum *values, bool *isnull);

不过该函数是用来解析HeapTuple而不是HeapTupleHeader的。我没有在postgresql里找到将HeapTuple转换成HeapTupleHeader的函数或宏，不过在GetAttributeByNum代码里倒是有相应的转化代码。

函数返回组合类型

要从c语言函数中返回一行或者组合类型，要使用特殊的api，就要#include "funcapi.h"

制作一个复合类型数据值(也就是一个"行")有两种方法： 你可以从一个 Datum 值数组里制作，也可以从一个可以传递给该行的字段类型的输入转换函数的 C 字符串数组里制作。不管是哪种方式，你首先都需要为行结构获取或者制作一个 TupleDesc描述符。在使用 Datums 的时候，你给BlessTupleDesc传递这个TupleDesc 然后为每行调用heap_form_tuple。在使用C字符串的时候， 你给TupleDescGetAttInMetadata 传递TupleDesc，然后为每行调用BuildTupleFromCStrings。 如果是返回一个行集合的场合，所有设置步骤都可以在第一次调用该函数的时候一次性完成。

另外postgresql为了方便大家设置TupleDesc，提供了

TypeFuncClass get_call_result_type(FunctionCallInfo fcinfo,
                                   Oid *resultTypeId,
                                   TupleDesc *resultTupleDesc)

把传递给调用函数自己的fcinfo传递给它(要求使用版本-1 的调用习惯)。 resultTypeId可以声明为NULL或者 接收函数的结果类型OID的局部变量地址(指针)。 resultTupleDesc应该是一个局部的TupleDesc变量地址(指针)。 检查结果是否TYPEFUNC_COMPOSITE；如是， resultTupleDesc就已经填充好需要的TupleDesc了。 如果不是，你可以报告一个类似"返回记录的函数在一个不接受记录的环境中被调用"的错误。

我是选择的Datums数组路线，需要先

    TupleDesc tupdesc;
    /* Build a tuple descriptor for our result type */
    if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
        ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("function returning record called in context that cannot accept type record")));

    tupdesc = BlessTupleDesc(tupdesc);
  • 得到元组描述符TupleDesc
  • 然后构建填充所需要Datums数组和bool数组，这一步是我们大展身手的地方
  • 调用heap_form_tuple函数根据构造好的Datums数组和bool数组生成对应的HeapTuple
  • 接着调用HeapTupleGetDatum（是的没错，其内部调用的HeapTupleHeaderGetDatum）生成对应的Datum
  • 最后调用PG_RETURN_DATUM返回得到的Datum即可。
static inline HeapTuple heap_form_tuple(TupleDesc tupleDescriptor,
				Datum *values, bool *isnull)

#define HeapTupleGetDatum(tuple)		HeapTupleHeaderGetDatum((tuple)->t_data)

#define PG_RETURN_DATUM(x)	 return (x)

其实函数最终返回的是HeapTupleHeader的Datum。

测试

postgresql的测试分为多种，目前主要关心回归测试。其主要是顶级目录或者src/test/regress下运行make check或者make installcheck等运行系统所以的测试套装，其底层调用pg_regress二进制命令运行测试。

运行pg_regress可能报错sh: 1: /usr/local/greenplum-db-oss/bin/psql: not found，可以通过建立符号链接解决问题

mkdir -p /usr/local/greenplum-db-oss/bin
ln -s ${GPHOME}/bin/psql /usr/local/greenplum-db-oss/bin/

待运行的sql脚本们xxx.sql文件存放在sql文件夹下，预期结果们xxx.out文件放在expected文件下。运行回归测试后会产生运行xxx.sql脚本的结果xxx.out，其会被放在results文件夹下。pg_regress会调用diff对比预期结果和实际结果（空格空行都会影响对比结果，如果觉着调整空格空行太过麻烦的话，其实可以将某次运行得到的results/xxx.out复制到excepted/下作为供之后运行结果参考的正确结果），并可能会产生diffs结果文件。

可以运行pg_regress xxx测试xxx，也可以写调度文件（可以在调度文件会指定多个不同的xxx），测试一批测试用例。

Greenplum与之类似，但通常并不能通过所有的postgresql测试用例。

我是使用apt安装的Greenplum，并使用的单机版本。其提供了pg_regress命令，不过其在${GPHOME}/lib/postgresql/pgxs/src/test/regress/下，可能需要手动调用。

PostgreSQL自带测试方法汇总
PostgreSQL回归测试（regress）介绍及执行方法
PostgreSQL回归测试--自定义测试脚本

调试

gdb

可以使用gdb调试运行中的udf，开三个终端，分别用于

  • 终端1用于打开psql，输入sql命令等
  • 终端2用于调试Master上的QD(Query Dispatcher)
  • 终端3用于调试Segment上的QE(Query Executor)，UDF会在此被调用执行。

我通常的做法是：

  1. 在psql中运行select pg_backend_pid();获取QD的进程号
  2. 在终端2中运行gdb -p QD进程号,然后打断点b cdbdisp_dispatchToGang，接着c
  3. 在终端1的psql中输入并执行会调用到我们编写的udf的sql命令，会运行到终端2打的端点处暂停。此时QE进程已经被创建启动了。
  4. 在终端3处运行ps aux|grep postgre | grep idle会看到多个进程（我个人理解这都是QE进程吧），我通常会gdb -p 最小QE进程号，这时，你可以根据函数名或者文件行号打断点到自己编写的udf中（如果是想调试段错误这种，不打断点也可以）
  5. 在终端2即QD的gdb中c，可能需要多次，直到QD的gdb界面显示continue而不是处于可供调试的暂停状态为止
  6. 在终端3级QE的gdb中c，通常会暂停在端点处或者段错误报错处，接着就是正常的调试工作。

我使用apt安装的Greenplum的程序，其实其在gdb里可以调试的，只是gdb找不到对应的源码导致我们无法在gdb中查看源代码而已。可以自己下载与调试的Greenplum对应源代码，然后使用gdb的dir命令帮助gdb找到源代码的位置。我通常是dir到待调试的Greenplum程序文件的所在目录。

利用gdb对greenplum的segment源码进行跟踪的方法
Greenplum 编译、安装、和调试

log

在postgresql的c语言中debug用的log的话，通常可用

ereport(DEBUG1, (errcode(ERRCODE_SUCCESSFUL_COMPLETION), errmsg("your log msg")));

另外，可以在数据库终端里使用ALTER SYSTEM SET client_min_messages TO error;全局设置客户端接收到的日志级别（可能需要运行select pg_reload_conf();重新加载生效）

Assert

set debug_assertions to on;

打开各种断言检查。这是调试助手。如果你经历了奇怪的问题或者崩溃， 那么你可能会想把这个打开，因为它可能暴露编程的错误。要使用这个选项， 我们必须在编译PostgreSQL的时候定义宏USE_ASSERT_CHECKING (通过configure选项--enable-cassert完成)。 请注意，如果启用断言选项编译PostgreSQL，那么debug_assertions缺省就是on

但是在使用apt安装的greenplum上运行报错ERROR: assertion checking is not supported by this build

另外再编译udf时打开USE_ASSERT_CHECKING会发现启用了代码中的pg提供的断言Assert，但是在该产生异常的地方并没有产生异常。

在分析了${GPHOME}/include/postgresql/server/c.h中

/*
 * Trap
 *		Generates an exception if the given condition is true.
 */
#define Trap(condition, errorType) 
	do { 
		if ((assert_enabled) && (condition)) 
			ExceptionalCondition(CppAsString(condition), (errorType), 
								 __FILE__, __LINE__); 
	} while (0)

/*
 *	TrapMacro is the same as Trap but it's intended for use in macros:
 *
 *		#define foo(x) (AssertMacro(x != 0), bar(x))
 *
 *	Isn't CPP fun?
 */
#define TrapMacro(condition, errorType) 
	((bool) ((! assert_enabled) || ! (condition) || 
			 (ExceptionalCondition(CppAsString(condition), (errorType), 
								   __FILE__, __LINE__), 0)))

#define Assert(condition) 
		Trap(!(condition), "FailedAssertion")

#define AssertMacro(condition) 
		((void) TrapMacro(!(condition), "FailedAssertion"))

中之后，我们发现Assert其实底层调用Trap，会判断assert_enabled（我在用apt安装的greenplum中发现该全局变量被置为0了）和断言条件。

所以想要在自己编写的udf中启用postgresql的断言判断的一个hacker的解决方法

  1. 先在udf的c文件中改造Assert宏
#ifdef FORCE_USE_ASSERT_CHECKING
#ifdef Assert
#undef Assert
#endif
#define Assert(condition) 
    do { 
        if (!(condition)) 
            ExceptionalCondition(CppAsString(condition), "FailedAssertion", 
                                 __FILE__, __LINE__); 
    } while (0)
#endif


/* your code use Assert */

/*
#ifdef FORCE_USE_ASSERT_CHECKING
#define Assert(condition) 
        Trap(!(condition), "FailedAssertion")
#endif
*/
  1. 在编译时打开FORCE_USE_ASSERT_CHECKING，即使用make install PG_CFLAGS+=-DFORCE_USE_ASSERT_CHECKING

stack trace

Getting a stack trace of a running PostgreSQL backend on Linux/BSD

profile

  • gprofgprof 在postgresql中的使用，不过这需要自己编译greenplum或者postgresql，太沉重了。我只想profile开发的udf而已。另外gprof也不支持动态库。
  • sprof可以分析共享库，可以我在运行期官方示例就遇到了bug，报错Inconsistency detected by ld.so: dl-open.c: 641: _dl_open: Assertion `_dl_debug_initialize (0, args.nsid)->r_state == RT_CONSISTENT' failed!
  • profil是unistd.h提供的用于profile的函数
  • Valgrind好像也可以做profile，我之前一直简单的把它当做内存泄露检测工具，不过其profile时好像会严重拖慢原程序的运行速度。
  • perfProfiling with perf和Perf：Linux下性能分析工具。其主要提供perf top(类似于top命令)查看当前系统的情况和perf record，perf report运行和记录然后分析。
  • gperftools中的cpu-profiler是谷歌推出的性能分析工具。Google performance Tools (gperftools) 使用心得

一般用linux的perf就可。另外当主查询为count()，子查询为udf里的函数时，可能时因为数据库的优化策略，其并不会真正执行udf里的函数，我们无法在这种情况下对udf里的函数进行profile。当udf函数和generate_series函数同作为子查询中的一员并列执行时，udf函数并不会被执行多次。（不要问我怎么知道的，说出来都是泪）

gperftools

这里说下gperftools的cpu-profiler，其分析时可以不重新编译代码，支持多线程热点分析和链接库热点分析，profile的速度也很快，网上有人说回降低原程序30%的速度，好像直接在服务器线上profile也可以接受。

运行sudo apt-get install google-perftools libgoogle-perftools-dev可以通过apt安装相关包。其中google-perftools主要是安装google-pprof(就是pprof)。libgoogle-perftools-dev会搞定相关的符号链接和开发所需的头文件，而它们依赖的libgoogle-perftools4包会安装相关的库文件

当想要分析udf中的agg函数时。可以添加如下代码

#include <gperftools/profiler.h>


/* Placed at the beginning of the *state transition functions* */
if (!ProfilingIsEnabledForAllThreads()) {
	char filename[25] = "udf_agg_prof.";
	for (int i = strlen(filename); i < sizeof(filename)/sizeof(filename[0]); i++)
		filename[i] = ('a' + (rand() % 26));
	filename[sizeof(filename)/sizeof(filename[0])-1] = '';
	ereport(NOTICE, (errcode(ERRCODE_SUCCESSFUL_COMPLETION), errmsg("ProfilerStart. filename: %s", filename)));
	ProfilerStart(filename);
}


/* Placed at the end of the *final function* */
ProfilerStop();
ereport(NOTICE, (errcode(ERRCODE_SUCCESSFUL_COMPLETION), errmsg("ProfilerStop")));

Gperftools CPU Profiler中提到链接期参数-lprofiler可能需要换成-Wl,--no-as-needed,-lprofiler,--as-needed才能达到想要的效果。对应到udf中，就是在udf的Makefile文件中添加SHLIB_LINK = -Wl,--no-as-needed,-lprofiler,--as-needed。生成的性能记录文件会在segement节点的文件夹里（如 ~/gpdata2/gpsne1/）。然后用google-pprof --text path_to_udfxxxso path_to_profxxx。即可。

C++ Profiler工具之初体验对gprof和gperftools的cpu-profiler代码原理解析

varlena

在Greenplum 6.8.1 (postgresql 9.4) 中，sizeof(varlena) != VARHDRSZ，palloc个还没有实际数据的varlena的时候可能需要注意下。

struct varlena
{
	char		vl_len_[4];		/* Do not touch this field directly! */
	char		vl_dat[1];
};
typedef struct varlena bytea;

#define VARHDRSZ		((int32) sizeof(int32))

但是在postgresql 9.5中，sizeof(varlena) == VARHDRSZ，我也是醉了。一般的这种变长实现，都是类似于postgresql 9.5这种。未来的某一天，Greenplum应该会升级到postgresql 9.5，所以这是个需要注意的地方。

#define FLEXIBLE_ARRAY_MEMBER   /* empty */

struct varlena
{
        char            vl_len_[4];             /* Do not touch this field directly! */
        char            vl_dat[FLEXIBLE_ARRAY_MEMBER];  /* Data content is here */
};

内存上下文(MemoryContext)

gp源代码/src/backend/utils/mmgr/README介绍的挺详细的。另外感觉这篇文章讲的还行Postgresql内存池源码分析

总的来说，Greenplum（postgresql）的内存上下文类似于编程语言中的作用域，在内存上下文中申请的内存类似于作用域中的变量。

MemoryContextData和AllocSetContext的关系就是抽象父类（或者接口类）和具体实现类的关系，只不过是用c语言特性实现的。

在开发聚集函数的过程中，如果想要让palloc得到的内存生命周期跨越跨在状态转移函数的多次调用，需要调用AllocSetContextCreate创建新的内存上下文，并在其中调用palloc等即可。

如果只用系统默认的内存上下文，在第n次状态转移函数中palloc的内存会在第n+1次状态转移函数被调用之前被回收，但是改内存并没有被清空置0，所以在第n+1次状态转移函数调用中也能访问第n次状态转移函数palloc的内存，但是该内存上的值随时可能会被系统改变，进而会导致出现类似于随机性的bug，类似于最终结果有时对有时错，说多了都是泪。

另外好像不能在一个内存上文中环境下pfree或者repalloc从属于另外一个内存上下文中的内存片。

文件组织

.c文件

存储代码

Makefile文件

postgresql为了方便大家弄了个makefile模板${GPHOME}/lib/postgresql/pgxs/src/makefiles/pgxs.mk，我们在自己的makefile只需要声明几个特定的变量，然后通过添加

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

的方式，导入postgresql的makefile模板就可以了，模板里有关于配置变量的说明。

xxx.control

主要用于存储关于开发的udf模块的信息

xxx.sql

主要用于存储sql命令，用于创建udf等。
常用的命令如下：

CREATE TYPE
CREATE OR REPLACE FUNCTION
CREATE  AGGREGATE

日期

日期在postgresql的c中是DateADT类型，且是Julian date，而且还偏移了POSTGRES_EPOCH_JDATE

void j2date(int jd, int *year, int *month, int *day)
int date2j(int y, int m, int d)


j2date(jd + POSTGRES_EPOCH_JDATE, &year, &month, &day);
int last_day_in_month = date2j(y0 + (int)(m0 == 12), (m0 % 12) + 1, 1) - 1 - POSTGRES_EPOCH_JDATE; // the last day of current month

参考

主要参考

一定要好好多看文档，开发过程中我遇到几个bug，其实在文档里都有提到的。
C-Language Functions
User-defined Aggregates
CREATE FUNCTION | Tanzu Greenplum Docs
CREATE AGGREGATE | Tanzu Greenplum Docs

次要参考

Greenplum Documention
PostgreSQL 9.4.4 中文手册
PostgreSQL: Documentation
Welcome to the PostgreSQL Wiki!
PostgreSQL Source Code: Main Page

PgSQL · 内核开发 · 如何管理你的 PostgreSQL 插件

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/Tifa-Best/p/13547034.html