如何在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