Home » Java » Enterprise Java » Installing PostgreSQL PL/Java as a PostgreSQL Extension

About Bear Giles

Installing PostgreSQL PL/Java as a PostgreSQL Extension

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. 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'

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

I have read and agree to the terms & conditions

 

Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

3 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Riccardo
Riccardo
6 years ago

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

Bear Giles
Bear Giles
6 years ago

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 »

Riccardo
Riccardo
6 years ago
Reply to  Bear Giles

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.