In 2011 I wrote a series of articles on PostgreSQL PL/Java. The basic information is still solid but there is a now a much easier way to install PL/Java from source. This also eliminates the need to depend on third parties to create packages. These notes will be fairly brief since I assume my readers are already familiar with git and maven.
(Note: I’ve passed this information to the PL/Java team so it may already be handled by the time you read this.)
Perform the basic build
- Clone the PL/Java repository at https://github.com/tada/pljava.
- Run maven not make.
- …
- Profit!
Of course it’s not that simple. Maven can pull in its own dependencies but we still need several specialized libraries beyond the standard GNU toolchain. On my Ubuntu system I needed:
- postgresql-server-dev-9.4
- libpg-dev
- libpgtypes3
- libecpg-dev
(I don’t know the corresponding package names for RedHat/Fedora/CentOS.)
It may take a bit of experimentation but it shouldn’t be too hard to identify all of the packages you need. Just remember that you’ll usually want the packages with the “-dev” extension.
There are a large number of compiler warnings and errors but most if not all seem to be related to sign conversions. This warrants further investigation – sign conversion warnings indicate possible attack surfaces by malicious users – but for now we should be fine as long as maven succeeds. We need three files:
./src/sql/install.sql ./pljava/target/pljava-0.0.2-SNAPSHOT.jar ./pljava-so/target/nar/pljava-so-0.0.2-SNAPSHOT-i386-Linux-gpp-shared/lib/i386-Linux-gpp/shared/libpljava-so-0.0.2-SNAPSHOT.so
Copying the files
We can now copy the three files to their respective locations.
$ sudo cp ./pljava-so/target/nar/pljava-so-0.0.2-SNAPSHOT-i386-Linux-gpp-shared/lib/i386-Linux-gpp/shared/libpljava-so-0.0.2-SNAPSHOT.so \ /usr/lib/postgresql/9.4/lib/pljava.so $ sudo cp ./pljava/target/pljava-0.0.2-SNAPSHOT.jar /usr/share/postgresql/9.4/extension/pljava--1.4.4.jar $ sudo cp ./src/sql/install.sql /usr/share/postgresql/9.4/extension/pljava--1.4.4.sql
We can learn the correct target directory with the ‘pg_config’ command.
$ pg_config PKGLIBDIR = /usr/lib/postgresql/9.4/lib SHAREDIR = /usr/share/postgresql/9.4 ...
I have changed the version from 0.0.2-SNAPSHOT to 1.4.4 since we want to capture the PL/Java version, not the pom.xml version. I hope these will soon be kept in sync.
Editing pljava–1.4.4.sql
We need to add two lines to the installation sql:
SET PLJAVA.CLASSPATH='/usr/share/postgresql/9.4/extension/pljava--1.4.4.jar'; SET PLJAVA.VMOPTIONS='-Xms64M -Xmx128M';
It’s important to remember that there is a unique JVM instantiated for each database connection. Memory consumption can become a major concern when you have 20+ simultaneous connections.
Create the pljava.control file
We must tell PostgreSQL about the new extension. This is handled by a control file.
/usr/share/postgresql/9.4/extension/pljava.control
# pljava extension comment = 'PL/Java bundled as an extension' default_version = '1.4.4' relocatable = false
Make libjvm.so visible
We normally specify the location of the java binaries and shared libraries via the JAVA_HOME environment variable. This isn’t an option with the database server.
There are two approaches depending on whether you want to make the java shared library (libjvm.so) visible to all applications or just the database server. I think the former is easiest.
We need to create a single file
/etc/ld.so.conf.d/i386-linux-java.conf
/usr/lib/jvm/java-8-openjdk-i386/jre/lib/i386/server
where most of the pathname comes from JAVA_HOME. The location may be different on your system. The directory must contain the shared library ‘libjvm.so’.
We must also tell the system to refresh its cache.
$ sudo ldconfig $ sudo ldconfig -p | grep jvm libjvm.so (libc6) => /usr/lib/jvm/java-8-openjdk-i386/jre/lib/i386/server/libjvm.so libjsig.so (libc6) => /usr/lib/jvm/java-8-openjdk-i386/jre/lib/i386/server/libjsig.so
Loading the extension
We can now easily load and unload PL/Java.
=> CREATE EXTENSION pljava; CREATE EXTENSION => DROP EXTENSION pljava; DROP EXTENSION
In case of flakiness…
If the system seems flaky you can move the two ‘set’ commands into the postgresql.conf file.
/etc/postgresql/9.4/main/postgresql.conf
#------------------------------------------------------------------------------ # CUSTOMIZED OPTIONS #------------------------------------------------------------------------------ # Add settings for extensions here PLJAVA.CLASSPATH='/usr/share/postgresql/9.4/extension/pljava--1.4.4.jar' PLJAVA.VMOPTIONS='-Xms64M -Xmx128M'
| Reference: | Installing PostgreSQL PL/Java as a PostgreSQL Extension from our JCG partner Bear Giles at the Invariant Properties blog. |
What’s the point of installing an extension that will spawn a JVM for each connection? I think it would add a considerable overhead both in memory and in execution time…
I’m a java developer and I love it, but I would always stick to standard SQL procedures
That’s a good question. The cost of spawning a JVM once the shared library has been loaded is actually pretty low. Where we get clobbered is loading large libraries that make heavy use of reflection. The sweet spot is something complex enough that you can’t just write it in plpgsql or one of the other scripting languages but not so complex that you’re pulling in large libraries. The other thing to remember is that most applications will (I hope) be using connection pooling. That spreads the cost of the JVM startup across multiple queries albeit at a small risk that… Read more »
Ok I got it, but I wouldn’t sleep well fearing that the database server could use so much memory just for running code; if you use connection pooling I guess you could have tens of active JVM at each time and this seems much, unless your JVM do really trivial tasks.
Thanks for your article and reply anyway.