PostgreSQL 9.1.3 plpgsql debugger module

82602716

2012-07-31

关注关注

今天一位网友在群里面问PostgreSQL 9.0如何debug 函数. 我记得在8.3的时候有一个插件叫edb-debugger是可以使用的. 手头上没有9.0的数据库, 于是测试了一下在9.1上能不能用, 结果是编译不通过.

最后找到了解决办法, 记录如下.

在pgfoundry中有一个开源的edb-debugger插件可以用来调试PostgreSQL的PLPGSQL函数.

http://pgfoundry.org/projects/edb-debugger/

但是这个版本太老, 在PostgreSQL 9.1中无法编译通过, 报错如下.

make

Makefile:63: warning: overriding commands for target `install'

../../src/makefiles/pgxs.mk:120: warning: ignoring old commands for target `install'

Makefile:77: warning: overriding commands for target `installdirs'

../../src/makefiles/pgxs.mk:150: warning: ignoring old commands for target `installdirs'

gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic  -I. -I. -I../../src/include -D_GNU_SOURCE -I/usr/include/libxml2   -c -o pldbgapi.o pldbgapi.c

pldbgapi.c: In function ‘pldbg_attach_to_port’:

pldbgapi.c:346: warning: implicit declaration of function ‘MAKE_OFFSET’

pldbgapi.c: In function ‘pldbg_wait_for_target’:

pldbgapi.c:474: warning: implicit declaration of function ‘SHM_OFFSET_VALID’

pldbgapi.c:476: warning: implicit declaration of function ‘MAKE_PTR’

pldbgapi.c:476: warning: cast to pointer from integer of different size

gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic  -L../../src/port  -Wl,-rpath,'/opt/pgsql/lib',--enable-new-dtags  -shared -o pldbgapi.so pldbgapi.o

gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic  -I. -I. -I../../src/include -D_GNU_SOURCE -I/usr/include/libxml2   -c -o targetinfo.o targetinfo.c

targetinfo.c: In function ‘getTriggerFuncOid’:

targetinfo.c:268: error: ‘SnapshotNow’ undeclared (first use in this function)

targetinfo.c:268: error: (Each undeclared identifier is reported only once

targetinfo.c:268: error: for each function it appears in.)

targetinfo.c: In function ‘getProcOidBySig’:

targetinfo.c:508: error: too few arguments to function ‘FuncnameGetCandidates’

targetinfo.c: In function ‘getProcOidByName’:

targetinfo.c:555: error: too few arguments to function ‘FuncnameGetCandidates’

make: *** [targetinfo.o] Error 1

rm pldbgapi.o

不知道为什么不更新了, 作者是这两位.

Korry Douglas ([email protected])

Dave Page ([email protected])

在EDB发布的EDB-AS版本中是包含了debugger的.

只是直接把它的so文件拷贝到开源版本的PostgreSQL中无法使用.

例如我把EDB-AS 9.1.2.2版本的$libdir/plugins/plugin_debugger.so文件拷贝到开源的PostgreSQL $PGHOME/lib/plugins/目录下.

配置postgresql.conf

shared_preload_libraries = '$libdir/plugins/plugin_debugger'

在启动数据库时报错如下 :

FATAL:  could not load library "/opt/pgsql/lib/plugins/plugin_debugger.so": /opt/pgsql/lib/plugins/plugin_debugger.so: undefined symbol: NonSPLFunctionContext

这个NonSPLFunctionContext在PostgresPlus/9.1AS/include/server/utils/elog.h 文件里面定义的.

但是把它拷贝到开源的PostgreSQL的/opt/pgsql/include/server/utils/elog.h后依旧.

虽然pgfoundry里面提供下载的只有0.9.3版本, 庆幸的是cvs里面有最新的.

请见参考链接, 把这些文件下载过了后放到源码的contrib目录里面新建一个debugger目录.

编译过程 :

su - root

. /home/postgres/.bash_profile

cd /opt/soft_bak/postgresql-9.1.3/contrib/debugger/

make

make install

修改数据库配置文件 :

vi $PGDATA/postgresql.conf

shared_preload_libraries = '$libdir/plugins/plugin_debugger'

重启数据库,

在需要调试的数据库里面使用超级用户安装函数和类.

psql -h 127.0.0.1 digoal postgres -f /opt/pgsql/share/contrib/pldbgapi.sql 

CREATE TYPE

CREATE TYPE

CREATE TYPE

CREATE TYPE

CREATE TYPE

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

创建一个测试函数 :

create or replace function debugger_test (i int) returns int as $$

declare

v_result int;

begin

v_result := 0;

if i<0 then

  raise notice 'Please enter i >=0.';

  raise exception '';

end if;

for x in 0..i loop

v_result := v_result + x;

end loop;

return v_result;

exception

when others then

  v_result := 0;

  return v_result;

end;

$$ language plpgsql;

使用pgAdmin登陆到这个数据库, 右键点击函数的时候就有调试选项了.