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.
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.
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 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
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
pljava.module_path
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.
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.
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.
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.
Be sure to read these additional sections if:
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).
LOAD
or CREATE EXTENSION
timeIf 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
.
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.
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.
Exactly where you place the files, and what pathnames you use in the above commands, can depend on your situation:
postgres
?postgres
runs as, but the ability to write some locations that user can read?The rest of this page will cover those cases. First, the quick 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.
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.
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.
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.