Invariant Properties

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

1. Clone the PL/Java repository at https://github.com/tada/pljava.
2. Run maven not make.
3. …
4. 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'