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