The PL/pgSQL debugger is a project hosted on PostgreSQL Git that provides a debugging interface into PostgreSQL Version 8.2 or higher. The project is hosted at http://git.postgresql.org/gitweb/?p=pldebugger.git;a=summary.
The PL/pgSQL debugger lets you step through the PL/pgSQL code, set and clear breakpoints, view and modify variables, and walk through the call stack.
As you can see from the description, the PL/pgSQL debugger can be quite a handy little tool to have in your arsenal.
OK, now we will move past the glamour and actually get the debugger running on our system. If you install PostgreSQL with one of the packages that contains the debugger, the installation is pretty simple. Otherwise, you will need to build it from the source.
A detailed discussion of how to install the PL/pgSQL debugger from the source is beyond the scope of this book, but I will just list the set of steps to install the debugger quickly. The best way to build the source will be to pull the latest version from the Git repository and follow the README file in the directory. If you want to get started with it quickly and you have a Windows machine available, the simplest way to use the debugger is using the PostgreSQL Windows installer from http://www.postgresql.org/download/windows/.
Here is a set of simple steps that should get you up and running if you want to install from the source:
- Clone the Git repository as shown. You can view the repository at http://git.postgresql.org/gitweb/?p=pldebugger.git;a=summary:
git clone http://git.postgresql.org/git/pldebugger.git
- Copy this
pldebugger/directory tocontrib/in your PostgreSQL source tree. - Run
make && make installin thepldebuggerfolder. - Modify the
shared_preload_librariesconfiguration option inpostgresql.confas follows:shared_preload_libraries = '$libdir/plugin_debugger'
- Restart PostgreSQL.
- Create the debugger extension:
CREATE EXTENSION pldbgapi;
This should install the PL debugger and you should be able to use it with pgAdmin3.
Tip
You can also install PostgreSQL using EnterpriseDB's one-click installers for most platforms, which also includes the PL debugger at http://www.enterprisedb.com/products-services-training/pgdownload.
The PL/pgSQL debugger module works with pgAdmin3. You don't need to perform special steps with the installation of pgAdmin3 for the debugger to function. Install it as usual from your package manager on the platform that you are using. For Ubuntu 10.04 LTS, the command is as follows:
sudo apt-get install pgadmin3
When the debugger is available for a particular database, it can be seen in the context menu when you right-click on a PL/pgSQL function. We have already created some of the debuggers in the earlier part of this chapter. Using format_us_full_name as an example, right-click on it and navigate to Debugging | Debug:
As a result, you will see the following dialog:
Enter some values into the columns, as seen in the preceding screenshot, and click on the Debug button. You will be deposited into the debugger:
This will allow you to step through the code and see the values of any variables as they are being changed. Click on the step-into button a few times to see how the values are modified as the function is performed.
The PL/pgSQL debugger does not require any resources on the server when not actually in use. Because it is invoked manually from within pgAdmin3, it is not resident in memory until it is actually called upon. This architecture does not require any background processes or additional daemons for the sake of debugging.
Also, the PL/pgSQL debugger does not require any special "calling" functions to be written in order to invoke the debugging process. There are no errors to trap and no tables of error codes to interpret. Everything necessary to the debugging process is available in a simple window.
If you connect to your database as a superuser, you also have the ability to set a global break point. This break point can be set on any function or trigger and it will stop the next time any code path calls the function. This is particularly useful if you want to debug your functions or triggers in the context of your entire running application.
The greatest advantage of the PL/pgSQL debugger is that it does not require any special rigging in the functions that are being debugged.
There is no code to be inserted or removed, and good coding practices don't need to be modified with respect to debugging. There is no possibility to "forget" the debugging code when moving to production. All of your PL/pgSQL functions are now instantly ready to debug without any special action.
As you have become painfully aware, the installation of the debugger leaves a lot to be desired. This debugger has not become very popular in the PostgreSQL community at large because of the rather large learning curve involved, and that's just to get it installed.
This form of debugging is meant for personal productivity while actively developing functions. It does not work well as an automation tool.