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.
Avoiding CREATE EXTENSION for some reason? Use
LOAD 'libpljava-so-1.6.8'; instead of the CREATE EXTENSION
command. 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_locationlibjvm (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.vmoptionspljava.module_pathpljava/sharedir/pljava/pljava-1.6.8.jar and
pljava/sharedir/pljava/pljava-api-1.6.8.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.
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.
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 PL/Java's default policy grants them some filesystem access. Functions that
specify
LANGUAGE java can be created by any user or role that has been granted
USAGE permission ON LANGUAGE java. The default policy grants them no extra
permissions. The exact permissions granted in either case can be customized
in pljava.policy.
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 EXTENSIONERROR: relation "see doc: do CREATE EXTENSION PLJAVA in new session"
already exists
For PL/Java, CREATE EXTENSION is a wrapper around installation via LOAD
(which was needed for PostgreSQL versions now of only historical interest,
and remains supported for cases where CREATE EXTENSION is too inflexible).
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.
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.