running-java-within-postgres comment

[jcflack]

Hi!

Thanks for the blog post about PL/Java. I noticed a couple of things that might allow it to be simplified.

0. While your post says PL/Java is not available as a prebuilt package, as you are using debian in your example, in fact it is. If you enable the PostgreSQL debian repository, you can simply apt-get install postgresql-server-9.6 postgresql-9.6-pljava (along with whatever other packages you need at run time). There is then no need for maven, gcc, git, or any build-from-source process.
1. The debian package of PL/Java declares its own dependency on default-java, so that package will be pulled in if you don't have it, without needing to be requested.
2. When using the debian package, there is no need for SET pljava.libjvm_location as the packager has compiled in a default value that refers to the normal default-java package. Therefore, the pljava.libjvm_location variable only needs to be set if you wish to run PL/Java with a different Java runtime than the default.
3. If the user postgres is a superuser in your database, there is no real need for GRANT USAGE ON LANGUAGE java TO postgres;.
4. It might be worth mentioning that the jdbc:default:connection is not a conventional JDBC connection (it does not use PG's network protocol to converse with the backend); it is simply a shim that presents direct internal access to the backend via the familiar JDBC API.
5. Code written for Java 7 or later would conventionally use try-with-resources when opening JDBC Connections, Statements, or ResultSets. (In PL/Java, for Connection it doesn't really matter, as that shim "connection" never closes anyway. But it is harmless, and for Statement or ResultSet it can ensure resources are released.)
6. If a @Function annotation does not have a comment attribute, but the function has a javadoc comment, the first sentence of the javadoc will be used as the SQL function comment.

But those are minor points. I think the post did quite a good job covering the basics.

A couple more direct links into the documentation that might be helpful are to the Hello, world example (which is much like your own example but has a "further reading" list at the bottom) and, for production environments, the VM option recommendations page: Java's default VM options on a server machine kind of assume it has the machine to itself, and some tweaks to those options can greatly reduce its footprint when running in PostgreSQL.

Thanks for the coverage!

[rieckpil]

Hey @jcflack, thanks for reaching out and giving feedback on the article. I'll add the missing links/parts in the blog post.

Thank you!

[jcflack]

You're welcome. :) I noticed another thing:

   SELECT sqlj.install_jar( 'file:///tmp/simple-java-function/target/simple-java-function.jar','jfunctions', true );

The function takes the fully-qualified path of the .jar file, ...

To be honest, PL/Java doesn't really care whether the URL is relative or absolute, certainly not enough to put "fully-qualified" in boldface.

select sqlj.install_jar('file:foo.jar', 'foo', false);

would load foo.jar if it happened to be in the server's current directory (the datadir). If you give a relative file: URL, you just have to know where the file is, relative to the datadir.

You can also give an absolute file: URL using just one leading slash rather than three. Three slashes are just the //hostname/path form where the hostname happens to be empty, meaning the local host, just the same as /path.

Cheers!

[rieckpil]

I've updated the post: https://rieckpil.de/howto-writing-postgresql-functions-with-java-using-pl-java/. You can have a second look and give me feedback if you want.

Thank you very much!

[jcflack]

It looks good. The "install with apt-get" section skips a little too far ahead: it is still necessary to CREATE EXTENSION pljava inside the database. But there should be no need to SET pljava.libjvm_location if using the package from the repo (unless you want PL/Java to run with a different JVM than default-java).

In neither case should it be necessary to GRANT USAGE ON LANGUAGE java TO postgres (unless the database has an unusual configuration and the postgres is not a superuser).

Be aware that this is not a conventional JDBC URL, rather than a shim for direct access.

I might say something like "this is not a conventional JDBC connection to a new postgres backend, but rather direct access into the current backend through the JDBC API."

The word 'than' is extraneous. 'Rather' can work two ways in English, with and without 'than', and the JDBC sentence is of the second type:

• A dog is a mammal, rather than a reptile.
• A dog is not a reptile, rather a mammal.

Thanks!

[jcflack]

I remembered one more thing I meant to mention. Before I forget it again:

try(ResultSet resultSet = DriverManager //
       .getConnection("jdbc:default:connection") //
      .createStatement() //
       .executeQuery("SELECT * FROM persons")) {

Could be done as:

try(Statement s = DriverManager.getConnection("jdbc:default:connection") //
                  .createStatement(),
    ResultSet resultSet = s.executeQuery("SELECT * FROM persons")) {

so that both the Statement and the ResultSet will be closed when the scope is exited.

Even this way would work:

try(Statement s = DriverManager.getConnection("jdbc:default:connection") //
                  .createStatement()) {
  ResultSet resultSet = s.executeQuery("SELECT * FROM persons");

where only the Statement would be an auto-closed resource, but that's ok because closing a Statement closes any dependent ResultSets.

But auto-closing a ResultSet doesn't close the Statement that created it, so the current version in the blog would leave the Statement open.

[rieckpil]

@jcflack I've updated the post again. Thanks for your patience

[jcflack]

I see another possible refinement: :)

GRANT USAGE ON LANGUAGE java TO your_user;

... the GRANT USAGE statement is required for your database application user to have access to pljava.

A user with USAGE privilege on a language is able to create new functions that are written in that language.

Once a function is created, any user with EXECUTE privilege on that function is able to use the function.

Depending on your security requirements, you might want the same user to be able to create new functions and to use existing ones, or you might want an application user only to be able to execute existing functions, while reserving the privilege of creating functions to a different user, or even only to superusers (in which case no GRANT USAGE is needed).