Install PL/JAVA in PostgreSQL 9.5.1

Some time ago I blogged about how to install PL/JAVA in Postgres Plus Advanced Server 9.5. This resulted in an interesting comment from the maintainer of PL/Java. After exchanging some emails and some more testing it turned out that there really is an issue with my build of PostgreSQL. I usually compile PostgreSQL from source and use the “–with-extra-version” flag when I do the “./configure” and that seems to be an issue (at least in 1.5.0-BETA3). So, lets try to install PL/JAVA into a PostgreSQL 9.5.1 instance which was not compiled with the “–with-extra-version” option.

If you take a look at the build instructions for PL/JAVA there are a few requirements before we can start:

• We need to be able to compile and link C code
• We need a java compiler
• We need pg_config. This is available by default once PostgreSQL is installed.
• We need Maven for building PL/JAVA

As I am on a RedHat based distribution installing the C and Java compiler is just a matter of using yum:

[[email protected] ~]$ yum install gcc-c++ gcc java-1.8.0-openjdk-devel

Once these are installed the first two requirements are met:

[email protected]:/home/postgres/ [PG11] g++ -version
g++: error: unrecognized command line option ‘-version’
g++: fatal error: no input files
compilation terminated.
[email protected]:/home/postgres/ [PG11] g++ --version
g++ (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4)
Copyright (C) 2015 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

[email protected]:/home/postgres/ [PG11] javac -version
javac 1.8.0_71

For pg_config I do not need to do anything as my PostgreSQL environment is already set:

[email protected]:/home/postgres/ [PG11] which pg_config
/u01/app/postgres/product/95/db_1/bin/pg_config
[email protected]:/home/postgres/ [PG11] pg_config
BINDIR = /u01/app/postgres/product/95/db_1/bin
DOCDIR = /u01/app/postgres/product/95/db_1/doc/postgresql
HTMLDIR = /u01/app/postgres/product/95/db_1/doc/postgresql
INCLUDEDIR = /u01/app/postgres/product/95/db_1/include
PKGINCLUDEDIR = /u01/app/postgres/product/95/db_1/include/postgresql
INCLUDEDIR-SERVER = /u01/app/postgres/product/95/db_1/include/postgresql/server
LIBDIR = /u01/app/postgres/product/95/db_1/lib
PKGLIBDIR = /u01/app/postgres/product/95/db_1/lib/postgresql
LOCALEDIR = /u01/app/postgres/product/95/db_1/share/locale
MANDIR = /u01/app/postgres/product/95/db_1/share/man
SHAREDIR = /u01/app/postgres/product/95/db_1/share/postgresql
SYSCONFDIR = /u01/app/postgres/product/95/db_1/etc/postgresql
PGXS = /u01/app/postgres/product/95/db_1/lib/postgresql/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--with-libs=/opt/local/Current/lib' '--with-includes=/opt/local/Current/include/libxml2:/opt/local/Current/include' '--prefix=/mnt/hgfs/pginstaller.auto/server/staging/linux-x64' '--with-ldap' '--with-openssl' '--with-perl' '--with-python' '--with-tcl' '--with-tclconfig=/opt/local/EnterpriseDB/LanguagePack/9.5/Tcl-8.5/lib' '--with-pam' '--enable-thread-safety' '--with-libxml' '--with-ossp-uuid' '--docdir=/mnt/hgfs/pginstaller.auto/server/staging/linux-x64/doc/postgresql' '--with-libxslt' '--with-libedit-preferred' '--with-gssapi' 'LD_LIBRARY_PATH=/opt/local/Current/lib' 'CFLAGS=-DMAP_HUGETLB=0x40000'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE -I/opt/local/Current/include/libxml2 -I/opt/local/Current/include
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -DMAP_HUGETLB=0x40000
CFLAGS_SL = -fpic
LDFLAGS = -L../../../src/common -L/opt/local/Current/lib -Wl,--as-needed -Wl,-rpath,'/mnt/hgfs/pginstaller.auto/server/staging/linux-x64/lib',--enable-new-dtags
LDFLAGS_EX = 
LDFLAGS_SL = 
LIBS = -lpgcommon -lpgport -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -ledit -lrt -lcrypt -ldl -lm 
VERSION = PostgreSQL 9.5.1

The last requirement is Maven and this is trivial to install, too:

[email protected]:/home/postgres/ [PG11] mkdir -p /u01/app/postgres/product/maven
[email protected]:/home/postgres/ [PG11] cd /u01/app/postgres/product/maven
[email protected]:/u01/app/postgres/product/maven/ [PG11] wget http://mirror.easyname.ch/apache/maven/maven-3/3.3.9/binaries/apache-maven-3.3.9-bin.tar.gz
[email protected]:/u01/app/postgres/product/maven/ [PG11] tar -axf apache-maven-3.3.9-bin.tar.gz 
[email protected]:/u01/app/postgres/product/maven/ [PG11] cd apache-maven-3.3.9
[email protected]:/u01/app/postgres/product/maven/apache-maven-3.3.9/ [PG11] bin/mvn -version
Apache Maven 3.3.9 (bb52d8502b132ec0a5a3f4c09453c07478323dc5; 2015-11-10T17:41:47+01:00)
Maven home: /u01/app/postgres/product/maven/apache-maven-3.3.9
Java version: 1.8.0_71, vendor: Oracle Corporation
Java home: /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.71-2.b15.el7_2.x86_64/jre
Default locale: en_US, platform encoding: UTF-8
OS name: "linux", version: "3.8.13-118.4.1.el7uek.x86_64", arch: "amd64", family: "unix"

