tada
Debugging "My" Pl/Java Code
I don't know where else to stick this so that it can be found by others that may need it, so I'm sticking it here. My Pl/Java code wasn't working (big surprise of course). So I wanted to do some remote debugging from Eclipse. It involved 2 steps. First though, this is using :
Eclipse IDE for Java Developers
Version: Neon.3 Release (4.6.3)
Build id: 20170314-1500
PostgresSQL
d:\pljava>psql --version
psql (PostgreSQL) 9.5.7
and
d:\pljava>java -version
java version "1.8.0_131"
Java(TM) SE Runtime Environment (build 1.8.0_131-b11)
Java HotSpot(TM) 64-Bit Server VM (build 25.131-b11, mixed mode)
In postgresql.conf in D:\data\pg95\ directory (my Postgres data directory) at the bottom add:
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
# Add settings for extensions here
# Turn on Elcipse Debugging of the Java code:
pljava.vmoptions='-agentlib:jdwp=transport=dt_socket,server=y,suspend=n,address=8000'
# end debug options
in Eclipse create a remote debugging profile:
Choose the project to Debug,
Connection Type is "Standard (Socket Attach)"
Connection Properties
Host: localhost
Port: 8000
In my case, it is a local copy of Postgres - since this is debugging inside the main Postgres process, please realize that if you set this up on a shared development database, Postgres ONLY does the debugging and all other processing is suspended while the debug session is in process.
Finally, the firewall has to allow the connection. In my case it was "Windows Advanced Firewall" but your firewall may be different. If the connection keeps getting refused, this may be the problem.
Please note that this is the "current" at the time of this writing for how to specify the debugging option for Java 8. It was different for prior versions of Java (it changed around Java 5 or 6).
It took me 2 days of Googling to put this all together and I've lost all the various references so I would like to thank those that I found bits of information from and Thank You, all of you.
I hope this will help save someone else all the time it took me to get this working.
Tom
Thanks for the report. I'm thinking the best place I should add this info is right in the Debugging your Java code wiki page, which has jdb and dbx info so far, but not Eclipse.
My suggestion would be to leave postgresql.conf alone, and just do
SET pljava.vmoptions TO '-agentlib:jdwp=transport=dt_socket,server=y,suspend=n,address=8000';
in the session you want to debug, before it does anything with PL/Java. That saves you having to restart or SIGHUP the server, and only affects the session you are debugging.
please realize that if you set this up on a shared development database, Postgres ONLY does the debugging and all other processing is suspended while the debug session is in process.
That's another reason to avoid putting debug options into postgresql.conf. Because PostgreSQL has a backend-process-per-session design (and a backend using PL/Java starts its own JVM), if you just SET pljava.vmoptions with debug settings in one session, only that session's JVM will start with debug enabled.
Now, you could still use the debugger to set a breakpoint in your code, and if that's inside a transaction or holding a lock, you could end up blocking other database sessions that way. But if you put the options in postgresql.conf, you're going to have every session that uses PL/Java trying to listen for a debug connection (and if you specifically assign a port number as in your example, they'll all try to listen on that port, which only one will be able to get. And, one that tries to get it and fails will report a JDWP FATAL ERROR that kills its backend, causing the postmaster to kick everyone else out for an instant to recover).
It's also possible to leave off the ,address=8000 and then the system just picks an unused port and tells you the number it picked. Well, it doesn't really tell you, it writes the message to stdout—the backend's stdout, not your client's—so it's not as convenient as it could be to find out the port it picked. You can look in the server log file if you have access.
If you'll be debugging on the same host, it turns out you don't need to find out the port that was picked. Instead of Standard (Socket Attach), there should be a Process Attach option, and you just specify the PID, which you can get with SELECT pg_backend_pid(); in the session you are debugging. I'm not sure exactly how that looks in Eclipse, but it looks like this using jdb:
jdb -attach 39663 # if you found out the port number, which is simply shorthand for
jdb -connect com.sun.jdi.SocketAttach:port=39663
# but if you only know the PID:
jdb -connect com.sun.jdi.ProcessAttach:pid=8887
Ok, maybe this is something jdb can do that Eclipse can't...
Under the hood, the ProcessAttach just examines the process with PID 8887 to find out what port it's using, then does a SocketAttach. That saves you having to dig for the port number yourself, but it only works on the same host. If you want to connect your debugger from another box, you have to know the port.
There's a StackOverflow answer giving sample code to determine the port that was picked if you know the PID (in other words, basically what ProcessAttach is doing under the hood).
One caution is when you run with server=y debugging options, the JVM will listen for a debug connection on any network interface (possibly including from the wild wooly internet). You can also specify address=localhost:8000 to ensure it only listens for connections from the local machine. (Or, address=localhost: to let it choose the port number; notice the colon!)
It would be ideal to combine localhost listening with ProcessAttach, which only works on the local host anyway, but ProcessAttach seems to be too stupid: it examines the process, finds out the port being used, then tries to connect to that port on the network interface instead of localhost. Ooops...
The place to read about all these transport options is here.
Now a question for you: I think I tried this with Eclipse at one time, got it to attach with no problem, but had trouble getting it to find the source files and show source (I had not developed the code inside an Eclipse project). Have you found the secret to that?
I seem to have answered my own question. Without opening a "Project", it's still possible to make a remote debugging configuration and use its Sources tab to add a directory from the filesystem, then open the Debug perspective. There's no way to set a breakpoint except on a line in the editor, and File, Open can also open that directly from the filesystem to get things started.
