(work in progress...)
PL/Java is a PostgreSQL extension that allows us to
- write stored procedures in java
- make custom static methods visible within PostgreSQL
The latter allows us to create user-defined functions (UDF) and user-defined types (UDT). There is also limited support for foreign data wrappers (FDW).
I wrote a series of articles on how to use PL/Java back in 2011.
- Introduction to PostgeSQL PL/java, part 1 (stored procedures)
- Introduction to PostgeSQL PL/java, part 2: Working with Lists
- Introduction to PostgeSQL PL/java, part 3: Triggers
- Introduction to PostgeSQL PL/java, part 4: User Defined Types
- Introduction to PostgeSQL PL/java, part 5: Operations and Indexes
This extension creates a new JVM for each database connection. These docker images are configured (per PL/Java VM option recommendations) for improved performance albeit at the cost of making it impossible to attach to a running instance with a debugger.
We can get an additional performance boost by pre-compiling the java classes to native libraries
using the Java Ahead-of-Time Compiler (jaotc). The PL/Java documentation refers to
Quicker Clojure startup with AppCDS and AOT
for an example of this.
pgTAP provides a TAP mechanism for PostgreSQL unit testing. It allows us to run tests in the database itself. It's useful when writing tests for stored procedures - and very useful when writing tests for java-based user-defined functions (UDF) and user-defined types (UDT).
The Dockerfile installs the official pl/java package available at the PostgreSQL apt repo. The Dockerfile also installs pgTAP (for unit testing) and pgxnclient (PostGresql Extension Network) since anyone writing code in pl/java will definitely want to test it and is likely to be interested in additional extensions.
Finally the Dockerfile sets up a simple script that creates the 'java' extension and grants public access to 'java' (but not javau).
See the official PostgreSQL image page. This image does nothing but preinstall pl/java, pgTAP, and pgxnclient.
If you need the minimal viable command use
$ docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=password beargiles/pljava
A natural extension to this docker image is one that adds a /docker-entrypoint-initdb.d script
that defines pl/java-based stored procedures and any UDF/UDT that uses them.
It is possible to add your own java jars to the classpath and make the classes and methods available to the system. This is particularly useful when you wish to create opaque user-defined functions (UDF) user-defined types (UDT).
In this case you must add /usr/share/postgresql/14/pljava/pljava-api-1.6.4.jar to your claspath
and follow the restrictions listed on the pl/java wiki.
E.g., only static methods will be visible.
The Dockerfile will then need to be modified to:
- download and build the java code
- add a
/docker-entrypoint-initdb.dscript using 'pgxs' to install the jar(s)
At this point the custom jars will be available for use by any pl/java stored procedure.
The official list of prebuilt PL/Java distributions includes two additional docker images.
The source code is located at github.com/beargiles/postgresql-pljava-docker.