PostgreSQL Debug Function Using pgAdmin

Question

I am sending this to enable the debugger on the PostgreSQL server to debug the plpgsql function by going through the codes using pgadmin.

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

These steps must be completed successfully, and plugin_debugger.dll must be loaded successfully, as can be verified using the show shared_preload_libraries command, and I can see the debugging option in the context menu by right-clicking on the function in pgAdmin.

When you select "Debug" → "Debug" a window appears that allows me to enter values ​​for the input parameters. But after that, when I click OK , it does not respond at all.

Any ideas or I missed something in setting up a debugger on the server

I am using PostgreSQL 8.3 and pgAdmin 1.14

Answer 1

You must enable debugging in two places. In PGAdmin and the database itself. This article that you referred to does a great job of this, but there are some nuances.

PGAdmin

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

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

And the path to plugin_debugger.dll was actually

 $libdir/plugin_debugger.dll 

not

 $libdir/plugins/plugin_debugger.dll 

as stated in the article. So your postgresql.conf needs a line like this

 shared_preload_libraries = '$libdir/plugin_debugger.dll' 

Find the actual .dll if in doubt. If you are on Linux, the file you are looking for is plugin_debugger.so . Remember that changing the postgresql.conf file will require a reboot for the changes to take effect.

PostgreSQL Database

Assuming you are using your PostgreSQL database on a Linux server, this method does an excellent job of explaining how to load dependencies to enable debugging. Make sure you use root during installation.

The easy-to-skip part issues a command against the actual database that you want to debug. For newer versions on PostgreSQL, all you need to do is the following:

 CREATE EXTENSION IF NOT EXISTS pldbgapi; 

If this does not return an error, you should be good to go.

Some noteworthy notes:

• As mentioned above, you can only debug when working as a superuser account.
• From your documents, you can only debug pl / pgsql functions. Therefore, if your function says something like LANGUAGE c PGAdmin, it will not even display the Debug option with the right mouse button when selecting a function. Find something that has LANGUAGE plpgsql and the Debug menu should show.

Answer 2

Ken

You tried pgAdmin 1.8 to eliminate the problem with PgAdmin 1.14 / PostgreSQL 8.3 interaction. It has been some time since I used 8.3 for the article I wrote - about which you are talking about, I tested with 1.14 / PostgreSQL 9.1, so there may well be a problem with interacting with the old version. Unfortunately, I no longer have 8.3 test.

I vaguely remember that you had a question that you had once, but that was when I had another shared library in my postgresql.conf in addition to pldebugger. I don’t remember which one was, but deleting another shared library fixed my problem.

Hope some of these suggestions help, Regina

Answer 3

I had the same problem. Make sure that only the generic lib that you load in postgres.conf is a debugger. Nothing more. Even the profiler. If you get an SSL error while trying to debug a function, connect to the server.