Debug PostgreSQL function using pgAdmin

Question

I refer this to enable the debugger in the PostgreSQL server in order to debugging the plpgsql function by stepping through the codes using the pgadmin.

I have already set shared_preload_libraries = '$libdir/plugins/plugin_debugger.dll' in the postgresql.conf, run pldbgapi.sql , and restarted the server.

These steps should have been run successfully and plugin_debugger.dll should be loaded successfully as can be verified by using the command show shared_preload_libraries, and I can see the debugging option in the context menu with a right click on a function in pgAdmin.

When choosing "Debugging" --> Debug, a window pops up to allow me to enter the values for the input parameters. But after that, when I press OK, it does not respond at all.

Any ideas or did I miss something in the setup of the debugger on the server??

I am using PostgreSQL 8.3 and pgAdmin 1.14

Answer 1

You have to enable debugging in two places. On PGAdmin and on the database itself. That article you referenced does a wonderful job explaining it but there were some nuances.

PGAdmin

When updating your postgresql.conf file to load the debugging library, I was running PGAdmin on Windows so the file was here:

C:\Program Files\PostgreSQL\9.4\data\postgresql.conf 

And the path to the plugin_debugger.dll was actually

$libdir/plugin_debugger.dll 

not

$libdir/plugins/plugin_debugger.dll 

as specified in the article. So your postgresql.conf would need a line like this

shared_preload_libraries = '$libdir/plugin_debugger.dll' 

Search for the actual .dll if you're in doubt. If you're on Linux the file you'll be looking for is plugin_debugger.so. Don't forget that changing the postgresql.conf file will require a restart for the change to take effect.

PostgreSQL Database

Assuming you're running your PostgreSQL database on a Linux server this gist does an excellent job at explaining how to download the dependencies for enabling debugging. Make sure you're running as root when you install.

The part that's easy to miss is issuing the command against the actual database you want to debug. For newer versions on PostgreSQL all you'll need to do is this:

CREATE EXTENSION IF NOT EXISTS pldbgapi; 

If that doesn't return an error, you should be good to go.

Some additional things of note:

• As mentioned above, you can only debug when running as a super user account
• From their docs you can only debug pl/pgsql functions. So if your function says something like LANGUAGE c PGAdmin won't even show a right-click Debug menu option when you select the function. Look for something that has LANGUAGE plpgsql and the Debug menu should show.