Installing PL/Java

Selecting a current Java version to use with PL/Java

Whichever JDK version you use to build PL/Java, you may want to use PL/Java with another Java version at run time, so your PL/Java application code can use the newer Java features. When you reach the step setting PL/Java configuration variables, the pljava.libjvm_location variable will allow you to do that.

For the impatient

After completing the build:

java -jar pljava-packaging/target/pljava-pgX.jar

   (run the above with sufficient privilege to write in the PostgreSQL
    installation directories, or read further for how to install in
    non-standard places)

psql
CREATE EXTENSION pljava;
GRANT USAGE ON LANGUAGE java TO ...; -- see "Usage permission" below

where pgX represents the PostgreSQL version, and … wait, you’re impatient, just look in the directory, you’ll see the jar file there.

Upgrading an older PL/Java installation? Use CREATE EXTENSION pljava FROM unpackaged; after reading Upgrade installations below.

Not running PostgreSQL 9.1 or higher? Use LOAD 'libpljava-so-1.6.6'; instead of the CREATE EXTENSION command. (It works in later versions too, if you prefer it to CREATE EXTENSION.) Using a Mac? Be sure to add .bundle at the end of the file name in the LOAD command. Windows? Remove lib from the front. Something else? Keep reading.

You may get a message that some configuration variable must be set. If so, keep reading.

What the above will do

The jar file produced in pljava-packaging/target at the end of the build contains all the files needed to install PL/Java, and it is self-extracting when simply run with java -jar. It does not contain any complicated, black-box installer, and if you prefer, it is just as easy to extract normally with the jar command or any zip tool, in which case you will see that it contains files at symbolic paths such as

pljava/pkglibdir/...
pljava/sharedir/...

which you can move to wherever you like. Those names are based on the options you would pass to pg_config to find the right locations for your PostgreSQL setup, and that is exactly what the self-extractor does. Assuming it is run with the permissions needed to write there, it will put the PL/Java files into the expected locations, so that CREATE EXTENSION will find it without much configuration needed.

Because that is done by the extractor, you can take the built jar file to other PostgreSQL installations, even with other filesystem layouts, and it will put the files in the right places there too.

It has to be able to find pg_config, and if you have more than one installation of PostgreSQL on the machine, it has to find the right pg_config. You can either arrange the path in your environment so the right pg_config comes first, or specify it with -Dpgconfig= on the command line:

java -Dpgconfig=/local/pgsql/bin/pg_config -jar pljava-packaging/...

All of the standard install locations can also be changed with -D options:

java -Dpgconfig.sharedir=/local/foo -jar pljava-packaging/...

would cause all the files that would normally go in the configured share directory to be placed in /local/foo instead. You can therefore install PL/Java in many situations where you might not have write access to the standard locations, or might have other reasons to prefer giving the files another location.

If you change locations, you will probably have to adjust some of PL/Java’s configuration variables to match, before installation will succeed. For that, you will have to become patient, and read the rest of this page.

You will most probably have to set pljava.libjvm_location. See the next section.

It is useful to consider pljava.vmoptions. See the VM options page.

One last small thing the extractor does, that won’t automatically happen if you extract with other tools, is make sure the text files have the right line-ending style for your system. Everything will still work, and there are easy fixes if you open them in an editor and they look funny. As long as you just extract with java -jar the self-extractor takes care of it for you.

PL/Java configuration variables

PL/Java has configuration variables for most important file locations. One that you will almost always have to set is pljava.libjvm_location because the PL/Java installer doesn’t control where different platforms and packagers put the Java files. (If it did, things would be a lot more organized, have no doubt.)

You can set these variables within a database session, before issuing the LOAD or CREATE EXTENSION command. (In case you don’t always get things right on the first try, you might set them after, too.) For example:

SET pljava.libjvm_location TO '/usr/lib/jvm/java-9/lib/...';
pljava.libjvm_location
You are looking for a file named libjvm (or just jvm on some platforms, such as Windows) with extension .so, .dll, .bundle, or .dylib typically, buried a few directories/folders down in the location where Java is installed. If more than one Java version is installed, be sure to find the library from the version you want PL/Java to use. See locating libjvm for help finding it. Then set this variable to the full pathname, including the filename and extension.
pljava.vmoptions
While it should not be necessary to set these before seeing the first signs of life from PL/Java, there are useful options to consider setting here before calling the installation complete. Some are described on the VM options page.
pljava.module_path
There is probably no need to set this variable unless installation locations were changed, in which case, it should be set to the final installed full pathnames of the files that are called pljava/sharedir/pljava/pljava-1.6.6.jar and pljava/sharedir/pljava/pljava-api-1.6.6.jar in the installer jar. The pathnames should be separated by the appropriate character for your platform; often a colon, or a semicolon on Windows. Note: this variable isn’t meant to point to the code you develop and use in PL/Java—that’s what the sqlj.install_jar function is for.

