PostgreSQL 9.1.3 plpgsql debugger module

今天一位網友在群裡面問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.cpldbgapi.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 sizegcc -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.ogcc -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.ctargetinfo.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 oncetargetinfo.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 1rm pldbgapi.o

不知道為什麼不更新了, 作者是這兩位.

Korry Douglas (korry.douglas@enterprisedb.com)Dave Page (dave.page@enterprisedb.com)

在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_profilecd /opt/soft_bak/postgresql-9.1.3/contrib/debugger/makemake install

修改資料庫設定檔 : 

vi $PGDATA/postgresql.confshared_preload_libraries = '$libdir/plugins/plugin_debugger'

重啟資料庫,在需要調試的資料庫裡面使用超級使用者安裝函數和類.

psql -h 127.0.0.1 digoal postgres -f /opt/pgsql/share/contrib/pldbgapi.sql CREATE TYPECREATE TYPECREATE TYPECREATE TYPECREATE TYPECREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTION

建立一個測試函數 : 

create or replace function debugger_test (i int) returns int as $$declarev_result int;beginv_result := 0;if i<0 then  raise notice 'Please enter i >=0.';  raise exception '';end if;for x in 0..i loopv_result := v_result + x;end loop;return v_result;exceptionwhen others then  v_result := 0;  return v_result;end;$$ language plpgsql;

使用pgAdmin登陸到這個資料庫, 右鍵點擊函數的時候就有調試選項了.
一個調試頁面 :