That’s it. Maven is ready and we can start with building PL/JAVA ( Release 1.5.0-BETA3 for the scope of this post ):

[email protected]:/u01/app/postgres/product/maven/apache-maven-3.3.9/ [PG11] mkdir -p /u01/app/postgres/product/pljava
[email protected]:/u01/app/postgres/product/maven/apache-maven-3.3.9/ [PG11] cd /u01/app/postgres/product/pljava
[email protected]:/u01/app/postgres/product/pljava/ [PG11] wget https://github.com/tada/pljava/archive/V1_5_0b3.tar.gz
[email protected]:/u01/app/postgres/product/pljava/ [PG11] tar -axf V1_5_0b3.tar.gz 
[email protected]:/u01/app/postgres/product/pljava/ [PG11] cd pljava-1_5_0b3/
[email protected]:/u01/app/postgres/product/pljava/pljava-1_5_0b3/ [PG11] export PATH=/u01/app/postgres/product/maven/apache-maven-3.3.9/bin/:$PATH
[email protected]:/u01/app/postgres/product/pljava/pljava-1_5_0b3/ [PG11] mvn clean install

The last command produces plenty of output. The important notes are at the end:

[INFO] ------------------------------------------------------------------------
[INFO] Reactor Summary:
[INFO] 
[INFO] PostgreSQL PL/Java ................................. SUCCESS [  6.739 s]
[INFO] PL/Java API ........................................ SUCCESS [  5.920 s]
[INFO] PL/Java backend Java code .......................... SUCCESS [  3.444 s]
[INFO] PL/Java backend native code ........................ SUCCESS [ 46.330 s]
[INFO] PL/Java Deploy ..................................... SUCCESS [  3.397 s]
[INFO] PL/Java Ant tasks .................................. SUCCESS [  1.346 s]
[INFO] PL/Java examples ................................... SUCCESS [  3.731 s]
[INFO] PL/Java packaging .................................. SUCCESS [  7.674 s]
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 01:21 min
[INFO] Finished at: 2016-03-23T10:25:14+01:00
[INFO] Final Memory: 38M/93M
[INFO] ------------------------------------------------------------------------

Looks fine so far. Following the installation instructions to install PL/JAVA:

[email protected]:/u01/app/postgres/product/pljava/pljava-1_5_0b3/ [PG11] java -jar pljava-packaging/target/pljava-pg9.5-amd64-Linux-gpp.jar
/u01/app/postgres/product/95/db_1/lib/postgresql/libpljava-so-1.5.0-BETA3.so as bytes
/u01/app/postgres/product/95/db_1/share/postgresql/pljava/pljava-1.5.0-BETA3.jar as bytes
/u01/app/postgres/product/95/db_1/share/postgresql/pljava/pljava-api-1.5.0-BETA3.jar as bytes
/u01/app/postgres/product/95/db_1/share/postgresql/pljava/pljava-examples-1.5.0-BETA3.jar as bytes
/u01/app/postgres/product/95/db_1/share/postgresql/extension/pljava.control as lines (ASCII)
/u01/app/postgres/product/95/db_1/share/postgresql/pljava/pljava--1.5.0-BETA3.sql as lines (UTF8)
/u01/app/postgres/product/95/db_1/share/postgresql/pljava/pljava--unpackaged--1.5.0-BETA3.sql as lines (UTF8)
/u01/app/postgres/product/95/db_1/share/postgresql/pljava/pljava--1.5.0-BETA2--1.5.0-BETA3.sql as lines (UTF8)
/u01/app/postgres/product/95/db_1/share/postgresql/pljava/pljava--1.5.0-BETA1--1.5.0-BETA3.sql as lines (UTF8)

Looks fine, too. Everything needed by PostgreSQL to install the extension should be there now. Lets try:

[email protected]:/u01/app/postgres/product/pljava/pljava-1_5_0b3/ [PG11] psql postgres
Null display is "NULL".
Timing is on.
psql.bin (9.5.1)
Type "help" for help.

([email protected][local]:7777) [postgres] > create extension pljava;
CREATE EXTENSION
Time: 1947.125 ms
([email protected][local]:7777) [postgres] > \dx
                                   List of installed extensions
  Name   |   Version   |   Schema   |                         Description                          
---------+-------------+------------+--------------------------------------------------------------
 pljava  | 1.5.0-BETA3 | sqlj       | PL/Java procedural language (https://tada.github.io/pljava/)
 plpgsql | 1.0         | pg_catalog | PL/pgSQL procedural language
(2 rows)

Ready to be used …