Install PL/Java 1.5.2 in PostgreSQL 11

PostgreSQL 11 includes several procedural languages with the base distribution: PL/pgSQL, PL/Tcl, PL/Perl, and PL/Python. In addition, there are a number of procedural languages that are developed and maintained outside the core PostgreSQL Distribution like PL/Java (Java), PL/Lua (Lua), PL/R (R), PL/sh (Unix Shell), and PL/v8 (JavaScript).

In this post, we are going to see how to compile/install PL/Java procedural language add-on module in PostgreSQL 11.

PL/Java is a free add-on module that brings Java Stored Procedures, Triggers, and Functions to the PostgreSQL™ backend. The way it works today, when you write a PL/Java function, a compiled Java code(.class file) stored in Java archive file(.jar file), will be loaded into the database. When PL/Java function executes, it creates a JVM within the PostgreSQL server process and then asks the JVM to execute the byte-code form of function. It can also access a method of a Java class. However, PL/Java does not include a Java compiler or a Java Virtual Machine, those components should come as a separate sources, it just includes a set of deployment aids that makes it easy to install/manage server-side functions written in Java.

Enabling PL/Java in PostgreSQL database has its own benefits and caveats – lets not get into those details here, however you can go through an article that explains in detail on PL/Java adoption here. Let’s start the installation steps.

Software Prerequisites

  • Install Dependencies
    gcc-c++, gcc, java 1.8.x, openssl
  • Install Apache Maven to build PL/Java
  • Install PostgreSQL 11.x (RPM version)

Install Dependencies


Install Apache Maven

PL/Java is built using Apache Maven. It will execute on the PL/Java source bundle and produce files required for PostgreSQL(It will not install). Download the latest Apache Maven and follow the steps as shown below:

Enable mvn in the PATH


Install/Configure PostgreSQL 11

  1. Install PostgreSQL v11 Yum Repository from PostgreSQL.org site.
  2. Install PostgreSQL via YUM
  3. Setup database cluster and start.
  4. Verify the database connection and check the list of languages in the database.(default: plpgsql enabled)

After installing all dependencies, set the environment variables(gcc, java, pg_config, mvn) so they are in PATH to compile PL/Java.

We are all set with dependencies.

Install/Configure PL/Java

Download PL/Java tar.gz format from the release page here, and extract .tar.gz to directory.

Run ‘mvn clean install‘ command in extracted directory.

You should get above summary output from “mvn clean install”, which indicates all the dependencies are hooked with PL/Java. Now, run Pljava-packaging/pg11xxx jar file so that it will extract/produce needed files in PostgreSQL Library directory.

As you can make out from the above output, it has produced a list of files(.jar, .so, .sql) in “/usr/pgsql-11/share/pljava” and “/usr/pgsql-11/lib”.

Now, lets enable PL/Java GUC parameters in $PGDATA/postgresql.conf and restart the database server so it will influence PL/Java operation. There are set of PL/Java parameters mentioned in wiki here, however, there are two parameters which are mandatory to enable in the database cluster:

    • pljava.classpath – Location where “pljava-1.5.2.jar” created in our last step.
    • pljava.libjvm_location – Location where “libjvm.so” to load the Java RunTime.

We know “pljava-1.5.2.jar” location, lets find “libjvm” for JDK 1.8.x on this machine.

Lets set both parameters in $PGDATA/postgresql.conf and restart

OK. Now, create PL/Java language in database.

Nice!! Let’s do quick test by creating PL/Java function and run.

Cool!! Hope it helps. Please feel free to leave comments. Thank you for reading.

Raghav

Leave a Reply