I'm one of those old-fashioned folks that debugs with print lines and raise notices. They're nice. They always work, you can put clock time stops in there and don't require any fancy configuration. At a certain point you do have to pull out a real debugger to see what is going on. This often happens when your one-liners are no longer good enough and now you have to write 20 liners of plpgsql code.
Such is the case with geocoding and the PostGIS tiger geocoder specifically. Lots of interest has revived on that with people submitting bug reports and we've got paying clients in need of a fairly easy and speedy drop-in geocoder that can be molded to handle such things as road way locations, badly mis-spelled real estate data, or just simply to get rid of their dependency on Google, Yahoo, MapQuest, ESRI and other online or pricey geocoding tools. So I thought I'd take this opportunity to supplement our old-fashioned debugging with plpgsqldebugger goodness. In this article, we'll show you how to configure the plpgsql debugger integrated in PgAdmin and run with it.
The pgAdmin plpgsql debugger has existed since PgAdmin 1.8 and PostgreSQL 8.3, so you need at least that version of PgAdmin and PostgreSQL to use it. You also need to run it under a super user account. So that unfortunately rules it out as an option for many use-cases. After all those conditions are met it takes a couple more steps to run with it. The libraries needed for it come prepackaged with EnterpriseDb packaged Windows/Linux/Mac OSX one click PostgreSQL installs and binaries. Not sure if it comes packaged with others or not. I tend to use it just in development mode so don't have installed on any of our production servers.
shared_preload_libraries = '$libdir/plugins/plugin_debugger.dll'
The profile one is optional. Note: the above is for windows server. If you are on Linux it would be:
shared_preload_libraries = '$libdir/plugins/plugin_debugger.so'
Now we are ready to take the debugger for a test drive.
If you have installed the debugger correctly, when you open up PgAdmin III and navigate to the database you installed the debugger module and right-mouse click a plpgsql function, you should see a new Debug option with Debug and Set breakpoint sub-options as shown .
It's your basic debug with one panel showing the function code, allowing you to step thru and toggle breakpoints, a local variables panel showing you variables in function and highlighting when they change, a stack pane to monitor the calls, and a not too interesting Parameters pane to show you waht you type in for the function. It's a bit more interesting when you step thru other functions being called.
A DBMS Messages output pane becomes visible too if you happen to have notices in your function.
The Stack Pane shows what functions you are currently running, useful if your function calls other functions. If you do a step thru, you will also end up stepping into the other functions.
The Local Variables Pane changes as you step thru and the most recent change is marked in red:
Once the function is done running, the Output Pane shows the result.