67

I refer this to enable the debugger in the PostgreSQL server in order to debugging the plpgsql function by stepping through the code using 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.

enter image description here

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.

enter image description here

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

starball
  • 20,030
  • 7
  • 43
  • 238
Ken Chan
  • 84,777
  • 26
  • 143
  • 172
  • 9
    +1 good question. I don't know why it fails, though. If nothing turns up, you can always send your question to the pgAdmin support list: pgadmin-support@postgresql.org (include a link to your question here). See the [list archive here](http://archives.postgresql.org/pgadmin-support/2011-10/threads.php). – Erwin Brandstetter Oct 23 '11 at 11:02
  • Thanks Erwin for the link , I have already sent the questions to the pgAdmin support – Ken Chan Oct 24 '11 at 04:38
  • 5
    Answer !!! http://archives.postgresql.org/pgadmin-support/2011-10/msg00052.php – bilash.saha Nov 02 '11 at 15:34
  • And you are running as super user? According to the site you refer to one needs to be super user in order for it to work. – John P Nov 12 '11 at 09:11
  • Yes , I am the super user account . The same problem exists even I use the `postgres` account that is created by default – Ken Chan Nov 12 '11 at 09:40
  • What platform are you running on? Looks like a flavor of Linux? – leanne Dec 21 '11 at 19:32
  • Duh, Windows... Okay, so here's another probably duh question: did you run the pldbgapi.sql script _before_ or _after_ restarting the server? If it was before, you could try restarting the server again... – leanne Dec 22 '11 at 03:30
  • Er, re-running the pldbgapi.sql script (not restarting the server - the script is supposed to be run _after_ the server restart). – leanne Dec 22 '11 at 03:38
  • @leanne : Here is the flow I try : 1 . Install a new PostgreSQL 8.3 server on the window platform . 2. After installation , PostgreSQL starts automatically . 3. Change the configuration of PostgreSQL to enable `plugin_debugger.dll` plug-in .4 Restart the server and connect to the server using pgAdmin to run `pldbgapi.sql` .5.Restart the server . So , the `pldbgapi.sql` is already run after the server restart – Ken Chan Dec 22 '11 at 04:31
  • Strange.. I Tried it as well and I faced the same problem. I think it's a bug. – Wouter Dorgelo Dec 26 '11 at 21:58
  • I know this is obvious, but have you set breakpoints? Can you check if your function runs (by logging, or inserting something in a table)? – alex Dec 30 '11 at 10:25
  • To @Alex and Mr. Pallazzo : The functions can run properly . And for the `Set breakpoints` options, it open a dialog saying that `Waiting for another session to invoke the add() function`. But of course , it has no respond though I invoke add() function in other transactions . I think it may due to `plugin_debugger.dll` has some problems to listen to the incoming debugging request. I tried again to enable the debugger in other window machine and it can work. So I think I setup the debugger correctly . – Ken Chan Dec 30 '11 at 12:25
  • @KenChan If you figured out how to do the setup correctly, answer your question and mark it as answered. – Mansuro Jan 02 '12 at 09:02
  • pldbgapi.sql script can be found in share\extension\pldbgapi--1.0.sql path. – azhidkov Jul 30 '15 at 07:56
  • please see suggestion for install it on linux in [this](http://stackoverflow.com/questions/18534998/where-can-i-get-pldbgapi-sql-in-order-to-install-the-debugger-for-postgresql/42045979#42045979) question – shcherbak Feb 04 '17 at 22:06

3 Answers3

8

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.
Community
  • 1
  • 1
Joel B
  • 12,082
  • 10
  • 61
  • 69
2

Ken,

Have you tried pgAdmin 1.8 to rule out issue with PgAdmin 1.14/ PostgreSQL 8.3 interaction. It's been a while since I've used 8.3 and for the article I wrote -- which you are referring to, I was testing with 1.14/ PostgreSQL 9.1 so it could very well be an issue with the interaction with older version. Unfortunately I don't have a 8.3 anymore to test with.

I vaguely remember having the issue you had once, but it was when I had another shared library in my postgresql.conf in addition to the pldebugger. Can't remember which one that was, but removing the other shared library fixed my issue.

Hope some of these suggestions help, Regina

LR1234567
  • 1,216
  • 10
  • 16
  • Thanks Regina . I tried to use pgAdmin 1.14 , 1.12 and 1.8 with the PostgreSQL server 8.13.16 , and the problem still exists . But in my office 's PC , I tried to use pgAdmin 1.12 with the PostgreSQL server 8.13.5 ,and it works very well. I will try to use the latest version of PostgreSQL 9.1 if I am free – Ken Chan Jan 02 '12 at 19:54
  • I wonder if it's some sort of antivirus thing blocking it then. – LR1234567 Jan 09 '12 at 08:23
  • Thanks Regina for the suggestion . But I disabled the antivirus (which is Avira AntiVir) and the window firewall , but it still does not work. – Ken Chan Jan 10 '12 at 11:24
1

I had the same problem. Make sure the only shared lib you´re loading in the postgres.conf is the debugger. Nothing else. Not even the profiler. If you get a SSL error when trying to debug a function, reconnect to the server.

foo
  • 11
  • 1