提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  后端

当前栏目

Postgreqsql动态加载plpgsql钩子函数的实例（调试利器）

实例调试 函数 动态 加载 利器 钩子 plpgsql
2023-06-13 09:18:42 时间

1 前言

Postgresql的plpgsql提供了一套钩子函数支持运行时动态加载，非常便于调试plpgsql。本文总结使用方法和实例。

plpgsql的钩子变量plpgsql_plugin_ptr：

// pl_handler.c

void
_PG_init(void)
{
	...
	...
	plpgsql_plugin_ptr = (PLpgSQL_plugin **) find_rendezvous_variable("PLpgSQL_plugin");
}

2 效果

使用load加载so，enable钩子函数。然后执行函数就可以看到回显了，在函数的setup、启动、结束都有函数钩子，可以查看estate、stmt结构的任何变量，非常方便。也可以做一些stmt或datums变量的调整、修改，影响执行过程。

$ psql
psql (16devel)
Type "help" for help.

postgres=# load 'plpgsql_callback';
LOAD
postgres=# DO LANGUAGE plpgsql $$
DECLARE
    cnt int;
BEGIN
    cnt := 1;
    raise notice 'cnt: %', cnt;
END;
$$;
NOTICE:  function setup: "inline_code_block", function has 2 datums
NOTICE:  function begin: "inline_code_block"
NOTICE:  statement [     statement block] begin - [ln: 4]
NOTICE:  statement [          assignment] begin - [ln: 5]
NOTICE:  statement [          assignment] end   - [ln: 5]
NOTICE:  statement [               RAISE] begin - [ln: 6]
NOTICE:  cnt: 1
NOTICE:  statement [               RAISE] end   - [ln: 6]
NOTICE:  statement [              RETURN] begin - [ln: 0]
NOTICE:  statement [              RETURN] end   - [ln: 0]
NOTICE:  statement [     statement block] end   - [ln: 4]
NOTICE:  function end: "inline_code_block"
DO
postgres=#

3 源码

plpgsql_callback.c

#include "postgres.h"
#include "plpgsql.h"

PG_MODULE_MAGIC;

void		_PG_init(void);
void		_PG_fini(void);

static void plpgsql_cb_func_setup(PLpgSQL_execstate *estate, PLpgSQL_function *func);
static void plpgsql_cb_func_beg(PLpgSQL_execstate *estate, PLpgSQL_function *func);
static void plpgsql_cb_func_end(PLpgSQL_execstate *estate, PLpgSQL_function *func);
static void plpgsql_cb_stmt_beg(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt);
static void plpgsql_cb_stmt_end(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt);

const char *
plpgsql_stmt_typename(PLpgSQL_stmt *stmt)
{
	switch (stmt->cmd_type)
	{
		case PLPGSQL_STMT_BLOCK:
			return _("statement block");
		case PLPGSQL_STMT_ASSIGN:
			return _("assignment");
		case PLPGSQL_STMT_IF:
			return "IF";
		case PLPGSQL_STMT_CASE:
			return "CASE";
		case PLPGSQL_STMT_LOOP:
			return "LOOP";
		case PLPGSQL_STMT_WHILE:
			return "WHILE";
		case PLPGSQL_STMT_FORI:
			return _("FOR with integer loop variable");
		case PLPGSQL_STMT_FORS:
			return _("FOR over SELECT rows");
		case PLPGSQL_STMT_FORC:
			return _("FOR over cursor");
		case PLPGSQL_STMT_FOREACH_A:
			return _("FOREACH over array");
		case PLPGSQL_STMT_EXIT:
			return ((PLpgSQL_stmt_exit *) stmt)->is_exit ? "EXIT" : "CONTINUE";
		case PLPGSQL_STMT_RETURN:
			return "RETURN";
		case PLPGSQL_STMT_RETURN_NEXT:
			return "RETURN NEXT";
		case PLPGSQL_STMT_RETURN_QUERY:
			return "RETURN QUERY";
		case PLPGSQL_STMT_RAISE:
			return "RAISE";
		case PLPGSQL_STMT_ASSERT:
			return "ASSERT";
		case PLPGSQL_STMT_EXECSQL:
			return _("SQL statement");
		case PLPGSQL_STMT_DYNEXECUTE:
			return "EXECUTE";
		case PLPGSQL_STMT_DYNFORS:
			return _("FOR over EXECUTE statement");
		case PLPGSQL_STMT_GETDIAG:
			return ((PLpgSQL_stmt_getdiag *) stmt)->is_stacked ?
				"GET STACKED DIAGNOSTICS" : "GET DIAGNOSTICS";
		case PLPGSQL_STMT_OPEN:
			return "OPEN";
		case PLPGSQL_STMT_FETCH:
			return ((PLpgSQL_stmt_fetch *) stmt)->is_move ? "MOVE" : "FETCH";
		case PLPGSQL_STMT_CLOSE:
			return "CLOSE";
		case PLPGSQL_STMT_PERFORM:
			return "PERFORM";
		case PLPGSQL_STMT_CALL:
			return ((PLpgSQL_stmt_call *) stmt)->is_call ? "CALL" : "DO";
		case PLPGSQL_STMT_COMMIT:
			return "COMMIT";
		case PLPGSQL_STMT_ROLLBACK:
			return "ROLLBACK";
	}

	return "unknown";
}

static PLpgSQL_plugin plugin_funcs = {
	plpgsql_cb_func_setup,
	plpgsql_cb_func_beg,
	plpgsql_cb_func_end,
	plpgsql_cb_stmt_beg,
	plpgsql_cb_stmt_end,
	NULL,
	NULL
};

static void
plpgsql_cb_func_setup(PLpgSQL_execstate *estate, PLpgSQL_function *func)
{
	elog(NOTICE, "function setup: \"%s\", function has %d datums", func->fn_signature, func->ndatums);
}

static void
plpgsql_cb_func_beg(PLpgSQL_execstate *estate, PLpgSQL_function *func)
{
	elog(NOTICE, "function begin: \"%s\"", func->fn_signature);
}

static void
plpgsql_cb_func_end(PLpgSQL_execstate *estate, PLpgSQL_function *func)
{
	elog(NOTICE, "function end: \"%s\"", func->fn_signature);
}

static void
plpgsql_cb_stmt_beg(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt)
{
	elog(NOTICE, "statement [%20s] begin - [ln: %d]", plpgsql_stmt_typename(stmt), stmt->lineno);
}

static void
plpgsql_cb_stmt_end(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt)
{
	elog(NOTICE, "statement [%20s] end   - [ln: %d]", plpgsql_stmt_typename(stmt), stmt->lineno);
}


void
_PG_init(void)
{
	PLpgSQL_plugin **var_ptr = (PLpgSQL_plugin **) find_rendezvous_variable("PLpgSQL_plugin");

	*var_ptr = &plugin_funcs;
}

void
_PG_fini(void)
{
}

Makefile

MODULES = plpgsql_callback

REGRESS = plpgsql_callback

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

相关文章