Invariant Properties

  • rss
  • Home

Installing PostgreSQL PL/Java as a PostgreSQL Extension.

Bear Giles | August 8, 2015

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:

  1. ./src/sql/install.sql
  2. ./pljava/target/pljava-0.0.2-SNAPSHOT.jar
  3. ./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
./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.

  1. $ 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 \
  2.   /usr/lib/postgresql/9.4/lib/pljava.so
  3.  
  4. $ sudo cp ./pljava/target/pljava-0.0.2-SNAPSHOT.jar /usr/share/postgresql/9.4/extension/pljava--1.4.4.jar
  5.  
  6. $ sudo cp ./src/sql/install.sql /usr/share/postgresql/9.4/extension/pljava--1.4.4.sql
$ 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.

  1. $ pg_config
  2. PKGLIBDIR = /usr/lib/postgresql/9.4/lib
  3. SHAREDIR = /usr/share/postgresql/9.4
  4. ...
$ 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:

  1. SET PLJAVA.CLASSPATH='/usr/share/postgresql/9.4/extension/pljava--1.4.4.jar';
  2. SET PLJAVA.VMOPTIONS='-Xms64M -Xmx128M';
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

  1. # pljava extension
  2. comment = 'PL/Java bundled as an extension'
  3. default_version = '1.4.4'
  4. relocatable = false
# 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

  1. /usr/lib/jvm/java-8-openjdk-i386/jre/lib/i386/server
/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.

  1. $ sudo ldconfig
  2. $ sudo ldconfig -p | grep jvm
  3.     libjvm.so (libc6) => /usr/lib/jvm/java-8-openjdk-i386/jre/lib/i386/server/libjvm.so
  4.     libjsig.so (libc6) => /usr/lib/jvm/java-8-openjdk-i386/jre/lib/i386/server/libjsig.so
$ 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.

  1. => CREATE EXTENSION pljava;
  2. CREATE EXTENSION
  3.  
  4. => DROP EXTENSION pljava;
  5. DROP EXTENSION
=> 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

  1. #------------------------------------------------------------------------------
  2. # CUSTOMIZED OPTIONS
  3. #------------------------------------------------------------------------------
  4.  
  5. # Add settings for extensions here
  6.  
  7. PLJAVA.CLASSPATH='/usr/share/postgresql/9.4/extension/pljava--1.4.4.jar'
  8. PLJAVA.VMOPTIONS='-Xms64M -Xmx128M'
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

# Add settings for extensions here

PLJAVA.CLASSPATH='/usr/share/postgresql/9.4/extension/pljava--1.4.4.jar'
PLJAVA.VMOPTIONS='-Xms64M -Xmx128M'
Categories
java, pl/java, PostgreSQL
Comments rss
Comments rss
Trackback
Trackback

« Extending PostgreSQL: Complex Number Data Type Adding Database Logging to JUnit3 »

Leave a Reply

Click here to cancel reply.

You must be logged in to post a comment.

Archives

  • May 2020 (1)
  • March 2019 (1)
  • August 2018 (1)
  • May 2018 (1)
  • February 2018 (1)
  • November 2017 (4)
  • January 2017 (3)
  • June 2016 (1)
  • May 2016 (1)
  • April 2016 (2)
  • March 2016 (1)
  • February 2016 (3)
  • January 2016 (6)
  • December 2015 (2)
  • November 2015 (3)
  • October 2015 (2)
  • August 2015 (4)
  • July 2015 (2)
  • June 2015 (2)
  • January 2015 (1)
  • December 2014 (6)
  • October 2014 (1)
  • September 2014 (2)
  • August 2014 (1)
  • July 2014 (1)
  • June 2014 (2)
  • May 2014 (2)
  • April 2014 (1)
  • March 2014 (1)
  • February 2014 (3)
  • January 2014 (6)
  • December 2013 (13)
  • November 2013 (6)
  • October 2013 (3)
  • September 2013 (2)
  • August 2013 (5)
  • June 2013 (1)
  • May 2013 (2)
  • March 2013 (1)
  • November 2012 (1)
  • October 2012 (3)
  • September 2012 (2)
  • May 2012 (6)
  • January 2012 (2)
  • December 2011 (12)
  • July 2011 (1)
  • June 2011 (2)
  • May 2011 (5)
  • April 2011 (6)
  • March 2011 (4)
  • February 2011 (3)
  • October 2010 (6)
  • September 2010 (8)

Recent Posts

  • 8-bit Breadboard Computer: Good Encapsulation!
  • Where are all the posts?
  • Better Ad Blocking Through Pi-Hole and Local Caching
  • The difference between APIs and SPIs
  • Hadoop: User Impersonation with Kerberos Authentication

Meta

  • Log in
  • Entries RSS
  • Comments RSS
  • WordPress.org

Pages

  • About Me
  • Notebook: Common XML Tasks
  • Notebook: Database/Webapp Security
  • Notebook: Development Tips

Syndication

Java Code Geeks

Know Your Rights

Support Bloggers' Rights
Demand Your dotRIGHTS

Security

  • Dark Reading
  • Krebs On Security Krebs On Security
  • Naked Security Naked Security
  • Schneier on Security Schneier on Security
  • TaoSecurity TaoSecurity

Politics

  • ACLU ACLU
  • EFF EFF

News

  • Ars technica Ars technica
  • Kevin Drum at Mother Jones Kevin Drum at Mother Jones
  • Raw Story Raw Story
  • Tech Dirt Tech Dirt
  • Vice Vice

Spam Blocked

53,793 spam blocked by Akismet
rss Comments rss valid xhtml 1.1 design by jide powered by Wordpress get firefox