Those three are not the only PL/Java configuration variables there are, but it is unlikely you would have to change any others before installation succeeds. For the rest, there is a configuration variable reference page.

One thing you can do with the configuration settings is quickly test that you have built a working PL/Java before installing the files in any permanent location. As long as the database server has access to the files in the build tree, you can start a session and set the variables to point to them, for a quick simple test. See locating the built files for how to find their exact names in your build tree. After testing, you would probably put the files in more permanent locations and discard the temporary variable settings.

Making the configuration settings persistent

If you have loaded PL/Java successfully after making necessary changes to some pljava.* variables, and the files are in the final locations where you want them, you will want your variable settings to stick. The simplest way is to reissue the same SET commands as ALTER DATABASE databasename SET variablename FROM CURRENT commands, which will preserve the current settings and make them effective when any user connects to the same database.

Another approach is to save them to the server’s configuration file. If you wish PL/Java to be available for all databases in a cluster, it may be more convenient to put the settings in the file than to issue ALTER DATABASE for several databases, but pg_ctl reload will be needed to make changed settings effective. Starting with PostgreSQL 9.4, ALTER SYSTEM may be used as an alternative to editing the file.

If you have several databases in the cluster and you favor the CREATE EXTENSION way of installing PL/Java, setting the variables with ALTER SYSTEM or the cluster-wide configuration file will make sure that CREATE EXTENSION just works, in any database where PL/Java is wanted. Different per-database settings can still be made if one database needs them.

For PostgreSQL releases earlier than 9.2, the configuration file is the only way to make your settings persistent.

Upgrade installations

PL/Java performs an upgrade installation if there is already an sqlj schema with tables that match a known PL/Java schema from version 1.3.0 or later. It will convert, preserving data, to the current schema if necessary.

A database cluster using PL/Java can be binary-upgraded using pg_upgrade when certain requirements are met.

For more on both procedures, see Upgrading.

Usage permission

Installation of PL/Java creates two “languages”, java and javau. Functions that specify LANGUAGE javau can be created only by superusers, and are subject to very few restrictions at runtime. Functions that specify LANGUAGE java can be created by any user or role that has been granted USAGE permission ON LANGUAGE java. They run under a security manager that denies access to the host filesystem.

Note: For implications when running on Java 17 or later, please see JEP 411.

PostgreSQL, by default, would grant USAGE to PUBLIC on the java language, but PL/Java takes a more conservative approach on a new installation. In keeping with the principle of least privilege, selective access can then be granted to those users or roles that will be expected to install Java functions. Usage may be explicitly granted to PUBLIC if a site prefers that traditional policy.

In a repeat or upgrade installation (the language java already exists), no change will be made to the access permissions granted on it.

Special topics

Be sure to read these additional sections if:

Troubleshooting the installation

Puzzling error message from CREATE EXTENSION

ERROR:  relation "see doc: do CREATE EXTENSION PLJAVA in new session"
already exists

For PL/Java, CREATE EXTENSION (which works in PostgreSQL 9.1 and later) is a wrapper around installation via LOAD (which works in all versions PL/Java supports). A quirk of this arrangement is that PostgreSQL treats LOAD as a no-op for the remainder of a session once the library has been loaded, so CREATE EXTENSION pljava works in a fresh session, but not in one where PL/Java’s native code is already in place.

In that case, you see the above message about a strangely but meaningfully named table. The solution is simple: just retry in a new session (in psql, \c makes that easy).

Undefined symbol errors at LOAD or CREATE EXTENSION time

If PL/Java loading fails with undefined-symbol errors that seem to refer to common system libraries (libldap, for example), see Building PL/Java with a RUNPATH.

More background and special considerations

These last sections cover a little more of what happens under the hood. CREATE EXTENSION wraps these details up nicely, but they may still be of interest for particular needs.

Install, configure, check

