While doing a project at a customer we came to a point where we needed to install pl/java into Postgres Plus Advanced Server 9.5. If you follow the official documentation you will probably fail and get stuck when trying to build pl/java with maven (at least I did fail several times). Unfortunately there is not much documentation around for solving the issues that popped up. As we have a very good communication channel into EnterpriseDB we finally got the instructions on how to do it the right way:

If you did a standard installation of Postgres Plus Advanced server you should find a pljava.jar file in the “lib” directory of the installation:

[email protected]:/home/enterprisedb/ [PG2] ls /u01/app/postgres/product/95/db_0/9.5AS/lib/pljava.jar 
/u01/app/postgres/product/95/db_0/9.5AS/lib/pljava.jar

The next step is to point the “pljava.classpath” parameter to the location of this jar file:

[email protected]:/home/enterprisedb/ [PG2] sqh
Null display is "NULL".
Timing is on.
psql.bin (9.5.0.5)
Type "help" for help.

([email protected][local]:5445) [postgres] > alter system set pljava.classpath='/u01/app/postgres/product/95/db_0/9.5AS/lib/pljava.jar';
ERROR:  unrecognized configuration parameter "pljava.classpath"
Time: 0.327 ms
([email protected][local]:5445) [postgres] > 

Hm, does not work. So lets add it directly to the postgresql.conf configuration file:

[email protected]:/u02/pgdata/PG2/ [PG2] echo "pljava.classpath='/u01/app/postgres/product/95/db_0/9.5AS/lib/pljava.jar'" >> /path/to/postgresql.conf

When the Postgres Plus Advanced server starts up it needs to know the location of the java libraries so adjusting the LD_LIBRARY_PATH environment variable is a good idea (if you want to do make that persistent add it to the startup script of Postgres Plus Aavanced server):

[email protected]:/u02/pgdata/PG2/ [PG2] export LD_LIBRARY_PATH=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.71-2.b15.el7_2.x86_64/jre/lib/amd64/server/:$LD_LIBRARY_PATH 

Once ready the server can be restarted and the “pljava.classpath” parameter should be set:

[email protected]:/u02/pgdata/PG2/ [PG2] pg_ctl restart -D /u02/pgdata/PG2/ -m fast


([email protected][local]:5445) [postgres] > show pljava.classpath;
                    pljava.classpath                    
--------------------------------------------------------
 /u01/app/postgres/product/95/db_0/9.5AS/lib/pljava.jar
(1 row)

Time: 0.170 ms
([email protected][local]:5445) [postgres] > 

Great. The next step is to execute the installation script:

[email protected][local]:5445) [postgres] > i /u01/app/postgres/product/95/db_0/9.5AS/share/pljava_install.sql                                                          

If everything went fine the output should look like this:

CREATE SCHEMA
Time: 1.375 ms
GRANT
Time: 0.855 ms
CREATE FUNCTION
Time: 304.740 ms
CREATE LANGUAGE
Time: 30.509 ms
CREATE FUNCTION
Time: 0.991 ms
CREATE LANGUAGE
Time: 0.926 ms
CREATE TABLE
Time: 38.192 ms
GRANT
Time: 1.327 ms
CREATE TABLE
Time: 7.942 ms
GRANT
Time: 0.999 ms
ALTER TABLE
Time: 2.919 ms
CREATE TABLE
Time: 3.061 ms
GRANT
Time: 1.004 ms
CREATE TABLE
Time: 4.768 ms
GRANT
Time: 0.978 ms
CREATE FUNCTION
Time: 1.402 ms
CREATE FUNCTION
Time: 20.138 ms
CREATE FUNCTION
Time: 1.386 ms
CREATE FUNCTION
Time: 0.878 ms
CREATE FUNCTION
Time: 0.861 ms
CREATE FUNCTION
Time: 1.353 ms
CREATE FUNCTION
Time: 1.116 ms
CREATE FUNCTION
Time: 1.823 ms
CREATE FUNCTION
Time: 1.160 ms

Looks fine. Lets verify if the language is really registered:

