pljava: Recent Wiki Updates

Home

2025-09-29T12:51:54-04:00

Welcome to PL/Java

If you have comments or ideas regarding this wiki, please convey them on the Mailing List. A great deal of information can also be found at the project information site.

	Releases quick guide
	1.6 series	1.5 series
Description	Current recommended; for PostgreSQL 9.5 and later, Java 9 and later. Upgrades from the 1.5 series should be planned upgrades; please review the release notes 1.6.0 to current.	Legacy support, for PostgreSQL back to 8.2, Java back to 6. Will not build with Java 15 or later. Can be used with 15 through 23 if built with an earlier JDK.
Latest	1.6.10	1.5.8

Important news

For implications of running on Java 24 and later, please see JEP 411.

Overview

PL/Java is a free add-on module that brings Java™ Stored Procedures, Triggers, and Functions to the PostgreSQL™ backend. The development started late 2003 and the first release of PL/Java arrived in January 2005. The project is released under the PLJava License license.

Features

Ability to write functions, triggers, user-defined types, ... using recent Java versions. (To see the currently-targeted versions, please see the versions page.)

Standardized utilities (modeled after the SQL 2003 proposal) to install and maintain Java code in the database.
Standardized mappings of parameters and result. Supports scalar and composite user-defined types (UDTs), pseudo types, arrays, and sets.
An embedded, high performance JDBC driver utilizing the internal PostgreSQL SPI routines.
Metadata support for the JDBC driver. Both DatabaseMetaData and ResultSetMetaData are included.
Integration with PostgreSQL savepoints and exception handling.
Ability to use IN, INOUT, and OUT parameters
Two language handlers, javau (functions not restricted in behavior, only superusers can create them) and java (functions run under a security manager blocking filesystem access, users who can create them configurable with GRANT/REVOKE). For implications of running on Java 24 and later, please see JEP 411.
Transaction and Savepoint listeners enabling code execution when a transaction or savepoint is commited or rolled back.

PL/Java earlier supported GCJ, but targets conventional Java virtual machines for current development.

Documentation

The first stop for up-to-date documentation should be the project information site.

You may also find useful information via the wiki links below. Information here will be migrating to the project information site as it is brought up to date.

Installation Guide
User Guide
Contribution Guide

Resources

Note: To be sure of running a current PL/Java, please check the releases page to see what is current. You may check for any prebuilt packages available for your platform. If prebuilt packages are not available for your platform, or if they are behind the current version, please consider Building PL/Java using the source here on GitHub.

The "no longer supported" downloads linked below are quite old and of chiefly historical interest.

Source downloads
Prebuilt packages
No longer supported downloads
Mailing List
Questions tagged pljava on Stack Overflow (Atom feed)
Feed for changes to this wiki itself
Bug Tracking
Older bug tracker at PgFoundry
Even older bug tracker at GBorg

Technology

Technology in Brief
The choice of JNI

Contribution guide

2025-05-31T17:15:34-04:00

PL/Java is an open source project and contributions are vital for its success. In fact, all development of the project is done using contributions. Here are a few guide lines that will help you submit a contribution.

Getting started

Make sure you have a GitHub account.
Create a fork of the PL/Java repository.
Take a look at the Git Best Practices document.

Let people know what you're planning

You should let the community know what you're planning to do by discussing it on the PL/Java Mailing List. In many cases it might also be a good idea to first create an issue where the details of what needs to be done can be discussed (the actual pull-request is an issue in itself so in case you already have something, that issue is probably sufficient).

Making Changes

