如何在PostgreSQL中调试plpgsql存储过程(pldebugger, pldbgapi)

标签

PostgreSQL , 调试plpgsql

背景

PostgreSQL支持多种存储过程语言，例如plpgsql, C, plpython, plperl, pltcl, pljava, 等等。

用户可以使用这些存储过程语言，创建对应的函数或存储过程（returns void）。

那么如何调试PostgreSQL的存储过程呢？社区提供了一个插件pldebugger，可用于调试存储过程。

https://git.postgresql.org/gitweb/?p=pldebugger.git;a=summary

pldebugger介绍

  16 Installation  
  17 ------------  
  18   
  19 - Copy this directory to contrib/ in your PostgreSQL source tree.  
  20   
  21 - Run 'make; make install'  
  22   
  23 - Edit your postgresql.conf file, and modify the shared_preload_libraries config  
  24   option to look like:  
  25   
  26   shared_preload_libraries = '$libdir/plugin_debugger'  
  27   
  28 - Restart PostgreSQL for the new setting to take effect.  
  29   
  30 - Run the following command in the database or databases that you wish to  
  31   debug functions in:  
  32   
  33   CREATE EXTENSION pldbgapi;  
  34   
  35   (on server versions older than 9.1, you must instead run the pldbgapi--1.0.sql  
  36   script directly using psql).  
  37   
  38   
  39 Usage  
  40 -----  
  41   
  42 Connect pgAdmin to the database containing the functions you wish to debug.  
  43 Right-click the function to debug, and select Debugging->Debug to execute and  
  44 debug the function immediately, or select Debugging->Set Global Breakpoint to  
  45 set a breakpoint on the function. This will cause the debugger to wait for  
  46 another session (such as a backend servicing a web app) to execute the function  
  47 and allow you to debug in-context.  
  48   
  49 For further information, please see the pgAdmin documentation.  
  50   
  51   
  52 Troubleshooting  
  53 ---------------  
  54   
  55 The majority of problems we've encountered with the plugin are caused by  
  56 failing to add (or incorrectly adding) the debugger plugin library to the  
  57 shared_preload_libraries configuration directive in postgresql.conf (following  
  58 which, the server *must* be restarted). This will prevent global breakpoints  
  59 working on all platforms, and on some (notably Windows) may prevent the   
  60 pldbgapi.sql script from executing correctly.  
  61   
  62   
  63 Architecture  
  64 ------------  
  65   
  66 The debugger consists of three parts:  
  67   
  68 1. The client. This is typically a GUI displays the source code, current  
  69    stack frame, variables etc, and allows the user to set breakpoints and  
  70    step throught the code. The client can reside on a different host than  
  71    the database server.  
  72   
  73 2. The target backend. This is the backend that runs the code being debugged.  
  74    The plugin_debugger.so library must be loaded into the target backend.  
  75   
  76 3. Debugging proxy. This is another backend process that the client is  
  77    connected to. The API functions, pldbg_* in pldbgapi.so library, are  
  78    run in this backend.  
  79   
  80 The client is to connected to the debugging proxy using a regular libpq  
  81 connection. When a debugging session is active, the proxy is connected  
  82 to the target via a socket. The protocol between the proxy and the target  
  83 backend is not visible to others, and is subject to change. The pldbg_*  
  84 API functions form the public interface to the debugging facility.  
  85   
  86   
  87 debugger client  *------ libpq --------* Proxy backend  
  88   (pgAdmin)                                 *  
  89                                             |  
  90                                   pldebugger socket connection  
  91                                             |  
  92                                             *  
  93 application client *----- libpq -------* Target backend  

如果在编译pldebugger时遇到如下告警，可以修改一下pldbgapi.c

pldbgapi.c: In function ‘pldbg_get_stack’:  
pldbgapi.c:790:25: warning: format ‘%d’ expects argument of type ‘int’, but argument 3 has type ‘uint64 {aka long unsigned int}’ [-Wformat=]  
   sprintf( callCount, "%d", srf->call_cntr );  
                         ^  
  
修改如下  
  
                /*  
                 * frameString points to a string like:  
                 *      targetName:funcOID:lineNumber:arguments  
                 */  
                sprintf( callCount, "%zu", srf->call_cntr );  

pldebugger安装

1. 编译软件

git clone git://git.postgresql.org/git/pldebugger.git  
  
cd pldebugger  
  
export PATH=/home/digoal/pgsql9.6/bin:$PATH  
  
USE_PGXS=1 make clean  
USE_PGXS=1 make  
USE_PGXS=1 make install  

2. 修改配置

cd $PGDATA  
vi postgresql.conf  
  
shared_preload_libraries = '$libdir/plugin_debugger'  

3. 重启数据库

pg_ctl restart -m fast  

如何调试存储过程

1. 在需要调试存储过程的目标数据库中，安装pldbgapi插件

postgres=# create extension pldbgapi ;  
CREATE EXTENSION  

2. 创建被调试的测试代码(如果已经有目标函数了,请忽略此步骤)

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;   

3. 打开pgAdmin客户端，使用pgAdmin登陆到这个数据库, 右键点击函数，点击调试选项。

参考

https://git.postgresql.org/gitweb/?p=pldebugger.git;a=summary