([email protected][local]:5445) [postgres] > select * from pg_language where lanname like 'java%';
 lanname | lanowner | lanispl | lanpltrusted | lanplcallfoid | laninline | lanvalidator | lanacl 
---------+----------+---------+--------------+---------------+-----------+--------------+--------
 java    |       10 | t       | t            |         16656 |         0 |            0 | NULL
 javau   |       10 | t       | f            |         16658 |         0 |            0 | NULL
(2 rows)

Time: 299.411 ms

Unfortunately pl/java is not listed as an extension:

([email protected][local]:5445) [postgres] > dx
                                  List of installed extensions
       Name       | Version |   Schema   |                     Description                      
------------------+---------+------------+------------------------------------------------------
 edb_dblink_libpq | 1.0     | pg_catalog | EnterpriseDB Foreign Data Wrapper for PostgreSQL
 edb_dblink_oci   | 1.0     | pg_catalog | EnterpriseDB Foreign Data Wrapper for Oracle
 edbspl           | 1.0     | pg_catalog | EDB-SPL procedural language
 pldbgapi         | 1.0     | pg_catalog | server-side support for debugging PL/pgSQL functions
 plpgsql          | 1.0     | pg_catalog | PL/pgSQL procedural language
(5 rows)

All the object have been created in a dedicated schema which contains some tables and functions:

([email protected][local]:5445) [postgres] > dn
    List of schemas
  Name  |    Owner     
--------+--------------
 public | enterprisedb
 sqlj   | enterprisedb
(2 rows)

([email protected][local]:5445) [postgres] > set search_path='sqlj';
SET
Time: 0.167 ms

([email protected][local]:5445) [postgres] > d
                      List of relations
 Schema |           Name           |   Type   |    Owner     
--------+--------------------------+----------+--------------
 sqlj   | classpath_entry          | table    | enterprisedb
 sqlj   | jar_entry                | table    | enterprisedb
 sqlj   | jar_entry_entryid_seq    | sequence | enterprisedb
 sqlj   | jar_repository           | table    | enterprisedb
 sqlj   | jar_repository_jarid_seq | sequence | enterprisedb
 sqlj   | typemap_entry            | table    | enterprisedb
 sqlj   | typemap_entry_mapid_seq  | sequence | enterprisedb
(7 rows)

([email protected][local]:5445) [postgres] > df
                                            List of functions
 Schema |        Name        | Result data type  |              Argument data types              |  Type  
--------+--------------------+-------------------+-----------------------------------------------+--------
 sqlj   | add_type_mapping   | void              | character varying, character varying          | normal
 sqlj   | drop_type_mapping  | void              | character varying                             | normal
 sqlj   | get_classpath      | character varying | character varying                             | normal
 sqlj   | install_jar        | void              | bytea, character varying, boolean             | normal
 sqlj   | install_jar        | void              | character varying, character varying, boolean | normal
 sqlj   | java_call_handler  | language_handler  |                                               | normal
 sqlj   | javau_call_handler | language_handler  |                                               | normal
 sqlj   | remove_jar         | void              | character varying, boolean                    | normal
 sqlj   | replace_jar        | void              | bytea, character varying, boolean             | normal
 sqlj   | replace_jar        | void              | character varying, character varying, boolean | normal
 sqlj   | set_classpath      | void              | character varying, character varying          | normal
(11 rows)

Time to see if it is really working and to create a simple java program:

CREATE FUNCTION getsysprop(VARCHAR) RETURNS VARCHAR
  AS 'java.lang.System.getProperty'
  LANGUAGE java;

This should return a result when executed:

([email protected][local]:5445) [postgres] > SELECT getsysprop('user.home');
     getsysprop     
--------------------
 /home/enterprisedb
(1 row)

Time: 707.600 ms

Fine, works. Have fun with pl/java.

Btw, for completeness there is a uninstall script, too:

([email protected][local]:5445) [postgres] > ! ls /u01/app/postgres/product/95/db_0/9.5AS/share/pljava_uninstall.sql                                  
/u01/app/postgres/product/95/db_0/9.5AS/share/pljava_uninstall.sql

Thumbnail [60x60]
by
Daniel Westermann