Create a local clone of your fork.
Choose an appropriate PL/Java branch as the base for your contribution. This guide used to say "You should branch off the master branch", but that is not always the best choice. Please take a moment to look at Which PL/Java branch to base on? below. Then pop that section off your reading stack and return to this point. :)
Create a topic branch for your work. You should base it on the branch you selected in the previous step. Name your branch by the type of contribution, source branch, and nature of the contribution, for example, bug/master/my_contribution. Generally, the type is bug, or feature, but you can use something else if those don't fit. You can look at existing branch names in the repository for ideas. To create a topic branch based on master:
```
git checkout master && git pull && git checkout -b bug/master/my_contribution
```
Don't work directly on the master branch, or any other core branch. Your pull request will be rejected unless it is on a topic branch.
As to source code formatting and other coding conventions, please look at the upstream PostgreSQL Coding Conventions. PL/Java generally follows those, only without using the automated pgindent tool, so if there is a place in your code that some slight departure from those rules could make much easier to read, then you may do the more-readable thing and not worry about something like pgindent messing it up.
Keep your commits distinct. Each commit should accomplish one clear step in the development of your contribution, and include whatever changes to whatever files were needed for that step ... and then the next clear step should come in another commit. Please look at Organizing commits below for more on this.
Make sure your commit messages are in the proper format.
If your commit fixes an issue, close it with your commit message (by appending, e.g., fixes #1234, to the summary).

Submitting Changes

Be sure to test your changes! Test more than just the build, where appropriate; for example, test the documentation build (mvn site site:stage) if you have edited documentation files, javadoc comments, the version of maven-site-plugin, etc.
Keep in mind the range of PostgreSQL and Java versions that should be supported by the branch of PL/Java you are working on. The REL1_6_STABLE branch, for example, documents support for PostgreSQL back to 9.5 and Java back to 9. Be careful not to introduce hard dependencies on newer versions. Test on older supported versions if you can.
Push your changes to a topic branch in your fork of the repository.
Submit a pull request to the tada/pljava repository.

Which PL/Java branch to base on?

In many projects, active development all happens on a branch called something like main (or sometimes master in a project as old as PL/Java) and, if there are older branches supporting released versions, selected changes get "backpatched" into those.

PL/Java's development does not, at present, fit into that model. There are currently (as of mid-2025) two distinct threads of development taking place in the project:

Ongoing maintenance of PL/Java 1.6.x, which is what's in active use and gets regular minor-number releases, takes place on the REL1_6_STABLE branch.
The REL1_7_STABLE branch is reserved for a future release once a major refactoring and modernization is complete. The progress of the refactoring is being tracked in a long-lived pull request, PR 399. PR 399 is effectively the branch feature/REL1_7_STABLE/model.
The branch called master has a future major version of 2 reserved, but nothing is really happening on that branch at present.

Under the current development practice, maintenance changes that are made on REL1_6_STABLE get merged forward so that the master and REL1_7_STABLE branches do not fall behind.

Under these conditions, you have these most-likely choices of branch to form the base of your contribution:

If your contribution is a simple bug fix to currently-in-release PL/Java 1.6, use REL1_6_STABLE as the base.
Or, if your contribution is a simple new feature that you would like to have soon in a regular release (and is straightforward enough that you will not mind having to build it in the devilishly-hard-to-maintain unrefactored 1.6 code base!), use REL1_6_STABLE as your base for that too.
For any more ambitious contribution you would like to make to the future of PL/Java, the best choice will be to base your branch on REL1_7_STABLE and regularly merge feature/REL1_7_STABLE/model into it to keep it up to date. You should study the PR 399 pull-request comments to understand the new interfaces available and prefer them to the ones being deprecated.

Organizing commits

Some projects request that you squash multiple commits into one before submitting a pull request, but that is not wanted in the PL/Java project. Any pull request more involved than the simplest bug fix should probably consist of more than one commit. Each commit should cover a clear step along the path from PL/Java-without-the-new-feature to PL/Java-with-it, and the commit message for each step should explain that step, and explain whatever key decisions led to that step being done that way and not some other way.

This does not mean that a pull request should be all the commits that you made in your local repository clone as you developed the contribution, with all the times you backtracked, fixed typos, and changed your mind. As soon as you feel you have something ready to submit, you should look back over the history of your commits and think of a way to organize them into a deliberate sequence of steps, clear enough for another reader to easily follow, for getting from PL/Java-without-the-feature to PL/Java-with.

At that point, you should become familiar with git rebase --interactive if you are not already, and use it to rework your commits into the sequence that will tell your clear story. Look over the new commit history after you have done that. If you see that it is good, it is ready to be included in a pull request.

But generally avoid force-pushing to a shared repository

If you have interactively revised a history of commits, then the next time you push to a git repository where the old commits existed, a "force-push" will be needed because the history has changed.

That is a non-problem as long as you are working locally and pushing into your own locally-cloned repository. Once you have pushed some commits up to a shared repository, on the other hand, it is possible other people have linked to those or based branches on them, and a force-push that changes them will create problems for others.

Therefore, it makes sense to force-push freely and often in your own local repository, as much as you need while reorganizing your commits into a coherent story. But once you have pushed those commits up to a visible repository, it is best to leave them as they are. If you have pushed some commits before your development is complete, then as you continue the work in a further sequence of commits, you can again freely revise that sequence locally until you have another sequence ready to push, and then push that sequence upstream and leave it alone as well. If you discover a mistake in something already pushed and publicly visible, that should just be fixed somewhere in your next sequence of commits.

Most rules can be bent: if you have just pushed something up to a public repository and then recognized a mistake minutes or seconds later, a force-push may be ok if no one else is likely to have saved references to your branch during that time.

Commit Message Format

What should be included in a commit message? The three basic things to include are:

Summary or title.
Detailed description
Issue number (optional).

Here is a sample commit message with all that information:

Adds UTF-8 encoding to POM properties

Some POM's did not have the source encoding specified. This
caused unnecessary warning printouts during build. This commit
ensures that all POM's includes the correct declaration for
UTF-8.

Closes #1234

The summary should be kept short, no more than 49 characters, and the lines in the detailed message should not exceed 72 characters. These limits are recommended to get the best output possible from the git log command and also to be able to view the commits in a terminal window with 80 character limit.

The issue number is optional and should only be included when the commit really closes an issue. The close will then occur when the pull request is merged.

JEP 411

2025-03-23T18:01:03-04:00

PL/Java and JEP 411: Java 17, Java 24, and beyond

Changes in Java, first announced with Java 17 and of serious consequence in Java 24, force important functional changes in PL/Java.

Permissions and enforcement in PL/Java

PL/Java supports at least two PostgreSQL CREATE LANGUAGE declarations, one with and one without the TRUSTED property, and enforces limits on what a function declared in the TRUSTED language can do. The PL/Java 1.6 series goes further and makes those restrictions configurable, and more than two PL/Java "languages" can be declared, with different configurable restrictions for each, as described in the documentation.

To provide and support these controls, PL/Java relies on many features of the groundbreaking Java 2 security architecture as specified in 2002.

Changes in Java 17 and beyond: JEP 411

In April 2021, the Java developers announced a JDK Enhancement Proposal, JEP 411, to remove this functionality from Java over a series of releases, with the first changes already shipped in Java 17.

As originally described, the "major components of the security model are security policy, access permissions, protection domain, access control checking, privileged operation, and class loading and resolution." JEP 411 announced eventual elimination of the first, fourth and fifth, with the second and third to remain in vestigial form. PL/Java's policy enforcement relies on the affected Java features.

Java 17

In Java 17, all of the functionality remains; only warnings and deprecation markings have been added. Java 17 and 21 are also positioned as long-term support releases, successors in that role to Java 11. It will therefore be possible to run current PL/Java versions without difficulty for a number of years.

However, Java 17 will be designed to write an unconditional warning on its standard error channel (which will go into PostgreSQL's log file, if logging_collector is enabled), every time any backend loads PL/Java. The JEP 411 proponents rebuffed requests to allow a higher layer, like PL/Java, to suppress the low-level JVM warning and issue one that is layer-appropriate (such as an explanation about trusted functions or a link to this wiki page).

The JVM's boilerplate warning will look like this:

WARNING: A terminally deprecated method in java.lang.System has been called
WARNING: System::setSecurityManager has been called by org.postgresql.pljava.internal.Backend
WARNING: Please consider reporting this to the maintainers of org.postgresql.pljava.internal.Backend
WARNING: System::setSecurityManager will be removed in a future release

As much as the warnings will urge you, please do not consider reporting them to the maintainers of PL/Java.

The PL/Java 1.6 series, as described below, does attempt to suppress the boilerplate JVM warning, and issue a "migration advisory" ereport of a more useful nature, only upon certain administrative actions like CREATE EXTENSION or installing a jar, and no more than once in a session. That should cut down on the log spam.

Java 18 through 23

When run on Java 18 through 23, PL/Java can still provide policy enforcement and a trusted/untrusted language distinction, but requires a specific JVM option added in the pljava.vmoptions configuration setting:

-Djava.security.manager=allow will allow PL/Java to work as it always has, with policy enforcement
-Djava.security.manager=disallow will allow running PL/Java (1.6.9 or later) with no policy enforcement, as described in new documentation

Without one or the other of those settings, PL/Java 1.5 or 1.6 will fail to start on Java 18 through 23.

Java 24 and later

With Java 24, only the -Djava.security.manager=disallow option (and therefore only PL/Java 1.6.9 or later) will work, as the crucial Java language features are no longer implemented. Where policy enforcement is needed, current PL/Java releases should be run on any earlier Java version, including 17 and 21, which are positioned for long-term support.

In some future Java version, the needed Java features will not be merely unimplemented but removed from the API, and current PL/Java versions will fail to compile, and fail with linkage errors at runtime. A future PL/Java version will be needed that eliminates hard dependencies on the affected APIs.

Plans for PL/Java release series

PL/Java 1.5 series

PL/Java 1.5 is the series minimally maintained for legacy support (PostgreSQL back to 8.2, Java back to 6). Its build process requires Java 14 or earlier, but it can be run on later Java versions. Minimal changes will be made in 1.5.

Because it already does not support building on newer Java versions, it will not need any source code changes to suppress the new deprecation warnings.
It will make no attempt to intercept the fixed warning message described above. When running on Java 17 or later, that warning will be written on the backend's standard error channel, every time a backend starts PL/Java.
Code will not be added to automatically supply the -Djava.security.manager=allow option for any Java version.
Code will be added (in 1.5.8) to attempt to detect when the needed components have become no-ops, and then throw an exception rather than executing any function declared as trusted.

Options if that exception is seen can include:

Continuing to use PL/Java 1.5, downgrading Java to the preceding version
Changing all trusted function declarations to untrusted and accepting that they will run without security enforcement
Upgrading to a newer PL/Java version if available.

Note that even functions declared as untrusted have normally had a few limits enforced in PL/Java 1.5, and even those limits will be unenforced if running on a version of Java where the needed components are no-ops.

PL/Java 1.6 series

PL/Java 1.6 is the current recommended series covering PostgreSQL 9.5 and later, Java 9 and later.

Code added in 1.6.3 attempts to suppress the boilerplate, low-level warning from the JVM, so that more meaningful notice in relevant PL/Java terms can be given. Suppression of the boilerplate is best-effort, relying on JDK internals, given the rebuffed request for an exposed control serving that purpose.
Code added in 1.6.3:
- Issues warnings (or notices) of the upcoming JEP 411 impacts, including a link to this wiki page
- Issues the warning whenever the runtime Java major version is above 11, so as to warn not only sites that have moved from 11 LTS to 17 LTS, but also sites that update more frequently between LTS versions
- Issues the warning when PL/Java is installed or upgraded, or when a cluster that has PL/Java installed undergoes a binary pg_upgrade (in the pg_upgrade case, the warning will be unconditional, because no JVM is started at that time to query its version)
- Issues the warning, at most one per session, at commit of a transaction that has declared or redeclared at least one PL/Java function
- Refuses to execute, if the needed support is detectably stubbed out, unless a special configuration option is set
Code added in 1.6.9:
- Supports use in a without enforcement mode described in new documentation
1.6.9 will not be usable on whatever version of Java finally removes the deprecated APIs. It will fail with linkage errors.
A release after 1.6.9 may also be adapted to run without linkage errors on a Java version where the APIs have been finally removed.

Note that even functions declared as untrusted run with significant limits by default in PL/Java 1.6, and the limits on both trusted and untrusted functions are configurable in policy, and all such limits will be unenforced if running without enforcement. The new documentation should be reviewed carefully before using PL/Java in that mode.

PL/Java 1.6 will continue to see active development for the foreseeable future. The existing enforcement mechanisms remain functional through Java 23, including Java 21 LTS, and for some applications, its fine-grained security policy may remain more attractive than what post-JEP-411 versions will support.

PL/Java ?.?

A new major version of PL/Java will need to be developed that can support some notion of trusted-function enforcement without relying on the rescinded Java mechanisms.

This will be a big undertaking, and will have to scale back expectations to a much more basic model of what can be enforced: chiefly filesystem access, process manipulations, and IPC. The result will probably land somewhere more fine-grained and configurable than PL/Java 1.5, but much less so than 1.6.

All usual disclaimers about forward-looking statements must be applied.

During that development, both existing PL/Java series can continue to be used with the limitations described above.

An OpenJDK fork to maintain policy enforcement

A fork of OpenJDK, independent of the PL/Java project, has been made whose goal is to track the feature development of OpenJDK 24 and later while maintaining support for policy enforcement.

If the project is successful and proves usable as a JVM runtime for PL/Java, it may offer an attractive option for applications where both policy enforcement and the most modern Java language features are wanted. It may be worthy of support by organizations seeking such an option.

That project can be found on GitHub here.

This page will be updated as more is learned about that project's success and usability with PL/Java.

Build tips

2024-04-12T11:26:27-04:00

Tips for resolving build problems

Some typical issues encountered when building PL/Java can be listed here, along with tips for resolving them.

The tips that always apply

Please do carefully read the build instructions, especially the "software prerequisites" section, and the "special topics" section for any that apply to the platform where you are building.

Also be sure to review the "troubleshooting the build" section at the end of the build instructions page.

If you review the mailing list archive and the issues list, you may find a report of a situation like your own. (On the issues list, it is possible someone reported an issue, a solution was found, and the issue was closed, so look at recent closed issues too.)

Failure shown for `pljava-api`

`Fatal error compiling` caused by `invalid flag: --release`

By itself, Fatal error compiling isn't very helpful. Use Maven's -e option to get full exception stack traces.

org.apache.maven.lifecycle.LifecycleExecutionException: Failed to execute goal ...:compile (default-compile) on project pljava-api: Fatal error compiling
...
Caused by: org.apache.maven.plugin.MojoExecutionException: Fatal error compiling
...
Caused by: org.codehaus.plexus.compiler.CompilerException: invalid flag: --release
...
Caused by: java.lang.IllegalArgumentException: invalid flag: --release
    at com.sun.tools.javac.api.JavacTool.processOptions
    ...
    at org.codehaus.plexus.compiler.javac.JavaxToolsCompiler.compileInProcess

If you see a stack trace like this building the PL/Java 1.6 series or later, you simply have Maven running on a Java version that is too old. PL/Java 1.6.x requires Java 9 or later.

It can be possible to have the environment set up so that simple command

javac --version

at the shell will find the javac that you want on the PATH and report that version, but the mvn command finds a different installed Java version and uses it to run Maven. Because Maven does Java compilation in-process, the PL/Java code ends up being compiled by whatever Java version is running Maven itself. If that version is not at least Java 9, this error results. The command

mvn --version

is useful because it does not show only the version of Maven, but also the version of Java that Maven has found to run with.

The JAVA_HOME variable can be set in the environment to ensure Maven runs on the needed version of Java.

Failure shown for `pljava-so`

Missing `-devel` prerequisite packages

The most common cause of reported failures building pljava-so is a missing required file. Sometimes your distribution's packaging system will have chosen to organize a prerequisite piece of software into more than one package, for example, one that contains only library files, and another with a name ending in -dev or -devel that contains the necessary .h files. Some distributions take this further than others; see the "special topics" section for Ubuntu for an example where even libraries built as part of PostgreSQL itself are split up into multiple separate packages.

The solution is simple: look over the error messages from the pljava-so section of the build output to find any that refer to a file that could not be found. Usually it will be a .h file or a library (.so, .dll, .dylib, etc.).

Find out the name of the package, according to the OS or package distribution you are using, that contains the missing file, install that package, and you have probably solved the whole problem.

Further tip: Finding the error message that really mattered is easier if you follow the "troubleshooting the build" tip about the -Pwnosign option, to cut down the number of other messages that do not matter, if that option works on your platform.

Still stuck?

Please describe the issue you are facing on the mailing list.

Prebuilt packages

2023-09-19T15:40:06-04:00

Prebuilt PL/Java distributions

At present, the PL/Java project is reliant on downstream packagers to produce prebuilt, installable PL/Java packages for various platforms. The official PL/Java releases are offered in source form and take only a few minutes to build with Apache Maven as described in the build instructions.

This wiki page will be updated to list known prebuilt PL/Java packages and the platforms they are built for. As with any prebuilt distribution, you should be acquainted with the policies and reputation of any supplier of a prebuilt package. The PL/Java project has not directly built or verified any package listed here.

Known prebuilt packages available

Debian/Ubuntu packages on apt.postgresql.org

As of 2020, the Debian/Ubuntu packages have been consistently available at the current PL/Java versions (now 1.5.6) and for a range of Debian, Ubuntu, and PostgreSQL versions and architectures.

{1.5.2,"11.1 (Debian 11.1-1.pgdg+1)",11.0.1,Linux,amd64}

PL/Java 1.5.2 packages available for PostgreSQL 11 back to 9.3 for Debian unstable/buster/stretch and Ubuntu cosmic/bionic/xenial, for amd64/i386/ppc64el. Dbgsym packages available. Includes pljava-examples jar with the optional Saxon examples already built (download Saxon-HE 9.8.0.14 jar separately to use them).

Added 14 November 2018 Updated 5 October 2020

Docker images

Bear Giles reports offering the following images on hub.docker.com:

beargiles/postgres-pgxnclient: PostgreSQL image with only pgxn-client installed. This can be used as the base for other extensions.
beargiles/postgres-pljava: Same as above but with PL/Java installed. It includes the default Java 11 JRE.
beargiles/postgres-pljava-dev: Same as above but with everything required to built the extension. Note: this uses Java 17 JDK, not the Java 11 JDK.

From information 9 August 2023

Adrian Escutia Soto has prepared an image of 64-bit PostgreSQL 11 on Debian with Java 11 and PL/Java 1.6.2 for use with Docker.

{1.6.2,"11.11 (Debian 11.11-1.pgdg90+1)",11.0.6,Linux,amd64}

added 21 February 2021

Martin Bednar has prepared images of 64-bit PostgreSQL (9.5 and 9.4) with PL/Java 1.5.0 and Oracle Java 8 for use with Docker.

{1.5.0,9.5.1,1.8.0_74,Linux,amd64}
{1.5.0,9.4.6,1.8.0_74,Linux,amd64}

added 12 April 2016

Complete PostgreSQL distributions from BigSQL

BigSQL provides native installers for Centos 6 and 7, Ubuntu 12.04 and 14.04, OS X 10.9+, Windows 7+, and Windows Server 2008 and 2012. These distributions of PostgreSQL 9.5, 9.4, 9.3, and 9.2 include PL/Java 1.5.0.

added 12 April 2016

To list a prebuilt package here

Please announce the availability of your package on the pljava-dev mailing list, along with the output of the third query below:

Note: as of mid-May 2016, the pljava-dev mailing list is working again, and should be used to announce packages. In case the mailing list does not seem to work, then please open an issue.

SELECT sqlj.install_jar( fileurl-to-built-pljava-examples-*.jar , 'ex', true);
SELECT sqlj.set_classpath('javatest', 'ex');

SELECT array_agg(java_getsystemproperty(p)) FROM (values
('org.postgresql.pljava.version'),
('org.postgresql.version'),
('java.version'),
('os.name'),
('os.arch')
) AS props(p);

Security

2021-09-25T17:38:28-04:00

Installation

Only a PostgreSQL super user can install PL/Java. The PL/Java utility functions are installed as "security definer" so that they execute with the access permissions that were granted to the creator of the functions.

Trusted vs. untrusted language

PL/Java can declare two language entries in SQL: java and javau. Following the conventions of other PostgreSQL PLs, the 'untrusted' language (javau) places no restrictions on what the Java code can do, while the 'trusted' language (java) installs a security manager that restricts access to the filesystem. In PL/Java 1.5.x, a legacy version, those policies are fixed.

In PL/Java 1.6.x, the policies for both java and javau are configurable, and additional language "aliases" can be created and given policies of their own, as described in the docs.

GRANT/REVOKE USAGE ON LANGUAGE java can be used to regulate which users are able to create functions in the java language. For the javau language, regardless of permissions, only superusers can create functions.

Important: for implications of running on Java 17 and later, please see JEP 411.

Execution of the deployment descriptor

The install_jar, replace-jar, and remove_jar utility functions optionally execute commands found in a SQL deployment descriptor. Such commands are executed with the permissions of the caller. In other words, although the utility function is declared with "security definer", it switches back to the identity of the invoker during execution of the deployment descriptor commands.

Classpath manipulation

The utility function set_classpath requires that the caller of the function has been granted CREATE permission on the affected schema, unless it is the public schema, in which case the caller must be a superuser.

Build process custom Maven plugin

2020-07-01T00:43:13-04:00

Custom Maven plugin for the build process (GSoC 2020)

As long as this page looks, it is still intended for discussion; there is a lot of room still left for design decisions, better ideas, and so on.

Since PL/Java was Mavenized in 2013, the pljava-so subproject has been built using the nar-maven-plugin, assisted by the maven-antrun-plugin running some actions in an Ant build.xml that were not available as a Maven plugin.

Since Kenneth Olson added MSVC support in 2014, there has also been some JavaScript in that process. With Ant already used in the build, an Ant script target offers a useful way for small bits of straight code to express clearly what was unwieldy or impossible to express in strict Maven declarative style using only existing plugins.

Experience with this combination has shown that the nar-maven-plugin is not a perfect fit for the needs of this build. Some of the reasons were covered in the GSoC 2020 project idea.

It appears that there are enough details specific to PostgreSQL and PL/Java to warrant developing a custom Maven plugin to perform this build. It's been confirmed that Maven can build a plugin subproject and use it in another subproject in a single build, so this should not complicate the PL/Java build process by adding another step.

Relationship to PGXS

PGXS is the native extension building system supplied in PostgreSQL for building extensions in C or C++, implemented as a system of makefiles for GNU make. It encapsulates knowledge of the compiler and linker invocations needed on different supported platforms, and standard actions such as running pg_config to obtain needed options and flags for the specific PostgreSQL installation being built against.

The current build system has been essentially an effort to cobble nar-maven-plugin, Ant, and JavaScript into something that does what PGXS does, but in cross-platform Java and from Maven. The issues filed against the current system tend to reflect where it departs from what PGXS would do.

The proposed new plugin has pgxs in the name to suggest the intended behavior similarity, but not necessarily to require it be implemented a particular way, such as directly over the real PGXS. (While the PGXS makefiles are supplied with PostgreSQL, typically in a development package, and PL/Java already requires C compilers and linkers and a JDK to build, if there are platforms where GNU make would be a separate install, relying directly on PGXS would lengthen the list of prerequisites for building PL/Java from source.)

Role of scripting

It is not a goal to eliminate JavaScript from the build process. It has proven too useful to have a clear and compact scripting language available to express some parts of the build process directly. Makefiles, too, as used in PGXS, get their flexibility from allowing bits of script wherever needed, with the make machinery supplying the dependency resolution and ordering.

It is not a goal to produce something as complete and flexible and automated as make. Really, PL/Java needs to compile some C files and link them. The work is in selecting the right command sequences to do that on several different platforms, relying on correct values from pg_config.

We currently have this "inside-out"

The current build system could be said to have the role of scripting turned inside out. It uses JavaScript where necessary to do something the available Maven plugins would not do, instead of where it would be the clearest expression of a task.

For example, details of what options might be passed to a platform's compiler or linker can be buried inside nar-maven-plugin and not available for easy inspection by reading PL/Java's POM. At the same time, a reader of the POM sees 72 lines of JavaScript to quote a string for C, an algorithm that hasn't changed in twenty years and is not an interesting part of PL/Java's build.

The new plugin's philosophy should be to turn that around, implement the boring details and building blocks inside the plugin, and make some available as public methods that bits of script might call.

Ideally, it will have a configuration syntax that allows some JavaScript inlined into the XML, the same as is now being done with the maven-antrun-plugin. Done right, we could drop the reliance on both nar-maven-plugin and maven-antrun-plugin and move all of the build logic into pom.xml rather than having some of it split off in build.xml.

Basic operations

Get PostgreSQL build-specific information by querying pg_config

Both our current build process and PGXS do this. For PGXS it happens in Makefile.global when it is included by PGXS.mk.

For the current PL/Java build it happens in build.xml; ant ends up writing values into a pgsql.properties file just so a Maven plugin can read the file and set the properties in Maven. We should be able to simplify that and just set Maven's properties. (But beware! They can't just be set as pljava-so's properties; pljava-packaging uses them too, currently by reading that file again.)

Merge build-specific information into platform-specific recipes

Both PGXS and nar-maven-plugin contain embedded information on the needed compiling and linking commands for different supported platforms. The build-specific information from pg_config gets merged into those generic recipes.

For PGXS, the embedded rules for linking a shared object on the different platforms are found in Makefile.shlib.

For the nar-maven-plugin, they are found in a file inside the plugin's jar file in the Maven repository, so the easiest way to inspect them is on github. It is inconvenient to add support to PL/Java for additional platforms, because the nar-maven-plugin does not offer a way to supply some additional platform definitions that add to its built-in set. There is only the option of using a system property to specify another file to be used instead of the built-in one, so a new platform can only be supported by dropping a new file into the source directory and editing the docs to tell a person building for that platform to use an extra command-line option. (This commit is an example.)

How should the plugin embed the platform recipes?

The super-ambitious idea would be a Java parser for GNU makefiles that would gather the information directly from PostgreSQL's files. But that would be a major undertaking and we could easily get away with less.

If the plugin had a configuration section in the POM that would accept some simple JavaScript (maybe looking like JSON, or like a map from platform names to functions, etc.), that would be an easily readable form for the recipes They would be right there in the POM for inspection, not buried somewhere inside a jar file, and would be easy to edit to add new platforms in the future. The initial set could just be human-populated by looking at Makefile.shlib, which wouldn't be much work, and changes are infrequent.

(Just for the record, the Java makefile parser idea would not be completely bonkers; somebody has started one but no commits in several years. It has a TODO saying no support for many things I'm sure are in PostgreSQL makefiles, but it also has commits more recent than the TODO that mention some of those things, so maybe the TODO is out of date. I have not tried to run it. It is not BSD licensed, so would not belong in a PL/Java distribution, but if somebody forked it, got it complete enough to read PostgreSQL's makefiles, and deployed it to Maven Central, it would be usable as a build-time dependency. It might also get used more widely.)

Exported symbol filtering

The PGXS makefiles appear to support filtering of which global symbols to export for most or all supported platforms, something PL/Java does not currently do. That is worth supporting if we can: PL/Java only has a couple of entry points that need to be exported for PostgreSQL to call, and keeping the rest of its global symbols non-exported would reduce the pressure to give others long unwieldy names hoping they won't collide with other extensions.

Argument passing workaround for Windows

When invoking other programs on Windows, various characters in command-line arguments can cause the receiving program to parse the command line incorrectly (background in issue 190). The root cause is inherent in the Java Process API itself, whose design doesn't take the weirdness of Windows command-line parsing into effect.

What makes Windows command-line parsing weird is that Windows doesn't do it: it is up to each program to parse the command line it receives, and different ones can use different rules.

In practice, most programs use the C library they are linked to, so the number of different rule sets out there is more like the number of C libraries/versions in use, but that number is greater than 1 and with rules that differ. (I am relying on this source for these details.)

That means on Windows, when invoking a program with arguments, in some way which lexical rules to apply must also be somehow specified, so that any interesting argument values can have the correct escaping done to ensure they are recovered correctly when the invoked program parses them.

Windows might have something comparable to ldd that could look at a target executable and report what runtime library it links to, so the right rules could be selected. But autodetection might be more ambitious than we need (and could still have exceptions anyway, programs that link to an unknown library, or do their own custom argument parsing). So a way to specify the right set of rules to use will be necessary anyway, and probably sufficient.

There's no need to implement every different set of lexical rules any Windows program has ever used, as long as we figure out what rules are used by each of the compiling/linking tools our build recipes will use, and make sure to implement those.

What might a workaround API look like?

An obvious idea would look like subclasses of ProcessBuilder that apply different escaping rules. Code would pass individual arguments to the builder in the usual way, but they would have the right rules applied when the process is started.

However, ProcessBuilder is final, so the API can't look exactly like that.

It could look like some other class that has a start method taking a ProcessBuilder argument and returning a Process, much like the start method of ProcessBuilder itself. The argument list of a ProcessBuilder can be retrieved and modified. A POSIX-flavored subclass would have a start method that does nothing special, and directly calls start on the ProcessBuilder; a Windows-flavored one could modify the arguments first.

But it's not quite that simple

It won't be enough to simply transform the argument list, before calling ProcessBuilder.start, so that the values have the right escaping for the target program. That's because this argument list still has to go through Java's start implementation, which will try to add Windows escaping itself (using its built-in rules that were the problem in the first place).

It can be absorbing to try to think of ways to transform the arguments so that the end result of (our transformation) followed by (Java's transformation) produces the right values for the target program. But depending on the rules in play, there is no guarantee it is even possible.

When I faced a similar problem another time, I ended up transforming the command and arguments into an invocation of python, with the original arguments passed through base64 (no spaces, no punctuation, nothing to go wrong) and a snippet of Python code that would un-base64 the arguments and invoke the intended target.

On Windows, a similar idea using PowerShell might be most natural.

I don't know enough about PowerShell to say whether there's an advantage either way between just passing encoded args as I was doing with Python (assuming PowerShell has functions to decode base64), or to construct a serialized object to pass to PowerShell as by the PowerShell Remoting Protocol.

One last thing: a simple report Mojo

This is not directly related to building the native code, but with adding a custom plugin anyway, it would be helpful to have one simple Mojo that implements MavenReport and allows a bit of JavaScript to be given and run in the site life cycle.

The reason is that I am very unhappy with the maven-javadoc-plugin, as this commit and this one will show.

By the end of those two commits, it seems PL/Java now gets its Javadoc built by threading one tricky path, maybe even the only path, between the existing bugs in the plugin and javadoc. The arrival any day of one more bug in the wrong place might leave zero paths.

That is way too much work and too much risk, considering that the docs could be built simply by launching javadoc with nearly all default options. The extra work here has only been to get maven-javadoc-plugin not to add extra options that cause failure.

In the build life cycle, using maven-antrun-plugin again with four lines of JavaScript to launch javadoc with the right options would solve the problem once and for all.

But the maven-antrun-plugin's Mojos do not implement MavenReport, so that isn't an available option in the site life cycle.

Sql deployment descriptor

2020-05-12T22:16:54-04:00

SQLJ deployment descriptors

The install_jar, replace_jar, and remove_jar functions can act on a deployment descriptor allowing SQL commands to be executed after the jar has been installed or prior to removal.

The descriptor is added as a normal text file to your jar file. In the Manifest of the jar there must be an entry that appoints the file as the SQLJ deployment descriptor.

Name: deployment/examples.ddr
SQLJDeploymentDescriptor: TRUE

Such a file can be written by hand according to the format below, but the usual method is to add specific Java annotations in the source code, as described under function mapping - SQL generation. The Java compiler then generates the deployment descriptor file at the same time it compiles the Java sources, and the compiled classes and .ddr file can all be placed in the jar together.

The format of the deployment descriptor is stipulated by ISO/IEC 9075-13:2003.

<descriptor file> ::=
  SQLActions <left bracket> <rightbracket> <equal sign>
  { [ <double quote> <action group> <double quote>
    [ <comma> <double quote> <action group> <double quote> ] ] }

<action group> ::=
    <install actions>
  | <remove actions>

<install actions> ::=
  BEGIN INSTALL [ <command> <semicolon> ]... END INSTALL

<remove actions> ::=
  BEGIN REMOVE [ <command> <semicolon> ]... END REMOVE

<command> ::=
    <SQL statement>
  | <implementor block>

<SQL statement> ::= <SQL token>...

<implementor block> ::=
  BEGIN <implementor name> <SQL token>... END <implementor name>

<implementor name> ::= <identifier>

<SQL token> ::= ! an SQL lexical unit specified by the term "<token>"
                  in Sub clause 5.2, "<token> and <separator>", in ISO/IEC 9075-2.

If implementor blocks are used, PL/Java will consider only those with implementor name PostgreSQL (case insensitive) by default. Here is a sample deployment descriptor:

SQLActions[] = {
  "BEGIN INSTALL
    CREATE FUNCTION javatest.java_getTimestamp()
      RETURNS timestamp
      AS 'org.postgresql.pljava.example.Parameters.getTimestamp'
      LANGUAGE java;
  END INSTALL",
  "BEGIN REMOVE
    DROP FUNCTION javatest.java_getTimestamp();
  END REMOVE"
}

Configurable implementor-block recognition

Although, by default, only the implementor name PostgreSQL is recognized, the implementor name(s) to be recognized can be set as a list in the variable pljava.implementors. It is consulted after every command while executing a deployment descriptor, which gives code in the descriptor a rudimentary form of conditional execution control, by changing which implementor blocks will be executed based on discovered conditions.

Thoughts on logging

2019-01-27T20:50:14-05:00

Thoughts on logging

Update: This turns out to be more timely than I realized—PL/Pythonu is also developing a patch (starting last month) to the same end.

On their client side, psycopg2 already exposes a documented, supported PostgreSQL-specific Diagnostics object.

Note: this page does not describe how PL/Java currently works, except in the "Background" part. It is a proposal for further development.

Also, to anyone reading this for review or comment, a lot of this material may be more familiar than it is to me. If I seem to describe it in excessive detail, please regard that as my effort to have it straight in my own head.

Also also, to some it may seem strange that I use phrases like "log event" without insisting on any essential difference between thrown exceptions and calls on loggers. It's true, I'm not marking any such essential difference, and I hope, before this is done, that won't seem so strange.

So, here goes.

... logging isn't particularly magical.
—Dave Cramer

... it's what you do with it.
—variously attributed

Background

PostgreSQL has supremely good log messages.

It even has a style guide for writing them, and it pays off in the well-known quality and helpfulness of PostgreSQL messages.

Part of the excellence of PostgreSQL's messages can be traced to their rich structure. A message is not a blob of text with whatever details seemed useful while writing the code. It is a structured record with information serving several specific purposes and at several distinct levels of detail:

item	pq	PL/pgSQL	pgjdbc (+ -ng Notice)	pgjdbc-ng Exc	PL/Java
elevel	S		getSeverity		getErrorLevel
sqlstate	C	RETURNED_SQLSTATE	getSQLState getCode	getSQLState	getSqlState
message	M	MESSAGE_TEXT	getMessage	getMessage	getMessage
detail	D	PG_EXCEPTION_DETAIL	getDetail		getDetail
hint	H	PG_EXCEPTION_HINT	getHint		getHint
context	W	PG_EXCEPTION_CONTEXT	getWhere		getContextMessage
schema_name	s	SCHEMA_NAME	getSchema	getSchema
table_name	t	TABLE_NAME	getTable	getTable
column_name	c	COLUMN_NAME	getColumn	getColumn
datatype_name	d	PG_DATATYPE_NAME	getDatatype	getDatatype
constraint_name	n	CONSTRAINT_NAME	getConstraint	getConstraint
cursorpos	P		getPosition		getCursorPos
internalpos	p		getInternalPosition		getInternalPos
internalquery	q		getInternalQuery		getInternalQuery
filename	F		getFile		getFilename
lineno	L		getLine		getLineno
funcname	R		getRoutine		getFuncname
output_to_server					isOutputToServer
output_to_client					isOutputToClient
show_funcname					isShowFuncname
saved_errno					getSavedErrno
hide_stmt
hide_ctx
domain
context_domain

The libpq on-the-wire protocol preserves this structure, sending these components (the ones with pq codes) distinctly and intact to the front end. This gives client code enormous flexibility to catch and handle conditions appropriately. If the condition has to be logged or reported to a user, it can be shown at any appropriate level of detail, or even with a user interface that permits drilling down from generalities to specifics.

How awesome is that? Consider this: I have seen, with my own eyes, non-technical users entering stuff into a PostgreSQL database (using something as generic as LibreOffice Base as the front end) have an error dialog pop up, read it, understand what had to be corrected in the entry, and recover on their own.

I challenge anyone who has had support experience to tell me that ain't magic.

By and large, messages in PostgreSQL really are that good.

Original log event life cycle

A message that originates in the PostgreSQL backend proper begins as a call to ereport or elog in elog.c. The rules there are just a bit special:

If the message has any severity below ERROR (so, DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, LOG, COMMERROR, INFO, NOTICE, or WARNING) or above ERROR (so, FATAL, PANIC), it gets written immediately to logs / reported to the front end (according to the log_min_messages and client_min_messages settings), and then control returns to the call site if it was below ERROR, and does not return if it was above.
If the severity is exactly ERROR, it gets thrown PostgreSQL-style, and can be caught in PG_TRY/PG_CATCH constructs. The elog code does not send it to the server log or the front end at all at that point, but only when (if ever) it bubbles up to PostgresMain without having been handled. If it gets caught and handled, then any logging becomes the responsibility of whatever code caught it.

Handling logged events in front-end code

Events from the server that arrive at the front end show up in a form the front-end code can inspect and choose how to handle, either by polling for them (libpq PQresultErrorField, JDBC getWarnings if less severe than ERROR), or catching an exception (JDBC if severity is ERROR).

This code in turn might want to log an event (whether one received from the backend as just described, or one originating in the front-end code itself). To do that, it will probably use some convenient library available to it, such as java.util.logging in Java.

For code that is really running in a front end, that's the end of the story, but for code running in a backend PL, the story has only begun.

Handling logged events in a back-end PL

The first requirements for server-side code are the same as for the front end: it should be able to intercept, examine, and handle or not handle as appropriate, events that originate during its calls into the backend. PL/pgSQL makes a good example, with the trapping of errors built into the language, and inspection of (most) elements of the structure. Naturally, whatever isn't caught in PL/pgSQL code should continue propagating outward with all its structured information intact.

In PL/pgSQL, all of that applies to events with the exact severity ERROR: warnings/notices/etc. are invisible to PL/pgSQL code, as the elog logic sends those right out from under the PL and straight to the front-end client (conditioned only on the client_min_messages setting). That might not be always ideal: there can be a tension between ease of development/troubleshooting, favoring lots of logging, and confidentiality demands, which could require that some messages be edited or suppressed, which the PL code can't do if they zip right past it.

As for PL/Java

PL/Java is at parity with PL/pgSQL as far as the ability to catch error events from the backend (as Java SQLExceptions), or to propagate them up the stack without information loss if they are not caught, or are caught and rethrown without change.

Internally, it has good provisions for examining individual properties of the event (currently not including the schema, table, column, datatype, and constraint names that appeared in 9.3). However, these provisions aren't quite exposed yet to ordinary developers writing PL/Java code. Although documented in the wiki, they aren't accessible to PL/Java code compiled normally against pljava-api.jar; it would have to be compiled against the full pljava.jar and refer explicitly to ServerException and ErrorData in the org.postgresql.pljava.internal package.

(In passing, the current design where the magic only happens for a single ServerException subclass of SQLException stands in the way of implementing the categorized exceptions for JDBC 4.0.)

This is one area where a good API should be worked out and published. PL/Java provides a JDBC interface to the backend, because that is how the SQL/JRT standard is written, which PL/Java is meant to implement. As far as JDBC is concerned, access to these implementation-specific error details would be an extension, such as might be accessed by unwrap on a standard JDBC object. Being committed to a JDBC interface to PostgreSQL, it would be ideal to agree on the details with the other, front-end JDBC interfaces to PostgreSQL, as front-ends also receive finely structured PostgreSQL error details that client code may want to examine.

(Again in passing, the fact that PL/Java presents a JDBC interface could raise hopes that PL/Java functions are also able to examine warnings using the standard JDBC mechanism. At the moment, they aren't, and the PG_TRY/PG_CATCH constructs aren't enough to fix that, because only severity level ERROR is handled that way. PL/Java would have to also use the emit_log_hook in order to present a behavior analogous to JDBC on the front end. It would then pull ahead of PL/pgSQL on that dimension.)

How do the other PostgreSQL JDBCs give access to error details?

pgjdbc

If you have an SQLException and it can be cast to org.postgresql.util.PSQLException, then you can call getServerErrorMessage() on it, and get a ServerErrorMessage. Same deal if you have an SQLWarning that is castable to org.postgresql.util.PSQLWarning. (None of this is exactly trumpeted in the docs, and as you can see, the PSQLException and PSQLWarning links above are to privateapi pages, though the classes are public and accessible.)

Just as in PL/Java, the design here with a single PSQLException class is an obstacle to moving forward with the categorized exceptions in JDBC 4.0.

pgjdbc-ng

In pgjdbc-ng, categorized exceptions are partially implemented: at least there is one PGSQLIntegrityConstraintViolationException, and one PGSQLSimpleException for everything else. To allow for multiple categories, these share a common interface, PGSQLExceptionInfo. Calling code does not need to test for a bunch of implementation-specific class names, but can simply catch JDBC exceptions by their standard java.sql names, and test for castability to a single interface.

Interestingly, the interface gives access only to the column, constraint, datatype, schema, and table names available from PostgreSQL 9.3 onward (exactly the five things PL/Java currently doesn't expose!) and none of the much more anciently supported detail, hint, context, etc. And pgjdbc-ng doesn't supply any SQLWarning subclass that implements it.

All of the elements the protocol can forward are available on a different object, com.impossibl.postgres.protocol.Notice, but I am not sure user code has any way to get one. The classes that use it seem fairly internal.

Unlike both PL/Java's ErrorData and pgjdbc's ServerErrorMessage, in pgjdbc-ng both the PGSQLExceptionInfo and the Notice are mutable, providing setter methods as well as getters ... leading naturally into the next section.

Originating loggable events

Whether running client-side or in a server-side PL, it's often helpful to look at the details of events from below, as the last section explored. But for a server-side PL, it doesn't stop there, because server-side code is usually implementing logic that may have its own events to report, and as far as the client-side is concerned, those are just more events from the backend. Ideally, a PL function would be a "full citizen" and able to originate events (or rethrow caught ones wrapped in higher-level descriptions) with the same structure and quality one expects to see from PostgreSQL itself.

Here again, PL/pgSQL makes a good example. Using RAISE, code can generate an event with direct control of eleven of its most interesting attributes. (The ones not settable from PL/pgSQL, cursor positions, line numbers, and such, are at a level of detail few PL/pgSQL functions would want to work at anyway.)

PL/pgSQL has hit a kind of sweet spot with syntax that is so easy and clear it invites writing good messages that an ultimate user could find helpful. A statement like:

RAISE invalid_text_representation USING
  MESSAGE = 'Unrecognized prefix in telephone number ' || tno,
  DETAIL = 'The digits at the start of the number do not match any known '
           'international number prefix. Are you sure it is right?',
  HINT = 'If you are sure it''s right, ask the IT people if '
         'there is a more recent "ITU-T bulletin 994" they can load. '
         'Meanwhile, you can enter the number with a ! in front, '
         'but it may be flagged on data quality reports until fixed.';

is about as clear as can be in the code, as well as giving anyone who receives it a fighting chance at understanding what has happened.

As for PL/Java

PL/Java is not yet at parity with PL/pgSQL on this dimension. If PL/Java code catches any exception that began as a PostgreSQL ereport, that Java exception will wrap an ErrorData object; if that same exception is rethrown, it is transparently turned back into a PostgreSQL event and continues on its way without information loss. But there is no way for PL/Java code to create an exception with those properties. At best, it can create an ordinary SQLException, which will turn into a PostgreSQL log event using its SQLState and with its class name and message used as the message.

For any other kind of exception, only message is set (from the exception class name and message), and SQLState of XX000 for "internal error". When the origin is a Java exception, the severity will always be ERROR.

The other way for PL/Java code to originate a log event is to use the logging API. PL/Java presents mostly Java standard APIs for code to use, so in this case the API is java.util.logging, and PL/Java has wired it so log events created that way are handed off to the PostgreSQL logging system. (As a side effect of the way that system works, 'logging' any event with a severity that maps to PostgreSQL ERROR turns out to have the same effect as throwing it, while at any other severity it simply gets logged.)

When passed on to PostgreSQL, the details include the timestamp, class name or logger name, the message, and the stack trace of any associated Java throwable—but at present, all of that ends up strung together in the message attribute of the PostgreSQL event, using a severity mapped from the java.util.logging.Level. There are some other low-hanging-fruit mappings that could be made automatically, like the Java SourceClassName and SourceMethodName to PostgreSQL filename and funcname, but for the present they are not, and no other programmatic control over the created log event is yet available to PL/Java code.

Mapping of severity levels

Because there are only seven predefined java.util.logging.Levels and some of their names are different from PostgreSQL's, PL/Java maps them as follows:

		FINEST	FINER	FINE			INFO		WARNING	SEVERE
DEBUG5	DEBUG4	DEBUG3	DEBUG2	DEBUG1	LOG	COMMERROR	INFO	NOTICE	WARNING	ERROR	FATAL	PANIC

The Java level CONFIG isn't explicitly mapped, and anything that isn't explicit will map to the PostgreSQL level LOG. For completeness, I've shown the PostgreSQL levels FATAL and PANIC, though a good case could be made that no PL code should ever be allowed to use them.)

How can JDBC front-end code originate events?

While the front-end situation may be simpler (there is no need to make logging interoperate with server code in both directions, as PL/Java must), some of the same considerations can be carried over. Even on the client end, JDBC is not the client, it's still part of the stack. It may originate its own log messages or throw its own SQLExceptions for reasons other than events it forwards from the backend. Layers above it see a clean, consistent picture of "the database stack" when those events are of similar form, no matter the level they come from.

These days, there could even be another sophisticated layer or three sitting on top of JDBC and beneath the application code, and it might want to have the same facilities available to it.

A day that I would like to see—and I think it can be reached—is the day when a PostgreSQL error can be raised by the backend, caught by PL/Java, examined in all its structured detail by Java code using some extension of the JDBC API, rethrown, piped to the frontend JDBC and thrown again to the client code, caught there, and examined again in the same structured detail using the same extended API.

This becomes even more appealing, and maybe even more achievable, if PL/Java and a front-end JDBC work toward sharing more code.

when throwing

Being JDBC interfaces, both pgjdbc and pgjdbc-ng throw the standard JDBC SQLException (or, more precisely, subclasses of it), and create instances of the standard SQLWarning, which are collected and polled for, rather than thrown.

JDBC categorized exceptions are not yet supported by pgjdbc, and are partly supported by pgjdbc-ng.

The pgjdbc-ng exceptions that implement PGSQLExceptionInfo can be instantiated from scratch, and can have the column, constraint, datatype, schema, and table names set, as well as the JDBC standard SQLException attributes.

The pgjdbc PSQLException and PSQLWarning throwables can be instantiated from scratch, and can be constructed from a ServerErrorMessage that can also be built from scratch, allowing control over all of the same log event attributes that would be sent to the front-end for a backend event. However, the only way at present to construct that ServerErrorMessage is to supply a String in the exact format of the v3 protocol message that would come from the backend to represent the event. The constructed exception's message then is set to the entire result of toString on the ServerErrorMessage.

when logging

Logging in pgjdbc, which is older than java.util.logging, is done with the project-specific org.postgresql.core.Logger. This simple class identifies messages with a connection ID prefix, filters them by severity, timestamps them, and writes them to the DriverManager's LogWriter. Unlike java.util.logging, it doesn't do message formatting or internationalization, but a separate class org.postgresql.util.GT (also excluded from the public API) does both when used in calls to the logger. It gets translations from ResourceBundles, the same form java.util.logging uses.

When logging a PSQLException instance that carries a ServerErrorMessage, the result looks much like a message logged by the backend, because ServerErrorMessage.toString produces that form (and it was entirely stuffed into the message attribute of the exception).

Logging in pgjdbc-ng is done using java.util.logging, as in PL/Java, and in keeping with the appearance of java.util.logging-related API in JDBC itself starting in 4.1. In the existing examples in the code where a NoticeException or SQLException are passed directly to the logger, most of the available Notice or PGSQLExceptionInfo will not be seen, as far as I can see, as toString has not been overridden. ErrorUtils will create the SQLException subclasses using only the message and sqlstate from the original Notice. A NoticeException holds a reference to the Notice it was constructed from, and has a method to retrieve it, but the exception's message is set using only the additional String passed to its constructor.

What would a nice API look like?

By this point it should be clear why I've been writing about "log events" as one concept, when I might be expected to talk of log messages and exceptions as separate things. In PostgreSQL and in JDBC, both are forms the same information may take as it travels between A and B. It may be passed along as a message on a log channel, received, and thrown as an exception; something thrown as an exception can be caught and stuffed onto a log channel, where "log channel" might mean the ereport conveyor in the server code, the network protocol to the front end, the SQL warnings chain in JDBC, ....

This shapeshifting is not only possible but downright common in PostgreSQL and JDBC, and especially in PL/Java, where the same event may be batted about between those two forms repeatedly (how deep can the call stack get with PL/Java functions making SQL queries that call other functions also made in PL/Java?). And all of that is just fine as long as the conversion at each step is information-preserving and reversible.

An abstract `LogRecord` class (not derived from `Exception`)

We've seen that all three of (pgjdbc, pgjdbc-ng, PL/Java) include a class of some sort (ServerErrorMessage, Notice, and ErrorData, respectively) that is meant to carry all the information about a PostgreSQL log event in its intact structured form, and can be carried over a log channel or wrapped in an exception, and recovered at the end of a journey either way.

So, my proposal starts here: there should be such a class, and it should be documented and available as PostgreSQL extended JDBC API. For this discussion, I'll call it LogRecord. (There will be time for polishing name choices. There is an existing Java class LogRecord but of course the package is different.)

An interface for exceptions that carry `LogRecord`s

To allow moving forward with the categorized exceptions in JDBC 4.0, there needs to be an interface rather than a common parent class, and simply has a setter and getter for attaching a LogRecord to the exception. These would ordinarily not be used directly, but rather through methods of LogRecord.

Different concrete subclasses of `LogRecord`

PL/Java would supply its special concrete implementation that wraps a native error-data block; a front-end JDBC would supply one (or two) that are initialized from the on-the-wire protocol. In all cases, there would be a plain pure-Java one that can be filled in from scratch.

An `ereport`-like API for creating a `LogRecord` from scratch

... starting with a static method on LogRecord and with method chaining:

import static org.postgresql.something.LogRecord.ereport;
...
logrec = ereport(Level.ERROR).errcode(ERRCODE_DIVISION_BY_ZERO)
          .errmsg("You''ve tried to divide {0} by zero", dividend)
	  // .log()   OR
	  // .throwAs(SQLException.class)

Methods for the conversions into/out of log system or exception

Examples log() and throwAs() were seen above, with throwAs a convenience built on asException(...). If the LogRecord has been freshly constructed, asException creates the correct JDBC 4 categorized exception with a reference to the log record and vice versa. If that has happened already, it just returns the already created exception object.

The static fromException() method does the reverse: if the exception was created from a LogRecord originally (so, it implements the interface and its log record reference isn't null), just returns that original LogRecord. If not, creates a new LogRecord initialized as informatively as possible with whatever can be gleaned from the exception.

Those methods are what make possible the repeated batting around that an event might live through on its way from a deep call stack in PL/Java all the way out to a handler on the front end, without having serious identity crises. (It will be trickier inside PL/Java than I need to spend time on here, but should not be prohibitively so.)

In concert with existing standard API

I propose to converge on java.util.logging, and for this extended LogRecord class to be derived from the standard LogRecord.

(Soon below I will touch on how to "converge on java.util.logging" without serious disruption of pgjdbc, which currently uses the homegrown logging class.)

The specialized class will have several extra methods, and some overridden ones just to give it a reasonable default behavior when treated as an ordinary java.util.logging.LogRecord. For example its overridden getMessage method may do some formatting by default and return more information than just the message field, while different methods would be provided for a caller in the know to examine specific individual fields.

None of those differences stop it from being a valid instance of java.util.logging.LogRecord, and it can be passed into the logging system by calling log just like any other record. So can other, non-extended LogRecords and normal calls on the convenience methods of Logger, all at the same time. Code ported from other environments, knowing nothing of the extensions and using the standard logger API will work fine, and can be mixed with code using the extended features.

Call sites that aren't trying to make good user-visible messages (all the usual logger.finest("sent an M, got two dollar signs and a comma") kind of thing) don't have any need to change.

Using familiar level names

The current implementation in PL/Java maps PostgreSQL severity levels onto the (smaller set of) standard Levels. This adds another bit of cognitive load in the development process: I have to remember, for example,

SET log_min_messages TO DEBUG2;
SELECT javatest.logmessage('FINER', 'Hello world');

are talking about the same severity level, and it's an error to forget and use the other name either place, and the mapping loses information—it's not invertible.

A feature of the design of Level is it can be subclassed, and additional levels can be defined; the numeric values of the standard ones are spaced widely apart to allow new ones between them, and the parser even learns the names of new levels so they "just work". I propose defining the PostgreSQL levels whose names do not already match Level names, with a possible relationship like this:

PostgreSQL	Java
	ALL
	FINEST?
DEBUG5
	FINEST?
DEBUG4
DEBUG3
	FINER
DEBUG2
	FINE
DEBUG1
	CONFIG
LOG
COMMERROR
INFO	INFO
NOTICE
WARNING	WARNING
	SEVERE?
ERROR
	SEVERE?
FATAL
PANIC
	OFF

As you can see, I'm still considering arguments about where FINEST and SEVERE should go.

The effect, again, is that code from elsewhere that only expects the usual names from the standard library will work fine, code with more PostgreSQLy origins can use those familiar names, and a developer or admin can set the logging level using any of them, whichever seems more natural at the time.

Could `pgjdbc` move to `java.util.logging` without disruption?

I think so. The class org.postgresql.core.Logger could be kept, and simply delegate to other classes; the changes at points where log events are read off the wire and exceptions are created should be fairly internal and localized. I'd like to give it a shot.

I think the categorized exceptions in JDBC 4 are worth moving to, and offer a much nicer way for client code to distinguish what kind of thing went wrong, but changing that might actually turn out to be what needs the most coordination with client code. I would like to hope there isn't much client code out there that has linked to PSQLException by name (when it is only shown on "privateapi" javadocs), but I can only imagine there is some.

A place to pause

I have not managed to squeeze in every relevant thought here, but this is already long and enough to elicit some discussion and questions, and if I keep writing I will probably just be answering the wrong ones, so this seems a good place to stop and listen.

Performance tuning

2018-10-17T02:02:42-04:00

Tuning PL/Java performance

As of 2018, there is a strong selection of Java runtimes that can be used to back PL/Java, including at least:

Oracle's Java (and Hotspot JVM)
OpenJDK (with Hotspot JVM)
OpenJDK (with Eclipse OpenJ9 JVM)

These JVMs offer a wide variety of configurable options affecting both memory footprint and time performance of applications using PL/Java. The options include initial and limit sizes for different memory regions, aggressiveness of just-in-time and ahead-of-time compilation, choice of garbage-collection algorithm, and various forms of shared-memory caching of precompiled classes.

The formal PL/Java documentation contains a fairly extensive treatment of useful Hotspot settings, including a section on plausible minimum settings for memory footprint achievable with different class-sharing and garbage-collector settings. The documentation there of the comparable options and limits for OpenJ9 is more sparse at present.

This wiki page is intended as a clearinghouse for tuning tips and performance measurements for various PL/Java workloads and the available Java runtimes, that can be updated more actively between releases of the formal documentation.

Tip for quickly comparing runtime configurations

Once the PL/Java extension is installed in a database, in any newly-created session, the Java virtual machine is started on the first use of a PL/Java function. The JVM that is started, and how, are determined by the settings of pljava.* configuration variables in effect at that moment, most importantly:

pljava.libjvm_location selects which Java runtime will be used
pljava.vmoptions supplies the options to be passed to it

Therefore, all without exiting psql, a new Java runtime or combination of options can be tested by switching to a new connection with \c, setting those options differently, and again calling the PL/Java function of interest.

It can be convenient to include the settings on the psql \c line. For example, to time functionOfInterest() on two different Java runtimes:

\c "dbname=postgres options='-c pljava.libjvm_location=/path/to/oracle/.../libjvm.so'"
EXPLAIN ANALYZE SELECT functionOfInterest();
\c "dbname=postgres options='-c pljava.libjvm_location=/path/to/openj9/.../libjvm.so'"
EXPLAIN ANALYZE SELECT functionOfInterest();

For obvious reasons, the pljava.libjvm_location and pljava.vmoptions variables require privilege to set, so the connection needs to be made with superuser credentials.

Sample workload: Java XML manipulation

We will create a table containing a single XML document:

CREATE TABLE catalog_as_xml AS
SELECT schema_to_xml('pg_catalog', true, false, '') AS x;

In PostgreSQL 11beta3, the resulting document has the following size (after PL/Java and the example code have been loaded):

SELECT octet_length(xml_send(x)) AS uncompressed, pg_column_size(x) AS toasted
FROM catalog_as_xml;

uncompressed	toasted
14049808	1130828

A test query will return the string value of every element whose string value is exactly six characters (a query that may be artificial and contrived, but can be expressed nearly identically in XML Query (the standard-mandated language for SQL XMLTABLE) and in the PostgreSQL native XMLTABLE syntax, which is limited to XPath 1.0).

The baseline will be the query expressed in XPath 1.0 using the PostgreSQL XMLTABLE function:

EXPLAIN ANALYZE SELECT
  xmltable.*
FROM
  catalog_as_xml,
  XMLTABLE('//*[string-length(.) = 6]'
	   PASSING x
	   COLUMNS s text PATH 'string(.)'
	  );

It will be compared to the equivalent query expressed in XQuery 1.0 and the "xmltable" function defined in the not-built-by-default org.postgresql.pljava.example.saxon.S9 example, relying on the Saxon-HE library:

EXPLAIN ANALYZE SELECT
  xmltable.*
FROM
  catalog_as_xml,
  LATERAL (SELECT x AS ".") AS p,
  "xmltable"('//*[string-length(.) eq 6]',
	     PASSING => p,
	     COLUMNS => array[ 'string(.)' ]
	    )		  AS (  s text     );

The Java query will be run in both Oracle Java 8 (on the Hotspot JVM) and OpenJDK 8 (with the OpenJ9 JVM), with different choices of class-sharing options:

tag	description
`pg`	Baseline, PostgreSQL `XMLTABLE`
`hs`	Hotspot, no sharing
`hs-cds`	Hotspot, class data sharing (Java runtime classes only)
`hs-appcds`	Hotspot, AppCDS (commercial feature), Java runtime, PL/Java, Saxon
`j9`	OpenJ9, no `-Xquickstart`, no sharing
`j9q`	OpenJ9, `-Xquickstart`, no sharing
`j9s`	OpenJ9, no `-Xquickstart`, sharing (Java runtime, PL/Java, Saxon)
`j9qs`	OpenJ9, `-Xquickstart`, sharing (as above)

EXPLAIN ANALYZE reported timings in milliseconds:

iteration	`pg`	`hs`	`hs-cds`	`hs-appcds`	`j9`	`j9q`	`j9s`	`j9qs`
1st	908.231	1888.859	1837.186	1539.781	3250.965	3095.733	2443.649	2644.991
2nd	879.483	772.545	838.082	826.558	1229.200	1855.513	1073.335	1932.083
4th	881.302	664.422	688.487	673.037	1011.018	1708.208	987.191	1912.010
8th	880.766	640.940	643.535	632.260	962.517	1660.867	952.857	1870.506
16th	880.622	654.674	682.772	627.037	967.805	1656.651	943.923	1941.888

Discussion

The baseline native XMLTABLE implementation in PostgreSQL delivers consistent times over successive runs. Java timings improve over successive early runs, as the VM identifies and reoptimizes hot areas.
For all of the Java results, the first-iteration result includes the time to launch the Java virtual machine. For Hotspot, this gives a time to first result from 67% (best) to 108% (worst) longer than the native baseline.
All tested Hotspot configurations are outperforming the native implementation as soon as the next iteration, and eventually by 22% to 28%.
For this workload, Hotspot seems to have a striking performance advantage relative to OpenJ9. Possible explanations:
- Saxon is a mature and carefully-optimized library; are its optimizations extremely specific to Hotspot?
- PL/Java makes heavy use of JNI; could this pattern be less well handled in OpenJ9?
OpenJ9's -Xquickstart is a poor fit for this workload, as it suppresses JIT optimization so drastically that performance improves very little on successive runs.
The combination of -Xquickstart and -Xshareclasses for this workload is especially disappointing, probably because the two features, when combined, force the ahead-of-time compilation of all methods. That sounds promising, but not if the AOT code significantly underperforms what the optimizing JIT would generate.
Memory footprint was not compared. PL/Java's documentation already has a section on plausible memory settings for Hotspot, but not for OpenJ9, which has a good reputation for memory frugality. Exploration would be worthwhile.
There could be other workloads in which the Hotspot and OpenJ9 relative timings could be closer, or even reversed.
The procedure to set up class sharing for OpenJ9 is considerably simpler than to set up AppCDS for Hotspot, enough to make OpenJ9 an attractive choice for workloads where the performance is more comparable.

Variation by processor count

The results above were obtained with 6 available processor cores (12 hyperthreads). Here, the best Hotspot (h-) and OpenJ9 (j-) configurations from above (hs-appcds and j9s, respectively) are repeated for different numbers of cores and threads available to the backend process.

iteration	`h-4c8t`	`h-4c4t`	`h-2c4t`	`h-2c2t`	`h-1c2t`	`h-1c1t`
1st	1798.020	2140.068	1871.740	2760.872	2564.169	4306.058
2nd	780.182	827.379	825.943	1054.749	1064.300	1704.112
4th	661.740	672.415	662.259	786.407	734.421	756.054
8th	619.978	653.686	641.784	659.112	678.855	722.792
16th	619.824	647.092	664.287	671.862	639.365	651.502

iteration	`j-4c8t`	`j-4c4t`	`j-2c4t`	`j-2c2t`	`j-1c2t`	`j-1c1t`
1st	2413.365	2419.176	2411.006	2470.092	2457.868	3673.986
2nd	1108.991	1093.504	1050.731	1142.424	1072.635	2599.973
4th	969.465	1003.736	988.883	983.431	941.495	1032.896
8th	967.447	900.644	963.011	926.342	920.390	1032.316
16th	1113.963	932.503	1496.240	925.137	939.179	990.072

Discussion

Hotspot's initial startup uses parallelism to good advantage, so the startup time suffers when cores are limited, and especially when limited to one hardware thread. Interestingly, comparing sets with the same number of threads, in one case independent on an equal number of cores, and in the other case hyperthreads on half as many cores, the data above seem to favor the hyperthreaded case. More runs might reveal whether that apparent pattern persists.

Even with only one hardware thread available, Hotspot can still produce code that outperforms the native libxml2 no later than the fourth iteration.

OpenJ9, while not achieving the ultimate speeds of Hotspot on this workload, shows a first-run time that suffers less when limited to few CPUs. However, that advantage diminishes when taking the times of the first two runs into account.

Not shown in these tables, but as expected, the baseline PostgreSQL native XMLTABLE posted timings of 893 ms first run, 877 ms second run, consistently with the earlier values, even when limited to one core, one thread.

Notes on methodology

Platform

Intel Xeon X5650 2.67 GHz, 6 cores (12 hyperthreads), 24 GB RAM, Linux.

PostgreSQL installation, Java runtimes, database, and PL/Java and Saxon libraries and jars installed in an in-memory (tmpfs) filesystem.

Connection strings used for each test configuration

Note: the connection strings below for the Hotspot runs with AppCDS contain the option -XX:+UnlockCommercialFeatures because the runs were done on Oracle Java 8 where AppCDS is a commercial feature, and its use in production will need a license from Oracle. The same feature appears in OpenJDK with Hotspot starting in Java 10, where it is not a commercial feature, and does not require that -XX:+UnlockCommercialFeatures option; it is otherwise configured in the same way.

\c "dbname=postgres options='-c pljava.libjvm_location=/var/tmp/nohome/jre/lib/amd64/server/libjvm.so -c pljava.vmoptions=-Djava.home=/var/tmp/nohome/jre\\\ -XX:+UseSerialGC\\\ -XX:+DisableAttachMechanism\\\ -Xshare:off -c pljava.classpath=/var/tmp/nohome/pg11/share/postgresql/pljava/pljava-1.5.1-SNAPSHOT.jar:/var/tmp/nohome/jre/lib/Saxon-HE-9.8.0-14.jar'"
\c "dbname=postgres options='-c pljava.libjvm_location=/var/tmp/nohome/jre/lib/amd64/server/libjvm.so -c pljava.vmoptions=-Djava.home=/var/tmp/nohome/jre\\\ -XX:+UseSerialGC\\\ -XX:+DisableAttachMechanism\\\ -Xshare:on -c pljava.classpath=/var/tmp/nohome/pg11/share/postgresql/pljava/pljava-1.5.1-SNAPSHOT.jar:/var/tmp/nohome/jre/lib/Saxon-HE-9.8.0-14.jar'"
\c "dbname=postgres options='-c pljava.libjvm_location=/var/tmp/nohome/jre/lib/amd64/server/libjvm.so -c pljava.vmoptions=-Djava.home=/var/tmp/nohome/jre\\\ -XX:+UseSerialGC\\\ -XX:+DisableAttachMechanism\\\ -Xshare:on\\\ -XX:+UnlockCommercialFeatures\\\ -XX:+UseAppCDS\\\ -XX:SharedArchiveFile=/var/tmp/nohome/pljava.jsa -c pljava.classpath=/var/tmp/nohome/pg11/share/postgresql/pljava/pljava-1.5.1-SNAPSHOT.jar:/var/tmp/nohome/jre/lib/Saxon-HE-9.8.0-14.jar'"
\c "dbname=postgres options='-c pljava.libjvm_location=/var/tmp/jdk8u162-b12_openj9-0.8.0/jre/lib/amd64/j9vm/libjvm.so'"
\c "dbname=postgres options='-c pljava.libjvm_location=/var/tmp/jdk8u162-b12_openj9-0.8.0/jre/lib/amd64/j9vm/libjvm.so -c pljava.vmoptions=-Xquickstart'"
\c "dbname=postgres options='-c pljava.libjvm_location=/var/tmp/jdk8u162-b12_openj9-0.8.0/jre/lib/amd64/j9vm/libjvm.so -c pljava.vmoptions=-Xshareclasses:cacheDir=/var/tmp/pljavaj9cache'"
\c "dbname=postgres options='-c pljava.libjvm_location=/var/tmp/jdk8u162-b12_openj9-0.8.0/jre/lib/amd64/j9vm/libjvm.so -c pljava.vmoptions=-Xshareclasses:cacheDir=/var/tmp/pljavaj9cache\\\ -Xquickstart'"

Jars loaded into PL/Java

The PL/Java sqlj.install_jar function was used to install the PL/Java examples jar (giving it the name ex), with deploy => true to create the function declarations, and also the Saxon-HE-9.8.0-14.jar, naming it saxon.

The PL/Java application classpath (set with sqlj.set_classpath on the public schema), was ex during the Hotspot runs, and ex:saxon during the OpenJ9 runs. (For the Hotspot runs, the Saxon jar was placed on the system classpath by adding it to pljava.classpath instead, as explained below.)

Setup for Hotspot

The existing Hotspot installation on disk was copied to the tmpfs.
That invalidates the paths in the supplied classes.jsa shared archive that was generated when Java was installed to its location on disk, so the lib/amd64/server/classes.jsa file was removed from the copy and regenerated with java -Xshare:dump to contain the correct paths. That shared archive contains only classes of the Java runtime itself.
The shared archive for AppCDS, to include PL/Java implementation classes and the Saxon library as well as the Java runtime's classes, was generated in two steps:
1. A connection string with -XX:DumpLoadedClassList=filename was issued and the test query was executed, to populate the class list with the needed classes.
2. A new connection string with -Xshare:dump and -XX:SharedClassListFile naming the classlist file generated in the first step was issued, and then SELECT sqlj.get_classpath('public'); to trigger PL/Java loading. Java reads the class list and generates the shared archive, and the backend exits.
Because Hotspot AppCDS will share only classes from the system classpath, the pljava.classpath setting was altered to include Saxon-HE-9.8.0-14.jar as well as the PL/Java jar.
Because PL/Java's security manager disallows jar loading from arbitrary filesystem locations, the Saxon-HE-9.8.0-14.jar was placed in Java's jre/lib directory and the pljava.classpath referred to it there.
AppCDS will not share classes contained in a signed jar, and the distributed Saxon-HE-9.8.0-14.jar is signed, so the copy placed in jre/lib was "de-signed" by deleting its TE-050AC.SF entry and all Name:/Digest: sections from its MANIFEST.MF entry.

Setup for OpenJ9

The OpenJDK with OpenJ9 download was unzipped in the /var/tmp tmpfs.
Because PL/Java under OpenJ9 is able to share classes from the PL/Java application classpath (the one managed by sqlj.set_classpath) and not just the system classpath, there was no need to add the Saxon jar to pljava.classpath as there was for Hotspot. It was simply loaded with sqlj.install_jar under the name saxon, and put on the application classpath with SELECT sqlj.set_classpath('public', 'ex:saxon');.
Each set of runs with sharing (j9s, j9qs) was prepared by starting a fresh session with the same connection string to be used for that set, and the shareDir named in that connection string empty. Sixteen runs were made without timing, to populate the shared cache.
Then the same connection string was used again to start a fresh session, and the full set of 16 runs repeated and timed.

Connection strings generating `AppCDS` shared archive

See the earlier note concerning the -XX:+UnlockCommercialFeatures option, which is needed (with legal implications) to use the AppCDS feature in Oracle Java. The same feature appears in OpenJDK as of Java 10, without the need for that option or a commercial license.

\c "dbname=postgres options='-c pljava.libjvm_location=/var/tmp/nohome/jre/lib/amd64/server/libjvm.so -c pljava.vmoptions=-Djava.home=/var/tmp/nohome/jre\\\ -XX:+UseSerialGC\\\ -XX:+DisableAttachMechanism\\\ -Xshare:off\\\ -XX:DumpLoadedClassList=/var/tmp/nohome/pljava.classlist\\\ -XX:+UnlockCommercialFeatures\\\ -XX:+UseAppCDS -c pljava.classpath=/var/tmp/nohome/pg11/share/postgresql/pljava/pljava-1.5.1-SNAPSHOT.jar:/var/tmp/nohome/jre/lib/Saxon-HE-9.8.0-14.jar'"
\c "dbname=postgres options='-c pljava.libjvm_location=/var/tmp/nohome/jre/lib/amd64/server/libjvm.so -c pljava.vmoptions=-Djava.home=/var/tmp/nohome/jre\\\ -XX:+UseSerialGC\\\ -XX:+DisableAttachMechanism\\\ -Xshare:dump\\\ -XX:SharedClassListFile=/var/tmp/nohome/pljava.classlist\\\ -XX:+UnlockCommercialFeatures\\\ -XX:+UseAppCDS\\\ -XX:SharedArchiveFile=/var/tmp/nohome/pljava.jsa -c pljava.classpath=/var/tmp/nohome/pg11/share/postgresql/pljava/pljava-1.5.1-SNAPSHOT.jar:/var/tmp/nohome/jre/lib/Saxon-HE-9.8.0-14.jar'"

"De-signing" the Saxon jar

Hotspot's AppCDS will not share classes from a signed jar, so the signatures were removed from the Saxon jar with this procedure:

zip -d Saxon-HE-9.8.0-14.jar META-INF/TE-050AC.SF
unzip Saxon-HE-9.8.0-14.jar META-INF/MANIFEST.MF
ed META-INF/MANIFEST.MF <<END-COMMANDS
/^[[:space:]]/+1,$d
wq
END-COMMANDS
zip -u Saxon-HE-9.8.0-14.jar META-INF/MANIFEST.MF

Stripping the signatures does not impair the operation of the open-source Saxon-HE. It is conceivable that the commercial Saxon-PE or Saxon-EE would object to such treatment.

Setup for processor-count variation

Several Linux control groups were created as follows:

mkdir /sys/fs/cgroup/cpuset/{1c1t,1c2t,2c2t,2c4t,4c4t,4c8t}
for i in /sys/fs/cgroup/cpuset/?c?t
do
  echo 0 >$i/cpuset.mems
done
echo 0       >/sys/fs/cgroup/cpuset/1c1t/cpuset.cpus
echo 0,1     >/sys/fs/cgroup/cpuset/1c2t/cpuset.cpus
echo 0,2     >/sys/fs/cgroup/cpuset/2c2t/cpuset.cpus
echo 0-3     >/sys/fs/cgroup/cpuset/2c4t/cpuset.cpus
echo 0,2,4,6 >/sys/fs/cgroup/cpuset/4c4t/cpuset.cpus
echo 0-7     >/sys/fs/cgroup/cpuset/4c8t/cpuset.cpus

After each new backend was established with the appropriate \c line, its process ID was obtained with SELECT pg_backend_pid(); and echoed into cgroup.procs in the appropriate cpuset subdirectory.

The OpenJ9 class share was initially populated with one set of 16 runs before any timing was done. Timings were then done in the order shown, from 4c8t to 1c1t, and the -Xshareclasses option did not have readonly added for the timed sets. Because OpenJ9 can continue adding JIT hints to a class share during operation, it is possible that the later sets benefit from JIT hints added during the earlier ones.

Mapping an sql type to a java class

2017-07-15T19:24:21-04:00

Mapping an SQL type to a Java class

Using PL/Java, you can install a mapping between an arbitrary type and a Java class. There are two prerequisites for doing this:

You must know the storage layout of the SQL type that you are mapping.
The Java class that you map to must implement the interface java.sql.SQLData.

Mapping an existing SQL data type to a java class

Here is an example of how to map the PostgreSQL geometric point type to a Java class. We know that the point is stored as two float8's, the x and the y coordinate.

You can consult the postgresql source code when the exact layout of a basic type is unknown. I peeked at the point_recv function in file src/backend/utils/adt/geo_ops.c to determine the exact layout of the point type.

Once the layout is known, you can create the java.sql.SQLData implementation that uses the class java.sql.SQLInput to read and the class java.sql.SQLOutput to write data:

package org.postgresql.pljava.example;

import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;

public class Point implements SQLData {
   private double m_x;
   private double m_y;
   private String m_typeName;

   public String getSQLTypeName() {
      return m_typeName;
   }

   public void readSQL(SQLInput stream, String typeName) throws SQLException {
      m_x = stream.readDouble();
      m_y = stream.readDouble();
      m_typeName = typeName;
   }

   public void writeSQL(SQLOutput stream) throws SQLException {
      stream.writeDouble(m_x);
      stream.writeDouble(m_y);
   }

   /* Meaningful code that actually does something with this type was
    * intentionally left out.
    */
}

Finally, you install the type mapping using the add_type_mapping command:

SELECT sqlj.add_type_mapping('point', 'org.postgresql.pljava.example.Point');

You should now be able to use your new class. PL/Java will henceforth map any point parameter to the org.postgresql.pljava.example.Point class.

Creating a composite UDT and mapping it to a java class

Here is an example of a complex type created as a composite UDT.

CREATE TYPE javatest.complextuple AS (x float8, y float8);

SELECT sqlj.add_type_mapping('javatest.complextuple',
       'org.postgresql.pljava.example.ComplexTuple');

package org.postgresql.pljava.example;

import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;

public class ComplexTuple implements SQLData {
   private double m_x;
   private double m_y;
   private String m_typeName;

   public String getSQLTypeName()
   {
      return m_typeName;
   }

   public void readSQL(SQLInput stream, String typeName) throws SQLException
   {
      m_typeName = typeName;
      m_x = stream.readDouble();
      m_y = stream.readDouble();
   }

   public void writeSQL(SQLOutput stream) throws SQLException
   {
      stream.writeDouble(m_x);
      stream.writeDouble(m_y);
   }

   /* Meaningful code that actually does something with this type was
    * intentionally left out.
    */
}

Generating SQL automatically

The SQL shown above for this example will be written for you by the Java compiler, if the ComplexTuple class is simply annotated as a "mapped user-defined type" with the desired SQL name and structure:

@MappedUDT(schema="javatest", name="complextuple",
    structure={"x float8", "y float8"})
public class ComplexTuple implements SQLData {
  ...

Generating the SQL reduces the burden of keeping the definitions in sync in two places. See the hello world example for more.

Packaging tips

2017-06-20T20:42:57-04:00

Packaging tips

This wiki page can be used to gather issues and tips that pertain to building PL/Java packages for downstream distributions or repositories, in between updates to the packaging section in the versioned documentation.

Anyone producing a prebuilt PL/Java package is encouraged to announce its availability on the Prebuilt packages wiki page.

SQL functions

2017-06-20T01:15:33-04:00

Functions in the sqlj schema

install_jar

The install_jar command loads a jarfile from a location appointed by an URL into the SQLJ jar repository. It is an error if a jar with the given name already exists in the repository.

Usage

SELECT sqlj.install_jar(<jar_url>, <jar_name>, <deploy>);

Parameter	Description
jar_url	The URL that denotes the location of the jar that should be loaded
jar_name	This is the name by which this jar can be referenced once it has been loaded
deploy	True if the jar should be deployed according to a deployment descriptor, false otherwise

replace_jar

The replace_jar command will replace a loaded jar with another jar. Use it to update already loaded files. It's an error if the jar is not found.

Usage

SELECT sqlj.replace_jar(<jar_url>, <jar_name>, <redeploy>);

Parameter	Description
jar_url	The URL that denotes the location of the jar that should be loaded.
jar_name	The name of the jar to be replaced.
redeploy	True if the jar should be undeployed according to the deployment descriptor of the old jar and deployed according to the deployment descriptor of the new jar, false otherwise.

remove_jar

The remove_jar command will drop the jar from the jar repository. Any classpath that references this jar will be updated accordingly. It's an error if the jar is not found.

Usage

SELECT sqlj.remove_jar(<jar_name>, <undeploy>);

Parameter	Description
jar_name	The name of the jar to be removed.
undeploy	True if the jar should be undeployed according to a deployment descriptor, false otherwise.

get_classpath

The get_classpath command will return the classpath that has been defined for the given schema. NULL is returned if the schema has no classpath. It's an error if the given schema does not exist.

Usage

SELECT sqlj.get_classpath(<schema>);

Parameter	Description
schema	The name of the schema

set_classpath

The set_classpath command will define a classpath for the given schema. A classpath consists of a colon separated list of jar names. It's an error if the given schema does not exist or if one or more jar names references nonexistent jars.

Usage

SELECT sqlj.set_classpath(<schema>, <classpath>);

Parameter	Description
schema	The name of the schema.
classpath	The colon separated list of jar names.

add_type_mapping

The add_type_mapping command installs a mapping between a SQL type and a Java class. Once the mapping is in place, parameters and return values will be mapped accordingly. Please read Mapping an SQL type to a Java class for detailed information.

Usage

SELECT sqlj.add_type_mapping(<sql type>, <java class>);

Parameter	Description
sql type	The name of the SQL type. The name can be qualified with a schema (namespace). If the schema is omitted, it will be resolved according to the current setting of the search_path.
java class	The name of the class. The class must be found in the classpath in effect for the current schema

drop_type_mapping

The drop_type_mapping command removes a mapping between a SQL type and a Java class.

Usage

SELECT sqlj.drop_type_mapping(<sql type>);

Parameter	Description
sql type	The name of the SQL type. The name can be qualified with a schema (namespace). If the schema is omitted, it will be resolved according to the current setting of the search_path.

Note on jar URLs

The install_jar and replace_jar commands accept a URL (that must be reachable from the server) to a jar file. It is even possible, using the rules for jar URLs, to construct one that refers to a jar file within another jar file. For example:

jar:file:outer.jar!/inner.jar

However, Java's caching of the "outer" jar may frustrate attempts to replace or reload a newer version within the same session.

Logging

2017-06-19T21:53:51-04:00

Logging in PL/Java

PL/Java uses the standard java.util.logging.Logger Hence, you can write things like:

Logger.getAnonymousLogger().info(
    "Time is " + new Date(System.currentTimeMillis()));

At present, the logger is hardwired to a handler that maps the state of the PostgreSQL configuration setting log_min_messages to a valid Logger level and that outputs all messages using the backend function ereport().

Importantly, Java's Logger methods can quickly discard any message logged at a finer level than the one that was mapped from PostgreSQL's setting at the time PL/Java was first used in the current session. Such messages never even get as far as ereport(), even if the PostgreSQL setting is changed later.

So, if expected messages from Java code are not showing up, be sure that the setting in PostgreSQL, at the time of PL/Java's first use in the session, is fine enough that Java will not throw the messages away. Once PL/Java has started, the settings can be changed as desired and will control, in the usual way, what ereport does with the messages PL/Java delivers to it.

Through PL/Java 1.5.0, only the log_min_messages setting is used to set that Java cutoff level. Starting with 1.5.1, the cutoff level in Java is set (still only once at PL/Java startup) based on the finer of log_min_messages and client_min_messages.

The following mapping applies between the Logger levels and the PostgreSQL backend levels:

java.util.logging.Level	PostgreSQL level
SEVERE	ERROR
WARNING	WARNING
INFO	INFO
FINE	DEBUG1
FINER	DEBUG2
FINEST	DEBUG3

See Thoughts on logging for likely future directions in this area.

Functions returning sets

2017-04-19T22:30:04-04:00

Set-returning functions

Returning sets is tricky. You don't want to first build a set and then return it, since large sets would eat excessive resources. It's better to produce one row at a time. Incidentally, that's exactly what the PostgreSQL backend expects a function that RETURNS SETOF <type> to do. The <type> can be a scalar type such as an int, float or varchar, it can be a complex type, or a RECORD.

Returning a SETOF <scalar type>

In order to return a set of a scalar type, you need create a Java method that returns an implementation the java.util.Iterator interface.

CREATE FUNCTION javatest.getNames()
  RETURNS SETOF varchar
  AS 'foo.fee.Bar.getNames'
  IMMUTABLE LANGUAGE java;

The corresponding Java class:

package foo.fee;
import java.util.Iterator;

import org.postgresql.pljava.annotation.Function;
import static org.postgresql.pljava.annotation.Function.Effects.IMMUTABLE;

public class Bar
{
    @Function(schema="javatest", effects=IMMUTABLE)
    public static Iterator<String> getNames()
    {
        ArrayList<String> names = new ArrayList<>();
        names.add("Lisa");
        names.add("Bob");
        names.add("Bill");
        names.add("Sally");
        return names.iterator();
    }
}

Returning a SETOF <complex type>

A method returning a SETOF <complex type> must use either the interface org.postgresql.pljava.ResultSetProvider or org.postgresql.pljava.ResultSetHandle. The reason for having two interfaces is that they cater for optimal handling of two distinct use cases. The former is great when you want to dynamically create each row that is to be returned from the SETOF function. The latter makes sense when you want to return the result of an executed query.

Using the ResultSetProvider interface

This interface has two methods. The boolean assignRowValues(java.sql.ResultSet tupleBuilder, int rowNumber) and the void close() method. The PostgreSQL query evaluator will call the assignRowValues() repeatedly until it returns false or until the evaluator decides that it does not need any more rows. It will then call close().

You can use this interface the following way:

CREATE FUNCTION javatest.listComplexTests(int, int)
  RETURNS SETOF complexTest
  AS 'foo.fee.Fum.listComplexTest'
  IMMUTABLE LANGUAGE java;

The function maps to a static java method that returns an instance that implements the ResultSetProvider interface.

public class Fum implements ResultSetProvider
{
  private final int m_base;
  private final int m_increment;
  public Fum(int base, int increment)
  {
    m_base = base;
    m_increment = increment;
  }
  public boolean assignRowValues(ResultSet receiver, int currentRow)
  throws SQLException
  {
    // Stop when we reach 12 rows.
    //
    if(currentRow >= 12)
      return false;
    receiver.updateInt(1, m_base);
    receiver.updateInt(2, m_base + m_increment * currentRow);
    receiver.updateTimestamp(3, new Timestamp(System.currentTimeMillis()));
    return true;
  }
  public void close()
  {
  	// Nothing needed in this example
  }
  @Function(effects=IMMUTABLE, schema="javatest", type="complexTest")
  public static ResultSetProvider listComplexTests(int base, int increment)
  throws SQLException
  {
    return new Fum(base, increment);
  }
}

The listComplexTests(int base, int increment) method is called once. It may return null if no results are available, or an instance of the ResultSetProvider. Here the Fum class implements this interface so it returns an instance of itself. The method assignRowValues(ResultSet receiver, int currentRow) will then be called repeatedly until it returns false. At that time, close() will be called.

The currentRow parameter can be a convenience in some cases, and unnecessary in others. It will be passed as zero on the first call, and incremented by one on each subsequent call. If the ResultSetProvider is returning results from some source (like an Iterator) that remembers its own position, it can simply ignore currentRow.

Using the ResultSetHandle interface

This interface is similar to the ResultSetProvider interface in that it has a close() method that will be called at the end. But instead of having the evaluator call a method that builds one row at a time, this method has a method that returns a ResultSet. The query evaluator will iterate over this set and deliver its contents, one tuple at a time, to the caller until a call to next() returns false or the evaluator decides that no more rows are needed.

Here is an example that executes a query using a statement that it obtained using the default connection. The SQL looks like this:

CREATE FUNCTION javatest.listSupers()
  RETURNS SETOF pg_user
  AS 'org.postgresql.pljava.example.Users.listSupers'
  LANGUAGE java;

CREATE FUNCTION javatest.listNonSupers()
  RETURNS SETOF pg_user
  AS 'org.postgresql.pljava.example.Users.listNonSupers'
  LANGUAGE java;

And here is the Java code:

public class Users implements ResultSetHandle
{
  private final String m_filter;
  private Statement m_statement;

  public Users(String filter)
  {
    m_filter = filter;
  }

  public ResultSet getResultSet()
  throws SQLException
  {
    m_statement = DriverManager.getConnection("jdbc:default:connection")
      .createStatement();
    return m_statement.executeQuery("SELECT * FROM pg_user WHERE " + m_filter);
  }

  public void close()
  throws SQLException
  {
    m_statement.close();
  }

  @Function(schema="javatest", type="pg_user")
  public static ResultSetHandle listSupers()
  {
    return new Users("usesuper = true");
  }

  @Function(schema="javatest", type="pg_user")
  public static ResultSetHandle listNonSupers()
  {
    return new Users("usesuper = false");
  }
}

Parallel query and PLJava

2016-10-30T18:15:44-04:00

Parallel query and PL/Java

PL/Java 1.5.1 adds support for PostgreSQL 9.6, and with that comes the possibility of using PL/Java functions in parallel queries. Simple testing shows that this actually works; PL/Java functions can even be declared PARALLEL SAFE if they meet the requirements, and executed in the parallelized parts of queries.

However, this is a substantial change to conditions in which PL/Java was developed, so this wiki page is here to collect the notes that are likely to come with experience using this new capability. Such experience might include empirically-determined, good values for parallel_setup_cost, nonobvious cases where a function should not be declared RESTRICTED or SAFE, and so on.

Notes go here

Preview of new documentation

Until PL/Java 1.5.1 is released, here is a preview of the new section of the user's guide.

PL/Java in parallel query or background worker

With some restrictions, PL/Java can be used in parallel queries, from PostgreSQL 9.6, and in some background worker processes (as introduced in PostgreSQL 9.3, though 9.5 or later is needed for support in PL/Java).

Background worker processes

Because PL/Java requires access to a database containing the sqlj schema, PL/Java is only usable in a worker process that initializes a database connection, which must happen before the first use of any function that depends on PL/Java.

Parallel queries

Like any user-defined function, a PL/Java function can be annotated with a level of "parallel safety", UNSAFE by default.

When a function labeled UNSAFE is used in a query, the query cannot be parallelized at all. If a query contains a function labeled RESTRICTED, parts of the query may execute in parallel, but the part that calls the RESTRICTED function will be executed only in the lead process. A function labeled SAFE may be executed in every process participating in the query.

Parallel setup cost

PostgreSQL parallel query processing uses multiple operating-system processes, and these processes are new for each parallel query. If a PL/Java function is labeled PARALLEL SAFE and is pushed by the query planner to run in the parallel worker processes, each new process will start a Java virtual machine. The cost of doing so will reduce the expected advantage of parallel execution.

To inform the query planner of this trade-off, the value of the PostgreSQL configuration variable parallel_setup_cost should be increased. The startup cost can be minimized with attention to the PL/Java VM option recommendations, including class data sharing.

Limits on `RESTRICTED`/`SAFE` function behavior

There are stringent limits on what a function labeled RESTRICTED may do, and even more stringent limits on what may be done in a function labeled SAFE. The PostgreSQL manual describes the limits in the section Parallel Labeling for Functions and Aggregates.

While PostgreSQL does check for some inappropriate operations from a PARALLEL SAFE or RESTRICTED function, for the most part it relies on functions being labeled correctly. When in doubt, the conservative approach is to label a function UNSAFE, which can't go wrong. A function mistakenly labeled RESTRICTED or SAFE could produce unpredictable results.

Internal workings of PL/Java

While a given PL/Java function itself may clearly qualify as RESTRICTED or SAFE by inspection, there may still be cases where a forbidden operation results from the internal workings of PL/Java itself. This has not been seen in testing (simple parallel queries with RESTRICTED or SAFE PL/Java functions work fine), but to rule out the possibility would require a careful audit of PL/Java's code. Until then, it would be prudent for any application involving parallel query with RESTRICTED or SAFE PL/Java functions to be first tested in a non-production environment.

Creating a scalar udt in java

2016-01-29T22:41:22-05:00

Creating a scalar (or, base) user-defined type

This text assumes that you have some familiarity with how scalar types are created and added to the PostgreSQL type system. For more info on that topic, please read this chapter in the PostgreSQL docs.

Creating new scalar type using Java functions is very similar to how they are created using C functions from an SQL perspective but of course very different when looking at the actual implementation. Java stipulates that the mapping between a Java class and a corresponding SQL type should be done using the interfaces java.sql.SQLData, java.sql.SQLInput, and java.sql.SQLOutput and that is what PL/Java is using. In addition, the PostgreSQL type system stipulates that each type must have a textual representation.

Let us create a type called javatest.complex (similar to the complex type used in the PostgreSQL documentation). The name of the corresponding Java class will be org.postgresql.pljava.example.ComplexScalar.

The Java code for the scalar type

Prerequisites for the Java implementation

The java class for a scalar UDT must implement the java.sql.SQLData interface. In addition, it must also implement a method static T parse(String stringRepresentation, String typeName) where T will be the name of the class--that is, parse will create and return an instance of the class--and the java.lang.String toString() method. The toString() method must return something that the parse() method can parse.

package org.postgresql.pljava.example;

import java.io.IOException;
import java.io.StreamTokenizer;
import java.io.StringReader;
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
import java.util.logging.Logger;

import org.postgresql.pljava.annotation.Function;
import org.postgresql.pljava.annotation.SQLType;
import org.postgresql.pljava.annotation.BaseUDT;

import static org.postgresql.pljava.annotation.Function.Effects.IMMUTABLE;
import static
       org.postgresql.pljava.annotation.Function.OnNullInput.RETURNS_NULL;

@BaseUDT(schema="javatest", name="complex",
         internalLength=16, alignment=BaseUDT.Alignment.DOUBLE)
public class ComplexScalar implements SQLData
{
   private double m_x;
   private double m_y;
   private String m_typeName;

   @Function(effects=IMMUTABLE, onNullInput=RETURNS_NULL)
   public static ComplexScalar parse(String input, String typeName)
   throws SQLException
   {
      try
      {
         StreamTokenizer tz = new StreamTokenizer(new StringReader(input));
         if(tz.nextToken() == '('
         && tz.nextToken() == StreamTokenizer.TT_NUMBER)
         {
            double x = tz.nval;
            if(tz.nextToken() == ','
            && tz.nextToken() == StreamTokenizer.TT_NUMBER)
            {
               double y = tz.nval;
               if(tz.nextToken() == ')')
               {
                  return new ComplexScalar(x, y, typeName);
               }
            }
         }
         throw new SQLException("Unable to parse complex from string \""
	    + input + '"');
      }
      catch(IOException e)
      {
         throw new SQLException(e.getMessage());
      }
   }

   public ComplexScalar()
   {
   }

   public ComplexScalar(double x, double y, String typeName)
   {
      m_x = x;
      m_y = y;
      m_typeName = typeName;
   }

   @Override
   public String getSQLTypeName()
   {
      return m_typeName;
   }

   @Function(effects=IMMUTABLE, onNullInput=RETURNS_NULL)
   @Override
   public void readSQL(SQLInput stream, String typeName) throws SQLException
   {
      m_x = stream.readDouble();
      m_y = stream.readDouble();
      m_typeName = typeName;
   }

   @Function(effects=IMMUTABLE, onNullInput=RETURNS_NULL)
   @Override
   public void writeSQL(SQLOutput stream) throws SQLException
   {
      stream.writeDouble(m_x);
      stream.writeDouble(m_y);
   }

   @Function(effects=IMMUTABLE, onNullInput=RETURNS_NULL)
   @Override
   public String toString()
   {
      s_logger.info(m_typeName + " toString");
      StringBuffer sb = new StringBuffer();
      sb.append('(');
      sb.append(m_x);
      sb.append(',');
      sb.append(m_y);
      sb.append(')');
      return sb.toString();
   }

   /* Meaningful code that actually does something with this type was
    * intentionally left out.
    */
}

The class itself is annotated with @BaseUDT, giving its SQL schema and name, and the length and alignment needed for its internal, stored form.

Because the compiler knows the class is a BaseUDT, it already expects the parse, toString, readSQL, and writeSQL methods to be present, and will generate the correct SQL to declare them as functions to PostgreSQL. The @Function annotations are only there to declare the immutability and on-null-input behavior for those methods, because those values are not the defaults when declaring a function.

User guide

2015-12-22T20:53:04-05:00

User guide (wiki version)

The first reference should be the user guide at the main project site.

Here at this wiki version, you may still find useful information that is not yet migrated to the project site. Some of the information here may be outdated. Wiki content is slowly migrating to the main site as it is checked and brought up to date.

Utilities

The PL/Java Deployer is a Java client program that helps you deploy PL/Java in the database. It is now obsolescent; for current instructions on installing PL/Java, see the installation guide at the main project site.
SQL functions that can be executed from SQL

Authoring

Debugging

Troubleshooting

Sporadic hanging

Savepoints

2015-12-22T20:48:35-05:00

Savepoints

PostgreSQL savepoints are exposed using the standard setSavepoint() and releaseSavepoint() methods on the java.sql.Connection interface. Two restrictions apply:

A savepoint must be rolled back or released in the function where it was set.
A savepoint must not outlive the function where it was set.

"Function" here refers to the PL/Java function that is called from SQL. The restrictions do not prevent the Java code from being organized into several methods, but the savepoint cannot survive the eventual return from Java to the SQL caller.

Function mapping

2015-12-22T20:48:35-05:00

Functions

A Java function is declared with the name of a class and a public static method on that class. The class will be resolved using the classpath that has been defined for the schema where the function is declared. If no classpath has been defined for that schema, the public schema is used. Please note that the system classloader will take precedence always. There is no way to override classes loaded with that loader.

The following function can be declared to access the static method getProperty on the java.lang.System class:

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

SELECT getsysprop('java.version');

Both the parameters and the return value can be explicitly stated so the above example could also have been written:

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

This way of declaring the function is useful when the default mapping is inadequate. PL/Java will use a standard PostgreSQL explicit cast when the SQL type of the parameter or return value does not correspond to the Java type defined in the mapping.

Note: the "explicit cast" here referred to is not accomplished by creating an actual SQL CAST expression, but by (mostly) equivalent means. At the time of this writing, two special cases are not yet implemented.

SQL generation

The simplest way to write the SQL function declaration that corresponds to your Java code is to have the Java compiler do it for you:

public class Hello {
  @Function
  public static String hello(String toWhom) {
    return "Hello, " + toWhom + "!";
  }
}

When this function is compiled, a "deployment descriptor" containing the right SQL function declaration is also produced. When it is included in a jar file with the compiled code, PL/Java's sqlj.install_jar function will create the SQL function declaration at the same time it loads the jar. See the full hello world example for more.

Technology in brief

2015-12-22T20:48:35-05:00

A function or trigger in SQL resolves to a static method in a Java class. In order for the function to execute, the appointed class must be installed in the database. PL/Java adds a set of functions that helps installing and maintaining the java classes. Classes are loaded into the database from normal Java archives (AKA jars). A Jar may optionally contain a deployment descriptor that in turn contains SQL commands to be executed when the jar is deployed/undeployed. The functions are modeled after the standards proposed for SQL 2003.

PL/Java implements a standardized way of passing parameters and return values. Complex types and sets are passed using the standard JDBC ResultSet class. Great care has been taken not to introduce any proprietary interfaces unless absolutely necessary so that Java code written using PL/Java becomes as database agnostic as possible.

A JDBC driver is included in PL/Java. This driver is written directly on top of the PostgreSQL internal SPI routines. This driver is essential since it's very common for functions and triggers to reuse the database. When they do, they must use the same transactional boundaries that where used by the caller.

PL/Java is optimized for performance. The Java virtual machine executes within the same process as the backend itself. This vouches for a very low call overhead. PL/Java is designed with the objective to enable the power of Java to the database itself so that database intensive business logic can execute as close to the actual data as possible.

The standard Java Native Interface (JNI) is used when bridging calls from the backend into the Java VM and vice versa. Please read the rationale behind The choice of JNI and a more in-depth discussion about some implementation details.

The versions of PostgreSQL and Java targeted by current PL/Java development can be reviewed on the versions page.

Running the pl java sample tests

2015-12-22T20:48:35-05:00

Running PL/Java sample tests

The PL/Java Source distribution contains a couple of rudimentary tests. The tests are divided into two jar files. One is the client part found in the test.jar. It contains some methods that executes SQL statements and prints the output (all contained there can of course also be executed from psql or any other client). The other is the examples.jar which contains the sample code that runs in the backend. The latter must be installed in the database in order to function. An easy way to do this is to use psql and issue the command:

SELECT sqlj.install_jar('file:///some/directory/examples.jar', 'samples',  true);

Please note that the deployment descriptor stored in examples.jar will attempt to create the schema javatest so the user that executes the sqlj.install_jar command must have permission to do that. A number of tests now run from the deployment descriptor itself, so by the time install_jar finishes, PL/Java will have completed those tests.

Once loaded, you must also set the classpath used by the PL/Java runtime. This classpath is set per schema (namespace). A schema that lacks a classpath will default to the classpath that has been set for the public schema. The tests will use the schema javatest. To define the classpath for this schema, simply use psql and issue the command:

SELECT sqlj.set_classpath('javatest', 'samples');

The first argument is the name of the schema, the second is a colon separated list of jar names. The names must reflect jars that are installed in the system.

NOTE: If you don't use schemas, you must still issue the set_classpath command to assign a correct classpath to the 'public' schema. This can only be done by a super user.

Now, you should be able to run the client test application:

java -cp <path including the jdbc driver and test.jar> org.postgresql.pljava.test.Tester

Returning complex types

2015-12-22T20:48:35-05:00

Returning complex types

The SQL-2003 draft suggest that a complex return value is handled as an IN/OUT parameter and PL/Java implements it that way. If you declare a function that returns a complex type, you will need to use a Java method with boolean return type with a last parameter of type java.sql.ResultSet added after all of the method's visible parameters. The output parameter will be initialized to an updatable ResultSet that contains exactly one row.

CREATE FUNCTION createComplexTest(int, int)
  RETURNS complexTest
  AS 'foo.fee.Fum.createComplexTest'
  IMMUTABLE LANGUAGE java;

The PL/Java method resolver will now find the following method in class foo.fee.Fum:

public static boolean complexReturn(int base, int increment, ResultSet receiver)
throws SQLException
{
  receiver.updateInt(1, base);
  receiver.updateInt(2, base + increment);
  receiver.updateTimestamp(3, new Timestamp(System.currentTimeMillis()));
  return true;
}

The return value denotes if the receiver should be considered as a valid tuple (true) or NULL (false).

Exception handling

2015-12-22T20:48:35-05:00

Exception handling

You can catch and handle an exception in the PostgreSQL back-end just like any other exception. The back-end ErrorData structure is exposed as a property in a ServerException class derived from java.sql.SQLException, and the Java try/catch mechanism is synchronized with the back-end mechanism.

Note: for several reasons (see Thoughts on logging for background), referring to ServerException and ErrorData from your code is not currently recommended, and in the future may become impossible. An improved mechanism is expected in a future release. Until then, using only the standard Java API of java.sql.SQLException and its standard attributes (such as SQLState) is recommended wherever possible.

PL/Java will always catch exceptions that you don't. They will cause a PostgreSQL error and the message is logged using the PostgreSQL logging utilities. The stack trace of the exception will also be printed if the PostgreSQL configuration parameter log_min_messages is set to DEBUG1 or lower.

Important Note:

You will not be able to continue executing back-end functions until your function has returned and the error has been propagated when the back-end has generated an exception unless you have used a save-point. When a save-point is rolled back, the exceptional condition is reset and execution can continue.

Default type mapping

2015-12-22T20:48:35-05:00

Scalar types

Scalar types are mapped in a straight forward way. Here's a table of the current mappings (will be updated as more mappings are implemented).

PostgreSQL	Java
bool	boolean
"char"	byte
int2	short
int4	int
int8	long
float4	float
float8	double
char	java.lang.String
varchar	java.lang.String
text	java.lang.String
name	java.lang.String
bytea	byte[]
date	java.sql.Date
time	java.sql.Time (stored value treated as local time)
timetz	java.sql.Time
timestamp	java.sql.Timestamp (stored value treated as local time)
timestamptz	java.sql.Timestamp

Array scalar types

All scalar types can be represented as an array. Although PostgreSQL will allow that you declare multi dimensional arrays with fixed sizes, PL/Java will still treat all arrays as having one dimension (with the exception of the bytea[] which maps to byte[][]). The reason for this is that the information about dimensions and sizes is not stored anywhere and not enforced in any way. You can read more about this in the PostgreSQL Documentation.

However, the current implementation does not enforce the array size limits — the behavior is the same as for arrays of unspecified length.

Actually, the current implementation does not enforce the declared number of dimensions either. Arrays of a particular element type are all considered to be of the same type, regardless of size or number of dimensions. So, declaring number of dimensions or sizes in CREATE TABLE is simply documentation, it does not affect run-time behavior.

PostgreSQL	Java
bool[]	boolean[]
"char"[]	byte[]
int2[]	short[]
int4[]	int[]
int8[]	long []
float4[]	float[]
float8[]	double[]
char[]	java.lang.String[]
varchar[]	java.lang.String[]
text[]	java.lang.String[]
name[]	java.lang.String[]
bytea[]	byte[][]
date[]	java.sql.Date[]
time[]	java.sql.Time[] (stored value treated as local time)
timetz[]	java.sql.Time[]
timestamp[]	java.sql.Timestamp[] (stored value treated as local time)
timestamptz[]	java.sql.Timestamp[]

Domain types

A domain type will be mapped in accordance with the type that it extends unless you have installed a specific mapping to override that behavior.

Pseudo types

PostgreSQL	Java
"any"	java.lang.Object
anyelement	java.lang.Object
anyarray	java.lang.Object[]
cstring	java.lang.String
record	java.sql.ResultSet
trigger	org.postgresql.pljava.TriggerData (see Triggers)

NULL handling of primitives

The scalar types that map to Java primitives can not be passed as null values. To enable this, those types can have an alternative mapping. You enable this mapping by explicitly denoting it in the method reference.

CREATE FUNCTION trueIfEvenOrNull(integer)
  RETURNS bool
  AS 'foo.fee.Fum.trueIfEvenOrNull(java.lang.Integer)'
  LANGUAGE java;

In java, you would have something like:

package foo.fee;

public class Fum
{
  static boolean trueIfEvenOrNull(Integer value)
  {
    return (value == null)
      ? true
      : (value.intValue() % 1) == 0;
  }
}

The following two statements should both yield true:

SELECT trueIfEvenOrNull(NULL);
SELECT trueIfEvenOrNull(4);

In order to return null values from a Java method, you simply use the object type that corresponds to the primitive (i.e. you return java.lang.Integer instead of int). The PL/Java resolver mechanism will find the method regardless. Since Java cannot have different return types for methods with the same name, this does not introduce any ambiguities.

Starting with PostgreSQL version 8.2 it will be possible to have NULL values in arrays. PL/Java will handle that the same way as with normal primitives, i.e. you can declare methods that use a java.lang.Integer[] parameter instead of an int[] parameter.

Composite types

A composite type will be passed as a read-only java.sql.ResultSet with exactly one row by default. The ResultSet will be positioned on its row so no call to next() should be made. The values of the composite type are retrieved using the standard getter methods of the ResultSet. Example:

CREATE TYPE compositeTest
  AS(base integer, incbase integer, ctime timestamptz);

CREATE FUNCTION useCompositeTest(compositeTest)
  RETURNS VARCHAR
  AS 'foo.fee.Fum.useCompositeTest'
  IMMUTABLE LANGUAGE java;

In class Fum we add the static following static method The foo.fee.Fum.useCompositeTest method:

public static String useCompositeTest(ResultSet compositeTest)
throws SQLException
{
  int base = compositeTest.getInt(1);
  int incbase = compositeTest.getInt(2);
  Timestamp ctime = compositeTest.getTimestamp(3);
  return "Base = \\"" + base +
    "\\", incbase = \\"" + incbase +
    "\\", ctime = \\"" + ctime + "\\"";
}

Default mapping

Types that have no mapping are currently mapped to java.lang.String. The standard PostgreSQL textin/textout routines registered for respective type will be used when the values are converted.

Using jdbc

2015-12-22T20:48:35-05:00

Using JDBC

PL/Java contains a JDBC driver that maps to the PostgreSQL SPI functions. A connection that maps to the current transaction can be obtained using the following statement:

Connection conn = DriverManager.getConnection("jdbc:default:connection");

From there on, you can prepare and execute statements just like with any other JDBC connection. There are a couple of limitations though:

The transaction cannot be managed in any way. Thus, you cannot use methods on the connection such as:
- commit()
- rollback()
- setAutoCommit()
- setTransactionIsolation()
A savepoint cannot outlive the function in which it was set and it must also be rolled back or released by that same function.
ResultSets returned from executeQuery() are always FETCH_FORWARD and CONCUR_READ_ONLY.
Meta-data became available in PL/Java 1.1.
CallableStatement (for stored procedures) is not yet implemented.
Clob/Blob types need more work. byte[] and String works fine for bytea/text respectively. A more efficient mapping is planned where the actual array is not copied.

Triggers

2015-12-22T20:48:35-05:00

Triggers

The method signature of a trigger is predefined. A trigger method must always return void and have a org.postgresql.pljava.TriggerData parameter. No more, no less. The TriggerData interface provides access to two java.sql.ResultSet instances; one representing the old row and one representing the new. The old row is read-only and the new row is updateable.

The ResultSets are only available for triggers that are fired ON EACH ROW. Delete triggers have no new row, and insert triggers have no old row. Only update triggers have both.

In addition to the sets, several boolean methods exists to gain more information about the trigger.

CREATE TABLE mdt (
  id int4,
  idesc text,
  moddate timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL);

CREATE FUNCTION moddatetime()
  RETURNS trigger
  AS 'org.postgresql.pljava.example.Triggers.moddatetime'
  LANGUAGE java;

CREATE TRIGGER mdt_moddatetime
  BEFORE UPDATE ON mdt
  FOR EACH ROW
  EXECUTE PROCEDURE moddatetime (moddate);

And here is the corresponding Java code:

/**
 * Update a modification time when the row is updated.
 */
static void moddatetime(TriggerData td)
throws SQLException
{
  if(td.isFiredForStatement())
    throw new TriggerException(td, "can't process STATEMENT events");

  if(td.isFiredAfter())
    throw new TriggerException(td, "must be fired before event");

  if(!td.isFiredByUpdate())
    throw new TriggerException(td, "can only process UPDATE events");

  ResultSet _new = td.getNew();
  String[] args = td.getArguments();
  if(args.length != 1)
    throw new TriggerException(td, "one argument was expected");

  _new.updateTimestamp(args[0], new Timestamp(System.currentTimeMillis()));
}

Installation guide

2015-12-22T20:48:35-05:00

Installing PL/Java

For the most current information on installing PL/Java, see the installation guide on the project information site.

Complete uninstall

2015-12-22T20:48:35-05:00

In order to completely uninstall PL/Java you need to have super user privileges on the database. Here's how you do it.

Get rid of the sqlj schema and all objects depending on it.
- If you installed PL/Java with CREATE EXTENSION pljava then drop it with DROP EXTENSION pljava CASCADE
- If you installed PL/Java with a LOAD command, then drop it with DROP SCHEMA sqlj CASCADE
Caution: Either command will drop the PL/Java schema and language declarations, all jars you may have loaded, all functions and types they provided, and everything else in your database that depends on any of those things.

You can try either command without CASCADE first, to see a list of what would be dropped.
Remove any settings of PL/Java variables (configuration variables with names starting with pljava.) that you may have changed from their defaults.
- If you had set a variable var for a particular database using ALTER DATABASE dbname SET var ... then reset it using ALTER DATABASE dbname RESET var.
- If you had set it for the whole cluster using ALTER SYSTEM SET var ... then reset it using ALTER SYSTEM RESET var and, when you have reset all, use SELECT pg_reload_conf().
- If you had set PL/Java variables by editing the configuration file (particularly on PostgreSQL before 9.2, where this is the only available method), remove the settings from the file, then use SELECT pg_reload_conf() in SQL.
- The variable dynamic_library_path is not specific to PL/Java, but if you added a directory to it for the sake of PL/Java, undo that.
Remove the PL/Java files from the file system.
- If you installed PL/Java with a package manager, uninstall it the same way.
- Otherwise, remove the installed files from wherever you installed them.

The choice of JNI

2015-12-22T20:48:35-05:00

Rationale behind using JNI as opposed to threads in a remote JVM process.

Reasons to use a high level language like Java™ in the backend

A large part of the reason why JNI was chosen in favor of an RPC based, single JVM solution was due to the expected use-cases. Enterprise systems today are almost always 3-tier or n-tier. Database functions, triggers, and stored procedures are mechanisms that extend the functionality of the backend tier. They typically rely on a tight integration with the database due to a very high rate of interactions and execute inside of the database largely to limit the number of interactions between the middle tier and the backend tier. Some typical use-cases:

Referential integrity enforcement. Using Java, referential integrity can be implemented that goes beyond what can be done using the standard SQL semantics. It may involve checking XML documents, enforcing some meta-driven rule system, or other complex tasks that put high demands on the implementation language.
Advanced pattern recognition. Soundex, image comparison, etc.
XML support functions. Java comes with a lot of XML support. Parsers etc. are readily available.
Support functions for O/R mappers. A variety of support can be implemented depending on design. One example is an O/R mapper that allows methods on persistent objects. A lot can be gained if such methods are pushed down and executed within the database. Consider the following (OQL):

SELECT AVG(x.salary - x.computeTax()) FROM Employee x WHERE x.salary > 120000;

Pushing the computeTax logic down to the database instead of computing it in the middle tier (where much or the O/R logic resides) is a huge gain from a performance standpoint. The statement could be transformed into SQL as:

SELECT AVG(x.salary - computeTax(x.salary)) FROM Employee x WHERE x.salary > 120000;

As a result, very few interactions (typically only one) need to be made between the middle and the backend tier.

Views and indexes making use of computed values. In the above example and index could be created on computeTax(x.salary) and a view could express that as net_income.
Message queue management. Delivering or fetching things using message queues or other delivery mechanisms. As with most interactions with other processes, this requires transaction coordination of some kind.

One might argue that since a JVM often is present running an app-server in the middle tier, would it not be more efficient if that JVM also executed the database functions and triggers? In my opinion, this would be very bad. One major reason for moving execution down to the database is performance (by minimizing the number of roundtrips between the app-server and the database) another is separation of concern. Referential data integrity and other ways to extend the functionality of the database should not be the app-servers concern, it belongs in the backend tier. Other aspects like database versus app-server administration, replication of code and permission changes for functions, and running different tiers on different servers, makes it even worse.

Resource consumption

Having one JVM per connection instead of one thread per connection running in the same JVM will undoubtedly consume more resources. There are however a couple of facts that must be remembered:

The overhead of multiple processes is already present due to the fact that each connection is a process in a PostgreSQL system.
In order to keep connections separated in case they run in the same JVM, some kind of "compartments" must be created. Either you create them using parallel class loader chains (similar to how EAR files are managed in an EJB server) or you use a less protective model similar to a servlet engine. In order to get a separation that is comparable to what you get using separate JVM's, you must chose the former. That consumes some resources.
The JVM has undergone a series of improvements in order to reduce footprint and startup time. Some significant improvements where made in Java 1.4 and Java 1.5 introduces Java Heap Self Tuning, Class Data Sharing, and Garbage Collector Ergonomics (read more here), technologies that will minimize the startup time and make the JVM adopt its resource consumption in a much improved way.
PL/Java can make use of the GCJ. Using this technology, all core classes will be compiled into binaries and optionally pre-loaded by the postmaster. It also means that all modules that are loaded using the install_jar/replace_jar can be compiled into real shared objects. Finally, it means that the footprint for each "JVM" will be significantly decreased.

Note: GCJ is no longer targeted by current PL/Java development.

Connection pooling

In the Java community you are very likely to use a connection pool. The pool will ensure that the number of connections stays as low as possible and that connections are reused (instead of closed and reestablished). New JVMs are started rarely.

Connection isolation

Separate JVMs gives you a much higher degree of isolation. This brings a number of advantages:

There's no problem attaching a debugger to one connection (one JVM) while the others run unaffected.
There's no chance that one connection manages to accidentally (or maliciously) exchange dirty data with another connection.
A process that performs tasks that consume a lot of CPU under a long period of time can be scheduled with a lower priority using a simple OS command.
The JVMs can be brought down and restarted individually.
Security policies are much easier to enforce.

Transaction visibility

In order to maintain the correct visibility, the transaction must somehow be propagated to the Java layer. I can see two solutions for this using RPC. Either an XA aware JDBC-driver is used (requires XA support from PostgreSQL) or a JDBC driver is written so that it calls back to the SPI functions in the invoking process. Both choices results in an increased number of RPC calls and a negative performance impact.

The PL/Java approach is to use the underlying SPI interfaces directly through JNI by providing a "pseudo connection" that implements the JDBC interfaces. The mapping is thus very direct. Data need never be serialized nor duplicated.

RPC performance

Remote procedure calls are extremely expensive compared to in-process calls. Relying on an RPC mechanism for Java calls will cripple the usefulness of such an implementation a great deal. Here are two examples:

In order for an update trigger to function using RPC, you can choose one of two approaches. Either you limit the number of RPC calls and send two full Tuples (old and new) and a Tuple Descriptor to the remote JVM, and then pass a third Tuple (the modified new) back to the original, or you pass those structures by reference (as CORBA remote objects) and perform one RPC call each time you access them. You have a tradeoff between on one hand, limited functionality and poor performance, and on the other, good functionality and really bad performance.
When one or several Java functions are used in the projection or filter of a SELECT statement on a query processing several thousand rows, each row will cause at least one call to Java. In case of RPC, this implies that the OS needs to do at least two context switches (back and forth) for each row in the query.

Using JNI to directly access structures like TriggerData, Relation, TupleDesc, and HeapTuple minimizes the amount of data that needs to be copied. Parameters and return values that are primitives need not even become Java objects. A 32-bit int4 Datum can be directly passed as a Java int (jint in JNI).

Simplicity

I've have some experience of work involving CORBA and other RPCs. They add a fair amount of complexity to the process. JNI however, is invisible to the user.

pljava: Recent Wiki Updates

Home

Welcome to PL/Java

Important news

Overview

Features

Documentation

Resources

Technology

Contribution guide

Getting started

Let people know what you're planning

Making Changes

Submitting Changes

Which PL/Java branch to base on?

Organizing commits

But generally avoid force-pushing to a shared repository

Commit Message Format

JEP 411

PL/Java and JEP 411: Java 17, Java 24, and beyond

Permissions and enforcement in PL/Java

Changes in Java 17 and beyond: JEP 411

Java 17

Java 18 through 23

Java 24 and later

Plans for PL/Java release series

PL/Java 1.5 series

PL/Java 1.6 series

PL/Java ?.?

An OpenJDK fork to maintain policy enforcement

Build tips

Tips for resolving build problems

The tips that always apply

Failure shown for pljava-api

Fatal error compiling caused by invalid flag: --release

Failure shown for pljava-so

Missing -devel prerequisite packages

Still stuck?

Prebuilt packages

Prebuilt PL/Java distributions

Known prebuilt packages available

Debian/Ubuntu packages on apt.postgresql.org

Docker images

Complete PostgreSQL distributions from BigSQL

To list a prebuilt package here

Security

Installation

Trusted vs. untrusted language

Execution of the deployment descriptor

Classpath manipulation

Build process custom Maven plugin

Custom Maven plugin for the build process (GSoC 2020)

Relationship to PGXS

Role of scripting

We currently have this "inside-out"

Basic operations

Get PostgreSQL build-specific information by querying pg_config

Merge build-specific information into platform-specific recipes

How should the plugin embed the platform recipes?

Exported symbol filtering

Argument passing workaround for Windows

What might a workaround API look like?

But it's not quite that simple

One last thing: a simple report Mojo

Sql deployment descriptor

SQLJ deployment descriptors

Configurable implementor-block recognition

Thoughts on logging

Thoughts on logging

Background

PostgreSQL has supremely good log messages.

Original log event life cycle

Handling logged events in front-end code

Handling logged events in a back-end PL

As for PL/Java

How do the other PostgreSQL JDBCs give access to error details?

pgjdbc

pgjdbc-ng

Originating loggable events

As for PL/Java

Failure shown for `pljava-api`

`Fatal error compiling` caused by `invalid flag: --release`

Failure shown for `pljava-so`

Missing `-devel` prerequisite packages

An abstract `LogRecord` class (not derived from `Exception`)

An interface for exceptions that carry `LogRecord`s

Different concrete subclasses of `LogRecord`

An `ereport`-like API for creating a `LogRecord` from scratch

Could `pgjdbc` move to `java.util.logging` without disruption?

Connection strings generating `AppCDS` shared archive