Because PL/Java, by design, runs entirely in the backend process created for each connection to PostgreSQL, to configure it does not require any cluster-wide actions such as stopping or restarting the server, or editing the configuration file; any necessary settings can be made in SQL over an ordinary connection (in PostgreSQL 9.2 and later, anyway).

Caution: if you are installing a new, little-tested PL/Java build, be aware that in the unexpected case of a crash, the postmaster will kick other sessions out of the database for a moment to verify integrity, and then let them reconnect. If that would be disruptive, it may be best to initdb a new cluster in some temporary location and test PL/Java there, installing to a production server only when satisfied.

After connecting to the desired database (the connection must be as a PostgreSQL superuser), the commands for first-time PL/Java setup are:

SET client_min_messages TO NOTICE; -- or anything finer (INFO, DEBUG, ...)
SET pljava.libjvm_location TO ' use the libjvm path here ';
SET pljava.module_path TO ' use the pljava and pljava-api jar paths here ';
LOAD ' use the PL/Java native library path here ';

(The client_min_messages setting is only to ensure you do not miss the NOTICE message in case of success.) If you see

NOTICE: PL/Java loaded

then you are ready to test some PL/Java functions, such as the ones in the examples.jar supplied in the build.

Although typically only pljava.libjvm_location and pljava.module_path need to be right for PL/Java to function, there is a reference to PL/Java configuration variables if you need it.

Choosing where to place the files

Exactly where you place the files, and what pathnames you use in the above commands, can depend on your situation:

  • Are you a superuser on the OS where PostgreSQL is installed (or are you a distribution maintainer building a PL/Java package for that platform)?
  • Are you not an OS superuser, but you have PostgreSQL superuser rights and OS permissions as the user that runs postgres?
  • Do you have only PostgreSQL superuser rights, no ability to write locations owned by the user postgres runs as, but the ability to write some locations that user can read?
  • Do you have PostgreSQL superuser rights and want to quickly test that you have built a working PL/Java?

The rest of this page will cover those cases. First, the quick check.

A quick install check

For a quick sanity test, there is no need to move the built files to more permanent locations, as long as the build tree location and permissions are such that the PostgreSQL backend can read them where they are. Use those pathnames directly in the SET and LOAD commands.

For the lowest-impact quick test, begin a transaction first, load PL/Java and run any tests you like, then roll the transaction back.

OS superuser or distribution maintainer

If you fall in this category, you can minimize configuration within PostgreSQL by placing the built files into standard locations, so SET commands are not needed for PostgreSQL to find them. For example, if the PL/Java native library is copied into the PostgreSQL $libdir (shown by pg_config as PKGLIBDIR), then the LOAD command can be given just the basename of the file instead of a full path. Or, if dynamic_library_path is already set, the file can be placed in any directory on that list for the same effect.

If the pljava-${project.version}.jar and pljava-api-${project.version}.jar files are placed in the default location (typically a pljava subdirectory of the PostgreSQL “share” directory), then pljava.module_path will not need to be set.

The self-extracting jar file produced by the build, assuming it is run with adequate permission, will extract the files into appropriate locations determined by querying pg_config on the target system. If that system may have more than one PostgreSQL installation and you wish to control which one the files get installed into, pass the full path to that installation’s pg_config executable with -Dpgconfig= on that java -jar ... command line. (In more difficult cases, each category of file location, such as pgconfig.sharedir, can be separately overridden on the command line.)

If you are a distribution maintainer packaging PL/Java for a certain platform, and you know or control that platform’s conventions for where the Java libjvm should be found, please supply that full path on the mvn command line with -Dpljava.libjvmdefault= to make it the default for pljava.libjvm_location, so users on that platform can see a working PL/Java with no need to set that variable in the usual case. That tip and more are covered in packaging PL/Java for a software distribution.

PostgreSQL superuser with access as user running postgres

If you are not a superuser on the OS, you may not be able to place the PL/Java files in the default locations PostgreSQL was built with. If you have permissions as the user running postgres, you might choose locations in a directory associated with that user, such as the DATADIR, and set the pljava.* variables to point to them. Use a LOAD command with the full path of the native library, or set dynamic_library_path to include its location, and give only the basename to LOAD.

If you would rather ensure that the user running postgres, if compromised, could not modify these files, then the next case will be more appropriate.

PostgreSQL superuser, OS user distinct from the user running postgres

In this case, simply place the files in any location where you can make them readable by the user running postgres, and set the pljava.* variables accordingly.