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:
enterprisedb@centos7:/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:
enterprisedb@centos7:/home/enterprisedb/ [PG2] sqh Null display is "NULL". Timing is on. psql.bin (9.5.0.5) Type "help" for help. (enterprisedb@[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 (enterprisedb@[local]:5445) [postgres] >
Hm, does not work. So lets add it directly to the postgresql.conf configuration file:
enterprisedb@centos7:/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):
enterprisedb@centos7:/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:
enterprisedb@centos7:/u02/pgdata/PG2/ [PG2] pg_ctl restart -D /u02/pgdata/PG2/ -m fast
(enterprisedb@[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
(enterprisedb@[local]:5445) [postgres] >
Great. The next step is to execute the installation script:
enterprisedb@[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:
(enterprisedb@[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:
(enterprisedb@[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:
(enterprisedb@[local]:5445) [postgres] > dn
List of schemas
Name | Owner
--------+--------------
public | enterprisedb
sqlj | enterprisedb
(2 rows)
(enterprisedb@[local]:5445) [postgres] > set search_path='sqlj';
SET
Time: 0.167 ms
(enterprisedb@[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)
(enterprisedb@[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:
(enterprisedb@[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:
(enterprisedb@[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