PostGIS in Action
About the Authors
Consulting
Monday, June 27. 2011Using PgAdmin PLPgSQL DebuggerPrinter FriendlyRecommended Books: PostGIS in Action PostgreSQL 9 Admin Cookbook / High Performance 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. Installing and configuring the PgAdmin PL/PgSQL debuggerThe 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.
Now we are ready to take the debugger for a test drive. Using the plpgsql debugger
Comments
Display comments as
(Linear | Threaded)
Someone tried your tutorial, and he had an issue http://stackoverflow.com/questions/7865615/debug-postgresql-function-using-pgadmin do you have any idea what might be the reason?
Not off hand. I was using PostgreSQL 9.1 so could be a bug in 8.3/PgAdmin 1.14 interaction. He might want to try PgAdmin 1.8 to rule that out as an issue.
Hi Regina,
I installed PostgreSQL 9.2 onto a Windows server 2008 (x64). I made the modification in the postgres.conf like this: shared_preload_libraries = 'C:\Program Files (x86)\PostgreSQL\9.2\lib\plugin_debugger.dll'; I restarted the service postgres_9.2 who is assigned to postgres user; Into a database I used the shell to crete extension pldbgapi with this command: create extension pldbgapi; I had the confirmation the extension was created; I verified in the pgAdminIII the existence of the extension pldbgapi; The user postgres was created manually before installing Postgres and it hasn't specials privileges; I verified in Tools\Server configuration that shared_preload_libraries is checked; But the debugger is still not activated. Can you give me a hint for this problem, please. PS Before, I tried to install x64 of Postgres 9.2 but I was unable to restart the server because, pg_ctl.exe has 0 bytes. Thank you, Ion
I presume you are running the 32-bit version of PostgreSQL since you are referencing a 32-bit version?
You shouldn't need all that path stuff in shared_preload_libraries, just the dll name should be sufficient since its going to look in lib of the postgresql instance. If for some reason you need the full path, change the slashes from \ to / . PostgreSQL never seems to work right in a lot of places with windows style slashes
Hi everybody, I am trying to debug postgre SQL function but I cant seem to find a way how to test a function that takes a composite type as an argument. My usual call of this function is like select function(ROW(0,null,'xyz')); this works OK but how do I enter this variable into debugger? It always fail to debug the function because it places it into '' and adds cast to the end of it.
What does this step entail?
"Install the file pldbgapi.sql in the database you want to debug plpgsql functions in." I have 9.2 for Win7 x-64. There is a file called pldbgapi--1.0.sql in the \9.2\share\extension folder but I'm not sure what to do with it -- where to put it, if it has to be moved, and whether it needs to be renamed to get rid of the dashes.
Tim,
If you are on 9.1 or 9.2 use the below SQL command in your database. CREATE EXTENSION pldbgapi; The 9.1 and 9.2 use the new PostgreSQL extension command instead of having to manually execute scripts.
I can't seem to find much on installing the debugger on linux. CREATE EXTENSION pldbgapi yields => ERROR: could not open extension file "/usr/pgsql-9.1/share/extension/pldbgapi.control": No such file or directory
It's not packaged with PostgreSQL source. The EnterpriseDb Windows and Linux distributions carry it. For using with other Linux distros, you'll probably need to get the source and compile yourself.
Source download is on git: http://git.postgresql.org/gitweb/?p=pldebugger.git;a=summary You'll want to make sure you installed the postgresql devel (which goes by different names depending on what Linux PostgreSQL distribution you are using) |
Entry's LinksQuicksearchCalendar
Categories
SubscribeBlog Administration |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||
.
.
Tracked: Oct 06, 16:54
Tracked: Oct 21, 08:02